sql-server - nonclustered - primary key clustered sql server

¿Qué significan realmente los índices agrupados y no agrupados?

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

Me doy cuenta de que esta es una pregunta muy antigua, pero pensé que ofrecería una analogía para ayudar a ilustrar las buenas respuestas de arriba.

ÍNDICE CLUSTRADO

Si entras en una biblioteca pública, encontrarás que todos los libros están ordenados en un orden particular (lo más probable es que sea el Sistema decimal de Dewey o DDS). Esto corresponde al "índice agrupado" de los libros. Si el DDS # para el libro que desea es 005.7565 F736s , comenzaría por ubicar la fila de estanterías con la etiqueta 001-099 o algo así. (Este signo de extremo al final de la pila corresponde a un "nodo intermedio" en el índice.) Eventualmente, se desglosaría hasta el estante específico etiquetado 005.7450 - 005.7600 , luego escanearía hasta encontrar el libro con el DDS especificado # , y en ese punto has encontrado tu libro.

ÍNDICE NO CLUSTRADO

Pero si no ingresó a la biblioteca con el DDS # de su libro memorizado, entonces necesitará un segundo índice para ayudarlo. En los viejos tiempos, en la parte delantera de la biblioteca, se podía encontrar una maravillosa oficina de cajones conocida como el "Catálogo de tarjetas". En ella había miles de tarjetas 3x5, una para cada libro, ordenadas alfabéticamente (por título, quizás). Esto corresponde al "índice no agrupado" . Estos catálogos de tarjetas se organizaron en una estructura jerárquica, de modo que cada cajón se etiquetara con la gama de tarjetas que contenía ( Ka - Kl , por ejemplo, es decir, el "nodo intermedio"). Una vez más, profundizaría hasta encontrar su libro, pero en este caso, una vez que lo haya encontrado (es decir, el "nodo hoja"), no tiene el libro en sí, sino una tarjeta con un número de índice. (el número DDS) con el que puede encontrar el libro real en el índice agrupado.

Por supuesto, nada detendría al bibliotecario de fotocopiar todas las tarjetas y clasificarlas en un orden diferente en un catálogo de tarjetas separado. (Por lo general, había al menos dos catálogos de este tipo: uno ordenado por nombre de autor y otro por título). En principio, podría tener tantos índices "no agrupados" como desee.

Answer 5

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.

Answer 6

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.

Answer 7

Í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

Answer 8

Í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