unknown - ¿MySQL no usa índices con la cláusula WHERE IN?

unknown column user in where clause

Question

Estoy tratando de optimizar algunas de las consultas de la base de datos en mi aplicación Rails y tengo varias que me han dejado perplejo. Todos están usando un IN en la cláusula WHERE y todos realizan escaneos de tabla completa, a pesar de que parece existir un índice apropiado.

Por ejemplo:

SELECT `user_metrics`.* FROM `user_metrics` WHERE (`user_metrics`.user_id IN (N,N,N,N,N,N,N,N,N,N,N,N))

realiza un escaneo completo de la tabla y EXPLAIN dice:

select_type: simple
type: all
extra: using where
possible_keys: index_user_metrics_on_user_id  (which is an index on the user_id column)
key: (none)
key_length: (none)
ref: (none)
rows: 208

¿Los índices no se usan cuando se usa una declaración IN o tengo que hacer algo diferente? Las consultas aquí las está generando Rails para poder volver a ver cómo se definen mis relaciones, pero primero pensé que comenzaría con posibles soluciones en el nivel de base de datos.

Answer 1

¿Es mejor si elimina los corchetes redundantes alrededor de la cláusula where?

Aunque podría ser solo porque solo tienes 200 o más filas, decidió que un escaneo de tabla sería más rápido. Pruebe con una tabla con más registros en ella.

Answer 2

Algunas veces MySQL no usa un índice, incluso si uno está disponible. Una circunstancia bajo la cual esto ocurre es cuando el optimizador estima que usar el índice requeriría que MySQL tenga acceso a un porcentaje muy grande de las filas en la tabla. (En este caso, es probable que un escaneo de tabla sea mucho más rápido porque requiere menos búsquedas).

¿Qué porcentaje de filas coincide con su cláusula IN?

Answer 3

Intenta forzar este índice:

SELECT `user_metrics`.*
FROM `user_metrics` FORCE INDEX (index_user_metrics_on_user_id)
WHERE (`user_metrics`.user_id IN (N,N,N,N,N,N,N,N,N,N,N,N))

Acabo de verificar, utiliza un índice exactamente en la misma consulta:

EXPLAIN EXTENDED
SELECT * FROM tests WHERE (test IN (''test 1'', ''test 2'', ''test 3'', ''test 4'', ''test 5'', ''test 6'', ''test 7'', ''test 8'', ''test 9''))

1, ''SIMPLE'', ''tests'', ''range'', ''ix_test'', ''ix_test'', ''602'', '''', 9, 100.00, ''Using where''

Answer 4

Sé que llego tarde a la fiesta. Pero espero poder ayudar a alguien más con un problema similar.

Últimamente, estoy teniendo el mismo problema. Luego decido usar self-join-thing para resolver mi problema. El problema no es MySQL. El problema somos nosotros El tipo de devolución de la subconsulta es una diferencia de nuestra tabla. Por lo tanto, debemos convertir el tipo de subconsulta en el tipo de columna de selección. Debajo está el código de ejemplo:

select `user_metrics`.* 
from `user_metrics` um 
join (select `user_metrics`.`user_id` in (N, N, N, N) ) as temp 
on um.`user_id` = temp.`user_id`

O mi propio código:

Antiguo: (No usar índice: ~ 4s)

SELECT 
    `jxm_character`.*
FROM
    jxm_character
WHERE
    information_date IN (SELECT DISTINCT
            (information_date)
        FROM
            jxm_character
        WHERE
            information_date >= DATE_SUB(''2016-12-2'', INTERVAL 7 DAY))
        AND `jxm_character`.`ranking_type` = 1
        AND `jxm_character`.`character_id` = 3146089;

Nuevo: (Use índice: ~ 0.02s)

SELECT 
    *
FROM
    jxm_character jc
        JOIN
    (SELECT DISTINCT
        (information_date)
    FROM
        jxm_character
    WHERE
        information_date >= DATE_SUB(''2016-12-2'', INTERVAL 7 DAY)) AS temp 
        ON jc.information_date = STR_TO_DATE(temp.information_date, ''%Y-%m-%d'')
        AND jc.ranking_type = 1
        AND jc.character_id = 3146089;

jxm_character:

• Registros: ~ 3.5M
• PK: jxm_character (information_date, ranking_type, character_id)

SHOW VARIABLES LIKE ''%version%'';

''protocol_version'', ''10''
''version'', ''5.1.69-log''
''version_comment'', ''Source distribution''

Última nota: asegúrese de entender la regla de más a la izquierda del índice MySQL.

P / s: Perdón por mi mal inglés. Publiqué mi código (producción, por supuesto) para borrar mi solución: D.

Answer 5

Vea cómo MySQL usa índices .

También valide si MySQL aún realiza un escaneo de tabla completo después de agregar filas adicionales de 2000 o así a su tabla user_metrics . En las tablas pequeñas, el acceso por índice es en realidad más caro (I / O-wise) que un escaneo de tabla, y el optimizador de MySQL podría tener esto en cuenta.

Contrariamente a mi publicación anterior , resulta que MySQL también está utilizando un optimizador basado en costos , lo cual es una muy buena noticia, es decir, siempre que ejecute ANALYZE al menos una vez cuando crea que el volumen de datos en su base de datos es representativo. del uso diario futuro.

Al tratar con optimizadores basados ​​en costos (Oracle, Postgres, etc.), debe asegurarse de ejecutar periódicamente ANALYZE en sus diversas tablas a medida que su tamaño aumenta en más de un 10-15%. (Postgres lo hará automáticamente por usted, de forma predeterminada, mientras que otros RDBMS dejarán esta responsabilidad a un DBA, es decir, usted). A través del análisis estadístico, ANALYZE ayudará al optimizador a tener una mejor idea de la cantidad de E / S (y otras recursos, tales como CPU, necesarios, por ejemplo, para la clasificación) estarán involucrados al elegir entre varios planes de ejecución candidatos. La falla al ejecutar ANALYZE puede resultar en decisiones de planificación muy malas, a veces desastrosas (por ejemplo, consultas de milisegundos que toman, a veces, horas debido a bucles anidados incorrectos en JOIN s).

Si el rendimiento sigue siendo insatisfactorio después de ejecutar ANALYZE , normalmente podrá evitar el problema mediante el uso de sugerencias, por ejemplo, FORCE INDEX , mientras que en otros casos podría haber tropezado con un error de MySQL (por ejemplo, este más antiguo , que podría haber mordido eras tú para usar Rails '' nested_set ).

Ahora, dado que está en una aplicación de Rails , será engorroso (y anulará el propósito de ActiveRecord ) emitir sus consultas personalizadas con pistas en lugar de seguir utilizando las generadas por ActiveRecord .

Mencioné que en nuestra aplicación Rails todas las consultas SELECT caían por debajo de 100ms después de cambiar a Postgres, mientras que algunas de las combinaciones complejas generadas por ActiveRecord ocasionalmente tomaban 15s o más con MySQL 5.1 debido a bucles anidados con escaneos internos de tablas, incluso cuando los índices estaban disponibles. Ningún optimizador es perfecto, y debe conocer las opciones. Otros posibles problemas de rendimiento a tener en cuenta, además de la optimización del plan de consulta, son el bloqueo. Sin embargo, esto está fuera del alcance de su problema.