dans SQL Server (2005+) Je dois indexer une colonne (correspondances exactes uniquement) qui est E.g. Une recherche contre une colonne indexée évidemment plus petite est toujours mieux à certains égards, mais je ne suis pas assez familier avec la manière dont SQL Server optimise ses index de savoir comment il traite de la longueur. nvarchar (2000 +) . Quel est le moyen le plus évolutif et le plus performant d'approcher cela?
dans SQL Server (2005+), quelle serait la différence pratique de l'indexation sur une colonne avec les types suivants:
nvarchar (2000)
CHAR (40)
binaire (16)
binaire (16) est-elle mesurable plus rapidement qu'une recherche contre un nvarchar (2000) indexé (2000) ? Si oui, combien?
5 Réponses :
bien sûr Un binaire (16) sera beaucoup plus rapide - faites simplement la plus rapide des calculs:
Si vous avez une table avec 100'000 entrées, vous devrez disposer de 200 pages pour l'index avec une clé binaire (16), tandis que vous aurez besoin de 50 000 pages pour le même index avec Nvarchar (2000 )
Même les E / S ajoutés à lire et à analyser toutes ces pages vont tuer toutes les performances que vous auriez pu avoir ........
marc
mise à jour: Aussi, vérifiez régulièrement et maintenez vos indices - si vous avez plus de 30% de fragmentation, reconstruisez-vous - si vous avez 5-30% de fragmentation, réorganisez-la. Consultez un script automatique de maintenance d'index DB bien testé sur http: // sqlfool .COM / 2009/06 / Index-défrag-script-v30 /
pour la clé en cluster sur une table SQL Server sur une table SQL Server, essayez d'éviter les GUID, car ils sont de nature aléatoires et provoquent ainsi une fragmentation d'index potentiellement massive et font donc mal aux performances. De plus, bien que ce ne soit pas une exigence difficile, essayez de vous assurer que votre clé en cluster est unique - si ce n'est pas le cas, SQL Server ajoutera un joueur de quatre octets à celui-ci. En outre, la clé en clustere est ajoutée à chaque entrée de chaque index non clusterné. Donc, dans la clé en cluster, il est extrêmement important d'avoir une colonne petite, unique, stable (non changeante) (de manière optimale qu'elle augmente jamais , cela vous donne les meilleures caractéristiques et performances -> INT L'identité est parfaite).
Pour mes index habituels, j'essaie d'éviter les indices composés autant que je peux - les faire référencer d'autres tables devient plutôt désordonnée (où des clauses avec plusieurs comparaisons d'égalité).
Quoi d'autre part des considérations d'espace pure? Si plusieurs autres colonnes sont stockées avec l'index, votre nombre de comparaisons n'est pas aussi drastique, quelles autres différences y aurait-il?
Vous pouvez avoir au plus 900 octets par entrée d'index, votre Nvarchar (2000) ne volera pas. La plus grande différence sera une profondeur d'index - le nombre de pages pour traverser la racine de la page de la feuille. Donc, si vous devez rechercher, vous pouvez indexer sur checksum, comme celui-ci: (exemple d'ici Bien sûr, si vous devez appliquer l'unicité, vous devrez utiliser des déclencheurs. Un autre L'idée est de convertir votre nvarchar à une valeur binaire plus petite et d'indexer à ce sujet, mais pouvez-vous garantir que chaque valeur est toujours zippée à 900 octets ou moins?
+1 Excellent point, oui - 900 octets est le max pour une entrée d'index.
Vous avez besoin d'un hachage beaucoup plus grand qu'un checksum 32 bits. CheckSum retourne int et il aura, dans le cas meilleur , une collision de probabilité de 50% après seulement 64k enregistrements, une très petite table. rusanu.com/2009/05/29/...
Remus, avec un hachage plus grand, vous aurez moins de chances d'obtenir de faux positifs, mais vous en aurez toujours. Déclenche seulement dans ce cas.
À droite, si vous décidez de le faire respecter avec la gâchette, un petit hachage rapide est correct, car vous résoudre les conflits «manuellement» de toute façon. Un hachage assez important, d'autre part, vous permet de compter sur le hasard seul et de ne pas autoriser les doublons (si le conflit est raisonnablement improbable, même avec la rencontre dans le milieu), puis vous pouvez compter sur l'unicité de l'index, beaucoup plus efficace. que de déclencher. C'est bien sûr un compromis que le bon chemin dépend de l'affaire.
Vous pensez à cela de la mauvaise direction:
Si une colonne est un Ne laissez pas l'indiction choix dicter vos types de colonne. Si vous devez indexer un En fonction de votre mise à jour, je créerais probablement une colonne de contrôle ou une colonne calculée à l'aide de la fonction binaire (16) ou nvarchar (2000) fait peu de différence là-bas, car vous n'allez pas simplement ajouter des index willy nilly.
NvarchaRar (2000) Considérez un index FullText ou d'ajouter une valeur de hachage pour la colonne et index.
hachbytes () fonction et index. Notez qu'un somme de contrôle n'est pas identique à un hachage cryptographique et vous avez donc un peu plus probablement des collisions, mais vous pouvez également correspondre à l'intégralité du contenu du texte et de filtrer avec l'index d'abord. Les hashbytes () sont moins susceptibles d'avoir des collisions, mais il est toujours possible et vous devez donc toujours comparer la colonne réelle. Les hashbytes sont également plus chères pour calculer le hash pour chaque requête et chaque changement.
En fait, c'est l'une des raisons pour lesquelles je pose cela - un bref hachage binaire d'un grand domaine sera-t-il mieux à indiquer?
Une colonne de hash ne peut rechercher qu'une correspondance exacte. Si vous n'avez pas besoin de correspondances partielles (comme «FOO%») ni de gammes (entre 'A »et' B '), vous pouvez utiliser des hachages.
Okay: Maintenant, nous examinons une question différente: "Je dois indexer une colonne Nvarchar (2000). L'objectif est de rendre ce type de requête exécuté plus rapidement: ______. Comment dois-je faire ça?"
@Joel Merci, j'ai réduit la portée de la question à cela.
Si le résultat du hachage (cryptographique) est suffisamment grand, les chances d'une collision sont si basses, vous n'avez pas besoin de comparer les valeurs de colonne réelles. Je soupçonne qu'un 128 bits suffisent à quasiment tous les objectifs: vous auriez besoin d'insérer 2 ^ 64 valeurs (une quantité assez irréalisable de données) pour avoir une cote décente à observer une seule paire de valeurs en collision. Et si vous pour une raison quelconque, ne faites pas confiance à cela - utilisez simplement un hachage plus long; La probabilité d'un affrontement diminue de manière exponentielle car le nombre de bits augmente. SHA512 est commun et pas trop lent ...
dans l'index majust est de 900 octets de toute façon , donc vous Impossible d'indexer NvarchaRar (2000).
Une touche d'index plus grande signifie moins de touches s'adapte dans les pages d'index afin qu'elle crée un arbre plus grand, plus de disque utilisé, plus d'E / S, plus de tirage tampon, moins de mise en cache. Pour les clés en cluster, c'est bien pire car la valeur de la clé en cluster est utilisée comme valeur de recherche sur tous les autres index non clusterés, de sorte qu'il augmente la taille de tous les index tous les En fin de compte, le nombre de pages numérisées / recherchez-la, le nombre de pages numérisées / recherches. Cela se traduit par des lectures physiques (= heure d'attente d'E / S) ou des lectures logiques (= pollution cache). Autre que les considérations d'espace, les types de données ne font que peu de différence dans un comportement de requête. Char / Varchar / Ncharar / Nvarchar a des collations à prendre en compte sur des comparaisons, mais le coût de la recherche d'ordre de collecte n'est généralement pas un facteur décisif. et le dernier mais non le moindre, probablement le plus important. facteur, est Et parfois, vous devez envisager des problèmes de concurrence, comme lorsque vous devez éliminer < Un href = "http://rusanu.com/2009/05/16/readwrite-deadlock/" rel = "nOfollow Noreferrer"> impression résultant d'un chemin d'accès distinct de mise à jour sur le même fiche . Utilisez une colonne de hachage MD5 persistée: Vous devez être très attentivement avec Votre recherche, le hachage va différer définitivement pour toute différence d'entrée, c'est-à-dire. Si vous recherchez un paramètre ASCII au lieu d'unicode un ... Vous aurez un Coup de chanson décente si votre table pousse grosse. Mise à jour après la publication d'édition
En réalité, il vaut mieux comprendre et voir par vous-même. Par exemple, le script suivant compare une recherche d'index via un entier de 4 octets contre une recherche via un chargeur de 50 octets. Il est à 3 lectures pour un int (la profondeur de l'arbre B construit sur une colonne INT) vs 4 se lit pour un char (la profondeur de l'arbre B construit sur une colonne de charme).
EXEC sp_spaceused 'dbo.NarrowKey'; -- 32K EXEC sp_spaceused 'dbo.WideKey'; -- 136K
Avez-vous besoin de rechercher ou d'appliquer l'unicité?
@ALEX J'ai besoin d'appliquer l'unicité, mais ne fera que faire des correspondances exactes.
Une autre idée est de glisser votre nvarchar à une valeur binaire plus petite et d'indexer à ce sujet, mais pouvez-vous garantir que chaque valeur est toujours zippée à 900 octets ou moins?