sql-server create - ¿Qué significan realmente los índices agrupados y no agrupados?




clustered index (8)

Tengo una exposición limitada a DB y solo he usado DB como programador de aplicaciones. Quiero saber acerca de los Non clustered indexes y Non clustered indexes . Busqué en Google y lo que encontré fue:

Un índice agrupado es un tipo especial de índice que reordena la forma en que los registros en la tabla se almacenan físicamente. Por lo tanto, la tabla solo puede tener un índice agrupado. Los nodos de hoja de un índice agrupado contienen las páginas de datos. Un índice no agrupado es un tipo especial de índice en el que el orden lógico del índice no coincide con el orden físico almacenado de las filas en el disco. El nodo hoja de un índice no agrupado no consta de las páginas de datos. En su lugar, los nodos de hoja contienen filas de índice.

Lo que encontré en SO fue ¿Cuáles son las diferencias entre un índice agrupado y un índice no agrupado? .

¿Alguien puede explicar esto en un lenguaje sencillo?


Answers

En el almacenamiento orientado a filas de SQL Server, los índices agrupados y no agrupados se organizan como árboles B.

( Fuente de la imagen )

La diferencia clave entre los índices agrupados y los índices no agrupados es que el nivel de hoja del índice agrupado es la tabla. Esto tiene dos implicaciones.

  1. Las filas en las páginas de hoja de índice agrupadas siempre contienen algo para cada una de las columnas (no dispersas) en la tabla (ya sea el valor o un puntero al valor real).
  2. El índice agrupado es la copia principal de una tabla.

Los índices no agrupados también pueden hacer el punto 1 utilizando la cláusula INCLUDE (desde SQL Server 2005) para incluir explícitamente todas las columnas no clave, pero son representaciones secundarias y siempre hay otra copia de los datos (la tabla en sí).

CREATE TABLE T
(
A INT,
B INT,
C INT,
D INT
)

CREATE UNIQUE CLUSTERED INDEX ci ON T(A,B)
CREATE UNIQUE NONCLUSTERED INDEX nci ON T(A,B) INCLUDE (C,D)

Los dos índices anteriores serán casi idénticos. Con las páginas de índice de nivel superior que contienen valores para las columnas clave A,B y las páginas de nivel de hoja que contienen A,B,C,D

Solo puede haber un índice agrupado por tabla, porque las filas de datos en sí pueden ordenarse en un solo orden.

La cita anterior de los libros en línea de SQL Server causa mucha confusión

En mi opinión estaría mucho mejor expresado como.

Solo puede haber un índice agrupado por tabla, porque las filas de nivel hoja del índice agrupado son las filas de la tabla.

La cita de los libros en línea no es incorrecta, pero debe tener claro que la "clasificación" de los índices agrupados y no agrupados es lógica y no física. Si lee las páginas a nivel de hoja siguiendo la lista enlazada y lee las filas en la página en orden de matriz de ranuras, leerá las filas del índice en orden ordenado pero físicamente las páginas pueden no estar ordenadas. La creencia común de que con un índice agrupado las filas siempre se almacenan físicamente en el disco en el mismo orden en que la clave de índice es falsa.

Esta sería una implementación absurda. Por ejemplo, si se inserta una fila en el medio de una tabla de 4GB, SQL Server no tiene que copiar 2GB de datos en el archivo para dejar espacio para la fila recién insertada.

En su lugar se produce una división de página. Cada página en el nivel de hoja de los índices agrupados y no agrupados tiene la dirección ( File:Page ) de la página siguiente y la anterior en orden lógico de clave. Estas páginas no necesitan ser contiguas o en orden de claves.

por ejemplo, la cadena de páginas vinculadas puede ser 1:2000 <-> 1:157 <-> 1:7053

Cuando ocurre una división de página, se asigna una nueva página desde cualquier parte del grupo de archivos (ya sea de una extensión mixta, para tablas pequeñas o una extensión uniforme no vacía que pertenezca a ese objeto o una extensión uniforme recién asignada). Es posible que esto ni siquiera esté en el mismo archivo si el grupo de archivos contiene más de uno.

El grado en que el orden lógico y la contigüidad difieren de la versión física idealizada es el grado de fragmentación lógica.

En una base de datos recién creada con un solo archivo ejecuté lo siguiente.

CREATE TABLE T
  (
     X TINYINT NOT NULL,
     Y CHAR(3000) NULL
  );

CREATE CLUSTERED INDEX ix
  ON T(X);

GO

--Insert 100 rows with values 1 - 100 in random order
DECLARE @C1 AS CURSOR,
        @X  AS INT

SET @C1 = CURSOR FAST_FORWARD
FOR SELECT number
    FROM   master..spt_values
    WHERE  type = 'P'
           AND number BETWEEN 1 AND 100
    ORDER  BY CRYPT_GEN_RANDOM(4)

OPEN @C1;

FETCH NEXT FROM @C1 INTO @X;

WHILE @@FETCH_STATUS = 0
  BEGIN
      INSERT INTO T (X)
      VALUES        (@X);

      FETCH NEXT FROM @C1 INTO @X;
  END

Luego revisé el diseño de la página con

SELECT page_id,
       X,
       geometry::Point(page_id, X, 0).STBuffer(1)
FROM   T
       CROSS APPLY sys.fn_PhysLocCracker( %% physloc %% )
ORDER  BY page_id

Los resultados fueron por todo el lugar. La primera fila en orden de clave (con el valor 1 - resaltado con la flecha abajo) estaba en casi la última página física.

La fragmentación se puede reducir o eliminar mediante la reconstrucción o reorganización de un índice para aumentar la correlación entre el orden lógico y el orden físico.

despues de correr

ALTER INDEX ix ON T REBUILD;

Tengo el siguiente

Si la tabla no tiene un índice agrupado, se llama montón.

Los índices no agrupados pueden construirse en un montón o en un índice agrupado. Siempre contienen un localizador de filas a la tabla base. En el caso de un montón, este es un identificador de fila físico (deshecho) y consta de tres componentes (Archivo: Página: Ranura). En el caso de un índice agrupado, el localizador de filas es lógico (la clave de índice agrupado).

Para el último caso, si el índice no agrupado ya incluye naturalmente la (s) columna (s) de la clave CI como columnas de la clave del NCI o como columnas INCLUDE -d, no se agrega nada. De lo contrario, la (s) columna (s) de clave de CI que faltan se agregan silenciosamente al NCI.

SQL Server siempre garantiza que las columnas clave sean únicas para ambos tipos de índice. Sin embargo, el mecanismo en el que se aplica esto para índices no declarados como únicos difiere entre los dos tipos de índice.

Los índices uniquifier obtienen un uniquifier agregado para cualquier fila con valores clave que dupliquen una fila existente. Esto es sólo un número entero ascendente.

Para los índices no agrupados no declarados como SQL Server único, se agrega silenciosamente el localizador de filas a la clave de índice no agrupado. Esto se aplica a todas las filas, no solo a las que en realidad son duplicadas.

La nomenclatura agrupada frente a no agrupada también se usa para los índices del almacén de columnas. Las mejoras en papel de los estados de los almacenes de columnas de SQL Server

Si bien los datos del almacén de columnas no están realmente "agrupados" en ninguna clave, decidimos mantener la convención tradicional de SQL Server de referirnos al índice primario como un índice agrupado.


Un índice agrupado significa que le está diciendo a la base de datos que almacene valores cercanos que realmente estén cerca uno del otro en el disco. Esto tiene la ventaja de un rápido escaneo / recuperación de registros que caen en algún rango de valores de índices agrupados.

Por ejemplo, tiene dos tablas, Cliente y Orden:

Customer
----------
ID
Name
Address

Order
----------
ID
CustomerID
Price

Si desea recuperar rápidamente todos los pedidos de un cliente en particular, es posible que desee crear un índice agrupado en la columna "CustomerID" de la tabla de pedidos. De esta manera, los registros con el mismo CustomerID se almacenarán físicamente cerca uno del otro en el disco (agrupados), lo que acelera su recuperación.

PS El índice en CustomerID obviamente no será único, por lo que debe agregar un segundo campo para "unificarlo" o dejar que la base de datos lo maneje por usted, pero esa es otra historia.

Respecto a los índices múltiples. Solo puede tener un índice agrupado por tabla porque esto define cómo se organizan físicamente los datos. Si desea una analogía, imagine una gran sala con muchas mesas en ella. Puede poner estas tablas para formar varias filas o juntarlas todas para formar una gran mesa de conferencias, pero no ambas formas al mismo tiempo. Una tabla puede tener otros índices, luego apuntarán a las entradas en el índice agrupado que a su vez finalmente dirá dónde encontrar los datos reales.


Una regla de oro muy simple y no técnica sería que los índices agrupados se usen generalmente para su clave principal (o, al menos, una columna única) y que los no agrupados se usen para otras situaciones (tal vez una clave externa) . De hecho, SQL Server creará por defecto un índice agrupado en su (s) columna (s) de clave principal. Como habrá aprendido, el índice agrupado se relaciona con la forma en que los datos se clasifican físicamente en el disco, lo que significa que es una buena opción para la mayoría de las situaciones.


Índice agrupado

Un índice agrupado determina el orden físico de los DATOS en una tabla. Por esta razón, una tabla solo tiene 1 índice agrupado.

como "diccionario" No hay necesidad de ningún otro índice, ya está indexado según las palabras

Índice no agrupado

Un índice no agrupado es análogo a un índice en un libro. Los datos se almacenan en un lugar. el índice se almacena en otro lugar y el índice tiene punteros a la ubicación de almacenamiento de los datos. Por esta razón, una tabla tiene más de 1 índice no agrupado.

como "libro de química" al mirar, hay un índice separado para señalar la ubicación del Capítulo y en el "FIN" hay otro Índice que señala la ubicación de PALABRAS comunes


Con un índice agrupado, las filas se almacenan físicamente en el disco en el mismo orden que el índice. Por lo tanto, solo puede haber un índice agrupado.

Con un índice no agrupado hay una segunda lista que tiene punteros a las filas físicas. Puede tener muchos índices no agrupados, aunque cada nuevo índice aumentará el tiempo necesario para escribir nuevos registros.

En general, es más rápido leer un índice agrupado si desea recuperar todas las columnas. No tiene que ir primero al índice y luego a la tabla.

Escribir en una tabla con un índice agrupado puede ser más lento, si es necesario reorganizar los datos.


A continuación, encontrará algunas características de los índices agrupados y no agrupados:

Índices agrupados

  1. Los índices agrupados son índices que identifican de forma única las filas en una tabla SQL.
  2. Cada tabla puede tener exactamente un índice agrupado.
  3. Puede crear un índice agrupado que cubra más de una columna. Por ejemplo: create Index index_name(col1, col2, col.....) .
  4. De forma predeterminada, una columna con una clave principal ya tiene un índice agrupado.

Índices no agrupados

  1. Los índices no agrupados son como índices simples. Sólo se utilizan para la recuperación rápida de datos. No estoy seguro de tener datos únicos.

Índice agrupado

Los índices agrupados clasifican y almacenan las filas de datos en la tabla o vista según sus valores clave. Estas son las columnas incluidas en la definición del índice. Solo puede haber un índice agrupado por tabla, porque las filas de datos en sí pueden ordenarse en un solo orden.

La única vez que las filas de datos en una tabla se almacenan en un orden ordenado es cuando la tabla contiene un índice agrupado. Cuando una tabla tiene un índice agrupado, la tabla se denomina tabla agrupada. Si una tabla no tiene un índice agrupado, sus filas de datos se almacenan en una estructura desordenada llamada montón.

No agrupado

Los índices no agrupados tienen una estructura separada de las filas de datos. 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 el valor clave. El puntero de una fila de índice en un índice no agrupado a una fila de datos se llama un localizador de filas. La estructura del localizador de filas depende de si las páginas de datos se almacenan en un montón o en una tabla agrupada. Para un montón, un localizador de fila es un puntero a la fila. Para una tabla agrupada, el localizador de filas es la clave de índice agrupado.

Puede agregar columnas sin clave al nivel de hoja del índice no agrupado para omitir los límites de la clave de índice existente y ejecutar consultas cubiertas e indexadas. Para obtener más información, consulte Crear índices con columnas incluidas. Para obtener detalles sobre los límites de clave de índice, consulte las Especificaciones de capacidad máxima para SQL Server.

Referencia: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described


Generalmente, si elige la capa de aplicación, puede diseñar el código de su aplicación para hacer el registro en un solo punto, que manejará consistentemente toda su tabla histórica. los disparadores de manera diferente son un enfoque más complicado de mantener porque se replican (según la tecnología de db) para cada tabla: en el caso de cientos de tablas, la cantidad de código para el disparador puede ser un problema.

Si tiene una organización de soporte que mantendrá el código que está escribiendo ahora y no sabe quién mantendrá su código (típico para las grandes industrias), no puede asumir cuál es el nivel de habilidad de la persona que lo solucionará. aplicación, en ese caso es mejor en mi opinión hacer que el principio de funcionamiento de la tabla histórica sea lo más simple posible, y la capa de aplicación es probablemente el mejor lugar para este propósito.





sql-server performance indexing clustered-index non-clustered-index