La optimización en MySQL pasa por tres componentes, a saber:

• Optimización del servidor MySQL
• Optimización de la base de datos
• Optimización de las consultas

Optimización de la configuración del servidor MySQL

La optimización del servidor puede incluir una multitud de enfoques y métodos, lo que intentaremos presentar en lo que sigue es una introducción a los enfoques de base, a saber:

• Compilación del servidor
• Afinamiento de los parámetros del servidor
• Afinamiento de otros parámetros

Para hacer una buena optimización, es necesario proceder con una metodología empírica a saber hacer las modificaciones una por una y probar cada vez la reacción del sistema para ver el resultado. Una medida del rendimiento antes y después de haber efectuado la optimización permite ver si el sistema ha sido optimizado o no.

Compilación del servidor

Es recomendado utilizar la versión del código fuente del servidor MySQL y compilarla teniendo en cuenta los diferentes parámetros del sistema a saber el conjunto de caracteres a utilizar, el microprocesador sobre el que va a correr 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 los valores de los parámetros del servidor.
Como recordarás 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 los parámetros que gestionan la memoria. Se debe tener en cuenta que cuanta más memoria disponga el servidor, más rápido será, sin embargo, hay que asegurarse de que la memoria esté disponible.
MySQL contiene un conjunto de buffers y cachés internos, en el que es posible configurar el espacio asignado a cada uno a partir de las variables del fichero my.cnf. Las dos variables más importantes son key_buffer_size y table_cache ya que son compartidas por todos los threads que corren sobre el servidor e influyen de manera considerable en el rendimiento.
Un ejemplo de variables:

• key_buffer_size: memoria utilizada para las copias de seguridad de los índices MyISAM.
• table_cache: numero de tablas que pueden ser abiertas simultáneamente.
• read_buffer_size: memoria utilizada para la copia de respaldo de los datos salidos de los full scan de las tablas.
• 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 para la replicación.

Optimización de la base de datos

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

• Saber lo que está lento en las bases de datos
• Elegir la metodología correcta
• Utilizar índices
• Utilizar OPTIMIZE TABLE

Qué es lo que ralentiza las bases de datos

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

• Insuficiente numero de índices: 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 tener índices, sino que hay que estudiar bien las necesidades de indexación.
• Uso excesivo de índices: para optimizar las consultas y búsquedas, los índices son la solución, sin embargo, el aumento de índices afecta el 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.
• Uso de privilegios en las tablas y columnas de las tablas: en cada acceso MySQL debe verificar los derechos sobre las tablas y las columnas de las tablas lo que ralentiza considerablemente el rendimiento.
• 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 eficaces.
Es necesario tener en cuenta un cierto número de consideraciones:

• Apropiada elección de los tipos de campos: siempre procurar elegir las variables más adaptadas 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.
• Uso de campos de longitud fija: el uso de longitudes predeterminadas permite optimizar el acceso a las columnas ya que 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).
• Aumentar el uso de las restricciones NON NULL cuando sea posible para optimizar el espacio de almacenamiento.
• Elegir el tipo correcto para las tablas: MySQL permite tener en un mismo esquema tablas de diferente tipo.
• Hacer una buena indexación de las tablas.

Utilizar los índices

Un índice es una tabla de búsqueda que nos permite encontrar rápidamente líneas en una tabla. El índice permite determinar la posición del registro buscado en una tabla.
Si una tabla no tiene índice, todos los registros serian recorridos durante la búsqueda.
Los índices en MySQL son almacenados como de b-trees (árboles binarios), que representa una estructura de datos fácil y rápida de recorrer.
El índice puede incluir una o varias columnas, el índice 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 ésta incluye una o varias columnas, será necesario crear el índice correspondiente para optimizar el tiempo de respuesta vía el comando CREATE INDEX

Uso del comando OPTIMIZE TABLE

Equivalente a la defragmentación del disco duro, el comando OPTIMIZE TABLE permite defragmentar las tablas.

Optimización de las consultas

MySQL permite analizar las consultas y conocer el tiempo y plan de ejecución. Esta información permite comprender lo que hace que las consultas sean lentas y optimizar la ejecución de éstas.

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

Una vez detectadas las consultas lentas, la ejecución del comando EXPLAIN permite comprender la ejecución y por lo tanto conocer o intervenir para optimizar.

PD: El artículo original fue escrito por wjaouadi, contribuidor de CommentCaMarche