SQL, které zboří každý server

Kamarád technik poněkud nevybíravě komentoval práci webových programátorů, kteří podle jeho slov neumí řádně optimalizovat SQL, pokud jsou schopni shodit databázový server. Nebudu hodnotit jejich schopnosti, jen jsem mu chtěl ukázat, že není žádný problém napsat (i třeba omylem) SQL dotaz tak, že to opravdu neustojí (i když to je trochu přehnané) žádný DB stroj.

Pro ukázku mějme dvě malé tabulky a a b. Každá obsahuje 3 záznamy:

tomas=> select * from a;
 1
 2
 3

tomas=> select * from b;
 a
 b
 c

Velmi jednoduchým dotazem (spojením bez omezení – kartézkým součinem), dosáhneme velmi lehce vysokého počtu hodnot (počet hodnot v tabulce a vynásobený počtem hodnot v tabulce b). V tomto ukázkovém případě dosáhneme počtu hodnot 3×3 = 9. Opět pro ukázku výsledek kartézkého součinu (9 záznamů se sem ještě vleze):

tomas=> select * from a,b;
 1 | a
 1 | b
 1 | c
 2 | a
 2 | b
 2 | c
 3 | a
 3 | b
 3 | c

Asi každého napadne podstata daného problému. Můžeme použít tabulky (ve složitějším dotazu to jsou pracovní temporary tabulky) o stále malém počtu záznamů (dejme tomu 1000), kdy jejich spojením vznikne jedna tabulka o milionu záznamů, v případě spojení tří tabulek už to bude miliarda záznamů atd.

Takový počet už se určitě nevleze do paměti a s vysokou pravděpodobností ani na disk.

tomas=> select count(*) from c;
 1000

tomas=> select count(*) from d;
 1000

tomas=> select count(*) from e;
 1000

Každá z těchto tabulek má 40kB:

 public | c    | table | tomas | 40 kB      |
 public | d    | table | tomas | 40 kB      |
 public | e    | table | tomas | 40 kB      |

Spojením dvou tabulek vznikne výsledná tabulka o velikosti 35MB, tedy přibližně 1000x větší:

insert into cd select * from c,d;

public | cd  | table | tomas | 35 MB      |

Teď už není těžké uhodnout, jakou velikost by měla výsledná tabulka vzniklá spojením všech tří tabulek. Milirda záznamů, odhadovaná velikost 35GB. Pojďme to vyzkoušet :-D

insert into cde select * from c,d,e;

public | cde  | table | tomas | 41 GB      |

Zdrojové tabulky ani nemusejí být na disku, je možné je vyrobit nějakou funkcí.

Pro DB specialisty to není nic nového ani překvapivého. Berte to tedy jako doklad nutnosti testování každého DB dotazu na reálných datech (na pár záznamech to není nic, s čím by si DB server neporadil, ale produkční data už to mohou spolehlivě zahltit) a jeho konzultace pomocí EXPLAIN. Také to nemusí být takto explicitní použití hovotých tabulek. Kartézký součin bez omezení se může najít v nějakém komplikovaném dotazu, jehož výstupem, je několik záznamů, ale DB bude při zpracování tohoto dotazu nucena vytvořit temporary tabulku o velkém objemu).