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

Index en grappe

Un index clusterisé détermine l'ordre physique des données dans une table, c'est pourquoi une table n'a qu'un seul index clusterisé.

• " dictionnaire " Pas besoin d'un autre index, il est déjà indexé selon les mots.

Index non groupé

Un index non clusterisé est analogue à un index dans un livre, les données étant stockées à un seul endroit. L'index Pour cette raison, une table peut avoir plus d'un index non clusterisé.

• " Chimie Dans le "livre", il y a un index séparé pour indiquer l'emplacement du chapitre et à la "FIN", il y a un autre index indiquant l'emplacement des MOTS communs.

Answer 2

Une règle 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 index 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 clusterisé sur votre ou vos colonnes de clé primaire. Comme vous l'aurez appris, l'index clustered est lié à la façon dont les données sont physiquement triées sur le disque, ce qui signifie qu'il s'agit d'un bon choix polyvalent pour la plupart des situations.

Answer 3

Index en grappe

Un index en grappe est en fait une table organisée en arborescence. Au lieu de stocker les enregistrements dans un espace de table Heap non trié, l'index clusterisé est en fait un index B+Tree dont les nœuds feuilles, qui sont ordonnés par la valeur de la colonne clé des clusters, stockent les enregistrements réels de la table, comme l'illustre le diagramme suivant.

L'index en grappe est la structure de table par défaut dans SQL Server et MySQL. Alors que MySQL ajoute un index clusterisé caché même si une table ne possède pas de clé primaire, SQL Server construit toujours un index clusterisé si une table possède une colonne de clé primaire. Dans le cas contraire, le serveur SQL est stocké comme une table de type Heap.

L'index en grappe peut accélérer les requêtes qui filtrent les enregistrements par la clé de l'index en grappe, comme les instructions CRUD habituelles. Puisque les enregistrements sont situés dans les nœuds feuilles, il n'y a pas de recherche supplémentaire pour les valeurs de colonnes supplémentaires lors de la localisation des enregistrements par leurs valeurs de clé primaire.

Par exemple, lorsque vous exécutez la requête SQL suivante sur le serveur SQL :

SELECT PostId, Title
FROM Post
WHERE PostId = ? 

Vous pouvez voir que le plan d'exécution utilise une opération de recherche d'index en grappe pour localiser le nœud feuille contenant l'objet Post et il n'y a que deux lectures logiques nécessaires pour analyser les nœuds de l'index en grappe :

|StmtText                                                                             |
|-------------------------------------------------------------------------------------|
|SELECT PostId, Title FROM Post WHERE PostId = @P0                                    |
|  |--Clustered Index Seek(OBJECT:([high_performance_sql].[dbo].[Post].[PK_Post_Id]), |
|     SEEK:([high_performance_sql].[dbo].[Post].[PostID]=[@P0]) ORDERED FORWARD)      | 

Table 'Post'. Scan count 0, logical reads 2, physical reads 0

Index non clusterisé

Étant donné que l'index clusterisé est généralement construit à partir des valeurs de la colonne de la clé primaire, si vous souhaitez accélérer les requêtes qui utilisent une autre colonne, vous devrez ajouter un index secondaire non clusterisé.

L'index secondaire va stocker la valeur de la clé primaire dans ses nœuds feuilles, comme l'illustre le schéma suivant :

Ainsi, si nous créons un index secondaire sur le fichier Title de la colonne Post table :

CREATE INDEX IDX_Post_Title on Post (Title)

Et nous exécutons la requête SQL suivante :

SELECT PostId, Title
FROM Post
WHERE Title = ? 

Nous pouvons voir qu'une opération de recherche d'index est utilisée pour localiser le nœud feuille dans le fichier de données. IDX_Post_Title qui peut fournir la projection de la requête SQL qui nous intéresse :

|StmtText                                                                      |
|------------------------------------------------------------------------------|
|SELECT PostId, Title FROM Post WHERE Title = @P0                              |
|  |--Index Seek(OBJECT:([high_performance_sql].[dbo].[Post].[IDX_Post_Title]),|
|     SEEK:([high_performance_sql].[dbo].[Post].[Title]=[@P0]) ORDERED FORWARD)|

Table 'Post'. Scan count 1, logical reads 2, physical reads 0

Etant donné que les PostId La valeur de la colonne Primary Key est stockée dans le IDX_Post_Title cette requête n'a pas besoin d'une recherche supplémentaire pour localiser l'élément Post dans l'index en grappe.

Answer 4

Index en grappe

Les index groupé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 clusterisé par table, car les lignes de données elles-mêmes ne peuvent être triées que dans un seul ordre.

Le seul cas où les lignes de données d'une table sont stockées dans un ordre trié est celui où la table contient un index en grappe. Lorsqu'une table possède un index clusterisé, elle est appelée table clusterisée. Si une table n'a pas d'index clusterisé, ses lignes de données sont stockées dans une structure non ordonnée appelée tas.

Non groupé

Les index non groupés ont une structure distincte des lignes de données. Un index non clusterisé contient les valeurs clés de l'index non clusterisé et chaque entrée de valeur clé possède un pointeur vers la ligne de données qui contient la valeur clé. Le pointeur d'une ligne d'index dans un index non clusterisé vers une ligne de données est appelé un localisateur de ligne. La structure du localisateur de ligne varie selon que les pages de données sont stockées dans un tas ou dans une table clusterisée. Pour un tas, un localisateur de rangée est un pointeur vers la rangée. Pour une table clusterisée, le localisateur de ligne est la clé d'indexation clusterisée.

Vous pouvez ajouter des colonnes non clés au niveau feuille de l'index non clusterisé pour contourner les limites de clés d'index existantes et exécuter des requêtes indexées entièrement couvertes. Pour plus d'informations, voir Créer des index avec des colonnes incluses. Pour plus de détails sur les limites de clés d'index, voir Maximum Capacity Specifications for SQL Server.

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

Answer 5

Permettez-moi de vous proposer une définition du manuel sur l'"indice de regroupement", qui est tirée de l'article 15.6.1 de l'ouvrage Systèmes de bases de données : Le livre complet :

On peut aussi parler de indices de regroupement qui sont des index sur un ou plusieurs attributs de telle sorte que tous les tuples ayant une valeur fixe pour la clé de recherche de cet index apparaissent sur un nombre de blocs aussi réduit que possible.

Pour comprendre la définition, examinons l'exemple 15.10 fourni par le manuel :

Une relation R(a,b) qui est trié sur l'attribut a et stockées dans cet ordre, emballé dans des blocs, est sûrement clusterd. Un index sur a est un indice de clustering, puisque pour un a -valeur a1, tous les tuples avec cette valeur pour a sont consécutives. Ils apparaissent donc emballés dans des blocs, sauf éventuellement pour le premier et le dernier bloc qui contiennent a -valeur a1, comme le suggère la Fig.15.14. Cependant, un index sur b est peu probable qu'il s'agisse d'un regroupement, puisque les tuples avec une b -valeur sera réparti dans tout le fichier, à moins que les valeurs de l'option a y b sont très étroitement corrélés.

Notez que la définition n'impose pas que les blocs de données soient contigus sur le disque ; elle indique seulement que les tuples avec la clé de recherche sont emballés dans le moins de blocs de données possible.

Un concept connexe est relation groupée . Une relation est "en grappe" si ses tuples sont regroupés dans un nombre de blocs aussi réduit que possible pour contenir ces tuples. En d'autres termes, du point de vue d'un bloc disque, s'il contient des tuples de différentes relations, alors ces relations ne peuvent pas être regroupées (c'est-à-dire qu'il existe un moyen plus compact de stocker une telle relation en échangeant les tuples de cette relation provenant d'autres blocs disques avec les tuples n'appartenant pas à la relation dans le bloc disque actuel). En clair, R(a,b) dans l'exemple ci-dessus est groupé.

Pour relier deux concepts, une relation clusterisée peut avoir un indice de clustering et un indice de non clustering. Cependant, pour une relation non clusterisée, l'index de clustering n'est pas possible à moins que l'index soit construit sur la clé primaire de la relation.

Le mot "cluster" est utilisé à tous les niveaux d'abstraction du stockage des bases de données (trois niveaux d'abstraction : tuples, blocs, fichiers). Un concept appelé " fichier groupé ", qui décrit si un fichier (une abstraction pour un groupe de blocs (un ou plusieurs blocs de disque)) contient des tuples d'une relation ou de différentes relations. Il n'est pas lié au concept d'index de clustering car il se situe au niveau du fichier.

Cependant, certains matériel pédagogique aime définir l'index de mise en grappe basé sur la définition du fichier mis en grappe. Ces deux types de définitions sont les mêmes au niveau de la relation clusterisée, qu'ils définissent la relation clusterisée en termes de bloc de disque de données ou de fichier. A partir du lien dans ce paragraphe,

Un index sur le ou les attributs A d'un fichier est un index de regroupement lorsque : Tous les tuples ayant une valeur d'attribut A = a sont stockés séquentiellement (= consécutivement) dans le fichier de données.

Stocker les tuples consécutivement est la même chose que de dire "les tuples sont emballés dans un nombre de blocs aussi réduit que possible" (avec une différence mineure : l'un parle du fichier, l'autre du disque). C'est parce que stocker les tuples consécutivement est le moyen d'obtenir "emballé dans à peu près aussi peu de blocs que possible pour contenir ces tuples".