Índices clúster y no clúster

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance

Un índice es una estructura de disco asociada con una tabla o una vista que acelera la recuperación de filas de la tabla o de la vista. Un índice contiene claves generadas a partir de una o varias columnas de la tabla o la vista. Dichas claves están almacenadas en una estructura (árbol b) que permite que SQL Server busque de forma rápida y eficiente la fila o filas asociadas a los valores de cada clave.

Una tabla o una vista puede contener los siguientes tipos de índices:

• Agrupado

  • Los índices clúster ordenan y almacenan las filas de los datos de la tabla o vista de acuerdo con los valores de la clave del índice. Estos valores clave son las columnas incluidas en la definición del índice. Solo puede haber un índice clúster por cada tabla, porque las filas de datos solo pueden estar almacenadas de una forma.
  • La única ocasión en la que las filas de datos de una tabla están ordenadas es cuando la tabla contiene un índice clúster. Cuando una tabla tiene un índice clúster, la tabla se denomina tabla agrupada. Si una tabla no tiene un índice clúster, sus filas de datos están almacenadas en una estructura sin ordenar denominada montón.

• No agrupado

  • Los índices no clúster tienen una estructura separada de las filas de datos. Un índice no clúster contiene los valores de clave de índice no clúster y cada entrada de valor de clave tiene un puntero a la fila de datos que contiene el valor clave.

  • El puntero de una fila de índice no clúster hacia una fila de datos se denomina localizador de fila. La estructura del localizador de filas depende de si las páginas de datos están almacenadas en un montón o en una tabla agrupada. Si están en un montón, el localizador de filas es un puntero hacia la fila. Si están en una tabla agrupada, el localizador de fila es la clave de índice clúster.

  • Puede agregar columnas sin clave al nivel hoja de un índice no agrupado con el fin de eludir los límites existentes para las claves de índice, así como para ejecutar consultas totalmente cubiertas. Para más información, vea Creación de índices con columnas incluidas. Para obtener más información sobre los límites para las claves de índice, consulte Especificaciones de capacidad máxima para SQL Server.

Tanto los índices clúster como los no clúster pueden ser únicos. Esto significa que con un índice único, dos filas no pueden tener el mismo valor para la clave de índice. De lo contrario, el índice no es único y varias filas pueden compartir el mismo valor de clave. Para más información, vea Creación de índices únicos.

Los índices se mantienen automáticamente para una tabla o vista cuando se modifican los datos de la tabla.

Consulte Índices para los tipos adicionales de índices de propósito especial.

Índices y restricciones

Los índices se crean automáticamente cuando las restricciones PRIMARY KEY y UNIQUE se definen en las columnas de tabla. Por ejemplo, cuando crea una tabla con una restricción UNIQUE, el motor de base de datos crea automáticamente un índice no agrupado. Si configura una restricción PRIMARY KEY, el motor de base de datos crea automáticamente un índice agrupado, a menos que ya exista uno. Cuando intenta aplicar una restricción PRIMARY KEY en una tabla existente y ya existe un índice agrupado en esa tabla, SQL Server aplica la clave principal mediante un índice no agrupado.

Para más información, vea Creación de claves principales y Creación de restricciones únicas.

Cómo usar los índices el optimizador de consultas

Los índices bien diseñados pueden reducir las operaciones de E/S de disco y consumen menos recursos del sistema. Por lo tanto, estos índices mejoran el rendimiento de las consultas. Los índices pueden ser útiles para diversas consultas que contienen instrucciones SELECT, UPDATE, DELETE o MERGE. Fíjese en la consulta SELECT JobTitle, HireDate FROM HumanResources.Employee WHERE BusinessEntityID = 250 en la base de datos AdventureWorks2022 . Cuando se ejecuta la consulta, el optimizador de consultas evalúa cada método disponible para recuperar datos y selecciona el método más eficiente. El método puede ser un recorrido de tabla o puede ser recorrer uno o más índices si existen.

Durante un recorrido de tabla, el optimizador de consultas leerá todas las filas de la tabla y extraerá las filas que cumplen con los criterios de la consulta. Un recorrido de la tabla genera muchas operaciones de E/S de disco y puede consumir recursos. No obstante, puede ser el método más eficaz si, por ejemplo, el conjunto de resultados de la consulta es un porcentaje elevado de filas de la tabla.

Cuando el optimizador de consultas utiliza un índice, busca en las columnas de clave de índice, busca la ubicación de almacenamiento de las filas que necesita la consulta y extrae las filas coincidentes de esa ubicación. Por lo general, la búsqueda del índice es mucho más rápida que la búsqueda en la tabla. A diferencia de una tabla, un índice suele contener muy pocas columnas por fila y las filas están ordenadas.

El optimizador de consultas normalmente selecciona el método más eficaz cuando ejecuta consultas. No obstante, si no hay índices disponibles, el optimizador de consultas debe utilizar un recorrido de la tabla. Su tarea consiste en designar y crear los índices que sean más educados para su entorno para que el optimizador de consultas tenga una selección de índices eficientes entre los cuales elegir. SQL Server proporciona el Asistente para la optimización de motor de base de datos, que sirve de ayuda en el análisis del entorno de su base de datos y para seleccionar los índices apropiados.

Contenido relacionado

• Guía de diseño y de arquitectura de índices de SQL Server y Azure SQL
• Crear un índice clúster
• Crear índices no agrupados