Usando índices correctos para aumentar la velocidad de las consultas

Cada vez que escribimos un SELECT o una vista tenemos dos opciones:

1. Que esté optimizado
2. Que no esté optimizado

Que esté optimizado significa que se ejecuta con la mayor velocidad posible. Leyendo solamente el SELECT que escribimos muchas veces puede ser difícil comprobar si está optimizado o no, sobre todo cuando dicho SELECT es muy largo y hay varias tablas involucradas en él.

Afortunadamente disponemos de programas gráficos que nos facilitan muchísimo esa tarea. En este artículo se usa el programa EMS SQL Manager (tiene una versión Lite que es gratis). Veamos un ejemplo:

Tenemos una tabla llamada SUCURSALES que tiene estas filas:

(haciendo click en la imagen la verás más grande)

Esta tabla tiene un índice llamado UQ_SUCURSALES que está compuesto por la columna SUC_CODIGO

 Tenemos una tabla llamada BANCOS que tiene estas filas:

(haciendo click en la imagen la verás más grande)

Esta tabla tiene un índice llamado PK_BANCOS que está compuesto por estas dos columnas: BAN_CODSUC y BAN_IDENTI

Escribimos un SELECT y luego verificamos si está optimizado o no:

SELECT
   B.BAN_CODSUC,
   S.SUC_NOMBRE AS BAN_NOMSUC,
   B.BAN_IDENTI,
   B.BAN_NOMBRE
FROM
   BANCOS     B
JOIN
   SUCURSALES S
      ON B.BAN_CODSUC = S.SUC_CODIGO

Hacemos click en «Explain query» y vemos esto:

(haciendo click en la imagen la verás más grande)

que es el PLAN utilizado por el Firebird y que nos indica que la tabla B (o sea BANCOS) no está usando un índice, por eso aparece la palabra «NATURAL» a su derecha. La tabla S (o sea SUCURSALES) sí está usando un índice y ese índice se llama UQ_SUCURSALES.

También podemos verlo de forma gráfica haciendo click en «Performance Analysis»

(haciendo click en la imagen la verás más grande)

 La tabla de SUCURSALES sí está usando un índice, pero aunque esa tabla tiene solamente 4 filas fueron procesadas 16 filas. Eso está mal y debe ser mejorado.

A su vez, la tabla BANCOS no está usando un índice. Si lo que queremos es obtener todas las filas sin un orden específico esto está bien, es lo correcto. ¿Por qué? porque el orden NATURAL se procesa más rápido que cualquier índice. Sin embargo, si queremos que las filas sean mostradas ordenadas según algún otro criterio, no usar índice está mal y debe ser mejorado.

Rápidamente podemos saber si una tabla está usando un índice o no mirando el color del cilindro: un color azul significa que sí usa un índice y un color rojo granate significa que no usa un índice.

¿Por qué la tabla de BANCOS no está usando un índice?

Porque nunca se le pidió al Firebird que usara un índice en esa tabla

¿Cómo se le pide al Firebird que use un índice en un SELECT?

Mediante las cláusulas WHERE, JOIN, ORDER BY

Veamos ahora lo que sucede cuando escribimos este otro SELECT:

SELECT
   B.BAN_CODSUC,
   S.SUC_NOMBRE AS BAN_NOMSUC,
   B.BAN_IDENTI,
   B.BAN_NOMBRE
FROM
   BANCOS     B
JOIN
   SUCURSALES S
      ON B.BAN_CODSUC = S.SUC_CODIGO
WHERE
   B.BAN_CODSUC >= 0

Hacemos click en «Explain query» y vemos esto:

(haciendo click en la imagen la verás más grande)

O sea que ahora sí la tabla de BANCOS está usando un índice. Y al hacer click sobre «Performance Analysis» vemos esto:

(haciendo click en la imagen la verás más grande)

¿Y qué pasa si en el WHERE en lugar de escribir B.BAN_CODSUC >= 0 escribimos B.BAN_IDENTI > 0?

Después de todo, el índice está compuesto por ambas columnas: B.BAN_CODSUC y B.BAN_IDENTI, así que la pregunta es ¿obtendremos el mismo resultado?

SELECT
   B.BAN_CODSUC,
   S.SUC_NOMBRE AS BAN_NOMSUC,
   B.BAN_IDENTI,
   B.BAN_NOMBRE
FROM
   BANCOS     B
JOIN
   SUCURSALES S
      ON B.BAN_CODSUC = S.SUC_CODIGO
WHERE
   B.BAN_IDENTI > 0

El nuevo PLAN es este:

(haciendo click en la imagen la verás más grande)

Lo cual nos indica que la tabla S (o sea: SUCURSALES) no está usando un índice. Y si hacemos click sobre «Performance Analysis» obtenemos:

(haciendo click en la imagen la verás más grande)

Finalmente, cambiamos nuevamente la cláusula WHERE y observamos lo que ocurre:

SELECT
   B.BAN_CODSUC,
   S.SUC_NOMBRE AS BAN_NOMSUC,
   B.BAN_IDENTI,
   B.BAN_NOMBRE
FROM
   BANCOS     B
JOIN
   SUCURSALES S
      ON B.BAN_CODSUC = S.SUC_CODIGO
WHERE
   B.BAN_CODSUC >= 0 AND
   B.BAN_IDENTI > 0

Ahora el PLAN del SELECT es este:

(haciendo click en la imagen la verás más grande)

El cual nos indica que ambas tablas (SUCURSALES y BANCOS) están usando índices. Y al hacer click sobre «Performance Analysis» vemos:

 (haciendo click en la imagen la verás más grande)

Conclusión:

 Cuando la consulta está optimizada devuelve sus resultados mucho más rápidamente que cuando no lo está. Por lo tanto debemos siempre tratar de que todas nuestras consultas estén optimizadas. Para ello contamos con dos ayudas:

• el PLAN de la consulta
• el Análisis de la Performance de la consulta

Mediante el PLAN podremos saber cuales tablas están usando índices y cuales son los índices que utilizan. A veces el Firebird utiliza un índice que no es el más apropiado y en ese caso podemos obligarle a que use otro índice mejor (mira el enlace de más abajo)

Mediante el Análisis de la Performance podremos saber que tan eficiente es el uso de un índice. No se tienen los mismos resultados con cualquier índice, algunos son mejores que otros

El PLAN y el Análisis de la Performance debemos usarlos juntos, no nos servirá de mucho usar al uno sin el otro. Si solamente usamos el PLAN no sabremos que la performance es baja aunque se esté usando un índice. Si solamente analizamos la performance no sabremos cual índice se está usando y por lo tanto tampoco sabremos que se puede usar otro índice con el cual obtener mejores resultados.

Cuando lo que queremos es obtener los datos y no nos interesa obtenerlos con un orden en especial entonces utilizar NATURAL en la tabla principal (la que se encuentra a continuación de la cláusula FROM) es lo mejor, ya que es lo más rápido que podemos conseguir.

Artículos relacionados:

https://firebird21.wordpress.com/2013/04/30/usando-un-plan/

https://firebird21.wordpress.com/2013/05/03/algo-mas-sobre-plan/

https://firebird21.wordpress.com/2013/05/02/esta-la-vista-optimizada/

https://firebird21.wordpress.com/2013/05/07/detectando-una-consulta-que-esta-tardando-mucho/

https://firebird21.wordpress.com/2013/03/09/consultas-lentas-causas-y-soluciones/