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.

Problém se výrazně projeví pokud web prochází vyhledávací boti, kteří se snaží projít všechny dostupné stránky.

Diagnostika

• Vysvětlení EXPLAIN
• Profilování PROFILE

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

• Změnit rozvržení tabulek a omezit množství dat tak, aby se problém projevoval méně. Např. rozdělit data do více tabulek.

• Zredukovat velikost načítaných dočasných sloupců

Normálně se dotaz:

SELECT * FROM `Table` ORDER BY `Column` LIMIT 100000, 10

provede tak, že se prochází všech 100 tisíc řádků. Ovšem je rozdíl, zda se načte 100 tisíc řádků s délkou 10 bajtů nebo 500 bajtů. Takže jedna z možných optimalizací je, že se pomocí LIMITu s ofsetem načtou pouze sloupce dva a to primární klíč a sloupec, podle kterého řadíme. Až pak se pomocí JOINu přiřadí k výsledným 10 řádkům skutečná objemnější data řádků.

Dotaz by pak vypadala např. takto:

SELECT T2.* FROM (SELECT Id, Column FROM `Table` ORDER BY `Column` LIMIT 100000, 10) AS T1
LEFT JOIN `Table` AS T2 ON T2.Id = T1.Id

Zrychlení závisí na poměru velikostí řádků se dvěma sloupci vs. se všemi požadovanými sloupci. Což může být např.

Vnější odkazy

• Efficient pagination using MySQL
• Optimized Pagination using MySQL
• How can I speed up a MySQL query with a large offset in the LIMIT clause?
• MySQL ORDER BY / LIMIT performance: late row lookups