Índices en SQL Server

Qué es un índice

Un índice consiste en una estructura de punteros a registros o grupos de registros asociados a una tabla. Esta estructura contiene claves asociadas a una o varias columnas de la tabla. La organización de estos punteros a los datos se realiza de manera que se reduzcan Ias búsquedas cuando éstas sean necesarias, distingue los diferentes métodos de indexación. Un índice resulta más eficiente cuantas menos comprobaciones sobre la estructura deba realizar para encontrar un registro.

Los registros de la tabla tienen un tamaño fijo y se almacenan en ficheros, de manera que cada registro queda perfectamente determinado por su posición en el fichero. De esta forma los punteros a los datos que se almacenan en los índices no contienen más que la posición de estos registros en el fichero.

SQL Server almacena los índices como un objeto más de la base de datos. Los objetos de SQL Server se almacenan en un modelo orientado a la página y no al registro, la indexación no se lleva a cabo en ese modelo de puntero fijo, en este modo de almacenamiento, los índices se almacenan en páginas separadas, dentro de la misma base de datos.

Por qué crear índices

La existencia de índices supone mejoras en la búsqueda de registros. Si deseamos buscar una palabra en todo un libro puede resultar lento y engorroso, pero también la podemos buscar en el índice del libro en el cual nos indicará en que página se encuentra y podremos encontrarla mucho más rápidamente. Los índices en SQL Server funcionan de forma similar al ejemplo descrito.

Esta analogía descrita se denomina unclustered, porque no ordena las palabras físicamente en el libro. A pesar de su utilidad los índices conllevan penalizaciones pues ocupan un cierto número de páginas que encarece el libro, y si éste se actualiza añadiéndole, un nuevo capítulo, debe reorganizarse el índice lo que supone un coste operacional adicional. Por tanto, la creación de índices está sujeta a un cierto compromiso entre la rapidez y el tamaño ocupado, así como su operatividad. Si el libro no va a cambiar (la tabla no se actualiza) y todo lo que vamos a hacer es consultarlo (sólo van a existir sentencias SELECT, el único coste es el papel (espacio en disco) y podremos crear todos los índices que queramos (siempre que dispongamos de espacio suficiente). Cuantos más índices, más rápido permitirá encontrar las palabras. Pero los índices no sólo sirven en el caso de consultas, también tienen utilidad en la actualización ya que, en ciertos casos, pueden acelerar las operaciones de UPDATE y DELETE, porque el gestor normalmente debe realizar una búsqueda de un registro antes de poder eliminarlo o actualizarlo. Una vez se ha llevado a cabo la actualización, los índices deberán ser actualizados. Si el coste de actualización, de índices es inferior a la mejora obtenida en la búsqueda, el índice mejorará el rendimiento. Esto sucede si el número de índices de la tabla no es muy alto.

Existen dos tipos de índices en SQL

SQL Server clustered indexes.
Son índices que se reflejan en la ordenación física de los datos, en otras palabras son índices que al generarse reordenan la tabla. Desde el punto de vista del modelo relacional los registros de una tabla no tienen ningún tipo de ordenación. Para que en una tabla los registros se ordenen de una cierta manera, es necesario crear un índice de tipo clustered.

SQL Server unclustered indexes.
Opuesto al caso anterior, es posible definir un índice que sea estrictamente una ordenación lógica de los datos, sin que se produzca después de su definición ningún tipo de modificación de la ubicación física de los registros. Este tipo de índices se denominan unclustered.

Si una tabla no posee ningún índice clustered, los datos se van añadiendo de manera secuencial en la última página de datos asignada a la tabla. Esta forma de almacenamiento aunque es la más sencilla es también muy poco eficiente a la hora de acceder a la información. Para acceder a un registro, será preciso recorrer todas las páginas de datos anteriores examinando cada uno de los registros que se almacenan en ellas. Es por ello que es altamente recomendable definir un índice clustered para cada una de las tablas de nuestras bases de datos.

Los índices pueden o no ser únicos. En el primer caso, no se permite que existan dos registros que tengan el mismo valor para las columnas que sirven de clave para el índice.

Selectividad de los índices

Un índice es más útil cuanto más selectivo sea. Entendemos por selectividad de un índice la cantidad de acotamiento que produce en los registros y este acotamiento es inversamente proporcional al número de registros que comparten un mismo valor de las columnas clave del índice.

Por ejemplo, un índice único, como puede ser el creado a partir de un identificador único en una tabla de clientes (el Nº de cliente), es muy selectivo. Sin embargo, un índice creado por el nombre del cliente sería muy poco selectivo, pues seguro que hay muchos clientes que se llaman igual.

La selectividad de un índice puede cambiar con los datos así, un índice que pueda ser muy selectivo hoy, no lo sea en absoluto cuando se inserten masivamente registros. SQL Server puede generar estadísticas de selectividad y distribución de los índices, y las almacena en páginas especiales, que evalúa  el optimizador de consultas  con el objetivo de determinar si es adecuado y eficiente utilizarlo un determinado índice para responder a una cierta consulta. Estas estadísticas se deben actualizar con frecuencia y es posible consultarlas con el comando DBCC SHOW_STATISTICS.

Por ejemplo:

DBCC SHOW_STATISTICS ("DBO.tbExpedientes", pk_tbExpedientes);

GO

Muestra las estadísticas del índice pk_tbExpedientes de la tabla tbExpedientes del propietario dbo.

Definición de índices

Existen dos métodos para definir índices: explícitamente, utilizando el comando  CREATE INDEX o implícitamente, al crear una tabla.

Comando CREATE INDEX

Para crear un índice hay que ejecutar el comando ya comentado CREATE INDEX. Un índice básico de tipo unclustered por defecto sería así:

CREATE INDEX pk_tbExpedientes2

    ON  documenta.TbExpedientes (strExpediente);

GO

Si queremos crear un índice clustered lo tendremos que especificar explícitamente. En la lista de columnas del ejemplo se ha puesto sólo strExpediente pero podemos poner varias indicando las claves por las que se ordenarán los elementos en el árbol balanceado del índice. Puede especificarse cualquier combinación de columnas  siempre que no se incluyan columnas de tipo, text, ntext, image o bit.

El parámetro UNIQUE especifica que el índice será único, por tanto no aceptará que dos registros de la tabla tengan la misma combinación de columnas de índice. Cuando se crea un índice con la cláusula UNIQUE, el servidor examina si existe alguna combinación de columnas repetida para el índice, si la encuentra falla la creación del índice. Si se crea con éxito, cada operación de inserción y actualización será comprobada por el servidor para asegurarse que no se produzcan violaciones de esta restricción.

Distribución del índice

Los parámetros PAD_INDEX y FILLFACTOR especifican el espacio interior y externo, respectivamente, que quedará vacío en las páginas de índice al crearlo, con el objetivo de que ese espacio pueda ser ocupado en las modificaciones que se apliquen al índice deban al ir alterándose los datos de la tabla.

FILLFACTOR, especifica un porcentaje a dejar vacío en las páginas. Especificar  un valor distinto de 0 (el valor por defecto) aumenta el espacio necesario para crear el índice, pero mejora el rendimiento en las actualizaciones de la tabla.

Creando índices en la definición de la tabla

Al crear una tabla, se automáticamente un índice si se especifican las siguientes cláusulas en la sentencia de creación CREATE TABLE 

PRIMARY KEY.  Esta cláusula crea una clave primaria, esto crea un índice de valores únicos sobre las columnas de la clave.

UNIQUE. Define una columna con valores únicos y se crea un índice de tipo UNIQUE.

CLUSTERED. Crea un índice clustered.

NON CLUSTERED. Crea un índice unclustered.

El siguiente es un ejemplo de uso: ovas TABLE clientes (idciiente in PRINARY KEY CLUSTERED, nombre char (50) , direccion cher( . DNI char (25 ) UNIQUE NONCLUSTERED

Por ejemplo:

CONSTRAINT [pk_tbNombreTabla] PRIMARY KEY CLUSTERED

(

          [id] ASC,

           [strCodigo] ASC,

     [DNI] UNIQUE NONCLUSTERED)

)

Estructura de los índices

Árboles balanceados.

La estructura de los índices en SQL Server es una estructura es que denominamos árbol balanceado o b-tree. Un árbol balanceado (b-tree), organiza las búsquedas siguiendo una ramificación determinada a través de un árbol en el que cada rama se conecta a otras dos o a un nodo terminal u boja. En este tipo de árboles de búsqueda el camino seguido para encontrar un registro nunca es más de un 145 % del camino óptimo.

Representación gráfica de la estructura de árbol balanceado.

Tablas e índices

Cuando una tabla tiene definido un índice clustered, las páginas de datos están distribuidas con una estructura de lista doblemente enlazada, en la que en la cabecera de cada página se encuentra el identificador de cada una y los punteros anterior y siguiente.

Cuando la tabla tiene una estructura de heap, es decir, cuando no hay índice clustered definido, no existe lista doblemente enlazada.

La creación de un índice, idependientemente que sea clustered o no, conlleva la generación de una estructura de árbol balanceado separada. Cada tabla tiene una colección de páginas de datos, y colecciones de páginas adicionales para implementar cada índice definido para ella.

La tabla sys.indexes.  

Existe una tabla que contiene información sobre los índices, estos están representados en la tabla sys.indexes y sustituye a la antigua sysindexes que se mantiene operativa aun por compatibilidad. Cada tabla y cada índice tienen un  registro en la tabla sys.indexes. En el enlace hay una completa explicación de la interpretación de cada valor de cada campo. El campo type_desc indica si el índice es clustered, unclustered o heap.

Tipos de índice

Índices agrupados (Clustered indexes)

Este tipo de índices es muy útil cuando los datos sean seleccionados de forma frecuente y se incluya en la selección  una o varias columnas.

Se pueden definir índices que se reflejen en la ordenación física de los datos. En caso de definir un índice como clustered, sólo será posible definir un índice de este tipo para cada tabla, Pues sólo es viable un tipo de  ordenación física. Al crear un índice de este tipo, las páginas de datos se ordenan lógicamente siguiendo la clave que da cuerpo al índice y posteriormente se re-ordenan físicamente según el mismo criterio.

Es similar a la guía telefónica, donde el nombre está dispuesto en una forma ordenada y luego el número de teléfono en frente. 

Tomado de Skatageri

Una vez se ha producido está ordenación de las páginas de datos, el sistema genera una serie de páginas que contienen punteros a las páginas físicas, tantas como sean necesarias para hacer referencia a todos los registros. Estas páginas intermedias podrán a su vez ser agrupadas en páginas intermedias de mayor nivel que contengan punteros a ellas, si esto es necesario, de este modo se crea un árbol balanceado con tantos niveles como sea necesario. Este proceso de agrupación se repetirá hasta que exista una única página base o raíz del árbol.

Las búsquedas sobre tablas indexadas según este criterio es mucho más rápida, siempre que se seleccionen siguiendo la clave. En cada búsqueda por clave sólo será necesario explorar, como máximo, un número de páginas igual al número de niveles del árbol balanceado.

Índices no agrupados (Unclustered indexes)

En estos índices no se ordenan los datos de la tabla. En este caso se crea una estructura de punteros siguiendo el modelo de árbol balanceado, que permitirá acceder de manera más rápida a los datos según la clave generadora del índice.

Son similares a los índices de palabras de algunos libros en los que aparece una lista de palabras o conceptos seguidos de la página en la que se encuentran.

El nivel más alto del árbol, las hojas, no serán las propias páginas de datos sino un conjunto de páginas que contienen una fila para cada uno de los registros de la tabla. Las filas de esas páginas contendrán como información el número de la página de datos en el que  se almacena el registro que posee la clave deseada.

 

Tomado de Skatageri

Herramientas para evaluar el rendimiento de la Base de datos

Cuanto más compleja es una tabla, es decir, cuantas más posibilidades diferentes de selección existan, más posibilidades de creación de un índice existirán. A mayor número de columnas en una tabla, mayor será  el número de índices que pueden crearse.

Lo que va a determinar lo idóneo de un índice es el optimizador de consultas, cuando genere los planes.

Para evaluar el rendimiento de la base de datos y poder así diseñar los índices apropiadamente disponemos de la posibilidad de ver el plan de ejecución de una consulta.

Ver el plan de ejecución de una consulta

Para ver el plan de ejecución de una consulta desde el Analizador de SQL Server. El primer paso consiste en crear una nueva secuencia de comandos SQL.

Una vez creada, se abrirá un cuadro de diálogo en el que podemos ver el plan de ejecución de la sentencia SQL.

Sobre el icono del índice, pulsando el botón derecho del ratón podemos ver más detalles sobre el rendimiento.

Para interpretar la salida de la ejecución gráfica hay que tener en cuenta las siguientes indicaciones.

-Cada nodo de la estructura en árbol se representa como un icono que especifica el operador lógico y físico utilizado para ejecutar esa parte de la consulta o instrucción.

-Cada nodo está relacionado con un nodo principal. Los nodos secundarios que tienen el mismo nodo principal se dibujan en la misma columna. Pero, no todos los nodos de la misma columna tienen porqué pertenecer necesariamente al mismo nodo principal. Cada conexión con su nodo principal se representa a través de reglas con puntas de flecha.

-Los operadores se muestran como símbolos relacionados con un nodo principal específico.

-El ancho de la flecha es proporcional al número de filas. Se utiliza el número real de filas cuando está disponible. Si no, se utiliza el número estimado de filas.

-Cuando la consulta contiene varias instrucciones, se dibujan varios planes de ejecución de la consulta.

Estas son las principales salidas con las que podemos encontrarnos y su interpretación.

Table Scan

Esto indica que el motor necesita leer completamente la tabla sin utilizar un índice. En la mayor parte de las situaciones, su aparición indica que es necesario  crear un índice o reestructurarlo si ya existe. Aunque no siempre es así, el motor siempre intenta predecir los costos de ejecución basados en las estadísticas que va almacenando, si se estima que va ser más rápido leer toda la tabla en vez de leer un índice, usará ese método. Esto suele suceder con tablas con pocos datos y la consulta en cuestión no conlleva ningún filtro.

Clustered Index Scan

Es similar a Table Scan, pues recorre completamente la tabla pero utilizando ésta vez alguno de los índices clustered de los que dispone. Aparece normalmente en tablas con muchos datos.

Clustered Index Seek

Si aparece este epígrafe, es síntoma de un correcto uso de los índices clustered en la base de datos.

Index Scan

Similar a  Table Scan y Clustered Index Scan, la diferencia es que éste utiliza un índice Non-Clustered para recorrer la tabla. Muchas veces puede ser síntoma de un mal uso de los índices, aunque también puede aparecer cuando usamos las cláusulas ORDER BY, JOIN o GROUP BY.

Index Seek

Index seek, es igual que Clustered Index Seek, pero trabajará con un Índice Non-Clustered.

Bookmark Lookup

No podremos evitar tener este tipo de operación en la Base de Datos pero si se debe buscar es que sea mínima su aparición y cuando lo hace, lo haga con un costo bajo. Aparece cuando se necesita hacer un salto desde el apuntador de un  índice non-clustered a la páginas de datos reales. Una forma de evitar su aparición excesiva, es limitar los campos requeridos en la consulta y sólo solicitar los que están incluidos en el índice, Es un buen motivo para no hacer consultas del tipo SELECT * FROM.

Si fuera estrictamente necesario en algunos casos extremos, se puede considerar la inclusión de todas las columnas de la tabla dentro del índice.

En los índices Non-Clustered se puede agregar o copiar el contenido de las columnas, sin que estas sean parte del índice en sí, en otras palabras, no sirven para realizar búsquedas ni filtros pero cuando sea necesario mostrarlos se dispondrá de “una copia” de los datos, sin tener la necesidad de usar el puntero para buscar en la tabla el dato. Esta característica bien utilizada puede reducir considerablemente el costo de las consultas, pero al tener más datos los índices crecen rápidamente porque ocupan más espacio y por tanto se tienen menos claves por cada página de índice lo que puede llegar a aumentar el nivel de entradas y salidas. Así que, es necesario considerar muy detenidamente el contexto para decidirse a utilizarlo.

RID Lookup

Este operador no es muy frecuente, aparece si el motor intenta optimizar por su cuenta la consulta y no existe un índice agrupado (Clustered) entonces buscará a través del índice único ROW ID (RID).

Sort

Cuando el motor necesita ordenar un campo que no está indizado, por ejemplo al aplicar ORDER BY, GROUP BY, TOP, etc. Cuando aparece hay que estar atentos por si falta algún índice en alguna tabla, aunque no siempre es así, llenar de índices la Base de Datos tampoco es recomendable. La necesidad de ejecutar algunas consultas ocasionalmente no justifica la creación de otro índice más.

JOIN El operador JOIN entra en acción cuando precisamente cuando se hace uso de la cláusula JOIN para unir dos o más tablas en la consulta y está determinado normalmente por el volumen de datos con el que se trabajará para que SQL Server elija usar uno u otro. Nested Loop Join Este operador se muestra cuando el volumen de datos con el que se trabajará es relativamente pequeño. Merge Join Este operador al igual que el anterior se muestra en la unión de tablas, pero con un volumen de datos mucho más grande, (cientos de miles de filas). Hash Join Este tipo de JOIN es muy específico para grandes volúmenes de datos, sobre todo si no están indizados. Si aparece en nuestra base de datos OLTP, es indicativo de que funcionará mejor sobre diseños OLAP, así que si no estamos trabajando con DATA WAREHOUSING, es muy poco probable que sea útil. Hash Match Cuando aparece este operador el motor de base de datos está comparando contenido que puede aparecer en un JOIN, o en un WHERE y son lugares donde no debe estar, si lo hace es por falta de índices. Donde sí son muy útiles es cuando incluimos la cláusula DISTINCT, UNION, UNION ALL, donde no sólo se compara el valor de un campo, sino todo un conjunto de columnas o incluso de filas y columnas. Stream Aggregate Aparece cuando agrupamos los datos, y mezclamos con funciones agregadas como MIN, SUM, AVG, también con las cláusulas HAVING o SORT si se utiliza para ordenar los datos antes de agrupar. Aquí la continuación de índices en SQL Server.