Korralikult kasutatuna võib SQL-i andmebaasi register olla nii tõhus, et see võib tunduda maagiana. Järgmine harjutuste seeria näitab, et selle all on enamiku SQL-indeksite loogika - ja neid õigesti vehkima - on üsna sirgjooneline.

Selles seerias SQL-i indeksid on selgitatud , käsitleme motivatsioone indeksite kasutamiseks andmetele juurdepääsu saamiseks ja indeksite kujundamiseks nii, nagu seda teevad kõik kaasaegsed RDBMS-id. Seejärel vaatame algoritme, mida kasutatakse konkreetsete päringumustrite andmete tagastamiseks.

Jälgimiseks ei pea indeksite kohta palju teadma SQL-i indeksid on selgitatud . Eeltingimusi on ainult kaks:

• SQL-i põhiteadmised
• Põhiteadmised mis tahes programmeerimiskeelest

Peamised teemad SQL-i indeksid on selgitatud satuvad on:

• Miks me vajame SQL-i andmebaaside indekseid; teostusplaanide visualiseerimine indeksite abil
• Indeksi kujundus: millised indeksid muudavad päringu kiireks ja tõhusaks
• Kuidas saame indeksite efektiivseks kasutamiseks päringu kirjutada
• Indeksite kasutamise mõju SQLis lugemise / kirjutamise efektiivsusele
• Indeksite katmine
• Jaotus, selle mõju lugemisele ja kirjutamisele ning millal seda kasutada

See ei ole lihtsalt SQL-i juhendi õpetus - see on sügav sukeldumine indeksite alusmehaanika mõistmisse.

Me selgitame välja, kuidas RDBMS kasutab indekse, tehes harjutusi ja analüüsides meie probleemide lahendamise meetodeid. Meie harjutusmaterjal koosneb kirjutuskaitstud Google'i arvutustabelitest. Harjutuse tegemiseks võite kopeerida Google Sheet ( Fail → Tee koopia ) või kopeerige selle sisu oma Google'i lehele.

Igas harjutuses näitame SQL päring, mis kasutab Oracle'i süntaksit. Kuupäevade puhul kasutame ISO 8601 vormingut YYYY-MM-DD

1. harjutus: kõik kliendi broneeringud

Esimene ülesanne - ärge seda veel tehke - on leida kõik read Broneeringu tabel konkreetse hotelli broneerimissüsteemi kliendi jaoks ja kopeerige need oma arvutustabelisse, simuleerides järgmise päringu täitmist:

SELECT * FROM Reservations WHERE ClientID = 12;

Kuid me tahame järgida kindlat meetodit.

1. lähenemisviis: sorteerimine keelatud, filtreerimine keelatud

Esimesel katsel ärge kasutage sorteerimise ega filtreerimise funktsioone. Palun märkige kulutatud aeg üles. Tulemuseks olev leht peaks sisaldama 73 rida.

See pseudokood illustreerib algoritmi ülesande täitmiseks ilma sortimata:

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

Sel juhul pidime kontrollima kõiki 841 rida, et naasta ja kopeerida 73 tingimust, mis vastavad tingimustele.

2. lähenemine: ainult sortimine

Teiseks prooviks sorteerige leht vastavalt ClientID väärtusele veerg. Ärge kasutage filtreid. Salvestage aeg ja võrrelge seda ajaga, mis kulus ülesande täitmiseks ilma andmeid sortimata.

Pärast sorteerimist näeb lähenemine välja selline:

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

Seekord pidime kontrollima “ainult” 780 rida. Kui saaksime kuidagi esimesse ritta hüpata, võtaks see veelgi vähem aega.

Kuid kui peaksime selle ülesande jaoks välja töötama programmi, oleks see lahendus esimesest veelgi aeglasem. Seda seetõttu, et me peaksime kõigepealt kõik andmed sortima, mis tähendab, et igale reale tuleks juurde pääseda vähemalt üks kord. Selline lähenemine on hea ainult siis, kui leht on juba soovitud järjekorras sorteeritud.

2. harjutus: ettetellitud kuupäevast algavate broneeringute arv

Nüüd on ülesandeks lugeda registreerimiste arv 16. augustil 2020:

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

Kasutage 1. harjutuse arvutustabelit. Mõõtke ülesande täitmiseks kulutatud aega ja võrrelge seda nii sorteerimisega kui ka ilma. Õige arv on 91.

Sorteerimata lähenemise korral on algoritm põhimõtteliselt sama, mis 1. harjutusest.

Sorteerimisviis on samuti sarnane eelmise harjutuse omaga. Jagame ahela lihtsalt kaheks osaks:

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

3. harjutus: kriminaaluurimine

Politseiinspektor soovib näha 13. ja 14. augustil 2020 hotelli saabunud külaliste nimekirja.

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;

1. lähenemine: sorteeritud ainult kuupäeva järgi

Inspektor soovib nimekirja kiiresti. Teame juba, et parem sorteerime tabeli / arvutustabeli vastavalt saabumiskuupäevale. Kui me just harjutuse 2 lõpetasime, on meil õnne, et laud on juba sorteeritud. Niisiis, rakendame lähenemist, mis sarnaneb harjutuse 2 lähenemisviisiga.

Palun proovige üles märkida aeg, ridade arv, mida pidite lugema, ja loendis olevate üksuste arv.

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

Seda lähenemist kasutades pidime 46 külalise nimekirja koostamiseks lugema 511 rida. Kui suutsime täpselt alla libiseda, ei pidanud me esimest korda 13. augusti asukoha tuvastamiseks tegema kordustsüklist 324 lugemist. Pidime siiski lugema üle 100 rea, et kontrollida, kas külaline saabus hotelli HotelID -ga / _ + _ |

Inspektor ootas kogu selle aja, kuid polnud rahul: külaliste nimede ja muude asjakohaste andmete asemel esitasime ainult mõttetute isikutunnistuste loendi.

Selle aspekti juurde naaseme hiljem sarjas. Leiame kõigepealt viisi, kuidas loendit kiiremini ette valmistada.

2. lähenemine: sorteeritud hotelli järgi, siis kuupäev

Ridade sortimiseks 3 järgi siis HotelID saame valida kõik veerud ja seejärel kasutada Google'i arvutustabelite menüüvalikut Andmed → Sordi vahemik .

DateFrom

Enne esimese leidmist meie hotelli pidime vahele jätma esimesed 338 saabumist. Pärast seda käisime üle 103 varasema saabuja, et leida esimene 13. augustil. Lõpuks kopeerisime 46 järjestikust väärtust -- 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 See aitas meil, et kolmandas etapis suutsime järjestikuste ID-de ploki kopeerida. Kahju, et me ei suutnud sellest plokist kuidagi esimesse ritta hüpata.

3. lähenemine: sorteeritud ainult hotelli järgi

Proovige nüüd sama harjutust, kasutades tabelit ClientID ainult.

Tabelile rakendatud algoritm on järjestatud HotelID ainult on vähem efektiivne kui sortimisel HotelID järgi ja HotelID (selles järjekorras):

DateFrom

Sel juhul peame kõik 166 hotelli saabumist läbi lugema -- 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 / _ + _ | ja igaühe jaoks kontrollida, kas HotelID kuulub soovitud intervalli.

4. lähenemine: sorteeritud kuupäeva järgi, siis hotell

Kas on tõesti oluline, kas sorteerime kõigepealt 3 järgi ja siis DateFrom või vastupidi? Uurime: proovige sortida kõigepealt HotelID, siis DateFrom järgi.

DateFrom

Esimese rea koos vastava kuupäevaga leidsime, seejärel lugesime rohkem, kuni leidsime esimese hotelli saabumise. Pärast seda olid mitmete ridade puhul mõlemad tingimused täidetud, õige kuupäev ja õige hotell. Kuid pärast saabumist hotelli 3 oli meil saabumine samaks kuupäevaks hotellidesse 4, 5 ja nii edasi. Pärast neid pidime uuesti lugema järgmise päeva ridu hotellide 1 ja 2 kohta, kuni suutsime järjest lugeda saabuvaid huvipakkuvaid hotelle.

Nagu näeme, on kõigi lähenemisviiside kogu rea ridade keskel üks järjestikune andmeplokk, mis esindab osaliselt vastendatud andmeid. Lähenused 2 ja 4 on ainsad, kus loogika võimaldab meil algoritmi täielikult peatada enne osaliste vastete lõppu jõudmist.

Lähenemisviis 4 on täielikult vastendanud andmed kahes plokis, kuid lähenemine 2 on ainus, kus sihitud andmed on kõik ühes järjestikuses plokis.

	Lähenemine 1	Lähenemine 2	Lähenemine 3	Lähenemine 4
Esialgsed vahelejäetavad read	324	338 + 103 = 441	342	324
Kandidaadi read uurimiseks	188	46	166	159
Vahelejäetavad read pärast algoritmi peatumist	328	353	332	357
Vahelejäetavaid ridu kokku	652	794	674	681

Numbrite järgi on selge, et lähenemisel 2 on antud juhul kõige rohkem eeliseid.

SQL-i indeksid selgitatud: järeldused ja mis edasi

Nende harjutuste tegemine peaks selgitama järgmisi punkte:

1. Korralikult sorteeritud tabelist lugemine on kiirem.
2. Kui tabelit pole veel sorteeritud, sorteerimine võtab rohkem aega kui sortimata tabelist lugemine.
3. Sorteeritud tabeli otsingu tingimusele vastava esimese rea juurde liikumise leidmine säästaks palju lugemisi.
4. Abiks oleks, kui laud oleks eelnevalt sorteeritud.
5. Abiks oleks tabeli sorteeritud koopiate säilitamine kõige sagedasemate päringute jaoks.

Nüüd kõlab tabeli sorteeritud koopia peaaegu nagu andmebaasi register. Järgmine artikkel SQL-i indeksid on selgitatud kaaned algeline indeksi rakendamine . Täname lugemast!

Põhitõdesid mõistes

Mis on SQL-i indeksid?

Andmebaasi register on oluline lisaandmete struktuur, mis aitab kiirendada andmete otsimist. SQL-päringu käivitamiseks juurdepääsetavate andmete hulk on peamine tegur, mis aitab kaasa täitmisajale. Hästi kavandatud indeksite kasutamine minimeerib juurdepääsetavate andmete mahu.

Kuidas toimivad indeksid SQL-is?

Peamine kasutusjuht on päring, mis tagastab andmeid tüübi 'veeru väärtus X ja Y vahel' tingimuse alusel. Veerus olev indeks võimaldab RDBMS-il leida kiiresti esimene tingimusele vastav rida, lugeda etteantud vahemikust järjestikuseid ridu ja peatada ilma, et oleks vaja muid andmeid lugeda.

Mis on SQL-i indeksite tüübid?

Indekseid saab tüüpidesse kategoriseerida mitmel viisil: selle struktuur (B-puu, räsitabel, binaarne, veerupood, täistekst jne), olenemata sellest, kas see on rühmitatud või mitte, ja kas see on jaotatud (lokaalselt, globaalselt või üldse mitte). Mõni salvestab terveid ridu, mõni tuletisväärtusi, teine ​​sirgeid veergude koopiaid.

Kuidas andmebaasi register töötab?

Tüüpiline indeks rakendatakse tasakaalustatud puu struktuuri abil. Indeksi lehetasemed sorteeritakse veeru väärtuste põhjal. Niisiis, kui tahame leida kõik indekseeritud veeru konkreetse väärtusega read, suudame esimese kiiresti leida ja järjestikuseid ridu lugeda, kui need vastavad väärtusele.

Kas indekseerimine parandab päringu toimivust?

Sobiv indeks võib oluliselt vähendada SELECT-käsku juurde pääsevate andmete hulka, mis on peamine tegur, mis aitab kaasa päringu täitmisele.

Mis on andmebaasi indeksi vajadus?

Kaasaegsed andmebaasid hoiavad ja avaldavad sageli tohutul hulgal andmeid. Kui kasutaja proovib hankida vaid väikese tüki andmeid ilma vastava registrita, võib (nõela heinakuhjas) otsimine võtta tunde.