Question Puis-je optimiser une requête SELECT DISTINCT x FROM hugeTable en créant un index sur la colonne x?


J'ai une énorme table, ayant un nombre beaucoup plus petit (par ordre de grandeur) de valeurs distinctes sur certaines colonnes x.

J'ai besoin de faire une requête comme SELECT DISTINCT x FROM hugeTable, et je veux le faire relativement vite.

J'ai fait quelque chose comme CREATE INDEX hugeTable_by_x ON hugeTable(x), mais pour une raison quelconque, même si la sortie est petite, l'exécution de la requête n'est pas aussi rapide. Le plan de requête montre que 97% du temps est consacré à l'analyse d'index de hugeTable_by_x, avec un nombre estimé de lignes égal à la taille de la table entière. Ceci est suivi, entre autres, d'une opération de correspondance de hachage.

Depuis que j'ai créé un index sur la colonne xPuis-je m'attendre à ce que cette requête s'exécute très rapidement?

Notez que j'utilise Microsoft SQL Server 2005.


19
2018-05-12 05:54


origine


Réponses:


Ce n'est probablement pas un problème d'indexation, mais un problème de conception de données. Normalisation, pour être précis. Le fait que vous ayez besoin d'interroger des valeurs distinctes d'un champ, et même de vouloir ajouter un index, indique clairement que le champ doit être normalisé dans une table distincte avec une (petite) clé de jointure. Ensuite, les valeurs distinctes seront immédiatement disponibles en analysant la table étrangère de recherche beaucoup plus petite.

Mettre à jour
Pour contourner ce problème, vous pouvez créer un vue indexée sur un agrégat par le champ "distinct". COUNT_BIG est un agrégat autorisé dans les vues indexées:

create view vwDistinct
with schemabinding
as select x, count_big(*)
from schema.hugetable
group by x;

create clustered index cdxDistinct on vwDistinct(x);

select x from vwDistinct with (noexpand);

22
2018-05-12 06:12



SQL Server n'implémente aucune fonctionnalité pour rechercher directement la valeur distincte suivante dans un index ignorant les doublons en cours de route.

Si vous avez beaucoup de doublons, vous pouvez utiliser un CTE récursif pour le simuler. La technique vient de ici. ("DISTINCT super rapide utilisant un CTE récursif"). Par exemple:

with recursivecte as (
  select min(t.x) as x
  from hugetable t
  union all
  select ranked.x
  from (
    select t.x,
           row_number() over (order by t.x) as rnk
    from hugetable t
    join recursivecte r
      on r.x < t.x
  ) ranked
  where ranked.rnk = 1
)
select *
from recursivecte
option (maxrecursion 0)

6
2018-05-12 19:37



Si vous connaissez les valeurs à l'avance et qu'il y a un index sur la colonne x (ou si chaque valeur est susceptible d'apparaître rapidement sur une analyse complète de la table entière), il est beaucoup plus rapide d'interroger chacun individuellement:

select vals.x
from [values] as vals (x)
where exists (select 1 from bigtable where bigtable.x = vals.x);

Procéder en utilisant existe () fera autant de recherches d'index qu'il y a de valeurs valides.

Comme vous l'avez écrit (ce qui est correct si les valeurs ne sont pas connues à l'avance), le moteur de requête devra lire la table entière et le hash agrégera le mess pour extraire les valeurs. (Ce qui rend l'index inutile.)


1
2018-05-12 10:16



Non, mais il existe des solutions de contournement (à l'exclusion de la normalisation):

Une fois que l'index est en place, il est alors possible d'implémenter en SQL ce que l'optimiseur pourrait faire automatiquement:

https://stackoverflow.com/a/29286754/538763  (plusieurs solutions de contournement citées)

D'autres réponses disent que vous pouvez normaliser ce qui résoudrait votre problème mais même une fois que son serveur SQL normalisé aime toujours effectuer une analyse pour trouver le max () dans le ou les groupes. Solutions de contournement:

https://dba.stackexchange.com/questions/48848/efficiently-query-max-over-multiple-ranges?rq=1


1
2017-12-24 01:08



Peut-être. Bien que cela ne soit pas garanti, cela dépend entièrement de la requête.

Je suggère de lire cet article de Gail Shaw (partie 1 et partie 2).


0
2018-05-12 05:58



En faisant un SELECT DISTINCT sur un champ indexé, une analyse d'index est logique, car l'exécution doit toujours analyser chaque valeur de l'index pour la table entière (en supposant que WHERE clause, comme cela semble être le cas par votre exemple).

Les index ont généralement plus d'impact sur WHERE conditions, JOINS, et ORDER BY clauses.


0
2018-05-12 06:04



Selon votre description du plan d'exécution, je pense que c'est la meilleure exécution possible.

L'index Scan lit l'intégralité de l'index tel qu'il est stocké (pas dans l'ordre d'index), le HASH MATCH fait la distinction.

Il pourrait y avoir d’autres façons de contourner votre problème. Dans SQL Server, les vues indexées me viennent à l’esprit. Cependant, cela pourrait vous donner un gros succès pour les écritures sur cette table.


0
2018-05-12 06:04