Que signifient réellement les termes "Clustered" et "Non-Clustered" ?

Question

J'ai une exposition limitée à la DB et je ne l'ai utilisée qu'en tant que programmeur d'applications. Je souhaite connaître Clustered y Non clustered indexes . J'ai fait une recherche sur Google et j'ai trouvé ce qui suit :

Un index clusterisé est un type spécial d'index qui réordonne la façon dont les les enregistrements de la table sont physiquement physiquement. Par conséquent, une table ne peut avoir qu'un seul un seul index clusterisé. Les nœuds feuilles d'un index clusterisé contiennent les pages données. Un index non clusterisé est un type spécial d'index dans lequel l'ordre l'ordre logique de l'index ne correspond pas correspond pas à l'ordre physique de stockage des des rangées sur le disque. Le noeud feuille d'un non clusterisé n'est pas composé les pages de données. Au lieu de cela, les nœuds leaf contiennent des lignes d'index.

Ce que j'ai trouvé dans SO était Quelles sont les différences entre un index clusterisé et un index non clusterisé ? .

Quelqu'un peut-il expliquer cela en langage clair ?

Answer 1

Avec un index clusterisé, les lignes sont stockées physiquement sur le disque dans le même ordre que l'index. Par conséquent, il ne peut y avoir qu'un seul index clusterisé.

Avec un index non clusterisé, il existe une deuxième liste qui contient des pointeurs vers les lignes physiques. Vous pouvez avoir plusieurs index non clusterisés, bien que chaque nouvel index augmente le temps nécessaire à l'écriture de nouveaux enregistrements.

Il est généralement plus rapide de lire à partir d'un index clusterisé si l'on veut récupérer toutes les colonnes. Il n'est pas nécessaire d'aller d'abord dans l'index, puis dans la table.

L'écriture dans une table avec un index en cluster peut être plus lente, s'il est nécessaire de réorganiser les données.

Answer 2

Un index en grappe signifie que vous demandez à la base de données de stocker des valeurs proches les unes des autres sur le disque. Cela présente l'avantage d'un balayage / d'une récupération rapide des enregistrements qui se situent dans une certaine plage de valeurs d'index en grappes.

Par exemple, vous avez deux tables, Client et Commande :

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

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

Si vous souhaitez retrouver rapidement toutes les commandes d'un client particulier, vous pouvez créer un index en grappe sur la colonne "CustomerID" de la table Order. De cette façon, les enregistrements ayant le même CustomerID seront physiquement stockés à proximité les uns des autres sur le disque (clusterisés), ce qui accélère leur récupération.

P.S. L'index sur CustomerID ne sera évidemment pas unique, vous devrez donc soit ajouter un deuxième champ pour "unicifier" l'index, soit laisser la base de données s'en charger pour vous, mais c'est une autre histoire.

Concernant les indices multiples. Vous ne pouvez avoir qu'un seul index clusterisé par table car il définit la manière dont les données sont physiquement organisées. Si vous souhaitez faire une analogie, imaginez une grande pièce contenant de nombreuses tables. Vous pouvez soit mettre ces tables pour former plusieurs rangées, soit les rassembler pour former une grande table de conférence, mais pas les deux en même temps. Une table peut avoir d'autres index, ils pointeront alors vers les entrées de l'index clusterisé qui, à son tour, dira finalement où trouver les données réelles.

Answer 3

Dans le stockage orienté ligne de SQL Server, les index groupés et non groupés sont organisés sous forme d'arbres B.

( Source d'image )

La principale différence entre les index en grappes et les index non en grappes est que le niveau de feuille de l'index en grappes es la table. Cela a deux implications.

1. Les lignes des pages feuilles de l'index en grappe contiennent toujours quelque chose pour chacune des colonnes (non éparses) du tableau (soit la valeur, soit un pointeur vers la valeur réelle).
2. L'index clusterisé est la copie primaire d'une table.

Les index non clusterisés peuvent aussi faire le point 1 en utilisant la fonction INCLUDE (depuis SQL Server 2005) pour inclure explicitement toutes les colonnes non clés, mais il s'agit de représentations secondaires et il existe toujours une autre copie des données (la table elle-même).

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)

Les deux indices ci-dessus seront presque identiques. Avec les pages d'index de niveau supérieur contenant les valeurs des colonnes clés A, B et les pages de niveau feuille contenant A, B, C, D

Il ne peut y avoir qu'un seul index clusterisé par table, parce que les lignes de données ne peuvent être triées que dans un seul ordre.

La citation ci-dessus, tirée des livres en ligne sur le serveur SQL, est source de confusion.

À mon avis, il serait bien mieux formulé comme suit .

Il ne peut y avoir qu'un seul index clusterisé par table car les lignes de niveau feuille de l'index clusterisé sont les lignes du tableau.

La citation en ligne du livre n'est pas incorrecte mais il faut bien comprendre que le "tri" des index non groupés et groupés est logique et non physique. Si vous lisez les pages au niveau des feuilles en suivant la liste chaînée et que vous lisez les lignes de la page dans l'ordre du tableau des fentes, vous lirez les lignes de l'index dans l'ordre trié, mais physiquement, les pages peuvent ne pas être triées. L'idée communément admise qu'avec un index en cluster, les lignes sont toujours stockées physiquement sur le disque dans le même ordre que l'index. clé est fausse.

Ce serait une mise en œuvre absurde. Par exemple, si une ligne est insérée au milieu d'une table de 4 Go, le serveur SQL fait ce qui suit pas doit copier 2 Go de données plus haut dans le fichier pour faire de la place à la nouvelle ligne insérée.

Au lieu de cela, une division de la page se produit. Chaque page au niveau des feuilles des index groupés et non groupés a l'adresse ( File: Page ) de la page suivante et précédente dans l'ordre logique des clés. Ces pages ne doivent pas nécessairement être contiguës ou dans l'ordre des clés.

Par exemple, la chaîne de pages liées pourrait être la suivante 1:2000 <-> 1:157 <-> 1:7053

Lorsqu'une division de page se produit, une nouvelle page est allouée à partir de n'importe quel endroit du groupe de fichiers (soit à partir d'une étendue mixte, pour les petites tables, soit à partir d'une étendue uniforme non vide appartenant à cet objet, soit à partir d'une étendue uniforme nouvellement allouée). Cela peut même ne pas être dans le même fichier si le groupe de fichiers en contient plusieurs.

Le degré auquel l'ordre logique et la contiguïté diffèrent de la version physique idéalisée est le degré de fragmentation logique.

Dans une base de données nouvellement créée avec un seul fichier, j'ai exécuté ce qui suit.

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

Puis j'ai vérifié la mise en page avec

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

Les résultats sont très variables. La première ligne dans l'ordre des clés (avec la valeur 1 - mise en évidence par une flèche ci-dessous) se trouvait pratiquement sur la dernière page physique.

La fragmentation peut être réduite ou supprimée en reconstruisant ou en réorganisant un index pour augmenter la corrélation entre l'ordre logique et l'ordre physique.

Après avoir exécuté

ALTER INDEX ix ON T REBUILD;

J'ai obtenu ce qui suit

Si la table n'a pas d'index clusterisé, elle est appelée "heap".

Les index non clusterisés peuvent être construits sur un tas ou un index clusterisé. Ils contiennent toujours un localisateur de ligne qui renvoie à la table de base. Dans le cas d'un tas, il s'agit d'un identificateur de rangée physique (rid) et il se compose de trois éléments (File:Page : Slot). Dans le cas d'un index clusterisé, le localisateur de ligne est logique (la clé de l'index clusterisé).

Dans ce dernier cas, si l'index non clusterisé inclut déjà naturellement la ou les colonnes clés CI, soit en tant que colonnes clés NCI, soit en tant que colonnes clés NCI. INCLUDE -d colonnes alors rien n'est ajouté. Dans le cas contraire, la ou les colonnes clés de l'ICN manquantes sont ajoutées à l'ICN en silence.

SQL Server s'assure toujours que les colonnes clés sont uniques pour les deux types d'index. Le mécanisme par lequel cela est appliqué pour les index non déclarés comme uniques diffère cependant entre les deux types d'index.

Les index en grappe obtiennent un uniquifier ajoutées pour toutes les lignes dont les valeurs de clé font double emploi avec une ligne existante. Il s'agit simplement d'un nombre entier ascendant.

Pour les index non clusterisés qui ne sont pas déclarés comme uniques, SQL Server ajoute silencieusement le localisateur de ligne dans la clé de l'index non clusterisé. Cela s'applique à toutes les lignes, et pas seulement à celles qui sont réellement des doublons.

La nomenclature clustered vs non clustered est également utilisée pour les index de type column store. Le papier Améliorations des magasins de colonnes du serveur SQL États

Bien que les données du stockage en colonnes ne soient pas réellement "groupées" sur une clé, nous avons décidé de conserver la convention traditionnelle de SQL Server qui consiste à faire référence aux données du stockage en colonnes. nous avons décidé de conserver la convention traditionnelle de SQL Server qui fait l'index primaire comme un index clusterisé.

Answer 4

Je me rends compte qu'il s'agit d'une très vieille question, mais j'ai pensé proposer une analogie pour aider à illustrer les belles réponses ci-dessus.

INDICE GROUPÉ

Si vous entrez dans une bibliothèque publique, vous constaterez que les livres sont tous rangés dans un ordre particulier (très probablement le système décimal Dewey, ou DDS). Cela correspond à la "index en grappe" des livres. Si le DDS# du livre que vous voulez est 005.7565 F736s vous devez commencer par localiser la rangée d'étagères étiquetées 001-099 ou quelque chose comme ça. (Ce signe d'extrémité à la fin de la pile correspond à un "nœud intermédiaire" dans l'index). Vous finissez par descendre jusqu'à l'étagère spécifique intitulée 005.7450 - 005.7600 Ensuite, vous scannez jusqu'à ce que vous trouviez le livre avec le numéro DDS spécifié, et à ce moment-là vous avez trouvé votre livre.

INDICE NON GROUPÉ

Mais si vous n'êtes pas arrivé à la bibliothèque en ayant mémorisé le numéro DDS de votre livre, vous aurez besoin d'un deuxième index pour vous aider. Autrefois, vous trouviez à l'avant de la bibliothèque un merveilleux bureau à tiroirs appelé "Card Catalog". Il contenait des milliers de fiches 3x5 - une pour chaque livre, classées par ordre alphabétique (par titre, peut-être). Cela correspond au "index non groupé" . Ces catalogues de cartes étaient organisés selon une structure hiérarchique, de sorte que chaque tiroir était étiqueté avec la gamme de cartes qu'il contenait ( Ka - Kl par exemple, c'est-à-dire le "nœud intermédiaire"). Une fois de plus, vous allez forer jusqu'à ce que vous trouviez votre livre, mais en este Dans ce cas, une fois que vous l'avez trouvé (c'est-à-dire le "nœud de la feuille"), vous n'avez pas le livre lui-même, mais seulement une carte avec un numéro d'identification. indice numéro (le DDS#) avec lequel vous pourriez trouver le livre actuel dans l'index groupé.

Bien entendu, rien n'empêcherait le bibliothécaire de photocopier toutes les fiches et de les classer dans un ordre différent dans un catalogue de fiches distinct. (En général, il y avait au moins deux catalogues de ce type : un trié par nom d'auteur, et un autre par titre). En principe, vous pouvez avoir autant de ces index "non groupés" que vous le souhaitez.

Answer 5

Trouvez ci-dessous quelques caractéristiques des index clusterisés et non clusterisés :

Index en grappe

1. Les index groupés sont des index qui identifient de manière unique les lignes d'une table SQL.
2. Chaque table peut avoir exactement un index clusterisé.
3. Vous pouvez créer un index clusterisé qui couvre plus d'une colonne. Par exemple : create Index index_name(col1, col2, col.....) .
4. Par défaut, une colonne avec une clé primaire a déjà un index clusterisé.

Index non groupés

1. Les index non groupés sont comme des index simples. Ils sont juste utilisés pour une récupération rapide des données. Il n'est pas certain que les données soient uniques.