15 consejos para Optimizar nuestras consultas SQL

Vamos a ver 15 formas de optimizar nuestras consultas SQL. Muchas son comunes y conocidas por todos los que nos dedicamos a esto. Otras quizá sean menos obvias y os sirvan de ayuda. Son simples instrucciones para que el DMBS ("DataBase Management System") -o como lo llamamos en mi entorno, el Motor de la Base de Datos- realice sus operaciones más rápido.

1.- Índices

Indexar nuestras columnas es una de las maneras más comunes y sencillas de optimizar nuestras consultas. Sin embargo, se debe tener un profundo conocimiento sobre cómo funciona el indexado en cada DMBS para utilizar correctamente sus índices. Dicho de otro modo: crear índices sin sentido, sencillos o absurdos sin comprender exactamente cómo funciona nuestra Base de Datos puede tener justamente el efecto contrario al deseado en nuestras consultas, y hacer que funcionen aún más lentas.

2.- Símbolos Operacionales

Los símbolos operacionales como >,<,=,!=, etc. son muy útiles en nuestras consultas. Se pueden optimizar algunas consultas si la columna con el símbolo operacional en cuestión esta indexada. Por ejemplo:   SELECT * FROM MI_TABLA WHERE COLUMNA > 16 ;Esta consulta no está optimizada, ya que el motor de la Base de Datos debe buscar el valor 16, y DESPUÉS escanear hacia delante del 16 y por detrás.   SELECT * FROM MI_TABLA WHERE COLUMNA >= 17 ;De este modo, el DMBS debe saltar directamente a los valores mayores a 16. Es casi la misma manera, pero evitas que el DMBS tenga que comparar también los valores menores para ver si entran en la query.

3.- Comodines

En SQL, el comodín se nos presenta con el símbolo ‘%’ (se puede ver un truco acerca de él en mi artículo anterior Buscar el tanto por ciento (%) en un LIKE de una SELECT). Usar comodines ralentiza bastante nuestras consultas, especialmente si la tabla en la que buscamos en bastante grande. Se pueden optimizar dichas consultas si podemos permitirnos poner el comodín únicamente como comodín-sufijo, en vez de como comodín-prefijo o como comodín-total.

#Comodín-total:
SELECT * FROM MI_TABLA WHERE COLUMNA LIKE '%manzana%';
#Comodín-sufijo:
SELECT * FROM MI_TABLA WHERE COLUMNA LIKE 'manzana%';
#Comodín-prefijo:
SELECT * FROM MI_TABLA WHERE COLUMNA LIKE '%manzana';
Esta columna debe estar indexada para que se aplique algo de optimización. P.D: Hacer un comodín-total en una tabla con varios millones de registros puede suponer que tires abajo la Base de Datos.

4.- Operador NOT

Intenta evitar todo lo posible el operador NOT en SQL. Es mucho más rápido buscar por un valor exacto (operador positivo) como por ejemplo un LIKE, IN, EXISTS o el símbolo operacional =, en vez de usar un operador negativo como NOT LIKE, NOT IN, NOT EXIST o el símbolo != . Usar un operador negativo provoca que la búsqueda tenga que recorrer cada línea por separado identificar los que no son o que existen dentro de la tabla. Sin embargo, usando un operador positivo, la búsqueda para en cuanto se encuentra el resultado.

5.- COUNT Vs EXIST

Muchos de nosotros solemos usar el operador COUNT para determinar si existe un dato en particular. SELECT COLUMNA FROM MI_TABLA WHERE COUNT(COLUMNA) > 0 ; Este sistema es muy malo, ya que el COUNT debe contar cada registro de la tabla para ver cuantos hay. La mejor alternativa es usar el operador EXISTS, que para en cuanto encuentra el primer registro que concuerda con la búsqueda, sin tener que contarlos todos. Por lo tanto, existe.

6.- Comodín Vs Substr

Muchos desarrolladores indexamos nuestras tablas. Por lo tanto, si una columna en particular está indexada, es mejor usar un comodín para buscar en ella en vez de un Substr. El comodín usará el índice, el Substr no.

#MAL
SELECT * FROM MI_TABLA WHERE Substr(COLUMNA, 1, 1) = 'pepito' ; Esta consulta hará Substr a cada registro de la tabla individualmente para rastrear el valor ‘pepito’. Pero del siguiente modo:

#MEJOR
SELECT * FROM MI_TABLA WHERE COLUMNA = 'pepito%' ;
La consulta con comodín corre mucho más rápido en el DBMS. Ejemplo:
#BUSCAR TODAS LAS FILAS QUE EL PRIMER CARACTER ES 'E'
SELECT * FROM MI_TABLA WHERE COLUMNA = 'E%' ;

7.- Columna con Índice Único

Algunas Bases de Datos (como MySQL) buscan mucho mejor en columnas que son únicas e indexadas (unique & indexed). Por esto mismo, es mejor recordar indexar por estas columnas que son únicas. Y si la columna es realmente de este modo, declararlas así. Aún así, si la columna nunca se va a usar para propósitos de búsqueda, no hay motivos para indexar esta columna en concreto, aunque se quede como única.

8.- Funciones Max y Min

Las funciones Max y Min buscan el valor Máximo y Mínimo de una columna. Podemos optimizar estas búsquedas colocando un índice en esas columnas concretas. Corrección: Podemos usar Max y Min en columnas que ya están indexadas. Pero si dicha columna se usa frecuentemente, tener un índice además debería ayudar a acelerar dichos operadores Max y Min. Decidir si poner un índice sólo para acelerar los Max y Min no es aconsejable. Los índices son caros de mantener, y crear indices sólo para acelerar Max y Min es una locura. Es como sacrificar el bosque entero por un único árbol.

9.- Tipos de Datos

Se deben usar los tipos de datos más eficientes (más pequeños) siempre que sea posible. Es innecesario, y algunas veces hasta peligroso, proporcionar tipos de datos enormes cuando se pueden solucionar un problema con tipos más pequeños. Por ejemplo, usando los tipos de datos enteros más pequeños para así tener tablas más pequeñas. MEDIUMINT es a veces una elección mejor que un INT, porque una columna MEDIUMINT uses 25% menos de espacio en disco. Por otro lado, VARCHAR es mejor que LONGTEXT para almacenar texto como un email, unas observaciones o pequeños detalles.

10.- Índices Primarios

La primera columna que se use para un indexado debe ser lo más corta posible. Esto hace que la identificación de cada fila sea más sencilla y eficiente por parte del DBMS.

11.- Indexado de cadenas

No es necesario indexar toda la cadena cuando en su lugar se pueden indexar un prefijo o sufijo de la cadena. Es aconsejable llevar a cabo dicha indexación especialmente si el prefijo o sufijo de la cadena proporciona un identificador único para la cadena.
Los índices más cortos son más rápidos, no sólo debido a que requieren menos espacio en disco, sino porque también evitan que se acceda a ellos menos veces y a la caché de índice, y por lo tanto menos búsquedas en disco.

12.- Limitar el resultado

Otro método muy común para optimizar tu query es minimizar el número de filas devueltas. Si en una tabla tienes varios billones de registros y lanzas una simple pero potente query sin limitación, puedes echar a bajo la Base de Datos entera. SELECT * FROM MI_TABLA ; No seas perezoso. Si sólo necesitas una cantidad de registros, trata de limitar el resultado. De esta forma no sólo serás más eficaz, sino que ayudarás a minimizar el daño que puede ocasionar un ataque por SQL de este tipo. SELECT * FROM MI_TABLA WHERE 1 LIMIT 10 (Sólo en MySQL) ;

13.- Usa el Valor por Defecto

Si usas MySQL u Oracle, aprovéchate de la ventaja que tienen estas Bases de Datos por el hecho de tener columnas con valores por defecto. Inserta valor explícitamente únicamente cuando sean diferentes de los valores por defecto. Esto reduce el análisis (parsing) que MySQL u Oracle deben hacer y mejora la velocidad de los INSERTS.

14.- Subquery en un IN

Muchos de nosotroso usamos una sub-consulta (o subquery) dentro de un operador IN, tal que así:
SELECT * FROM MI_TABLA WHERE COLUMNA IN ( SELECT COLUMNA2 FROM MI_TABLA2 ); Hacer esto es muy caro para el DBMS porque la consulta SQL debe evaluar la query exterior antes que la interior. En vez de esto, podemos usar lo siguiente:
SELECT * FROM MI_TABLA, (SELECT COLUMNA2 FROM MI_TABLA2) as dummytable WHERE dummytable.COLUMNA2 = MI_TABLA.COLUMNA;
Usando una dummytable (o tabla tonta) es mejor que usar un operador IN para hacer una sub-consulta. Como alternativa, un operador EXIST también es mejor.

15.- UNION en vez de OR

Los índices pierden su velocidad cuando se usan en situaciones de OR (en MySQL al menos) SELECT * FROM MI_TABLA WHERE COLUMNA_1 = 'pepito' OR COLUMNA_2 = 'manolito';
Si hacemos la query anterior usando 2 consultas con UNION, estas sí usarán sus índices. SELECT * FROM MI_TABLA WHERE COLUMNA_1 = 'pepito'
UNION
SELECT * FROM MI_TABLA WHERE COLUMNA_2 = 'manolito'

De hecho, corre más rápido.

Sumario

Estos consejos de optimización no garantizan que tus consultas/querys de SQL no se conviertan en cuellos de botella. Tampoco se garantiza que esas consultas que tienes pesadas, enormes y lentas sean inmediatas. Pero sí pueden hacer que se aligeren un poco. Parar hacerlas perfectas, se requiere mucho más análisis comparativo y conocimientos más profundos para optimizarlas a nivel máximo.
Este artículo ha sido traducido y adaptado de un documento que encontré por internet hace tiempo, y me lo guardé. Se puede consultar el original y mandar los agradecimientos en este link: http://www.docstoc.com/docs/69264261/15-Ways-to-Optimize-Your-SQL-Queries

8 comentarios:

  1. De que trabajas cielo??? No quieres cambiar ,en serio?

    ResponderEliminar
  2. Hola muy buen aporte, estoy aplicando lo del "12.- Limitar el resultado" y envio como parametro la cantidad de filas a retornar, pero me genera un error a la hora de ejecutar el Retrieve del DW. Estoy usando PB12.5 y MySql. ME puedes ayudar con esto por favor.

    ResponderEliminar
  3. ¿Qué error te devuelve el Retrieve de la Datawindow?

    ResponderEliminar
  4. Muy últil, como aplicación del punto 1(el más complejo) el siguiente link:
    http://ora-flashes.blogspot.com.es/2012/09/optimizacion-creacion-de-indices-en.html

    ResponderEliminar
    Respuestas
    1. Gracias por tu respuesta ;) Me guardo el link que parece útil.

      Eliminar
  5. Gracias, Es un buen aporte. Solo para matizar un poco: Las consultas del punto 15 no son equivalentes.

    ResponderEliminar
  6. Tu aporte es bueno pero antes de llegar a esto pudiste comentar sobre hacer un análisis del plan de ejecución de la consulta para saber, por donde esta la debilidad de la consulta y empezar a optimizar

    ResponderEliminar

Mi foto
Geek y Friki de amplio espectro pero de baja intensidad. Bloguero, forero y jugón online. Y Papá. Cada vez con menos tiempo para los hobbies.
Experto en PowerBuilder.
¿Te ha gustado? Haz un +1 en Google!

También te puede interesar...