Indeșii SQL explicați, Pt. 1

Folosit corect, un index de bază de date SQL poate fi atât de eficient încât ar putea părea magic. Dar următoarea serie de exerciții va arăta că dedesubt, logica majorității indicilor SQL - și mânuirea lor corectă - este destul de simplă.

În această serie, SQL Indexes Explained , vom parcurge motivațiile pentru utilizarea indicilor pentru a accesa date și pentru proiectarea indicilor în modul în care este realizat de toate RDBMS-urile moderne. Ne vom uita apoi la algoritmii utilizați pentru a returna date pentru anumite modele de interogare.

Nu trebuie să cunoașteți prea multe despre indecși pentru a putea urma SQL Indexes Explained . Există doar două condiții prealabile:

• Cunoștințe de bază SQL
• Cunoștințe de bază a oricărui limbaj de programare

Principalele subiecte în care vor intra indexurile SQL explicate sunt:

• De ce avem nevoie de indici de baze de date SQL; vizualizarea planurilor de execuție folosind indici
• Designul indexului: care indici fac o interogare rapidă și eficientă
• Cum putem scrie o interogare pentru a folosi în mod eficient indecșii
• Impactul utilizării indicilor în SQL asupra eficienței de citire/scriere
• Indici de acoperire
• Partiționarea, impactul său asupra citirii și scrierii și când să o utilizați

Acesta nu este doar un tutorial de index SQL - este o scufundare profundă în înțelegerea mecanismelor de bază ale indicilor.

Ne vom da seama cum un RDBMS folosește indici făcând exerciții și analizând metodele noastre de rezolvare a problemelor. Materialul nostru de exerciții este format din Foi de calcul Google numai pentru citire. Pentru a face un exercițiu, puteți copia foaia Google ( Fișier → Faceți o copie ) sau puteți copia conținutul acesteia în propria foaie Google.

În fiecare exercițiu, vom afișa o interogare SQL care utilizează sintaxa Oracle. Pentru date, vom folosi formatul ISO 8601, YYYY-MM-DD .

Exercițiul 1: Toate rezervările unui client

Prima sarcină - nu o faceți încă - este să găsiți toate rândurile din foaia de calcul Rezervare pentru un anumit client al unui sistem de rezervare la hotel și să le copiați în propria foaie de calcul, simulând execuția următoarei interogări:

 SELECT * FROM Reservations WHERE ClientID = 12;

Dar vrem să urmăm o anumită metodă.

Abordarea 1: fără sortare, fără filtrare

Pentru prima încercare, nu utilizați nicio funcție de sortare sau filtrare. Vă rugăm să înregistrați timpul petrecut. Foaia rezultată ar trebui să conțină 73 de rânduri.

Acest pseudocod ilustrează algoritmul pentru îndeplinirea sarcinii fără sortare:

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

În acest caz, a trebuit să verificăm toate cele 841 de rânduri pentru a reveni și a copia 73 de rânduri care îndeplinesc condiția.

Abordarea 2: Numai sortarea

Pentru a doua încercare, sortați foaia în funcție de valoarea coloanei ClientID . Nu folosiți filtre. Înregistrați timpul și comparați-l cu timpul necesar pentru a finaliza sarcina fără a sorta datele.

După sortare, abordarea arată astfel:

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

De data aceasta, a trebuit să verificăm „doar” 780 de rânduri. Dacă am putea sări cumva la primul rând, ar dura și mai puțin timp.

Dar dacă ar trebui să dezvoltăm un program pentru sarcină, această soluție ar fi chiar mai lentă decât prima. Asta pentru că ar trebui să sortăm mai întâi toate datele, ceea ce înseamnă că fiecare rând ar trebui accesat cel puțin o dată. Această abordare este bună numai dacă foaia este deja sortată în ordinea dorită.

Exercițiul 2: Numărul de rezervări care încep la o dată dată

Acum sarcina este să numărăm numărul de check-inuri pe 16 august 2020:

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

Utilizați foaia de calcul de la Exercițiul 1. Măsurați și comparați timpul petrecut pentru îndeplinirea sarcinii cu și fără sortare. Numărul corect este 91.

Pentru abordarea fără sortare, algoritmul este practic același cu cel din exercițiul 1.

Abordarea sortării este, de asemenea, similară cu cea din exercițiul anterior. Vom împărți bucla în două părți:

 -- 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

Exercițiul 3: Urmărire penală

Inspectorul de poliție solicită să vadă o listă cu oaspeții care au sosit la hotel în zilele de 13 și 14 august 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;

Abordarea 1: Sortate numai după dată

Inspectorul vrea lista rapid. Știm deja că ar fi bine să sortăm tabelul/foaia de calcul în funcție de data sosirii. Dacă tocmai am terminat Exercițiul 2, suntem norocoși că tabelul este deja sortat. Așadar, aplicăm abordarea similară cu cea din exercițiul 2.

Vă rugăm, încercați să înregistrați ora, numărul de rânduri pe care a trebuit să le citiți și numărul de articole din listă.

 -- 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

Folosind această abordare, a trebuit să citim 511 rânduri pentru a compila o listă de 46 de invitați. Dacă am reuși să alunecăm cu precizie în jos, nu ar fi trebuit să efectuăm 324 de citiri din ciclul de repetare doar pentru a localiza prima sosire pe 13 august. Totuși, a trebuit să citim mai mult de 100 de rânduri pentru a verifica dacă oaspetele a sosit la hotel cu un HotelID de 3 .

Inspectorul a așteptat tot acest timp, dar nu a fost mulțumit: în loc de numele oaspeților și alte date relevante, am livrat doar o listă de acte de identitate fără sens.

Vom reveni la acest aspect mai târziu în serie. Să găsim mai întâi o modalitate de a pregăti lista mai rapid.

Abordarea 2: Sortat după hotel, apoi dată

Pentru a sorta rândurile după HotelID apoi DateFrom , putem selecta toate coloanele, apoi folosim opțiunea din meniul Foi de calcul Google Data → Sort range .

 -- 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

A trebuit să sărim peste primele 338 de sosiri înainte de a o localiza pe prima la hotel. După aceea, am trecut peste 103 sosiri anterioare pentru a-l localiza pe primul pe 13 august. În cele din urmă, am copiat 46 de valori consecutive ale ClientID . Ne-a ajutat că în al treilea pas, am putut copia un bloc de ID-uri consecutive. Păcat că nu am putut sări cumva la primul rând din acel bloc.

Abordarea 3: Sortat numai după hotel

Acum încercați același exercițiu folosind foaia de calcul comandată numai de HotelID .

Algoritmul aplicat tabelului ordonat numai după HotelID este mai puțin eficient decât atunci când sortăm după HotelID și DateFrom (în această 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

În acest caz, trebuie să citim toate cele 166 de sosiri la hotel cu un HotelID de 3 și pentru fiecare, să verificăm dacă DateFrom aparține intervalului solicitat.

Abordarea 4: Sortat după dată, apoi hotel

Chiar contează dacă sortăm mai întâi după HotelID și apoi DateFrom sau invers? Să aflăm: încercați să sortați mai întâi după DateFrom , apoi după 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)

Am localizat primul rând cu data relevantă, apoi am citit mai multe până am localizat prima sosire la hotel. După aceea, pentru un număr de rânduri, au fost îndeplinite ambele condiții, data corectă și hotelul potrivit. Totuși, după sosiri în Hotel 3, am avut sosiri la hotelurile 4, 5 și așa mai departe, pentru aceeași dată. După ele, a trebuit să citim din nou rânduri pentru ziua următoare pentru hotelurile 1 și 2, până am putut citi sosiri consecutive la hotelul nostru de interes.

După cum putem vedea, toate abordările au un singur bloc consecutiv de date în mijlocul setului complet de rânduri, reprezentând date parțial potrivite. Abordările 2 și 4 sunt singurele în care logica ne permite să oprim complet algoritmul înainte de a ajunge la sfârșitul potrivirilor parțiale.

Abordarea 4 are date complet potrivite în două blocuri, dar Abordarea 2 este singura în care datele vizate sunt toate într-un singur bloc consecutiv.

După cifre, este clar că Abordarea 2 are cele mai multe avantaje în acest caz.

Indecși SQL explicați: concluzii și ce urmează

Efectuarea acestor exerciții ar trebui să clarifice următoarele puncte:

1. Citirea dintr-un tabel bine sortat este mai rapidă.
2. Dacă un tabel nu este deja sortat, sortarea durează mai mult decât citirea dintr-un tabel nesortat.
3. Găsirea unei modalități de a sări la primul rând care corespunde unei condiții de căutare din tabelul sortat ar economisi o mulțime de citiri.
4. Ar fi util să aveți un tabel sortat în avans.
5. Menținerea copiilor sortate ale tabelului pentru cele mai frecvente interogări ar fi utilă.

Acum, o copie sortată a unui tabel sună aproape ca un index al bazei de date. Următorul articol din SQL Indexes Explained acoperă o implementare rudimentară de index. Multumesc pentru lectura!