¡Qué passaaa neeeng! Hoy vamos a ver un poco de 'tunning' de consultas SQL para mejorar la velocidad de respuesta de MySQL (y posiblemente otros SGBD basados en SQL) en la obtención de datos. Como no podía ser de otro modo, este post va dedicado al sensei Row :P

Para entrar en materia y ejemplificar las cosas de modo que quien quiera hacer las pruebas y observar los resultados pueda hacerlo, vamos a trabjar con una sencilla BD que pongo a vuestra disposición. Como figura en el gráfico, sólo consta de 3 tablas:

Como se trata de tablas de tipo MYISAM, las relaciones entre tablas (las FK's, Foreign Keys o llaves foraneas) las llevamos por nuestra cuenta. Esto significa que MySQL no se preocupa de ellas, y tendremos que ser nosotros (a nivel de MySQL o de nuestra aplicación) quien lo gestione al trabajar con la Base de Datos. Esto no sería así si usáramos tablas de tipo InnoDB, puesto que éstas contemplan FK's nativamente, pero en este ejemplo no las usaremos porque las diversas features que las distinguen de MYISAM pueden suponer un penalty en el rendimiento.

Descripción de las tablas

Como podemos ver, la cosa es bastante simple. Existen 3 tablas: - clientes; contiene información personal básica de clientela, como nombre, apellidos, si es un conductor novel o no y la edad. - vehículo; contiene tipos de vehículos clasificados por grupos, numero de ruedas y un baremo de peso. - clientes_vehiculo; es una tabla puramente de relación entre ambas tablas. Por ello, tiene 2 FK's: id_cliente, que refiere a clientes.id, y id_vehiculo, que hace lo propio con vehiculo.id.

Test time

Vamos a hallar el nombre de los clientes que usan motocicleta. Haremos varios queries (aka consultas) distintos para observar su rendimiento a través del tiempo de respuesta de MySQL.

1. Queries sueltos...

SELECT id FROM vehiculo WHERE clase = 'motocicleta'; SELECT id_cliente FROM clientes_vehiculo WHERE id_vehiculo = el_anterior_id_obtenido; SELECT nombre FROM clientes WHERE id = el_anterior_id_obtenido;
Tiempo total: 0.0012 seg + 0.0012 seg + 0.0012 seg = 0.0035 seg

2. Subselects/Subconsultas...

SELECT nombre FROM clientes WHERE id IN (SELECT id_cliente FROM clientes_vehiculo WHERE id_vehiculo IN (SELECT id FROM vehiculo WHERE clase='motocicleta'))
Tiempo total: 0.3509 seg (100 veces más lenta que la anterior!)

3. Dos queries a base de AND's con distinta ordenación...

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;
Tiempo total: 0.0020 seg

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';
Tiempo total: 0.0018 seg

En resumen, queda claro que el tipo de consultas más rápidas son las que se realizan a base de AND's estableciendo las relaciones entre tablas (esto es, las últimas, que consiguen rebajar casi a la mitad de tiempo los resultados de los queries sueltos). Los SUBSELECTS son harto más lentos que realizar las consultas por separado, porque para cada uno de ellos recorremos enteras cada una de las tablas a las que se hacen referencia en el query.

Test time 2

Ahora añadamos algo más a lo que teníamos. Busquemos de nuevo el nombre de clientes que usan motocicleta y (aquí la nueva condición) que son menores de 16 años.

1. Queries sueltos...
SELECT id FROM vehiculo WHERE clase = 'motocicleta'; SELECT id_cliente FROM clientes_vehiculo WHERE id_vehiculo = el_anterior; SELECT nombre FROM clientes WHERE id = el_anterior AND edad < 16;
Tiempo total: 0.0012 seg + 0.0011 seg + 0.0006 seg = 0.0029 seg
2. Subselects/Subconsultas...

SELECT nombre FROM clientes WHERE id IN (SELECT id_cliente FROM clientes_vehiculo WHERE id_vehiculo IN (SELECT id FROM vehiculo WHERE clase='motocicleta')) AND edad < 16
Tiempo total: 0.3687 seg

SELECT nombre FROM clientes WHERE edad < 16 AND id IN (SELECT id_cliente FROM clientes_vehiculo WHERE id_vehiculo IN (SELECT id FROM vehiculo WHERE clase='motocicleta'))
Tiempo total: 0.0041 seg (impresionante descenso por el primer filtro)

3. Dos queries a base de AND's con distinta ordenación...

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 AND c.edad < 16;
Tiempo total: 0.0018 seg

SELECT c.nombre FROM clientes c, clientes_vehiculo cv, vehiculo v WHERE c.edad < 16 AND c.id = cv.id_cliente AND cv.id_vehiculo = v.id AND v.clase = 'motocicleta';
Tiempo total: 0.0018 seg

En este segundo round destacan un par de cosas. La primera, que los queries sueltos son más rápidos que los que ejecutamos en el primer round aún y teniendo más filtros. Precisamente, este nuevo filtro es el que ayuda a discriminar más entradas. Y en segundo lugar, el mismo filtro de edad situado al principio en la segunda consulta SUBSELECT reduce drásticamente el tiempo de respuesta por el mismo motivo. Aún y así, en general no es recomendable usar SUBSELECTS por causa del bajo rendimiento que ofrecen.

Conclusiones y consideraciones finales Primero de todo, hemos visto que los SUBSELECTS are evil. Segundo, que las consultas en base a relaciones ofrecen muy buen rendimiento. Y tercero y último, que el orden de los filtros es muy importante y determinante en la velocidad de las consultas que diseñemos.

Se debe tomar en consideración que para la ejecución de cada query (excepto las que forman parte de un mismo grupo, como en los casos de las queries sueltas), se ha reiniciado MySQL para desestimar cualquier caché que pudiera ayudar a la mejora de resultados.


Comentarios (1)