Indices en Microsoft SQL Server

Supón que buscamos en un libro de Química el tema de los “Metales Alcalinos”, lo primero que tendríamos que hacer es recurrir al índice para localizar en que página se encuentra la información que necesitamos, de similar forma SQL crea índices en las tablas para realizar búsquedas de forma mas eficiente en este video tutorial te muestro como crear índices en una tabla de Microsoft SQL Server y abajo explicamos los conceptos relacionados.

Como SQL Server accede a los datos

SQL Server puede acceder a los datos en una tabla leyendo todas las páginas de la tabla (conocida como exploración de tabla) o usando páginas de índice para ubicar las filas requeridas. Cada página en un índice tiene un tamaño de 8 kilobytes (KB).
Cada vez que SQL Server necesita acceder a los datos en una tabla, tiene que elegir entre hacer un escaneo de tabla o buscar y leer uno o más índices. SQL Server elegirá la opción con la menor cantidad de esfuerzo para ubicar las filas requeridas.
Siempre puede resolver consultas leyendo los datos de la tabla subyacente. Los índices no son necesarios, pero acceder a los datos leyendo grandes cantidades de páginas es considerablemente más lento que los métodos que usan índices apropiados.
En ocasiones, SQL Server crea sus propios índices temporales para mejorar el rendimiento de las consultas. Sin embargo, hacerlo depende del optimizador y está fuera del control del administrador o programador de la base de datos; estos índices temporales no serán discutidos en este módulo. Los índices temporales solo se usan para mejorar un plan de consulta si ya no existe una indexación adecuada. Una tabla sin un índice se conoce como una “heap table”.

Necesidad de Índices

Los índices no se describen en las definiciones del Lenguaje de consulta estructurado (SQL) de ANSI. Los índices se consideran un detalle de implementación para el proveedor. SQL Server usa índices para mejorar el rendimiento de las consultas y para implementar ciertas restricciones.
Como se mencionó antes, SQL Server siempre puede leer toda la tabla para devolver los resultados requeridos, pero hacerlo puede ser ineficiente. Los índices pueden reducir el esfuerzo requerido para localizar resultados, pero solo si están bien diseñados.
SQL Server también usa índices como parte de su implementación de PRIMARY KEY y restricciones ÚNICAS. Cuando asigna una restricción PRIMARY KEY o UNIQUE a una columna o conjunto de columnas, SQL Server indexa automáticamente esa columna o conjunto de columnas. Lo hace para que sea posible verificar rápidamente si un valor dado ya está presente.
Una analogía útil
Es útil considerar una analogía con la que sea más fácil relacionarse. Considera una biblioteca física. La mayoría de las bibliotecas almacenan libros en un orden determinado, que es básicamente un orden alfabético dentro de un conjunto de categorías definidas.
Tenga en cuenta que incluso cuando almacena los libros en orden alfabético, hay varias formas de hacerlo. El orden de los libros podría basarse en el título del libro o el nombre del autor. Cualquiera que sea la opción elegida hace que una forma de búsqueda sea más fácil y otras búsquedas más difíciles. Por ejemplo, si los libros fueron almacenados por orden de título, ¿cómo encontraría los que fueron escritos por un autor en particular? Un índice en el título de un libro y un índice en el autor significaría que un bibliotecario podría encontrar libros rápidamente para cualquier tipo de búsqueda.

Estructura de Índices

Las estructuras de árbol definidas de esta forma porque pueden parecerse a la raíz de un árbol proporcionan capacidades de búsqueda rápida para un gran número de entradas en una lista.
Los índices en los sistemas de bases de datos a menudo se basan en estructuras de árbol binario. Los árboles binarios son estructuras simples donde en cada nivel se toma la decisión de navegar hacia la izquierda o hacia la derecha. Sin embargo, este estilo de árbol puede desequilibrarse rápidamente y ser menos útil; por lo tanto, SQL Server usa un árbol equilibrado.

Ejemplo de árbol binario

arbol

Utilizando la imagen de ejemplo de un árbol binario que almacena los valores de 1 a 200, considere cómo encontrar el valor 136. Si los datos se almacenaran al azar, cada registro debería examinarse hasta encontrar el valor deseado. Le tomaría un máximo de 200 inspecciones para encontrar el valor deseado.
Compare esto contra el uso de un índice. Coincidere buscar en la estructura de árbol binario el valor 136. Como es mayor que 100 tomara el camino derecho del árbol despues del nodo raíz, llegando al nodo 101-200. ¿136 es menor o mayor que 150? Es menor que, así que navega por el lado izquierdo. El valor deseado se puede encontrar en la página que contiene los valores de 126 a 150, ya que 136 es mayor que 125. Al observar cada valor en esta página para 136, se encuentra en el décimo registro. Por lo tanto, es necesario comparar un total de 13 valores, utilizando un árbol binario, frente a 200 inspecciones posibles en un montón aleatorio.
Los índices de SQL Server se basan en una forma de árbol autoequilibrado. Mientras que los árboles binarios tienen, como máximo, dos hijos por nodo, los índices de SQL Server pueden tener un mayor número de hijos por nodo. Esto ayuda a mejorar la eficiencia de los índices y reduce la profundidad total de un índice-profundidad que se define como el número de niveles desde el nodo superior (llamado nodo raíz) hasta los nodos inferiores (llamados nodos hoja).

Índice Agrupado

En lugar de almacenar filas de datos de datos como un montón o “Heap!, puede diseñar tablas que tengan un orden lógico interno. Este tipo de tabla se conoce como un índice agrupado o rowstore.
Una tabla que tiene un índice agrupado tiene un orden predefinido para las filas dentro de una página y para las páginas dentro de la tabla. El orden se basa en una clave que consta de una o más columnas. La clave se conoce comúnmente como clave de agrupamiento.
Las filas de una tabla solo pueden estar en un solo orden, por lo que solo puede haber un índice agrupado en una tabla. Una entrada IAM se utiliza para apuntar a un índice agrupado.
Existe una idea errónea de que las páginas en un índice agrupado están “físicamente almacenadas en orden”. Aunque esto es posible en situaciones raras, no suele ser el caso. Si fuera cierto, la fragmentación de los índices agrupados no existiría. SQL Server intenta alinear el orden físico y lógico mientras crea un índice, pero pueden surgir desórdenes a medida que se modifican los datos.
Las páginas de índice y de datos están vinculadas dentro de una jerarquía lógica y también tienen enlaces dobles en todas las páginas en el mismo nivel de la jerarquía, para ayudar al escanear en un índice.
Creación

Es posible crear índices agrupados, ya sea directamente utilizando el comando CREATE INDEX, o automáticamente en situaciones donde se especifica una restricción PRIMARY KEY en la tabla:
Crear un índice directamente en una tabla existente
CREATE INDEX IX_ISBN ON Library.Book (ISBN);

El siguiente Transact-SQL creará una tabla. La sentencia alter luego agrega una restricción, con el efecto secundario de crear un índice agrupado.
Crear un índice indirectamente:

CREATE TABLE Library.LogData
(LogID INT IDENTIDAD (1,1),
LogData XML NOT NULL);
ALTER TABLE Library.LogData ADD CONSTRAINT PK_LogData PRIMARY KEY (LogId);

Actualizando
Es posible reconstruir, reorganizar y desactivar un índice. La última opción de deshabilitar un índice no es realmente aplicable para los índices agrupados, porque deshabilitar uno no permite ningún acceso a los datos subyacentes en la tabla. Sin embargo, deshabilitar un índice no agrupado tiene sus usos. Estos serán discutidos en un tema futuro.

Transact-SQL se puede usar para reconstruir un solo índice.

Reconstruir un índice específico
ALTER INDEX IX_ISBN ON Library.Book REBUILD;

También es posible reconstruir todos los índices en una tabla especificada.

Reconstruir todos los índices en una tabla

ALTER INDEX ALL ON Library.Book REBUILD;

La instrucción REORGANIZE se puede usar de la misma manera, ya sea en un índice específico o en una tabla completa.

Reorganizar todos los índices en una tabla

ALTER INDEX ALL ON Library.Book REORGANIZE;

Eliminando

Si un índice agrupado se crea explícitamente, el siguiente Transact-SQL lo eliminará:
Eliminar un índice agrupado
DROP INDEX IX_ISBN ON Library.Book;

Será necesario modificar una tabla para eliminar un índice agrupado, si se creó como consecuencia de la definición de una restricción.
Eliminar un índice agrupado si se creó como parte de agregar una restricción
ALTER TABLE Library.LogData DROP CONSTRAINT PK_LogData;

Analogía de la Biblioteca Física
En la analogía de la biblioteca, un índice agrupado es similar a almacenar todos los libros en un orden específico. Un ejemplo de esto sería almacenar libros en orden de Número de libro estándar internacional (ISBN). Claramente, la biblioteca solo se puede ordenar en una dirección.

Índice No Agrupado

Has visto cómo las tablas se pueden estructurar como montones o tienen índices agrupados. Una tercera opción es que puede crear índices adicionales sobre estas tablas para proporcionar formas alternativas de localizar rápidamente los datos requeridos. Estos índices adicionales se denominan índices no agrupados.
Una tabla puede tener hasta 999 índices no agrupados. Los índices no agrupados se pueden definir en una tabla, independientemente de si la tabla usa un índice agrupado o un montón, y se utilizan para mejorar el rendimiento de las consultas importantes.
Siempre que actualice las columnas de clave del índice no agrupado o actualice las claves de clúster en la tabla base, los índices no agrupados deben actualizarse también. Esto afecta el rendimiento de modificación de datos del sistema. Cada índice adicional que se agrega a una tabla aumenta el trabajo que SQL Server puede necesitar realizar cuando modifica las filas de datos en la tabla. Debe tener cuidado de equilibrar la cantidad de índices que se crean con los gastos generales que introducen.
Creación

De forma similar a los índices agrupados, los índices no agrupados se crean explícitamente en una tabla. Las columnas que se incluirán también deben especificarse.

Crear un índice no agrupado
CREATE INDEX NONCLUSTERED IX_Book_Publisher
ON Library.Book (PublisherID, ReleaseDate DESC);

Hay una opción que es única para los índices no agrupados. Pueden tener una opción INCLUDE adicional en la declaración que se usa para crear índices de cobertura. Esto permite incluir columnas al índice que no participan del ordenamiento de este pero que permiten mejorar las consultas que hacen referencia a las columnas que se incluyen.

Creación de un índice no agrupado de cobertura

CREATE INDEX NONCLUSTERED NCIX_Author_Publisher
ON Library.Book (BookID)
INCLUDE (AuthorID, PublisherID, ReleaseDate);

Actualizando
Transact-SQL para índices no agrupados es exactamente el mismo que para los índices agrupados. Es posible reconstruir, reorganizar y desactivar un índice.
Deshabilitar un índice puede ser muy útil para índices no agrupados en tablas que van a tener grandes cantidades de datos, ya sea insertados o eliminados. Antes de realizar estas operaciones de datos, se pueden deshabilitar todos los índices no agrupados. Una vez procesados ​​los datos, los índices pueden habilitarse ejecutando una instrucción REBUILD. Esto reduce los impactos en el rendimiento de tener índices no agrupados en las tablas.
Supresión

El mismo Transact-SQL que se utiliza para índices agrupados eliminará un índice no agrupado.

Eliminar un índice no agrupado
DROP INDEX NCIX_Author_Publisher ON Library.Book;

Analogía física
Los índices no agrupados pueden considerarse índices que apuntan hacia las librerías. Proporcionan formas alternativas de buscar la información en la biblioteca. Por ejemplo, pueden dar acceso por autor, por fecha de lanzamiento o por editorial. También pueden ser índices compuestos donde puede encontrar un índice por fecha de lanzamiento, dentro de las entradas de cada autor. Los índices compuestos serán discutidos en la próxima lección.

One thought on “Indices en Microsoft SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *