Posso ottimizzare una SELECT DISTINCT x DA hugeTable query con la creazione di un indice sulla colonna di x?

Ho un enorme tavolo, avendo un numero molto più piccolo (di diversi ordini di grandezza) di valori distinti su qualche colonna x.

Ho bisogno di fare una query come SELECT DISTINCT x FROM hugeTable, e io voglio fare questo in modo relativamente veloce.

Ho fatto qualcosa di simile CREATE INDEX hugeTable_by_x ON hugeTable(x), ma per qualche ragione, anche se la produzione è piccola, l’esecuzione della query non è veloce. Il piano di query mostra che il 97% del tempo viene speso per la Scansione dell’Indice di hugeTable_by_x, con un numero stimato di righe è uguale alla dimensione dell’intera tabella. Questa è seguita da, tra le altre cose, un Hash Corrisponde operazione.

Ho creato un indice sulla colonna x, non posso aspettare questa query per eseguire molto rapidamente?

Nota che sto utilizzando Microsoft SQL Server 2005.

  • Ciao a tutti, È la Colonna è l’Indicizzazione di un ‘int’ di campo? L’idea di un idex su una tabella per il sistema, una mappa in cui l’indice si trova nel modello, e quindi rendere più facile per recuperare. Se questo campo non ha rilevanza oltre che essere un valore, è davvero suole fare molta differenza, perche ‘ deve ancora analizzare la tabella.
  • Se, per esempio, ci sono 1000 righe in hugeTable con x=1, quindi hugeTable_by_x ancora deve contenere 1000 riferimenti a tali righe la foglia di livello per x=1. E se questi riferimenti sono ampie (qual è la chiave di clustering per hugeTable?), l’indice sta per essere abbastanza grande per sé.
  • Nota che ho anche provato SELECT x FROM hugeTable GROUP BY x, e dà esattamente lo stesso piano di query.



7 Replies
  1. 22

    È probabile che questo non è un problema di indicizzazione, ma uno dei dati di progettazione. La normalizzazione, per essere precisi. Il fatto che avete bisogno di query distinte valori di un campo, e anche disposto ad aggiungere un indice, è un forte indicatore che il campo deve essere normalizzati in un tavolo separato con un (piccolo) chiave di join. Quindi i valori distinti saranno immediatamente disponibili attraverso la scansione molto più piccolo di ricerca stranieri tabella.

    Aggiornamento

    Come soluzione alternativa, è possibile creare un vista indicizzata su un’aggregazione con la ‘distinti’ campo. COUNT_BIG è un aggregato che è consentito in viste indicizzate:

    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);
    • Mentre quello che dici è sicuramente vero, il caso si sta parlando può essere esattamente il MOTIVO per cui un SELECT DISTINCT è stato fatto, come parte del processo di normalizzazione. Per esempio, abbiamo un sistema che porta a un feed di dati da un insieme di scaricato i file FTP. Questi dati NON normalizzati a tutti. Che è la metà l’esatta funzione del nostro processo è quello di normalizzare i dati, che carichiamo sul nostro sistema. Così, per esempio, abbiamo una query (in pseudocodice) come INSERT INTO NORMALIZEDVALUELIST (NAME) SELECT DISTINCT SOMEFIELD FROM UNNORMALIZEDSOURCE WHERE <SOMEFIELD NOT IN NORMALIZEDVALUELIST(NAME)>.
    • Riguardo la soluzione, non il costo della cpu nel tempo di mantenere una vista indicizzata attraverso operazioni CRUD essere superiore a quella di un semplice indice sulla tabella originale? Poi alcuni di altre soluzioni inviato maggio le scelte migliori.
    • La vista indicizzata non aggiungere un costo per le operazioni di scrittura. Penso che potrebbe in realtà essere più conveniente rispetto a un indice sulla colonna, se ci sono solo un paio di valori distinti e la tabella è di grandi dimensioni. Se non altro, lo spazio occupato dalla vista indicizzata è quindi molto inferiore a quello che l’indice. Quindi la risposta è “dipende”.
  2. 6

    SQL Server non realizzare qualsiasi impianto a cercare direttamente al prossimo valore distinto in un indice di saltare duplicati lungo la strada.

    Se si dispone di molti doppioni, allora si può essere in grado di utilizzare un CTE ricorsiva per simulare questo. La tecnica viene da qui. (“Super-veloce DISTINTE utilizzando un CTE ricorsiva”). Per esempio:

    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)
  3. 2

    Se si conoscono i valori in anticipo e vi è un indice sulla colonna x (o se ogni valore è probabile che appaiono velocemente su una seq scan di tutta la tabella), è molto più veloce di query singolarmente:

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

    Di procedere utilizzando esiste() per fare il maggior numero di indice di ricerche come ci sono valori validi.

    Il modo in cui hai scritto (che è corretto se i valori non sono noti in anticipo), il motore delle query avrà bisogno di leggere l’intera tabella hash di aggregazione casino di estrarre i valori. (Il che rende l’indice inutile.)

  4. 1

    No. Ma ci sono alcuni accorgimenti (esclusi normalizzazione):

    Una volta che l’indice è a posto, quindi la sua possibile implementazione in SQL che l’ottimizzatore può fare automaticamente:

    https://stackoverflow.com/a/29286754/538763 (più soluzioni alternative citato)

    Altre risposte dicono che si può normalizzare che avrebbe risolto il problema, ma ancora una volta la sua normalizzato di SQL Server piace ancora di eseguire una scansione per trovare il max() all’interno del gruppo(s). Soluzioni:

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

  5. 0

    Quando si fa un SELECT DISTINCT su un campo indicizzato, un indice di scansione senso, in quanto l’esecuzione deve ancora eseguire la scansione di ogni valore dell’indice per l’intera tabella (supponendo che non WHERE clausola, come sembra essere il caso con il tuo esempio).

    Indici di solito hanno più di un impatto sulla WHERE condizioni, JOINS, e ORDER BY clausole.

    • Non è “necessario eseguire la scansione di ogni valore dell’indice”. Internamente è possibile eseguire una serie di ricerche (ad esempio, binario di ricerca) per trovare le successive variazioni di valore.
  6. 0

    Come per la descrizione delle modalità di esecuzione del piano, io credo che la migliore esecuzione possibile.

    L’Indice di Scansione si legge l’intero indice memorizzati (non in ordine di indice), HASH MATCH non distinti.

    Ci potrebbero essere altri modi per aggirare il problema. In SQL Server, Viste Indicizzate venire nella mia mente. Tuttavia, che potrebbe dare un grande successo per scrivere su quel tavolo.

Lascia un commento