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.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
De que trabajas cielo??? No quieres cambiar ,en serio?
ResponderEliminarmuy buen aporte, me gustaria saber mas de este tema.
ResponderEliminarHola 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¿Qué error te devuelve el Retrieve de la Datawindow?
ResponderEliminarMuy últil, como aplicación del punto 1(el más complejo) el siguiente link:
ResponderEliminarhttp://ora-flashes.blogspot.com.es/2012/09/optimizacion-creacion-de-indices-en.html
Gracias por tu respuesta ;) Me guardo el link que parece útil.
EliminarGracias, Es un buen aporte. Solo para matizar un poco: Las consultas del punto 15 no son equivalentes.
ResponderEliminarTu 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