Cuando un proyecto es pequeño no nos preocupamos por el nivel de carga, o la demora en las consultas, ni muchas otras cuestiones que son de muchísima importancia cuando nos encontramos frente a un sistema mediano-grande.
Utilizar el menor tipo de dato posible
Al momento de crear la tabla, ya sabremos qué valores contendrá. No podemos caer en el error de quedarnos cortos pero para realmente optimizar mysql tenemos que ser cuidadosos no sólo al momento de crearlas, sinó revisarlas con el tiempo. Por ejemplo, si sabemos que un campo contendrá un rango de números positivos, utilicemos UNSIGNED, entonces el dato aunque ocupará el mismo espacio, nos dará el doble de valores posibles aumentando el límite superior. El mejor ejemplo son los campos que sean AUTO_INCREMENT, sinó tendremos un rango disponible que nunca vamos a usar.
Por otro lado, siempre que sea posible utilizar los tipos de datos binarios y redefinir los campos que tengamos sobredimensionados. Por ejemplo, si vamos a necesitar guardar 1 dígito, podemos utilizar ENUM y será mucho mejor que un char, un int, tinyint u otro de longitud 1.
No repetir consultas lentas
Es importante tener configurado y evaluar los logs de consultas lentas, o slow querys para poder actuar en base a algo que seguramente está funcionando mal, o lento. Lo primero que podemos hacer, es buscar alguna consulta, o patrón de consulta que se repita. Suele ocurrir que aparece alguna consulta muy compleja que se repite mucho y suele demorar. Si tenemos mucho tráfico y ocurre esto, es conveniente almacenar los resultados en otra tabla, y antes de volver a ejecutar esa consulta, verificar que no estén los resultados ya almacenados, siempre y cuando tengamos en cuenta una ventana de tiempo, es decir renovar esos resultados cuando haga falta.
Atención! Este procedimiento puede ser contraproducente si la consulta compleja no se ejecuta muy frecuentemente…
Información redundante
Además, podemos establecer campos redundantes en algunas tablas de uso frecuente. Si bien no es una práctica de manual, a veces mejora mucho el rendimiento, tener actualizado el resultado de una consulta que recupere utilizando un SUM o un COUNT en base a una clave y que genere agrupaciones y joins complejos, teniendo esa información en un campo y de esa manera evitarle al servidor ejecutar consultas repetitivamente sin necesidad y de esta manera optimizar mysql.
Esto aplica para todo tipo de operaciones matemáticas, que pueden ser evitadas manejando algunas situaciones en el lenguaje de programación que interactúe con el usuario luego de la consulta. No siempre se puede hacer esto, pero hay que tenerlo en cuenta cuando tenemos un cuello de botella en algún punto.
Procedimientos del motor
No podemos optimizar mysql sin conocer dos utilizaciones del motor, dos procedimientos que nos brindan muchísima información de diagnóstico.
Por un lado tenemos EXPLAIN, podemos usarlo antecediendo a cualquier consulta, y no nos mostrará los resultados, sinó que nos explicará cuántos, cómo y porqué recorrerá los registros la consulta, y algunos comentarios que nos puede ayudar para detectar falencias. Podemos ver más info de esto aquí.
Otra función menos conocida, pero no menos importante es el PROCEDURE ANALYSE() que se utiliza después de un select sobre una tabla completa. Nos mostrará estadísticas y consejos sobre la tabla seleccionada. Ojo con esto! El diagnóstico se hace sobre el contenido actual de la tabla, y puede aconsejarte cambios que no son lógicos, pero esto tenemos que evaluarlo nosotros. (Lo más divertido es cuando te devuelve que el tipo de campo debería ser un ENUM del 1 al 64 cuando tenés esos valores en determinado campo.)
Acá podemos evaluar qué campos no se usan, cuáles tienen sentido o incluso cuáles podríamos modificar para optimizar mysql.
Lo más importante: índices
El índice genera rutas de acceso a los datos, sin necesidad de evaluar si cumple con las condiciones. Aprender sobre su utilización es de suma importancia.
Los campos que frecuentemente se utilizan para filtrar, es decir se encuentran luego del WHERE deberían estar indexados.
Ahora, CUIDADO! Cada vez que definimos un índice en un campo, hacemos más lenta la escritura en la tabla, porque cada vez que se cambie un registro, se actualizarán los índices, pero… será más rápida la lectura. Debemos encontrar un equilibrio que nos permita optimizar mysql de acuerdo a nuestras necesidades.
Les recomiendo leer en detalle antes de aventurarse a probar con índices, especialmente cuando se utilizan índices definidos para más de un campo, para saber cómo se deben crear las consultas y ordenar los campos luego.
Como lectura interesante en este tema, la página oficial de mysql para introducción a los índices aquí.
