Sortea2 Blog

En un proyecto web medianamente ambicioso llega siempre el momento en que la parte «sagrada» de la aplicación es la Base de Datos. El problema reside cuando las tablas de nuestra Base de Datos empiezan a tener más de 10.000 registros. A partir de ese momento debemos pensar de diferente manera: los accesos deben optimizarse, el sistema de búsqueda ya es demasiado lento y a veces llega incluso al límite de tiempo de ejecución, ya no se puede hacer un ALTER TABLE sin tener que tener la tabla bloqueada unos segundos, etc. Cuando nos empezamos a encontrar con estos problemas es cuando no hemos hecho una predicción suficientemente buena y debemos actuar rápidamente para remediarlo.

Optimizar MySQL es una tarea que debemos tener en mente desde el minuto cero, aunque sin obsesionarse. Siempre es mejor avanzar que quedarse atascado.

A continuación os mostraré 10 consejos que pueden acelerar los accesos a vuestras tablas:

1. Usa índices:

   Un índice es un sistema informático que permite al gestor de base de datos (MySQL) acceder a la información (registros de una tabla) de una forma más directa que si tuviera que ir dato a dato comprobando si cumple las condiciones.

   Los campos que solamos incluir dentro del «WHERE» de las consultas más comunes deben estar indexadas o bien controladas.

   Los accesos vía PRIMARY KEY son, con diferencia los más veloces. De hecho, cuando utilizamos el motor InnoDB, los datos se encuentran físicamente ordenados por el valor de la clave primaria. Esto hace que acceder a los datos sea trivial.

   Si sabemos que un determinado registro no va a poderse repetir, debemos incluirle un índice UNIQUE y si vamos a realizar búsquedas sobre un campo de fecha por ejemplo, le podemos añadir un INDEX simple.

   Siempre que indexamos un campo disminuimos la velocidad de escritura en la tabla porque cada vez que la manipulamos MySQL tiene que asegurarse de actualizar el índice. Hay que encontrar el equilibrio que permita a la aplicación funcionar rápidamente. Si una tabla recibe muchas modificaciones quizás haya que sacrificar algo de velocidad en las consultas.

   Otro dato a tener en cuenta es que MySQL utiliza mayoritariamente índices B-Tree, en el cual se va creando un árbol con las posibles combinaciones. Por ejemplo, en un índice compuesto por los campos (nombre, apellido1, apellido2) será agrupado primero por nombre, luego por apellido1 y luego por apellido2. Una consulta que busque por apellido2 únicamente no utilizaría el índice. En cambio, una consulta por nombre sería rápida. Una consulta consultando por nombre, apellido1 y apellido2 sería bastante óptima también porque podríamos recorrer el índice de arriba a abajo para encontrar el dato.

2. Utilización de campos binarios

   Los tipos de dato binario son BINARY, VARBINARY y BLOB que van en consonancia con CHAR, VARCHAR y TEXT. Si el texto que vas a almacenar no va a tener una codificación como UTF-8 es mucho más conveniente utilizar campos binarios, puesto que ocupan mucho menos espacio y las comparaciones se hacen como datos numéricos internamente.

   Por ejemplo, campos que guarden cadenas codificadas como MD5, campos de permalink (URL amigable para buscadores), nombres de usuario que sepamos que no permiten caracteres extraños, etc. pueden ser puestos como binarios. Por cada unidad de información utilizaremos menos bytes y por lo tanto el motor de almacenamiento de MySQL podrá manejar los datos con más soltura.

3. Utiliza UNSIGNED

   Si al crear una tabla, sabes que un campo solamente contendrá valores numéricos positivos, utiliza UNSIGNED. Esto aumenta al doble la capacidad máxima de los campos sin alterar el número de bytes necesarios para almacenar cada dato. Por ejemplo, todos los campos que utilicen un AUTO_INCREMENT deben utilizarlo, puesto que de la otra manera tendremos un rango enorme de números que jamás utilizaremos.

   En aplicaciones que manejen gran cantidad de datos es posible que un campo INT se nos quede corto para almacenar un AUTO_INCREMENT. Si estuviera UNSIGNED habríamos tenido un campo con el doble de vida útil.

4. Guarda información redundante

   Con esto nos estaríamos saltando la normalización de las tablas. Pero la realidad es que en las aplicaciones web lo más común es que por cada inserción de datos tengamos muchas más consultas de datos. Lo habitual es que la información cambie cada cierto tiempo pero que sea consultada a todas horas.

   Puede ser útil tener campos calculados. Por ejemplo, total de comentarios en una entrada, número de opiniones sobre un producto, ID del último comentario, etc.  Cada vez que se realice una modificación en esas tablas tendremos que hacer más trabajo, pero normalmente compensa.

5. No uses tipos de datos gigantes

   Me atrevería a decir que el 99% de las personas jamás necesitarán un campo LONGTEXT a no ser que se trate de un gestor de contenidos o algo por el estilo; incluso habrá muy pocas que necesiten un campo BIGINT para uso normal. Un BIGINT ocupa cada número 8 bytes y puede almacenar un número de un 9 seguido de 18 ceros (aproximadamente). El campo LONGTEXT almacena hasta 4 billones y pico de caracteres, que pueden ser mucho más de 50 páginas de Word por ejemplo.

   Los tipos de dato exageradamente grandes ocupan más espacio en disco y rara vez se utilizan por completo. Es por eso que es más recomendable usar los campos adecuados sin pasarse demasiado.

6. Trata de usar los simplificadores del lenguaje

   MySQL proporciona algunas maneras que ayudan a hacer las consultas más cortas y más fáciles de entender. Estos métodos no solo nos ayudan a nosotros, sino que internamente son mucho más eficientes. Por ejemplo, usar la expresion " WHERE sorteo = 1 OR sorteo = 2 OR sorteo = 5" es mucho menos eficiente que la siguiente: " WHERE sorteo IN(1,2,5)".

   Algunas de estas expresiones pueden ser BETWEEN, IN, LEAST, GREATEST o INTERVAL.

   Ten en cuenta además de que cuanto más cortas sean las consultas, menos bytes ocuparán y menos tardará el servidor de MySQL en recibirlas. Sobre todo en conexiones remotas.

7. Intenta unificar consultas

   Intenta siempre hacer una consulta cuando normalmente lo harías con varias. INSERT puede insertar varios registros al mismo tiempo si se separan los «VALUES» por paréntesis y comas, por ejemplo: INSERT INTO sorteo VALUES (1,2, 3),(4,5,6),(7,8,9).

   DELETE múltiples se pueden construir en vez de en bucle insertando los valores a borrar dentro de un IN() de MySQL.

   También es posible modificar varios registros desde una consulta. Para ello tendremos que utilizar una sintaxis un tanto peculiar, pero que puede ser útil. Podéis ver esa sintaxis en el siguiente enlace: http://stackoverflow.com/a/84111

   Es útil sobre todo para modificaciones masivas a través de un script.  Por ejemplo, un script que descargue imágenes para cada producto y luego actualice el campo «imagen» de la tabla «producto». Ir uno a uno modificando cada registro sería muy lento e intensivo. Podríamos agrupar las modificaciones de 10 en 10 registros para aliviar la carga de la base de datos.

   Cada vez que mandamos una consulta tenemos que contar con el tiempo de ping, que es lo que tarda el cliente de mysql en «llegar» al servidor. Si tenemos un ping de 10ms ninguna consulta tardará menos de 10ms porque es el tiempo mínimo. Si unificamos 100 consultas en una, esos 10ms solo los tenemos que perder una vez.

8. Evita operaciones matemáticas

   No es buena idea hacer cálculos complejos en una SELECT. Siempre que sea posible hay que traer los datos al lenguaje que utilices (PHP por ejemplo) y hacer estos cálculos. Por ejemplo, realizar redondeos, medias, divisiones y demás puede resultar problemático y hay que tener siempre en mente que cuanto menos trabajo haga la Base de Datos, más rápida será.

   Normalmente las únicas operaciones matemáticas que compensan son las asociadas a un GROUP BY.

9. Utiliza MyISAM si es posible

   Un denominador común en las aplicaciones web es que casi siempre se hacen más selects que inserts/updates/deletes. Si este es nuestro caso, podemos renunciar a las posibilidades relacionales que permite InnoDB para tener un gestor más rápido en las consultas.

   Si recibimos muchas consultas de manipulación de datos MyISAM es insostenible y deja corruptas las tablas con frecuencia. Debemos encontrar el equilibrio. Para un blog MyISAM es ideal. Para un bróker online en tiempo real no lo es.

10. Utiliza el PROCEDURE ANALYSE() de MySQL

    Esta es una característica muy desconocida de MySQL pero de gran utilidad. Se añade tras una consulta y lo que hace es procesarla y devolver estadísticas sobre cada campo seleccionado. Dice máximos, mínimos, medias y lo más importante, propone el tipo de campo óptimo para cada columna.

    Pruébalo tú mismo, ejecuta SELECT * FROM mytabla PROCEDURE ANALYSE(); y comprueba los resultados.

    Ten en cuenta que a nada que haya menos de 100 posibles valores para un campo ya va a proponerte que utilices un ENUM. No siempre esto será aplicable a tu aplicación.

    Este procedimiento es bastante desconocido pero es muy útil sobre todo cuando tenemos que empezar a dar soporte a tablas creadas por otras personas. Es posible que así identifiquemos campos que en realidad no se usan, tipos de datos que son erróneos, etc.

En definitiva todos los puntos se refieren a que hay que tener control total de los datos que tenemos almacenados, siempre teniendo en cuenta las limitaciones y tratando de mantener una integridad de los datos.

Fuentes: MySQL Certification Study Guide, Building Scalabe Web Sites (The Flickr way).