Spiegazione degli indici SQL, pt. 1

Usato correttamente, un indice di database SQL può essere così efficace da sembrare magico. Ma la seguente serie di esercizi mostrerà che, sotto, la logica della maggior parte degli indici SQL, e il loro corretto utilizzo, è piuttosto semplice.

In questa serie, Spiegazione degli indici SQL , analizzeremo le motivazioni per l'utilizzo degli indici per accedere ai dati e per la progettazione degli indici nel modo in cui viene eseguita da tutti i moderni RDBMS. Esamineremo quindi gli algoritmi utilizzati per restituire i dati per modelli di query specifici.

Non devi sapere molto sugli indici per essere in grado di seguire SQL Indexes Explained . Ci sono solo due presupposti:

• Conoscenza SQL di base
• Conoscenza di base di qualsiasi linguaggio di programmazione

Gli argomenti principali in cui verranno spiegati gli indici SQL sono:

• Perché abbiamo bisogno di indici di database SQL; visualizzazione dei piani di esecuzione mediante indici
• Progettazione degli indici: quali indici rendono una query veloce ed efficiente
• Come possiamo scrivere una query per utilizzare efficacemente gli indici
• L'impatto dell'uso degli indici in SQL sull'efficienza di lettura/scrittura
• Indici di copertura
• Il partizionamento, il suo impatto su lettura e scrittura e quando usarlo

Questo non è solo un tutorial sull'indice SQL, è un'analisi approfondita della meccanica alla base degli indici.

Scopriremo come un RDBMS utilizza gli indici facendo esercizi e analizzando i nostri metodi di risoluzione dei problemi. Il nostro materiale per gli esercizi è costituito da Fogli Google di sola lettura. Per fare un esercizio, puoi copiare il Foglio Google ( File → Crea una copia ) o copiarne il contenuto nel tuo Foglio Google.

In ogni esercizio, mostreremo una query SQL che utilizza la sintassi Oracle. Per le date, utilizzeremo il formato ISO 8601, YYYY-MM-DD .

Esercizio 1: Tutte le prenotazioni di un cliente

Il primo compito, non farlo ancora, è trovare tutte le righe del foglio di calcolo Prenotazione per un cliente specifico di un sistema di prenotazione di hotel e copiarle nel proprio foglio di lavoro, simulando l'esecuzione della seguente query:

 SELECT * FROM Reservations WHERE ClientID = 12;

Ma vogliamo seguire un metodo particolare.

Approccio 1: nessun ordinamento, nessun filtro

Per il primo tentativo, non utilizzare alcuna funzione di ordinamento o filtro. Per favore, registra il tempo trascorso. Il foglio risultante dovrebbe contenere 73 righe.

Questo pseudocodice illustra l'algoritmo per eseguire l'attività senza ordinare:

 For each row from Reservations If Reservations.ClientID = 12 then fetch Reservations.*

In questo caso, abbiamo dovuto controllare tutte le 841 righe per restituire e copiare 73 righe che soddisfano la condizione.

Approccio 2: solo ordinamento

Per il secondo tentativo, ordina il foglio in base al valore della colonna ClientID . Non utilizzare filtri. Registra il tempo e confrontalo con il tempo impiegato per completare l'attività senza ordinare i dati.

Dopo l'ordinamento, l'approccio è simile al seguente:

 For each row from Reservations If ClientID = 12 then fetch Reservations.* Else if ClientID > 12 exit

Questa volta abbiamo dovuto controllare “solo” 780 righe. Se potessimo in qualche modo saltare alla prima fila, ci vorrebbe ancora meno tempo.

Ma se dovessimo sviluppare un programma per l'attività, questa soluzione sarebbe ancora più lenta della prima. Questo perché prima dovremmo ordinare tutti i dati, il che significa che ogni riga dovrebbe essere accessibile almeno una volta. Questo approccio è valido solo se il foglio è già ordinato nell'ordine desiderato.

Esercizio 2: Il numero di prenotazioni a partire da una determinata data

Ora il compito è contare il numero di check-in il 16 agosto 2020:

 SELECT COUNT (*) FROM Reservations WHERE DateFrom = TO_DATE('2020-08-16', 'YYYY-MM-DD')

Usa il foglio di calcolo dell'Esercizio 1. Misura e confronta il tempo impiegato per completare l'attività con e senza l'ordinamento. Il conteggio corretto è 91.

Per l'approccio senza ordinamento, l'algoritmo è sostanzialmente lo stesso dell'Esercizio 1.

Anche l'approccio di ordinamento è simile a quello dell'esercizio precedente. Divideremo semplicemente il ciclo in due parti:

 -- Assumption: Table reservation is sorted by DateFrom -- Find the first reservation from the 16th of August 2020. Repeat Read next row Until DateFrom = '2020-08-16' -- Calculate the count While DateFrom = '2020-08-16' Increase the count Read the next row

Esercizio 3: Indagine penale

L'ispettore di polizia chiede di vedere un elenco degli ospiti arrivati ​​in hotel il 13 e 14 agosto 2020.

 SELECT ClientID FROM Reservations WHERE DateFrom BETWEEN ( TO_DATE('2020-08-13', 'YYYY-MM-DD') AND TO_DATE('2020-08-14', 'YYYY-MM-DD') ) AND HotelID = 3;

Approccio 1: Ordinato solo per data

L'ispettore vuole la lista veloce. Sappiamo già che faremmo meglio a ordinare la tabella/foglio di calcolo in base alla data di arrivo. Se abbiamo appena terminato l'esercizio 2, siamo fortunati che la tabella sia già ordinata. Quindi, applichiamo l'approccio simile a quello dell'Esercizio 2.

Per favore, prova a registrare l'ora, il numero di righe che dovevi leggere e il numero di elementi nell'elenco.

 -- Assumption: Table reservation is sorted by DateFrom -- Find the first reservation from the 13th of August 2020. Repeat Read next row Until DateFrom >= '2020-08-13' -- Prepare the list While DateFrom < '2020-08-15' If HotelID = 3 then write down the ClientID Read the next row

Usando questo approccio, abbiamo dovuto leggere 511 righe per compilare un elenco di 46 ospiti. Se fossimo in grado di scorrere con precisione verso il basso, non avremmo effettivamente dovuto eseguire 324 letture dal ciclo di ripetizione solo per individuare il primo arrivo il 13 agosto. Tuttavia, abbiamo ancora dovuto leggere più di 100 righe per verificare se l'ospite è arrivato in hotel con un HotelID di 3 .

L'ispettore ha aspettato tutto quel tempo, ma non sarebbe stato felice: invece dei nomi degli ospiti e di altri dati rilevanti, abbiamo consegnato solo un elenco di ID privi di significato.

Torneremo su questo aspetto più avanti nella serie. Per prima cosa troviamo un modo per preparare l'elenco più velocemente.

Approccio 2: Ordinato per Hotel, quindi Data

Per ordinare le righe in base a HotelID quindi DateFrom , possiamo selezionare tutte le colonne, quindi utilizzare l'opzione di menu Fogli Google Dati → Intervallo di ordinamento .

 -- Assumption: Sorted according to HotelID and DateFrom -- Find the first reservation for the HotelID = 3. Repeat Read next row Until HotelID >= 3 -- Find the first arrival at the hotel on 13th of August While HotelID = 3 and DateFrom < '2020-08-13' Read the next row -- Prepare the list While HotelID = 3 and DateFrom < '2020-08-15' Write down the ClientID Read the next row

Abbiamo dovuto saltare i primi 338 arrivi prima di individuare il primo al nostro hotel. Successivamente, abbiamo esaminato 103 arrivi precedenti per individuare il primo il 13 agosto. Infine, abbiamo copiato 46 valori consecutivi di ClientID . Ci ha aiutato il fatto che nel terzo passaggio siamo stati in grado di copiare un blocco di ID consecutivi. Peccato che non siamo riusciti in qualche modo a saltare alla prima riga da quel blocco.

Approccio 3: Ordinato solo per hotel

Ora prova lo stesso esercizio utilizzando solo il foglio di calcolo ordinato da HotelID .

L'algoritmo applicato alla tabella ordinata solo per HotelID è meno efficiente rispetto a quando ordiniamo per HotelID e DateFrom (in quest'ordine):

 -- Assumption: Sorted according to HotelID -- Find the first reservation for the HotelID = 3. Repeat Read next row Until HotelID >= 3 -- Prepare the list While HotelID = 3 If DateFrom between '2020-08-13' and '2020-08-14' Write down the ClientID Read the next row

In questo caso, dobbiamo leggere tutti i 166 arrivi in ​​hotel con un HotelID di 3 , e per ciascuno, verificare se il DateFrom appartiene all'intervallo richiesto.

Approccio 4: Ordinato per data, poi Hotel

Importa davvero se ordiniamo prima per HotelID e poi DateFrom o viceversa? Scopriamolo: prova a ordinare prima per DateFrom , poi per HotelID .

 -- Assumption: Sorted according to DateFrom and HotelID -- Find the first arrival on 13th of August While DateFrom < '2020-08-13' Read the next row --Find the first arrival at the Hotel While HotelID < 3 and DateFrom < '2020-08-15' Read the next row Repeat If HotelID = 3 Write down the ClientID Read the next row Until DateFrom > '2020-08-14' or (DateFrom = '2020-08-14' and HotelID > 3)

Abbiamo individuato la prima riga con la data pertinente, quindi leggere di più fino a individuare il primo arrivo in hotel. Successivamente, per un certo numero di righe, sono state soddisfatte entrambe le condizioni, la data corretta e l'hotel giusto. Tuttavia, dopo gli arrivi all'Hotel 3, abbiamo avuto arrivi agli hotel 4, 5 e così via, per la stessa data. Dopo di loro, abbiamo dovuto leggere di nuovo le righe per il giorno successivo per gli hotel 1 e 2, finché non siamo stati in grado di leggere gli arrivi consecutivi al nostro hotel di interesse.

Come possiamo vedere, tutti gli approcci hanno un singolo blocco di dati consecutivo nel mezzo dell'insieme completo di righe, che rappresenta dati parzialmente abbinati. Gli approcci 2 e 4 sono gli unici in cui la logica ci consente di fermare completamente l'algoritmo prima di raggiungere la fine delle corrispondenze parziali.

L'approccio 4 ha dati completamente abbinati in due blocchi, ma l'approccio 2 è l'unico in cui i dati di destinazione sono tutti in un blocco consecutivo.

Dai numeri, è chiaro che l'Approccio 2 ha i maggiori vantaggi in questo caso.

Spiegazione degli indici SQL: conclusioni e prospettive

L'esecuzione di questi esercizi dovrebbe rendere chiari i seguenti punti:

1. La lettura da una tabella ordinata correttamente è più veloce.
2. Se una tabella non è già ordinata, l'ordinamento richiede più tempo rispetto alla lettura da una tabella non ordinata.
3. Trovare un modo per passare alla prima riga che corrisponde a una condizione di ricerca all'interno della tabella ordinata risparmierebbe molte letture.
4. Sarebbe utile avere un tavolo ordinato in anticipo.
5. Sarebbe utile mantenere le copie ordinate della tabella per le query più frequenti.

Ora, una copia ordinata di una tabella suona quasi come un indice di database. L'articolo successivo in Spiegazione degli indici SQL copre un'implementazione dell'indice rudimentale. Grazie per aver letto!