MySQL: Opomenutá vlastnost – pohledy

Nejsem velkým vyznavačem MySQL, už jsem se tu pokoušel porovnat MySQL a PostgreSQL z mého pohledu, ale používám ji, protože řadě zákazníků nemohu změnit webhosting. Mnohé hostingy ale nabízejí i MySQL 5.x, jež vypadá jako dospělá databáze a umí i pohledy, které mohou významně ušetřit zátěž databáze, ale přesto jsou ve velkém opomíjené.

Pro jeden z mých projektů připravuji data a protože mám ceníky od několika výrobců a dodavatelů (ti nemusejí mít v nabídce jen jednoho výrobce) a kolem databází se pohybuji již netriviálně krátkou dobu, vím, že je potřeba data pro uložení do tabulek optimalizovat, normalizovat. Normalizace ale znamená, že máte místo jedné tabulky s více sloupci několik tabulek, z nichž většina funguje jako číselníky. Je to příjemné z hlediska velikosti obsazeného diskového prostoru, trochu méně příjemné pro vkládání, protože při importu ceníku musíte každý řádek rozebrat do několika částí a ty podle potřeby uložit do databáze.

Pro výběr v trochu přehledné formě je sice možné využít v dotazech spojování tabulek (JOIN), ale to nemusí být zrovna nejrychlejší řešení, režie konverzí datových typů mezi aplikací a databází bude velmi pravděpodobně to ještě poměrně malé zlo, horší bude, že databáze musí (tak trochu) analyzovat, podle jakých sloupců se vybírají data a jaké potřebuje využít indexy. Mnohem rychlejší, a výhodnější i z hlediska zatížení databázového serveru, je využití pohledů.

Pohledy vcelku běžně znají uživatelé pokročilejších databázových řešení. Nejsnadnějším vysvětlením ,toho co jsou, je použít pro ně název virtuální tabulka, která je obvykle určena jen pro čtení dat. MySQL je až do verze 5.0 neuměla, ač to byla dost požadovaná funkčnost a celá řada vývojářů by je zajisté ocenila. Verze 5.0 byla přelomová ve více oblastech, ale v tomto článečku chci psát jen o pohledech. Dá se napsat, že verze 5.0 udělala z MySQL seriózní databázi a přestala být pouze rychlým filesystémem, s indexací a jakýmsi fulltextovým vyhledáváním, vhodným leda jako webový backend.

V mém nasazení, respektive v jedné z mnoha databází, které mi na serveru běží jsou data v následující struktuře:

CREATE TABLE zbozi (
kod varchar(200) collate utf8_czech_ci NOT NULL,
nazev text collate utf8_czech_ci NOT NULL,
vyrobci_id int(10) unsigned NOT NULL,
dodavatele_id int(10) unsigned NOT NULL,
mnozstvi int(11) NOT NULL,
nakup double NOT NULL default '0',
imported tinyint(4) NOT NULL default '0',
PRIMARY KEY (kod)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

CREATE TABLE kategorie (
kategorie_id int(11) NOT NULL auto_increment,
vltava varchar(25) collate utf8_czech_ci default NULL,
vltava2 varchar(25) collate utf8_czech_ci default NULL,
zencart varchar(255) collate utf8_czech_ci NOT NULL,
PRIMARY KEY (kategorie_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
COLLATE=utf8_czech_ci AUTO_INCREMENT=57 ;

CREATE TABLE dodavatele (
dodavatele_id int(10) unsigned NOT NULL auto_increment,
nazev varchar(50) collate utf8_czech_ci NOT NULL,
PRIMARY KEY (dodavatele_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=3 ;

CREATE TABLE vyrobci (
vyrobci_id int(10) unsigned NOT NULL auto_increment,
vyrobce varchar(50) collate utf8_czech_ci default NULL,
vltava int(10) unsigned default '0',
PRIMARY KEY (vyrobci_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=24 ;

Je to struktura dat po normalizaci, kdy jsou duplicitní data vyřazena do zvláštních tabulek, fungujících, jako regulerní číselníky. Velmi často potřebuji pro přípravu importních seznamů název výrobku, kolik jej je skladem, jaká je nákupní cena (koeficienty se převádí na prodejní), název výrobce a jméno dodavatele. Samozřejmě to lze realizovat JOINovaným SELECTEM, například:

SELECT t1.kod AS kod,t1.nazev AS nazev,t2.vyrobce AS vyrobce,
t2.vltava AS nomenklatura, t3.nazev AS dodavatel, t1.mnozstvi AS mnozstvi,
t1.nakup AS nakup, t1.imported AS importovano from
((import.zbozi AS t1 left join import.vyrobci AS t2
on((t1.vyrobci_id = t2.vyrobci_id))) left join import.dodavatele AS t3
on((t1.dodavatele_id = t3.dodavatele_id))

Dotaz sice vypadá trochu komplikovaně, nicméně je jednoduchý, ale díky délce mírně klame tělem, je to obyčejný JOIN přes 3 tabulky.

Použití pohledu je o něco čistší a nepoměrně rychlejší. Pohled si připravíte podobně jako předchozí dotaz, jen před ten dotaz uvedete sekvenci CREATE VIEW nazev_pohledu AS …:

CREATE VIEW zbozi2 AS SELECT t1.kod AS kod, t1.nazev AS nazev,
t2.vyrobce AS vyrobce, t2.vltava AS nomenklatura, t3.nazev AS dodavatel,
t1.mnozstvi AS mnozstvi, t1.nakup AS nakup, t1.imported AS importovano from
((import.zbozi AS t1 left join import.vyrobci AS t2
on((t1.vyrobci_id = t2.vyrobci_id))) left join import.dodavatele AS t3
on((t1.dodavatele_id = t3.dodavatele_id))

Výše uvedený příkaz vytvoří jakousi virtuální tabulku, ale protože je dotaz tohoto příkazu uložený na databázi a plně zanalyzovaný, je výběr z pohledu nepoměrně rychlejší, než kdybyste data vybírali ze 3 tabulek spojovaných pokaždé JOINem, pochopitelně pohled můžete vytvořit z kolika tabulek chcete a nemusí v něm být jen takto triviální vyjmenování sloupců, ale třeba i hodnoty vypočítané ze sloupců.

Pokud budete chtít získat data z pohledu, funguje naprosto normální příkaz SELECT * FROM jmeno_pohledu, pochopitelně že včetně omezení a výběru sloupců, které aktuálně potřebujete. Co jsem nezkoušel a zkoušet nebudu je INSERT, UPDATE, nebo DELETE dat v pohledu předpokládám, že by se to nemuselo chovat rozumně, ani nevím, jestli tyto operace MySQL dovoluje, vím že některé konkurenční servery ano.

PhpMyAdmin se na pohledy tváří malinko podivně, v přehledu databáze ani neuvidíte, kolik má řádků. Pohled zabírá na disku téměř nulové místo i v porovnání s tabulkami, ze kterých je sestaven, pokud tyto obsahují jen jednotky řádků, samozřejmě při velkých tabulkách obsazuje pohled defakto nulový prostor. Pohledy používám v databázích zcela běžně, protože je levnější a potenciálně výhodnější využívat takovéto optimalizace, než řešit extrémně rychle bobtnající databázi posilováním hardware.

12 komentářů u „MySQL: Opomenutá vlastnost – pohledy“

  1. Hmm, tak tady nevím nevím. Ne, že bych nebyl přesvědčen o výhodnosti pohledů, ale alespoň podle ohlasů z netu jejich implementace pokulhává – MySQL nedobře optimalizuje subqueries, což se pak projevuje i na při použití pohledů (což je už vyřešené v 6ce). Docela by mne překvapilo, kdyby přístup do pohledu měl být rychlejší než přístup do tabulky – můžeš zveřejnit nějaká čísla. Při dnešní rychlosti procesorů generování plánu zabere minimum času – hrdlo je čtení z disku. Něco jiného by bylo použití materializovaných pohledů, ty ovšem MySQL zatím nepodporuje. Jsi první, kdo si pochvaluje rychlost pohledů v MySQL. Ve všech doporučeních, co jsem našel na netu se zatím doporučovalo pohledy a poddotazy nepoužívat – je ale možné, že to už je to v MySQL opravené – je fakt, že MySQL nesleduji.

  2. Pavel Stěhule: Z principu by měl být pohled rychlejší, pro mě je vždy rychlejší na programování, protože než psát pokaždé skložené dotazy, ať JOINy, nebo subSELECTy, je příjemnější dělat nad virtuální tabulkou. Pokud jsem viděl dobře, tak phpMyAdmin tvrdil při výběru téhož o 10 ms kratší čas při pohledu, než při JOINech, ale jak říkám, je tam málo dat, jen cca 10 000 záznamů v té hlavní tabulce.

  3. Z tvého blogu není zřejmé, kterou rychlost myslíš – rychlost psaní (implementace) bych spíš než rychlost označil jako vyšší produktivitu a hlavně pohledy nepochybně přispívají k čitelnosti a přehlednějšímu uspořádání aplikace – jen u MySQL je dobré testovat rychlost provedení.

  4. Pavel Stěhule: Rychlejší by měly logicky být ve výběrech dat, jestli tomu tak není, mají implementační chybu, předpokládám, že hlášenou v Bugzille. Z programátorského hlediska je zpříjemnění jasné, samozřejmě, že jsem opomenul možnost pohledy definovat pro uživatele, ke čtení, jimž nechci zpřístupňovat původní tabulky jako takové.

  5. Proč by měly být rychlejší? Generování plánu trvá max 1ms. Tj +/- nic – Kde vidíš další zrychlení? Co je ale horší – díky horšímu planneru se ti může stát, že získáš neoptimální plán (a tam pak může být rozdíl už v sek). Jinak pomalé poddotazy jsou v Bugzille už dávno – od jejich implementace v 4.1

  6. Možná chápu blbě princip databází, ale pokud je něco připravené na serveru, tak by to mělo být rychlejší, než když pošlu dlouhý dotaz. Řekl bych, že můj příklad je nevhodně zvolený, přeci můžu mít pohled sestavený jen pro akce za poslední týden, nebo pro id obchodníků, protože pomocí WHERE mohu ovlivnit, jaká data v pohledu chci, moc nevěřím tomu, že pak neušetřím čekání aplikace na reakci databáze. Neměl bys čas získávat i na konverzi typů, protože při JOINech, subSELECTech konvertuješ podstatně víc parametrů? Jo je to optimalizace o minimech, ale pokud budu přemýšlet takto, tak příjdu na to, že z celé databáze budou stačit jen prosté tabulky, indexy a transakce, zbytek je zbytečný komfort.

  7. Kupodivu, když jsem výběr z toho pohledu dělal, tak monitor v GUI nástrojích MySQL nevyskakoval tak vysoko, jako při složeném dotazu. Spíše jsem přesvědčen, že i z výkonostního hlediska pohledy význam mají.

  8. Hm – řekl bych, že úplně nechápeš, co znamená prepared statement – to se týká jen prováděcích plánů. Vůbec to nemá žádný vliv na data – data rozhodně nejsou připravená. Něco jiného jsou materializované pohledy – tam k úspoře dochází, nicméně ty MySQL nepodporuje.

  9. Pohledy bych si také rád vyzkoušel a srovnal s běžným dotazem. Ale mám zatím jen MySQL verzi 5.0 a neupgradoval jsem. Takže zatím nic 🙂 Jinak moje tabulky jsou velmi podobné tomu příkladu v článku

  10. Dalibor: MySQL 5.0 by pohledy již umět měla, jedu na tomtéž, jen pár setinek za tou nulou. Po článku jsem to zkusil trochu proměřit, zrychlení dolování dat téměř neznatelné, takže „jen“ zpříjemnění vývoje a náhledů na data.

  11. Já chtěl použít pohled ke sloučení 2 tabulek, abych nemusel dělat select s UNION, ale bohužel pohled je o celou polovinu pomalejší. Mám 2 tabulky – Klienti (27500 řádků), Klienti_pracovni (14 řádků). Pokud udělám dotaz s UNION, tak výběr 30 řádků trvá v průměru kolem 2 vteřin. Pokud však udělám výběr z pohledu, kde je ten samý dotaz, co používám při výběru, tak se pod 3 vteřiny nedostanu, většinou je to kolem 3,3 vteřiny.
    Chtěl jsem proto vyzkoušet tabulku typu MERGE, ale s tím mám pořád problém, tak uvidíme, jestli se najde optimálnější řešení.

    1. @Honza: Toto vypadá spíše na špatnou indexaci, ani kartézský součin těch tabulek není velká množina. Pohrejte si s EXPLAINEM a zjistěte, jaké indexy potřebujete pro spojení těch tabulek. Tipuji, že chyba návrhu struktury bude mnohem výraznější, než je zpomalení pohledů, které dělá na pozadí JOIN/UNION. EXPLAINujte si i dotaz, kterým tvoříte pohled. MERGE tabulky je něco úplně jiného, tím se řeší zcela jiný problém, než UNIONem/JOIN.

Komentáře jsou uzavřeny.