Explain: optimización de búsquedas MySQL

Existe una herramienta integrada en MySQL que te permite obtener información muy interesante, y que te dará muy buenas pistas de optimización a la hora de construir tus consultas y tus índices. Esto es EXPLAIN.

Uso de EXPLAIN

Su uso no puede ser más sencillo. Simplemente agréguelo delante de una consulta MySQL.
Por ejemplo :

EXPLAIN SELECT nom, prenom FROM utilisateurs WHERE nom = 'Dupont';

También puede obtener más información usando el parámetro EXTENDIDO (ejecutando el comando EXPLAIN EXTENDED).

Luego tendrá un resultado como este, que le indicará el plan de ejecución de la consulta:

Interpretación de resultados

Echemos un vistazo más de cerca al resultado de EXPLAIN.

id : esta columna da un id diferente a cada SELECT (en el caso de subconsultas). En el ejemplo a continuación, todas las filas tienen 1 en id porque son parte de una única cláusula SELECT.
select_type : el select_type depende de su consulta y cualquier anidamiento de consultas y subconsultas, o unión entre varios SELECT. Puede tener como valor, por ejemplo, SIMPLE para un SELECT simple, PRIMARIO para el primer SELECT de un anidamiento, DERIVED para SELECT derivados , UNIÓN…
table : la tabla o el alias afectado por la línea.
type : como tipo de unión, uno de los valores más importantes que devuelve EXPLAIN. Puede tomar los siguientes valores, del mejor valor posible al peor:
- sistema : la tabla tiene una sola fila
- const : la tabla devuelve como máximo una fila indexada. A continuación, la tabla se lee una sola vez y el valor se considera una constante.
- eq_ref : el índice de tipo PRIMARIO o ÚNICO NO NULO es utilizado completamente por la unión. Para cada combinación de las tablas anteriores, solo se lee una fila de esta tabla.
- ref : todas las filas de la tabla con valores de índice coincidentes se leen para cada combinación de las tablas anteriores. Por lo tanto, la diferencia con eq_ref es que la combinación no puede seleccionar una sola fila.
- fulltext : la unión se basa en un índice de texto completo.
- ref_or_null : idéntico a ref con la adición de una búsqueda de valores nulos.
- index_merge : la unión utiliza la optimización «Index Merge», es decir, para responder a la consulta, fusionará varios índices existentes en la tabla. En este punto, podría surgir la pregunta de si no se puede establecer un índice de varias columnas. Puede ver este artículo en el sitio de Percona que trata el tema, así como el documento oficial sobre la fusión de índices .
- unique_subquery : equivalente a eq_ref para ciertas subconsultas en PRIMARY KEYs o índices únicos utilizados en un IN.
- index_subquery : equivalente a unique_subquery pero subíndices no únicos.
- range : recuperación de líneas correspondientes a un rango de datos, en particular con operadores como BETWEEN, >, <, >=, <= …
- index : équivalent à ALL (voir ci-dessous), mais à la différence que l’arbre d’index est scanné complètement au lieu de la table (ce qui est généralement plus rapide, l’index étant la plupart du temps plus petit que la mesa).
- all : se escanea toda la tabla, esto generalmente indica que se deben revisar los índices o la consulta.
possible_keys : indica los índices que MySQL cree que pueden ser relevantes para responder a la consulta. Si tiene «NULL» en esta columna, puede echar un vistazo más de cerca para ver si sería necesario crear un índice.
key : clave que ha decidido utilizar MySQL, que también puede ser otro índice diferente al o a los listados entre los posibles. Este valor de retorno, junto con el anterior, es muy importante para determinar si tus índices son buenos y relevantes, y si la creación de un índice puede ser interesante.
key_len : longitud del índice retenido.
ref : indica el campo que se compara con «clave».
filas : estimación del número de filas que MySQL cree que debería examinar (esto puede ser diferente del número real).
filtrado (solo con EXTENDIDO): da una estimación del porcentaje de filas que serán filtradas por la(s) condición(es).
extra : información adicional que devuelve EXPLAIN. Tienes la lista exhaustiva de posibles valores en la documentación oficial . Tenga en cuenta algunos valores interesantes de todos modos, como » using temporal «, que indica que la consulta tendrá que crear una tabla temporal, y » using filesort «, que indica que será necesario un pase adicional para ordenar los resultados. Si tiene un » using index «, es una buena noticia, significa que tiene un índice de cobertura y que MySQL podrá responder sin acceso a la tabla.

Casos de uso e índice no utilizado

En comparación con el análisis de los resultados de EXPLAIN, ha visto que esto le permitirá ver si sus índices están bien utilizados y, por lo tanto, si son relevantes. Esto también le permitirá optimizar más fácilmente sus consultas.
Por ejemplo, pude así realizar sobre una base de la que había recuperado el mantenimiento de ciertas preocupaciones. Una de las consultas estaba tardando mucho en ejecutarse, mientras que los índices parecían relevantes. Usando EXPLAIN, pude ver que el índice principal de una tabla no se estaba usando, mientras que se estaba usando como una unión entre las dos tablas más grandes en la consulta. Al profundizar en la pregunta, pude ver que los campos no eran equivalentes entre las dos tablas (los dos varchar no tenían el mismo tamaño y, curiosamente, uno de ellos tenía una codificación diferente). Después de modificar el tamaño y la codificación, se utiliza el índice y el tiempo de ejecución se divide por 10.

Una buena idea es acoplar este análisis a las consultas que le devuelve el registro de Slow Queries de MySQL .