Velká MySQL tabulka, optimalizace SELECT pro stránkování

19. 6. 2012 19:22:42

Zdravím,

Mám v mysql tabulku (říkejme ji třeba "pokus") a ta obsahuje přes 1 500 000 záznamů.

Potřeboval bych udělat stránkování (to mám již vyřešené), nicméně když vypíši tímto příkladem data pro danou stránku:

pokud je $limit_start třeba 500 (LIMIT 500,40) je všecko ok, ale pokud mám $limit_start třeba 750000 (LIMIT 750000,40) tak se to strašně dlouho načítá (nebo vůbec).

Můžete mi prosím někdo poradit, jak mám udělat tento dotaz efektivně, abych nemusel číst všecky řádky v tabulce i když je nepotřebuji, ale aby se vypsalo jen těch 40 a rychlost byla furt stejná (co nejvyšší)??

Díky moc všem, kteří přispějí do diskuze

19. 6. 2012 19:22:42

https://webtrh.cz/diskuse/velka-mysql-tabulka-optimalizace-select-pro-strankovani#reply776972

Martin Schlemmer

(36 hodnocení)

19. 6. 2012 19:35:18

Pošlete sem strukturu tabulky a výsledek toho dotazu s EXPLAIN:

19. 6. 2012 19:35:18

https://webtrh.cz/diskuse/velka-mysql-tabulka-optimalizace-select-pro-strankovani#reply776971

Martin Schlemmer

(36 hodnocení)

19. 6. 2012 19:42:11

Zkuste to přepsat na

A - to nesouvisí s vaším dotazem, ale s obecnými doporučenými postupy - vypište explicitně sloupce, které potřebujete, místo hvězdičky.

19. 6. 2012 19:42:11

https://webtrh.cz/diskuse/velka-mysql-tabulka-optimalizace-select-pro-strankovani#reply776970

pes502

(4 hodnocení)

19. 6. 2012 19:44:17

Explain je zde = http://up.ezin.cz/images/explain.png

Jinak

id INT (11)

všecko ostatní TEXT ... TEXT tam mám jako výchozí, protože jsem s tím dělal pokusy, je možné to přeházet na nějaké lepší datové typy.

Jinak velikost indexu: 15MB

Délka řádku ø 1 123

Velikost řádku ø 1 134 B

19. 6. 2012 19:44:17

https://webtrh.cz/diskuse/velka-mysql-tabulka-optimalizace-select-pro-strankovani#reply776969

Martin Schlemmer

(36 hodnocení)

19. 6. 2012 20:01:21

Ještě lepší nápad

Další důvod pro pomalý dotaz (konkrétně Using filesort):

Pokud čtete sloupce typu TEXT a BLOB, MySQL nemůže pracovat v paměti, ale musí zapisovat na disk.

http://dev.mysql.com/doc/refman/5.0/en/blob.html

Instances of BLOB or TEXT columns in the result of a query that is processed using a temporary table causes the server to use a table on disk rather than in memory because the MEMORY storage engine does not support those data types. Use of disk incurs a performance penalty, so include BLOB or TEXT columns in the query result only if they are really needed. For example, avoid using SELECT *, which selects all columns.

Takže řešení takhle z křesla:

1. Nepoužívejte LIMIT :OFFSET, :CUTOFF pro vysoký OFFSET. Přepište to výše naznačeným způsobem.

Viz Avoid offset for large tables

2. Všechny sloupce typu TEXT přesuňte do druhé tabulky, kde použijete stejný primární klíč. Relační dotazy provádějte nad první tabulkou a z druhé vytáhněte potřebné informace až jako druhý krok, jen pomocí primárního klíče.

19. 6. 2012 20:01:21

https://webtrh.cz/diskuse/velka-mysql-tabulka-optimalizace-select-pro-strankovani#reply776968

pes502

(4 hodnocení)

19. 6. 2012 20:08:51

Napsal Martin Schlemmer;810038
2. Všechny sloupce typu TEXT přesuňte do druhé tabulky, kde použijete stejný primární klíč. Relační dotazy provádějte nad první tabulkou a z druhé vytáhněte potřebné informace až jako druhý krok, jen pomocí primárního klíče.

Toto jsem moc nepochopil :/

Abych pravdu řekl, nikdy jsem takové problémy řešit nemusel a SQL zas až tak do hloubky neznám :/

19. 6. 2012 20:08:51

https://webtrh.cz/diskuse/velka-mysql-tabulka-optimalizace-select-pro-strankovani#reply776967

Martin Schlemmer

(36 hodnocení)

19. 6. 2012 20:18:18

V jedné tabulce máte primární klíč (ve vašem případě `id`) a všechny sloupce kromě sloupců typu TEXT a BLOB.

V druhé tabulce máte stejný primární klíč a všechny sloupce typu TEXT a BLOB.

Všechny dotazy provádíte nad první tabulkou.

Druhou tabulku použijete pouze pro načtení chybějících údajů:

19. 6. 2012 20:18:18

https://webtrh.cz/diskuse/velka-mysql-tabulka-optimalizace-select-pro-strankovani#reply776966

pes502

(4 hodnocení)

19. 6. 2012 20:20:40

A pokud přehážu pole typu TEXT třeba na VARCHAR (což by se dalo) tak to půjde vyřešit přímo z jedné tabulky?

19. 6. 2012 20:20:40

https://webtrh.cz/diskuse/velka-mysql-tabulka-optimalizace-select-pro-strankovani#reply776965

Martin Schlemmer

(36 hodnocení)

19. 6. 2012 20:27:53

To záleží, jak široký ten VARCHAR bude a kolik má MySQL k dispozici paměti.

V tabulce typu MEMORY se totiž VARCHAR chová jako CHAR.

http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html

Variable-length types such as VARCHAR are stored using a fixed length.

Při sloupci VARCHAR(1024), tedy 1024 bytů / 1 kB na buňku a dvou milionech řádcích bude ten jediný sloupec zabírat necelé 2 GB RAM.

19. 6. 2012 20:27:53

https://webtrh.cz/diskuse/velka-mysql-tabulka-optimalizace-select-pro-strankovani#reply776964

Martin Schlemmer

(36 hodnocení)

19. 6. 2012 20:31:41

Zkuste ještě tohle:

A ty IDs dosaďte do tohoto dotazu oddělené čárkou:

19. 6. 2012 20:31:41

https://webtrh.cz/diskuse/velka-mysql-tabulka-optimalizace-select-pro-strankovani#reply776963

pes502

(4 hodnocení)

19. 6. 2012 20:33:23

Napsal Martin Schlemmer;810052
Zkuste ještě tohle:
SELECT id FROM `table` WHERE id BETWEEN 750000 AND (750000 + 40) ORDER BY id DESC;
A ty IDs dosaďte do tohoto dotazu oddělené čárkou:
SELECT * FROM `table` WHERE id IN (IDs);

To s tím BETWEEN jsem již četl na více webech, ale nevím, jak to mám udělat.

Na STRANA 1 mám video, které má ID třeba 1500000 a na straně třeba 37000 mám video, které má ID 1 a zatím mě nenapadl způsob, jak zjišťovat IDčka, abych je mohl dosadit.

Používám tuto fci, kteoru jsem si sepsal:

proměnná $actual stáhne z $_GET aktuální stránku, na které se uživatel nachází

19. 6. 2012 20:33:23

https://webtrh.cz/diskuse/velka-mysql-tabulka-optimalizace-select-pro-strankovani#reply776962

Martin Schlemmer

(36 hodnocení)

19. 6. 2012 20:53:43

Za předpokladu, že v číslování není mezera a jsou aktivní všechny řádky (nejsou např. označené jako smazané nebo v moderaci), dostanete nejnižší a nejvyšší ID pro stránku takto:

Pokud neplatí ty dva předpoklady, nedá se poradit takhle na dálku. Budete muset být kreativní a víc se vzdělat.

Intuitivní řešení by bylo očíslovat si všechny aktivní záznamy samostatně, v extra sloupci.

P.S.: "Actual" znamená "skutečný". "Stávající" je "current".

19. 6. 2012 20:53:43

https://webtrh.cz/diskuse/velka-mysql-tabulka-optimalizace-select-pro-strankovani#reply776961

naniccz

(3 hodnocení)

19. 6. 2012 20:58:52

Mate mě ten filesort v EXPLAINu -- nepoužije se index (primárního klíče?!), a proto to tak dlouho trvá. Pokud je čtení výrazně častější než zápis, vyplatí se držet "materializovaný pohled" s vazbou 1:1 k této tabulce, určující pořadí záznamů (s indexem na tom pořadí), a vybírat tím BETWEEN dotazem konkrétní interval pořadí...

19. 6. 2012 20:58:52

https://webtrh.cz/diskuse/velka-mysql-tabulka-optimalizace-select-pro-strankovani#reply776960

Martin Schlemmer

(36 hodnocení)

19. 6. 2012 21:09:33

Pokud se nepletu, filesort se použije, protože ten index se musí řadit obráceně (DESC, přitom je ale řazený ASC).

19. 6. 2012 21:09:33

https://webtrh.cz/diskuse/velka-mysql-tabulka-optimalizace-select-pro-strankovani#reply776959

SEOPeter

19. 6. 2012 21:50:32

Řešil jsem podobně "objemný" projekt s tabulkami cca milion řádků. Podle svých zkušeností musím říct, že MySQL není dělané na takto rozsáhlé databáze. Funguje to, ale je šíleně pomalé.

Problém je v typu DB, doporučuju změnit. Já sám jsem to musel řešit nakonec v Céčku.

19. 6. 2012 21:50:32

https://webtrh.cz/diskuse/velka-mysql-tabulka-optimalizace-select-pro-strankovani#reply776958