Particionamiento vertical

20 consejos para optimizar consultas SQL

1.- Elije el motor de almacenamiento mas apropiado

Los principales motores de búsqueda son MyISAM e InnoDB, pero existen muchos otros como Aria, TokuDB, XtraDB o archive.

MyISAM es mas apropiado en aquellos casos en los que predominen las consultas SELECT.

Sus principales características son:
– Bloqueo a nivel de tabla
– Mayor velocidad en consultas SELECT
– Alta compresión
– Búsquedas full-text
– No soporta claves foráneas

InnoDB es mas apropiado si predomina los INSERT, UPDATE o DELETE

Sus principales características son:
– Bloqueo a nivel de fila
– Permite claves foraneas
– Búsquedas full-text a partir de la versión 5.6.4
– Permite transacciones

Mención especial para Aria (como alternativa a MyISAM) y Archive (para almacenar logs).

 

2.- Evita select *

Selecciona tan solo aquellas columnas que realmente vayas a utilizar.

Seleccionar mas columnas de las necesarias aumenta el tiempo que tarda en ejecutarse la consulta y el tiempo de transferencia si la BBDD está en un servidor independiente.

Bien:
SELECT id, titulo FROM tareas;

Mal:
SELECT * FROM tareas;

 

3.- Limita el n.º de resultados

Utiliza una clausula LIMIT si solo necesitas una determinada cantidad de resultados o si estás filtrando por id y por lo tanto solo hay un resultado como máximo.

Bien:
SELECT id, titulo FROM tareas where id = 555 LIMIT 1;

Mal:
SELECT id, titulo FROM tareas where id = 555;

4.- Evita subconsultas

Por cada subconsulta que agregues, MySQL va a realizar una query adicional por cada registro de la query principal.

Si tienes una consulta que muestra 100 resultados, realizará 100 consultas adicionales por cada subconsulta.

Bien:
SELECT t.id, t.nombre, s.salario
FROM trabajadores t
JOIN salarios s ON s.trabajador = t.id

Mal:
SELECT t.id, t.nombre, (SELECT salario from salarios s where s.trabajador = t.id) as ‘salario’
FROM trabajadores t

 

5.- Guarda direcciones IP como unsigned int

Guarda las direcciones IP como un entero sin signo. PHP permite convertir una IP con ip2long y long2ip para revertir el proceso.

6.- Optimiza la consulta para la cache

Algunas funciones impiden a MySQL cachear la consulta, por ejemplo, la función now() obtiene la fecha/hora actual cada vez que se ejecuta.

Una solución sería obtener la fecha en el lenguaje que estés usando junto con SQL (PHP, JAVA, etc)

 

7.- Join con el mismo tipo de columna

Hacer join con una columna del mismo tipo y tamaño es mas rápido.

8.- Autoincrementales unsigned

Los autoincrementales no pueden ser negativos, estableciendo la columna como unsigned ganas un bit extra, lo que permite guardar valores mas grandes.

Tipo Bytes Mínimo Máximo Unsigned
TINYINT 1 -128 127 No
0 255 Si
SMALLINT 2 -32768 32767 No
0 65535 Si
MEDIUMINT 3 -8388608 8388607 No
0 16777215 Si
INT 4 -2147483648 2147483647 No
0 4294967295 Si
BIGINT 8 -9223372036854775808 9223372036854775807 No
0 18446744073709551615 Si

 

9.- EXISTS en lugar de IN

Utiliza exists siempre que sea posible, ya que EXISTS deja de buscar cuando encuentra una coincidencia.

 

10.- Evita usar WILCARD (%)

El wilcard (%) penaliza seriamente el rendimiento, utilizalo solo si es estricamente necesario.

Mal:
SELECT id, usuario, password
FROM usuarios
WHERE usuario LIKE ‘%bruno%’;

Bien:
SELECT id, usuario, password
FROM usuarios
WHERE usuario = ‘bruno’;

 

11.- Utiliza CHAR en lugar de VARCHAR

CHAR es hasta un 50% mas rápido que VARCHAR, aunque tiene dos incovenientes:
– Ocupa mas espacio en disco
– Tiene un límite de 255 caracteres.

 

12.- Utiliza el tipo de columna ENUM

Enum es un tipo de columna que permite almacenar un listado de valores que internamente se guardan como un TinyInt.

Es ideal para almacenar valores fijos, como por ejemplo: colores, sexo, estados, etc.

13.- Evita usar ORDER BY RAND

Cuando agregas la claúsula ORDER BY RAND, lo que MySQL va a hacer es:
– Seleccionar TODOS los registros que cumplan con tu claúcula WHERE
– Los carga en memoria o en una tabla temporal
– Asociar valores aleatorios a cada fila
– Los ordena
– Muestra N resultados de acuerdo a la claúsula LIMIT

Algunos motores de BBDD, como Oracle, ni tan siquiera permiten realizar esta acción.

14.- Guarda los logs en el motor ARCHIVE

Archive está pensado para guardar un gran volumen de datos.

Permite INSERT y SELECT, pero no DELETE, REPLACE o UPDATE, aunque puedes hacer un TRUNCATE para vaciar la tabla.

Este motor es un buen candidato para almacenar logs.

15.- Las columnas pequeñas son mas rápidas

Utiliza siempre el tamaño justo para la columna.

Por ejemplo, si vas a guardar un DNI, no tiene sentido tener un campo de 20 caracteres, ya que el DNI está formado por 9 caracteres.

 

16.- Particionamiento vertical

En ocasiones ocurre que tenemos tablas con columnas que no son estricamente necesarias.

Imagina una tabla que se emplea para inciar sesión y que además tiene información adicional del usuario.

Tabla sin particionar

Dado que el nombre, apellidos y el DNI no son necesarios para iniciar sesión, lo mas lógico es mover las columnas a otra tabla (ficha_usuario) y referenciar a la tabla usuarios.

Particionamiento vertical

17.- Usan explain

Explain permite obtener información acerca de como se va a ejecutar la consulta, para así optimizarla.

Ejemplo:
EXPLAIN SELECT t.id, t.nombre, s.salario
FROM trabajadores t
JOIN salarios s ON s.trabajador = t.id

 

18.- Indexa las columnas sobre las que ejecutes búsquedas

Por ejemplo, en la tabla libros, sería apropiado indexar el nombre del libro o el ISBN, dependiendo del uso que le estés dando.

Indexar campos búsqueda

19.- Benchmark

La función BENCHMARK ejecuta una exprensión N veces. Se utiliza para conocer cuán rápido ejecuta MySQL la expresión.

El resultado de la consulta siempre es 0 (no va a seleccionar NADA), lo importante es el tiempo que tarda en ejecutar la consulta.

SELECT BENCHMARK(500000,ENCODE(‘hola’,’adios’));
+———————————————-+
| BENCHMARK(500000,ENCODE(‘hola’,’adios’)) |
+———————————————-+
|                                            0 |
+———————————————-+
1 row in set (2.46 sec)

20.- Sígue a The Linux Alchemist

Seguir a The Linux Alchemist aumenta el rendimiento de todas las base de datos en un 125%, trust me I’m an engineer.

Límite phpMyAdmin 2MB

Cómo aumentar el límite de 2MB al importar en phpMyAdmin

phpMyAdmin bajo Ubuntu tiene un límite a la hora de importar un fichero SQL de 2MB, esto no se debe tanto a la configuración de phpMyAdmin, sino a la configuración por defecto de php, que tiene un límite de 2MB por fichero y hasta 8MB por petición POST.
Límite phpMyAdmin 2MB

 

Configuración implicada

Las líneas de configuración causantes son:
post_max_size que limita el máximo de MB que puedes enviar en una petición POST.
upload_max_filesize que limita el máximo de MB que puede pesar cada fichero que envíes por POST.
*Ambas en el fichero de configuración /etc/php/7.0/apache2/php.ini

Por ejemplo, si post_max_size = 40 y upload_max_filesize = 10 puedes enviar un máximo de 40MB por POST y cada fichero no puede pesar mas de 10MB.

 

Modificar configuración

Para editar el fichero sirve cualquier editor, mientras seas root
sudo gedit /etc/php/7.0/apache2/php.ini
sudo pluma /etc/php/7.0/apache2/php.ini
sudo kate /etc/php/7.0/apache2/php.ini
sudo geany /etc/php/7.0/apache2/php.ini
sudo nano /etc/php/7.0/apache2/php.ini

Editando las siguientes dos líneas se soluciona el problema, yo suelo poner  un número alto (200MB normalmente), que puede no ser recomendable en producción, pero para desarrollar en local sirve.
post_max_size = 200M
upload_max_filesize = 200M

 

Reiniciar apache2

Para aplicar los cambios es necesario reiniciar apache.

apache2-restart

sudo service apache2 restart

o bien:

apache2ctl restart

sudo apache2ctl restart

¡Y listo!