Kyselyjen suoritus

SQL-kieli on tietokannan käyttäjälle mukava kyselyjen tekemisessä, koska käyttäjän riittää kuvata, mitä tietoa hän haluaa hakea, ja tietokantajärjestelmä hoitaa loput. Niinpä tietokantajärjestelmän on tärkeää pystyä löytämään jokin tehokas tapa toteuttaa käyttäjän antama kysely ja toimittaa kyselyn tulokset käyttäjälle.

Kyselyn suunnitelma

Monet tietokantajärjestelmät antavat mahdollisuuden pyytää järjestelmää kertomaan suunnitelmansa, miten se aikoo suorittaa annetun kyselyn. Tämän avulla voimme tutkia tietokantajärjestelmän sisäistä toimintaa.

Tarkastellaan esimerkkinä kyselyä, joka hakee retiisin tiedot taulusta Tuotteet:

SELECT * FROM Tuotteet WHERE nimi='retiisi';

Kun laitamme SQLitessä kyselyn eteen sanan EXPLAIN, saamme seuraavan tapaisen selostuksen siitä, miten kysely aiotaan suorittaa:

sqlite> EXPLAIN SELECT * FROM Tuotteet WHERE nimi='retiisi';
addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     12    0                    00  Start at 12  
1     OpenRead       0     2     0     3              00  root=2 iDb=0; Tuotteet
2     Rewind         0     10    0                    00               
3       Column         0     1     1                    00  r[1]=Tuotteet.nimi
4       Ne             2     9     1     (BINARY)       52  if r[2]!=r[1] goto 9
5       Rowid          0     3     0                    00  r[3]=rowid   
6       Copy           1     4     0                    00  r[4]=r[1]    
7       Column         0     2     5                    00  r[5]=Tuotteet.hinta
8       ResultRow      3     3     0                    00  output=r[3..5]
9     Next           0     3     0                    01               
10    Close          0     0     0                    00               
11    Halt           0     0     0                    00               
12    Transaction    0     0     1     0              01  usesStmtJournal=0
13    TableLock      0     2     0     Tuotteet       00  iDb=0 root=2 write=0
14    String8        0     2     0     retiisi        00  r[2]='retiisi'
15    Goto           0     1     0                    00 

SQLite muuttaa kyselyn tietokannan sisäiseksi ohjelmaksi, joka hakee tietoa tauluista. Tässä tapauksessa ohjelman suoritus alkaa riviltä 12, jossa alkaa transaktio, ja sitten rivillä 14 rekisteriin 2 sijoitetaan hakuehdossa oleva merkkijono "retiisi". Tämän jälkeen suoritus siirtyy riville 1, jossa aloitetaan taulun Tuotteet käsittely, ja rivit 2–9 muodostavat silmukan, joka etsii hakuehtoa vastaavat rivit taulusta.

Voimme myös pyytää tiiviimmän suunnitelman laittamalla kyselyn eteen sanat EXPLAIN QUERY PLAN. Tällöin tulos voi olla seuraava:

sqlite> EXPLAIN QUERY PLAN SELECT * FROM Tuotteet WHERE nimi='retiisi';
0|0|0|SCAN TABLE Tuotteet

Tässä SCAN TABLE Tuotteet tarkoittaa, että kysely käy läpi taulun Tuotteet rivit.

Kyselyn optimointi

Jos kyselyssä haetaan tietoa vain yhdestä taulusta, kysely on yleensä helppo suorittaa, mutta todelliset haasteet tulevat vastaan usean taulun kyselyissä. Tällöin tietokantajärjestelmän tulee osata optimoida kyselyn suorittamista eli muodostaa hyvä suunnitelma, jonka avulla halutut tiedot saadaan kerättyä tehokkaasti tauluista.

Tarkastellaan esimerkkinä seuraavaa kyselyä, joka listaa kurssien ja opettajien nimet:

SELECT K.nimi, O.nimi FROM Kurssit K, Opettajat O WHERE K.opettaja_id = O.id;

Koska kysely kohdistuu kahteen tauluun, olemme ajatelleet kyselyn toiminnan niin, että se muodostaa ensin kaikki rivien yhdistelmät tauluista Kurssit ja Opettajat ja valitsee sitten ne rivit, joilla pätee ehto K.opettaja_id = O.id. Tämä on hyvä ajattelutapa, mutta tämä ei vastaa sitä, miten kunnollinen tietokantajärjestelmä toimii.

Ongelmana on, että tauluissa Kurssit ja Opettajat voi molemmissa olla suuri määrä rivejä. Esimerkiksi jos kummassakin taulussa on miljoona riviä, rivien yhdistelmiä olisi miljoona miljoonaa ja veisi valtavasti aikaa käydä läpi kaikki yhdistelmät.

Tässä tilanteessa tietokantajärjestelmän pitääkin ymmärtää, mitä käyttäjä oikeastaan on hakemassa ja miten kyselyssä annettu ehto rajoittaa tulosrivejä. Käytännössä riittää käydä läpi kaikki taulun Kurssit rivit ja etsiä jokaisen rivin kohdalla jotenkin tehokkaasti yksittäinen haluttu rivi taulusta Opettajat.

Voimme taas pyytää SQLiteä selittämään kyselyn suunnitelman:

sqlite> EXPLAIN QUERY PLAN SELECT K.nimi, O.nimi FROM Kurssit K, Opettajat O WHERE K.opettaja_id = O.id;
0|0|0|SCAN TABLE Kurssit AS K
0|1|1|SEARCH TABLE Opettajat AS O USING INTEGER PRIMARY KEY (rowid=?)

Tämä kysely käy läpi taulun Kurssit rivit (SCAN TABLE Kurssit) ja hakee tietoa taulusta Opettajat pääavaimen avulla (SEARCH TABLE Opettajat). Jälkimmäinen tarkoittaa, että kun käsittelyssä on tietty taulun Kurssit rivi, kysely hakee tehokkaasti taulusta Opettajat rivin, jossa pääavain O.id on sama kuin K.opettaja_id.

Mutta miten käytännössä taulusta Opettajat voi hakea tehokkaasti? Tämä onnistuu käyttämällä indeksiä, joihin tutustumme heti seuraavassa aliluvussa.