Připojení Mysql z obou tabulek. Referenční příručka MySQL. Úvod do LEFT JOIN MySQL

I když jej lze aplikovat i na vývoj standardních softwarových produktů pro lokální použití. O tom ale tento článek nebude.

Často je při práci s databázemi v jednotlivých jazycích úkolem provést výběr dat pro zobrazení v různých sestavách, grafech a podobně. Při implementaci tohoto typu úlohy musíte zpravidla použít ne jednu, ale několik tabulek, které je spojují do jednoho dotazu, což výrazně komplikuje jeho návrh. V tomto případě je třeba vzít v úvahu, jak budou data vyvedena, jak se budou tabulky „vytahovat“ a jaký výsledek bude pro programátora nejpřijatelnější. K řešení takových problémů se používá jeden ze standardních jazykových konstruktů MySQL – Join.

Koncept slova připojit

Jazyky pro vývoj databáze, ať už jde o jakýkoli jazyk, jsou založeny na standardních slovech z anglických slovníků (proto, pokud umíte anglicky, bude pro vás práce s tabulkami mnohem jednodušší). Pro realizaci spojení tabulek s výběrem je použito stejné slovo - Join. Databázový programovací jazyk používá My SQL. Překlad tohoto funkčního slova je naprosto stejný jako v samotném jazyce – „sjednocení“.

Interpretace konstruktu MySQL - Join a kteréhokoli z nich bude naprosto stejná. Pokud dešifrujeme účel konstrukce, konkrétně schéma jejího fungování, dostaneme následující význam: konstrukce vám umožní shromáždit potřebná pole z různých tabulek nebo vnořených dotazů do jednoho výběru.

Typy struktur pro kombinování

Pokud programátor potřebuje shromáždit vzorek z několika tabulek a ví, jaká klíčová pole v nich jsou a jaká data jsou potřeba pro sestavu, pak může k dosažení požadovaného výsledku použít jednu z hlavních konstrukcí spojení. Existují čtyři hlavní konstrukce (pro spojování stolů):

1. Vnitřní spojení.
2. Křížové spojení.
3. Levý spoj.
4. Správně Připojte se.

V závislosti na daném úkolu bude každý ze standardních návrhů poskytovat různé výsledky, což vám umožní přijímat zprávy o různých parametrech v krátkém čase.

Vytváření a naplňování tabulek pro pozdější použití

Než začneme například uvažovat o mechanismech práce s konstrukcemi agregace dat, vyplatí se připravit několik tabulek, se kterými budeme v budoucnu pracovat. To pomůže názorně ukázat všechny principy fungování operátorů, navíc si tímto způsobem začátečníci snadněji osvojí všechny základy programování tabulek.

V první tabulce budou popsány některé předměty, se kterými se člověk neustále setkává po celý život.

Ve druhé tabulce popisujeme některé vlastnosti objektů z první tabulky, abyste s nimi mohli v budoucnu pracovat.

Obecně budou stačit dvě tabulky, aby bylo vidět, jak fungují na příkladu. Nyní můžeme začít s praktickým zkoumáním našich návrhů.

Použití vnitřního spojení

Při použití konstruktu MySQL - Join Ineer se vyplatí zvážit některé jeho vlastnosti. Tento návrh vám umožní vybrat z obou tabulek pouze ty záznamy, které jsou v první i druhé tabulce. Jak to funguje? V první tabulce máme hlavní klíč – ID, který udává pořadové číslo záznamů v tabulce.

Při vytváření druhé tabulky je použit stejný klíč jako pořadové číslo příklad je vidět na obrázcích. Při výběru dat operátor Select ve výsledku určí pouze ty záznamy, jejichž pořadová čísla se shodují - což znamená, že jsou v první i druhé tabulce.

Při použití návrhu musíte přesně pochopit, jaká data potřebujete získat. Nejčastější chybou, zejména mezi začínajícími programátory, je iracionální a nesprávné použití konstruktu Inner Join. Jak můžeme uvažovat o skriptu, který nám bude vracet informace o objektech a jejich vlastnostech z dříve popsaných a vyplněných tabulek. Zde však může být několik způsobů, jak strukturu použít. V tomto ohledu je My SQL velmi flexibilní jazyk. Můžeme se tedy podívat na příklady použití MySQL Inner Join.

Kombinování tabulek bez zadání jakýchkoli parametrů. V tomto případě dostaneme následující výsledek:

Pokud pomocí pomocného slova Using naznačíme, že je třeba vzít v úvahu hlavní klíče záznamů v tabulkách, pak se výsledek výběru dramaticky změní. V tomto případě získáme výběr, který vrátí pouze ty řádky, které mají stejné primární klíče.

Je také možná třetí možnost použití konstrukce, kdy dotaz používá slovo „on“ k označení polí, podle kterých mají být tabulky sloučeny. V tomto případě výběr vrátí následující údaje:

Vlastnosti použití levého spojení

Pokud zvážíme jiný způsob spojení tabulek pomocí konstrukce MySQL – Join, všimnete si rozdílu v datech, která jsou na výstupu. Takovým mechanismem je levý konstrukt.

Použití konstruktu MySQL Left Join má některé zvláštnosti a stejně jako Inner vyžaduje jasné pochopení výsledku, kterého je třeba dosáhnout.

V tomto případě budou nejprve vybrány všechny záznamy z první tabulky a poté k nim budou připojeny záznamy z druhé tabulky vlastností. Navíc, pokud má první tabulka záznam, například „stool“, a druhá tabulka pro něj nemá jedinou vlastnost, pak operátor Left zobrazí naproti tomuto záznamu hodnotu null, která programátorovi sdělí, že existují žádné atributy pro tento typ položky.

Použití tohoto designu vám umožní určit, která pole nebo například produkty v obchodě nejsou naceněny a tak dále.

Levý příklad

Abychom uvažovali o operátoru konstrukce Left Join MySQL v praxi, používáme dříve popsané tabulky. Řekněme, že potřebujete vybrat celý seznam produktů, které jsou v obchodě, a zkontrolovat, které z nich nemají atributy nebo vlastnosti. V tomto případě se ve výběru zobrazí všechny produkty a ty, které nemají vlastnost, budou mít prázdné hodnoty.

Použití Where v klauzuli o spojení

Jako parametr může spojení obsahovat nejen indikace polí, pomocí kterých je třeba propojit tabulky, ale může také zahrnovat operátor podmínky Where.

Uvažujme například skript, který by nám měl vracet pouze ty záznamy, u kterých není zadán atribut. V tomto případě musíte do konstrukce spojení přidat operátor podmínky a uvést, co přesně je jako výsledek potřeba vrátit.

Při použití Join - Where v MySQL musíte jasně pochopit, že se zobrazí pouze ty záznamy, na které se vztahuje zadaná podmínka, a výběr pak bude vypadat takto:

Takové dotazy umožňují provádět výběry na základě konkrétních dat, která se týkají stavu vybraného programátorem. Můžete zadat několik takových podmínek a zároveň maximalizovat parametry pro výběr dat ze sloučených tabulek.

Pomocí Join můžete změnit data v tabulkách

Konstrukce Join je v podstatě univerzální. Umožňuje nejen provádět různé výběry, ale také připojit jednu až několik tabulek k dotazům a zadávat do výběru další podmínky. Konstrukt lze použít i pro jiné datové operace. Join tedy lze použít ke změně dat v tabulce. Nebo spíše pro upřesnění podmínek v tabulce nebo v případech, kdy potřebujete aktualizovat data ve více tabulkách podle stejných podmínek.

Zvažte například následující problém. Jsou uvedeny tři tabulky, které obsahují některá data. Musíte změnit data v obou tabulkách pomocí jednoho dotazu. Právě k vyřešení tohoto druhu úlohy můžete použít konstrukci Join v příkazu Update. Samotný typ konstrukce Join závisí, stejně jako v případě vzorkování dat, na výsledku, kterého chce programátor získat.

Podívejme se na nejjednodušší příklad. Musíte aktualizovat data pomocí stejných podmínek s jedním požadavkem. Tyto druhy dotazů jsou vytvořeny pro optimalizaci práce s databází. Proč psát různé dotazy pro každou tabulku, když můžete všechny manipulace s daty provádět jedním dotazem? Příklad spojení v našem případě bude vypadat takto:

Stavební složité dotazy

Poměrně často je při práci s databází nutné vytvářet dotazy nejen spojením více tabulek, ale také pomocí poddotazů. Takové úlohy jsou pro začínajícího databázového programátora poměrně obtížné pochopit. Potíž je v tom, že si musíte každý krok promyslet, určit, jaká data je třeba z které tabulky nebo dotazu získat a jak s nimi budete muset v budoucnu pracovat.

Pro přesnější pochopení můžete zvážit (v MySQL Join) příklady složitých dotazů. Pokud jste začátečník a teprve začínáte pracovat s databázemi, pak bude takové školení jedině přínosné. Ideální varianta by byla

Tento dotaz vrátí 58 záznamů prodejních smluv, které mají dokončený nebo existující hotovostní zůstatek ke zvolenému datu. V tomto případě se jedná o aktuální datum. Do výběru byla také přidána podmínka, že název smlouvy musí obsahovat symboly „123“. Informace (data) zobrazené na obrazovce budou seřazeny - seřazené podle čísla smlouvy.

Následující příklad zobrazí údaje o všech platbách, na kterých bude uvedeno číslo smlouvy.

Pomocí poddotazů

Jak již bylo zmíněno dříve, při práci s databázemi lze spojit nejen tabulky, ale také tabulku s dotazem. Tento design slouží především k urychlení dotazu a jeho optimalizaci.

Pokud například potřebujete vybrat pouze dvě pole z tabulky, která má několik stovek polí a řekněme tisíc záznamů, měli byste použít dotaz, který vrátí pouze nezbytná pole a zkombinovat jej s hlavním výběrem dat. Jako příklad MySQL Join Select můžete zvážit dotaz tohoto typu:

Toto nejsou všechny způsoby, jak používat standardní konstrukce MySQL, pouze ty standardní. Jak používat konstrukci Join a v jakých typech se rozhodne programátor, ale stojí za to si zapamatovat a vzít v úvahu, jaký výsledek by měl být získán při provádění dotazu.

MySQL podporuje následující syntaxi příkazu JOIN při použití v příkazech SELECT:

odkaz na tabulku, odkaz na tabulku odkaz na tabulku JOIN odkaz na tabulku odkaz na tabulku VNITŘNÍ JOIN odkaz na tabulku odkaz na tabulku odkaz na tabulku STRAIGHT_JOIN odkaz na tabulku odkaz na tabulku LEFT JOIN odkaz na tabulku odkaz na tabulku LEFT JOIN na tabulku odkaz na tabulku NATURAL ] odkaz na tabulku JOIN ( na odkaz na tabulku LEFT_na do tabulky LEFT_reference na tabulku JO podmínka tabulka_reference RIGHT JOIN tabulka_reference tabulka_reference NATURAL ] JOIN tabulka_reference

kde tabulka_odkaz je definován jako:

Table_name[alias]

a join_condition je definován jako:

ON conditional_expr | POUŽÍVÁM (seznam_sloupců)

V klauzuli ON byste nikdy neměli zadávat žádné podmínky, které ukládají omezení na řádky v sadě výsledků. Pokud potřebujete určit, které řádky by měly být přítomny ve výsledku, měli byste tak učinit v klauzuli WHERE.

Upozorňujeme, že ve verzích před 3.23.17 operátor INNER JOIN nepřijímá parametr join_condition!

Přítomnost poslední z výše uvedených konstrukcí výrazu LEFT OUTER JOIN je způsobena pouze požadavky na kompatibilitu ODBC:

• Místo odkazu na tabulku lze použít alias, který se přiřazuje pomocí výrazů tbl_name AS alias_name nebo tbl_name alias_name: mysql> SELECT t1.name, t2.salary FROM zaměstnanec AS t1, info AS t2 WHERE t1.name = t2. jméno;
• Podmíněný příkaz ON je podmínka v jakékoli formě, kterou lze použít v klauzuli WHERE.
• Pokud není položka pro pravou tabulku nalezena v částech ON nebo USING LEFT JOIN, pak se pro tuto tabulku použije řádek se všemi sloupci nastavenými na NULL. Tuto funkci lze použít k nalezení výsledků v tabulce, která nemá ekvivalent v jiné tabulce: mysql> SELECT tabulka1.* FROM tabulka1 LEFT JOIN tabulka2 ON tabulka1.id=tabulka2.id WHERE tabulka2.id JE NULL; Tento příklad najde všechny řádky v tabulce1 s hodnotou id, která není přítomna v tabulce2 (tj. všechny řádky v tabulce1, pro které nejsou v tabulce2 žádné odpovídající řádky). To samozřejmě předpokládá, že table2.id je deklarován jako NOT NULL . Viz část 5.2.6 Jak MySQL optimalizuje LEFT JOIN a RIGHT JOIN.
• USING(seznam_sloupců) se používá k určení seznamu sloupců, které musí existovat v obou tabulkách. Výraz USING jako: A LEFT JOIN B USING (C1,C2,C3,...) je sémanticky totožný s výrazem ON, například: A.C1=B.C1 AND A.C2=B.C2 AND A .C3=B .C3,...
• NATURAL JOIN mezi dvěma tabulkami je definován jako sémantický ekvivalent INNER JOIN nebo LEFT JOIN s klauzulí USING, která specifikuje všechny sloupce přítomné v obou tabulkách.
• INNER JOIN a (čárka) jsou sémantické ekvivalenty. Oba provádějí úplné spojení na použitých stolech. Způsob propojení tabulek je obvykle specifikován v klauzuli WHERE.
• RIGHT JOIN funguje podobně jako LEFT JOIN. Pro zachování přenositelnosti kódu mezi různými databázemi se doporučuje použít LEFT JOIN místo RIGHT JOIN.
• STRAIGHT_JOIN je identický s JOIN, až na to, že levá tabulka je vždy čtena před pravou. Tento výraz lze použít pro (několik) případů, kdy optimalizátor spojení umístí tabulky do nesprávného pořadí.
• Počínaje verzí MySQL 3.23.12 můžete MySQL instruovat, který index by měl být použit při získávání informací z tabulky. Tato funkce je užitečná, pokud příkaz EXPLAIN (který zobrazuje informace o struktuře a pořadí dotazu SELECT) naznačuje, že MySQL používá nesprávný index. Zadáním hodnoty indexu v USE INDEX (seznam_klíčů) můžete přinutit MySQL, aby k vyhledávání záznamu používala pouze jeden ze zadaných indexů. Alternativní příkaz IGNORE INDEX (seznam_klíčů) brání MySQL v používání tohoto konkrétního indexu. Výrazy USE/IGNORE KEY jsou synonyma pro USE/IGNORE INDEX .

Některé příklady:

Mysql> SELECT * FROM tabulka1,tabulka2 WHERE tabulka1.id=tabulka2.id; mysql> SELECT * FROM tabulka1 LEFT JOIN tabulka2 ON tabulka1.id=tabulka2.id; mysql> SELECT * FROM tabulka1 LEFT JOIN tabulka2 USING (id); mysql> SELECT * FROM tabulka1 LEFT JOIN tabulka2 ON tabulka1.id=tabulka2.id LEFT JOIN tabulka3 ON tabulka2.id=tabulka3.id; mysql> SELECT * FROM table1 USE INDEX (key1,key2) WHERE key1=1 AND key2=2 AND key3=3; mysql> SELECT * FROM table1 IGNORE INDEX (key3) WHERE key1=1 AND key2=2 AND key3=3;

Kromě běžných jednoduchých dotazů s jednoduchými podmínkami vám jazyk SQL umožňuje skládat docela složité výrazy, na které je někdy děsivý pohled, vypadají tak nesrozumitelně. Takové dotazy se ale moc často nevyskytují a další úroveň složitosti po obvyklém SELECTu je podle mého názoru příkaz JOIN. Pojďme přijít na to, jak s tím pracovat.

Nejprve se podívejme na nejjednodušší často používané dotazy.

SELECT COUNT(*) FROM tabulka; //spočítat počet záznamů SELECT * FROM tabulka LIMIT 5,10; //počínaje 5. záznamem vybere 10 řádků. nejčastěji se používá pro navigaci po stránce SELECT * FROM tabulka ORDER BY num; //seřadí všechny záznamy z tabulky podle pole num SELECT * FROM tabulka WHERE year="1990"; //vyber záznam/záznamy, kde je hodnota roku 1990 SELECT * FROM tabulka WHERE jméno LIKE "%ova%"; //hledej v tabulce jméno, které obsahuje sekvenci "ova" SELECT DISTINCT name FROM tabulka; //pokud existují duplicitní pole, budou započítána pouze jednou SELECT * FROM jméno WHERE age IN (12,15,18); // zobrazí jména, která odpovídají polím s věkem 12,15,18 SELECT MAX(age) FROM table; //MAX/MIN - vybere položku s maximální nebo minimální hodnotou věku

Nyní přejděme přímo k příkladům JOIN:

Velmi oblíbeným úkolem, kde se používá příkaz JOIN, je spojení několika tabulek, které mají nějakou společnou vlastnost. Podívejme se na příklad. Řekněme, že máme dva stoly. První obsahuje údaje o jménu a adrese uživatele a má také sloupec user_id. Ve druhém sloupci máme také sloupec user_id a sloupec s domovským indexem uživatele.

SELECT jméno, adresa FROM tabulka1 LEFT JOIN tabulka2 ON tabulka1.id_uzivatele=tabulka2.id_uzivatele;

Aby to bylo ještě přehlednější, lze tento dotaz přepsat klasickým způsobem:

SELECT tabulka1.název, tabulka1.adresa, tabulka2.index FROM tabulka1, tabulka2 WHERE tabulka1.id_uzivatele = tabulka2.id_uzivatele;

Tímto způsobem můžete získat data z několika tabulek, a pokud existuje další podmínka, přidáme je také:

SELECT jméno, adresa FROM tabulka1 LEFT JOIN tabulka2 ON tabulka1.id_uživatele=tabulka2.id_uživatele WHERE jméno = "Vasya";

Pokud se ponoříme do teorie, dodáme, že existují následující možnosti JOIN:

Zde by bylo vhodné nakreslit slavné kruhy:

VNITŘNÍ SPOJENÍ

vrátí průnik dvou množin

SELECT t1.name, t2.city FROM Table1 t1 INNER JOIN Tabulka2 t2 ON t1.key2 = t2.key2;

Odrazem INNER JOIN je OUTER JOIN. Máme k dispozici tři typy OUTER JOIN - FULL, LEFT a RIGHT. Slovo OUTER není povinné.

PLNÉ PŘIPOJENÍ

Kombinuje dvě sady

FULL JOIN vrátí VŠECHNY záznamy z tabulek table a table2, bez duplicitních dat. Pokud nejsou k dispozici žádná data, bude nahrazena hodnotou NULL.

PŘIPOJIT SE VLEVO

Vrátí data z levé tabulky i data z pravé, která se protíná s levou.

Pokud není dostatek dat z pravé tabulky, bude nahrazena hodnotou NULL.

RIGHT JOIN – jak asi chápete, vrátí všechny hodnoty z pravé tabulky a protínající se data zleva.

Výjimky

Pokud potřebujeme data z první tabulky, pro kterou nejsou žádná data ve správné, pak stačí přidat klauzuli WHERE

SELECT t1.name, t2.city FROM Table1 t1 LEFT JOIN Tabulka2 t2 ON t1.key2 = t2.key2 WHERE t2.key2 IS NULL;

Více JOINů

Pomocí JOIN se můžete připojit nejen ke 2 stolům, ale k libovolnému počtu. Pokud potřebujete spojit 2 stoly, budete potřebovat 2 příkazy JOIN. Ale neměli byste jít do extrému a připojit se k hromadě tabulek, to vše bude mít významný dopad na výkon, takže někdy je lepší spustit několik poddotazů. Příklad spojení tří tabulek:

SELECT t1.Jméno, t2.Město, t3.Profese FROM Tabulka1 t1 INNER JOIN Tabulka2 t2 ON t1.key2 = t2.key2 INNER JOIN Tabulka3 t3 ON t1.key3 = t3.key3;

Doufám, že vám uvedené jednoduché příklady JOIN pomohly pochopit tento operátor.

Důvodem napsání tohoto článku byla debata v jedné z linkedin skupin související s MySQL a také komunikace s kolegy a lidmi z Habro :-)

V tomto článku jsem chtěl napsat, co jsou JOINy ​​v MySQL obecně a jak s nimi můžete optimalizovat dotazy.

Co jsou JOINy ​​v MySQL

V MySQL se termín JOIN používá mnohem šířeji, než byste si mysleli. Zde může být JOIN nazýván nejen dotazem, který kombinuje výsledky z více tabulek, ale také dotazem do jedné tabulky, například SELECT na jedné tabulce je také spojením.

Důvodem je to, že algoritmus provádění spojení v MySQL je implementován pomocí vnořených smyček. Tito. Každý následující JOIN je další vnořená smyčka. Pro provedení dotazu a vrácení všech záznamů, které splňují podmínku, provede MySQL smyčku a paralelně prochází záznamy první tabulky, přičemž kontroluje shodu s podmínkami popsanými v těle požadavku, když jsou nalezeny záznamy, které splňují podmínek, vnořená smyčka přes druhou tabulku hledá záznamy, které odpovídají první a splňují podmínky kontroly atd. .d.

Příklad běžného dotazu s INNER JOIN

VYBRAT
*
Z
Tabulka1
VNITŘNÍ SPOJENÍ
Tabulka2 ON P1(Tabulka1,Tabulka2)
VNITŘNÍ SPOJENÍ
Tabulka3 ON P2(Tabulka2,Tabulka3)
KDE
P(Tabulka1,Tabulka2,Tabulka3).

Kde P - podmínky pro slučování tabulek a filtrů v podmínce WHERE.

Můžete si představit následující pseudokód pro provedení takového požadavku.

PRO každý řádek t1 v tabulce 1 (
IF(P(t1)) (
PRO každý řádek t2 v tabulce 2 (
IF(P(t2)) (
PRO každý řádek t3 v tabulce 3 (
IF P(t3) (
t:=t1||t2||t3; VÝSTUP t;
}
}
}
}
}
}

* Tento zdrojový kód byl zvýrazněn pomocí Zvýrazňovače zdrojového kódu.

Kde konstrukce t1||t2||t3 znamená zřetězení sloupců z různých tabulek.

Pokud dotaz obsahuje OUTER JOIN, například LEFT OUTER JOIN

VYBRAT
*
Z
Tabulka1
PŘIPOJIT SE VLEVO
Tabulka2 PŘIPOJIT VLEVO Tabulka3 NA P2(Tabulka2,Tabulka3)
ON P1(Tabulka1,Tabulka2)
KDE
P(Tabulka1,Tabulka2,Tabulka3)

* Tento zdrojový kód byl zvýrazněn pomocí Zvýrazňovače zdrojového kódu.

Algoritmus pro provedení tohoto MySQL dotazu pak bude vypadat nějak takto

PRO každý řádek t1 v T1 (
BOOL f1:=NEPRAVDA;
PRO každý řádek t2 v T2 tak, že P1(t1,t2) (
BOOL f2:=NEPRAVDA;
PRO každý řádek t3 v T3 tak, že P2(t2,t3) (
IF P(t1,t2,t3) (
t:=t1||t2||t3; VÝSTUP t;
}
f2=PRAVDA;
f1=PRAVDA;
}
POKUD (!f2) (
IF P(t1,t2,NULL) (
t:=t1||t2||NULL; VÝSTUP t;
}
f1=PRAVDA;
}
}
POKUD (!f1) (
IF P(t1,NULL,NULL) (
t:=t1||NULL||NULL; VÝSTUP t;
}
}
}

* Tento zdrojový kód byl zvýrazněn pomocí Zvýrazňovače zdrojového kódu.

Jak tedy vidíme, JOINy ​​jsou pouze skupinou vnořených smyček. Proč se tedy do MySQL připojují také UNION a SELECT a dotazy s SUBQUERY?

Optimalizátor MySQL se snaží přivést dotazy do podoby, ve které je pro něj pohodlnější zpracovávat a provádět dotazy podle standardního schématu.

S SELECT je vše jasné - jen smyčka bez vnořených smyček. Všechny UNIONy se provedou jako samostatné dotazy a výsledky se přidají do dočasné tabulky a s touto tabulkou pak MySQL pracuje, tzn. procházení záznamů v něm. Je to stejný příběh s Subquery.

Například přenesením všeho do jedné šablony MySQL přepíše všechny dotazy RIGHT JOIN na ekvivalenty LEFT JOIN.

Ale strategie provádění dotazů prostřednictvím vnořených smyček ukládá určitá omezení, například kvůli tomuto schématu MySQL nepodporuje provádění dotazů FULL OUTER JOIN.

Ale výsledek takového dotazu lze získat pomocí UNION dvou dotazů na LEFT JOIN a RIGHT JOIN
Ukázku samotného požadavku naleznete v odkazu na wiki.

PŘIPOJTE SE k plánu provádění dotazů

Na rozdíl od jiných RDBMS MySQL negeneruje bajtový kód pro provedení dotazu, místo toho MySQL generuje seznam příkazů ve stromové podobě, které se při provádění dotazu řídí motor pro provádění dotazu.
Tento strom vypadá takto a nazývá se „strom doleva“

Na rozdíl od vyvážených stromů (Bushy plán), které se používají v jiných DBMS (například Oracle)

Optimalizace JOIN

Nyní přejděme k nejzajímavější části – optimalizaci spojování.
Optimalizátor MySQL, konkrétně část, která je zodpovědná za optimalizaci JOINů, vybírá pořadí, ve kterém budou sloučeny existující tabulky, protože můžete získat stejný výsledek (datovou sadu) s různým pořadím tabulek při sloučení. Optimalizátor MySQL vyhodnotí náklady různých plánů a vybere ten s nejnižšími náklady. Jednotkou vyhodnocení je operace jednoho čtení 4 kilobajtové stránky dat z náhodného umístění na disku.

Pro vybraný plán můžete zjistit cenu provedením příkazu

ZOBRAZIT STAV RElace, JAKO "Náklady_posledního_dotazu";

Odhad je založen na statistice: počtu paměťových stránek obsazených tabulkou a/nebo indexy pro tuto tabulku, mohutnost (počet jedinečných hodnot) indexů, délka záznamů a indexů, jejich rozložení atd. Při svém vyhodnocování optimalizátor nepředpokládá, že nějaké části skončí v mezipaměti, optimalizátor předpokládá, že každá operace čtení je přístupem na disk.

Někdy analyzátor-optimalizátor nemůže analyzovat všechny možné plány provádění a vybere ten špatný. Pokud máme například INNER JOIN na 3 stolech, pak má analyzátor 3 možné možnosti! = 6, a pokud slučujeme 10 tabulek, pak je již 10 možných možností! = 3628800... MySQL nedokáže analyzovat tolik možností, takže v tomto případě používá chamtivý vyhledávací algoritmus.

Ale neměli byste tento hack aplikovat na všechny dotazy a doufat, že optimalizujete shody a ušetříte čas při sestavování plánu provádění dotazů s optimalizátorem a přidávání STRAIGH_JOIN ke všem dotazům s připojením, protože Změny a slučování dat, které je nyní optimální, mohou časem přestat být optimální a dotazy se pak začnou velmi zpožďovat.

Také, jak již bylo zmíněno výše, výsledky spojení se umisťují do dočasných tabulek, proto je často vhodné použít „odvozenou tabulku“, ve které si na výběr klademe všechny podmínky, které potřebujeme, a také určujeme LIMIT a pořadí řazení. V tomto případě se zbavíme redundance dat v dočasné tabulce a také seřadíme v rané fázi (na základě výsledku jednoho vzorku, nikoli konečného slepení, což zmenší velikost záznamů, které budou tříděny). ).

Standardní příklad výše popsaného přístupu. Jednoduchý výběr pro vztah mnoho k mnoha: novinky a štítky k němu.

VYBRAT
t.tid, t.description, n.nid, n.title, n.extract , n.modtime
Z
VYBRAT
n.nid
Z
novinky n
KDE
n. typ = 1321
AND n.publikováno = 1
Stav AND = 1
OBJEDNAT PODLE
n.modtime DESC
OMEZIT
200
) jako novinky
VNITŘNÍ SPOJENÍ
novinky n ON n.nid = novinky.nid
VNITŘNÍ SPOJENÍ
news_tag nt ON n.nid = nt.nid
VNITŘNÍ SPOJENÍ
tagy t ON nt.tid = t.tid

* Tento zdrojový kód byl zvýrazněn pomocí Zvýrazňovače zdrojového kódu.

A na závěr malý problém, na který se občas při pohovorech ptám :-)

Existuje zpravodajský blogovací web. Existují takové entity, jako jsou zprávy a komentáře k nim.

Úkolem je napsat dotaz, který zobrazí seznam 10 novinek určitého typu (zadaného uživatelem), publikací seřazených podle času v chronologickém pořadí a také u každé z těchto novinek zobrazí maximálně 10 nejnovějších komentářů, tzn. pokud je více komentářů, zobrazíme pouze posledních 10.

Vše je potřeba udělat v jedné žádosti. Ano, toto nemusí být nejlepší způsob a můžete navrhnout jiné řešení :-)

Štítky: Přidat štítky

V MySQL lze výběr pomocí JOIN provádět různými způsoby. Pokusíme se zvážit všechny tyto typy žádostí. Zde je seznam všech dotazů týkajících se JOIN:

1. VNITŘNÍ SPOJENÍ
2. PŘIPOJIT SE VLEVO
3. SPRÁVNÉ PŘIPOJENÍ SE
4. RIGHT JOIN bez průniku s levou tabulkou
5. PLNÝ VNĚJŠÍ
6. FULL OUTER kde je levá nebo pravá tabulka prázdná

A zde je ilustrace těchto typů JOIN:

K článku přiložím soubory z naší stránky, mezi kterými bude join.php ve kterém zobrazím všechny záznamy pomocí různých operátorů JOIN.

VNITŘNÍ SPOJENÍ

Začneme tímto operátorem INNER JOIN, protože tento operátor se standardně spustí, pokud do dotazu jednoduše napíšete JOIN. Tento operátor vybere všechny záznamy ze dvou tabulek, kde je splněna podmínka následující po operátoru ON. Máme dvě tabulky Soubory a Zprávy:

Tabulka zpráv:

Dotaz s JOIN bude následující:

VYBRAT * ZE ZPRÁV VNITŘNÍ PŘIPOJENÍ K souborům ZAPNUTO Messages.fid=Files.fid

V důsledku toho se zobrazí následující záznamy:

Tabulka souborů:

LEFT JOIN bude potřeba, když zobrazíme všechny záznamy zpráv a zda je či není připojený soubor, zkontrolujeme pomocí PHP.

LEFT JOIN bez průniku s pravou tabulkou

LEFT JOIN vrátí všechny záznamy z levé tabulky, kromě těch, jejichž fid se shoduje v pravé tabulce.

Tabulka zpráv:

Dotaz s LEFT JOIN bez průniků by byl:

VYBRAT * ZE ZPRÁV VLEVO PŘIPOJIT K souborům ZAPNUTO Messages.fid=Files.fid WHERE Files.fid JE NULL

Výsledkem je takový výběr:

Tabulka souborů:

RIGHT JOIN bude potřeba, když zobrazíme všechny připojené soubory, bez ohledu na to, zda jsou použity nebo ne, pouze všechny soubory.

PRAVÉ PŘIPOJENÍ bez křižovatek

RIGHT JOIN bez průniků zobrazí všechny záznamy z pravé tabulky, kromě těch, které mají průsečíky s levou tabulkou.

Tabulka zpráv:

Dotaz s RIGHT JOIN bez průniků by byl:

VYBRAT * ZE Messages RIGHT JOIN Files ON Messages.fid=Files.fid KDE Messages.fid JE NULL

Tímto způsobem získáme následující údaje:

střední	bodytext	fid	cesta
NULL	NULL	1	/files/1.png

RIGHT JOIN bude potřeba při zobrazení všech připojených souborů, které nejsou připojeny k žádným zprávám. Například pokud chceme zobrazit soubory, které se nepoužívají.

ÚPLNÉ VNĚJŠÍ PŘIPOJENÍ

Přestože SQL má FULL OUTER JOIN, MySQL tento operátor neexistuje. Faktem je, že takový operátor je pro server obrovskou zátěží. Nyní máme 3 soubory a 3 zprávy, přičemž 4 řádky jsou vytvořeny jako výsledek provedení požadavku. Nejsem si jistý, jestli je dobrý nápad napsat dotaz, který kombinuje dva dotazy, LEFT JOIN a RIGHT JOIN. Stále je však možné emulovat požadavek FULL OUTER JOIN.

Tabulka zpráv:

Emulace dotazu pomocí FULL OUTER JOIN by vypadala takto:

VYBRAT * ZE ZPRÁV VLEVO PŘIPOJIT Soubory ZAPNUTO Messages.fid = Soubory.fid VYBRAT UNION * ZE ZPRÁV PRAVÉ PŘIPOJIT Soubory ZAPNUTO Messages.fid = Soubory.fid

V tomto dotazu používáme operátor UNION ke spojení dvou dotazů LEFT JOIN a RIGHT JOIN.

V důsledku toho obdržíme následující záznamy:

střední	bodytext	fid	cesta
1	Test	2	/files/2.png
2	Ahoj	NULL	NULL
3	Ahoj	3	/files/3.png
NULL	NULL	1	/files/1.png

A zde je pro mě těžké říci, proč je vyžadován FULL OUTER JOIN. Ale jelikož je to v SQL, bude to asi potřeba později.

PLNÉ VNĚJŠÍ SPOJENÍ bez křižovatek

Další typ JOIN je ještě šílenější než jen FULL OUTER JOIN, a to FULL OUTER JOIN bez průniků. Nemohu ani navrhnout, kde lze tento typ JOIN použít. Protože v důsledku toho dostáváme soubory, které se nepoužívají, a zprávy bez souborů. A jak už asi tušíte ani tento operátor neexistuje v MySQL. Zbývá jej pouze emulovat pomocí dvou operátorů: LEFT JOIN bez výčtů a RIGHT JOIN bez průniků.

Emulace požadavku FULL OUTER JOIN bez průniků:

$sql = "VYBRAT * ZE ZPRÁV VLEVO PŘIPOJIT Soubory ZAPNUTO Messages.fid = Soubory.fid WHERE Files.fid JE NULL UNION SELECT * FROM Messages RIGHT JOIN Files ON Messages.fid = Files.fid WHERE Messages.fid JE NULL";

Ve výsledku (zdrojové tabulky jsou stejné jako v příkladu s FULL OUTER JOIN) dostaneme:

střední	bodytext	fid	cesta
2	Ahoj	NULL	NULL
NULL	NULL	1	/files/1.png

To je asi vše, v dalších lekcích začneme psát i složitější dotazy do více tabulek najednou.