Cluster vs non-cluster
Ma connaissance de niveau inférieur de SQL (Server 2008) est limitée, et est maintenant contestée par nos DBA. Permettez-moi d'expliquer (j'ai mentionné des déclarations évidentes dans l'espoir que j'ai raison, mais si vous voyez quelque chose de mal, dites-moi s'il vous plait) le scénario:
Nous avons une table qui contient des "ordonnances judiciaires" pour les gens. Quand j'ai créé la table, (nom: CourtOrder), Je l'ai créée comme:
CREATE TABLE dbo.CourtOrder
(
CourtOrderID INT NOT NULL IDENTITY(1,1), (Primary Key)
PersonId INT NOT NULL,
+ around 20 other fields of different types.
)
J'ai ensuite appliqué un index non clusterisé à la clé primaire (pour plus d'efficacité). Mes raisons, c'est qu'il est un champ unique (clé primaire), et doit être indexé, principalement à des fins de sélection, comme nous le faisons souvent Select from table where primary key = ...
J'ai ensuite appliqué un index clusterisé sur PersonId. La raison était de regrouper les commandes pour une personne en particulier physiquement, comme la grande majorité du travail est d'obtenir des commandes pour une personne. Alors, select from mytable where personId = ...
On m'a tiré dessus maintenant. J'ai dit que nous devrions mettre l'index cluster sur la clé primaire, et l'indice normal sur le personId. Cela semble très étrange pour moi. Tout d'abord, pourquoi mettriez-vous un index clusterisé sur une colonne unique? qu'est-ce clustering? C'est sûrement un gaspillage de l'index clusterisé? J'aurais cru un indice normal serait utilisé sur une colonne unique. En outre, le regroupement de l'index signifierait que nous ne pouvons pas regrouper une colonne différente (une par table, non?).
Le raisonnement pour moi étant dit que j'ai fait une erreur est qu'ils croient que mettre un index clusterisé sur le PersonId rendrait les insertions lentes. Pour le gain de vitesse de 5% d'une sélection, Nous serions obtenir une dégradation de 95% de la vitesse sur les inserts et les mises à jour. Est-ce correct et valide?
Ils disent que parce que nous regroupons le personId, SQL Server doit réorganiser les données chaque fois que nous insérons ou modifions le PersonId.
Alors j'ai demandé, pourquoi SQL aurait-il le concept d'un INDEX clusterisé, s'il est si lent? Est-il aussi lent qu'ils disent? Comment dois-je configurer mes index pour obtenir des performances optimales? J'aurais pensé que SELECT est utilisé plus que INSERT... mais ils disent que nous avons des problèmes de verrouillage sur les INSERTS...
J'espère que quelqu'un peut m'aider.
5 réponses
La distinction entre un index clusterisé et un index non clusterisé est que l'index clusterisé détermine l'ordre physique des lignes dans la base de données. En d'autres termes, l'application de l'index clusterisé à PersonId signifie que les lignes seront physiquement triées par PersonId dans la table, permettant à une recherche d'index d'aller directement à la ligne (plutôt qu'à un index non clusterisé, qui vous dirigerait vers l'emplacement de la ligne, en ajoutant une étape supplémentaire).
Cela dit, c'est inhabituel pour que la clé primaire ne soit pas l'index clusterisé, mais pas du jamais vu. Le problème avec votre scénario est en fait le contraire de ce que vous supposez: vous voulez des valeurs uniques dans un index clusterisé, pas des doublons. Étant donné que l'index en cluster détermine l'ordre physique de la ligne, si l'index est sur une colonne non unique, le serveur doit ajouter une valeur d'arrière-plan aux lignes qui ont une valeur de clé en double (dans votre cas, toutes les lignes avec le même PersonId) de sorte que la valeur combinée (clé + valeur de fond) est unique.
La seule chose que je suggère est Pas en utilisant une clé de substitution (votre colonne CourtOrderId) comme clé primaire, mais utilisez plutôt une clé primaire composée du PersonId et une autre colonne ou un ensemble de colonnes identifiant de manière unique. Si ce n'est pas possible (ou pas pratique), alors mettez l'index clusterisé sur CourtOrderId.
Je ne suis en aucun cas un SQL Expert...so prenez cela comme un point de vue du développeur plutôt qu'une vue DBA..
Les insertions sur des index en cluster (ordonnés physiquement) qui ne sont pas dans un ordre séquentiel entraînent un travail supplémentaire pour les insertions/mises à jour. En outre, si vous avez beaucoup d'inserts qui se produisent à la fois et qu'ils se produisent tous au même endroit, vous vous retrouvez avec contention. Vos performances spécifiques varient en fonction de vos données et de la façon dont vous y accédez. La règle générale est de construire votre index clusterisé sur le valeur étroite la plus unique dans votre table (généralement le PK)
Je suppose que votre PersonId ne changera pas, donc les mises à jour n'entrent pas en jeu ici. Mais considérons un instantané de quelques lignes avec PersonId de 1 2 3 3 4 5 6 7 8 8
Insérez maintenant 20 nouvelles lignes pour PersonId de 3. Tout d'abord, puisque ce n'est pas une clé unique, le serveur ajoute quelques octets supplémentaires à votre valeur (en coulisse) pour le rendre unique (qui ajoute également un espace supplémentaire) et ensuite l'emplacement où ces résidera doit être modifier. Comparez cela à l'insertion d'un PK auto-incrémenté où les insertions se produisent à la fin. L'explication non technique se résumerait probablement à ceci: il y a moins de travail de "mélange de feuilles" à faire s'il progresse naturellement des valeurs plus élevées à la fin de la table par rapport à l'emplacement de remaniement des éléments existants à cet emplacement tout en insérant vos éléments.
Maintenant, si vous rencontrez des problèmes avec les insertions, vous insérez probablement un tas de valeurs PersonId identiques (ou similaires) à la fois ce qui provoque ce travail supplémentaire dans divers endroits tout au long de la table et la fragmentation vous tue. L'inconvénient de passer au PK en cluster dans votre cas, est que si vous rencontrez des problèmes d'insertion aujourd'hui sur les PersonIds qui varient en valeur répartie dans la table, si vous passez votre index cluster au PK et que toutes les insertions se produisent maintenant dans un emplacement, votre problème peut s'aggraver en raison d'une concentration (Sur le revers de la médaille, si votre les insertions d'aujourd'hui ne sont pas réparties partout, mais sont généralement groupées dans des zones similaires, alors votre problème sera probablement facilité en changeant votre index cluster de PersonId à votre PK parce que vous minimiserez la fragmentation.)
Vos problèmes de performance doivent être analysés à votre situation unique et prendre ces types de réponses comme des directives générales seulement. Votre meilleur pari est de compter sur un DBA qui peut valider exactement où se trouvent vos problèmes. Il semble que vous avez les conflits de ressources qui peuvent être au-delà d'un simple indice de tweak. Cela pourrait être le symptôme d'un problème beaucoup plus important. (Problèmes de conception probables...sinon limitations de ressources.)
En tout cas, bonne chance!
Certains auteurs suggèrent de ne pas "gaspiller" le CI sur une colonne identity s'il existe une alternative qui bénéficierait aux requêtes de plage.
À Partir de MSDN Index Cluster Directives de Conception de clé doit être choisi selon les critères suivants
- Peut être utilisé pour les requêtes fréquemment utilisées.
- fournir un haut degré d'unicité.
- peut être utilisé dans les requêtes de plage.
Votre colonne CourtOrderID répond à 2. Votre PersonId Rencontre 1 et 3. Comme la plupart des lignes se retrouveront avec le uniqueifier ajouté de toute façon, vous pourriez aussi bien le déclarer comme unique et utiliser PersonId,CourtOrderID car ce sera la même largeur mais sera plus utile car la clé d'index en cluster est ajoutée à tous les NCIS comme Localisateur de lignes et cela leur permettra de couvrir plus de requêtes.
Le principal problème avec l'utilisation de PersonId,CourtOrderID comme CI est que la fragmentation logique s'ensuivra probablement (et cela affecte particulièrement les requêtes de plage que vous essayez d'aider), vous devrez donc surveiller le remplissage facteur, et les niveaux de fragmentation et d'effectuer la maintenance de l'index plus souvent.
Il est expliqué dans le lien suivant: https://msdn.microsoft.com/en-us/ms190457.aspx
Cluster
Les index en cluster 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 fois où les lignes de données dans une table sont stocké dans l'ordre trié est lorsque la table contient un index en cluster. Lorsqu'une table a un index en cluster, la table est appelée table en cluster. Si une table n'a pas d'index en cluster, ses lignes de données sont stockées dans une structure non ordonnée appelée tas.
Non Cluster
Les index non clustered ont une structure distincte des lignes de données. Un index non clustered C contient les valeurs de clé d'index non clustered et chaque entrée de valeur de clé a un pointeur vers ligne de données contenant la valeur clé .
Le pointeur d'une ligne d'index dans un index non clustered vers une ligne de données est appelé un localisateur de ligne. La structure du Localisateur de lignes dépend si les pages de données sont stockées dans un tas ou une table en cluster. Pour un segment, 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-clustered pour contourner l'existant indexez les limites de clé, 900 octets et 16 colonnes de clé, et exécutez des requêtes entièrement couvertes et indexées.
Une base de données avec des sélections désagréables, se joint à une procédure stockée - seule la différence est l'index
Index-clustered vs nonclustered
891 rows
10 sec
NONCLUSTERED
OR
891 rows
14 sec
CLUSTERED