SQL

Materialized views in MySQL

sreda, 14. marec 2007 steak and blowjob day

Včasih pridemo do problema, ko želimo na svoji spletni predstavitvi objaviti kakšno statistiko, ki zahteva zelo požrešno poizvedbo, rezultat le-te pa je bolj kot ne zelo statičen. Zaradi požrešnosti poizvedbe in dolgega časa izvajanja se nam zmanjša odzivnost strani. Materialized views so lahko v takih primerih silno uporabna zadevščina, tukaj bom na kratko napisal, kako v MySQL, ki jih sicer ne podpira, vnesti vsaj nekaj okusa po njih.

Kaj točno so materialized views? So shranjeni podatki, ki vsebujejo predobdelane rezultate, ki smo jih pridobili preko SQL SELECT poizvedbe. Ker so podatki vnaprej izračunani, dobimo rezultate poizvedbe hitreje.

V katerem konkretnem primeru bi nam materialized views prišli prav? Enostavni primeri so recimo člankarski portal, kjer želimo prikazati seznam zadnjih nekaj člankov, potem recimo portal, kjer uporabniki ocenjujejo druge uporabnike, želimo pa prikazati nekaj najbolje ocenjenih, in še in še.

Recimo, da imamo naslednjo tabelo člankov:

CREATE TABLE clanki (
id INT AUTO_INCREMENT,
naslov TEXT,
vsebina TEXT,
objava DATE,
PRIMARY KEY (id),
INDEX objava (objava)
);

Če imamo v gornji tabeli manjše število podatkov, zadeva še nekako gre, kaj pa se zgodi, ko želite prikazati zadnjih 10 člankov, ko jih imate v tabeli že 100.000? Kakšen query ponavadi napišemo za to?

SELECT * FROM clanki ORDER BY objava DESC LIMIT 10;

Kaj se dogaja, ko izvedemo naslednji query? Poskusimo naslednje na tabeli s 100.000 zapisi:

EXPLAIN SELECT * FROM clanki ORDER BY objava DESC LIMIT 10;

Poizvedba povzroči obdelavo vseh 100.000 zapisov iz tabele skupaj s sortiranjem, kar seveda lahko traja kar precej časa. Želeli smo pa le rezultat, ki vsebuje 10 zapisov! Če želimo rezultat te poizvedbe prikazovati pri vsakem prikazu naše strani, imamo pa lahko že kar problem, kaj šele, če je takih poizvedb več.

Kaj storiti? Pripravimo vmesno tabelo, ki bo vsebovala samo seznam zadnjih nekaj objavljenih člankov.

CREATE TABLE clanki_zadnji (
id INT
);

Dobro, ampak ta tabela je prazna, kdo jo bo polnil? Napišemo najprej naslednjo proceduro:

DELIMITER |
CREATE PROCEDURE clanki_zadnji_materializiraj ()
BEGIN
DELETE FROM clanki_zadnji;
INSERT INTO clanki_zadnji
SELECT id
FROM clanki
ORDER BY objava DESC
LIMIT 10;
END;
|
DELIMITER ;

Sedaj lahko proceduro zaženemo:

CALL clanki_zadnji_materializiraj();

V tabeli clanki_zadnji imamo sedaj 10 zapisov. Super, ampak ali bomo to vedno morali poganjati ročno? Ne bo potrebe, zato imamo triggerje. Kreiramo tri triggerje, po enega za INSERT, UPDATE in DELETE:

CREATE TRIGGER clanki_insert
AFTER INSERT ON clanki
FOR EACH ROW CALL clanki_zadnji_materializiraj();
CREATE TRIGGER clanki_update
AFTER UPDATE ON clanki
FOR EACH ROW CALL clanki_zadnji_materializiraj();
CREATE TRIGGER clanki_delete
AFTER DELETE ON clanki
FOR EACH ROW CALL clanki_zadnji_materializiraj();

Poskusite nekaj člankov dodati, brisati, spreminjati. Vsakič, ko naredite katerokoli od teh sprememb, se podatki v tabeli clanki_zadnji osvežijo.

Poglejmo si sedaj naslednjo analizo:

EXPLAIN
SELECT clanki.*
FROM clanki_zadnji, clanki
WHERE clanki.id = clanki_zadnji.id
ORDER BY clanki.objava DESC;

Rezultat je seveda veliko boljši, kot zgoraj. Potrebnih je bilo le 10 skokov, da smo prišli do željenega rezultata.

Za konec lahko kreiramo še view za zadnje članke:

CREATE VIEW clanki_zadnji_view AS
SELECT clanki.*
FROM clanki_zadnji, clanki
WHERE clanki.id = clanki_zadnji.id
ORDER BY clanki.objava DESC;

in potem preprosto dostopamo do podatkov o zadnjih desetih člankih z naslednjo poizvedbo:

SELECT * FROM clanki_zadnji_view;

Tole je konec tega enostavnega primera uporabe materialized views, vsekakor bi z zadevo lahko počeli še vse kaj zahtevnejšega kot to, ampak za približno predstavo, kaj materialized views sploh so, in kako jih simulirati v MySQL, bo pa kar dovolj.

Domača naloga: kaj pa če članke pišemo vnaprej in želimo, da se med zadnjimi članki pojavijo šele, ko je polje objava manjše ali enako današnjemu datumu?

1.
16. marec 2007, 13:03

tole bo prov v redu :)

2.
hoho
19. marec 2007, 15:08

Ali pa pač narediš cache za celo stran in serviraš statične strani. Bolj enostavno in bolj učinkovito.

3.
19. marec 2007, 17:33

hoho, teoretično imaš seveda prav, kaj pa, če vseeno želiš na strani nekaj dinamične vsebine?

4.
hoho
19. marec 2007, 21:59

Then you're screwed :)

Imam tak primer s str_replace zamenjam stvar, ki naj bi bila dinamična.. je pa res da s tem počasi izginejo prednosti mojega pristopa

5.
Mr. Dee
20. marec 2007, 12:13

Super članek vini, kar tako naprej :)

6.
20. marec 2007, 12:57

Kulska reč tele materializirani viewi.. malo je edino butasto, ker mysql ne zmore takih view-ev že sam od sebe in je treba trike iskat :-/

Drgač pa lahko narediš tabelo direkno iz rezultata SQL stavka..
CREATE TABLE clanki_zadnji ENGINE = MYISAM AS
SELECT
...

Pa truncate je hitrejši kot delete, le da ni transaction safe...

7.
20. marec 2007, 13:11

ruph, imas prav glede TRUNCATE, ena od slabosti je pa tudi ta, da ne sproži DELETE triggerja, kar tukaj niti ni problem, glede na to, da tega triggerja ni na tabeli clanki_zadnji. Menim tudi, da tukaj hitrost ni problematična, konec koncev le brišem samo 10 zapisov iz tabele in to samo takrat, ko osvežim podatke kakšnega članka.

8.
30. september 2007, 20:26

Hmm, zanimivo opazil sem da uporabljaš * pri selectu, recimo SELECT * FROM ... , kolikšna pa bi bila razlika če bi uporabljal recimo SELECT `potrebno_polje`, `potrebno_polje2` FROM ... torej da nebi pobiral vseh spremenljivk ? Bi se to zelo poznalo ?

9.
1. oktober 2007, 10:55

Tux, seveda se pozna. Če nikjer drugje, se pozna pri količini pomnilnika, ki ga zasede rezultat poizvedbe, pri količini podatkov, ki jih prenašaš preko socketa, ko zahtevaš podatke vrstico za vrstico, pa gotovo še kje. Te zadeve so zagotovo zapisane kje v MySQL manualu, malo pobrskaj.