Materialized views in MySQL

March 14, 2007 2 minute read

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?

Vini