7. Tietokannan ominaisuudet
Tiedon eheys
Tiedon eheys tarkoittaa, että tietokannassa oleva tieto on paikkansa pitävää ja ristiriidatonta. Päävastuu tiedon laadusta on tietokantaa muuttavalla käyttäjällä tai sovelluksella, mutta myös tietokannan suunnittelija voi vaikuttaa asiaan lisäämällä tauluihin ehtoja, jotka valvovat tietokantaan syötettävää tietoa.
Sarakkeiden ehdot
Voimme lisätä taulun luonnin yhteydessä sarakkeisiin ehtoja, joita tietokantajärjestelmä valvoo tietoa lisättäessä ja muutettaessa. Näillä ehdoilla voi rajoittaa tietokantaan tulevaa tietoa. Tyypillisiä ehtoja ovat seuraavat:
UNIQUE
Ehto UNIQUE tarkoittaa, että sarakkeessa tulee olla eri arvo joka rivillä. Esimerkiksi seuraavassa taulussa vaatimuksena on, että joka tuotteella on eri nimi:
CREATE TABLE Products (
id INTEGER PRIMARY KEY,
name TEXT UNIQUE,
price INTEGER
);
Ehto UNIQUE voi kohdistua myös useampaan sarakkeeseen, jolloin se merkitään erikseen sarakkeiden jälkeen:
CREATE TABLE Products (
id INTEGER PRIMARY KEY,
name TEXT,
price INTEGER,
UNIQUE(name, price)
);
Tämä tarkoittaa, että taulussa ei voi olla kahta riviä, joilla on sama nimi ja sama hinta.
NOT NULL ja DEFAULT
Ehto NOT NULL tarkoittaa, että sarakkeessa ei saa olla arvoa NULL. Esimerkiksi seuraavassa taulussa tuotteen hinta ei saa olla tyhjä:
CREATE TABLE Products (
id INTEGER PRIMARY KEY,
name TEXT,
price INTEGER NOT NULL
);
Määre DEFAULT antaa sarakkeelle tietyn oletusarvon, jos sen arvo puuttuu rivin lisäämisessä. Esimerkiksi voimme määrittää hinnalle oletusarvon 0 näin:
CREATE TABLE Products (
id INTEGER PRIMARY KEY,
name TEXT,
price INTEGER DEFAULT 0
);
CHECK
Yleisempi tapa luoda ehto on käyttää avainsanaa CHECK, jonka jälkeen voi kirjoittaa minkä tahansa ehtolausekkeen. Esimerkiksi seuraavassa taulussa ehto price >= 0 tarkoittaa, että hinta ei saa olla negatiivinen:
CREATE TABLE Products (
id INTEGER PRIMARY KEY,
name TEXT,
price INTEGER,
CHECK (price >= 0)
);
Ehtojen valvonta
Ehtojen hyötynä on, että tietokantajärjestelmä valvoo ehtoja ja kieltäytyy tekemästä niitä rikkovia lisäyksiä tai muutoksia. Seuraavassa on esimerkki tästä SQLitessä:
sqlite> CREATE TABLE Products (id INTEGER PRIMARY KEY,
...> name TEXT,
...> price INTEGER,
...> CHECK (price >= 0));
sqlite> INSERT INTO Products(name, price) VALUES ('retiisi', 4);
sqlite> INSERT INTO Products(name, price) VALUES ('selleri', 7);
sqlite> INSERT INTO Products(name, price) VALUES ('nauris', –2);
Error: CHECK constraint failed: Products
sqlite> SELECT * FROM Products;
1|retiisi|4
2|selleri|7
sqlite> UPDATE Products SET price = –2 WHERE id = 2;
Error: CHECK constraint failed: Products
Kun koetamme lisätä tauluun Products rivin, jossa hinta on negatiivinen, tämä rikkoo ehdon price >= 0 eikä SQLite salli rivin lisäämistä vaan antaa virheen CHECK constraint failed: Products. Samalla tavalla käy, jos koetamme muuttaa olemassa olevan rivin sarakkeen hinnan negatiiviseksi jälkeenpäin.
Ehdot ohjelmoinnissa
Seuraava esimerkki näyttää, miten taulussa olevaa ehtoa voidaan hyödyntää ohjelmoinnissa. Haluamme, että tietokannan jokaisella tuotteella on eri nimi, minkä vuoksi sarakkeessa name on ehtona UNIQUE:
CREATE TABLE Products (
id INTEGER PRIMARY KEY,
name TEXT UNIQUE,
price INTEGER
);
Nyt tuotteen lisäämisen tietokantaan voi toteuttaa näin:
try:
db.execute("""
INSERT INTO Products (name, price) VALUES (?, ?)
""", [name, price])
print("Tuote lisätty tauluun")
except sqlite3.IntegrityError:
print("Lisäys ei onnistunut, tuote valmiiksi taulussa")
Tässä tapauksessa komento INSERT epäonnistuu, jos taulussa on valmiina samanniminen tuote. Python-koodissa tilanteen voi tunnistaa siitä, että lisäysyritys aiheuttaa virheen sqlite3.IntegrityError. Niinpä koodi voi yrittää lisätä tuotetta tutkimatta, onko tuote jo valmiiksi taulussa, ja tästä syntyvä mahdollinen virhe ilmaisee, että tuote on lisätty aiemmin.
Tämä on selkeästi parempi toteutus kuin tutkia koodissa ennen lisäämistä SELECT-kyselyllä, onko tuotetta jo taulussa, koska UNIQUE-ehdon avulla tietokanta pitää luotettavasti huolen asiasta ja koodiakin tarvitaan vähemmän.
Jos taulussa ei olisi UNIQUE-ehtoa ja sovellus suorittaisi komennot SELECT ja INSERT, olisi mahdollista, että toinen tietokannan käyttäjä ehtisi lisätä välissä saman tuotteen tauluun, jolloin taulussa olisikin kaksi tuotetta samalla nimellä. Kuitenkaan UNIQUE-ehdon kanssa näin ei voi tapahtua mitenkään.
Viittausten ehdot
Tauluissa oleviin viitauksiin liittyvät ehdot pitävät huolen siitä, että viittaukset viittaavat todellisiin riveihin. Tarkastellaan esimerkkinä seuraavia tauluja:
CREATE TABLE Teachers (
id INTEGER PRIMARY KEY,
name TEXT
);
CREATE TABLE Courses (
id INTEGER PRIMARY KEY,
name TEXT,
teacher_id INTEGER
);
Tässä tarkoituksena on, että taulun Courses sarake teacher_id viittaa taulun Teachers sarakkeeseen id. Kuitenkin tietokannan käyttäjä voi antaa sarakkeen teacher_id arvoksi mitä tahansa (esim. luvun 123), jolloin tietokannan sisältö muuttuu epämääräiseksi.
Voimme parantaa tilannetta kertomalla taulun Courses luonnissa REFERENCES-merkinnällä, että sarake teacher_id viittaa tauluun Teachers:
CREATE TABLE Courses (
id INTEGER PRIMARY KEY,
name TEXT,
teacher_id INTEGER REFERENCES Teachers
);
Tämän jälkeen voimme luottaa siihen, että taulussa Courses sarakkeen teacher_id arvot viittaavat todellisiin riveihin taulussa Teachers.
Huomaa, että historiallisista syistä SQLite ei oletuksena valvo viittauksia, vaan meidän tulee ensin suorittaa seuraava komento:
sqlite> PRAGMA foreign_keys = ON;
Tämä on SQLiten erikoisuus, ja muissa tietokannoissa viittauksia valvotaan aina.
Tässä on esimerkki viittauksien valvomisesta:
sqlite> PRAGMA foreign_keys = ON;
sqlite> CREATE TABLE Teachers (id INTEGER PRIMARY KEY, name TEXT);
sqlite> CREATE TABLE Courses (id INTEGER PRIMARY KEY, name TEXT,
...> teacher_id INTEGER REFERENCES Teachers);
sqlite> INSERT INTO Teachers (name) VALUES ('Kaila');
sqlite> INSERT INTO Teachers (name) VALUES ('Kivinen');
sqlite> SELECT * FROM Teachers;
1|Kaila
2|Kivinen
sqlite> INSERT INTO Courses (name, teacher_id)
...> VALUES ('Laskennan mallit', 2);
sqlite> INSERT INTO Courses (name, teacher_id)
...> VALUES ('PHP-ohjelmointi', 123);
Error: FOREIGN KEY constraint failed
Taulussa Teachers on kaksi opettajaa, joiden id-numerot ovat 1 ja 2. Niinpä kun koetamme lisätä tauluun Courses rivin, jossa teacher_id on 123, SQLite ei salli tätä vaan saamme virheilmoituksen FOREIGN KEY constraint failed.
Viittaukset ja poistot
Viittausten ehtoihin liittyy tavallisia sarakkeiden ehtoja mutkikkaampia tilanteita, koska viittaukset ovat kahden taulun välisiä. Erityisesti mitä tapahtuu, jos taulusta yritetään poistaa rivi, johon viitataan toisessa taulussa?
Yleensä oletuksena tietokannoissa riviä ei voi poistaa, jos siihen on viittaus muualta. Esimerkiksi jos koetamme äskeisen esimerkin päätteeksi poistaa taulusta Teachers rivin 2, tämä ei onnistu, koska siihen viitataan taulussa Courses:
sqlite> DELETE FROM Teachers WHERE id = 2;
Error: FOREIGN KEY constraint failed
Halutessamme voimme kuitenkin määrittää taulun luonnissa tarkemmin, mitä tällaisessa tilanteessa tapahtuu. Yksi vaihtoehto on ON DELETE CASCADE, mikä tarkoittaa, että rivin poistuessa myös siihen viittaavat rivit poistetaan:
CREATE TABLE Courses (
id INTEGER PRIMARY KEY,
name TEXT,
teacher_id INTEGER REFERENCES Teachers ON DELETE CASCADE
);
Nyt jos tietokannasta poistetaan opettaja, niin samalla poistetaan automaattisesti kaikki kurssit, joita hän opettaa. Tämä voi kuitenkin olla kyseenalainen vaihtoehto, koska tämän seurauksena tietokannan tauluista voi kadota yllättäen tietoa.
Mahdollisia vaihtoehtoja ON DELETE -osassa ovat:
NO ACTION: “älä tee mitään” (oletus)RESTRICT: estä poistaminenCASCADE: poista myös viittaavat rivitSET NULL: muuta viittaukset arvoksiNULLSET DEFAULT: muuta viittaukset oletusarvoksi
Hämmentävä seikka on, että myös oletusvaihtoehto NO ACTION estää rivin poistamisen, vaikka nimestä voisi päätellä muuta. Vaihtoehdot NO ACTION ja RESTRICT toimivat käytännössä lähes samalla tavalla, mutta tietokannasta riippuen niiden toiminnassa voi olla eroja joissain erikoistilanteissa.
Transaktiot
Transaktio on joukko peräkkäisiä SQL-komentoja, jotka tietokantajärjestelmä lupaa suorittaa yhtenä kokonaisuutena. Tietokannan käyttäjä voi luottaa siihen, että joko (1) kaikki komennot suoritetaan ja muutokset jäävät pysyvästi tietokantaan tai (2) transaktio keskeytyy eivätkä komennot aiheuta mitään muutoksia tietokantaan.
Transaktioiden yhteydessä esiintyy usein ihanteena kirjainyhdistelmä ACID, joka tulee seuraavista sanoista:
- Atomicity: Transaktiossa olevat komennot suoritetaan yhtenä kokonaisuutena.
- Consistency: Transaktio säilyttää tietokannan sisällön eheänä.
- Isolation: Transaktiot suoritetaan eristyksessä toisistaan.
- Durability: Loppuun viedyn transaktion tekemät muutokset jäävät pysyviksi.
Transaktion vaiheet
Itse asiassa transaktio on hyvin arkipäiväinen asia tietokannoissa, sillä oletuksena jokainen suoritettava SQL-komento on oma transaktionsa. Tarkastellaan esimerkiksi seuraavaa komentoa, joka kasvattaa jokaisen tuotteen hintaa yhdellä:
UPDATE Products SET price = price + 1;
Koska komento suoritetaan transaktiona, voimme luottaa siihen, että joko jokaisen tuotteen hinta todella kasvaa yhdellä tai sitten minkään tuotteen hinta ei muutu. Jälkimmäinen voi tapahtua esimerkiksi silloin, kun sähköt katkeavat kesken päivityksen. Silloinkaan ei siis voi käydä niin, että vain osa hinnoista muuttuu.
Usein kuitenkin sana transaktio viittaa erityisesti siihen, että suoritettavaan kokonaisuuteen kuuluu useampi SQL-komento. Tällöin annamme ensin komennon BEGIN, joka aloittaa transaktion, sitten kaikki transaktioon kuuluvat komennot tavalliseen tapaan ja lopuksi komennon COMMIT, joka päättää transaktion.
Klassinen esimerkki transaktiosta on tilanne, jossa pankissa siirretään rahaa tililtä toiselle. Esimerkiksi seuraava transaktio siirtää 100 euroa Maijan tililtä Uolevin tilille:
BEGIN;
UPDATE Accounts SET balance = balance - 100 WHERE customer = 'Maija';
UPDATE Accounts SET balance = balance + 100 WHERE customer = 'Uolevi';
COMMIT;
Transaktion ideana on, että mitään pysyvää muutosta ei tapahdu ennen komentoa COMMIT. Niinpä yllä olevassa esimerkissä ei ole mahdollista, että Maija menettäisi 100 euroa mutta Uolevi ei saisi mitään. Joko kummankin tilin saldo muuttuu ja rahat siirtyvät onnistuneesti tai molemmat saldot säilyvät entisellään.
Jos transaktio keskeytyy jostain syystä ennen komentoa COMMIT, kaikki transaktiossa tehdyt muutokset peruuntuvat. Yksi syy transaktion keskeytymiseen on jokin häiriö tietokoneen toiminnassa (kuten sähköjen katkeaminen), mutta voimme myös itse halutessamme keskeyttää transaktion antamalla komennon ROLLBACK.
Transaktioiden kokeilu
Hyvä tapa saada ymmärrystä transaktioista on kokeilla käytännössä, miten ne toimivat. Tässä on esimerkkinä yksi keskustelu SQLiten kanssa:
sqlite> CREATE TABLE Accounts (id INTEGER PRIMARY KEY,
...> customer TEXT, balance INTEGER);
sqlite> INSERT INTO Accounts (customer, balance) VALUES ('Uolevi', 350);
sqlite> INSERT INTO Accounts (customer, balance) VALUES ('Maija', 600);
sqlite> SELECT * FROM Accounts;
1|Uolevi|350
2|Maija|600
sqlite> BEGIN;
sqlite> UPDATE Accounts SET balance = balance - 100 WHERE customer = 'Maija';
sqlite> SELECT * FROM Accounts;
1|Uolevi|350
2|Maija|500
sqlite> ROLLBACK;
sqlite> SELECT * FROM Accounts;
1|Uolevi|350
2|Maija|600
sqlite> BEGIN;
sqlite> UPDATE Accounts SET balance = balance - 100 WHERE customer = 'Maija';
sqlite> UPDATE Accounts SET balance = balance + 100 WHERE customer = 'Uolevi';
sqlite> COMMIT;
sqlite> SELECT * FROM Accounts;
1|Uolevi|450
2|Maija|500
Alkutilanteessa Uolevin tilillä on 350 euroa ja Maijan tilillä on 600 euroa. Ensimmäisessä transaktiossa poistamme ensin Maijan tililtä 100 euroa, mutta sen jälkeen tulemme toisiin ajatuksiin ja keskeytämme transaktion. Niinpä transaktiossa tehty muutos peruuntuu ja tilien saldot ovat samat kuin alkutilanteessa. Toisessa transaktiossa viemme kuitenkin transaktion loppuun, minkä seurauksena Uolevin tilillä on 450 euroa ja Maijan tilillä on 500 euroa.
Huomaa, että transaktion sisällä muutokset kyllä näkyvät, vaikka niitä ei olisi tehty vielä pysyvästi tietokantaan. Esimerkiksi ensimmäisen transaktion SELECT-kysely antaa Maijan tilin saldoksi 500 euroa, koska edellinen UPDATE-komento muutti saldoa.
Transaktiot ohjelmoinnissa
Transaktiokomentoja (BEGIN, COMMIT, jne.) voi suorittaa ohjelmoinnissa samaan tapaan kuin muitakin SQL-komentoja. Esimerkiksi seuraava koodi lisää tauluun Products tuhat riviä for-silmukassa yhden transaktion sisällä:
db.execute("BEGIN")
for i in range(1000):
db.execute("""
INSERT INTO Products (name, price) VALUES (?, ?)
""", ["product" + str(i), 1])
db.execute("COMMIT")
Koska koodi on transaktion sisällä, koodi joko lisää kaikki rivit tietokantaan tai ei yhtään riviä, jos transaktio epäonnistuu jostain syystä.
Tässä tapauksessa transaktion käyttäminen myös nopeuttaa koodia, koska jokaista riviä ei lisätä erillisen transaktion sisällä vaan lisäys tapahtuu kokonaisuutena. Tämä auttaa tietokantaa toteuttamaan rivien lisäämisen tehokkaammin.
Rinnakkaiset transaktiot
Lisämaustetta transaktioiden käsittelyyn tuo, että tietokannalla voi olla useita käyttäjiä, joilla on meneillään samanaikaisia transaktioita. Missä määrin eri käyttäjien transaktiot tulisi eristää toisistaan?
Tämä on kysymys, johon ei ole yhtä oikeaa vastausta, vaan vastaus riippuu käyttötilanteesta ja myös tietokannan ominaisuuksista. Tavallaan paras ratkaisu olisi eristää transaktiot täydellisesti toisistaan, mutta toisaalta tämä voi haitata tietokannan käyttämistä.
SQL-standardi määrittelee transaktioiden eristystasot seuraavasti:
Taso 1 (read uncommitted)
On sallittua, että transaktio pystyy näkemään toisen transaktion tekemän muutoksen, vaikka toista transaktiota ei ole viety loppuun.
Taso 2 (read committed)
Toisin kuin tasolla 1, transaktio saa nähdä toisen transaktion tekemän muutoksen vain, jos toinen transaktio on viety loppuun.
Taso 3 (repeatable read)
Tason 2 vaatimus ja lisäksi jos transaktion aikana luetaan saman rivin sisältö useita kertoja, joka kerralla saadaan sama sisältö.
Taso 4 (serializable)
Transaktiot ovat täysin eristettyjä ja komennot käyttäytyvät samoin kuin jos transaktiot olisi suoritettu peräkkäin yksi kerrallaan jossain järjestyksessä.
Esimerkki
Tarkastellaan tilannetta, jossa tuotteen 1 hinta on aluksi 8 ja kaksi käyttäjää (K1 ja K2) suorittaa samaan aikaan komentoja transaktioiden sisällä:
K1: BEGIN;
K2: BEGIN;
K2: UPDATE Products SET price = 5 WHERE id = 1;
K1: SELECT price FROM Products WHERE id = 1;
K2: UPDATE Products SET price = 7 WHERE id = 1;
K2: COMMIT;
K1: SELECT price FROM Products WHERE id = 1;
K1: COMMIT;
Tasolla 1 käyttäjä 1 voi saada kyselyistä tulokset 5 ja 7, koska käyttäjän 2 tekemät muutokset voivat tulla näkyviin heti, vaikka käyttäjän 2 transaktioita ei ole viety loppuun.
Tasolla 2 käyttäjä 1 voi saada kyselyistä tulokset 8 ja 7, koska ensimmäisen kyselyn kohdalla toista transaktiota ei ole viety loppuun, kun taas toisen kyselyn kohdalla se on viety loppuun.
Tasoilla 3 ja 4 käyttäjä 1 saa kyselyistä tulokset 8 ja 8, koska tämä on tilanne ennen transaktion alkua eikä välissä loppuun viety transaktio saa muuttaa luettua rivin sisältöä.
Transaktiot käytännössä
Transaktioiden toteutustavat ja saatavilla olevat eristystasot riippuvat käytetystä tietokantajärjestelmästä. Esimerkiksi SQLitessä ainoa mahdollinen taso on 4, kun taas PostgreSQL toteuttaa tasot 2–4 ja oletuksena käytössä on taso 2.
Eristystaso 4 on tavallaan paras, koska silloin transaktioiden muutokset eivät voi näkyä mitenkään toisilleen. Miksi edes muut tasot ovat olemassa ja miksi esimerkiksi PostgreSQL:n oletustaso on 2?
Hyvän eristämisen hintana on, että se voi hidastaa tai estää transaktioiden suorittamista, koska transaktion vieminen loppuun voisi aiheuttaa ristiriitaisen tilanteen. Toisaalta monissa käytännön tilanteissa riittää mainiosti heikompikin eristys, kunhan tietokannan käyttäjä on siitä tietoinen.
Hyvää tietoa rinnakkaisten transaktioiden toiminnasta saa perehtymällä käytetyn tietokannan dokumentaatioon sekä testailemalla asioita itse käytännössä. Esimerkiksi voimme käynnistää itse kaksi SQLite-tulkkia, avata niillä saman tietokannan ja sen jälkeen kirjoittaa transaktioita sisältäviä komentoja ja tehdä havaintoja.
Seuraava keskustelu näyttää edellisen esimerkin tuloksen kahdessa rinnakkain käynnissä olevassa SQLite-tulkissa:
K1: BEGIN;
K2: BEGIN;
K2: UPDATE Products SET price = 5 WHERE id = 1;
K1: SELECT price FROM Products WHERE id = 1;
8
K2: UPDATE Products SET price = 7 WHERE id = 1;
K2: COMMIT;
Error: database is locked
K1: SELECT price FROM Products WHERE id=1;
8
K1: COMMIT;
Tästä näkee, että ensimmäinen transaktio tosiaan saa kummastakin kyselystä tuloksen 8. Toista transaktiota ei sen sijaan saada vietyä loppuun, vaan tulee virheviesti Error: database is locked, koska tietokanta on lukittuna samanaikaisen transaktion takia. Eristys toimii siis hyvin, mutta toista transaktiota pitäisi yrittää viedä loppuun uudestaan.
Vertailun vuoksi tässä on vastaava keskustelu PostgreSQL-tulkeissa (tasolla 2):
K1: BEGIN;
K2: BEGIN;
K2: UPDATE Products SET price = 5 WHERE id = 1;
K1: SELECT price FROM Products WHERE id = 1;
8
K2: UPDATE Products SET price = 7 WHERE id = 1;
K2: COMMIT;
K1: SELECT price FROM Products WHERE id = 1;
7
K1: COMMIT;
Nyt toisen transaktion muuttama arvo 7 ilmestyy ensimmäiseen transaktioon, mutta toisaalta molemmat transaktiot saadaan vietyä loppuun ongelmitta.
Miten transaktiot toimivat?
Transaktioiden toteuttaminen on kiehtova tekninen haaste tietokannoissa. Tavallaan transaktion tulee tehdä muutoksia tietokantaan, koska komennot voivat riippua edellisistä komennoista, mutta toisaalta mitään ei saa muuttaa pysyvästi ennen transaktion viemistä loppuun.
Yksi keskeinen ajatus tietokantojen taustalla on tallentaa muutoksia kahdella tavalla. Ensin kuvaus muutoksesta kirjataan lokitiedostoon (write-ahead log), jota voi ajatella luettelona suoritetuista komennoista. Vasta tämän jälkeen muutokset tehdään tietokannan varsinaisiin tietorakenteisiin. Nyt jos jälkimmäisessä vaiheessa sattuu jotain yllättävää, muutokset ovat jo tallessa lokitiedostossa ja ne voidaan suorittaa myöhemmin uudestaan.
Transaktioiden yhteydessä tietokantajärjestelmän täytyy myös pitää kirjaa siitä, mitkä muutokset ovat minkäkin meneillään olevan transaktion tekemiä. Käytännössä tauluihin voidaan tallentaa rivimuutoksia, jotka näkyvät vain tietyille transaktioille. Sitten jos transaktio pääsee loppuun asti, nämä muutokset liitetään taulun pysyväksi sisällöksi.
Kyselyjen suoritus
SQL-kielen taustalla oleva idea on, että tietokannan 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 kertovat pyydettäessä suunnitelmansa, miten annettu kysely aiotaan suorittaa. Tämän avulla voidaan tutkia tietokantajärjestelmän sisäistä toimintaa.
Tarkastellaan esimerkkinä kyselyä, joka hakee retiisin tiedot taulusta Products:
SELECT * FROM Products WHERE name = 'retiisi';
Kun laitamme SQLitessä kyselyn eteen sanan EXPLAIN, saamme seuraavan tapaisen selostuksen suunnitelmasta:
sqlite> EXPLAIN SELECT * FROM Products WHERE name = '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; Products
2 Rewind 0 10 0 00
3 Column 0 1 1 00 r[1]=Products.name
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]=Products.price
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 Products 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 Products 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 Products WHERE name = 'retiisi';
0|0|0|SCAN TABLE Products
Tässä SCAN TABLE Products tarkoittaa, että kysely käy läpi taulun Products 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 Courses.name, Teachers.name
FROM Courses, Teachers
WHERE Courses.teacher_id = Teachers.id;
Koska kysely kohdistuu kahteen tauluun, kyselyn voi ajatella muodostavan ensin kaikki rivien yhdistelmät tauluista Courses ja Teachers ja valitsevan sitten ne rivit, joilla pätee ehto Courses.teacher_id = Teachers.id. Tämä on hyvä ajattelutapa, mutta tämä ei vastaa sitä, miten kunnollinen tietokantajärjestelmä toimii.
Ongelmana on, että tauluissa Courses ja Teachers voi molemmissa olla suuri määrä rivejä. Esimerkiksi jos kummassakin taulussa on miljoona riviä, rivien yhdistelmiä olisi miljoona miljoonaa ja veisi valtavasti aikaa muodostaa ja 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 Courses rivit ja etsiä jokaisen rivin kohdalla jotenkin tehokkaasti yksittäinen haluttu rivi taulusta Teachers.
Voimme taas pyytää SQLiteä selittämään kyselyn suunnitelman:
sqlite> EXPLAIN QUERY PLAN SELECT Courses.name, Teachers.name FROM Courses, Teachers WHERE Courses.teacher_id = Teachers.id;
0|0|0|SCAN TABLE Courses
0|1|1|SEARCH TABLE Teachers USING INTEGER PRIMARY KEY (rowid=?)
Tämä kysely käy läpi taulun Courses rivit (SCAN TABLE Courses) ja hakee tietoa taulusta Teachers pääavaimen avulla (SEARCH TABLE Teachers). Jälkimmäinen tarkoittaa, että kun käsittelyssä on tietty taulun Courses rivi, kysely hakee tehokkaasti taulusta Teachers rivin, jossa Teachers.id on sama kuin Courses.teacher_id.
Mutta miten käytännössä taulusta Teachers voi hakea tehokkaasti? Tämä onnistuu käyttämällä taulun yhteydessä olevaa indeksiä.
Indeksit
Indeksi on tietokannan taulun yhteyteen tallennettu hakemistorakenne, jonka tavoitteena on tehostaa tauluun liittyvien kyselyiden suorittamista. Indeksin avulla tietokantajärjestelmä voi selvittää tehokkaasti, missä päin taulua on rivejä, jotka täsmäävät tiettyyn hakuehtoon.
Indeksiä voi ajatella samalla tavalla kuin kirjan lopussa olevaa hakemistoa, jossa kerrotaan hakusanoista, millä kirjan sivuilla ne esiintyvät. Hakemiston avulla löydämme tietyn sanan sijainnit paljon nopeammin kuin lukemalla koko kirjan läpi.
Pääavaimen indeksi
Kun tietokantaan luodaan taulu, sen pääavain saa automaattisesti indeksin. Tämän ansiosta voidaan suorittaa tehokkaasti hakuja, joissa ehto liittyy pääavaimeen.
Esimerkiksi kun luomme SQLitessä taulun
CREATE TABLE Products (
id INTEGER PRIMARY KEY,
name TEXT,
price INTEGER
);
niin taululle luodaan indeksi sarakkeelle id ja voimme etsiä tehokkaasti tuotteita id-numeron perusteella. Tämän ansiosta esimerkiksi seuraava kysely toimii tehokkaasti:
SELECT price FROM Products WHERE id = 3;
Voimme varmistaa tämän kysymällä kyselyn suunnitelman:
sqlite> EXPLAIN QUERY PLAN SELECT price FROM Products WHERE id = 3;
selectid order from detail
---------- ---------- ---------- ---------------------------------------------------------
0 0 0 SEARCH TABLE Products USING INTEGER PRIMARY KEY (rowid=?)
Suunnitelmassa näkyy SEARCH TABLE, mikä tarkoittaa, että kysely pystyy hakemaan taulusta tietoa tehokkaasti indeksin avulla.
Indeksin luominen
Pääavaimen indeksi on kätevä, mutta voimme haluta myös etsiä tietoa jonkin muun sarakkeen perusteella. Esimerkiksi seuraava kysely hakee rivit sarakkeen price perusteella:
SELECT name FROM Products WHERE price = 4;
Tämä kysely ei ole oletuksena tehokas, koska sarakkeelle price ei ole indeksiä. Näemme tämän pyytämällä taas selitystä kyselystä:
sqlite> EXPLAIN QUERY PLAN SELECT name FROM Products WHERE price = 4;
selectid order from detail
---------- ---------- ---------- -------------------
0 0 0 SCAN TABLE Products
Nyt suunnitelmassa näkyy SCAN TABLE, mikä tarkoittaa, että kysely joutuu käymään läpi taulun kaikki rivit. Tämä on hidasta, jos taulussa on paljon rivejä.
Voimme kuitenkin luoda uuden indeksin, joka tehostaa saraketta price käyttäviä kyselyitä. Saamme luotua indeksin komennolla CREATE INDEX näin:
CREATE INDEX idx_price ON Products (price);
Tässä idx_price on indeksin nimi, jolla voimme viitata siihen myöhemmin. Indeksi toimii luonnin jälkeen täysin automaattisesti, eli tietokantajärjestelmä osaa käyttää sitä kyselyissä
ja huolehtii sen päivittämisestä.
Indeksin luomisen jälkeen voimme kysyä uudestaan kyselyn suunnitelmaa:
sqlite> EXPLAIN QUERY PLAN SELECT name FROM Products WHERE price = 4;
selectid order from detail
---------- ---------- ---------- -----------------------------------------------------
0 0 0 SEARCH TABLE Products USING INDEX idx_price (price=?)
Indeksin ansiosta suunnitelmassa ei lue enää SCAN TABLE vaan SEARCH TABLE. Suunnitelmassa näkyy myös, että aikomuksena on hyödyntää indeksiä idx_price.
Lisää käyttötapoja
Voimme käyttää indeksiä myös kyselyissä, joissa haemme pienempiä tai suurempia arvoja. Esimerkiksi sarakkeelle price luodun indeksin avulla voimme etsiä vaikkapa rivejä, joille pätee ehto price < 3 tai price >= 8.
Indeksi on myös mahdollista luoda usean sarakkeen perusteella. Esimerkiksi voisimme luoda indeksin näin:
CREATE INDEX idx_price ON Products (price, name);
Tässä indeksissä rivit on järjestetty ensisijaisesti hinnan ja toissijaisesti nimen mukaan. Indeksi tehostaa hakuja, joissa hakuperusteena on joko pelkkä hinta tai yhdessä hinta ja nimi. Kuitenkaan indeksi ei tehosta hakuja, joissa hakuperusteena on pelkkä nimi.
Miten indeksi toimii?
Indeksi tarvitsee tuekseen hakemistorakenteen, josta voi hakea tehokkaasti rivejä sarakkeen arvon perusteella. Tämä voidaan toteuttaa esimerkiksi puurakenteena, jonka avaimina on sarakkeiden arvoja.
Asiaan liittyvää teoriaa käsitellään tarkemmin kurssilla Tietorakenteet ja algoritmit binäärihakupuiden yhteydessä. Tyypillisiä tietokantojen yhteydessä käytettäviä puurakenteita ovat B-puu ja sen muunnelmat.
Milloin luoda indeksi?
Periaatteessa voisi ajatella, että taulun jokaiselle sarakkeelle kannattaa luoda indeksi, jolloin monenlaiset kyselyt ovat nopeita. Tämä ei ole kuitenkaan käytännössä hyvä idea.
Vaikka indeksit tehostavat kyselyitä, niissä on myös kaksi ongelmaa: indeksi vie tilaa ja hidastaa myös tiedon lisäämistä ja muuttamista. Jälkimmäinen johtuu siitä, että kun taulun sisältö muuttuu, muutos täytyy päivittää kaikkiin tauluun liittyviin indekseihin. Indeksiä ei siis kannata luoda huvin vuoksi.
Hyvä syy indeksin luontiin on, että haluamme suorittaa usein tietynlaisia kyselyitä ja ne toimivat hitaasti, koska tietokantajärjestelmä joutuu käymään läpi turhaan jonkin taulun kaikki rivit kyselyn aikana. Tällöin voimme lisätä taululle sopivan indeksin, jonka avulla tällaiset kyselyt toimivat jatkossa tehokkaasti.
Indekseillä on käytännössä suuri vaikutus tietokantojen tehokkuuteen. Moni tietokanta toimii hitaasti sen takia, että siitä puuttuu oleellisia indeksejä.
Indeksit ovat myös yksi esimerkki siitä, miten toisteinen tieto voi tehostaa kyselyjä. Indekseissä kuitenkaan toisteista tietoa ei tallenneta tauluun vaan taulun ulkopuolelle erilliseen hakemistorakenteeseen.