Kirjautuminen

Haku

Tehtävät

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

  1. Osa 1 - Johdanto
  2. Osa 2 - PHP ja PDO
  3. Osa 3 - Hakukyselyt I
  4. Osa 4 - Hakukyselyt II
  5. Osa 5 - Hakukyselyt III
  6. Osa 6 - Lisäys, muutos ja poisto
  7. Osa 7 - Tietotyypit
  8. Osa 8 - Tietokannan suunnittelu
  9. Osa 9 - Monta taulua
  10. Osa 10 - Lisätietoa
  11. Liite 1 - MySQL:n komentorivityökalu
  12. Liite 2 - phpMyAdmin-sovellus

Kirjoittaja: Antti Laaksonen. Vuosi: 2009.

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@mbnet.fi. Muista kertoa samalla, mistä aiheista haluaisit lukea tulevaisuudessa Ohjelmointiputkan oppaista.


Kommentit

Rocceri [05.07.2009 14:59:43]

Lainaa #

Todella hieno opassarja! :) Toivottavasti tästä on apua monille.

Hakoulinen [05.07.2009 23:33:43]

Lainaa #

Näkymistä ja triggereistä jäin kaipaamaan edes vikaan osaan pientä esittelyä

punppis [08.07.2009 23:19:26]

Lainaa #

Indexit tuli uutena asiana, kiitos tästä.

ApE!0 [26.07.2009 20:16:26]

Lainaa #

Hienoa että tänne putkankin puolelle on saatu asiasta hyvä opas. En vielä ole kokonaan lukenut, mutta mikäli laatu vastaa edellisiä on artikkeli parhaimpien suomenkielisten joukossa. Toivottavasti en joudu perumaan sanojani ..

pXhZa [01.08.2009 03:09:06]

Lainaa #

Todella hyvä opas, selkeytti MySQL:n ja PDO:n käyttöä merkittävästi.

MikaBug [10.08.2009 19:23:19]

Lainaa #

Todella mainio opas ja jälleen sellaista laatua mitä Antilta on tottunut odottamaankin! :) Varsinkin PDO:n käyttö PHP:ssä oli itselle hyödyllinen osa opassarjaa. Varsin kattava tietopaketti sellaiselle, jolla ei vielä suurempaa kokemusta tietokantojen käytöstä ole (toisaalta uutta asiaa tulee varmasti vanhemmallekin tekijälle). Jään innolla odottamaan seuraavaa - etenkin PHP-aiheista - opassarjaa Antilta!

walkout_ [05.12.2009 23:18:36]

Lainaa #

Voisiko olla järkevää mainita myös että relaatiotietokanta ei ole ainoa. On kuulemma dokumentti, tiedosto ja XML-tietokantoja.
Mm. XML-tietokannassa voidaan XML:llä määrittää dynaamisesti lomake, millä ks. tietokantaan dynaamisesti syötetään dataa ja erikseen XML:llä määritetään miten data luetaan ja sille erikseen editointia varten XML-määritykset miten dataa voi editoida.
XML-tietokanta mahdollistaa sen että minkälaisen datan syöttöön sitä käytetään ja miten ilman että ohjelmiston lähdekoodiin tehdään muutoksia.

walkout_ [05.12.2009 23:24:52]

Lainaa #

Luin tuosta tietokantojen suunnitelu kirjasta että Indexit hidastavat vaan kannan toimintaa jos niitä käytetään turhaan osissa missä niitä ei oikeasti tarvita. Oikein käytettynä toiminta nopeutuu tietyssä mielessä.

walkout_ [05.12.2009 23:27:17]

Lainaa #

Myöskin indexi Unique mahdolistaa sen että sama tietoa ei turhaan syötetä kahteen kertaan.. Esim. että haluamme laittaa IP-numerot kantaan vain kerran.. ja toista kertaa ei voi laittaa samaa IP-numeroa.. joissakin tapauksissa tiettyjen arvojen duplikaatit voi sotkea koko ohjelman toiminnan.

GrayMan [23.04.2010 22:27:02]

Lainaa #

Pidin opassarjasta. Todella hyvin selitetty. :)

latenleffahylly [17.07.2012 17:22:54]

Lainaa #

Täydellinen opassarja, johon käytetty valtavasti aikaa.

Tämä on villi veikkaukseni sillä en löytänyt yhtään turhaa sanaa, tai koodin pätkää. Koodiesimerkit ovat täysin toimivia. (harjoittelen oman webhotellin kanssa). Suurkiitokset!

Kirjoita kommentti

Huomio! Kommentoi tässä ainoastaan tämän oppaan hyviä ja huonoja puolia. Älä kirjoita muita kysymyksiä tähän. Jos koodisi ei toimi tai tarvitset muuten vain apua ohjelmoinnissa, lähetä viesti keskusteluun.

Muista lukea keskustelun ohjeet.
Tietoa sivustosta