Indeks baze podatkov SQL je lahko ob pravilni uporabi tako učinkovit, da se morda zdi čaroben. Naslednja serija vaj pa bo pokazala, da je spodaj logika večine indeksov SQL - in ki jih pravilno uporablja - je povsem enostavno.

V tej seriji Razloženi indeksi SQL , sprehodili se bomo po motivaciji za uporabo indeksov za dostop do podatkov in za oblikovanje indeksov tako, kot to počnejo vsi sodobni RDBMS. Nato si bomo ogledali algoritme, ki se uporabljajo za vrnitev podatkov za določene vzorce poizvedb.

Ni vam treba vedeti veliko o indeksih, da jih lahko sledite Razloženi indeksi SQL . Predpogoja sta le dva:

• Osnovno znanje SQL
• Osnovno znanje katerega koli programskega jezika

Glavne teme Razloženi indeksi SQL bodo vstopili v:

• Zakaj potrebujemo indekse baz podatkov SQL; vizualizacija izvedbenih načrtov z uporabo indeksov
• Oblikovanje indeksov: kateri indeksi naredijo poizvedbo hitro in učinkovito
• Kako lahko napišemo poizvedbo za učinkovito uporabo indeksov
• Vpliv uporabe indeksov v SQL na učinkovitost branja / pisanja
• Kazala za kritje
• Pregrada, njen vpliv na branje in pisanje ter kdaj jo uporabiti

To ni samo vadnica za indekse SQL - to je globoko poglobitev v razumevanje osnovne mehanike indeksov.

Ugotovili bomo, kako RDBMS uporablja indekse, tako da izvaja vaje in analizira naše metode reševanja problemov. Naše gradivo je sestavljeno iz Google Preglednic, ki so samo za branje. Za vajo lahko kopirate Google Sheet ( Datoteka → Naredi kopijo ) ali kopirajte njegovo vsebino v svoj Google Sheet.

V vsaki vaji bomo pokazali SQL poizvedba, ki uporablja sintakso Oracle. Za datume bomo uporabili format ISO 8601, YYYY-MM-DD.

Vaja 1: Vse naročnikove rezervacije

Prva naloga - še ne storite tega - je najti vse vrstice iz preglednico rezervacije za določeno stranko sistema hotelskih rezervacij in jih kopirajte v svojo preglednico s simulacijo izvajanja naslednje poizvedbe:

SELECT * FROM Reservations WHERE ClientID = 12;

Vendar želimo slediti določeni metodi.

Pristop 1: Brez razvrščanja, brez filtriranja

Za prvi poskus ne uporabljajte funkcij za razvrščanje ali filtriranje. Zabeležite porabljeni čas. Nastali list mora vsebovati 73 vrstic.

Ta psevdokoda prikazuje algoritem za izvedbo naloge brez razvrščanja:

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

V tem primeru smo morali preveriti vseh 841 vrstic, da smo se vrnili in kopirali 73 vrstic, ki izpolnjujejo pogoj.

Pristop 2: Samo razvrščanje

Za drugi poskus razvrstite list glede na vrednost ClientID stolpec. Ne uporabljajte filtrov. Zapišite čas in ga primerjajte s časom, potrebnim za dokončanje naloge brez razvrščanja podatkov.

Po razvrščanju je pristop videti tako:

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

Tokrat smo morali preveriti 'le' 780 vrstic. Če bi nekako lahko skočili v prvo vrsto, bi trajalo še manj časa.

Če pa bi morali razviti program za to nalogo, bi bila ta rešitev še počasnejša od prve. To je zato, ker bi morali najprej razvrstiti vse podatke, kar pomeni, da bi morali do vsake vrstice dostopati vsaj enkrat. Ta pristop je dober le, če je list že razvrščen v želenem vrstnem redu.

Vaja 2: Število rezervacij, ki se začnejo na določen datum

Zdaj je naloga šteti število prijav 16. avgusta 2020:

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

Uporabite preglednico iz vaje 1. Izmerite in primerjajte čas, ki ste ga opravili z razvrščanjem in brez njega. Pravilno štetje je 91.

Za pristop brez razvrščanja je algoritem v bistvu enak algoritmu iz vaje 1.

Tudi pristop razvrščanja je podoben pristopu iz prejšnje vaje. Zanko bomo razdelili na dva dela:

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

Vaja 3: Kriminalistična preiskava

Policijski inšpektor zahteva vpogled v seznam gostov, ki so v hotel prispeli 13. in 14. avgusta 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;

Pristop 1: Razvrščeno samo po datumu

Inšpektor želi seznam hitro. Že vemo, da bi bilo bolje razvrstiti tabelo / preglednico glede na datum prihoda. Če smo ravno zaključili vajo 2, imamo srečo, da je tabela že razvrščena. Torej uporabljamo pristop, podoben tistemu iz vaje 2.

Poskusite zabeležiti čas, število vrstic, ki ste jih morali prebrati, in število elementov na seznamu.

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

S tem pristopom smo morali prebrati 511 vrstic, da smo sestavili seznam 46 gostov. Če bi lahko natančno zdrsnili navzdol, nam dejansko ni bilo treba izvesti 324 branja iz ponovnega cikla, da bi našli prvi prihod 13. avgusta. Kljub temu smo morali prebrati več kot 100 vrstic, da preverimo, ali je gost prispel v hotel z HotelID od 3.

Inšpektor je ves ta čas čakal, vendar ne bi bil vesel: Namesto imen gostov in drugih ustreznih podatkov smo priskrbeli le seznam nesmiselnih osebnih dokumentov.

Na ta vidik se bomo vrnili kasneje v nadaljevanju. Najdimo najprej način za hitrejšo pripravo seznama.

Pristop 2: Razvrščeno po hotelu, nato datumu

Če želite vrstice razvrstiti po HotelID nato DateFrom, lahko izberemo vse stolpce in nato uporabimo možnost menija Google Preglednice Podatki → Razvrsti obseg .

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

Morali smo preskočiti prvih 338 prihodov, preden smo prvega poiskali v našem hotelu. Po tem smo pregledali 103 prejšnje prihode, da smo 13. avgusta poiskali prvega. Na koncu smo kopirali 46 zaporednih vrednosti ClientID. Pomagalo nam je, da smo v tretjem koraku lahko kopirali zaporedni ID. Škoda, da nikakor nismo mogli skočiti v prvo vrstico iz tega bloka.

Pristop 3: Razvrščeno samo po hotelu

Zdaj poskusite isto vajo s preglednico, ki jo je naročila HotelID samo.

Algoritem, uporabljen za tabelo, razvrščeno po HotelID je manj učinkovit kot pri razvrščanju po HotelID in DateFrom (v tem vrstnem redu):

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

V tem primeru moramo prebrati vseh 166 prihodov v hotel z HotelID od 3 in za vsako preverite, če DateFrom pripada zahtevanemu intervalu.

Pristop 4: Razvrščeno po datumu, nato po hotelu

Ali je res pomembno, ali najprej razvrstimo po HotelID in nato DateFrom ali obratno? Ugotovimo: Najprej poskusite razvrstiti po DateFrom, nato po 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-14' or (DateFrom = '2020-08-14' and HotelID> 3)

Našli smo prvo vrstico z ustreznim datumom, nato prebrali več, dokler nismo našli prvega prihoda v hotel. Po tem sta bila za vrsto vrstic izpolnjena oba pogoja, pravilen datum in pravi hotel. Po prihodu v hotel 3 pa smo imeli prihode v hotele 4, 5 itd. Za isti datum. Po njih smo morali še enkrat prebrati vrstice za naslednji dan za hotela 1 in 2, dokler nismo mogli prebrati zaporednih prihodov v hotel, ki nas zanima.

Kot lahko vidimo, imajo vsi pristopi en zaporedni blok podatkov sredi celotnega nabora vrstic, ki predstavlja delno usklajene podatke. Edini pristop 2 in 4 sta logika, ki omogoča, da algoritem popolnoma ustavimo, preden pridemo do konca delnih ujemanj.

Pristop 4 ima popolnoma usklajene podatke v dveh blokih, vendar je pristop 2 edini, kjer so ciljni podatki vsi v enem zaporednem bloku.

	Pristop 1	Pristop 2	Pristop 3	Pristop 4
Začetne vrstice, ki jih je mogoče preskočiti	324	338 + 103 = 441	342	324
Vrstice kandidatov za pregled	188	46	166	159
Vrstice, ki jih je mogoče preskočiti, ko se algoritem ustavi	328	353	332	357
Skupno preskočljivih vrstic	652	794	674	681

Iz številk je jasno, da ima pristop 2 v tem primeru največ prednosti.

Pojasnjeni indeksi SQL: Zaključki in kaj sledi

Pri teh vajah bi morale postati jasne naslednje točke:

1. Branje iz pravilno razvrščene tabele je hitrejše.
2. Če tabela še ni razvrščena, razvrščanje traja več časa kot branje iz nesortirane tabele.
3. Če najdemo način za skok na prvo vrstico, ki ustreza pogoju iskanja v razvrščeni tabeli, bi prihranili veliko branja.
4. Koristno bi bilo, če bi mizo vnaprej razvrstili.
5. V pomoč bi bilo vzdrževanje razvrščenih kopij tabele za najpogostejša vprašanja.

Zdaj razvrščena kopija tabele zveni skoraj kot indeks baze podatkov. Naslednji članek v Razloženi indeksi SQL prevleke osnovno izvajanje indeksa . Hvala za branje!

Razumevanje osnov

Kaj so indeksi v SQL?

Kazalo baze podatkov je pomembna pomožna podatkovna struktura, ki pomaga pospešiti iskanje podatkov. Količina podatkov, dostopna za izvedbo poizvedbe SQL, je glavni dejavnik, ki prispeva k času izvajanja. Uporaba dobro oblikovanih indeksov zmanjša količino dostopnih podatkov.

Kako delujejo indeksi v SQL?

Glavni primer uporabe je poizvedba, ki vrne podatke na podlagi stanja tipa 'vrednost stolpca med X in Y'. Indeks v stolpcu omogoča RDBMS, da hitro najde prvo vrstico, ki izpolnjuje pogoj, prebere zaporedne vrstice iz danega obsega in se ustavi, ne da bi bilo treba prebrati druge podatke.

Kakšne so vrste indeksov v SQL?

Indekse lahko razvrstimo v vrste na več načinov: njegovo strukturo (B-drevo, razpršena tabela, binarno, shramba stolpcev, celotno besedilo itd.), Ne glede na to, ali so razvrščeni v skupine ali ne, in ali so razdeljeni (lokalno, globalno ali sploh ne). Nekateri shranjujejo cele vrstice, nekateri shranjujejo izpeljane vrednosti, drugi pa naravne kopije stolpcev.

Kako deluje indeks baze podatkov?

Tipični indeks se izvaja z uporabo uravnotežene drevesne strukture. Ravni listov indeksa so razvrščeni glede na vrednosti stolpcev. Ko želimo torej najti vse vrstice z določeno vrednostjo indeksiranega stolpca, lahko hitro poiščemo prvo in preberemo zaporedne vrstice, dokler se ujemajo z vrednostjo.

Ali indeksiranje izboljša uspešnost poizvedbe?

Ustrezen indeks lahko znatno zmanjša količino podatkov, do katerih dostopa izjava SELECT, kar je glavni dejavnik, ki prispeva k času izvajanja poizvedbe.

Kaj potrebuje indeks v zbirki podatkov?

Sodobne zbirke podatkov pogosto hranijo in objavljajo velike količine podatkov. Ko uporabnik poskuša pridobiti le majhen delček podatkov brez ustreznega indeksa, lahko iskanje (igle v kozolcu) traja ure.