MariaDB / Explain

Bienvenidos sean a este post, hoy veremos como utilizar el comando EXPLAIN.

Este comando es la herramienta fundamental para entender como trabaja un comando en el servidor, dado que a partir de la version 10 de mariadb no solo trabaja con SELECT sino tambien con UPDATE y DELETE, su sintaxis es la siguiente:

EXPLAIN [EXTENDED] comando;

Su sintaxis es muy simple, primero va el EXPLAIN luego de manera opcional EXTENDED, este nos agrega una nueva columna a la salida y tambien genera una nota que se puede visualizar con SHOW WARNINGS de la cual hablaremos luego, y por ultimo el comando que es el query que utilizamos habitualmente, pero en mariadb 10 tambien se agrego como opcion del comando SHOW:

SHOW EXPLAIN FOR <id_thread>;

Este comando nos permite obtener un plan de ejecucion de un comando que este corriendo, nos resultara especialmente util para comandos que tardan demasiado en correr, para saber cuales son los comandos que estan corriendos y sus ids de threads usaremos a SHOW PROCESSLIST, con esto comentado veamos algunos ejemplos:

MariaDB [curso]> explain select * from alumnos\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: alumnos
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
        Extra: 
1 row in set (0.001 sec)

MariaDB [curso]>

Esta es su forma mas basica donde simplemente lo corremos con un query, mas adelante explicaremos que hace cada campo que nos devuelve, volvamos a ejecutar el mismo query pero con el EXTENDED:

MariaDB [curso]> explain extended select * from alumnos\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: alumnos
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: 
1 row in set, 1 warning (0.001 sec)

MariaDB [curso]>

Observen que solo nos agrego una nueva linea (filtered) y ahora tenemos un aviso, veamos el aviso:

MariaDB [curso]> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `curso`.`alumnos`.`id` AS `id`,`curso`.`alumnos`.`usuario` AS `usuario`,`curso`.`alumnos`.`nombre` AS `nombre`,`curso`.`alumnos`.`apellido` AS `apellido`,`curso`.`alumnos`.`edad` AS `edad`,`curso`.`alumnos`.`ciudad` AS `ciudad` from `curso`.`alumnos`
1 row in set (0.000 sec)

MariaDB [curso]>

Como dijimos antes es simplemente una nota para clarificar un poco mas lo realizado en el query, les paso un ejemplo de como es trabajar con el SHOW PROCESSLIST y SHOW EXPLAIN::

MariaDB [(none)]> SHOW PROCESSLIST \G
*************************** 1. row ***************************
      Id: 12
    User: root
    Host: localhost
      db: NULL
 Command: Query
    Time: 0
   State: Table lock
    Info: SHOW PROCESSLIST
Progress: 0.000
*************************** 2. row ***************************
      Id: 37
    User: root
    Host: localhost
      db: test
 Command: Query
    Time: 1
   State: Sending data
    Info: SELECT a, b FROM t WHERE a = 1 ORDER BY a DESC
Progress: 0.000
3 rows in set (0.00 sec)

MariaDB [(none)]> SHOW EXPLAIN FOR 37 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: ref
possible_keys: idx_a
          key: idx_a
      key_len: 5
          ref: const
         rows: 1165569
        Extra:
1 row in set, 1 warning (0.00 sec)

MariaDB [(none)]>

Nota: 
Les muestro un ejemplo de esta manera porque no pude capturar uno correctamente

Con esto comentado y algunos ejemplos ejecutados veamos que hace cada columna que nos devuelve:

• id, define un identificador unico para cada linea
• select_type, informa el tipo de SELECT ejecutado
• table, informa cual es la tabla donde se ejecuto el comando
• type, define el tipo de JOIN que ejecuta
• possible_keys, indica las posibles claves que pueden usarse para ejecutar comandos
• key, define cual es la clave que el optimizador usara
• key_len, define el tamaño de la clave seleccionada, en bytes
• ref, nos indica cual es la columna usada para unir (JOIN) dos tablas
• rows, nos da un estimado de cuantas lineas se examinaran
• filtered, nos informa un porcentaje de cuantas lineas no seran devueltas
• Extra, esta define informacion adicional
• partitions, define una lista de particiones que seran accedidas

La mayoria de las columnas nos apareceran de manera predeterminada, salvo partitions que aparecera en el caso de que nuestra tabla este particionada y filtered aparecera si utilizamos la opcion EXTENDED, ya vimos como seria un ejemplo de trabajar con un query simple donde nos devuelve la informacion pero que sucede cuando ejecutamos un query donde se consultan dos tablas:

MariaDB [base1]> explain extended
    -> select tabla3.nombre, tabla3.apellido, tabla3.correo
    -> from tabla1, tabla3 where tabla3.nombre = tabla1.nombre
    -> order by tabla3.id asc\G

En este query le pido que me devuelva el valor de las columnas nombre, apellido y correo de la tabla3, luego pasamos las tablas que usaremos, para finalmente agregar un condicional donde filtra la informacion en base a la coincidencia del valor de la columna nombre en la tabla3 y el de columna nombre de la tabla1 y todo ordenado por la columna id de la tabla3, veamos como es su salida:

MariaDB [base1]> explain extended
    -> select tabla3.nombre, tabla3.apellido, tabla3.correo
    -> from tabla1, tabla3 where tabla3.nombre = tabla1.nombre
    -> order by tabla3.id asc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tabla1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: tabla3
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using where; Using join buffer (flat, BNL join)
2 rows in set, 1 warning (0.002 sec)

MariaDB [base1]>

Observen como ahora nos devolvio dos lineas, una para cada tabla, con sus respectivas caracteristicas pero ahora en Extra tenemos una breve descripcion de lo realizado en cada tabla, en la tabla1 nos menciona que utiliza temporary y filesort vamos a hablar un poco de ellos.

En este caso como en otros mariadb algunas veces necesita crear una tabla temporal interna para ejecutar un query, esto es necesario si los datos deben ser leidos mas de una vez dado que copiar la informacion en una tabla temporal nos permite liberar el bloqueo de la tabla una vez terminada la operacion de copia, aunque si la informacion es demasiado grande mariadb evitara usar esto, otras posibles causas del uso de tablas o archivos temporales son las siguientes:

• Vistas que agregan datos o estan definidas con el algoritmo TEMPTABLE
• Para operaciones de UNION
• Si las opciones ORDER BY y GROUP BY estan especificadas y no son iguales
• Si la opcion JOIN es usada y las opciones ORDER BY o GROUP BY contienen columnas que no estan en la primera tabla leida
• Si DISTINCT y ORDER BY estan presentes en el mismo query
• Cuando se necesita materializar un sub-query o una tabla derivada

Como vimos en el ultimo query siempre en la columna Extra veremos una mencion de cuando se utilizo una tabla temporal, como fue nuestro caso, para ir finalizando estas tablas temporales por lo general se almacenan en memoria pero si exceden el tamaño maximo permitido se las crea en disco, las circunstancias por las que se hacen en disco son:

• Cuando una columna de TEXT o BLOB es leida
• Cuando un GROUP BY o DISTINCT contiene una columna mas larga de 512 bytes (sin caracteres)
• Lo anterior aplica a UNION DISTINCT
• Para las operaciones UNION ALL

En resumen, hoy hemos visto a EXPLAIN, como es, para que sirve, como nos ayuda, cual informacion nos puede ser util y cual debemos evitar, tambien hemos visto como trabaja internamente, espero les haya sido util sigueme en tumblr, Twitter o Facebook para recibir una notificacion cada vez que subo un nuevo post en este blog, nos vemos en el proximo post.