Dois-je indexer un champ de bits dans SQL Server?

Question

Je me souviens avoir lu à un moment que l'indexation d'un champ avec une faible cardinalité (un faible nombre de valeurs distinctes) n'est pas vraiment la peine de le faire. J'avoue ne pas en savoir assez sur la façon dont les indices de travail pour comprendre pourquoi il en est.

Alors que faire si j'ai une table avec 100 millions de lignes, et je suis en sélectionnant les enregistrements où un champ de bits est 1? Et disons qu'à chaque point dans le temps, il y a seulement une poignée d'enregistrements où le champ de bits est à 1 (par opposition à 0). Est-ce la peine d'indexation que champ de bits ou pas? Pourquoi?

Bien sûr, je peux juste de le tester et de vérifier l'exécution du plan, et je vais le faire, mais je suis également curieux de connaître la théorie derrière tout cela. Quand la cardinalité de la matière et quand est-il pas?

Answer 1

Considérer qu'un index dans SQL - et l'index est vraiment une partie de la mémoire de pointer les autres morceaux de la mémoire (c'est à dire des pointeurs vers des lignes). L'index est divisé en pages de sorte que des parties de l'index peut être chargé et déchargé de la mémoire en fonction de l'utilisation.

Lorsque vous demandez un ensemble de lignes, SQL utilise l'index pour trouver les lignes plus rapidement que le tableau de numérisation (en regardant chaque ligne).

SQL cluster et les index non cluster. Ma compréhension de l'index cluster est qu'ils grouper des valeurs de l'indice sur la même page. De cette façon, lorsque vous demandez toutes les lignes correspondant à une valeur d'index, SQL pouvez le retour de ces lignes à partir d'un cluster de page de mémoire. C'est pourquoi, en essayant de cluster de l'indice de colonne GUID est une mauvaise idée - vous n'essayez pas de cluster de valeurs aléatoires.

Lors de l'indexation d'une colonne de type integer, SQL index contient un ensemble de lignes pour chaque valeur de l'indice. Si vous avez une plage de 1 à 10, alors que vous auriez 10 index des pointeurs. En fonction du nombre de lignes il y a de cela peut être contacté différemment. Si votre requête de recherche de l'index matching "1", et puis d'où le Nom contient "Fred" (en supposant que le Nom de colonne n'est pas indexé), SQL obtient l'ensemble des lignes correspondant à "1" très rapidement, puis les analyses de la table pour trouver le reste.

Donc, ce que SQL est vraiment en train de faire est d'essayer de réduire l'ensemble de travail (nombre de lignes), il a à parcourir.

Lors de l'indexation d'un champ de bits (ou une gamme étroite), vous seulement de réduire le travail défini par le nombre de lignes correspondant à cette valeur. Si vous avez un petit nombre de lignes correspondant elle permettrait de réduire votre jeu de travail beaucoup. Pour un grand nombre de lignes à 50/50 de distribution, il peut vous acheter très peu de gain de performance vs en gardant l'index à jour.

La raison tout le monde dit d'essai est parce que SQL contient un très intelligents et complexes d'optimisation qui peut ignorer un indice s'il décide de la table de balayage est plus rapide, ou peut utiliser un tri, ou peuvent organiser des pages de mémoire cependant, il sacrément bien aime.

Answer 2

Je viens de rencontrer cette question par le biais d'une autre. En supposant que votre déclaration selon laquelle seule une poignée d'enregistrements prend la valeur 1 (et que ce sont ceux-là qui vous intéressent), un index filtré peut constituer un bon choix. Quelque chose comme:

 create index [IX_foobar] on dbo.Foobar (FooID) where yourBitColumn = 1
 

Cela créera un index considérablement plus petit que l'optimiseur est suffisamment intelligent pour utiliser lorsqu'il s'agit d'un prédicat dans votre requête.

Answer 3

100 millions d'enregistrements, seuls quelques-uns ayant le champ de bits défini sur 1? Oui, je pense que l'indexation du champ de bits accélérerait considérablement l'interrogation des enregistrements bit = 1. Vous devriez obtenir une durée de recherche logarithmique à partir de l'index, puis ne toucher que les quelques pages avec des enregistrements bit = 1. Sinon, vous devez toucher toutes les pages du tableau des 100 millions d’enregistrements.

Là encore, je ne suis certainement pas un expert en base de données et il se peut que quelque chose d'important manque.

Answer 4

Bien que je ne pense pas que j'indexerais JUST un bit de colonne à part, il est très courant d'inclure des colonnes de bits dans le cadre d'un index composé.

Un exemple simple serait un index sur ACTIVE, LASTNAME au lieu de simplement nom, lorsque votre application recherche presque toujours des clients actifs.

Answer 5

Dans le cas où vous ne l'avez pas lu, Jason Massie a écrit récemment un article qui traitent de ce sujet.

http://statisticsio.com/Home/tabid/36/articleType/ArticleView/articleId/302/Never-Index-a-BIT.aspx

Edit: Nouvel article de localisation http://sqlserverpedia.com/blog/sql-server-bloggers/never-index-a-bit

Wayback machine pour les "Nouveaux" de l'article de l'emplacement: http://web.archive.org/web/20120201122503/http://sqlserverpedia.com/blog/sql-server-bloggers/never-index-a-bit/

Le nouveau Serveur SQL Pedia emplacement est Toadworld, qui dispose d'un nouvel article de Kenneth Fisher discuter de ce sujet:

http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2014/02/17/dba-myths-an-index-on-a-bit-column-will-never-be-used.aspx