Srovnání funkcí MySQL a PostgreSQL
Školení, která pořádám 
Webové aplikace jsem začal vyvíjet s PostgreSQL, pak jsem kvůli výkonnosti přešel na MySQL. Bylo to ale v dřevních dobách a třeba jsem jen PostgreSQL nedokázal správně nakonfigurovat. Z MySQL jsem myslím schopen vytáhnout slušný výkon a expert na PostgreSQL by jistě dokázal totéž i s ním. Na různé úlohy navíc může být vhodná jiná databáze, o rychlosti proto psát nechci.
Zajímá mě srovnání funkčních vlastností. MySQL má řada lidí zaškatulkovanou jako primitivní úložiště, to už ale díky verzi 5 neplatí. Takže připomenu, že transakce, cizí klíče (s tabulkami InnoDB, které používám), poddotazy, pohledy, triggery a uložené procedury už MySQL dlouhou dobu podporuje. Některé věci třeba ve srovnání s PostgreSQL s některými omezeními, ta ale reálnému využití nebrání. Co se mi tedy líbí na které databázi?
MySQL
- Vynikající podpora kódování. Pro každou databázi, tabulku a sloupec lze určit nezávislé kódování a způsob porovnávání, komunikovat z databází lze v kódování nezávislém na datech.
- Šikovná agregační funkce GROUP_CONCAT.
- Klauzule ON DUPLICATE KEY UPDATE, která dovoluje snadno pracovat s unikátními číselníky nebo aktualizovat statistické tabulky.
- Hodit se může i podpora fulltextového vyhledávání, ta je ale k dispozici bohužel jen v tabulkách typu MyISAM (v PostgreSQL je od verze 8.3).
- Pro pevně dané výčty je v MySQL k dispozici datový typ enum, který je velmi úsporný a efektivní, přitom se s ním pohodlně pracuje. V ostatních databázích se stejného chování dosahuje klauzulí CHECK u řetězcového sloupce, to ale není tak úsporné a efektivní. PostgreSQL podporuje enum od verze 8.3.
- Velmi využívaná je replikace, vždyť je v MySQL už od verze 3.23. PostgreSQL zavádí replikace až ve verzi 8.3, MySQL ale replikaci stále zdokonaluje.
- Líbí se mi, jak v InnoDB tabulkách interně fungují transakce. Ty jsou optimistické a data zapisují rovnou na konečné místo a vedle toho si vedou rollback log. Když se transakce potvrdí, tak tento log jen smažou, do té doby ho využívají ostatní transakce pro přístup ke staré verzi dat. PostgreSQL to řeší jinak a je potřeba v něm pravidelně spouštět příkaz VACUUM. To se dá v nových verzích řešit i automaticky, ale přístup InnoDB se mi líbí víc.
- Umělé primární klíče se snadno vytváří příznakem AUTO_INCREMENT. V PostgreSQL je nutné používat sekvence, případně zkratku serial.
- Modifikátor SQL_CALC_FOUND_ROWS dovoluje zjistit počet všech řádek nezávisle na omezení klauzulí LIMIT.
PostgreSQL
- Na Postgresu mě nadchl sloupec oid, pomocí kterého lze snadno pracovat se záznamem v libovolné tabulce. Třeba v Admineru se mi velmi obtížně pracuje s tabulkami s vícesloupcovým primárním klíčem nebo úplně bez něj. Pracovat s takovýmito tabulkami jde, ale se sloupcem oid by to bylo jednodušší.
- Mnohem propracovanější jsou v PostgreSQL indexy. Je možné nadefinovat funkční a částečné indexy. Většinou se bez nich dá žít, ale některé úlohy mohou zjednodušit.
- Líbí se mi datový typ pole. Sice jeho existence může někoho svádět ke špatnému návrhu databáze, ale věřím, že bych ho dokázal využít smysluplně.
- Pokud dojde v některém příkazu v rámci transakce k chybě, tak se další příkazy neprovedou. To může být někdy na překážku, většinou to je ale žádoucí chování.
- V uložených procedurách se dají bez hacků používat různé programovací jazyky. V MySQL jen s hacky.
- Někdo může ocenit, že tabulky v rámci jedné databáze lze sdružit do schémat. Osobně mi vyhovuje spíš plochá struktura, takže sám vytvářím spíš více menších databází.
- Podpora pravidel, pomocí kterých lze vytvářet virtuální tabulky.
Závěr
Myslím, že co se funkčnosti týče, tak jsou MySQL a PostgreSQL srovnatelné databáze. Mě se víc líbí vychytávky MySQL, ale s PostgreSQL bych pracoval také bez velkého přemáhání. Druhou oblastí je výkonnost a správa, do srovnání toho se ale pouštět nechci.
Přes PostgreSQL nejsem žádný expert, takže budu rád, když další postřehy doplníte do diskuse, já bych je pak případně zohlednil i v článku. Stejně tak jsem mohl zapomenout na nějakou unikátní vlastnost MySQL.
Diskuse
Pavel:
V mysql se mi špatně pracuje s pohledy.Jakmile vytvořím pohled používající agregační funkci, tak jednoduchý select z něj trvá násobně déle, než když ručně použiji selecty z pohledu (např. sekundy vs milisekundy). Vývojáři toto chování označili za vlastnost (algoritmus MERGE vs TEMPTABLE) a nehodlají s tím nic dělat.
Trpí Postgresql stejnou "vlastností" nebo si s tím poradí lépe?
Mám web, kde potřebuji v návratu mít něco jako "SUM(..)>SUM(..) as je_vetsi", přičemž ty sumy jsou dalšími sloupečky odpovědi. Protože ale nelze zaručit pořadí vyhodnocení, řešil jsem to vytvořením pohledu a z něj to beru selectem, ve kterém navíc přidávám tyto testy. Bohužel jsem díky hloupému chování mySQL narazil.
Pavel Stehule:
V PostgreSQL neni rozdil mezi primym SELECTem a dotazem skrz pohled. Pri zpracovani dotazu se odkaz na pohled nahradi svou definici. Teprve pote nastupuji optimalizace.
CREATE VIEW x AS SELECT * FROM tab;
SELECT * FROM x je ekvivalentni SELECT * FROM (SELECT * FROM tab)
Pavel Stehule: 
Jakub Vrána 
:
V MySQL to kupodivu neplatí:
SELECT * FROM (SELECT * FROM obce) AS pohled ORDER BY okres;
musí výsledek dotřídit, ale
CREATE OR REPLACE VIEW pohled AS SELECT * FROM obce;
SELECT * FROM pohled ORDER BY okres;
dokáže využít index nad sloupcem okres.
Jakub Vrána : Messa:
Malá poznámka k oid (v PostgreSQL) - v dokumentaci se doporučuje používat oid jen pro přístup do systémových tabulek; protože je jen 32bitové, nemusí být ve velkých databázích nebo tabulkách unikátní. Co bych v PostgreSQL oproti MySQL vyzdvihl jsou sekvence. Jakub Vrána :
Sekvence se dají v MySQL snadno emulovat: http://php.vrana.cz/sekvence-v-mysql.php.
Jakub Vrána : Jakub Podhorský:
postgres má enum od verze 8.3 takyad transakce: postgres využívá multigenerační architektury zatímco mysql transakčního logu což se nedá dost dobře porovnávat každé má své pro a proti
co se mně osobně třeba na postgresu líbí je že pro uživatelské funkce mohu využít víc jazyků (pl/pgsql, pl/perl, pl/php atd.)
na mysql mně hodně chybí podpora schémat
nevím jak je to teď ale v dřívějších verzích mysql nešly ukončit before triggery aniž by se něco nezapsalo do tabulky
Jakub Vrána :
Díky za postřehy, doplnil jsem to do článku. Co se transakcí týče, takže samozřejmě každá implementace má své pro i proti, mě je prostě sympatičtější přístup InnoDB.
Jakub Vrána : Ondrej Ivanic:
Toto su veci co mam rad na postgre:- je konstantne pomaly
- funkcie mozu vracat resultset co sa napr. hodi na generovanie postupnosti alebo ako v tomto priklade na zobrazenie posledneho mesiaca (aj ked v tabulke chybaju data v niektory den):
SELECT *
FROM (
SELECT NOW()::date - s.a AS dt
FROM generate_series(0, NOW()::date - INTERVAL '1 month') AS s(a)
) AS time_axis
LEFT JOIN (
...
) AS data USING (dt)
- moznost vytvorenia vlastnych typov (napr typ pre PSC, rodne cislo, ... co umoznuje pisat robustnejsie aplikacie)
- absenicia funkcii DATE_ADD/DATE_SUB (pg ma koncept typov a operatorov)
- vlastne typy, operatory a check podmienky na stplpcoch dokazu urobit dost blbovzdornu databazovu vrstvu.
- partitioning (sice ma este muchy, ale je pouzitelny)
- obcas mi vadi ze je dost casto treba pouzivat CAST() (alebo ::<typ>)
- dedicnost v tabulkach (aj partitioning je zalozny na dedicnosti)
ad VACUUM: to nie je len o tranzakciach, ale aj o updatovani statistik ktore su dolezite pre planovac
ad ENUM: z mojho pohladu asi najproblemovejsi typ a ked je moznost rad sa ho zbavim
Jakub Vrána :
Díky za postřehy.
Vlastní datové typy mohou být skutečně užitečné.
Partitioning přináší i MySQL 5.1.
ENUM má tu výhodu, že obsahuje identifikátor, na který se v aplikaci můžu spolehnout. Takže v aplikaci můžu psát WHERE stav = 'zaplacené' a ne třeba WHERE stav = 1, kde 1 je nějaká magická hodnota (nejspíš definovaná jako konstanta), u které doufám, že se v databázi nikdy nezmění. Ale ENUM se musí používat skutečně jen u pevně daných výčtů, jejichž změna by vedla i ke změně logiky aplikace. V jiných případech je samozřejmě lepší použít normální číselník.
Jakub Vrána : Ondrej Ivanic:
Ano, enum je tak trochu samo dokumentujuci, ale urobit alter ktory prida novu hodnotu do enumu na tabulke ktora prerastla na 500mil zanamov nie je vobec sranda.Zazil som aj databazy kde hodnoty enumu boli v slovencine s diakritikov co je este v pohode, ale dostalo ma ked boli v portugalstine a to od magickej hodnoty uz daleko nema.
Zvacsa dam otazku ze kolko zmien sa ocakava najblizsi rok a zadim sa podla odpovede:
ziadne zmeny -> asi 2-5, prva bude hned po uvedeni do produkcie
tak 1 za stvrt rok -> 1 mesacne
tak 1 mesacne -> zmeny budu caste
Yaroukh:
> WHERE stav = 'zaplacené'No vidíš, a já si zase myslím, že tohle je čuňárna.
david@grudl.com:
Bohužel tak trošku skutečně je, protože MySQL na chybu jako třeba WHERE stav = 'zaplacné' neupozorní. Ve spolupráci s dibi to řeším tak, že napíšu
WHERE stav=:Order.PAID:
a Order::PAID je konstanta, která musí existovat. Výhoda je ta, že se stejné konstanty dají použít i při následném zpracování, např.
<?php
if ($row->stav === Order::PAID) ...
?>
david@grudl.com: fos4:
Snad se nepletu, ale u MySQL vlastnost AUTO_INCREMENT muze byt pouze u primarniho klice.krteQ:
Na MySQL mi chybi transakce pro DDL. Napriklad nejde konzistentne upravit procedura/trigger, vzdy je maly okamzik, kdy v databazi chybi, nez se ulozi novy kod. O slozitejsich operacich nemluve, k dispozici je jen RENAME TABLE.Take pokud chcete efektivne a bezpecne zalohovat, je mix ruznych storage enginu na MySQL spis na obtiz nez k uzitku (logicka zaloha, binarni je celkem bez problemu).
Pavel Stehule:
U PostgreSQL lze agregovat do pole - což je univerzálnější než GROUP_CONCAT v MySQL. PostgreSQL má víc nástrojů pro sofistikovanější databázově orientované aplikace - ať už jsou to aplikační zámky, prostředí pro uložené procedury s docela hodně dobrou diagnostikou chyb - a hlavně možnost doplňování funkcionality. V provozu je pak unikátní transakčnost DDL příkazů.
MySQL je rychlejší pro typicky webové záležitosti a hlavně má vestavěnou replikaci.
Pavel Stehule: Jakub Vrána :
Možnost agregace do pole je výborná vlastnost, proti té je GROUP_CONCAT samozřejmě chudý příbuzný. Díky za tip, o tom jsem nevěděl.
Aplikačními zámky myslíš něco chytřejšího než jsou v MySQL funkce GET_LOCK a RELEASE_LOCK?
Jakub Vrána : Pavel Stehule:
Pole jsou super - http://www.postgres.cz/index.php/SQL_Triky#…_medi.C3.A1nu
Pavel Stehule: Václav Vaník:
Na PG se mi kromě výše uvedených líbí:
a) RULES
b) taková milá drobnost, že při cascade mazání se volají delete triggery v závislé tabuli (narozdíl od innodb)
A.:
Hlavní důvod, proč jsem zvolil postgresql byla již zmíněná absence fulltextu v inoodb a problémech spojených s mícháním typů myisam a innodb (někde se mohu spoléhat na cizí klíče, jindy ne, není to prostě pohodlné).
Bohužel ALE. Přišlo velké množství dat a s ním i problémy s výkoností a v tu chvíli jsem si trochu začal PostgreSQL vyčítat. Spoustu času se optimalizovalo, vymýšlel preprocessing atd, ale výsledek mi stále nepřijde ideální a mám pocit, že postgre prostě můj konkrétní návrh nedává (ikdyž ramka trochu pomohla :).
Například s čím jsem se setkal, že s větším počtem joinu šel výkon rapidně dolů (a je zajímavé, že ten počet byl fixní per dotaz) - ať jsem pak odebral jakýkoliv join, čas dotazu byl zase v normě.
A za odkaz také stojí: http://wiki.postgresql.org/wiki/Slow_Counting
Naopak s mysql jsem se většími daty setkal při práci s databázi čísel popisných v čr a výkonost byla bezproblémová. Avšak zde byly dotazy vcelku triviální, takže to není až takové srovnání :-/.
Docela bych rád od někoho slyšel nějaké zkušenosti s velkým objemem dat v postresql.
Ondrej Ivanic:
Ano, to som si vsimol aj ja. Ak ma su tabulky v hviezde tak je castokrat lepsie urobit:SELECT ... FROM (
SELECT key, ... WHERE ...
) AS main_t
LEFT JOIN t2 ON main_t.key = t2.key ...
LEFT JOIN t3 ON main_t.key = t3.key ...
...
co dava lepsie vysledky nez povodny:
SELECT ... FROM main_table
LEFT JOIN t2 ON main_t.key = t2.key ...
LEFT JOIN t3 ON main_t.key = t3.key ...
WHERE ...
Niekedy mam pocit ze PG najde vsetko mozne len nie optimalny plan a vobec netusim ze aku mu v tom pomoct :(
Pavel Stehule:
PostgreSQL má dva algoritmy pro optimalizaci - jeden klasický, a druhý heuristický (založený na GA). Přepnutí mezi prvním a druhým se děje v závislosti na počtu joinů. Navíc od určitého počtu joinů se vynechávají některé transformace. Na heuristický algoritmus si lidé občas stěžují. Zkuste zvýšit systémové proměnné geqo_threshold, join_collapse_limit a from_collapse_limit
Další informace se můžete dozvědět na http://www.nic.cz/akademie/course/16/detail/
pozn. PostgreSQL nemá žádnou speciální optimalizaci pro star schéma. Takže pokud pracujete s tímto schématem, pak jsou potřeba některé špinavé triky.
Pavel Stehule: Ondrej Ivanic:
Dik za link, podstate som sa k niecomu podobnemu aj dopracoval. Ono to bol sko taky logicky zaver ze je potrebne obmedzit result set z hlavnej tabulky - EXPLAIN \e bolo vidiet ze to pg joinuje spolu (horny odhad poctu riadkov bol cez dve miliardy)Navyse este pouzivame partitioning kroremu tiez treba zopar spinavych trikov aby to bolo pouzitelne.
Jakub Vrána :
Já fulltext řeším tak, že si z InnoDB tabulek kopíruji prohledávatelné texty do MyISAM tabulek pomocí triggeru (a aktualizuji a mažu) a při vyhledávání potom používám tu.
Jakub Vrána : Luděk:
A co srovnání licencí?Kolem licence MySQL jsem toho nacetl dost, ale popravde si nejsem jisty, za jakych podminek ji musim/nemusim koupit.
Kajman:
U mysql je nepříjemné, že se člověk musí vyvarovat poddotazům (v operátoru in atp.) a přepsat vše na joiny. Plánovač jinak ten poddotaz provádí pro každý řádek znovu (i když tam nejsou podmínky se závislostí). Ne, že by to nešlo, ale někdy se tím zmenšuje přehlednost. Pavel Stehule:
To by snad melo byt v 5.4 vyresene.
Pavel Stehule: fos4:
Neni tak uplne pravda, v nekterych pripadech to vychazi i mnohem lepe nez joiny nebo to jinak nejde udelat.Napr. tento dotaz se nebude vyhodnocovat pro kazdy radek zvlast:
SELECT *
FROM clanky
WHERE clanky.skupina IN (
SELECT skupiny.id
FROM skupiny
WHERE skupiny.typ = '...')
Prdlořeznictví Krkovička, n. p.:
1) U stránkování se mi u Mysql líbí klíčové slovo SQL_CALC_FOUND_ROWS, na druhou stranu ne fakt, že není ve standardu SQL. Jak to má PostgreSQL?2) U Mysql mě ještě napadá konstrukce WITH ROLLUP pro agregované dotazy. Ale mám takový pocit, že jsme ji nikdy nepoužili.
Jakub Vrána :
Díky za připomenutí SQL_CALC_FOUND_ROWS, doplnil jsem to do článku. WITH ROLLUP se opravdu nepoužívá.
Jakub Vrána : Pavel Stehule:
V PostgreSQL se dá podobná hodnota zjistit z explainu. WITH ROLLUP je implementace MSSQL grouping sets. Je to docela šikovné - v ANSI implementaci - hrál jsem si s tím, když jsem připravoval implementaci pro PostgreSQL - http://wiki.postgresql.org/wiki/Grouping_Sets
Pavel Stehule: Kajman:
Ještě se v mysql dotazech dají použít uživatelské proměnné - ale to je spíše vynucené tím, jak je mysql omezená :-) v postgresql lze tuším ladněji udělat na podobné filtrování funkci, co vrátí výsledné řádky.select k.*, @n:=@n*(@last_user=`user`)+1 n, @last_user:=`user` u
from `tabulka` k, (select @n:=0, @last_user:='') t
having n <=5
order by `user`, `body` desc
Ondřej Bouda:
MySQL, narozdíl od jiných DBMS, neumožňuje v těle triggeru modifikovat obsah tabulky, nad kterou je tento trigger spouštěn. Umožňuje pouze měnit vkládaný řádek, ne ostatní řádky.Tento nedostatek se málokde zmiňuje. Ostatně jako víc podobných gotchas, o kterých se člověk z manuálu nedoví a narazí, až když to potřebuje a polovinu aplikace má již napsanou. Až si vzpomenu na další, zase napíšu :)
Jakub Vrána :
Ano, jde o známý nedostatek MySQL.
V manuálu je jasně popsán spolu s ostatními omezeními: "Within a stored function or trigger, it is not permitted to modify a table that is already being used" - http://dev.mysql.com/doc/refman/5.1/en/stor…-restrictions.html. Tato stránka je odkazovaná i z dokumentace CREATE TRIGGER.
Jakub Vrána : Diskuse je zrušena z důvodu spamu.
© 2005-2024 