Índices Nonclustered (Índices parte 4)

Continuamos nuestra serie de Índices en SQL Server. Después de una introducción y de profundizar en las tablas HEAP y los índices clustered ha llegado el turno de los índices nonclustered. Durante este blog vamos a profundizar en su funcionamiento, cuál es la mejor estrategia para crearlos y responderemos a la pregunta de si son buenos o malos para el rendimiento.

Estructura de un índice nonclustered

En la introducción de esta serie comentamos que los índices nonclustered son como una copia de la tabla que solo incluye las columnas que hemos elegido. Profundicemos en esto, un índice nonclustered se crea sobre las mismas páginas de 8Kb que el resto de objetos en SQL Server. En ellas se almacenan las columnas que hemos añadido al índice ordenadas ascendente o descendentemente según elijamos y un puntero hacia la ubicación física del resto de columnas de esas filas. Al igual que pasaba con los índices clustered sobre estas columnas se crea una estructura de árbol B o árbol invertido desde un nivel raíz hasta los datos. Podemos verlo en esta imagen sacada de la documentación oficial de Microsoft:

Cuando se crea un índice nonclustered sobre una tabla HEAP o una vista que no tenga un índice clustered (más sobre vistas indexadas aquí) el nivel de hoja contendrá el RID (identificador de fila) de las filas además de las columnas incluidas en el índice. Si por el contrario se crea sobre una tabla con un índice clustered, el nivel hoja incluirá las columnas del índice clustered. Adicionalmente, solo si el índice clustered no es único, se incluirá también un identificador de fila.

Filtrar por un índice nonclustered

Cuando filtras por una columna incluida en un índice nonclustered SQL Server buscará en ese índice los valores deseados. Independientemente de lo que pase después esto es mucho más rápido que leer la tabla completa en busca de un valor y se reduce en gran medida el consumo de recursos de E/S. Sin embargo aquí pueden pasar dos cosas, puede que nuestro índice incluya todas las columnas que deseamos recuperar en cuyo caso no habrá que leer más o puede que necesitemos más información de la que contiene el índice. En este caso, usará el puntero para saltar directamente a la ubicación de esos datos sin hacer lecturas innecesarias. Cuando un índice incluye todos los campos que requiere la consulta se dice que el índice cubre la consulta y es lo más óptimo en cuanto a rendimiento de lecturas.

Buenas prácticas 

Acabamos de ver que lo mejor para nuestras lecturas será que nuestro índice nonclustered cubra completamente nuestras consultas, sin embargo, todo tiene un pero. A mi me gusta comparar los índices nonclustered con la típica mantita que todos tenemos en el sofá, esa que si te tapas hasta arriba te deja los pies fuera y si te tapas los pies no te arropa hasta arriba. Igual que la mantita, nuestros índices tienen sus cosas malas y para arreglar una cosa empeoran otra. Como hemos comentado, los índices nonclustered son objetos separados de la tabla por lo que cada vez que escribamos (o modifiquemos) un dato en la tabla se tiene que modificar en todos los índices que contengan esa columna. Esto degrada el rendimiento de las escrituras.

Tenemos que tener esto muy en mente a la hora de diseñar los índices nonclustered ya que no nos podemos pasar creando índices nonclustered. Tampoco debemos crear índices nonclustered en columnas que tengan pocas lecturas por cada escritura o incluso más escrituras que lecturas.

Columnas clave o columnas incluidas

Si después de analizar todo esto aún nos cuadra el índice nonclustered, tenemos que tener en cuenta la limitación de 16 claves por índice. Tranquilos si no habéis entendido esto, he metido un concepto nuevo que ahora os explico.

Las columnas que forman parte de un índice nonclustered pueden ser claves o incluidas. Llamamos columnas clave a las columnas que forman parte del índice y por las que vamos a poder filtrar. Sin embargo para poder cubrir más consultas en ocasiones nos interesará añadir otras columnas al índice por las que no se va a filtrar pero si vamos a querer recuperar. Para esto tenemos la opción de incluir columnas. Es importante que no vayamos a filtrar por las columnas incluidas ya que estas no se van a escribir en los niveles raíz e intermedios de nuestro índice nonclustered y solo van a estar en el nivel hoja. Si antes decíamos que un índice nonclustered solo puede tener 16 columnas clave, como columnas incluidas podemos tener hasta 1023. 

¿Qué columnas añado a mi índice?

A estas alturas ya sabemos las implicaciones para el rendimiento de los índices y la teoría, llega el momento de ponernos a crearlo. Hemos visto que no es recomendable crear índices con claves muy largas así que, crearemos las columnas clave que normalmente se usan para filtrar o agrupar los datos (en el where, join, group by o having de nuestras consultas). Si necesitamos agregar alguna consulta más para cubrir completamente las consultas lo haremos dentro de la cláusula INCLUDE.

Conclusión

Hemos visto cómo funcionan los índices nonclustered y como son un arma de doble filo para el rendimiento de nuestra base de datos. Sin haberlo comentado hasta ahora, pero interiorizando los conceptos que hemos aprendido también vamos a ser capaces de entender por qué no es una buena idea añadir todas las columnas de nuestra tabla en un índice nonclustered. Otra enseñanza que nos llevamos es el hecho de qué si un índice nonclustered puede cubrir completamente las consultas hacer select * es una práctica a evitar si queremos mejorar el rendimiento. 

Contenido de la serie

Introducción a los índices en SQL Server (Índices parte 1)

Estructura física de las tablas y HEAP (Índices parte 2)

Índices Clustered y Primary Keys (Índices parte 3)

Índices Columnares (Índices parte 5)

Índices especiales (Índices parte 6)