¿Cómo manejo las sentencias SQL lentas causadas por configuraciones de índice compuesto inadecuadas?
Escenario
En su instancia, una consulta SQL que se ejecutaba a las 11:00 y se esperaba que durara 8 segundos tomó más de 30 segundos.
Causas posibles
- Compruebe el uso de la CPU. En este ejemplo, durante ese período de tiempo, el uso de la CPU de la instancia no aumentó bruscamente y se mantuvo bajo, por lo que sabemos que la consulta lenta no fue causada por un uso alto de la CPU.
Figura 1 Uso de CPU
- Analizar los registros de consultas lentas generados durante ese período. En este ejemplo, que se muestra a continuación, había varias sentencias SQL que involucraban millones de filas que se analizaban. Estas fueron las sentencias lentas. Pero no se insertó una gran cantidad de datos en la tabla durante ese tiempo, por lo que sabemos que la ejecución lenta fue causada por la falta de configuración de índice o incorrecta. Al ejecutar EXPLAIN, se puede encontrar que el plan de ejecución de la sentencia SQL era un análisis de tabla completo.
Figura 2 Registros de consultas lentas
- Realice SHOW INDEX FROM en la tabla de la instancia para comprobar la cardinalidad de las tres columnas.
Figura 3 Cardinalidad del índice
El campo query_date con menor cardinalidad estaba en el primer lugar del índice compuesto, y el campo group_id con mayor cardinalidad estaba en el último lugar del índice compuesto. Además, la sentencia SQL contenía la consulta de rango del campo query_date. Como resultado, solo se indexó el campo query_date.
La sentencia SQL solo puede utilizar el índice de la columna query_date. Adicionalmente, el optimizador puede haber seleccionado la exploración de tabla completa durante la estimación de costes debido a que la cardinalidad era demasiado pequeña.
Se creó un nuevo índice compuesto con el campo group_id en primer lugar y el campo query_date en último lugar. El tiempo de consulta cumplió con las expectativas.
Solución
- Compruebe si la consulta lenta fue causada por recursos de CPU insuficientes.
- Compruebe si la estructura de la tabla está diseñada correctamente y si la configuración del índice es correcta.
- Ejecute la sentencia ANALYZE TABLE periódicamente para evitar planes de ejecución incorrectos, ya que realizar un gran número de operaciones INSERT o DELETE para los datos de la tabla puede resultar en estadísticas desactualizadas.
