Tietokannat: Näkymät, indeksit ja herättimet

Näkymät / Views

• Näkymä (view) on looginen näyte tietokannan tauluista tai näkymistä
• Näkymä ei voi sisältää SELECT INTO, ORDER BY tai COMPUTE –ehtoja (useimmissa tuotteissa)
• Ei voi viitata väliaikaisiin tauluihin
• Tarvitaan SELECT-oikeus tauluihin

CREATE VIEW Hinnasto
AS SELECT nimi, hinta FROM tuote

-- Käyttö

SELECT * FROM Hinnasto

Näkymän käyttö

• Näkymän kautta voidaan päivittää tai lisätä tietoja, mutta
  
• yleensä vain yhteen tauluun
• näkymässä ei saa olla DISTINCT-määrettä, HAVING-lausetta eikä alikyselyä
• Näkymä poistetaan DROP VIEW -komennolla
  
• Jos näkymässä on laskettuja sarakkeita, view-määrittelyn nimen jäljessä täytyy luetella sarakkeiden nimet:
  

CREATE VIEW ALVhinnasto(nimi, ALVhinta)
AS SELECT nimi, hinta*1.22
FROM TUOTE

Vinkkejä näkymien luontiin

• Testaa ensin kysely ja luo vasta sitten näkymä
• Jos taulun nimi muuttuu, luo kysely jossa on alkuperäisen taulun kentät; tällä tavoin sinun ei tarvitse muuttaa olemassa olevia SELECT- ym. käskyjä
• Näkymillä voit suojata tietoja käyttäjäryhmittäin
• Voit rakentaa näkymän toisen näkymän päälle, mutta se voi vaikeuttaa ymmärtämistä
• Joissakin tuotteissa nk. Materialized view (eli indexed view), jossa näkymä tallennettu levylle (ikään kuin tauluksi)
  

Esimerkkejä näkymistä

CREATE VIEW turku AS
SELECT ptun, pnimi, priorit, sijainti
FROM projekti
WHERE sijainti = 'TURKU';


-- KÄYTTÖ:

SELECT *
FROM turku;

SELECT pnimi
FROM turku
WHERE pnimi like 'K%'
ORDER BY pnimi -- ORDER BY pitää olla täällä EI näkymän luonnissa!

CREATE VIEW hlot
(sukunimi, etunimi, htun, tutkinto, pvm, kunta, ostun) AS
SELECT snimi, enimi, htun, tutkinto, pvm, kunta, ostun
FROM henkilo


SELECT *
FROM hlot

Näkymän sarakeotsikoista

CREATE VIEW tunnit_yht AS
SELECT ptun, SUM(tunnit) AS yht
FROM proj_henk
GROUP BY ptun

SELECT *
FROM tunnit_yht

-- TAI

CREATE VIEW tunnit_yht (ptun, yht) AS -- sarakeotsikot oltava täällä, jos funktioita ilman aliaksia
SELECT ptun, SUM(tunnit)
FROM proj_henk
GROUP BY ptun

SELECT *
FROM tunnit_yht

Indeksit

• Tauluille voidaan perustaa sarakekohtaisia hakemistoja eli indeksejä, esim.
  
  

CREATE INDEX i_postinro ON henkilo(postinro)

• Indeksien käytön tarkoituksena on mm. nopeuttaa tietokantaan tapahtuvia hakuja
• Yksilöivä (unique) indeksi voi toimia myös perusavaimena
  

CREATE UNIQUE INDEX i_sarake ON taulu(sarake)

Indeksien käyttö

• Indeksoinnin etuja:
• hakujen, liitosten, ryhmittelyjen ja lajittelujen nopeutuminen
• pakottaa yksilöimään rivit
• Kannattaa indeksoida:
• perusavaimet ja viiteavaimet (jokainen viiteavain erikseen!)
• sarakkeet, joita haetaan tai järjestetään usein (ja joissa on paljon tietoa eli rivejä on tuhansia tai miljoonia)
• Indeksointi vie levytilaa ja aikaa luotaessa ja muutoksissa
• Ei kannata indeksoida:
• harvoin haussa käytettäviä sarakkeita
• sarakkeita, joiden selektiivisyys (erottelukyky) on huono (esim henkilo-taulun ostun-kenttä on huono koska henkilöitä kuuluu vain muutamaan osastoon)

Peukalosääntö indeksin luomiselle (nk. 3 tähden indeksi)

1. Ota kaikki WHERE-kohdassa mainitut sarakkeet
2. Lisää ORDER BY –kohdassa mainitut sarakkeet
3. Laita loppuun SELECT-lauseessa olevat sarakenimet

Esimerkiksi kyselylle

SELECT eka, toka, koka FROM taulu WHERE neka = ’x’ ORDER BY toka

paras mahdollinen indeksi olisi

CREATE INDEX i_nekatokaekakoka ON taulu(neka,toka,eka,koka)

• Jos kaikki SELECTissä mainitut sarakkeet on mukana indeksissä, kyse on paksusta indeksistä
• Indeksi poistetaan DROP INDEX -käskyllä
  

Indeksit: miten?, vaikutus suorituskykyyn

Luodaan testitaulu

-- Luodaan taulu, lisätään dataa, katsotaan tulos

CREATE TABLE henkilot (
tunnus varchar(6) not null,
sukunimi varchar(128) not null,
etunimi varchar(128) not null,
osoite varchar(255) not null,
puhnro varchar(64) not null,
email varchar(128),
PRIMARY KEY (tunnus),
UNIQUE (email)
);

INSERT INTO henkilot VALUES
('havpen', 'Haverinen', 'Pentti', 'Poritie 4', '00001','pena@server.fi'),
('kanvis','Kanninen','Visa','Kumpiuja 2','11234','kane@abcde.fi'),
('polrau','Polkunen','Rauno','Umpikuja 1','01234','rane@abcde.fi');

SELECT * FROM henkilo;

Tutkitaan indeksejä

-- Katsotaan indeksi

SHOW INDEX FROM henkilot;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| henkilot |          0 | PRIMARY  |            1 | tunnus      | A         |           3 |     NULL | NULL   |      | BTREE      |         |
| henkilot |          0 | email    |            1 | email       | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

-- --> Indeksit PRIMARY ja email.
-- --> Cardinality ~ indeksin yksittäisten arvojen lkm ~ rivien lkm

-- Kaikki indeksit:

SELECT DISTINCT TABLE_NAME, INDEX_NAME 
FROM INFORMATION_SCHEMA.STATISTICS;

-- henkilot-taulujen indeksit:

SELECT DISTINCT TABLE_NAME, INDEX_NAME 
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_NAME = 'henkilot';

Luodaan 200 000 tietuetta PHP-ohjelmalla

-- Luodaan PHP-ohjelma teeiso.php esim.
-- nano teeiso.php

<?php
$fp = fopen("file.sql", "a+");
for($i=1;$i<=200000;$i++) {
  fwrite($fp,"INSERT INTO henkilot VALUES('$i','Suku$i','E$i','O$i','$i','$i@com');\n");
}
fclose($fp);
?>

-- Ajetaan PHP-ohjelma:

php teeiso.php

-- tuloksena tiedosto file.sql, jonka ekat ja vimpat rivit:

head -3 file.sql 
INSERT INTO henkilot VALUES('1','Suku1','E1','O1','1','1@com');
INSERT INTO henkilot VALUES('2','Suku2','E2','O2','2','2@com');
INSERT INTO henkilot VALUES('3','Suku3','E3','O3','3','3@com');
tail -3 file.sql 
INSERT INTO henkilot VALUES('199998','Suku199998','E199998','O199998','199998','199998@com');
INSERT INTO henkilot VALUES('199999','Suku199999','E199999','O199999','199999','199999@com');
INSERT INTO henkilot VALUES('200000','Suku200000','E200000','O200000','200000','200000@com');


-- Syötetään rivit ara-nimiseen tietokantaan,
-- kestänee muutaman minuutin, mutta ei 10 min kauempaa:

mysql -u ara -pSALASANA -h mysql.labranet.jamk.fi ara < file.sql

Testataan

mysql -u ara -pSALASANA -h mysql.labranet.jamk.fi ara

mysql> SELECT COUNT(*) FROM henkilot;
+----------+
| COUNT(*) |
+----------+
|   200003 |
+----------+
1 row in set (0.04 sec)


mysql> SELECT sukunimi FROM henkilot WHERE tunnus = 175175;
+------------+
| sukunimi   |
+------------+
| Suku175175 |
+------------+
1 row in set (0.21 sec)


mysql> SELECT sukunimi FROM henkilot WHERE tunnus = 175175;
+------------+
| sukunimi   |
+------------+
| Suku175175 |
+------------+
1 row in set (0.00 sec)


mysql> SELECT sukunimi FROM henkilot WHERE sukunimi = 'suku175175';
+------------+
| sukunimi   |
+------------+
| Suku175175 |
+------------+
1 row in set (0.10 sec)


mysql> CREATE INDEX i_sukunimi ON henkilot(sukunimi);
Query OK, 200003 rows affected (2.82 sec)
Records: 200003  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM henkilot;
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| henkilot |          0 | PRIMARY    |            1 | tunnus      | A         |      199249 |     NULL | NULL   |      | BTREE      |         |
| henkilot |          0 | email      |            1 | email       | A         |      199249 |     NULL | NULL   | YES  | BTREE      |         |
| henkilot |          1 | i_sukunimi |            1 | sukunimi    | A         |      199249 |     NULL | NULL   |      | BTREE      |         |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)


mysql> SELECT sukunimi FROM henkilot WHERE sukunimi = 'suku176111';
+------------+
| sukunimi   |
+------------+
| Suku176111 |
+------------+
1 row in set (0.01 sec)


mysql> SELECT sukunimi FROM henkilot WHERE etunimi = 'e175175';
+------------+
| sukunimi   |
+------------+
| Suku175175 |
+------------+
1 row in set (0.09 sec)

• Johtopäätökset:
• indeksillä sukunimihaku nopeutui lähes 10 kertaa, vaikka tämä aineisto on siinä mielessä huono, että tietueet lisätään jo valmiiksi "oikeassa järjestyksessä".

Herättimet (triggers)

• Proseduurit ovat ohjelmamoduuleita, joita voidaan tallentaa tietokannan systeemihakemistoon (ei käsitellä tarkemmin)
  
• Herätin on proseduurien erikoistapaus
• Herätin käynnistyy automaattisesti DML-tietokantaoperaatioista (INSERT, UPDATE tai DELETE)
• Eheyden valvontaan ja monimutkaisiin tarkistuksiin
• Muuttuneiden, poistuneiden ja uusien tietojen tallentamiseen toiseen tauluun => loki
• Tietojen toisteisuuden hallintaan (eheystarkistukset, joita ei ole tehty viite-eheyssäännöillä)
• Herätintä ei tarvitse siis kutsua erikseen!
  

Check-rajoitteen toteuttaminen herättimellä (MySQL)

• MySQL:stä puuttuvan CHECK-rajoitteen totetuksen puuttumisen voi toteuttaa herättimillä
• Esimerkki: Luodaan taulu, jossa on yksi kokonaislukukenttä luku
  
• Luodaan herättimet, jotka salivat ainoastaan positiivisten kokonaislukujen lisäämisen ja päivittämisen ao
  

-- Luodaan taulu ja lisätään dataa
CREATE TABLE taulu (luku INT) TYPE=InnoDB;
INSERT INTO taulu(luku) VALUES (1);
INSERT INTO taulu(luku) VALUES (2);
INSERT INTO taulu(luku) VALUES (3);


mysql> show triggers;
Empty set (0.00 sec)


-- Herätin lisäyksiä varten
DELIMITER $$

CREATE TRIGGER trigger1 
  BEFORE INSERT ON taulu
  FOR EACH ROW 
BEGIN
  IF !(NEW.luku >=0) THEN
    CALL `Virhe: Virheellinen arvo sarakkeeseen luku Uusi tietue`;
  END IF;
END$$

DELIMITER ;

-- Herätin lisäyksiä varten
DELIMITER $$

CREATE TRIGGER trigger2
  BEFORE UPDATE ON taulu
  FOR EACH ROW 
BEGIN
  IF !(NEW.luku >=0) THEN
    CALL `Virhe: Virheellinen arvo sarakkeeseen luku! Uusi tietue`; 
  END IF;
END$$

DELIMITER ;

--
mysql> show triggers;

+----------+----------+----------+----------+
| Trigger  | 
+----------+----------+----------+----------+
| trigger1 |
...

-- Lisätään dataa:

mysql> INSERT INTO taulu VALUES (4);
Query OK, 1 row affected (0.01 sec)

-- Yritetään lisätä
mysql> INSERT INTO taulu VALUES (-4);
ERROR 1305 (42000): PROCEDURE ara.Virhe: Virheellinen arvo sarakkeeseen luku Uusi tietue does not exist

-- Yritetään päivittää
mysql> UPDATE taulu set luku = -4 WHERE luku = 4;
ERROR 1305 (42000): PROCEDURE ara.Virhe: Virheellinen arvo sarakkeeseen luku Uusi tietue does not exist

-- Herättimien poistaminen haluttaessa:

mysql> drop trigger trigger1;
mysql> drop trigger trigger2;

• Huom: MySQL kyllä jäsentää (parse) CHECK-rajoitteen, mutta toimintaa ei ole implementoitu
• ====> CHECKin käyttämisestä ei tule virhe-ilmoitusta, mutta rajoite EI toimi!
  

---

Osa materiaalista on muokattu Jouni Huotarin materiaalista.

---