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

PostgreSQL

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.

Jakub Vrána, Seznámení s oblastí, 17.6.2009, diskuse: 35 (nové: 0)

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.

ikona 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)

ikona Jakub Vrána OpenID:

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.

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.

ikona Jakub Vrána OpenID:

Sekvence se dají v MySQL snadno emulovat: http://php.vrana.cz/sekvence-v-mysql.php.

Jakub Podhorský:

postgres má enum od verze 8.3 taky

ad 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

ikona Jakub Vrána OpenID:

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.

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

ikona Jakub Vrána OpenID:

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.

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.

ikona 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) ...

?>

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).

ikona 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.

ikona Jakub Vrána OpenID:

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?

ikona Pavel Stehule:

je to obdoba - http://www.postgresql.org/docs/8.3/interactive/…-ADVISORY-LOCKS

ikona Pavel Stehule:

Pole jsou super - http://www.postgres.cz/index.php/SQL_Triky#…_medi.C3.A1nu

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 :(

ikona 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.

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.

ikona Jakub Vrána OpenID:

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.

ikona Pavel Stehule:

viz http://www.postgres.cz/index.php/Optimalizace_…%C3%A9matem

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.

ikona Pavel Stehule:

To by snad melo byt v 5.4 vyresene.

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.

ikona Jakub Vrána OpenID:

Díky za připomenutí SQL_CALC_FOUND_ROWS, doplnil jsem to do článku. WITH ROLLUP se opravdu nepoužívá.

ikona 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

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 :)

ikona Jakub Vrána OpenID:

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.

Vložit komentář

Používejte diakritiku. Vstup se chápe jako čistý text, ale URL budou převedeny na odkazy a PHP kód uzavřený do <?php ?> bude zvýrazněn. Pokud máte dotaz, který nesouvisí s článkem, zkuste raději diskusi o PHP, zde se odpovědi pravděpodobně nedočkáte.

Jméno: URL:

avatar © 2005-2018 Jakub Vrána. Publikované texty můžete přetiskovat pouze se svolením autora. Ukázky kódu smíte používat s uvedením autora a URL tohoto webu bez dalších omezení Creative Commons. Můžeme si tykat. Skripty předpokládají nastavení: magic_quotes_gpc=Off, magic_quotes_runtime=Off, error_reporting=E_ALL & ~E_NOTICE a očekávají předchozí zavolání mysql_set_charset. Skripty by měly být funkční v PHP >= 4.3 a PHP >= 5.0.