mysql - LIMIT 1 es muy lento, para registros específicos, usando diferentes teclas

performance optimization

Question

Estoy diagnosticando una consulta lenta intermitente y he encontrado un comportamiento extraño en MySQL que no puedo explicar. Es elegir una estrategia clave diferente, no óptima para un caso específico, solo cuando se hace un LIMIT 1 .

Tabla (algunas columnas de datos sin referencia eliminadas por brevedad)

CREATE TABLE `ch_log` (
`cl_id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`cl_unit_id` INT(11) NOT NULL DEFAULT ''0'',
`cl_date` DATETIME NOT NULL DEFAULT ''0000-00-00 00:00:00'',
`cl_type` CHAR(1) NOT NULL DEFAULT '''',
`cl_data` TEXT NOT NULL,
`cl_event` VARCHAR(255) NULL DEFAULT NULL,
`cl_timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`cl_record_status` CHAR(1) NOT NULL DEFAULT ''a'',
PRIMARY KEY (`cl_id`),
INDEX `cl_type` (`cl_type`),
INDEX `cl_date` (`cl_date`),
INDEX `cl_event` (`cl_event`),
INDEX `cl_unit_id` (`cl_unit_id`),
INDEX `log_type_unit_id` (`cl_unit_id`, `cl_type`),
INDEX `unique_user` (`cl_user_number`, `cl_unit_id`)
)
ENGINE=InnoDB
AUTO_INCREMENT=419582094;

Esta es la consulta, que solo se ejecuta lentamente para un cl_unit_id específico :

EXPLAIN
SELECT *
FROM `ch_log`
WHERE `ch_log_type` =''I'' and ch_log_event = ''G''
AND cl_unit_id=1234
ORDER BY cl_date DESC
LIMIT 1;

id|select_type|table |type |possible_keys |key |key_len|ref|rows|Extra
1 |SIMPLE |ch_log|index|cl_type,cl_event,cl_unit_id,log_type_unit_id|cl_date|8 |/N |5295|Using where

Para todos los demás valores de cl_unit_id utiliza la clave log_type_unit_id que es mucho más rápida.

id|select_type|table |type|possible_keys |key |key_len|ref |rows|Extra
1 |SIMPLE |ch_log|ref |ch_log_type,ch_log_event,ch_log_unit_id,log_type_unit_id|log_type_unit_id|5 |const,const|3804|Using where; Using filesort

• Todas las consultas toman alrededor de 0.01 segundos.
• La consulta de "unidad lenta" toma 10-15 minutos!

No puedo ver nada extraño en los datos de esta ''unidad'':

• La unidad 1234 solo tiene 6 registros de tipo I y evento G.
• Otras unidades tienen muchas más.
• La unidad 1234 solo tiene 32,000 registros en total, lo cual es típico.
• Los datos en sí son normales, ni más grandes ni más viejos.
• Hay alrededor de 3.000 "unidades" en la base de datos, que representan dispositivos de registro. El cl_unit_id es su PK único (aunque sin restricción).

Información general

• Hay 30 millones de registros en total, alrededor de 12 GB.
• mysql 5.1.69-log
• Centos 64bit
• Los datos están cambiando gradualmente (30m = 3 meses de registros) pero no sé si esto ha ocurrido antes

Cosas que he intentado, y puedo "resolver" el problema con:

1. Eliminando el LIMIT 1 : la consulta se ejecuta en milisegundos y devuelve los datos.

2. Cambiar a LIMIT 2 u otras combinaciones, por ejemplo, 2,3, se ejecuta en milisegundos.

3. Añadiendo una sugerencia de índice - la resuelve:

   FROM `ch_log` USE INDEX (log_type_unit_id)

   pero ... no quiero codificar esto en la aplicación.

4. Agregar una segunda orden en la clave principal también lo "resuelve":

   ORDER BY cl_id, cl_date DESC 

   dando explicacion

   id|select_type|table |type|possible_keys |key |key_len|ref |rows|Extra
   1 |SIMPLE |ch_log|ref |ch_log_type,ch_log_event,ch_log_unit_id,log_type_unit_id|log_type_unit_id|5 |const,const|6870|Using where

   que es ligeramente diferente al tipo insinuado, con más registros examinados (6,000) pero aún se ejecuta en decenas de milisegundos.

Una vez más, podría hacer esto, pero no me gusta usar efectos secundarios que no entiendo.

Así que creo que mi pregunta principal es:

a) ¿Por qué solo sucede para LIMIT 1 ?

b) ¿Cómo pueden los datos en sí mismos afectar tanto la estrategia clave? Y qué aspecto de los datos, visto como la cantidad y la propagación en los índices parece típico.

Answer 1

Mysql elegirá un plan de explicación y usará diferentes índices dependiendo de lo que considere que es estadísticamente la mejor opción. Para todas sus primeras preguntas, esta es la respuesta:

1. Eliminando el LIMIT 1 : la consulta se ejecuta en milisegundos y devuelve los datos. y -> Sí, verifíquelo, el plan de explicación es bueno.
2. Cambiar a LIMIT 2 u otras combinaciones, por ejemplo, 2,3, se ejecuta en milisegundos. -> Se aplica lo mismo. El optimizador elige un índice diferente porque, de repente, las lecturas de bloque esperadas se vuelven dos veces más grandes que con LIMIT 1 (esa es solo una posibilidad)
3. Agregar una sugerencia de índice lo resuelve -> Por supuesto, obliga a un buen plan de explicación
4. Agregar una segunda orden con la clave principal también lo "resuelve" -> sí, porque por coincidencia, el resultado es un plan mejor explicado

Ahora, eso solo responde a la mitad de las preguntas.

a) ¿Por qué solo sucede para LIMIT 1?

En realidad sucede no solo por LIMIT 1 , sino por

• Su estadística de datos se reparte (orienta las decisiones del optimizador)
• Su cláusula ORDER BY DESC . Pruebe con ORDER BY ... ASC y probablemente también verá una mejora.

Este fenómeno está perfectamente reconocido. Por favor sigue leyendo .

Una de las soluciones aceptadas (de abajo hacia abajo en el artículo) es forzar el índice de la misma manera que lo hizo. Sí, a veces, está justificado. De lo contrario, esta sugerencia habría sido totalmente eliminada hace mucho tiempo. Los robots no pueden ser siempre perfectos :-)

b) ¿Cómo pueden los datos en sí mismos afectar tanto la estrategia clave? Y qué aspecto de los datos, visto como la cantidad y la propagación en los índices parece típico.

Lo dijiste, la propagación es lo que por lo general se cuela. No solo el optimizador puede tomar una decisión equivocada con estadísticas precisas, sino que también puede estar completamente apagado solo porque el delta en la tabla está justo por debajo de 1/16 del total de filas ...