Optimalizace stránkování výpisů u databáze MySQL: Porovnání verzí
(Založena nová stránka: =Ú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…) |
Bez shrnutí editace |
||
| Řádek 2: | Řádek 2: | ||
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. |
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. |
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. |
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. |
||
=Diagnostika= |
|||
* Vysvětlení EXPLAIN |
|||
* Profilování PROFILE |
|||
=Možná řešení= |
=Možná řešení= |
||
| Řádek 11: | Řádek 16: | ||
* Umožnit stránkovat pouze podle primárního klíče |
* 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á. |
* Umožnit stránkování pouze do omezeného limitu např. do 1000 položek, kde ještě zátěž není tak výrazná. |
||
Verze z 1. 2. 2014, 14:55
Ú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.
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á.