Best practice da seguire con gli indici del database [chiuso]

Ciò dipende in parte da ciò che il database deve essere utilizzato, poiché in generale gli indici rallentano gli inserimenti e gli aggiornamenti e velocizzano le query. In un data warehouse, generalmente non ci sono aggiornamenti e inserti in batch, rendendo più facile la creazione di indici e un sacco di domande, che si velocizzano con molti indici. In un database on-line per le vendite sul Web e simili, ci sono molti inserimenti e aggiornamenti, quindi avere più di alcuni indici attentamente selezionati li rallenterà.

Se si ottengono molte query di un tipo specifico, è possibile creare un indice per la query, sebbene ciò sia più per l'elaborazione in linea rispetto ai data warehouse. Se alcune colonne si presentano molto nelle query, potresti volere un indice su quella colonna, e questo è particolarmente utile per i data warehouse, che vengono interrogati in molti modi diversi e spesso imprevedibili.

Ogni volta che aggiungi o rimuovi un indice, prova a eseguire un test delle prestazioni per vedere quale effetto ha. Senza quello, stai sparando alla cieca.

Esistono libri sulla messa a punto di query e database, spesso specifici per un sistema di database e utilizzando gli strumenti di questo RDBMS. Se ti trovi a dover ottimizzare molto il database, però, stai eseguendo una grande operazione e probabilmente dovresti assumere un DBA con competenze adeguate.

Dipende molto da come usi le tue tabelle. Non esiste una risposta semplice e semplice.

Il miglior consiglio che posso darti è: usa un consigliere tuning . Analizzeranno i comandi del database mentre stai usando l'applicazione, quindi eseguiranno dei test di carico su di esso per fornirti consigli significativi.

Esistono per SQL Server & Oracle . Non so se altri DBMS li abbiano, solo dubito che non forniscano questi strumenti di base.

Poche raccomandazioni casuali:

• Gli indici forniscono guadagni ad alte prestazioni se applicati su colonne spesso incluse nella clausola WHERE
• Utilizza l'indice cluster per la colonna più utilizzata nelle query.
• Non dimenticare che puoi creare più indici con una combinazione di colonne (come vengono utilizzate nelle query)
• Avere molti indici ridurrà le prestazioni dei comandi INSERT.

Ultimo consiglio : se le prestazioni di DB sono davvero importanti per il tuo progetto, assumi uno specialista. È quello che ho fatto.

@Pierre 303 l'ha già detto, ma lo dirò di nuovo. DO utilizza indici su combinazioni di colonne. Un indice combinato su (a, b) è solo leggermente più lento per le query su a rispetto a un indice su a da solo ed è decisamente migliore se la query combina entrambe le colonne. Alcuni database possono unire indici su a e b prima di colpire la tabella, ma questo non è quasi buono come avere un indice combinato. Quando crei un indice combinato, devi inserire la colonna che è più probabile che venga ricercata per prima nell'indice combinato.

Se il tuo database lo supporta, DO metti indici su funzioni che appaiono nelle query piuttosto che nelle colonne. (Se stai chiamando una funzione su una colonna, gli indici su quella colonna sono inutili.)

Se utilizzi un database con tabelle temporanee reali che puoi creare e distruggere al volo (ad esempio PostgreSQL, MySQL, ma non Oracle), quindi DO creare indici su tabelle temporanee.

Se utilizzi un database che lo consente (ad es. Oracle), DO blocca in piani di query validi. Gli ottimizzatori di query nel tempo modificheranno i piani di query. Di solito migliorano il piano. Ma a volte lo fanno drammaticamente peggio. In genere non si noteranno miglioramenti del piano: la query non è un collo di bottiglia. Ma un singolo piano errato può distruggere un sito occupato.

NON hai indici sulle tabelle su cui stai per caricare un grosso carico di dati. È molto, molto più veloce rilasciare gli indici, caricare i dati, quindi ricostruire gli indici anziché conservarli mentre carichi la tabella.

NON utilizza gli indici sulle query che devono accedere a più di una piccola frazione di una tabella di grandi dimensioni. (Quanto piccolo dipende dall'hardware: il 5% è una regola empirica.) Ad esempio, se si dispone di dati con nomi e sesso, i nomi sono un buon candidato per l'indicizzazione poiché ogni nome dato rappresenta una piccola frazione delle righe totali. Non sarebbe utile indicizzare il genere poiché sarà comunque necessario accedere al 50% delle righe. Invece, vuoi veramente usare una scansione completa della tabella. Il motivo è che gli indici finiscono per accedere a un file di grandi dimensioni in modo casuale, causando la necessità di cercare il disco. Le ricerche sul disco sono lente. Per esempio, di recente sono riuscito a velocizzare una query lunga un'ora simile a:

SELECT small_table.id, SUM(big_table.some_value)
FROM small_table
  JOIN big_table
    ON big_table.small_table_id = small_table.id
GROUP BY small_table.id

meno di 3 minuti riscrivendolo come segue:

SELECT small_table.id, big_table_summary.summed_value
FROM small_table
  JOIN (
      SELECT small_table_id, SUM(some_value) as summed_value
      FROM big_table
      GROUP BY small_table_id
    ) big_table_summary
    ON big_table_summary.small_table_id =  small_table.id

che ha costretto il database a capire che non dovrebbe tentare di utilizzare l'indice allettante su big_table.small_table_id . (Un buon database, come Oracle, dovrebbe capirlo da solo: questa query era in esecuzione su MySQL.)

Aggiornamento: Ecco una spiegazione del punto di ricerca del disco che ho creato. Un indice dà una rapida occhiata per dire dove sono i dati nella tabella. Di solito è una vittoria visto che guarderai solo i dati che devi guardare. Ma non sempre, soprattutto se alla fine si guardano molti dati. I dischi trasmettono bene i dati, ma rallentano le ricerche. La ricerca casuale dei dati sul disco richiede 1/200 di secondo. La versione lenta della query finì per fare qualcosa come 600.000 di quelle e ci volle quasi un'ora. (Ha fatto più ricerche, ma la cache ne ha catturate alcune.) Al contrario la versione veloce sapeva che doveva leggere tutto e trasmettere dati a qualcosa come 70 MB / secondo. Ha ottenuto una tabella da 11 GB in meno di 3 minuti.

Fondamentalmente, gli indici accelerano la ricerca ma rallentano la scrittura e occupano spazio. Questo è il compromesso fatto.

Qualsiasi campo che viene frequentemente utilizzato per partecipare, cercare / confrontare o ordinare da un candidato per un indice. Sapere che è davvero benifici, misura. Tuttavia, le chiavi esterne di tabelle strongmente unite con lotti (> 1000) di record e pochi inserti pagheranno.

Per i campi di testo, è possibile indicizzare su una parte del campo (ad esempio, i primi 6 caratteri) che velocizzerebbe la query ma alleggerire il carico sugli indici. Le ricerche di testo completo (ricerca su like %substring% ) richiedono tecniche diverse, che non conosco, quindi non posso darti un consiglio.

Una situazione importante in cui gli indici non aiuteranno: non è possibile utilizzare l'indice dei campi data o data / ora completi durante la ricerca (/ join / ordine) in una parte della data. Un indice su date_created non ti aiuterà con una query come select * from t where year(date_created) = 2011 . In mysql non puoi creare un indice su parte della data. (Quando usi ' between ' invece di year() puoi usare l'indice nel campo data.)

Maggiori informazioni su MYSQL nel manuale: link

DO: indicizza i pochissimi campi a cui accedi più attraverso query e / o confronti.

NON: Indica ogni campo nella tabella pensando che lo renderà più veloce.

Non ho statistiche su di esso, ma cerco di mantenere non più di 4 campi indicizzati in una tabella se posso aiutarlo. La normalizzazione dei miei database di solito aiuta a mantenere bassi questi numeri poiché tutto diventa ricercabile con la chiave numerica (che è comunque più veloce). Cerco di stare lontano dai campi di testo completi per l'indicizzazione. Sono piuttosto pesanti.

DO: cerca di mantenere al minimo la dimensione totale dell'indice cluster. Le voci dell'indice cluster verranno incluse in altri indici non in cluster e da qui deriverà un potenziale per lo spreco di spazio su disco.

Pensa a un tavolo come un lessico, in cui gli articoli sono ordinati per ordine di apparizione (o nessun ordine utile) e di indice di tabella come indice di un libro per quel lessico.

Usi un indice per trovare rapidamente qualcosa in un libro. Invece di eseguire la scansione dell'intero libro, devi solo trovare la chiave nell'indice (un indice di solito viene in qualche modo ordinato (per categoria, per settore scientifico, per epoca storica, ecc.), Questo significa anche che non dovrai eseguire la scansione l'intero indice) e poi salta alla pagina giusta.

A differenza di un libro, tuttavia, una tabella non viene stampata una volta e quindi immutabile. Viene aggiornato continuamente e pertanto ogni indice deve essere aggiornato con esso. Ciò ovviamente ha un costo in termini di spazio e tempo, che può essere giustificato solo dall'utilità di un indice.

Quindi usa un indice per una colonna, se quella colonna è usata come una chiave nelle query di ricerca frequenti, e non usarne una, se non lo è. La parola frequente è tanto un quantificatore quanto più spesso, in generale. Alla fine dovrai fare una buona stima quali sono frequenti, quindi semplicemente eseguire benchmark delle prestazioni con o senza indice in caso di dubbio.