SQL Server Conociendo sobre indices

Introducción

Siempre es importante conocer los tipos de índices que se manejan en el motor de base de datos de Microsoft, ya sea de Microsoft SQL Server o Azure SQL Database, de esta forma, en esta entrega hablare de los índices.

Es importante recordar que un índice de base de datos es una estructura de datos que mejora la velocidad de las operaciones de recuperación de datos en una tabla de base de datos a costa de escrituras adicionales y espacio de almacenamiento para mantener la estructura de datos del índice. Los índices se utilizan para ubicar datos rápidamente sin tener que escanear una tabla de la base de datos cada vez que se accede a una solicitud de consulta.

Actualmente se pueden catalogar en tres tipos:

• Índices agrupados (clustered indexes)
• Índices no-agrupados (non-clustered indexes)
• Índices únicos (unique Index)

Índices Agrupados

Los índices agrupados permiten ordenar y almacenar las filas de datos en una tabla o vista en función de sus valores clave. Es necesario indicar que un índice agrupado es una estructura que se define como unida a la misma tabla, son columnas incluidas en la definición del índice.

Es preciso indicar que sólo puede haber un índice agrupado por tabla, porque las filas de datos en sí mismas se pueden almacenar en un solo orden. Para Microsoft SQL Server, un índice es una estructura en disco asociada con una tabla o vista que mejora la velocidad de recuperación de filas de una tabla o vista. Un índice se compone de claves creadas a partir de una o más columnas en una tabla o vista. A este tipo de índices también se denominan como índice de almacén de filas porque es un índice de árbol B.

Microsoft SQL Server Clustered Index Representation

Estas claves que componen el índice se almacenan en una estructura (árbol B) que permite a Microsoft SQL Server encontrar las filas asociadas con valores clave de manera rápida y eficiente. Un árbol B es un árbol de búsqueda binario equilibrado, es un árbol que automáticamente mantiene su altura pequeña para una secuencia de inserciones y eliminaciones.

Para crear un índice agrupado se declara dentro de la creación de la tabla, como ejemplo se indicará la creación de una tabla de un directorio, de la siguiente forma:

--- Crear una tabla incluyendo indice agrupado

CREATE TABLE Directorio
(
id int NOT NULL PRIMARY KEY CLUSTERED,
Name varchar(100)
);

Otra forma de crear la misma tabla sería

--- Crear tabla incluyendo indice agrupado al final de las columnas

CREATE TABLE Directorio
(
id int NOT NULL,
Name varchar(100),
CONSTRAINT PK_Directorio_Id PRIMARY KEY CLUSTERED(Id)
);

Finalmente, es posible crear la tabla sin declarar algún índice y crearlo posteriormente, como se muestra a continuación:

--- Crear tabla y posteriormente crear indice

CREATE TABLE Directorio
(
id int NOT NULL,
Name varchar(100)
);

--- Crear indice agrupado para la tabla anterior

CREATE CLUSTERED INDEX PK_Directorio_id 

ON Directorio (Id);

Cualquiera de estos tres ejemplos crea la tabla nombrada directorio indicando que el índice agrupado este asociado a la tabla a través de la columna Id.

Con la llegada de la versión Microsoft SQL Server 2012 y para el uso de almacenes de datos, es posible llevara a cabo la definición de índices agrupados con almacenamiento de columnas. El índice incluye todas las columnas de la tabla y almacena la tabla completa. Si la tabla existente es un índice agrupado o de pila, la tabla se convierte en un índice de almacén de columnas agrupado. Si la tabla ya está almacenada como un índice de almacén de columnas agrupado, el índice existente se elimina y éste se reconstruye.

Para crear un índice agrupado con almacenamiento de columnas se usa la siguiente sentencia:

--- Crear un indice agrupado con almacenamiento de columnas

CREATE CLUSTERED COLUMNSTORE IXCS_Directorio
ON Directorio (Id, Name);

Indices No-Agrupados

Un índice no agrupado es aquel que contiene los valores clave del índice y los localizadores de filas que apuntan a la ubicación de los datos de la tabla en el almacenamiento. Se pueden crear varios índices no agrupados para una tabla o vista indexada. Los índices no agrupados están diseñados para mejorar el rendimiento de las consultas de uso frecuente que no están cubiertas por el índice agrupado.

Es importante tener en cuenta que los índices no agrupados tienen una estructura separada de las filas de datos, por lo que un índice no agrupado contiene los valores clave del índice no agrupado, y cada entrada de valor clave tiene un puntero a la fila de datos que contiene dicho valor clave. El puntero a una fila de índice en un índice no agrupado a una fila de datos se llama localizador de filas. La estructura del llamado localizador de filas depende de cómo se almacenan las páginas de datos en un montón o en una tabla agrupada.

Los índices no agrupados también tienen una estructura de árbol B como los índices agrupados, considerando que las filas de datos de la tabla subyacente no se ordenan ni almacenan en orden de acuerdo con sus claves no agrupadas y que el nivel de hoja de un índice no agrupado está compuesto de páginas de índice en lugar de páginas de datos.

En los índices no agrupados también podemos identificar los siguientes subtipos:

• Índice filtrado (filtered Index): se trata de un índice no agrupado optimizado, especialmente adecuado para admitir consultas que seleccionan de un subconjunto de datos bien definido. Un predicado de filtro se utiliza para indexar solo una parte de las filas de la tabla. Vale la pena señalar que un índice filtrado bien diseñado puede mejorar el rendimiento de la consulta, lo que ayuda a reducir los costos de mantenimiento del índice y a reducir los costos de almacenamiento del índice en comparación con los índices de tabla completa.
• Índice de cobertura (covered index): este tipo de índice puede incluir columnas sin clave en un índice no agrupado y, al mismo tiempo, evita exceder las limitaciones actuales de tamaño de índice con un máximo de 16 columnas de clave y un tamaño máximo de clave de índice de 900 bytes. Generalmente, el Motor de base de datos no considera columnas sin una clave al calcular el número de columnas de clave de índice o el tamaño de la clave de índice. Las columnas sin clave se definen en la cláusula INCLUDE de la instrucción CREATE INDEX.
• Índice de almacenamiento de columnas (columnstore index): una tecnología para almacenar, recuperar y administrar datos mediante el uso de un formato de datos en columnas, denominado almacenamiento de columnas. Cuando se habla de índices de almacenamiento de columnas, los términos almacenamiento de filas y almacenamiento de columnas se utilizan para enfatizar el formato para el almacenamiento de datos. Los índices de almacén de columnas utilizan ambos tipos de almacenamiento.

 

SQL Server Non-Clustered Index Representation

Para la creación de los índices no agrupados, se requiere usar la sentencia CREATE INDEX, a continuación, veremos un ejemplo para cada uno de los subtipos indicados.

--- Indice No Agrupado
CREATE NONCLUSTERED INDEX IX_Directorio_Name 
ON Directorio (Name);

--- Indice No-Agrupado Filtrado
CREATE NONCLUSTERED INDEX IXF_Directorio_Friend 
ON Directorio (Name) 
WHERE Friend = 1;

--- Indice No-Agrupado de Cobertura
CREATE NONCLUSTERED INDEX IXC_Directorio_NameFriend 
WITH (Friend);

ON Directorio (Name) 

--- Indice No-agrupado de almacenamiento de columna
CREATE NONCLUSTER COLUMNSTORE INDEX IXCS_Directorio
ON Directorio (id,name);

Índices únicos

Un índice único en una tabla o vista es aquel en el que no se permite que dos filas tengan el mismo valor de clave de índice. Es importante indicar que un índice agrupado en una vista debe ser único. No se permite la creación de un índice único en columnas que incluyen valores duplicados, independientemente de que la opción IGNORE_DUP_KEY esté establecido en ON o no. Es importante mencionar, si se intenta la creación en una tabla con duplicados en la columna seleccionada, se muestra un mensaje de error. Los valores duplicados deben eliminarse antes de que se pueda crear un índice único en la columna o columnas. Es requerido que las columnas que se utilizan en un índice único deben establecerse en NOT NULL, ya que varios valores nulos se consideran duplicados cuando se crea un índice único.

Es importante indicar que aunque se indica como un tipo diferente a los índices agrupados y no agrupados, este tipo puede ser asociado a los anteriores, en general si no se especifica alguno de los tipos anteriores, por omisión se considera un índice no-agrupado.

Para crear un índice único, se puede utilizar la siguiente sentencia:

--- Crear índice único al crear la tabla
CREATE TABLE Directorio
(
Id int PRIMARY KEY,
Name varchar(100) NOT NULL UNIQUE 
);

--- Crear un índice unico
CREATE UNIQUE INDEX IXU_Directorio_Name 
ON Directorio (Name);

Conclusión

A partir de Microsoft SQL Server 2005 se permitían hasta 249 índices no agrupados por tabla, mientras que a partir de Microsoft SQL Server 2008 se permite hasta 999 índices no agrupados por tabla.

Como se ha visto, los índices son estructuras que apoyan el desempeño de las consultas, la existencia de índices en una base de datos incrementa el tamaño de esta, así que, aunque el máximo numero de índices en una tabla es de 1000, se hace necesario llevar a cabo un análisis de la necesidad y relevancia de los índices que se utilizan. 

Es importante mencionar que existen otros aspectos a considerar cuando se crea un índice, como el factor de relleno, si se utilizara la base de datos tempdb para llevar a cabo el ordenamiento, o si debe ser creado para uso en un grupo de archivos, particiones o usar paralelismo, consideraciones que no se han indicado en esta ocasión.