MySQL - bindovat či nebindovat?

Předpokládám, že většina z vás už někdy slyšela o takzvaných prepared statements. Je to určitý způsob práce s SQL dotazy, kdy si neprve sestavíme dotaz, aniž bychom do něj dosazovali konkrétní hodnoty (data). Následně dotaz necháme zparsovat DBMS a teprve pak připojíme (nabindujeme) do dotazu konkrétní hodnoty.

$stmt = $mysqli->prepare("SELECT `name` FROM `users` WHERE `id` = ?");
$id = 12;
$stmt->bind_param("i", $id);
$stmt->execute();
$name = '';
$stmt->bind_result($name);
while($s->fetch());
echo $name;

Výhody

Bindování s sebou přináší několik výhod.

  • Bezpečnost
    Protože jsme oddělili vlastní dotaz od dat (která většinou pocházejí od uživatele) a zároveň při předání dat DBMS přesně specifikujeme, co přesně předáváme (celé číslo, řetězec,...), zcela odpadá nebezpečí SQL injection. O předáváná data se nemusíme nijak starat, nemusíme je nijak slashovat.
  • Rychlost
    Pokud musíme provést vícekrát stejný dotaz s různými hodnotami, můžeme dotaz nechat zparsovat jen jednou a pak už do něj pouze dosazovat různé hodnoty. Teoreticky by měl následovat znatelný nárůst výkonu.

Výkon

Při měření výkonu se dostavilo zklamání. Při použití bindování u vědomě opakovaného dotazu (parsování dotazu můžeme předřadit cyklu) je výkon jen o málo vyšší než ten samý dotaz bez bindování (parsuje se při každém průchodu cyklem). Při použití hloupého bindování - bindování u nevědomě opakovaného dotazu (stejný dotaz použit na různých místech v rozsáhlém skriptu - parsování dotazu tedy pokaždé opakujeme) je výkon mnohem horší než při nebindování. Pokud navíc použijeme Query cache (defaultně zapnuto), ztrácí z pohledu výkonu bindování zcela smysl. Mysql zřejmě při parsování nového prepared statements předchozí zpracované prepared statements ignoruje a po nabindování dat do dotazu se nedívá, jestli už tento dotaz není v query cache, čímž se výrazně ztrácí výkon.

Metoda 1. test (ms) 2. test (ms)
S bindováním 161 1528
S hloupým bindováním 844 2630
Bez bindování 180 1655
Bez bindování s query cache 65 61

Zdrojové soubory

Metodika: Testováno nástrojem Apache jMeter (10 vláken, každé 20 GET dotazů).
Testovací stroj: Intel Pentium D 805, 2GB RAM, Ubuntu 8.04
Query cache vypnuta příkazem SET GLOBAL query_cache_size = 0;

Závěr

Z testu jasně vyplývá, že používat prepared statements a bindování dat do dotazů má smysl pouze z hlediska bezpečnosti. Z pohledu výkonu můžeme dosáhnout lepších výsledků pouze pokud voláme jeden stejný dotaz se stovkami tisíc různých hodnot. V opačném případě bude výhodnější spolehnout se na query cache.

Hodnocení

Komentáře

2008-08-13 21:11:59

Zcela souhlasím (viz odstavec začínající slovy "Prepared statements" zde http://phpfashion.com/dibi-pokrokovy-databazovy-layer). Navíc situaci, kdy bych volal stejný dotaz se stovkami tisíc různých hodnot, jsem v praxi nezažil - ani na firemním serveru, natož na webu. Teda kromě importu dump souboru - jenže i tady platí, že prepared statements ho zrychlí v řádu procent, zatímco *insert multiple rows* v MySQL řádově.

Na tento komentář odpověděl [2] Dundee
[2] Dundee
2008-08-13 23:58:46

#1 David Grudl: Díky :)

Mám z toho pocit, že je to tak trochu chyba vývojářů MySQL. Že myšlenka prepared statements není sama o sobě vůbec špatná. Nemám to podložené, ale vypadá to, že se DBMS opravdu vůbec nedívá, jestli už tentýž dotaz jednou nepředzpracovával (proto tak pomalé hloupé bindování).

A navíc je celkem očividné, že při použití prepared statements DBMS nepoužívá query cache. Nevidím důvod proč.

Pokud by obě tyto chyby vývojáři odstranili, začal bych o používání této techniky vážně uvažovat.

[3] spaze
2008-09-28 00:54:55

Je to už nějakej ten pátek, co to zmiňoval Wez Furlong na http://netevil.org/blog/2006/apr/using-pdo-mysql a evidentně místo tohoto klucí u MySQL raději udělají další storage engine.

[4] spaze
2008-09-28 00:55:35

Uh, link měl být takto: http://netevil.org/blog/2006/apr/using-pdo-mysql, sorry.

Komentáře již nelze přidávat