Cómo optimizar una base de datos MySQL

6min

Cuanto mejor mantengamos nuestra base de datos, mejor rendimiento obtendremos de las consultas que realicemos sobre la misma (los resultados se obtendrán más rápidamente, y en consecuencia, se podrán mostrar antes).

Para conseguir mejorar nuestra base de datos y obtener unos resultados óptimos, hay varios puntos que debemos considerar:

  • Optimizar la base de datos
  • Mejorar las consultas a la base de datos
  • Mejorar los scripts (programación PHP, ASP, etc.) que muestran los resultados de las consultas

En este artículo trataremos de darle algunas recomendaciones sobre cada uno de estos apartados para que mejore, en lo posible, el rendimiento de sus bases de datos MySQL. Y aunque algunas son específicas para esta, muchas se pueden aplicar a cualquier otro modelo que estés usando.

Índice

Cómo optimizar una base de datos MySQL

Paso 1. Optimiza el diseño con un modelo relacional

Siempre es necesario dedicar un tiempo al diseño de nuestra base de datos. Indicar bien las tablas, campos y sus relaciones, en función de las necesidades que tengamos, puede facilitarnos el mantenimiento y garantizarnos un rendimiento adecuado a nuestras necesidades.

Para conseguir un buen diseño de las tablas que integrarán nuestra base de datos, suele utilizarse un modelo relacional, donde se extraen los elementos, propiedades y relaciones entre los mismos, que se traducen en la base de datos en tablas, sus campos, índices y claves relacionadas.

Paso 2. Optimiza los índices

Una vez extraídas las tablas del modelo relacional, lo principal para un buen funcionamiento de nuestra base de datos es disponer de los índices correctos en las tablas sobre los que trabajará MySQL para extraer el resultado de las mismas.

La indexación, tanto de claves primarias como extranjeras, se puede obtener del modelo relacional.

  • Las claves primarias identifican unívocamente a cada elemento de una tabla.
  • Las claves extranjeras marcan las relaciones entre tablas.

Disponer de índices en los campos adecuados optimizará tus resultados:

  • Para mejorar una consulta (SELECT), hay que crear un índice sobre los campos que son utilizados en las búsquedas (los que aparecen en las cláusulas WHERE o JOIN).
  • Utiliza índices sobre campos con valores únicos. Los índices funcionan peor si el campo tiene valores duplicados.
  • Trata de que los índices sean cortos. Si indexas un campo de texto, evita hacerlo sobre campos de longitud variable, y acorta siempre el tamaño del índice a lo que consideres más adecuado. Por ejemplo, si un campo CHAR tiene 200 caracteres y sabes que los valores se distinguen en los primeros 20 caracteres, indexa sólo hasta dicho tamaño de campo. Ahorrarás espacio y ganarás velocidad de respuesta.
  • No crees índices innecesarios. Estos se actualizan con cada cambio en la tabla asociada y pueden ralentizar las modificaciones de la misma.

Paso 3. Optimiza los tipos de campos

Sé coherente con los tipos de campos en sus tablas y elige siempre los más adecuados:

  • Utiliza los mismos tipos de campos para el mismo tipo de información en distintas tablas. Si necesitaras cruzar tablas con campos del mismo tipo, ganará en rapidez.
  • Evita en lo posible el uso de campos de tamaño variable. Los campos de longitud fija (como CHAR) son más eficientes que los de longitud variable (VARCHAR, BLOB o TEXT).
  • Utiliza campos numéricos frente a campos de texto.
  • Trata de usar campos que no puedan tener valores nulos (Not Null), pues ralentizan las lecturas.

Paso 4. Optimiza las tablas

Mantén siempre tus tablas con la información necesaria (ni más ni menos):

  • Si creas tablas con el atributo row_format, usa el tipo fixed (en vez de dynamic), ya que las tablas se consultarán de modo más rápido.
  • Haz “limpieza” cada cierto tiempo. Si observas que tus tablas tienen muchos registros (han crecido mucho de tamaño), analiza sus datos para comprobar si algunos registros están anticuados y pueden eliminarse o archivarse. Reducir el tamaño de sus tablas mejorará su rendimiento.
  • Cuando hayas hecho varios cambios o eliminaciones en alguna tabla, ejecuta la sentencia OPTIMIZE TABLE, que reparará y ordenará la tabla para mejorar su rendimiento.

Cómo optimizar consultas MySQL

Durante el proceso de diseño, ten siempre presente las consultas tipo que se van a realizar sobre tu base de datos. Eso te permitirá optimizar al máximo su estructura para obtener los mejores resultados.

  • Cuando se realiza una consulta SELECT, hay que evitar en lo posible el uso del comodín “*”, e indicar sólo los campos imprescindibles que se necesitan. Eso reducirá el tamaño de la consulta.
  • Evita en lo posible el uso de LIKE. Las comparaciones entre campos de texto (BLOB, TEXT…) ralentizan las consultas. Si fuera necesario, crea índices fulltext para los campos de texto sobre los que vaya a efectuar consultas.
  • Evita también el uso de GROUP BY, ORDER BY o HAVING.
  • Prueba tus consultas con anterioridad mediante el comando EXPLAIN. Le mostrará un listado informativo sobre cómo se realiza la consulta. Sintaxis: Explain select * from tabla
  • Optimiza la cláusula WHERE ( –en inglés-):
    • Evita el uso de paréntesis innecesarios.
    • Usa COUNT (*) sólo en consultas sin cláusula WHERE y que afecten a una única tabla.
    • Si sabes que el resultado de una cláusula GROUP BY o DISTINCT va a ser muy reducido, usa la opción SQL_SMALL_RESULT. MySQL usará tablas temporales de acceso rápido para el resultado en vez métodos de ordenación.
  • Para añadir registros a sus tablas, es más eficiente realizar una inserción múltiple que varias inserciones por separado. Sintaxis: INSERT INTO table (campo1, campo2) VALUES (1, ‘valor1’), (2, ‘valor2’)
  • Encule la inserción de datos para evitar esperas innecesarias mediante el uso de la sentencia INSERT DELAYED. La inserción se encola (agrupándose en bloques para ejecutarse de forma más eficiente) a la espera de que la tabla afectada no esté siendo utilizada por ningún otro proceso.
  • Da prioridad a las sentencias de lectura (SELECT) frente a las de escritura (INSERT):
    • Con INSERT LOW_PRIORITY, consigues que las sentencias de inserción esperen a que no haya otros procesos leyendo la tabla para ejecutarse.
    • Con SELECT HIGH_PRIORITY, consigues que cualquier otro proceso simultáneo de actualización o inserción de datos espere a que se realice la consulta.
  • Si tienes dudas sobre el rendimiento de alguna expresión, utiliza la función:BENCHMARK (contador, expresión)Te mostrará el tiempo que tarda en ejecutarse.

Cómo optimizar scripts MySQL

  • Reduce el tiempo y número de conexiones a la base de datos:
    • Evita que el tratamiento de la información y la presentación de la misma se realicen dentro del tiempo de conexión. Para ello, almacena la consulta en alguna variable para tratar los datos posteriormente.
    • Si necesitas hacer varias consultas independientes, agrúpalas para realizar una única conexión para todas ellas.
  • Pagina los resultados. Limita la presentación a un número máximo (por ejemplo, 10 por página) con la opción LIMIT al final de su consulta.
  • Utiliza algún sistema de caché para reducir los accesos a la base de datos en casos de información poco cambiante que se consulte con frecuencia. Por ejemplo, puedes usar alguna variable de sesión si hay datos de alguna búsqueda que se tengan que mostrar en varias páginas. Eso evitará repetir la consulta por cada página.
  • Realiza consultas sólo de la información imprescindible. No metas en el SELECT campos que no vayas a necesitar, y no olvides introducir un criterio de búsqueda correcto en el WHERE si relacionas varias tablas.

Conclusiones sobre bases de datos MySQL

Si optimizas tu base de datos MySQL, vas a mejorar el rendimiento, la eficiencia y la escalabilidad de tu página web o aplicación. A través de las diferentes acciones que hemos explicado, esperamos que puedas sacarle todo su partido a partir de ahora.

Pero, ¿tiene alguna relación con dominios y hosting web? La respuesta es sí, ya que puedes evitar que tu sitio experimente problemas de rendimiento e inactividad. Por ejemplo, al crear una página web o una tienda online, si has optimizado correctamente tu BBDD, verás cómo se reducen los tiempos de carga y que la navegación es más fluida. Y esto se traducirá tanto en un mejor posicionamiento SEO como en una mayor tasa de retención de usuarios.

Además, ten en cuenta que la base de datos utiliza los recursos del servidor para almacenar, organizar y recuperar los datos, por lo que te recomendamos realizar una copia de seguridad en la nube, para evitar la pérdida de información en caso de que se produjera cualquier incidencia.

Fernán García de Zúñiga

Artículos relacionados

Productos relacionados: