Pregunta ¿Qué significa realmente el índice agrupado y no agrupado?


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

Un índice agrupado es un tipo especial de índice que reordena el camino   los registros en la tabla son físicamente   almacenado Por lo tanto, la tabla solo puede tener   un índice agrupado Los nodos de la hoja   de un índice agrupado contiene los datos   páginas. Un índice no agrupado es un   tipo especial de índice en el que   orden lógico del índice no   coincidir con el orden físico almacenado de   las filas en el disco. El nodo de la hoja de un   índice no agrupado no consiste en   las páginas de datos. En cambio, la hoja   los nodos contienen filas de índice.

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

¿Alguien puede explicar esto en inglés simple?


805
2017-08-09 15:59


origen


Respuestas:


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 desde un índice agrupado si desea recuperar todas las columnas. No tiene que ir primero al índice y luego a la mesa.

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


804
2017-08-09 16:05



Un índice agrupado significa que le está diciendo a la base de datos que almacene valores cercanos realmente cercanos entre sí en el disco. Esto tiene el beneficio de escaneo / recuperación rápida de registros que caen en algún rango de valores de índice agrupados.

Por ejemplo, tiene dos tablas, Cliente y pedido:

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

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

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

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

En cuanto a índices múltiples. Puede tener solo un índice agrupado por tabla porque define cómo se organizan físicamente los datos. Si deseas una analogía, imagina una gran sala con muchas tablas. Puede poner estas tablas para formar varias filas o juntarlas para formar una gran mesa de conferencias, pero no en ambas direcciones al mismo tiempo. Una tabla puede tener otros índices, y luego apuntarán a las entradas en el índice agrupado que, a su vez, indicará dónde encontrar los datos reales.


528
2017-08-09 16:01



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

enter image description here

(Fuente de imagen)

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

  1. Las filas en las páginas de hoja de índice agrupadas siempre contienen alguna cosa para cada una de las columnas (no dispersas) de 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 usando el INCLUDE cláusula (desde SQL Server 2005) para incluir explícitamente todas las columnas que no son clave, pero son representaciones secundarias y siempre hay otra copia de los datos alrededor (la tabla misma).

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   ellos mismos se pueden ordenar en un solo orden.

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

En mi opinión, sería mucho mejor redactado como.

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

La cita en línea de los libros no es incorrecta, pero debe tener claro que la "clasificación" de los índices agrupados y no agrupados es lógica, no física. Si lee las páginas a nivel de hoja siguiendo la lista vinculada y lee las filas en la página en orden de matriz de trama, entonces leerá las filas de í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 que el índice llave Es falso.

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

En cambio, 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 anterior en orden de clave lógica. Estas páginas no necesitan estar contiguas o en orden de tecla.

p.ej. la cadena de la página enlazada podría ser 1:2000 <-> 1:157 <-> 1:7053

Cuando se produce una división de página, se asigna una nueva página desde cualquier parte del grupo de archivos (desde una extensión mixta, para tablas pequeñas, o una extensión uniforme no vacía que pertenece a ese objeto o una extensión uniforme recién asignada). Esto podría no estar 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 verifiqué 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 el orden de las teclas (con el valor 1 - resaltado con la flecha a continuación) estaba en casi la última página física.

enter image description here

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 lo siguiente

enter image description here

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

Los índices no agrupados se pueden generar tanto en un montón como en un índice agrupado. Siempre contienen un localizador de fila de vuelta a la tabla base. En el caso de un montón, este es un identificador de fila físico (librado) 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 clave CI como columnas de clave NCI o INCLUDE-d columnas, entonces no se agrega nada. De lo contrario, la (s) columna (s) de clave de CI faltantes se agregan silenciosamente al NCI.

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

Los índices agrupados obtienen un uniquifier agregado para cualquier fila con valores clave que duplican una fila existente. Esto es solo un entero ascendente.

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

La nomenclatura agrupada frente a la no agrupada también se utiliza para los índices de almacén de columnas. El papel Mejoras a las tiendas de columnas de SQL Server estados

Aunque los datos de la columna de almacenamiento no están realmente "agrupados" en ninguna clave,   decidió mantener la convención tradicional de SQL Server de referencia   al índice primario como un índice agrupado.


206
2018-06-28 19:16



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

ÍNDICE CLUSTER

Si ingresa a una biblioteca pública, encontrará que los libros están organizados en un orden particular (muy probablemente el Sistema Decimal Dewey o DDS). Esto corresponde a la "índice agrupado" de los libros. Si el DDS # para el libro que deseaba era 005.7565 F736s, comenzarías por ubicar la fila de estantes etiquetados 001-099 o algo así. (Este signo de tapa final al final de la pila corresponde a un "nodo intermedio" en el índice). Eventualmente, se profundizaría en el estante específico etiquetado 005.7450 - 005.7600, entonces escanearías hasta encontrar el libro con el número DDS especificado, y en ese punto has encontrado tu libro.

ÍNDICE NO CLUSTER

Pero si no ingresó en la biblioteca con el DDS # de su libro memorizado, entonces necesitaría un segundo índice para ayudarlo. Antiguamente, en el frente de la biblioteca, se encontraba una maravillosa cómoda conocida como el "Catálogo de tarjetas". En él había miles de tarjetas 3x5, una para cada libro, ordenadas en orden alfabético (por título, quizás). Esto corresponde a la "índice no agrupado". Estos catálogos de tarjetas se organizaron en una estructura jerárquica, de modo que cada cajón se etiquetaría con el rango de tarjetas que contenía (Ka - Kl, por ejemplo; es decir, el "nodo intermedio"). Una vez más, perforarías hasta encontrar tu libro, pero en esta caso, una vez que lo haya encontrado (es decir, el "nodo hoja"), no tiene el libro en sí, sino solo una tarjeta con un índice número (el DDS #) con el que puede encontrar el libro real en el índice agrupado.

Por supuesto, nada impediría que el bibliotecario copie todas las tarjetas y las clasifique en un orden diferente en un catálogo de tarjetas separado. (Normalmente 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 de estos índices "no agrupados" como desee.


67
2017-10-26 21:06



Encuentre a continuación algunas características de índices agrupados y no agrupados:

Índices agrupados

  1. Los índices agrupados son índices que identifican de manera ú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. Simplemente se utilizan para la recuperación rápida de datos. No estoy seguro de tener datos únicos.

60
2018-01-21 14:21



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


41
2017-08-09 16:17



Índice agrupado

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

como "diccionario" No necesita ningún otro índice, ya es índice 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 solo lugar. el 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 fijamente hay un índice separado para señalar la ubicación del Capítulo y en el "FINALIZAR" hay otro Índice que señala la ubicación común de PALABRAS


4
2018-01-21 18:47



Índice agrupado

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

La única vez que las filas de datos en una tabla se almacenan en 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 de clave de índice no agrupados y cada entrada de valor de clave tiene un puntero a la fila de datos que contiene el valor de clave. El puntero de una fila de índice en un índice no agrupado a 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. Para un montón, un localizador de filas es un puntero a la fila. Para una tabla agrupada, el localizador de filas es la clave del índice agrupado.

Puede agregar columnas que no sean clave al nivel de hoja del índice no agrupado para eludir los límites de clave de índice existentes y ejecutar consultas indexadas totalmente cubiertas. Para obtener más información, vea Crear índices con columnas incluidas. Para obtener más información sobre los límites de las claves de índice, consulte Especificaciones de capacidad máxima para SQL Server.

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


3
2017-08-28 00:10



Si el archivo que contiene los registros se ordena de forma secuencial, un índice de clúster es un índice cuya clave de búsqueda también define el orden secuencial del archivo. Los índices de agrupamiento también se llaman índices primarios; el término índice primario puede parecer que denota un índice en una clave principal, pero dichos índices pueden construirse en cualquier clave de búsqueda. La clave de búsqueda de un índice de clúster suele ser la clave principal, aunque eso no es necesariamente así. Los índices cuya clave de búsqueda especifica una orden diferente del orden secuencial del archivo se denominan índices no agrupados o índices secundarios. Los términos "agrupado"Y"no agrupado"A menudo se usan en lugar de"agrupamiento"Y"no agrupamiento"


0
2017-07-12 16:24