Optimalizace stránkování výpisů u databáze MySQL

Úvod

Klasicky se stránkování řeší jako SELECT ... FROM tabulka WHERE podmínka ORDER BY sloupec LIMIT ofset, počet. Problém však nastává pro objemné tabulky s mnoha řádky, které se nevejdou do paměti serveru, a musí být tak načítány z disku. Dostupnou paměť lze ovlivnit parametrem innodb_buffer_pool_size, avšak ta je omezena max. dostupnou fyzickou pamětí a nastavuje se zpravidla na 80% operační paměti.

Podstata problému leží v tom, že parametr ofset u LIMITU nepoužívá indexy, ale server načítá všechny řádky tabulky až do hodnoty ofsetu. To znamená, že např. pro LIMIT 10000, 10 server musí načíst všech 10000 řádků a pak je následně zahodit, aby vrátil pouze 10 požadovaných. To vede k velkým diskovým operacím a přetěžování serverů. Náročnost přitom roste s počtem a velikostí řádků v tabulce.

Možná řešení

• Nepoužívat ofset u limitu a začátek výpisu určit podle WHERE sloupec > ofset, kde sloupec by měl být index pro urychlení operací.

• Umožnit stránkovat pouze podle primárního klíče

 V databázi InnoDb jsou fyzicky řádky uloženy za sebou podle primárního klíče. Navíc primární klíč je index, který umožňuje efektivní řazení výsledků. Ovšem pokud se některé řádky odebírají, tak číslo primárního klíče není lineárně rostoucí po jedničce.

• Umožnit stránkování pouze do omezeného limitu např. do 1000 položek, kde ještě zátěž není tak výrazná.