Question Que signifient réellement les index clusterisés et non clusterisés?


J'ai une exposition limitée à DB et n'ai utilisé que DB comme programmeur d'application. Je veux savoir à propos de Clustered et Non clustered indexes. J'ai googlé et ce que j'ai trouvé était:

Un index clusterisé est un type d'index spécial qui réorganise le chemin   les enregistrements dans la table sont physiquement   stocké. Par conséquent, la table peut avoir seulement   un index clusterisé. Les noeuds de feuille   d'un index en cluster contient les données   pages Un index non cluster est un   type spécial d'indice dans lequel le   ordre logique de l'index ne   correspondre à l'ordre physique stocké de   les lignes sur le disque. Le noeud feuille d'un   l'index non clusterisé ne comprend pas   les pages de données. Au lieu de cela, la feuille   les nœuds 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 anglais?


805
2017-08-09 15:59


origine


Réponses:


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 cluster.

Avec un index non clusterisé, il existe une deuxième liste avec des pointeurs vers les lignes physiques. Vous pouvez avoir plusieurs index non groupés, bien que chaque nouvel index augmente le temps nécessaire pour écrire de nouveaux enregistrements.

Il est généralement plus rapide de lire à partir d'un index cluster si vous souhaitez récupérer toutes les colonnes. Vous n'avez pas besoin d'aller d'abord à l'index, puis à la table.

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


804
2017-08-09 16:05



Un index clusterisé signifie que vous indiquez à la base de données de stocker des valeurs proches proches les unes des autres sur le disque. Ceci a l'avantage d'un balayage / extraction rapide des enregistrements tombant dans une certaine gamme de valeurs d'index clusterisées.

Par exemple, vous avez deux tables, Customer et Order:

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

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

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

P.S. L'index sur CustomerID ne sera évidemment pas unique, donc vous devez soit ajouter un second champ pour "uniquifier" l'index, soit laisser la base de données gérer cela pour vous, mais c'est une autre histoire.

En ce qui concerne les index multiples. Vous ne pouvez avoir qu'un seul index cluster par table car cela définit la façon dont les données sont organisées physiquement. Si vous souhaitez une analogie, imaginez une grande pièce avec beaucoup de tables dedans. Vous pouvez soit placer ces tables en plusieurs lignes, soit les regrouper 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 dans l'index clusterisé qui à son tour dira finalement où trouver les données réelles.


528
2017-08-09 16:01



Dans le stockage orienté lignes SQL Server, les index clusterisés et non clusterisés sont organisés en arborescences B.

enter image description here

(Source de l'image)

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

  1. Les lignes des pages de feuille d'index en cluster contiennent toujours quelque chose pour chacune des colonnes (non creuses) de la table (soit la valeur, soit un pointeur vers la valeur réelle).
  2. L'index clusterisé est la copie principale d'une table.

Les index non groupés peuvent également faire le point 1 en utilisant INCLUDE clause (Since SQL Server 2005) pour inclure explicitement toutes les colonnes non-clés, mais ce sont des représentations secondaires et il y a toujours une autre copie des données autour (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 index ci-dessus seront presque identiques. Avec les pages d'index de niveau supérieur contenant des valeurs pour les colonnes de clé A,B et les pages au niveau feuille contenant A,B,C,D

Il ne peut y avoir qu'un seul index cluster par table, car les lignes de données   eux-mêmes peuvent être triés dans un seul ordre.

La citation ci-dessus de livres SQL Server en ligne provoque beaucoup de confusion

À mon avis, il serait beaucoup mieux formulé comme.

Il ne peut y avoir qu'un seul index cluster par table, car les lignes de niveau feuille de l'index clusterisé sont les rangées de la table.

La citation en ligne de livres n'est pas incorrecte mais vous devriez être clair que le «tri» des deux index non groupés et groupés est logique non physique. Si vous lisez les pages au niveau de la feuille en suivant la liste chaînée et lisez les lignes sur la page dans l'ordre du tableau d'emplacements, vous lirez les lignes d'index dans l'ordre trié mais physiquement les pages ne seront pas triées. La croyance communément répandue selon laquelle les lignes sont toujours stockées physiquement sur le disque avec un index clusterisé dans le même ordre que l'index clé c'est faux.

Ce serait une mise en œuvre absurde. Par exemple, si une ligne est insérée au milieu d'une table de 4 Go, SQL Server ne ne pas devez copier 2 Go de données dans le fichier pour faire de la place pour la ligne nouvellement insérée.

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

par exemple. la chaîne de page liée pourrait être 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 (à partir d'une extension mixte, pour des petites tables ou une étendue uniforme non vide appartenant à cet objet ou une étendue uniforme nouvellement allouée). Cela peut même ne pas être dans le même fichier si le groupe de fichiers contient plus d'un.

Le degré auquel l'ordre logique et la contiguïté diffère 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 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 étaient partout. La première rangée dans l'ordre des clés (avec la valeur 1 - mise en évidence avec la flèche ci-dessous) était sur presque la dernière page physique.

enter image description here

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 couru

ALTER INDEX ix ON T REBUILD;

J'ai eu ce qui suit

enter image description here

Si la table n'a pas d'index clusterisé, elle s'appelle un tas.

Les index non clusterisés peuvent être créés sur un segment de mémoire ou un index cluster. Ils contiennent toujours un locator de ligne à la table de base. Dans le cas d'un tas, il s'agit d'un identificateur de ligne physique (rid) et se compose de trois composants (File: Page: Slot). Dans le cas d'un index clusterisé, le localisateur de lignes est logique (la clé d'index clusterisée).

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

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

Les index clusterisés obtiennent un uniquifier ajouté pour toutes les lignes avec des valeurs clés qui dupliquent une ligne existante. C'est juste un entier ascendant.

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

La nomenclature clustered vs non clustered est également utilisée pour les index de magasin de colonnes. Le papier Améliorations apportées aux magasins de colonnes SQL Server États

Bien que les données du magasin de colonnes ne soient pas vraiment "groupées" sur une clé, nous   décidé de conserver la convention de SQL Server traditionnelle de référence   à l'index primaire en tant qu'index en cluster.


206
2018-06-28 19:16



Je réalise que c'est une très vieille question, mais j'ai pensé que je proposerais une analogie pour aider à illustrer les bonnes réponses ci-dessus.

INDEX GRAPPÉ

Si vous entrez dans une bibliothèque publique, vous trouverez que les livres sont tous classés dans un ordre particulier (probablement le système décimal de Dewey, ou DDS). Cela correspond à "index clusterisé" des livres. Si le numéro DDS pour le livre que vous voulez était 005.7565 F736s, vous commencerez par localiser la rangée d'étagères qui est étiquetée 001-099 ou quelque chose comme ça. (Ce signe de fin de pile à la fin de la pile correspond à un "noeud intermédiaire" dans l'index.) Finalement, vous descendez vers l'étagère spécifique étiquetée 005.7450 - 005.7600, alors vous numériser jusqu'à ce que vous avez trouvé le livre avec le DDS # spécifié, et à ce moment-là vous avez trouvé votre livre.

INDICE NON CLUSTER

Mais si vous n'êtes pas entré dans la bibliothèque avec le numéro DDS de votre livre mémorisé, alors vous aurez besoin d'un deuxième index pour vous aider. Dans les temps anciens, vous trouverez à l'avant de la bibliothèque un magnifique bureau de tiroirs connu sous le nom "Card Catalog". Il y avait des milliers de cartes 3x5 - une pour chaque livre, classées par ordre alphabétique (par titre, peut-être). Cela correspond à "index non clusterisé". Ces catalogues de cartes étaient organisés en une structure hiérarchique, de sorte que chaque tiroir serait étiqueté avec la gamme de cartes qu'il contenait (Ka - Kl, par exemple; c'est-à-dire, le "noeud intermédiaire"). Encore une fois, vous devez percer jusqu'à ce que vous ayez trouvé votre livre, mais dans ce cas, une fois que vous l'avez trouvé (c'est-à-dire, le "nœud de feuille"), vous n'avez pas le livre lui-même, mais juste une carte avec un indice numéro (le numéro DDS) avec lequel vous pouvez trouver le livre réel dans l'index clusterisé.

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


67
2017-10-26 21:06



Voici quelques caractéristiques des index clusterisés et non-cluster:

Index clusterisés

  1. Les index clusterisé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 en cluster couvrant plusieurs colonnes. Par exemple: create Index index_name(col1, col2, col.....).
  4. Par défaut, une colonne avec une clé primaire a déjà un index cluster.

Index non groupés

  1. Les index non clusterisés sont comme des index simples. Ils sont juste utilisés pour la récupération rapide des données. Pas sûr d'avoir des données uniques.

60
2018-01-21 14:21



Une règle empirique très simple et non technique serait que les index clusterisés sont généralement utilisés pour votre clé primaire (ou, au moins, une colonne unique) et que les non clusterisés sont utilisés pour d'autres situations (peut-être une clé étrangère) . En effet, SQL Server créera par défaut un index cluster sur vos colonnes de clé primaire. Comme vous l'aurez appris, l'index clusterisé se rapporte à la façon dont les données sont physiquement triées sur le disque, ce qui signifie que c'est un bon choix général pour la plupart des situations.


41
2017-08-09 16:17



Index clusterisé

Un index clusterisé détermine l'ordre physique de DATA dans une table. Pour cette raison, une table ne possède qu'un seul index cluster.

comme "dictionnaire" Pas besoin d'un autre index, c'est déjà l'index selon les mots

Index non clusterisé

Un index non clusterisé est analogue à un index dans un livre. Les données sont stockées en un seul endroit. la index est stocké dans un autre endroit et l'index a des pointeurs vers l'emplacement de stockage des données. Pour cette raison, une table contient plus de 1 index non cluster.

comme "Livre de chimie" à regarder il y a un index séparé pour localiser le chapitre du point et à la "FIN" il y a un autre index pointant vers l'emplacement commun des MOTS


4
2018-01-21 18:47



Index clusterisé

Les index clusterisés trient et stockent les lignes de données dans la table ou la vue en fonction de leurs valeurs clés. Ce sont les colonnes incluses dans la définition de l'index. Il ne peut y avoir qu'un seul index cluster par table, car les lignes de données elles-mêmes peuvent être triées dans un seul ordre.

La seule heure où les lignes de données d'une table sont stockées dans l'ordre trié est lorsque la table contient un index cluster. Lorsqu'une table a un index cluster, la table est appelée une table en cluster. Si une table n'a pas d'index cluster, ses lignes de données sont stockées dans une structure non ordonnée appelée tas.

Non classé

Les index non cluster ont une structure distincte des lignes de données. Un index non cluster contient les valeurs de clé d'index non cluster et chaque entrée de valeur de clé a un pointeur vers la ligne de données qui contient la valeur de clé. Le pointeur d'une ligne d'index dans un index non-cluster vers une ligne de données s'appelle un localisateur de ligne. La structure du localisateur de lignes dépend du stockage des pages de données dans un tas ou dans une table en cluster. Pour un tas, un localisateur de ligne est un pointeur vers la ligne. Pour une table en cluster, le localisateur de ligne est la clé d'index en cluster.

Vous pouvez ajouter des colonnes non-clés au niveau feuille de l'index non-cluster pour ignorer les limites de clés d'index existantes et exécuter des requêtes indexées entièrement couvertes. Pour plus d'informations, consultez Créer des index avec des colonnes incluses. Pour plus de détails sur les limites de clé d'index, voir Spécifications de capacité maximale pour SQL Server.

Référence: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described 


3
2017-08-28 00:10



Si le fichier contenant les enregistrements est séquencé, un index de clustering est un index dont la clé de recherche définit également l'ordre séquentiel du fichier. Les indices de clustering sont également appelés indices primaires; le terme indice primaire peut sembler désigner un index sur une clé primaire, mais de tels indices peuvent en fait être construits sur n'importe quelle clé de recherche. La clé de recherche d'un index de clustering est souvent la clé primaire, bien que ce ne soit pas nécessairement le cas. Les indices dont la clé de recherche spécifie un ordre différent de l'ordre séquentiel du fichier sont appelés indices non clusterisés, ou indices secondaires. Les termes "en cluster" et "non-cluster"Sont souvent utilisés à la place de"regroupement" et "nonclustering"


0
2017-07-12 16:24