MySQL: ¿Para qué sirve EXPLAIN en una consulta y cómo interpretar los resultados obtenidos?

publicado por: Anonymous

He visto que en MySQL se puede ejecutar una consulta como lo hacemos normalmente:

SELECT 
     mis, columnas 
     FROM mitabla mt 
     INNER JOIN otratabla ot 
     ON mt.id = ot.id

Pero poniendo como primer comando EXPLAIN :

EXPLAIN SELECT 
     mis, columnas 
     FROM mitabla mt 
     INNER JOIN otratabla ot 
     ON mt.id = ot.id

Mi pregunta es, ¿para qué sirve EXPLAINy cómo han de ser interpretados los resultados arrojados cuando se ejecuta la consulta de esta manera si queremos mejorar los índices de una tabla?

solución

Tenía un artículo muy interesante guardado sobre EXPLAIN, que consta de 23 capítulos, con muchos ejemplos con explicación breve, para así entender mejor su funcionamiento, lástima que está en inglés y es un poco extenso, aun así, me anime a traducirlo a español de la mejor forma posible, para entenderlo mejor, ya que personalmente me parece un temario interesante.

Código fuente:
Adrian Hardy’s slides from PHPNW08
StackOverflow

1. Explicación MySQL EXPLAIN

Rápida y Fácil Optimización de Consultas

Adrián Hardy

2. Antes de comenzar …

Lo que usted necesita saber

  • Cómo y por qué agregamos índices a tablas
  • Los beneficios de mecanografía de campo correcto
  • Comprensión de los ideales de 3NF
  • Comprensión básica de SQL JOINs

Esta presentación

  • Introducción muy rápida a EXPLICAR
  • Mejorar la comprensión de MySQL y la indexación
  • Ejemplos / resultados simplificados

3. Introducción – Utilizar MySQL EXPLAIN

Prefijo una consulta con SELECT con EXPLAIN

  • MySQL no ejecutará la consulta, solo la analiza
  • EXPLAIN nos ayuda a entender cómo y cuándo MySQL, utilizará índices
  • EXPLAIN devuelve una tabla de datos de la que se identifica mejoras potenciales a Optimizar

  • Las consultas de tres maneras

    1. Modificar o crea índices
    2. Modificar la estructura de la consulta
    3. Modificar la estructura de datos
  • Consultas optimizadas = resultados más rápidos, menor carga del servidor …

4. Introducción – Revisión de la indexación

  • Estructura rápida y compacta para identificar las ubicaciones de las filas
  • Mantenga los índices en la memoria recortando la grasa:
    1. ¿Puedo reducir los caracteres de ese índice VARCHAR?
    2. ¿Puedo usar un TINYINT en lugar de un BIGINT?
    3. ¿Puedo usar un INTEGER para describir un estado o una bandera (más bien que una descripción textual)?
  • Corte el conjunto de resultados tan pronto como sea posible
  • MySQL sólo utilizará un índice por consulta / tabla – no puede combinar dos índices separados para hacer útil su uso*

Comprensión y preparación trae consigo la Estrategia de Indexación

5. Esquema de aplicación de reserva

Asistentes

+--------------+---------+---------------+---------------------+
| attendee_id  | surname | conference_id | registration_status | 
+--------------+---------+---------------+---------------------+
| INTEGER (PK) | VARCHAR |  INTEGER (FK) |      TINYINT        | 
+--------------+---------+---------------+---------------------+

Conferencias

+---------------+--------------+--------------+------+
| conference_id | location_id  | topic_id     | date |
+---------------+--------------+--------------+------+
| INTEGER (PK)  | INTEGER (FK) | INTEGER (FK) | DATE |
+---------------+--------------+--------------+------+

6. EXPLAIN – Ejemplo funcionando

EXPLAIN SELECT * FROM asistentes WHERE conference_id = 123 AND registration_status > 0 

+------------+----------------+------+-------+
| tabla      |  possible_keys | key  | row   |
+------------+----------------+------+-------+
| asistentes |       NULL     | NULL | 14052 |
+------------+----------------+------+-------+

Las tres columnas más importantes devueltas por EXPLAIN

  • Posibles claves
    • Todos los posibles índices que MySQL podría haber utilizado
    • Basado en una serie de búsquedas y cálculos muy rápidos
  • Seleccionada de Clave
  • Filas escaneadas
    • Indicación del esfuerzo requerido para identificar su conjunto de resultados

7. EXPLAIN – Ejemplo funcionando

EXPLAIN SELECT * FROM asistentes WHERE conference_id = 123 AND registration_status > 0 

+------------+----------------+------+-------+
| tabla      |  possible_keys | key  | row   |
+------------+----------------+------+-------+
| asistentes |       NULL     | NULL | 14052 |
+------------+----------------+------+-------+

Interpretación de los resultados

  • No hay índices adecuados para esta consulta
    • MySQL tuvo que hacer una exploración de la tabla completa
  • Escaneo de la tabla completa, su consulta es casi siempre más lenta
  • Las exploraciones de tabla completa, aunque no siempre son malas, suelen ser una indicación de que se requiere un índice

8. EXPLAIN – Ejemplo funcionando

ALTER TABLE ADD INDEX conf (id_conferencia); 

ALTER TABLE ADD INDEX reg (registration_status); 

EXPLAIN SELECT * FROM asistentes WHERE conference_id = 123 AND registration_status > 1 

+------------+----------------+------+-------+
| tabla      |  possible_keys | key  | row   |
+------------+----------------+------+-------+
| asistentes |    conf,reg    | conf | 331   |
+------------+----------------+------+-------+
  • MySQL tiene dos índices para elegir, pero descartado “reg”
  • “reg” no es lo suficientemente único
    • La propagación de valores también puede ser un factor (por ejemplo, cuando el 99% de filas Contienen el mismo valor)
  • Índice “unicidad” se llama cardinalidad
  • Hay margen para algún aumento de rendimiento …
    • Menor carga del servidor, una respuesta más rápida

9. EXPLAIN – Ejemplo funcionando

ALTER TABLE ADD INDEX reg_conf_index (registration_status, conference_id);

EXPLAIN SELECT * FROM asistentes WHERE conference_id = 123 AND registration_status > 1 

+------------+-----------------+----------------+-------+
| tabla      |  possible_keys  | key            | row   |
+------------+-----------------+----------------+-------+
| asistentes |    reg, conf,   | reg_conf_index | 204   |
|            | reg_conf_index  |                |       |
+------------+-----------------+----------------+-------+
  • reg_conf_index es una opción mucho mejor
  • Tenga en cuenta que las otras dos claves están todavía disponibles, sólo no es tan efectivo
  • Nuestra consulta es bien servida por el nuevo índice

10. EXPLAIN – Ejemplo funcionando

DELETE INDEX conf; DELETE INDEX reg; 

EXPLAIN SELECT * FROM asistentes WHERE conference_id = 123

+------------+----------------+------+-------+
| tabla      |  possible_keys | key  | row   |
+------------+----------------+------+-------+
| asistentes |       NULL     | NULL | 14052 |
+------------+----------------+------+-------+
  • Sin el índice “conf”, volvemos al cuadrado uno
  • El orden en el cual los campos fueron definidos en un índice compuesto afecta si está disponible para utilizar en una consulta
    • Recuerde que definimos nuestro índice: (registration_status, conference_id)

Solución potencial:

EXPLAIN SELECT * FROM asistentes WHERE conference_id = 123 AND registration_status> = -1 

+------------+----------------+----------------+-----+
| tabla      |  possible_keys | key            | row |
+------------+----------------+----------------+-----+
| asistentes | reg_conf_index | reg_conf_index | 204 |
+------------+----------------+----------------+-----+

11. EXPLAIN – Ejemplo 2

EXPLAIN SELECT * FROM asistentes WHERE surname LIKE 'har%'; 

+------------+----------------+---------+-----+
| tabla      |  possible_keys | key     | row |
+------------+----------------+---------+-----+
| asistentes |     surname    | surname | 234 |
+------------+----------------+---------+-----+

MySQL utiliza un índice de surname (apellido) – que es bueno.

EXPLAIN SELECT * FROM asistentes WHERE surname LIKE '% har%'; 

+------------+----------------+------+-------+
| tabla      |  possible_keys | key  | row   |
+------------+----------------+------+-------+
| asistentes |       NULL     | NULL | 14052 |
+------------+----------------+------+-------+

¡MySQL ni siquiera intenta usar un índice!

12. EXPLAIN – Ejemplo 3

EXPLAIN SELECT * FROM conferencias WHERE location_id = 2 OR topic_id IN (4,6,1)

+--------------+----------------+------+------+
| tabla        |  possible_keys | key  | row  |
+--------------+----------------+------+------+
| conferencias |  location_id,  | NULL | 5043 |
|              |   topic_id     |      |      |
+--------------+----------------+------+------+

MySQL no utiliza un índice, debido a la OR

ALTER TABLA ADD INDEX location_topic (location_id, topic_id);

EXPLAIN SELECT * FROM conferencias WHERE location_id = 2 OR topic_id IN (4,6,1)

+--------------+----------------+----------------+-----+
| tabla        |  possible_keys | key            | row |
+--------------+----------------+----------------+-----+
| conferencias |  location_id,  | location_topic | 15  |
|              |   topic_id,    |                |     |
|              | location_topic |                |     |
+--------------+----------------+----------------+-----+

Escaneo completo de tablas evitado – también podría utilizar el truco de UNION (ALL)

13. EXPLAIN – Ejemplo 4

EXPLAIN SELECT * FROM asistentes WHERE MD5 (conference_id) = MD5 (123)

+------------+----------------+------+-------+
| tabla      |  possible_keys | key  | row   |
+------------+----------------+------+-------+
| asistentes |       NULL     | NULL | 14052 |
+------------+----------------+------+-------+

Comprensiblemente, MySQL tiene que hacer una exploración completa de la tabla

¿Un ejemplo más realista?

EXPLAIN SELECT * FROM conferencias WHERE DATE_FORMAT (date, '%a') = 'Sat' 

+--------------+----------------+------+------+
| tabla        |  possible_keys | key  | row  |
+--------------+----------------+------+------+
| conferencias |      NULL      | NULL | 5043 |
+--------------+----------------+------+------+

Un buen candidato para la optimización # 3 – Modificar la estructura de datos

14. JOINs

  • Unir grandes conjuntos de datos (> = 100.000) es realmente donde
    EXPLAIN es útil
  • Cada JOIN en una consulta obtiene su propia fila en EXPLAIN
    • Asegúrese de que cada condición JOIN sea ‘rápido’
  • Asegúrese de que cada tabla unida está llegando a su conjunto de resultados tan pronto como sea posible
    • Los beneficios se combinan si cada unión requiere menos esfuerzo

15. JOINs – Ejemplo simple

EXPLAIN SELECT * FROM 
conferencias INNER JOIN asistentes USING (conference_id) 
WHERE conferences.location_id = 2 AND 
conferencias.topic_id IN (4,6,1) AND 
participantes.registration_status > 1 

+--------------+------+------------------+------------------+-------+
| tabla        | type | possible_keys    |        key       | row   |
+--------------+------+------------------+------------------+-------+ 
| conferencias | ref  | conference_topic | conference_topic | 15    | 
+--------------+------+------------------+------------------+-------+
| asistentes   | ALL  |       NULL       |       NULL       | 14052 |
+--------------+------+------------------+------------------+-------+

Parece que necesito un índice en asistentes.conference_id

Hay 13 valores diferentes para “type

16. La columna “extra”

Con cada EXPLAIN, se obtiene una columna “extra”, que muestra las operaciones adicionales invocadas para obtener el conjunto de resultados.

+-----------+---------------+------+-----+----------------+
|tabla      | possible_keys | key  | row | extra          |
+-----------+---------------+------+-----+----------------+
|asistentes | conf          | conf | 331 |  Using where,  |
|           |               |      |     | Using filesort |
+-----------+---------------+------+-----+----------------+

Algunos ejemplos de valores “extra”:

  • Uso de where
  • Uso de tabla temporal
  • Uso de archivo
  • Uso de índice

Existen muchos más valores “extra” que se tratan en el manual de MySQL.

17. “Usando filesort”

Evite, porque:

  • No utiliza un índice

    • Implica una exploración completa de su conjunto de resultados
    • Emplea un algoritmo genérico (es decir, un tamaño para todos)
    • Utiliza el sistema de archivos (eeek)
    • Obtendrá Más lenta con más datos

No todo es malo …

  • Perfectamente aceptable siempre que llegues a tu conjunto de
    resultados lo más rápido posible, y manténgalo predeciblemente
    pequeño
  • A veces inevitable – ORDER BY RAND ()
  • Las operaciones ORDER BY pueden usar índices para hacer ¡clasificación!

18. “Usando filesort” – Ejemplo

EXPLAIN SELECT * FROM asistentes WHERE conference_id = 123 ORDER BY surname

+------------+---------------+---------------+-----+----------------+
| tabla      | possible_keys | key           | row | extra          |
+------------+---------------+---------------+-----+----------------+
| asistentes | conference_id | conference_id | 331 | Using filesort |
+------------+---------------+---------------+-----+----------------+

MySQL utiliza un índice, pero está ordenando los resultados lentamente

ALTER TABLE asistentes ADD INDEX Conf_surname (conference_id, surname);

EXPLAIN SELECT * FROM asistentes WHERE conference_id = 123 ORDER BY surname

+------------+----------------+--------------+-----+-------+
| tabla      | possible_keys  | key          | row | extra |
+------------+----------------+--------------+-----+-------+
| asistentes | conference_id, | conf_surname | 331 |       |
|            | conf_surname   |              |     |       |                                         
+------------+----------------+--------------+-----+-------+

Hemos evitado un filesort (archivo de archivos)

19. “Utilizando el índice (INDEX)”

Celebre, porque:

  • MySQL obtuvo sus resultados sólo consultando el índice,
    • Que bien podría haber estado sentado en la memoria
  • MySQL no necesita ni siquiera mirar a la mesa para obtener sus resultados
    • Abrir una mesa puede ser una operación costosa.
  • MySQL puede responder a la siguiente consulta más rápidamente
    • ¿La forma más rápida de obtener sus datos?

Particularmente útil …

  • Cuando sólo está interesado en una sola fecha o un ID
  • El COUNT (), SUM (), AVG () etc. de un campo

20. “Usar el índice (INDEX)” – Ejemplo

EXPLAIN SELECT AVG (age) FROM asistentes WHERE conference_id = 123

+------------+---------------+---------------+-----+-------+
| tabla      | possible_keys | key           | row | extra |
+------------+---------------+---------------+-----+-------+
| asistentes | conference_id | conference_id | 331 |       |
+------------+---------------+---------------+-----+-------+

¡Nada es realmente incorrecto con esta consulta – podría ser más rápido!

ALTER TABLE asistentes ADD INDEX conf_age (conference_id, age);

EXPLAIN SELECT AVG (age) FROM asistentes WHERE conference_id = 123

+------------+----------------+--------------+-----+-------------+
| tabla      | possible_keys  | key          | row | extra       |
+------------+----------------+--------------+-----+-------------+
| asistentes | conference_id, | conf_surname | 331 | Using index |
|            | conf_surname   |              |     |             |                                         
+------------+----------------+--------------+-----+-------------+

Fuera del almacenamiento en caché, la manera más rápida de obtener sus datos (No es una garantía)

21. Avanzando …

Simplemente porque tus consultas son rápidas ahora, no significa que se mantendrán de esa manera para siempre

Habilitar el registro de consultas lentas de MySQL

  • –log-slow-queries = / var / lib / mysql / slow -query.log
  • Predeterminado a las consultas de registro que tardan más de 10 segundos
  • –long_query_time = 1
  • Utilizar el parche de “microslow” de Percona para los valores <1 segundo
  • Buscar la consulta en el registro, EXPLAIN, mejórela, enjuague y repita

22. Avanzando …

Utilice la línea de comandos para identificar problemas más generales

  • mysqladmin -u dbuser -p -r -i 10 extended-status
  • Las figuras son relativas, actualizadas cada 10 segundos
    • Slow_queries = número de consultas lentas en Último período
    • Select_Scan = exploraciones de tabla completa
    • Select_full_join = exploraciones completas para completar operaciones de unión
    • Created_tmp_disk_tables = filesorts
    • Key_read_requests / Key_write_requests
      • Determinar la ponderación de escritura / lectura de nuestra aplicación y modificar sus índices en consecuencia
Respondido por: Anonymous

Leave a Reply

Your email address will not be published. Required fields are marked *