Oppaat: MySQL ja PHP: Osa 10 - Lisätietoa

Opassarjan päätteeksi luomme katsauksen edistyneempiin tietokanta-aiheisiin: Indeksit nopeuttavat kyselyjä muodostamalla hakemistorakenteen taulun tiedoista. Transaktiot varmistavat, että joukko kyselyjä suoritetaan yhtenä kokonaisuutena. Viiteavaimet huolehtivat siitä, että taulujen väliset viittaukset ovat kelvollisia.

Indeksit

Indeksi on taulun oheen tallennettava hakemistorakenne, josta voi tarkistaa nopeasti, missä päin taulua on rivejä, joiden kentät vastaavat hakuehtoja. Indeksit nopeuttavat tietokannasta tehtäviä hakuja, ja jos tietokanta on suuri, nopeuserot voivat olla todella merkittäviä.

Indeksiä voi verrata kirjan lopussa olevaan hakemistoon. Jos kirjasta täytyy etsiä kohta, jossa mainitaan sana "tietokanta", yksi tapa on lukea kirja alusta loppuun. Kuitenkin nopeampaa on katsoa hakemistosta, että sana "tietokanta" esiintyy sivulla 153. Tiedon hakeminen taulusta ilman indeksiä vastaa kirjan lukemista kokonaan, ja tiedon hakeminen indeksin kanssa vastaa hakemiston käyttämistä.

Jokainen indeksi liittyy yhteen tai useampaan taulun kenttään. Indeksejä on järkevää luoda kentille, jotka esiintyvät usein hakuehdoissa. Taulun avain saa indeksin automaattisesti. Toisaalta indeksit hidastavat taulun tietojen muuttamista, koska muutokset täytyy kirjata myös indeksiin.

Seuraava kysely lisää tauluun tuotteet indeksin kentälle hinta. Tämän jälkeen tuotteita on nopeampaa etsiä hinnan perusteella.

CREATE INDEX omaindeksi ON tuotteet (hinta);

MyISAM ja InnoDB

MySQL:n kaksi vaihtoehtoista tietokantamoottoria ovat MyISAM ja InnoDB. Oletuksena käytössä on MyISAM, mutta InnoDB sisältää ominaisuuksia, jotka parantavat tietokannan luotettavuutta.

InnoDB:n käytöstä täytyy ilmoittaa taulun luonnissa seuraavasti:

CREATE TABLE tuotteet (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nimi TEXT,
    hinta INT
) ENGINE=InnoDB;

Seuraavaksi esiteltävät transaktiot ja viiteavaimet toimivat vain InnoDB:ssä. Jos käytössä on MyISAM, MySQL ei varoita asiasta!

Transaktiot

Välillä joukko perättäisiä tietokantaa muuttavia kyselyjä liittyy toisiinsa. Esimerkiksi jos keskustelupalstalle aloitetaan uusi keskusteluketju, tietokantaan täytyy lisätä uusi rivi sekä ketjut että viestit sisältävään tauluun. Ketju on järkevää lisätä ensin, koska viestiin täytyy tulla viittaus ketjuun.

Tässä menettelyssä on muutama ongelma: Jos jostain syystä viestin lisääminen ei onnistu (esim. yhteys tietokantaan katkeaa kyselyjen välillä), tietokantaan ilmestyy tyhjä keskusteluketju. Toisaalta jos tietokannasta satutaan hakemaan tietoa juuri kyselyjen välillä, käyttäjä voi nähdä keskeneräisen tilanteen.

Näiden ongelmien vuoksi olisi toivottavaa, että kesken jääneen kyselysarjan tekemät muutokset peruutettaisiin ja tietokannan väliaikainen tila ennen viimeisen kyselyn valmistumista ei näkyisi ulkopuolelle. Transaktiot ovat vastaus näihin toivomuksiin.

Seuraava PHP-koodi esittelee transaktioiden käyttöä:

try {
    $pdo->beginTransaction();
    // tässä on joukko toisiinsa liittyviä kyselyjä
    $pdo->commit(); // muutosten hyväksyntä
} catch (PDOException $e) {
    $pdo->rollBack(); // muutosten peruutus
    die("VIRHE: " . $e->getMessage());
}

Viite-eheys

Tähän mennessä taulujen viittaukset on toteutettu niin, että ensimmäiseen tauluun luodaan kokonaislukukenttä ja siihen tallennetaan toisessa taulussa oleva avain. Tällöin viittausten järkevyys on ohjelmoijan omalla vastuulla: viittaukseksi voi tallentaa vaikkapa luvun 12345, vaikka vastaavaa avainta ei olisi olemassa. Lisäksi jos toisesta taulusta poistetaan tietoa, aiemmin kelvollinen viittaus voi vanhentua.

Jos viittauksen sisältävän kentän määrittää erikseen viiteavaimeksi, MySQL pitää huolen siitä, että tallennetut kokonaisluvut ovat todella toisen taulun avaimia. Samalla viittauksen kohteena olevan rivin muutoksiin voi varautua eri tavoin: esimerkiksi viitattavan rivin poistaminen voi aiheuttaa automaattisesti myös viittaavan rivin poistamisen.

Seuraava kysely luo taulun tarjoukset niin, että kenttä tuote_id on viiteavain tauluun tuotteet.

CREATE TABLE tarjoukset (
    id INT PRIMARY KEY AUTO_INCREMENT,
    viikko INT,
    tuote_id INT,
    FOREIGN KEY (tuote_id) REFERENCES tuotteet (id)
) ENGINE=InnoDB;

Loppusanat

Toivottavasti tämä opassarja on ollut sinulle hyödyllinen MySQL:n opettelussa. Voit lähettää palautetta ja kysymyksiä oppaista sähköpostitse osoitteeseen antti.laaksonen@ohjelmointiputka.net. Muista kertoa samalla, mistä aiheista haluaisit lukea tulevaisuudessa Ohjelmointiputkan oppaista.