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

Continuando la serie de artículos que empezamos con Cómo mejorar la velocidad de una web, hoy vamos a hablar de como optimizar nuestra base de datos, tanto si  estamos limpiando tablas antiguas o creando nuevos índices para un acceso más rápido, siempre hay cosas que se pueden optimizar. Creo que lo mejor para este tema es separarlo en dos apartados claramente diferenciados:

  • Optimizar base de datos: Configuración y diseño de nuestra base de datos.
  • Optimizar consultas a base de datos: Acciones que realizamos en la base de datos y comandos mas eficientes.

Challenge accepted

¿Cómo optimizar base de datos?

Lo más importante es crear nuestra base de datos, sin base de datos no hay nada que optimizar :P

Crear diseño de base de datos, ajustándonos a los requerimientos.

Cuando hacemos el diseño tenemos que fijarnos en optimizar cada uno de los campos, para ello vamos a ver cada uno de los puntos que tenemos que tener en cuenta:

Usar tamaños estáticos

Si tenemos que almacenar el DNI de un usuario siempre va a tener 9 dígitos, por lo que tendríamos que usar un CHAR(9), ya que si usáramos un VARCHAR, implicaría perder más tiempo ya que su lectura, indización y almacenamiento es más complejo al tener un tamaño variable.

Valor CHAR(9) Almacenamiento VARCHAR(9) Almacenamiento
'' '         ' 9 bytes '' 1 byte
'62384' '62348    ' 9 bytes '62348' 6 bytes
'39238375D' '39238375D' 9 bytes '39238375D' 10 bytes

Como vemos si tenemos claro el tamaño es mejor usar un tamaño estático. Introduciendo este tema llamamos tabla fija o estáticas a las que todos los campos tienen un tamaño fijo, este tipo de tablas son mas eficientes ya que al realizar las consultas saben cual es el tamaño del campo, donde empiezan el valor en memoria y no tiene que realizar consultas innecesarias. Una técnica para separar los campos de tamaño variable es el particionado vertical, que veremos a continuación

Particiona las tablas

o particionado vertical, puede ser un arma de doble filo y tenemos que tener muy claro que los campos que vamos a separar no los vamos a necesitar juntar frecuentemente o perjudicará a nuestro rendimiento. Dicho esto nos puede ayudar en dos casos:

  • Evitar actualizar caché: Si tenemos una tabla usuarios que registra la última conexión del usuario, puede ser interesante tener una tabla a parte para que cada vez que un usuario inicie sesión, no tengamos que volver a actualizar la caché.
  • Hacer tablas mas pequeñas: Por otro lado si tenemos una tabla de usuarios que se usa para el login y queremos almacenar datos personales, podemos separar por frecuencia los campos que más usamos.

Los campos cortos son más rápidos

Como vemos en la documentación de MySQL, en la sección de requerimientos de almacenamiento nos indica cuanto espacio en memoria consume cada tipo de dato. Por lo que cuanto menos bytes tenga que procesar nuestra base de datos pues mejor :D

Tener claro cuando usar índices

Siempre tenemos que tener algún campo indexado en nuestras tablas pero tenemos que tener claro que agregar índices para solucionar consultas lentas, no es la solución.

  • Los índices no son solo para campos únicos o primarios
  • Las columnas con índice único son mucho más rápidas
  • Las búsquedas de una cadena dentro de un campo, no se ven afectadas por la indexación
//Se ve afectado por los indices
… WHERE campo LIKE "cadena%";
//No se ve afectado por lo indices
… WHERE campo LIKE "%cadena%";
//Tenemos que evitar este tipo de consultas ya que son muy costosas

Como conclusión podemos decir que el uso de los índices está pensado para usarlos con campos que se consultan con mucha frecuencia, se realizan agrupamientos, ordenamientos o joins.

Evita los campos NULL

Tenemos que valorar si realmente hay diferencia para nuestro caso actual usar un campo vacío en lugar de NULL ya que, agrega información adicional a nuestro campo y puede llegar a complicar nuestras consultas. Aun que si comparamos Oracle con MySQL vemos tratan de distinto modo este tema:

  • Oracle: No hace diferencia entre campos nulos y vacíos
  • MySQL: Las columnas NULL requieren espacio adicional en la fila a grabar donde los valores son NULL. Para las tablas MyISAM, cada columna NULL toma un bit extra, redondeando hacia arriba al byte más cercano.

Almacena las direcciones IP como UNSIGNED INT

Puede que lo más frecuente sea pensar en una ip como 15 caracteres y por consecuencia asignarle CHAR(15) a nuestro campo, pero si nos paramos a analizar nos fijamos que podemos guardarlo como entero sin signo ya que cubre la longitud deseada al aplicarle las siguientes funciones:

INETATON() e INETNTOA() en MySQL o ip2long() y long2ip() en PHP

Veamos un ejemplo:

SELECT INET_ATON("209.207.224.40");  
+-----------------------------+
| INET_ATON("209.207.224.40") |
+-----------------------------+
|                  3520061480 |
+-----------------------------+
1 row in set (0.00 sec)  

Lo que estaría realizando la función sería la siguiente operación:
209256^3 + 207256^2 + 224*256 +40.

Usar ENUM en lugar de VARCHAR

Si sabemos que vamos a usar unos valores específicos para el campo, la mejor opción es usar ENUM, son campos mucho más rápidos y compactos ya que se almacenan como un TINYINT. Hay un procedimiento en MySQL para detectar estos casos y que nos ayuda a analizar nuestra base de datos, dándonos sugerencias de que el tipo de una columna no es el adecuado. Para usarlo haremos uso de la llamada al procedimiento PROCEDURE ANALYSE(), que analizaremos a continuación.

Usa PROCEDURE ANALYSE(), para obtener sugerencias

Por ejemplo, si creaste un campo INT para tu clave primaria, pero no tienes muchas filas, podría sugerirte que uses MEDIUMINT en su lugar. O si estas usando un campo VARCHAR, podría sugerirte que lo conviertas en ENUM, si sólo estás escribiendo unos pocos valores. Hay que tener en cuenta que son sugerencias y que solo nosotros sabes la manera en la que va a crecer nuestra base de datos, por lo que la decisión de implementar los cambios puede ser buena o mala idea.

Optimización de MySQL

La optimización de MySQL también es muy importante. Aun que es muy variable dependiendo de nuestra aplicación, por lo que tenemos que analizar los valores de nuestra configuración. El archivo de configuración de MySQL / MariaDB normalmente se encuentra en /etc/my.cnf. Algunas de las opciones que tenemos que tener en cuenta son las siguientes.

  • tmptablesize:Indica el tamaño que tiene la tabla temporal antes de que MySQL pase a memoria como tabla MyISAM. (Siendo el acceso a memoria mucho mas lento que el acceso a disco)
  • maxheaptable_size: Controla el tamaño máximo que puede consumir una tabla en memoria.
  • querycachetype: Habilita la caché.
  • querycachesize: Indica el tamaño de la caché, muy útil cuando tenemos muchas lecturas intensivas, devolviendo el mismo resultado cada vez, su valor suele ir entre los 32M y 512M. Aun que puede no ser interesante si cuando queremos acceder a nuestra caché, se produce un fallo de caché y hay que ir a buscar el dato a disco, por lo que es interesante analizarlo para cada caso.
  • tablecache: Por defecto tiene el valor 64 e indica el número de tablas abiertas por cada threads. Cuanto mayor es este número menos fallos de caché obtendremos, pero tenemos que fijarnos bien que valor le tenemos que dar para no desperdiciar memoria. Este tamaño tiene que ser mayor al número de tablas de nuestra base de datos y nos podemos ayudar de OpenedTables para comprobar si el valor es correcto. OpenedTables nos indica la rapidez de creación de las tablas, por lo que si es muy elevado probablemente nuestro tablecache sea muy pequeño.
  • threadcachesize: Número de threads que el servidor cachea para reusar, mejora el rendimiento si se tienen muchas conexiones. Si un usuario se desconecta pone los threads en la caché si queda espacio.
  • keybuffersize: Indica el tamaño del bufer que usan los bloques de indices, el tamaño máximo es de 4GB
  • *join_buffer_size*: Indica el tamaño del bufer usado para joins sin índices y otros casos.
  • sortbuffersize: Indica el tamaño del bufer que se usa para ordenar los resultados, está alojado en cada thread cuando necesita realizar la ordenación.
  • readbuffersize: Indica el tamaño del bufer usado para leer tablas.
  • thread_stack: Indica el tamaño de la pila por cada thread, el tamaño por defecto es lo suficientemente alto para un buen funcionamiento, si estamos trabajando con consultas muy complejas tendríamos que subir el valor.

Podemos saber el valor actual de cada opción con:

show global variables like 'tmp_table_size';  

Además de estos parámetros existe un script MySQL Tuner que basándose en los logs de MySQL de nuestro servidor nos ofrece una configuración recomendada.

¿Cuánta memoria le damos a nuestra base de datos?

Pues después de todas las explicaciones vamos a ver como con los valores que acabamos de ver, podemos calcular de una manera optima el valor para la memoria de nuestra base de datos.
Formula mágica para memoria MySQL = keybuffersize + maxconnections * (joinbuffersize + readbuffersize + sortbuffersize + threadstack + tmptablesize)

¿Cómo optimizar consultas de búsqueda?

Para explicar mejor los puntos que afectan en la velocidad, voy a hacer uso de una base de pruebas que proporciona MySQL,con gran cantidad de registros para poder poner ejemplos de las consultas (Documentación mysql/Repositorio github).

Aprovecha al máximo la caché

Casi todos los servidores de MySQL tienen activo el sistema caché que nos ayuda a mejorar el rendimiento de la base de datos. El problema es que muchas veces no nos damos cuenta de que no lo estamos usando de la manera correcta.

// Cache no funciona
SELECT first_name FROM employees WHERE birth_date <= CURDATE();

// Cache funciona
SELECT first_name FROM employees WHERE birth_date <= '2016-10-10';  

Todos estos problemas suceden con todas las funciones de resultados variantes, otro ejemplo sería RAND() o NOW(), y tendríamos que considerar optimizar la consulta o deshabilitar la caché para la consulta.

No usar SELECT *

Puede que en el momento que escribas la consulta necesites todos los datos, pero en un futuro seguramente no muy lejano (la vida del ingeniero de software :P) la tabla sufra algunos cambios. life-of-a-software-developer Lo que implicaría:

  • Traer datos innecesarios que aumenta el tiempo de realizar todas las consultas a disco para obtener los datos.
  • Posibles problemas de seguridad.

Usar JOIN en lugar de encadenar SELECT

El anidamiento de SELECT, a parte de generar código ilegible y difícil de seguir casi siempre ofrece un peor rendimiento frente al uso de JOIN.

// Usando IN
SELECT first_name  
FROM employees  
WHERE emp_no IN (  
    SELECT emp_no
    FROM titles
    WHERE title LIKE 'Staff%'
);

// Mejor opción usando JOIN
SELECT first_name  
FROM employees as e  
INNER JOIN titles as t ON (e.emp_no = t.emp_no)  
WHERE t.title LIKE 'Staff%';  

También es importante destacar que si se utilizan indices en las columnas se debe utilizar el mismo tipo ya que MySQL solo será capaz de usar una de las columnas con índices en la comparación.

Limitar resultado

Cuando tengamos claro el número de resultados que queremos podemos hacer uso de LIMIT que nos devuelve la cantidad de resultados que queremos, Además de mejorar la velocidad de la consulta ayudarás a minimizar el daño que puede ocasionar un ataque SQL de este tipo.

SELECT first_name FROM employees;  
300024 rows in set (0,08 sec)

SELECT first_name FROM employees LIMIT 10;  
10 rows in set (0,00 sec)  

ORDER BY RAND() es el diablo 👹

Muchas veces puede ser un recurso interesante cuando queremos obtener los resultados de manera aleatoria, pero es una función que crece de manera exponencial y puede provocar verdaderos cuellos de botella en nuestras aplicaciones.

Analiza tus consultas

El uso de EXPLAIN nos muestra detalles internos de nuestras tablas, con los cuales podemos analizar y hacer mejoras para conseguir que nuestras consultas complejas sean más rápidas. El resultado de una query EXPLAIN nos muestra los índices que se utilizan, cómo se explora la tabla, cómo se está ordenando, etc…

Divide las consultas DELETE o INSERT grandes

Cuando tenemos una aplicación muy grande y con muchos usuarios simultáneos, hacer operaciones puede resultar muy costoso para nuestro servidor, pudiendo dejar nuestra aplicación bloqueada durante un periodo de tiempo o estropeando nuestro servidor Una solución para estos problemas es realizar nuestras operaciones por bloques haciendo uso de LIMIT en nuestras sentencias DELETE, o separar por bloques los inserts.

DELETE FROM salaries WHERE to_date <= '2000-01-01' LIMIT 10000;  

Optimizar consultas a una tabla con muchos registros

Existe una técnica que se usa cuando estamos tratando gran volumen de datos llamada "Sharding" o partición horizontal, que trata de dividir nuestros registros en distintas tablas consiguiendo un acceso a los datos mucho más rápido. Para particionar nuestra tabla lo tenemos que hacer con cierta consistencia, para que los datos quede agrupados. Por ejemplo:

  • Ubicación geográfica
  • Alfabeticamente