Buenas prácticas en el uso de índices - La selectividad

Introducción

Los índices mejoran el rendimiento de las consultas que seleccionan un pequeño porcentaje de las filas de una tabla. La selectividad de los índices es uno de los conceptos más importantes a la hora de entender si un índice puede ser útil en esos casos. Para comprender el concepto, vamos a analizar las consecuencias de la selectividad de un índice no agrupado (NON CLUSTERED) sobre una columna de una tabla de la base de datos de ejemplo AdventureWorks que tiene el SQL Server 2008. Los accesos puntuales, que es lo que vamos a analizar en este caso, no son la única utilidad que nos ofrecen los índices para mejorar los costos, pero es importante comprender por qué en ocasiones no se utilizan índices existentes.

La motivación

Uno de las características fundamentales del SQL Server 2008 (y versiones anteriores también) es que la selección de las estrategias de ejecución del motor relacional se encuentran basadas en los costos. De esta manera cobra vital importancia la creación de índices para reducir estos costos.

Cuando creamos índices una de las posibles utilidades que ofrecen es reducir la carga impuesta a la base de datos permitiendo accesos puntuales a los registros a partir del índice lo que habitualmente se conoce como INDEX SEEK, para luego acceder en un segundo paso a los registros de la tabla. Si no existe el índice, en lugar de que el motor de la base de datos pueda acceder puntualmente, tiene que explorar todos los registros de la tabla o índice agrupado, lo que se conoce como TABLE SCAN o CLUSTER INDEX SCAN.

Ambas estrategias de acceso tienen sus costos. Por lo tanto la selección que hace el motor de la base de datos deberá estar de acuerdo a los costos. Además, cada una de las estrategias tiene sus consecuencias.

Si tenemos índices que no se utilizan simplemente estaríamos aumentando el trabajo que tiene que realizar el SQL Server para mantener actualizados los índices cuando se realizan modificaciones y estaríamos aumentando el espacio de almacenamiento usado por guardar las estructuras de los índices tanto en memoria como en disco. Por lo tanto es importante entender algunos conceptos importantes para crear buenos índices.

La selectividad

Vamos a definir la selectividad de un índice, como el porcentaje promedio de filas en una tabla que tienen el mismo valor en las columnas involucradas en el índice, esto equivale al cociente (ratio) entre el número de valores distintos en las columnas que definen el índice y el número de registros en la tabla. La selectividad ideal es 1 y representa el caso en que todas las entradas del índice son únicas. Esta selectividad puede ser garantizada sólo por índices únicos. SQL Server crea automáticamente índices en las columnas de todas las restricciones de integridad de clave primaria (PRIMARY KEY) y únicas (UNIQUE) que de definen. Esto ocurre porque generalmente estos índices son los más selectivos y más eficientes para optimizar el rendimiento ya que permiten seleccionar al motor de la base de datos estrategias particulares.

Quizá sea más simple comprender el inverso multiplicativo de la selectividad que representa el promedio de filas de igual valor que se van a obtener en el caso de que exista un condición por igualdad en la clausula WHERE de la sentencia.

Veamos un simple ejemplo en la base de datos AdventureWorks de nuestro SQL Server 2008.

USE AdventureWorks

GO

SELECT COUNT (DISTINCT ProductID) FROM Sales.SalesOrderDetail

266

SELECT COUNT(*) FROM Sales.SalesOrderDetail

121317

Por lo tanto vemos que la selectividad de nuestro índice por la columna ProductID es 266/121317 0.002192602 lo que representa una selectividad regular ya que nuestro ideal es 1. Si vemos el inverso multiplicativo, lo que equivale a hacer121317/266 obtenemos 456.07 que representa el número promedio de registros cuando buscamos las filas de un producto en nuestra tabla de detalle de órdenes.

Veamos las consecuencias a la hora de realizar consultas.

Ejemplo 1

SELECT * FROM Sales.SalesOrderDetail WHERE productID = 870

El plan resultante es el siguiente

Como podemos ver el plan refleja el hecho de que existen muchos registros con el productID = 870 y por lo tanto le resulta más eficiente realizar en SCAN. La selectividad de la condición no es adecuada para un acceso puntual.

Ejemplo 2

SELECT * FROM Sales.SalesOrderDetail WHERE productID = 719

El plan resultante es el siguiente

En este caso el SQL Server elige realizar un SEEK del índice NON CLUSTERED porque determina que es más eficiente. Luego debe realizar un KEY LOOKUP para obtener el resto de las columnas que no se encuentran en el índice. Si comparamos estos dos casos podemos ver claramente que a diferentes valores de productID, el SQL Server determina en cual es el mejor método de acceso analizando la selectividad de cada valor de productID en particular.

Ejemplo 3

DECLARE @ProdId INT

SET @ProdId = 719

SELECT * FROM Sales.SalesOrderDetail WHERE productID = @ProdId

El plan resultante es el siguiente

En este ejemplo vemos que a pesar de que el valor es el mismo del ejemplo anterior, debido al uso de la variable no puede conocer el valor específico al momento de compilar y optimizar, y por lo tanto utiliza el caso promedio que corresponde a la selectividad promedio y por lo tanto realiza un SCAN independientemente del valor de la variable.

Vamos a dejar para otro artículo el tema de los parámetros de los procedimientos almacenados ya que requiere un análisis particular.

La densidad

Si buscamos en la documentación del SQL Server encontramos que el comando DBCC SHOW_STATISTICS nos permite ver la información estadística usada para seleccionar el plan de ejecución. Dentro de esta información se encuentran las densidades y un histograma disponible para la primera columna del índice. Esta fue la información que permitió determinar los planes de ejecución en los ejemplos anteriores.

La densidad se define como:

Por lo tanto si multiplicamos la densidad por la cantidad de registros de la tabla obtenemos el promedio de valores iguales o el inverso multiplicativo de la selectividad.

DBCC SHOW_STATISTICS ( 'Sales.SalesOrderDetail' , 'IX_SalesOrderDetail_ProductID')

Dentro de la información que nos presenta está:

Si multiplicamos por la cantidad de filas de la tabla obtenemos 0, 003759399 * 121317 = 456,07 que es el mismo valor que antes.

Conclusión

Un índice puede ser selectivo de acuerdo al valor utilizado. Si el índice no es selectivo, el SQL Server no lo va a utilizar para acceder a los registros mediante un SEEK y va a preferir realizar un TABLE SCAN. No tiene sentido crear índices para acceder puntualmente a un subconjunto de registros de la tabla en el caso de no ser selectivos. Existen otros motivos para crear índices como cover index que justifican en algunos casos índices no tan selectivos.