Pildoritas de Bases de Datos: Optimización. Explain Plan

Después de un parón algo más largo (pero no tan largo como el de verano), afrontamos ya el último de los posts de las Pildoritas de Bases de Datos. En él vamos a ver de forma muy básica como lanzar un Explain Plan y las ideas generales para poder interpretarlo, conforme a las cuales actuaremos en la optimización.

El Explain Plan es una funcionalidad que comparten todos los motores de BBDD y aunque cada uno tiene sus particularidades propias, se comparten ciertas ideas troncales para que cualquiera, independientemente de la tecnología, lo pueda entender.

El Explain Plan está asociado a las consultas y nos muestra un análisis de que tipos de recorridos se realizan en las tablas de consultas, lo cual nos sirve para saber si nuestra consulta es más o menos rápida. La forma de saber las diferencias de rendimiento de cada consulta y cada parte de una consulta es el coste.

Hay GUIs que dibujan en forma de árbol los explain plan, pero al final todos tiran de un comando más o menos igual, el EXPLAIN:

ORACLE

EXPLAIN  FOR [LA SELECT A ANALIZAR]

MySQL

EXPLAIN [LA SELECT A ANALIZAR]

Lo que más diferencia un explain plan realizado en diferentes tecnologias son los nombres de los accesos y los tipos de joins, aunque tienen relación suficiente para que uno lo entienda.

En el caso de acceso, hay dos grandes grupos: FULL TABLE SCAN e INDEX SCAN. No obstante dentro de estos grupos, como veremos, existen multitud de subcategorías.

Por otra parte también hay que tener en cuenta también como está haciendo el JOIN entre tablas nuestra BBDD. También veremos cuatro tipos de JOIN.

En ambos casos utilizaré los tipos de Oracle, no obstante otras tecnologías tienen, si no los mismos nombres, si muy parecidos.

Tipos de acceso

Como ya vimos en la otra pildorita de optimización, lo mejor siempre es que nuestra consulta tire de los índices y evitar así los FULL TABLE SCAN, los cuales recorren por completo la tabla y que, como es de imaginar, es el acceso más costoso. Esto no ocurre siempre, ya que a veces si el recorrido del índice es muy costoso un FULL TABLE puede dar mejor resultado.

Los recorridos de índices pueden ser de cuatro tipos en Oracle, aunque, como ya he aclarado antes, en el resto de tecnologías veremos “cosas” parecidas:

Index Unique Scan

El acceso busca una única clave a través de un índice único. Eso significa que siempre la búsqueda devolverá un resultado, en otro caso ya no sería UNIQUE. Un ejemplo claro de este tipo de acceso es el que hacemos cuando buscamos a través de la PK.

Index Range Scan

Como su nombre indica, en este caso no buscamos por un único valor, sino por un rango de ellos, por lo que nos devolverá más de un registro. Esto ocurre cuando usamos los operadores de rango (<, >, <>, <=, >=, BETWEEN).

Index Full Scan

Esta es la peor de las decisiones con índices y para que ocurra deberemos de no filtrar por el índice y que sea el motor el que decida que este acceso es más óptimo que un FULL SCAN, lo cual es raro, ya que, como os comenté en su momento, recorrer un índice es costoso y mejora la optimización sólo si está ordenado y se recorre un rango “pequeño” de registros de la tabla. Ahora bien, si por ejemplo queremos tirar un ORDER BY de una tabla sin filtrado y este orden es el utilizado por el índice, en ese caso entonces el motor decidirá acceder por esta vía.

Estas pautas de accesos son a nivel de lectura de los registros de la tabla. También hay otra parte muy importante que debemos tener en cuenta: Como se realizan los JOINs.

Tipos de JOIN

Nested Loop

Este es el JOIN típico. Se recorren los registros de A, y luego se prueba que cada uno de los registros de A esté en B. Es por eso que en otros capítulos dábamos las pautas de que hay que intentar siempre filtrar al máximo la tabla de unión (en el caso exacto la A) y que esta tabla fuente tiene que ser, si es posible, la más pequeña de ambas (incluyendo filtrados).

Hash Merge Join

Esto no está en todos los motores, así que no entraremos demasiado en el tema. Simplemente basta saber que este caso es incluso más eficiente que el Nested Loop, ya que la utilización de un Hash Map para la recuperación de datos es mucho más eficiente.

Sort Merge Join

Este es el peor JOIN que podemos encontrar y tiene lugar cuando hay que ordenar previamente el resultado de ambas tablas, lo que genera mayor carga de datos.

Producto Cartesiano

Esto es el terror de las SELECT, ya que recorre ambas tablas por completo. Esto ocurre porque la consulta está mal escrita y falta el JOIN entre ambas tablas.

 

Y con estos conceptos creo que podéis defenderos ante un EXPLAIN PLAN y entenderlo lo suficiente para hacer las tareas de optimización típicas en desarrollos. Claro está este tema puede profundizarse muchísimo. El Explain Plan muchas veces indica al motor como tiene que ejecutar la consulta, es más, si lanzamos un EXPLAIN PLAN – al menos en Oracle – de una consulta, su rendimiento mejorará, ya que el motor lo almacena en caché. Por ejemplo, existe un parámetro, OPTIMIZER_MODE (nuevamente hablo de Oracle), que sirve para indicar si queremos optimizar la consulta para búsquedas de todos los campos, para devolver todos los campos, para seguir reglas definidas por nosotros…pero como digo, esto ya es tarea de un DBA y yo por desgracia sólo soy una humilde (y pobre) desarrolladora.

Y ahora ya sí que sí ponemos punto y final a las Pildoritas de Bases de Datos. Esta ha sido la primera vez que me animo a escribir sobre un tema técnico y aunque seguramente haya cometido más de un fail, deseo de corazón que estas pildoritas os hayan hecho disfrutar una décima parte de lo que yo lo he hecho escribiéndolas. No sólo ha sido un reto el conseguir organizar las ideas para explicarlas de forma comprensible a otros, sino que además me ha servido para volver a refrescar muchas cosas que ya había olvidado.

Algunos pensaréis que es imposible unir contenido técnico y disfrute en la misma frase, pero para que veáis que hablo en serio os diré que ya tengo en mente una segunda serie de Pildoritas en mente, esta vez sobre POO y/o Java. Todavía es una idea embrionaria, y ni siquiera he pensado aún sobre cómo preparar y organizar el temario, ni que incluir en él exactamente. No obstante, me apetece muchísimo seguir haciendo Pildoritas, por lo que esto no es un Adiós, sino un Hasta Luego 🙂

Índice de Pildoritas

Share