Ubicación de columnas de alta cardinalidad en índices compuestos con consultas de rango

Al consultar una tabla con un índice compuesto que involucra una condición de rango, la ubicación de las columnas dentro del índice pueden afectar significativamente el rendimiento.

Considere los archivos de tabla con una clave principal (did, nombre de archivo) y dos índices compuestos: ÍNDICE (hora del archivo, ext) e ÍNDICE (ext, tiempo de archivo). Ambos índices contienen la columna de tiempo de archivo, que tiene una cardinalidad más alta que ext.

La consulta:

WHERE ext = '...'
  AND filetime BETWEEN ... AND ...

requiere acceder a datos basados ​​tanto en ext como en filetime. Surge la pregunta: ¿qué índice es óptimo para tal consulta?

Análisis

Para determinar el índice óptimo, podemos usar FORCE INDEX y examinar los planes de ejecución:

-- Force range on filetime first
FORCE INDEX(fe) SELECT COUNT(*), AVG(fsize)
FROM files
WHERE ext = 'gif'
  AND filetime >= '2015-01-01'
  AND filetime = '2015-01-01'
  AND filetime 
El resultado muestra que INDEX(ext, filetime) (ef) tiene un recuento de filas significativamente menor, lo que indica un escaneo más eficiente.
Seguimiento del optimizador
Para analizar más a fondo el comportamiento del optimizador, podemos utilizar el optimizador seguimiento:
SELECT explain_format = 'JSON';

SELECT COUNT(*), AVG(fsize)
FROM files
WHERE ext = 'gif'
  AND filetime >= '2015-01-01'
  AND filetime 
El seguimiento revela que el optimizador elige INDEX(ext, filetime) porque puede usar ambas columnas del índice para filtrar y recuperar datos. Por el contrario, INDEX(filetime, ext) solo puede usar la primera columna (filetime) para filtrar.
Conclusiones
Con base en el análisis, se pueden sacar las siguientes conclusiones dibujado:

• Para índices compuestos utilizados en consultas de rango, la columna involucrada en el predicado de igualdad (ext en este caso) debe colocarse primero en el índice definición.
• El rendimiento de la consulta mejora cuando las columnas de un índice se ordenan según el orden en que se utilizan en la cláusula WHERE.
• La cardinalidad por sí sola no es un factor decisivo para determinar la índice óptimo. En escenarios donde la columna de rango tiene una cardinalidad más alta pero la columna de igualdad está involucrada en un predicado de igualdad, colocar la columna de igualdad primero produce un mejor rendimiento.