Optimizar en MySQL: bases de datos, consultas, tablas...

A continuación, mostramos una serie de consejos y procedimientos para llevar a cabo la optimización de una base de datos MySQL. Esto mejorará notablemente su eficiencia y la gestión de los recursos.

¿Qué componentes se tienen en cuenta en la optimización? 

La optimización en MySQL pasa por tres componentes, estos son:

1. Optimización del servidor MySQL.
2. Optimización de la base de datos.
3. Optimización de las consultas.

¿Cómo optimizar la configuración del servidor MySQL?

La optimización del servidor puede incluir una multitud de enfoques y métodos. Para realizar el procedimiento de manera correcta, es necesario proceder con una metodología empírica, llevar a cabo las modificaciones una por una, ir testando la reacción del sistema y analizar el resultado. Una medida del rendimiento antes y después de haber efectuado la optimización permite ver si el sistema ha sido o no optimizado. Habrá que tener en cuenta los siguientes puntos: 

1. Compilación del servidor
2. Afinamiento de los parámetros del servidor
3. Afinamiento de otros parámetros

• Compilación del servidor

Se recomienda utilizar la versión del código fuente del servidor MySQL y compilar teniendo en cuenta los diferentes parámetros del sistema junto al conjunto de caracteres a utilizar, el microprocesador sobre el que se va a ejecutar y utilizar un compilador adaptado (por ejemplo pgcc para los microprocesadores Pentium).

• Afinamiento de los parámetros del servidor

Es posible optimizar el funcionamiento de MySQL cambiando el valor de los parámetros del servidor. Para mostrar los parámetros se debe utilizar el comando:

show variables;

Para ver el efecto de los parámetros sobre el servidor es necesario ejecutar el comando:

show status;

Existen numerosas herramientas de monitoreo que permiten ver los efectos de los cambios efectuados en los parámetros en el servidor MySQL, por ejemplo Mytop equivalente al comando top de Linux. El fichero my.cnf contiene todos los parámetros que deben ser optimizados. Inicialmente, es posible comenzar con aquellos que gestionan la memoria. Se debe tener en cuenta que de cuanta más memoria disponga el servidor, más rápido será. Sin embargo, hay que asegurarse de que esta esté disponible. MySQL contiene un conjunto de buffers y cachés internas, sobre las que es posible configurar el espacio asignado a partir de las variables del fichero my.cnf. Las dos variables más importantes son key_buffer_size y table_cache,  pues son compartidas por todos los threads que corren sobre el servidor e influyen de manera considerable en el rendimiento. Algunos ejemplos de variables son:

1. key_buffer_size: memoria utilizada para las copias de seguridad de los índices MyISAM.
2. table_cache: numero de tablas que pueden ser abiertas simultáneamente..
3. read_buffer_size: memoria utilizada para la copia de respaldo de los datos salidos de los full scan de las tablas.
4. sort_buffer: memoria utilizada para la copia de respaldo de los datos de las tablas que serán ordenadas con un ORDER BY.

• Afinamiento de otros parámetros

El servidor MySQL obtiene un funcionamiento óptimo en SOLARIS, sin embargo, es posible optimizarlo en otros SO para aproximarse a su rendimiento ideal. El uso de RAID-RAID 0 es recomendado para la optimización de las operaciones de lectura escritura. Así como el uso de discos SCSI en vez de IDE. El uso de redes rápidas optimiza el tiempo de respuesta y optimiza la comunicación entre cliente/servidor y amo/esclavo en la replicación.

¿Cómo optimizar la base de datos de MySQL?

Generalmente para la optimización de las bases de datos se recomienda hacer uso de buenas prácticas y metodologías de concepción que permitan implementar esquemas eficaces y normalizados. Para ello es necesario:

1. Saber qué ralentiza la base de datos. 
2. Elegir la metodología correcta.
3. Utilizar índices.
4. Utilizar OPTIMIZE TABLE.

• Bases de datos lentas

Generalmente, un cierto número de factores son la causa de la lentitud de las bases de datos. Entre los más frecuentes se encuentran:

1. Numero de índices insuficiente: la primera causa de la lentitud es el uso de tablas sin índices o sin índices en las columnas relativas a las búsquedas. Esto no quiere decir que todas las tablas deben de incluirlos, sino que hay que estudiar bien las necesidades de indexación.
2. Uso excesivo de índices: para optimizar las consultas y búsquedas, los índices son la solución. Sin embargo, el aumento de estos afecta al rendimiento en lo relativo a las actualizaciones. En la actualización de una tabla, las operaciones de inserción, modificación y eliminación repercuten generalmente sobre los índices.
3. Uso de privilegios en las tablas y columnas: en cada acceso, MySQL debe verificar los derechos sobre las tablas y las columnas, lo que ralentiza considerablemente el rendimiento.
4. No hacer la elección correcta en la concepción de la base de datos.

• Modelización de la base de datos

El uso de las buenas prácticas de modelización y concepción de bases de datos así como la elección de la metodología apropiada permite implementar bases de datos eficientes. Es necesario tener en cuenta las siguientes consideraciones:

1. Apropiada elección de los tipos de campos: hay que procurar elegir las variables que más se adaptan a las necesidades (por ejemplo, para almacenar un numero con no más de 10 dígitos, lo mejor es utilizar un tipo TINYINT). El uso de campos de menor tamaño permite cargar en memoria más columnas.
2. Uso de campos de longitud fija: el uso de longitudes predeterminadas permite optimizar el acceso a las columnas, pues sus posiciones son predefinidas. Esto implica disminuir el uso de VARCHAR, TEXT y BLOB (para TEXT y BLOB, se recomienda romper la normalización del esquema de la base de datos y hacer una copia de respaldo de estos campos en otras tablas).
3. Aumentar el uso de las restricciones NON NULL cuando sea posible para optimizar el espacio de almacenamiento.
4. Elegir el tipo correcto para las tablas: MySQL permite tener en un mismo esquema de diferente tipo.
5. Hacer una correcta indexación de las tablas.

• Utilizar los índices

Un índice es una tabla de búsqueda que nos permite encontrar rápidamente las líneas. Permite determinar la posición del registro buscado. Si una tabla no tiene índice, todos los registros serán recorridos durante la búsqueda. Los índices en MySQL son almacenados como b-trees (árboles binarios), que representan una estructura de datos fácil y rápida de recorrer. El índice puede incluir una o varias columnas y será llamado durante una búsqueda hecha sobre las columnas indexadas. En MySQL, la indexación es automática en las tablas con campos teniendo las restricciones PRIMARY, KEY, UNIQUE. La idea principal a tener en cuenta es que si una búsqueda es frecuente y esta incluye una o varias columnas, será necesario crear el índice correspondiente para optimizar el tiempo de respuesta. Para ello emplearemos el comando CREATE INDEX.

• Uso del comando OPTIMIZE TABLE

Equivalente a la desfragmentación del disco duro, el comando OPTIMIZE TABLE permite llevar a cabo este procedimiento en las tablas.

¿Cómo optimizar las consultas en MySQL?

MySQL permite analizar las consultas, así como conocer el tiempo y el plan de ejecución. Esta información permite comprender qué está ralentizándolas y optimizar su ejecución. Una vez detectadas las consultas lentas, la ejecución del comando EXPLAIN permite comprender el proceso y, por lo tanto, conocer o intervenir para llevar a cabo la optimización.

• Detectar las consultas lentas

Para detectar las consultas lentas es posible:

1. Observar las consultas lentas durante su ejecución y los tiempos de respuesta anormales.
2. Hacer un benchmark: testear las aplicaciones para ver qué componentes son los más lentos.
3. Verificar el Slow query log: es posible activar esta opción en MySQL configurando la variable --log-slow-queries.