jueves, 30 de abril de 2015

Covering indexes



Los índices de cobertura, son aquellos que nos ayudan a mejorar el rendimiento de ciertas consultas que utilizan un índice y que cuyo resultado incluye un mismo conjunto de columnas.

Tal como en las anteriores entradas, vamos a aterrizar esto en un ejercicio para que se vea la utilidad de estos elementos.

Tomaremos la base de datos AdventureWorks como ejemplo y partiremos del siguiente query:

SELECT ProductID, Name, ProductNumber, MakeFlag
FROM Production.Product
WHERE Color = 'White'


Si revisamos el plan de ejecución, notaremos que se está llevando a cabo un barrido del CLUSTERED INDEX de la tabla Production.Product:


Con un costo estimado de 0.0127253, el cual ciertamente es bastante bajo, pero esto se debe a la poca cantidad de elementos que tiene nuestro conjunto.

¿Qué es lo primero que haríamos para mejorar el rendimiento de esta consulta?, claro, irnos a la parte del WHERE y analizar el predicado.    Después de revisarlo podemos proponer un índice sobre la columna Color de la tabla Production.Product.

CREATE INDEX ixColor
ON Production.Product (Color)


Vamos a revisar nuevamente nuestro plan de ejecución:


Notaremos que hay un cambio importante, ahora se está realizando una búsqueda en el índice ixColor, justamente el que acabamos de crear.    Notemos también que se está llevando a cabo una tarea Key Lookup que sirve para encontrar las columnas que nos faltan para poder dar el resultado, las cuales son ProductID, Name, ProductNumber y MakeFlag.

El costo de este query cambió de 0.0127253 a 0.0126079, lo cual significa una variación menor al 1%, en verdad que no es nada impresionante si lo comparamos contra el costo que tendrá para la base de datos el mantener al día este nuevo índice respecto a los cambios que sufra la tabla a la que apunta.

Pero bueno, tomemos en cuenta que tenemos sólo 504 filas en la tabla de productos, también es por ello que la variación es tan pequeña.

Ahora, vamos a ahondar un poco más en el Key Lookup:


Nuestro índice ixColor, sólo guarda los valores del Color y una liga hacia la tabla Production.Product donde se almacena la fila a la que pertenece, de ahí que se tenga que hacer ese Key Lookup para encontrar los datos que le hacen falta al query para devolver el resultado completo.     Notemos que en la imagen aparecen en Output List Name, ProductNumber y MakeFlag.

¿Qué pasa si siempre regresamos las mismas columnas?, ¿qué pasa si este query regresa un subconjunto de columnas que habitualmente se ocupan?    Si este es el caso, estamos ante la perfecta opción de utilizar un índice de cobertura (covering index)

¿Qué es un covering index?, es aquel que almacena también los valores que forman parte del resultado, es decir, que ya no debe de ir a la tabla original por ellos dado que tiene una copia actualizada en él.

Vamos a crearlo:

CREATE INDEX ixColorCovering
ON Production.Product (Color)
INCLUDE (Name, ProductNumber, MakeFlag)


Y analicemos nuevamente el plan de ejecución para ver los cambios:


Podemos notar que ahora se está utilizando únicamente nuestro índice ixColorCovering.    ¿Qué costo tiene actualmente el query?, el costo es de 0.0032864, lo cual significa una mejora del 74.17%.

¿Qué tal?, ahora sí impresiona ¿no?, y eso que estamos hablando de una tabla con 504 filas, ahora imagina que estuviéramos trabajando en una tabla con miles o millones de filas, verás dos impactos mayúsculos:
  1. Tiempo de respuesta muchísimo menor.
  2. Menor uso de memoria de SQL Server para poder despachar el resultado.
Antes de dar por finalizada esta entrada, quisiera remarcar lo siguiente: cuidado con los índices, recuerda que cada vez que generas un nuevo índice, SQL debe actualizar su valor si es que el valor original en la tabla fue alterado, esto significa que una simple escritura se puede traducir en muchas más.

Espero te haya resultado de utilidad esta entrada, nos vemos la siguiente.

1 comentario:

  1. Excelente ejemplo y muy practico. Yo sabía de los índices de cobertura pero tenia un error conceptual en cuanto al uso de las columna en la clausula INCLUDE. Pensaba que ese listado de columnas que se pone ahí, eran columnas que eventualmente se podían utilizar en la clausula WHERE... Pero ahora que veo tu ejercicio ya me quedo clarísimo .. Felicitaciones...!!

    ResponderBorrar