Cuando una consulta, del tipo que sea (INSERT, SELECT, GRANT, …) es enviada a MySQL se ejecuta un procedimiento que consta de cuatro pasos: la caché de consultas, el parseado, la planificación y la ejecución.

1.- La caché de consultas: Cuando se recibe una consulta de tipo SELECT se ejecuta un algoritmo de hash y comprueba si tiene alguna consulta idéntica almacenada, en caso afirmativo devolverá el resultado cacheado. La caché de consultas es muy útil en tablas que no cambian frecuentemente y donde se realizan muchas peticiones idénticas, suele ser el caso de páginas web dinámicas que muestran el resultado almacenado en base de datos. Se activa con la directiva query_cache_type en el fiche my.cnf (OFF o 0 desactiva la caché, ON o 1 activa excepto cuando se usa SQL_NO_CACHE y 2 solo guarda resultados en caché cuando se indica expresamente con SQL_CACHE).
2.- Parseado: Si la consulta no se encuentra en caché se parsea, comprobando la sintaxis de la consulta y dividiéndola en sus elementos básicos. Se identifica el tipo de consulta, las tablas que están involucradas y se gestiona la cláusula WHERE para su ejecución.
3.- Planificación: Una identificados todos los elementos que intervendrán en la consulta llega el momento de decidir como será ejecutada. Con EXPLAIN podemos ver el plan que seguirá MySQL para resolver una consulta, lo que nos ayudará a modificar las intenciones de resolución escogiendo la que más nos convenga.
4.- Ejecución: Con todo decidido resta ejecutar la consulta y devolver el resultado.

Y hasta aquí todo el misterio que encierra la ejecución de una consulta sobre MySQL. El modo en el que ayudamos a que MySQL resuelva eficazmente las consultas realizadas determina en la mayoría de los casos el resultado final de rendimiento de una base de datos.

Habitualmente la mayor parte de las bases de datos están mal aprovechas, bien porque han crecido más de lo planteado o por un mal diseño del almacenamiento. Muchas veces se suele intentar solucionar a golpe de mejora de hardware, cuando en realidad una buena optimización ayudando a MySQL a ejecutar los procesos de consulta suele repercutir en una mejora mucho mayor que ofrecerle un hardware más potente.

Como segunda parte de la optimización de MySQL podemos mejorar las consultas que realicemos sobre la base de datos. A pesar de ser una tarea tediosa, de vez en cuando es conveniente realizar una revisión de las consultas, comprobando que siguen siendo igual de eficaces que cuando las planteamos.

• Como primera recomendación al hacer una consulta a una tabla es no usar “*” en el SELECT. ¿Si no se van a usar todos y cada uno de los campos definidos en esa tabla que sentido tiene que usemos toda la información? Ninguno, pues eso, definir la consulta con SELECT campo1, campo2, … FROM dará mejor resultado
• Probar la consulta que vamos a implementar con EXPLAIN y analizar el resultado. Intentaremos que las consultas que realicemos no contengan ALL en la columna type, puesto que en ese caso se hará un escaneo completo de la tabla.
• Los campos utilizados con la cláusula WHERE son buenos candidatos a ser índices, comprueba con EXPLAIN como los usaría MySQL para determinar si está aconsejado su uso.
• Evitar el uso innecesario de LIKE y RLIKE, pues realizar comparaciones extensas llevará mucho más tiempo. Si se usan tablas MyISAM pueden utilizarse índices FULLTEXT sobre los campos de texto donde se realice la comparación.
• Al insertar registros, resulta más eficiente la escritura de varias sentencias la mismo tiempo que varias por sepado. Con INSERT DELAYED puedes generar una cola que esperará hasta que la tabla no esté siendo utlizada (No puede usarse con todos los motores). Utilizando INSERT LOW_PRIORITY darás prioridad a las sentencias de escritura y con SELECT HIGH_PRIORITY darás prioridad a las sentencias de consulta.
• Cuando la cantidad de resultados a devolver por MySQL no va a ser utilizada en su conjunto, como cuando se usa un paginador, devolver la cantidad apropiada utilizando la cláusula LIMIT.

Ante todo hay que mantener una premisa básica: no guardar datos innecesarios y no extraer información que no se va a utilizar.

Mantener una base de datos optimizada es una de las claves para lograr una mejora importante en el rendimiento, tanto a nivel de base de datos como una mejora significativa en el rendimiento de las aplicaciones que hacen uso de ellas. Aunque voy a enfocarlo en MySQL, podría aplicarse a la mayoría de gestores de base de datos.

Podríamos centrarnos en tres escenarios a la hora de acometer una optimización de MySQL: la optimización de la base de datos, la mejora de las consultas que se realizan sobre ella y por último, pero no menos importante, el pulido de los programas que trabajan con los datos. Hoy vamos a centrarnos en la primera parte.

Optimizar la base de datos

• El primer punto a tener en cuenta será el diseño de la base de datos en el momento de su creación. No hay mejor remedio que hacer bien las cosas desde un principio. Habrá que pararse a calcular la carga que va a soportar MySQL, tanto a nivel de accesos como la cantidad de datos de habrá que almacenar. También es importante pensar como se extraerán los datos a la hora de hacer las tablas y que relaciones mantendrán esos datos con otras tablas y campos.
• Para el uso de cada tipo de campo sobre nuestras tablas debemos buscar siempre los más adecuados al tipo de dato que tendremos que almacenar. Un campo incorrecto forzará a trabajar más de lo necesario a nuestro motor de base de datos. Debemos evitar el uso de campos de longitud variable si no los necesitamos, intentaremos usar campos numéricos en lugar de campos de texto y evitaremos que los campos puedan contener NULL (ralentizará las consultas innecesariamente).
• Una vez tenemos el modelo de nuestra base de datos, debemos pensar en como se consultarán los datos para generar los índices. Cuando mejores sean los indices que tengamos, con mayor rapidez realizará MySQL las consultas solicitadas. Para ello podemos hacer uso de EXPLAIN para que nos muestre como se harán las consultas y que índices utilizará MySQL. Como orientación será bueno tener índices sobre aquellos campos que sean utilizados en las clausulas WHERE o JOIN, y no crees índices innecesarios, se actualizarán con cada modificación haciendo un trabajo innecesario.
• Otro punto importante y en el que la mayoría no se fijan, piensa si no será mejor que la base de datos viva por su cuenta y que las aplicaciones accedan a los datos a través de procedimientos almacenados y funciones. De este modo será posible realizar modificaciones no previstas sin tener que tocar nada en las aplicaciones que acceden a los datos.
• Hasta aquí sobre la creación y diseño, ahora viene el apartado de mantenimiento de los datos. Primer consejo y el más importante: mantener la base de datos con la información necesaria. Para ello cada cierto tiempo hay que realizar limpieza y eliminar o archivar aquellos datos que ya no sean necesarios. Por otra parte, cuando se han realizado muchos cambios debemos volver a reorganizar la información, para ello ejecutamos OPTIMIZE TABLE, que se encargará de reparar y optimizar los datos e índices de las tablas.
• Puede que en determinado momento se produzca una inserción masiva de datos por alguna circunstancia imprevista. Llegados a este punto, lo mejor es volver a analizar si el diseño de la base de datos se sigue correspondiendo con el objetivo definido durante su diseño inicial. Suele ser una de las cosas que más pereza da realizar, sobre todo porque tendrá incidencia sobre las aplicaciones que ya utilizan la base de datos, pero que mejor resultado puede dar cuando hablamos de optimización. En este apartado es cuando vienen muy bien tener procedimientos almacenados y funciones.

Mejorar las consultas a la base de datos