MENU
SVG Modal background Created with Snap
Close
Napište nám

Pekelné MySQL OFFSETy

Když jsme ve firmě implementovali novou exportovací logiku příjemců do Maileru, všimli jsme si celkem zajímavého, avšak logického výkonnostního problému u MySQL s OFFSETem.

Představme si situaci: Máme tabulku, kde je kolem 100 000 příjemců a chceme ji třeba v PHP někam exportovat. Udělat SELECT na celou tabulku by byla pochopitelně sebevražda, budeme to dělat tedy dávkově. Dávku si zvolíme třeba na 1000 příjemců. Proběhne nám tam tedy 100 dávek pro získání všech příjemců. Pokud se provedou všechny SELECTy pomocí OFFSETů, je vidět postupné zpomalování dotazu.

Ukázka na zjednodušeném SQL dotazu:

SELECT id FROM recipients ORDER BY id LIMIT 1000;

Dotaz zabere kolem 10 ms na našem stroji. Bez problému.

A co když běží třeba 80. dávka? 80 × 1000, to máme OFFSET 80000:

SELECT id FROM recipients ORDER BY id LIMIT 1000 OFFSET 80000;

Dotaz trval 662 ms?! No tak to teda ne. A to ještě před touto dávkou muselo běžet dalších 79 dávek s postupně se zhoršující rychlostí. Takže když se to zprůměruje, do 80. dávky zabrala jen práce s databází 331 × 80 ms? Celkem více jak 26 vteřin naprosto promrhaného výpočetního času! Tudy ceste nevede.

Proč se tak děje? OFFSET nemůže jednodušše “skočit” na danou pozici v databázi, jelikož k tomu nemůže použít primární klíč (ID). Posloupnost hodnot v primárním klíči nemusí být konzistentní. Různé záznamy se totiž mohly z databáze kdykoliv smazat. Musí tedy projet řádek po řádku, dokud nedojde na hledaný OFFSET. Docela makačka!

Dá se to vyřešit i lépe! Vůbec nepoužívat OFFSET. K čemu taky? Na primárním sloupci ID máme INDEX, to by byl hřích jej nepoužít. Takže OFFSET zahodíme a použije se klasická WHERE podmínka.

První dávka se provede bez WHERE podmínky jako v předchozím příkladu:

SELECT id FROM recipients ORDER BY id LIMIT 1000;

Od 2. dávky se už ale SELECTy změní:

SELECT id FROM recipients WHERE id > 1000 ORDER BY id LIMIT 1000;

ID ve WHERE podmínce je jednodušše ID posledního příjemce z minulé dávky (může být tedy jakékoliv!). Tímto si z databáze vytáhneme vždy hned 1000 následujících příjemců. A výsledek následujícího dotazu?

SELECT id FROM recipients WHERE id > 80000 ORDER BY id LIMIT 1000;

Vrátili jsme se zpět na 10 ms díky INDEXu! Hurá!

Tento problém lze řešit i jinými způsoby, které jsou k vidění například zde: http://explainextended.com/…row-lookups/

Ty lze použít například v případě, kdy nemůžete do WHERE podmínky v SELECT dotazu vložit ID posledního záznamu z minulé dávky.