enchufado
   RSS
#
MySQL Query Performance Tunning (parte II) (Bases de Datos) 2007-06-30 10:32:19

Vamos a echar un vistazo a la información que nos da el propio MySQL acerca de las consultas que generamos en el anterior post (es conveniente leerlo para una mejor comprensión, aunque no imprescindible). Esto lo llevamos a cabo gracias a la sentencia EXPLAIN.

Su uso es tan simple como poner esa cadena (EXPLAIN) justo al principio de cualquier consulta, y ejecutarla. En lugar de ejecutar la sentencia y mostrarnos los resultados, la salida nos dará una información bastante aproximada de cómo MySQL va a tratar y a ejecutar una sentencia SELECT dada.

Con esto, la tarea de optimización de consultas se facilita, pudiendo ver si necesitamos crear algún índice para aligerar cierto tipo de SELECTS, si no se está usando un índice que creíamos que si, si estamos haciendo las relaciones entre tablas de un modo correcto, etc...

Antes de ponerlo a prueba con los queries del anterior post, hagamos una breve descripción de lo que MySQL nos va a mostrar:

id select_type table type possible_keys key key_len ref rows Extra
  1. id: Es el identificador secuencial del SELECT (no SELECT en sentido estricto; cualquier otra operación de relación de tablas es tomado como tal). Si tenemos varios, se pondran en el orden en que los pusimos.
  2. select_type: El tipo de SELECT según lo entiende MySQL.
  3. table: La tabla que involucra esta operación de SELECT.
  4. type: El tipo de relación (join). El peor de los casos es ALL, significando que la búsqueda se realiza sobre la tabla completa. El escalafón, de mejor a peor, es: system, const, eq_ref, ref, ref_or_null, index_merge, unique_subquery, index_subquery, range, index y ALL.
  5. possible_keys: Indica los índices entre los cuales MySQL puede elegir para realizar la consulta.
  6. key: Indica el índice que MySQL finalmente usaría al ejecutar la query.
  7. key_len: Indica el tamaño de la clave que MySQL decide usar.
  8. ref: Muestra la columna que se compararía con el índice elegido en key para hacer la selección de filas de la tabla.
  9. rows: Supone el número de filas que MySQL cree que debe examinar para ejecutar el query. Obviamente, cuanto mejor sea nuestro filtrado, menos filas deben salir, lo cual redundará en una mejora en el desempeño de ejecución de la query que estemos diseñando/optimizando.
  10. Extra: Información adicional sobre cómo MySQL resuelve la query.

Y ahora los queries con EXPLAIN explicados (válgase la rebuznancia):

Test time (y Test time 2, puesto que la salida de EXPLAIN es la misma)

  1. Queries sueltos...
  2. EXPLAIN SELECT id FROM vehiculo WHERE clase = 'motocicleta';
    id select_type table type possible_keys key key_len ref rows Extra
    1 SIMPLE vehiculo ALL NULL NULL NULL NULL 10 Using where

    Aquí tenemos un SELECT simple (sin unions ni subselects) sobre la tabla vehiculo que buscará sobre todas las entradas. Efectivamente, en la tabla vehiculo solo hay 10 filas, y no se usará ningún índice porque el campo 'clase' no dispone de ningún tipo de indexación.

    EXPLAIN SELECT id_cliente FROM clientes_vehiculo WHERE id_vehiculo = el_anterior_id_obtenido;
    id select_type table type possible_keys key key_len ref rows Extra
    1 SIMPLE clientes_vehiculo ALL NULL NULL NULL NULL 11 Using where

    Estamos en el mismo caso de antes, sólo que esta vez la tabla clientes_vehiculo tiene 11 entradas.

    EXPLAIN SELECT nombre FROM clientes WHERE id = el_anterior_id_obtenido;
    id select_type table type possible_keys key key_len ref rows Extra
    1 SIMPLE clientes const PRIMARY PRIMARY 4 const 1

    Esta vez encontramos que el tipo de join/relación es const, que significa que como mucho la tabla tiene una sola coincidencia con una fila. Se da porque comparamos una constante con la PK de la tabla. Esto se traduce en que la query será resuelta con gran rapidez. Vemos también que la PK consta como posible índice a usar, cosa que acaba pasando. Finalmente confirmamos la causa de la rapidez; MySQL ve que sólo es necesario analizar una fila (de las aprox. 5000 que tiene la tabla!) para ejecutar la query.

  3. Subselects/Subconsultas...
  4. EXPLAIN SELECT nombre FROM clientes WHERE id IN (SELECT id_cliente FROM clientes_vehiculo WHERE id_vehiculo IN (SELECT id FROM vehiculo WHERE clase='motocicleta'))
    id select_type table type possible_keys key key_len ref rows Extra
    1 PRIMARY clientes ALL NULL NULL NULL NULL 5017 Using where
    2 DEPENDENT SUBQUERY clientes_vehiculo ALL NULL NULL NULL NULL 11 Using where
    3 DEPENDENT SUBQUERY vehiculo UNIQUE SUBQUERY PRIMARY PRIMARY 4 func 1 Using index; Using where

    Lo interesante y a la vez aterrador de este query es que el primer SELECT tiene que recorrer todas las filas de su tabla (clientes). El primer SELECT depende del segundo (DEPENDENT SUBQUERY), y asimismo también debe recorrer todas las filas de su tabla (clientes_vehiculo). Finalmente, el segundo SELECT también depende del tercero (DEPENDENT SUBQUERY), aunque esta vez tenemos un interesante UNIQUE SUBQUERY, es decir, un reemplazo del subquery por el índice para un mejor desempeño.

  5. Dos queries a base de AND's con distinta ordenación...
  6. EXPLAIN SELECT c.nombre FROM clientes c, clientes_vehiculo cv, vehiculo v WHERE v.clase = 'motocicleta' AND v.id = cv.id_vehiculo AND cv.id_cliente = c.id;
    id select_type table type possible_keys key key_len ref rows Extra
    1 SIMPLE cv ALL NULL NULL NULL NULL 11
    1 SIMPLE v eq_ref PRIMARY PRIMARY 4 autoecho.cv.id_vehiculo 1 Using where
    1 SIMPLE c eq_ref PRIMARY PRIMARY 4 autoecho.cv.id_cliente 1

    ¡Oh! ¿Qué pasó aquí? Para MySQL tenemos 3 SELECTS simples, como en el primer caso, con la diferencia de que ahora iteraremos a través de menos filas (13 en lugar de 22)... ¡Fantástico! En realidad sólo iteramos entera la tabla clientes_vehiculo (cv). Para las 2 últimos SELECTS vemos que el tipo de relación es eq_ref: se da cuando el índice es usado para la relación/join y además es PK o UNIQUE.

    EXPLAIN SELECT c.nombre FROM clientes c, clientes_vehiculo cv, vehiculo v WHERE c.id = cv.id_cliente AND cv.id_vehiculo = v.id AND v.clase = 'motocicleta';

    Para esta segunda query no hace falta poner la tabla resultante, puesto que es igual que la anterior. En este caso, el cambio de orden de los factores no altera el producto.


    Comentarios (5)


Volver al indice

login, admin, form, register