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.

Anuncios

Un comentario en “20 consejos para optimizar consultas SQL

Por cada comentario que haces, Dios salva un gatito

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s