Ajusta tus consultas MySQL como un profesional
Optimizar sus consultas no es un arte oscuro; es simplemente ingeniería simple.
Mucha gente considera que ajustar las consultas a bases de datos es un misterioso "arte oscuro" sacado de una novela de Harry Potter; con el encantamiento incorrecto, sus datos pasan de ser un recurso valioso a convertirse en un montón de papilla.
En realidad, ajustar las consultas para un sistema de base de datos relacional es una ingeniería simple y sigue reglas o heurísticas fáciles de entender. El optimizador de consultas traduce la consulta que envía a una instancia de MySQL y luego determina la mejor manera de obtener los datos solicitados utilizando esas heurísticas combinadas con lo que sabe sobre sus datos. Vuelva a leer la última parte: "lo que sabe sobre sus datos". Cuanto menos tenga que adivinar el optimizador de consultas sobre dónde se encuentran sus datos, mejor podrá crear un plan para entregarlos.
Para brindarle al optimizador una mejor visión de los datos, puede utilizar índices e histogramas. Si se usan correctamente, pueden aumentar considerablemente la velocidad de una consulta a una base de datos. Si sigues la receta, obtendrás algo que te gustará. Pero si agregas tus propios ingredientes a esa receta, es posible que no obtengas lo que deseas.
Optimizador basado en costos
La mayoría de las bases de datos relacionales modernas utilizan un optimizador basado en costos para determinar cómo recuperar los datos de la base de datos. Ese coste se basa en reducir al máximo las lecturas de discos muy costosas. El código del optimizador de consultas dentro del servidor de la base de datos mantiene estadísticas sobre la obtención de esos datos a medida que se encuentran y crea un modelo histórico de lo que se necesitó para obtener los datos.
Pero los datos históricos pueden estar desactualizados. Es como ir a la tienda a comprar tu snack favorito y sorprenderte por un aumento repentino de precio o que la tienda haya cerrado. El proceso de optimización de su servidor puede hacer una mala suposición basada en información antigua, y eso producirá un plan de consulta deficiente.
La complejidad de una consulta puede ir en contra de la optimización. El optimizador quiere ofrecer la consulta de menor costo de las opciones disponibles. Unir cinco tablas diferentes significa que hay cinco factores o 120 combinaciones posibles sobre cuál unir y qué. La heurística está integrada en el código para intentar atajar la evaluación de todas las opciones posibles. MySQL quiere generar un nuevo plan de consulta cada vez que ve una consulta, mientras que otras bases de datos como Oracle pueden tener un plan de consulta bloqueado. Por eso es vital brindar información detallada sobre sus datos al optimizador. Para lograr un rendimiento constante, es realmente útil tener información actualizada para que el optimizador de consultas la utilice al realizar planes de consultas.
Además, las reglas están integradas en el optimizador con suposiciones que probablemente no coincidan con la realidad de sus datos. El optimizador de consultas asumirá que todos los datos de una columna están distribuidos uniformemente entre todas las filas, a menos que tenga otra información. Y utilizará de forma predeterminada el menor de dos índices posibles si no ve otra alternativa. Si bien el modelo basado en costos para un optimizador puede tomar muchas buenas decisiones, puede encontrarse con casos en los que no obtendrá un plan de consulta óptimo.
¿Un plan de consulta?
Un plan de consulta es lo que el optimizador generará para que el servidor lo ejecute a partir de la consulta. La forma de ver el plan de consulta es anteponer la palabra EXPLAIN
a su consulta. Por ejemplo, la siguiente consulta solicita el nombre de una ciudad de la tabla de ciudades y el nombre de la tabla de países correspondiente, y las dos tablas están vinculadas por el código único del país. Este caso sólo interesa a las cinco ciudades principales alfabéticamente del Reino Unido:
SELECT city.name as 'City',
country.name as 'Country'
FROM city
JOIN country ON (city.countrycode = country.code)
WHERE country.code = 'GBR'
LIMIT 5;
Anteponer EXPLAIN
delante de esta consulta proporcionará el plan de consulta generado por el optimizador. Saltando todo el resultado excepto el final, es fácil ver la consulta optimizada:
select `world`.`city`.`Name` AS `City`,
'United Kingdom' AS `Country`
from `world`.`city`
join `world`.`country`
where (`world`.`city`.`CountryCode` = 'GBR')
limit 5;
Los grandes cambios son que country.name como 'País'
se cambió a 'Reino Unido' COMO 'País'
y la cláusula WHERE
pasó de mirando en la tabla de países a la tabla de ciudades. El optimizador determinó que estos dos cambios proporcionarán un resultado más rápido que la consulta original.
Índices
Escuchará índices y claves usados indistintamente en MySQL-verse. Sin embargo, los índices se componen de claves y las claves son una forma de identificar un registro, con suerte de forma única. Si una columna está diseñada como una clave, el optimizador puede buscar en una lista de esas claves para encontrar el registro deseado sin tener que leer la tabla completa. Sin un índice, el servidor tiene que comenzar en la primera fila de la primera columna y leer cada fila de datos. Si la columna se creó como un índice único, entonces el servidor puede ir a esa fila de datos e ignorar el resto. Cuanto más exclusivo sea el valor del índice (también conocido como cardinalidad), mejor. Recuerde, buscamos formas más rápidas de acceder a los datos.
El motor de almacenamiento InnoDB predeterminado de MySQL quiere que su tabla tenga una clave principal y almacenará sus datos en un árbol B+ con esa clave. Una característica de MySQL agregada recientemente son las columnas invisibles: columnas que no devuelven datos a menos que la columna se nombre explícitamente en la consulta. Por ejemplo, SELECT * FROM foo;
no proporciona ninguna columna designada como oculta. Esta función proporciona una manera de agregar una clave principal a tablas más antiguas sin tener que volver a codificar todas las consultas para incluir esa nueva columna.
Para complicar aún más esto, existen muchos tipos de índices, como el funcional, el espacial y el compuesto. Incluso hay casos en los que puedes crear un índice que proporcionará toda la información solicitada para una consulta de modo que no sea necesario acceder a la tabla de datos.
Describir los distintos índices está fuera del alcance de este artículo, así que piense en un índice como un acceso directo al registro o registros que desee. Puede crear un índice en una o más columnas o parte de esas columnas. El sistema de mi médico puede buscar mis registros por las primeras tres letras de mi apellido y fecha de nacimiento. El uso de varias columnas requiere utilizar primero el campo más exclusivo, luego el segundo más exclusivo, y así sucesivamente. Un índice de año-mes-día funciona para búsquedas de año-mes-día, año-mes y año, pero no funciona para búsquedas de día, mes-día o año-día. Ayuda a diseñar sus índices en función de cómo desea utilizar sus datos.
Histogramas
Un histograma es una distribución de sus datos. Si estuviera alfabetizando a las personas por su apellido, podría usar un "grupo lógico" para las personas con apellidos que comienzan con las letras de la A a la F, luego otro para las de la G a la J, y así sucesivamente. El optimizador supone que los datos están distribuidos uniformemente dentro de la columna, pero esto rara vez es así en el uso práctico.
MySQL proporciona dos tipos de histogramas: de igual altura, donde todos los datos se dividen equitativamente entre los depósitos, y singleton, donde hay un único valor en un depósito. Puede tener hasta 1024 depósitos. La cantidad de depósitos a elegir para su columna de datos depende de muchos factores, incluido el número de valores distintos que tiene, el grado de sesgo que tienen los datos y el nivel de precisión que realmente debe tener. Después de una cierta cantidad de depósitos, hay rendimientos decrecientes.
Este comando creará un histograma de 10 depósitos en la columna c1 de la tabla t:
ANALYZE TABLE t UPDATE HISTOGRAM ON c1 WITH 10 BUCKETS;
Imagine que vende calcetines pequeños, medianos y grandes y que cada talla tiene su propio contenedor para guardarlos. Para encontrar la talla que necesitas, vas a la papelera de esa talla. MySQL ha tenido histogramas desde que se lanzó MySQL 8.0 hace tres años, pero no son tan conocidos como los índices. A diferencia de los índices, no hay gastos generales para insertar, actualizar o eliminar un registro. Para actualizar un índice, se debe actualizar un comando ANALYZE TABLE
. Este es un buen enfoque cuando los datos no se agitan mucho y los cambios frecuentes en los datos reducirán la eficiencia.
¿Índices o histogramas?
Utilice índices para elementos únicos donde necesite acceder a los datos directamente. Hay gastos generales para actualizaciones, eliminaciones e inserciones, pero obtienes un acceso rápido si tus datos tienen la arquitectura adecuada. Utilice histogramas para datos que no se actualizan con frecuencia, como los resultados trimestrales de los últimos doce años.
Pensamientos de despedida
Este artículo surgió de una presentación reciente en la conferencia Open Source 101. Y esa presentación surgió de un taller en una conferencia PHP en el Reino Unido. El ajuste de consultas es un tema complejo y cada vez que presento en índices e histogramas, encuentro formas de perfeccionar mi presentación. Pero cada presentación también muestra que muchas personas en el mundo del software no conocen bien los índices y tienden a usarlos incorrectamente. Los histogramas no han existido durante el tiempo suficiente (espero) como para haber sido utilizados indebidamente de manera similar.