Dotaz: Tipy na optimalizaci spojení tabulek (JOIN) v mysql

[optokron]

Ahoj. Pokud mám několik tabulek v mysql, které pravidelně spojuji (HLAVNI left join DRUHA left join TRETI), jak co nejlépe optimalizovat toto spojení, aby bylo rychlé? Spojuji pomocí USING(primární klíč), ale nenapadá mě, jak to více optimalizovat. Je rozdíl v rychlosti jestli spojuji podle VARCHARu nebo podle INTu? Není možnost ukládat nějak trvale "cache spojení"?

---

---

Odpovědi

[razor]

Tak v první řadě je potřeba mít správně indexy. Dále int bude obecně rychlejší než varchar - vejde se lépe do paměti, hrozí menší riziko vzniku dočasné tabulky na disku. Snažit se mít v selectu pouze potřebné sloupce - opět snaha omezovat dočasnou tabulka na disku. Doporučuju prostudovat knihu např. MySQL profesionálně - optimalizace pro vysoký výkon

[razor]

A samozřejmě také nastavení serveru. Přidělit mu co nejvíce paměti můžeš. Atd. atd.

[optokron]

Já myslím, že největší náklady na spojení tabulek jsou asi způsobeny řazením spojovaných tabulek tzn. order by položka z tabulky 1, položka z tabulky 2. V tomto případě se asi moc nevyužije (z pohledu optimalizace spojování) omezení pomocí LIMIT řádků. Dá se tohle nějak řešit třeba optimalizacemi dotazů?

[dopisovatel]

Neco uz tady bylo receno ja bych pridal-vyhnout se dotazum typu SELECT * tim ze se pouzije hvezdicka se musi nacist cela tabulka. Takze presne deklarovat co chci za udaje. Mimochodem tak se pozna, ze ten kdo to psal byl prase a ne clovek. Pochopitelne LIMIT muze take pomoct. Vseobecne se ale doporucuje se ptat konkretne. Tzn dotaz maximalne nadefinovat.

[okbob]

Jelikož jsou data uložená po řádcích, tak použití * nebo výčtu nemá žádný vliv na objem dat, která se čtou z disku. Díky výčtu se ovšem data, která nejsou potřeba mohou brzo zahodit a nezabírají paměť a také se neposílají klientovi, čímž se zrychluje přenos.

[kuka]

Jen pro uplnost - toto nemusi byt vzdycky pravda. Nekdy se potrebna data mohou najit rovnou v indexu a tabulka se pak z disku vubec necte.

[okbob]

Pokud se použije index - tam, kde se má, použít, tak Vás šířka řádku obyčejně nebolí, ale máte pravdu, pokud se přistupuje pouze k datům, které jsou umístěny v indexu, tak je to ještě rychlejší.

[ubuntak]

jeste jedna vec pokud je zakladni navrh DB spatny tak z toho nedostanes lepsi vysledky ani kdyby ses stavel na hlavu. Kdysi sem prebral po jednom "praseti" db a ta byla tak vymyslena, ze kdyz colevek neco chtel musel udelat spojeni prez 4 tabulky coz se mi zda byt opravdu hodne. Jako prvni ukol bylo cele to prekopat a vymyslet strukturu tak aby bylo zpracovani rychle a efektivni. Dnes je tedy stav takovy ze se delaji spojeni max 2 tabulek. A navyseni rychlosti je znat.

[okbob]

Primárně bych se zaměřil na LEFT JOIN - zda-li je opravdu nutný, a zda-li nelze dotaz přepsat bez LEFT JOINu. LEFT JOIN znamená, že se bude sekvenčně číst tabulka nalevo, a pokud tato tabulka je velká, a efektivně není omezená predikátem ve WHERE, tak výsledný dotaz může být pomalý. Někteří programátoři, některé ORM systémy cpou LEFT/RIGHT JOIN automaticky bez přemýšlení, zda-li je nebo není nutný.

[optokron]

No right join nepoužívám - podle mysql dokumentace je efektivnější left join. Nicméně původní ("zdrojová") tabulka má cca 400 000 řádků a já potřebuju ke každému z nich připojit několik dalších (left join). Mám aplikaci, která většinou pracuje s 1 tabulkou, ale pokud je potřeba, tak spojuje třeba 7 tabulek (není to chyba návrhu ) - to už teď trvá cca 17 sekund, což je neúměrně hodně.

[okbob]

Ja jsem nemyslel nahradit LEFT JOIN right joinem, ale outer join nahradit inner joinem.

Spojeni 7 tabulek nemusi byt problem - problem je, kdyz od zacatku spojujete velke tabulky - pokud vysledkem bude 100 000 radku, tak je to ok, ale pokud by vysledkem melo byt nekolik tisic radek, tak bych hledal efektivnejsi variantu dotazu. Tech problemu muze byt vic - napriklad, kdyz mate sirokou tabulku a najednou ji musite zpracovavat sekvencne, a jste v ... 400 000 radku neni tolik, 17 vterin je relativne malo pro neinteraktivni operaci a prilis pro interaktivni. Pak muzete zvazit pouziti cache pripadne nejake variace na materializovany pohled.

[Kit]

RIGHT JOIN může být efektivní, pokud prohledávám v neklíčové části pravé tabulky například pomocí LIKE nebo regulárního výrazu. Samozřejmě za předpokladu indexování cizího klíče v levé tabulce.

[l0gik]

IMHO jedinej případ, kdy je right efektivní je když je potřeba RIGHT JOIN a když ae data na pravé straně JOINU příliš neopakují.

Jak s right joinem souvisí podle čeho vyhledávám vpravo nějak nechápu. Jakej je rozdíl mezi tím, když vpravo vyhledávám podle nějakého kritéria oproti tomu, když jsou vpravo pouze záznamy tomu kritériu vyhovující?

[Kit]

Pokud se v takových 7 spojených tabulkách následně sekvenčně vyhledává, tak JE to chyba v návrhu. Minimálně chyba v návrhu dotazu.

[optokron]

No v tabulkách se vyhledává, ale sekvenčně určitě ne (vyjma speciálních případů, kdy nejde použít index u LIKE). Provedl jsem nějaká měření. Spojoval jsem výchozí tabulku se 400 000 řádky s tabulkou se 3 000 000 řádky left joinem a zjistil jsem následující (přibližné průměrné časy):

Vlastní spojení: 0.001 s
Vyhledávání (WHERE t1.sloupec = 'neco' AND t2.sloupec = 'necojine' za použití indexů tzn. dotazy typu =, <, > ...: 0.05 s
Řazení podle sloupce výchozí tabulky: 0.001 s
Řazení podle sloupce druhé tabulky (left join druha_tabulka): 5 s
Kombinace řazení: 4 s

Také jsem vyzkoušel, že InnoDB je cca 2x rychlejší než MyISAM na hledání a ROW FORMAT InnoDB COMPACT je o trochu rychlejší než InnoDB REDUNDANT. Otázka je jasná: jak urychlit to řazení? RIGHT JOIN apod. řešení nepřipadají v úvahu, protože těch tabulek se bude spojovat více. Také by asi bylo dobré aplikovat podmínky WHERE do join podmínek (ON (t1.a = t2.a AND t1.sloupec = 'neco' ...)), nicméně to asi nepůjde zrealizovat, protože by to zachovalo všechny řádky a nevěděl bych, které existují a u kterých je NULL z důvodu nesplnění přidaných podmínek a INNER JOIN na druhé straně omezuje jinak - nicméně tohle mě tolik netrápí, protože je to na rozdíl od řazení velice rychlé i se standardním WHERE.

[Kit]

Také by asi bylo dobré aplikovat podmínky WHERE do join podmínek (ON (t1.a = t2.a AND t1.sloupec = 'neco' ...)), nicméně to asi nepůjde zrealizovat, protože by to zachovalo všechny řádky a nevěděl bych, které existují ...

A nemá se to náhodou psát takto?

... ON t1.a = t2.a WHERE t1.sloupec = 'neco' ...

[optokron]

To jistě, jen jsem psal, že aplikování přímo do spojových podmínek by vedlo k o něco málo rychlejšímu dotazu.

[l0gik]

Tak zrovna todle je typickej případ, kdy je LEFT JOIN špatně.

Pokud je ve WHERE podmínka na tabulku na pravé straně LEFT JOINU (pokud teda není součást té podmínky OR/AND IS NULL) znamená, že řádek v pravé tabulce musí existovat a proto je LEFT JOIN naprosto zbytečný. Tady je správné použít INNER JOIN, protože to je ekvivalentní a přitom rychlejší.

Co se týče nahrazení "LEFT JOINU" "RIGHT JOINEM", tak ten odstavec nějak nechápu, LEFT se od RIGHT JOINU liší pouze prohozením významů tabulek, v podstatě je to syntactic sugar.

[optokron]

(pokud teda není součást té podmínky OR/AND IS NULL)

Právě že je, proto se bez where neobejdu.

[l0gik]

Dáš sem prosím celej dotaz, o kterej se jedná?

[Josef Kufner]

Ono ty databáze jsou celkem chytré a povětšinou si to zvládnou přeskládat a zoptimalizovat i když to je napsané na první pohled neefektivně. Tak jako tak je vždy nutné kouknout na explain, protože se celkem běžně stává, že "pomalejší" konstrukce nakonec funguje rychleji.

[optokron]

A jak mám zoptimalizovat to řazení? Pokud řadím na jednotlivých tabulkách, tak je to velice rychlé, pokud řadím podle sloupce druhé tabulky ve spojení, tak je to velice pomalé. Nedá se to nějak zoptimalizovat?

[okbob]

jedine dat MySQL vic pameti

[Josef Kufner]

A/nebo omezit data ještě před joinem. Třeba udělat stránkování po měsících nebo tak něco, ale nemusí to být vždy možné.

[Kit]

Pokud řadíš podle sloupce z druhé tabulky, možná by se hodil RIGHT JOIN. Co říká EXPLAIN?

[Kit]

... nebo jak píše l0gik, použít INNER JOIN.

[l0gik]

Bez toho, aniž by sem tazatel dal svůj celý dotaz (a nejlépe jeho explain) se nehnem dál. To je jak střílet po vrabcích naslepo z ohnutý vzduchovky.

[optokron]

Inner join použít nemohu, na to co potřebuju je left join ideální. Zkoušel jsem i right join, ale rychlostně to dopadlo stejně.

Explain:

id      select_type       table    type     possible_keys     key      key_len      ref             rows      Extra
1       SIMPLE 	          ppp      index    NULL              name     767          NULL            309090    Using index; Using temporary; Using filesort
1       SIMPLE 	          t        eq_ref   PRIMARY           PRIMARY  767          ppp.prvni.name  1 	 

[optokron]

a dotaz

select name from ppp left join tabulka t on (ppp.name = t.name) order by t.sloupec1 limit 30

[Kit]

Nevím, jestli dobře vidím. Opravdu spojuješ dvě tabulky pomocí klíče dlouhého 767 znaků? Navíc cizí klíč v první tabulce bez indexu? V tom případě mu skutečně nezbývá, než sekvenčně projít všech 300k záznamů, spojit je s druhou tabulkou, výsledek seřadit a vybrat prvních 30 řádek. Čas je odpovídající.

[l0gik]

Pokud se to musí dělat takhle, tak bych určitě zkusil, jestli nebude rychlejší něco takovýdleho. Takovej "manuální LEFT JOIN", kterej nemusí procházet celou tabulku.

SELECT name FROM
(
SELECT name, sloupec1 as razeni FROM tabulka ORDER BY sloupec1 LIMIT 30
UNION 
SELECT name, NULL razeni FROM ppp WHERE name NOT EXIST IN (SELECT name FROM tabulka) LIMIT 30
)
ORDER BY razeni LIMIT 30

[Kit]

Pokud máš vazbu mezi tabulkami 1:1, můžeš vázat pomocí primárního klíče první tabulky. V druhé tabulce bude primárním klíčem cizí klíč. Bude to podstatně rychlejší.

[optokron]

Spojuje se na základě klíče name. Name je v první tabulce UNIQUE index a v druhé tabulce PRIMARY, takže to indexované je. Je to vazba 1:1 s tím, že ty tabulky jsou "naprosto nezávislé" tzn. name z první může a nemusí být v druhé a naopak. Proto to spojovaní těmito klíči a takovými indexy.

[l0gik]

No tak si udělej třetí tabulku names, kam budeš dávat (např. triggerama) možný názvy a přidělovat jim ID.

[optokron]

Tak tohle mě dostalo: jakmile jsem začal spojovat pomocí id (tj. místo varcharu číselně), tak stoupla doba provádění dotazu 3x! A to je navíc ta druhá tabulka úplně prázdná (tabulka s názvem tabulka). Udělal jsem nějakou brutální chybu nebo se mysql db zbláznila?

CREATE TABLE `tabulka` (
 `lid` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `id` int(10) unsigned NOT NULL,
 `sloupec1` tinyint(1) DEFAULT NULL,
 PRIMARY KEY (`lid`),
 KEY `sloupec1` (`sloupec1`),
 KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

select .... left join tabulka ... order by tabulka.sloupec1 desc
- podtržená část způsobí zpomalení na více než 30 s.


explain:
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	ppp 	ALL 	NULL 	NULL 	NULL 	NULL 	306409 	Using temporary; Using filesort
1 	SIMPLE 	tabulka 	ref 	id 	id 	4 	ppp.ppp.id 	1 	 

A je jedno jestli je id unikátní nebo ne (tady není, protože mi to umožní do budoucna spojení 1:N).

[l0gik]

Nemáš tam index.

[l0gik]

V tabulka je máš, chybí teda asi na tabulce ppp. Popř. pokud tam je, zkus zavolat OPTIMIZE TABLE, možná ještě neměl správný statistiky.

[optokron]

Indexy mám v obou tabulkách, optimize nepomohlo.

[l0gik]

Bez kompletního dotazu to těžko odhalíme, čím to je... Ty se bojíš, že Ti tu tabulku hacknem, že sem vždycky dáš jen torso dotazu? Co tahle kompletní skript, abychom mohli problém reprodukovat?

[optokron]

Hm, tak už to začalo fungovat, po optimize table jsem provedl pár dotazů a už se to chytlo, i po restartu mysql serveru funguje dobře. Ještě mě zaráží dvě věci:

1/ pokud provádím spojení pomocí sloupce typu varchar, tak spojení s řazením trvá déle než spojení na intu, ale po dalším dotazu už je to okamžité (kvůli keši), zato u spojování na intovém sloupci to je pořád stejně rychlé - proč se to nekešuje?

2/ pokud selektuju 10 sloupců (z první tabulky), tak dotaz trvá asi 4x déle, než když selektuju jeden, to už se mi vyplatí to nejprve vybrat a potom ručně v aplikaci provést třeba 30 dalších dotazů nad jednotlivou tabulkou. Nedá se s tímhle něco udělat (zvýšení "povolené paměti" jsem zkusil)? Připadá mi to zbytečně moc - přeci mysql provede stejné operace jako předtím, jen si musí zapamatovat o pár hodnot (omezených limitem) více

[l0gik]

Jenže ona to musí celé setřídit. Asi to třídí fyzicky a ne pomocí odkazů na řádky. Pak už samotné kopírování, výpadky cache atd.. udělají své.

Zkusil jsi ten workaround pomocí UNIONU a INNER JOINU? Ten by se moh třídění celé tabulky vyhnout úplně.

[optokron]

SELECT name FROM
(
SELECT name, sloupec1 as razeni FROM tabulka tab LIMIT 30
UNION
SELECT name, NULL razeni FROM ppp pp WHERE name NOT IN (SELECT name FROM tabulka) LIMIT 30
) t
ORDER BY razeni LIMIT 30

Toto je bleskové, ale nebude to funkční - mysql mi nedovolí order by u selectu v unionu. Také by to bylo nefunkční pro řazení podle více sloupců.

[l0gik]

Pokud chceš řadit v první části unionu, musíš dát ten subselect do závorky A proč nejde řadit podle víc sloupců? Prostě těch unionů poskládáš za sebou víc:

SELECT name,  FROM
(
(SELECT name, sloupec1 as razeni, NULL FROM tabulka LIMIT 30)
UNION
(SELECT name, NULL, sloupec1 as razeni2 FROM nevimco LIMIT 30)
UNION
(SELECT name, NULL, NULL FROM ppp pp WHERE name NOT IN (SELECT name FROM tabulka) LIMIT 30)
) t
ORDER BY razeni, razeni2 LIMIT 30

[optokron]

Ok, do těch závorek už to order rád jde, ale tento dotaz nemůže fungovat z principu. Aby to fungovalo, musel bych dát do prvního subselektu "order by razeni" a do druheho "order by razeni2".

jak by to mělo fungovat:

name      sloupec1       sloupec2
a         1              1
b         2              1
c         3              1
d         4              1
e         5              1
f         6              2
g         6              3
h         6              4
i         7              5
j         8              5
k         9              5
l         10             5
m         11             6
n         12             7
o         13             8
p         14             9


jenže ono to nejprve seřadí sloupec1, pak sloupec2 - tzn. pokud budou ve sloupci1 někde stejné hodnoty, pak ty sloupec2 nemusí vůbec ovlivnit a jen přidá další.

Nicméně tento typ dotazu určitě použiju při řazení podle jednoho sloupce (kde je zrychlení ze 4.5 na 0.001s). Díky.

[l0gik]

Jo, částečně máš pravdu, musí se to akorát napsat trochu složitějc - Pokud řadíš podle víc sloupců z jedný tabulky, problém to není, prostě v unionu bude ta jedna tabulka s víc řadícíma sloupcema. - Pokud řadíš podle víc tabulek, tak zkombinovat INNER JOIN na obě tabulky, první a druhou tabulku.

blbý je, že počet unionů naroste s počtem různejch tabulek exponenciálně. Pro dvě či tři to ale nevadí

(SELECT id, f1, f2, f4 NULL FROM t1 INNER JOIN t2 USING (id) ORDER BY f1, f2,3 LIMIT 30)
UNION
(SELECT id, f1, f2, NULL NULL NULL FROM t1 INNER JOIN t2 USING (id) ORDER BY f1, f2,3 LIMIT 30)
UNION
(SELECT id, NULL, NULL, f4 FROM t1 INNER JOIN t2 USING (id) ORDER BY f1, f2,3 LIMIT 30)
UNION
(SELECT id FROM mastertable WHERE id NOT IN (select id from t1) AND  id NOT IN (select id from t2) ORDER BY f1, f2,3 LIMIT 30)

Nejlepší v tomdle případě by bylo vytvořit temporary table a do ní to postupně sypat a skončit v okamžiku, kdy je v ní dostatek záznamů.

[optokron]

Mohl bys prosím tě trochu rozepsat co jednotlivé části dotazu dělají? V tomhle už se trochu přestávám orientovat.

[l0gik]

Jo já to psal v rychlosti a trochu blbě.

No prostě místo left joinu, kterej mysql implementuje tak, že ho nejprve udělá a pak omezí, tak nějakým způsobem spojím všechny kombinace tabulek, který můžou bejt výst k výsledku, protože ty umím omezit předem. Vyhnu se tím nákldnýmu sortu celý tabulky.

Pokud selektím z tabulky t a řadím dle left joinů z tabulky t1 a t2, tak musím dát dohromady (v tomto pořadí) - symbolickej zápis.

from t1 inner join t2                    
from t1 not in t2                        
from t2 not in t1
from t not in t1 and not in t2

S víc tabulkama to už začne bejt mazec, ale dá se to poskládat automaticky. Hlavně bych pak místo unionu použil temporary table, protože v ní můžeš počítat počet vložených záznamů a až jich bude dost, tak nepokračovat ve vkládání dalších subdotazů.

[Josef Kufner]

Těch deset sloupců můžeš přidat až potom, později. Udělej si select bez nich a pak buď prostým joinem (nebo obalením do dalšího selectu) přidej ty sloupce, které chceš, ale které neovlivní výsledek.