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

Chronos v 1. 2. 2014, 13:50

2014-02-01T13:50:43Z

← Starší verze		Verze z 1. 2. 2014, 15:50
Řádek 40:		Řádek 40:
	* [http://www.xarg.org/2011/10/optimized-pagination-using-mysql/ Optimized Pagination using MySQL]		* [http://www.xarg.org/2011/10/optimized-pagination-using-mysql/ Optimized Pagination using MySQL]
	* [http://stackoverflow.com/questions/1243952/how-can-i-speed-up-a-mysql-query-with-a-large-offset-in-the-limit-clause How can I speed up a MySQL query with a large offset in the LIMIT clause?]		* [http://stackoverflow.com/questions/1243952/how-can-i-speed-up-a-mysql-query-with-a-large-offset-in-the-limit-clause How can I speed up a MySQL query with a large offset in the LIMIT clause?]
			* [http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/ MySQL ORDER BY / LIMIT performance: late row lookups]



	[[Kategorie:Programování]]		[[Kategorie:Programování]]

Chronos v 1. 2. 2014, 13:41

2014-02-01T13:41:39Z

← Starší verze		Verze z 1. 2. 2014, 15:41
Řádek 23:		Řádek 23:

	* 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.		* 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:
			<source lang="sql">SELECT * FROM `Table` ORDER BY `Column` LIMIT 100000, 10</source> 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:
			<source lang="sql">
			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
			</source>

			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=

			* [http://www.slideshare.net/suratbhati/efficient-pagination-using-mysql-6187107 Efficient pagination using MySQL]
			* [http://www.xarg.org/2011/10/optimized-pagination-using-mysql/ Optimized Pagination using MySQL]
			* [http://stackoverflow.com/questions/1243952/how-can-i-speed-up-a-mysql-query-with-a-large-offset-in-the-limit-clause How can I speed up a MySQL query with a large offset in the LIMIT clause?]



	[[Kategorie:Programování]]		[[Kategorie:Programování]]

Chronos v 1. 2. 2014, 13:04

2014-02-01T13:04:15Z

← Starší verze		Verze z 1. 2. 2014, 15:04
Řádek 22:		Řádek 22:
	* 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á.

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


	[[Kategorie:Programování]]		[[Kategorie:Programování]]

Chronos v 1. 2. 2014, 12:57

2014-02-01T12:57:12Z

← Starší verze		Verze z 1. 2. 2014, 14:57
Řádek 5:		Řádek 5:

	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.

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

	=Diagnostika=		=Diagnostika=

Chronos v 1. 2. 2014, 12:55

2014-02-01T12:55:59Z

← Starší verze		Verze z 1. 2. 2014, 14:55
Řá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.		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á.

Chronos: 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…

2014-02-01T12:54:35Z

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…

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

[[Kategorie:Programování]]