Dopo i test, veniva eseguita una query condizionale su una tabella contenente più di 4 milioni di record, e il tempo di query arrivava fino a 40 secondi. Pertanto, è molto importante migliorare l'efficienza della query delle istruzioni SQL. Di seguito sono riportati alcuni metodi di ottimizzazione delle query statement ampiamente diffusi su Internet:
Prima di tutto, quando il volume dati è grande, dovresti cercare di evitare di scansionare l'intera tabella e considerare di costruire indici sulle colonne coinvolte in dove e ordine per dato, il che può accelerare notevolmente il recupero dei dati. Tuttavia, ci sono alcune situazioni in cui l'indicizzazione non funziona:
1. Cerca di evitare di usare operatori != o <> nella clausola where, altrimenti il motore abbandonerà l'uso degli indici ed eseguirà una scansione completa della tabella.
2. Cerca di evitare giudizio di valore nullo sui campi della clausola where, altrimenti il motore abbandonerà l'uso degli indici ed eseguirà una scansione completa delle tabelle, come ad esempio:
seleziona id da t dove num è nullo
Puoi impostare il valore predefinito di 0 sul num, assicurarti che non ci sia alcun valore nullo nella colonna numero della tabella, e poi interrogare così:
seleziona l'ID da t dove num=0
3. Cerca di evitare di usare OR nelle condizioni dove di unirsi, altrimenti il motore abbandonerà l'uso dell'indice ed eseguirà una scansione completa della tabella, ad esempio:
seleziona ID da t dove num=10 o num=20
Puoi fare query così:
seleziona l'ID da t dove num=10
Unione Tutti
seleziona id da t dove num=20
4. La seguente query porterà anch'essa a una scansione completa della tabella:
Seleziona ID da T dove il nome è '%abc%'
Per migliorare l'efficienza, considera la ricerca a testo completo.
5. In e non in devono essere usati con cautela, altrimenti porteranno a una scansione completa della tabella, come ad esempio:
seleziona l'ID da t dove num in(1,2,3)
Per valori continui, se puoi usare tra di essi, non usare in:
seleziona l'ID da t dove num è tra 1 e 3
6. Se usi il parametro nella clausola where, farà anche scansionare l'intera tabella. Poiché SQL risolve solo le variabili locali a runtime, ma l'ottimizzatore non può rimandare la selezione dei piani di accesso a runtime; Deve essere selezionato al momento della compilazione. Tuttavia, se un piano di accesso viene stabilito al momento della compilazione, il valore della variabile è ancora sconosciuto e quindi non può essere utilizzato come elemento di input per la selezione dell'indice. Le seguenti dichiarazioni saranno scansionate integralmente:
seleziona ID da t dove num=@num
Puoi forzare la query a usare invece un indice:
Seleziona l'ID da t con (Index(Index(Nome indice)) dove num=@num
7. Cerca di evitare di esprimere i campi nella clausola where, il che porterebbe il motore ad abbandonare l'uso dell'indice ed eseguire la scansione completa della tabella. Per esempio:
seleziona l'ID da t dove num/2=100
Dovrebbe essere cambiato in:
seleziona ID da t dove num=100*2
8. Cerca di evitare di eseguire operazioni di funzione sui campi della clausola where, che porteranno il motore ad abbandonare l'uso degli indici ed eseguire la scansione completa della tabella. Per esempio:
seleziona l'ID da t dove substring(name,1,3)='abc' – name id che inizia con abc
Seleziona ID da t dove datediff(day,createdate,'2005-11-30′)=0–'2005-11-30′ generato ID
Dovrebbe essere cambiato in:
Seleziona ID da T dove il nome è 'ABC%'
Seleziona l'ID da T dove è stato creato>='2005-11-30′ e creato<'2005-12-1′
9. Non eseguire funzioni, operazioni aritmetiche o altre operazioni di espressione a sinistra del "=" nella clausola where, altrimenti il sistema potrebbe non essere in grado di usare correttamente l'indice.
10. Quando si utilizza un campo indice come condizione, se l'indice è un indice composito, allora il primo campo nell'indice deve essere usato come condizione per garantire che il sistema utilizzi l'indice, altrimenti l'indice non verrà utilizzato e l'ordine dei campi deve essere il più possibile coerente con l'ordine dell'indice.
11. Non scrivere alcune query prive di significato, come generare una struttura di tabella vuota:
Seleziona col1,col2 in #t da T dove 1=0
Questo tipo di codice non restituisce alcun set di risultati, ma consuma risorse di sistema, quindi dovrebbe essere cambiato in qualcosa del genere:
crea tabella #t(...)
12. Molte volte è una buona scelta usare existe invece che in:
seleziona num da a dove num in (seleziona num da b)
Sostituisci con la seguente affermazione:
Seleziona num da A dove esiste (seleziona 1 da B dove num=A.num)
Cose a cui prestare attenzione quando si costruisce un indice:
1. Non tutti gli indici sono validi per le query, SQL si basa sui dati nella tabella per ottimizzare la query; quando la colonna dell'indice ha una grande quantità di duplicazione di dati, le query SQL potrebbero non utilizzare l'indice, ad esempio una tabella ha campi sesso, maschio, femmina quasi metà ciascuno, quindi anche se l'indice è costruito sul sesso, non avrà un ruolo nell'efficienza delle query.
2. Più indici non sono migliori, l'indice può certamente migliorare l'efficienza della selezione corrispondente, ma riduce anche l'efficienza di inserimento e aggiornamento, perché l'indice può essere ricostruito durante l'inserimento o l'aggiornamento, quindi come costruire un indice deve essere valutato attentamente, a seconda della situazione specifica. È meglio non avere più di 6 indici in una tabella e, se ce ne sono troppi, considerare se sia necessario costruire indici su alcune colonne usate raramente.
3. Evitare di aggiornare il più possibile le colonne di dati degli indici clusterizzati, perché l'ordine delle colonne di dati indicizzate clusterizzate è l'ordine fisico di archiviazione dei record delle tabelle e, una volta cambiato il valore della colonna, ciò porterà all'aggiustamento dell'ordine dell'intero record della tabella, consumando risorse considerevoli. Se il sistema applicativo deve aggiornare frequentemente le colonne degli indici clusterizzati, deve considerare se l'indice debba essere costruito come un indice clusterizzato.
Altri punti da notare:
1. Cerca di usare campi numerici e cerca di non progettare campi che contengano solo informazioni numeriche come caratteri, il che ridurrà le prestazioni di query e connessioni, aumentando il sovraccarico di memorizzazione. Questo perché il motore confronta ogni carattere della stringa uno per uno durante l'elaborazione di query e join, mentre per i tipi numerici è necessario confrontarlo solo una volta.
2. Non usare selezionare * da t da nessuna parte, sostituire "*" con una specifica lista di campi e non restituire campi non utilizzati.
3. Prova a usare variabili di tabella invece di tabelle temporanee. Se la variabile della tabella contiene una grande quantità di dati, si noti che l'indice è molto limitato (solo l'indice principale della chiave).
4. Evitare di creare e cancellare frequentemente tabelle temporanee per ridurre il consumo di risorse delle tabelle di sistema.
5. Le tabelle temporanee non sono inutilizzabili, e usarle in modo appropriato può rendere alcune routine più efficaci, ad esempio quando è necessario fare riferimento ripetutamente a una tabella grande o a un dataset in una tabella comunemente utilizzata. Tuttavia, per eventi una tantum, è meglio usare una tabella di esportazione.
6. Quando si crea una tabella temporanea, se la quantità di dati inseriti contemporaneamente è elevata, si può usare select into invece di create table per evitare di aumentare la velocità con un gran numero di log; Se la quantità di dati non è grande, per facilitare le risorse della tabella di sistema, dovresti prima creare la tabella e poi inserirla.
7. Se viene utilizzata una tabella temporanea, assicurarsi di eliminare esplicitamente tutte le tabelle temporanee alla fine della procedura memorizzata, troncare prima la tabella e poi abbassare la tabella, per evitare un blocco lungo della tabella di sistema.
8. Cerca di evitare di usare il cursore, perché l'efficienza del cursore è scarsa; se i dati gestiti dal cursore superano le 10.000 righe, dovresti considerare la riscriverla.
9. Prima di utilizzare il metodo basato sul cursore o il metodo temporaneo della tabella, dovresti prima cercare soluzioni basate su insiemi per risolvere il problema, e il metodo basato su insiemi è solitamente più efficace.
10. Come le tabelle temporanee, il cursore non è inutilizzabile. Usare FAST_FORWARD cursori per piccoli dataset è spesso meglio rispetto ad altri metodi di elaborazione riga per riga, specialmente se devi fare riferimento a più tabelle per ottenere i dati necessari. Le routine che includono il "totale" nel set di risultati sono solitamente più veloci di quelle eseguite con il cursore. Se il tempo di sviluppo lo permette, si possono provare sia metodi basati su cursori che su insiemi per vedere quale funziona meglio.
11. Impostare SET NOCOUNT ON all'inizio di tutte le stored procedure e trigger, e impostare SET NOCOUNT OFF alla fine. Non è necessario inviare DONE_IN_PROC messaggi al client dopo aver eseguito ogni istruzione della procedura memorizzata e del trigger.
12. Cerca di evitare di restituire dati grandi al cliente; se il volume di dati è troppo grande, dovresti considerare se la richiesta corrispondente sia ragionevole.
13. Cercare di evitare operazioni di transazione grandi e migliorare la capacità di concorrenza del sistema.
|
Pubblicato su 11/05/2018 13:57:06
|
|
|
Pubblicato su 17/05/2018 10:12:27