¿Porqué MySQL es lento con grandes tablas?

Si trabajas habitualmente con MySQL probablemente habrás escuchado que MySql no es la elección acertada para manejar tablas con mas de 1,000.000 de registros.

Pero entonces porque MySql es el motor de compañías como Google, Yahoo o Technorati, además estas compañías manejan algunos cientos de millones de registros y consiguen un gran rendimiento.

El motivo de este gran rendimiento es que estas tablas que tienen cientos de millones de registros están diseñadas y entendidas para trabajar con MySql, si diseñamos considerando lo que puede y no puede hacer el motor de base de datos probablemente consigamos un buen rendimiento, cualquier sistema de administración de Bases de Datos es distinto con respecto a los otros, lo que funciona bien y es eficaz en Oracle ó PostgreSQL no debe ser lo que mayor rendimiento ofrezca en Mysql.

Por ello para trabajar con tablas muy grandes debemos tener en cuenta tres claves: Buffers, Índices y Consultas.

Buffers

Un buffer es una ubicación de la memoria reservada para el almacenamiento temporal de información digital.

La primera cosa que deberíamos tener muy clara es el hecho de que hay una gran diferencia entre “Datos que están en memoria” y “Datos que no están en memoria”.

Pongamos que comenzamos con un tamaño de memoria y notamos un descenso gradual del rendimiento porque la base de datos está creciendo, la solución sería asegurarnos que tenemos memoria suficiente para el volumen de datos que estamos utilizando.

Índices

Los índices son usados para encontrar rápidamente los registros que tengan un determinado valor en alguna de sus columnas. Sin un índice, MySql tiene que iniciar una búsqueda por el primer registro y leer toda la tabla para encontrar los registros relevantes.

Un error común es pensar que los índices no afectan en tablas con poco volumen de datos, aún en tablas pequeñas (1.000 registros) es por lo menos 100 veces más rápido leer datos utilizando un índice, sin este índice estaríamos haciendo una lectura secuencial.

Por lo tanto queda claro que los índices son realmente eficaces para acelerar el acceso a datos.

Cuando MySql encuentra un índice en una columna lo utiliza en lugar de hacer un escaneo completo de la tabla (leer todos los registros de la tabla de manera secuencial). Esto reduce los tiempos de CPU y las operaciones de “lectura/escritura en disco”.

Para verlo lo mejor es explicarlo con datos, comenzamos con una tabla que tiene 100 Bytes por registro y suponiendo que tenemos 100MB/segundo de velocidad de lectura por lo que calculando llegamos a la conclusión de que podemos conseguir 1,000.000 de registros por segundo (Tablas tipo MyISAM).

En este ejemplo vamos a hacer lo siguiente, tenemos una tabla con 30,000.000 de registros con una columna que tiene valores comprendidos entre el 1 y el 10.000, en esta prueba vamos a ver la diferencia de tiempos entre “Escaneo total de la tabla” y “Escaneo de un rango de valores de la tabla (entre 1 y 100)”.

SQL:
mysql> SELECT count(pad) FROM large;
      +------------+
      | count(pad) |
      +------------+
      |   31457280 |
      +------------+
      1 row IN SET (4 min 58.63 sec)

      mysql> SELECT count(pad) FROM large WHERE val BETWEEN  1 AND 100;
      +------------+
      | count(pad) |
      +------------+
      |     314008 |
      +------------+
      1 row IN SET (29 min 53.01 sec)

Se puede ver que el resultado es contradictorio, ha tardado 5 minutos en contar 30,000.000 de registros y 30 minutos en contar el 1% (300.000).

Hay muchas maneras de optimizar este tipo de consultas, por ejemplo ordenando primero los valores y hacer la búsqueda sobre los registros ya ordenados, esta solución reduciría el tiempo de ejecución de la consulta y el resultado sería el mismo.

El uso de índices se antoja esencial en este tipo de ejemplos en los que tenemos grandes cantidades de datos en nuestras tablas, la mejora en la obtención de datos será muy significativa.

Consultas

La optimización de las consultas podría ser el punto más extenso de los tres por la gran variedad de posibilidades que tenemos a la hora de optimizar consultas.

Uno de los pasos más importantes a la hora de optimizar nuestras consultas de MySql es identificar cuáles son las que están causando problemas.

¿Cómo podemos detectar cuáles son las consultas más lentas?

Normalmente si tomamos las 10 consultas más lentas y las optimizamos de manera apropiada obtendremos un notable aumento en el rendimiento.

Vamos a ver cómo podemos identificar estas consultas:
Activar el ‘log’ de mysql para consultas lentas
El primer paso será asegurarnos que nuestro servidor tiene activado el log que nos mostrará aquellas consultas más lentas.

Chequeemos si el servidor MySql tiene el log activo:

mysqladmin var |grep log_slow_queries
| log_slow_queries                | OFF                          |

Si log_slow_queries esta en ‘ON’ ya tenemos el log activo, por defecto esta opción suele estar desactivada en caso de no tener log_slow_queries definido en la configuración del servidor tambien estará desactivado.

La variable long_query_time (Por defecto 1) define a partir de donde se tiene que considerar una consulta como lenta. El valor 1 está expresado en segundos y determina que toda consulta que exceda ese tiempo es considerada como tal.

Con esto ya estamos listos para activar el mysql slow query log. Debemos de dirigirnos al fichero de configuración (/etc/my.cnf RHEL/Centos o /etc/mysql/my.cnf en Debian, etc.), y en la sección mysqld añadiremos:

[mysqld]
long_query_time = 1
log-slow-queries = /var/log/mysql/mysql-slow.log

Está configuración añadirá al log todas aquellas consultas que sobrepasen un segundo en el archivo /var/log/mysql/mysql-slow.log.

Obviamente es tu decisión determinar cual es el valor para long_query_time.

Analizar el mysql slow queries log
Después de activar el “mysql slow queries log” debemos analizar los datos devueltos, aquí os dejo una serie de detalles que os ayudarán a comprender este archivo:

* Time: Cuánto tardó en ejecutarse la consulta
* Rows: Cuántos registros fueron servidos por el servidor
* Host: Cual fué el host que ejecutó la consulta
* La consulta MySql

Esta información nos será suficiente para determinar qué consultas deben ser optimizadas, cómo se explica al principio de este apartado hay multitud de maneras para optimizar las consultas.

MySql nos ofrece una pequeña utilidad que nos ayuda a analizar estos datos: The Slow Query Log

Conclusión

1. Indexa las columnas sobre las que realiza la búsqueda, no sobre las que selecciona
   Las mejores columnas para indexar son en las que aparecen la cláusula “WHERE” o las nombradas en las cláusulas “JOIN”.
   
   
2. Utiliza índices únicos
   Los índices trabajan mejor sobre columnas con valores único, y peor con aquellas que tiene muchos valores duplicados.

   Por ejemplo, si una columna contiene fechas y tiene varios valores diferentes, un índice diferenciará las filas fácilmente, sin embargo, no le ayudará tanto si se utiliza en una columna para registrar el valores como verdadero,falso y que contiene sólo los dos valores “V” y “F” (cualquiera que sea que busque, tomará casi la mitad de las filas).
   
   

3. Utiliza índices cortos
   Si vas a indexar una columna de cadenas, especifica una longitud prefijada, siempre que sea razonable hacerlo as, por ejemplo si tienes una columna CHAR (200), no indexes la columna entera si la mayor parte de los valores son únicos dentro de los 10 o 20 primeros caracteres. Indexar estos 10 o 20 primeros caracteres te ahorrará mucho espacio en el índice, y probablemente hará más rápidas nuestras consultas.
   
   
4. No abuses de los índices
   Los índices deben ser actualizados, y posiblemente reorganizados, cuando modifiques los contenidos de tus tablas. Si tienes un índice que raramente, o nunca, se usa estás ralentizando el sistema volviendo más lenta de forma innecesaria las modificaciones de la tabla.
   
   
5. Considera el tipo de comparaciones a realizar en un registro
   Los índices no sólo se usan para operaciones <, <=, =, >=, > y BETWEEN sino que también se usan para operaciones LIKE, cuando el patrón tiene un prefijo literal. Si sólo usa una columna para otro tipo de operaciones, tales como STRCMP(), no tiene sentido indexarlo.
   
   
6. Motor de almacenamiento
   Elegir el motor de almacenamiento adecuado (tipo de tabla): innoDB, MyISAM, etc, todo dependerá de lo que busquemos y en función de esto podemos optimizar bastante nuestra base de datos.
   
   

Para terminar recordar el principio de la entrada… puedes conseguir que MySQL rinda a buen rendimiento con grandes cantidades de datos pero para ello debes tener en cuenta sus limitaciones y saber cuales son las características que ofrecen mejor rendimiento.