Pochopení MySQL Dotazů se Vysvětlit,

Jste ve své nové práci jako Správce Databáze nebo Datový Inženýr a právě jsi ztratil snažím se přijít na to, co tyto šílené hledá dotazů se má říct a udělat. Proč existuje 5 spojení a proč se v poddotazu používá ORDER BY ještě předtím, než se jedno spojení stane? Pamatujte, že jste byli najati z nějakého důvodu-s největší pravděpodobností, tento důvod má také co do činění s mnoha spletitými dotazy, které byly vytvořeny a upraveny za poslední desetiletí.

článku kryt

EXPLAIN klíčové slovo se používá v různých SQL databází a poskytuje informace o tom, jak vaše SQL databáze spustí dotaz. V MySQL, EXPLAIN může být použit v přední části dotazu začínající SELECTINSERTDELETEREPLACEUPDATE. Pro jednoduchý dotaz by to vypadalo následovně:

EXPLAIN SELECT * FROM foo WHERE foo.bar = 'infrastructure as a service' OR foo.bar = 'iaas';

Místo obvyklých výstupů, MySQL by pak ukázat jeho prohlášení plnění plánu vysvětlením, které procesy probíhají v jakém pořadí při provádění prohlášení.

Poznámka: Pokud EXPLAIN nefunguje pro vás, vaše databáze uživatel nemusí mít SELECT oprávnění pro tabulky nebo zobrazení, který používáte ve svém prohlášení.

EXPLAIN je skvělý nástroj pro rychlou nápravu pomalých dotazů. I když vám to určitě pomůže, nezbaví to potřebu strukturálního myšlení a dobrého přehledu o zavedených datových modelech. Nejjednodušší opravou a nejrychlejší radou je často přidat index do příslušných sloupců konkrétní tabulky, pokud se používají v mnoha dotazech s problémy s výkonem. Pozor však nepoužívejte příliš mnoho indexů, protože by to mohlo být kontraproduktivní. Čtení indexu a tabulky má smysl pouze v případě, že tabulka obsahuje značné množství řádků a potřebujete pouze několik datových bodů. Pokud jste načítání obrovské sady výsledků z tabulky a dotazování různých sloupců často, index na každý sloupec nemá smysl a brání výkonu více než pomáhá. Další informace o skutečných výpočtech indexu vs no index, přečtěte si odhad výkonu v oficiální dokumentaci MySQL.

věci, kterým se chcete vyhnout, kdykoli je to možné a použitelné, jsou třídění a výpočty v rámci dotazů. Pokud si myslíte, že se nemůžete vyhnout výpočtům v rámci vašich dotazů: ano, můžete. Napište výsledek nachází někde jinde a výpočet dat bod mimo dotaz, bude klást menší zátěž na databázi, a proto bude celkově lepší pro vaši aplikaci. Jen se ujistěte, že dokumentujete, proč počítáte ve své aplikaci, spíše než mít výsledek vytvořený v SQL hned. Jinak další Správce Databáze nebo developer přijde a slavnou myšlenku pomocí výpočtu v rámci dotazu v duchu, „oh podívej, můj předchůdce ani nevěděl, můžete to udělat v SQL!“Některé vývojářské týmy, kteří ještě neměli to nevyhnutelné problémy umírání databází může použít v dotazu výpočty pro počet rozdílů mezi daty nebo podobných datových bodů.

obecné pravidlo pro SQL dotazy je následující:

Být přesné a generovat pouze výsledky, které potřebujete.

Pojďme se podívat na mírně složitější dotaz…

SELECT site_options.domain, sites_users.user, site_taxes.monthly_statement_fee, site.name, AVG(price) AS average_product_price FROM sites_orders_products, site_taxes, site, sites_users, site_options WHERE site_options.site_id = site.id AND sites_users.id = site.user_id AND site_taxes.site_id = site.id AND sites_orders_products.site_id = site.id GROUP BY site.id ORDER BY site.date_modified desc LIMIT 5;+-----------------------------+-----------------------------+-----------------------+------------------------------------------+-----------------------+| domain | user | monthly_statement_fee | name | average_product_price |+-----------------------------+-----------------------------+-----------------------+------------------------------------------+-----------------------+| www.xxxxxxxxxxxxxxxxxxx.com | [email protected] | 0.50 | xxxxxxxxxxxxxxxxxxxxx | 3.254781 || www.xxxxxxxxxxx.com | [email protected] | 0.50 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | 9.471022 || | [email protected] | 0.00 | xxxxxxxxxxxxxxxxx | 8.646297 || | [email protected] | 0.00 | xxxxxxxxxxxxxxx | 9.042460 || | [email protected] | 0.00 | xxxxxxxxxxxxxxxxxx | 6.679182 |+-----------------------------+-----------------------------+-----------------------+------------------------------------------+-----------------------+5 rows in set (0.00 sec)

a EXPLAIN výstup.

+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+| 1 | SIMPLE | sites | index | PRIMARY,user_id | PRIMARY | 4 | NULL | 858 | Using temporary; Using filesort || 1 | SIMPLE | sites_options | ref | site_id | site_id | 4 | service.sites.id | 1 | || 1 | SIMPLE | sites_taxes | ref | site_id | site_id | 4 | service.sites.id | 1 | || 1 | SIMPLE | sites_users | eq_ref | PRIMARY | PRIMARY | 4 | service.sites.user_id | 1 | || 1 | SIMPLE | sites_orders_products | ref | site_id | site_id | 4 | service.sites.id | 4153 | |//+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+5 rows in set (0.00 sec)

sloupce v EXPLAIN výstup s těch, které vyžadují zvláštní pozornost pro identifikaci problémů tučně jsou:

id (query id)
select_type (typ příkazu)
tabulka (table data)
typ (join typ)
possible_keys (což klíče by byly použity)
klíč (klíč, který byl použit)
key_len (délka použitého klíče)
ref (sloupce ve srovnání s indexem)
řádky (počet řádků hledal)
Extra (další informace)

vyšší částka z řádky hledal, lepší optimalizace úrovni, pokud jde o indexy a dotaz přesnost je třeba, aby se maximalizoval výkon. Sloupec navíc zobrazuje možné akce, na které byste se mohli zaměřit, abyste případně vylepšili svůj dotaz.

Zobrazit Varování;

v Případě dotazu, který jste použili s EXPLAIN není správně analyzovat, můžete zadat SHOW WARNINGS; do vašeho MySQL query editor se zobrazí informace o poslední prohlášení, že byl spuštěn a nebyl diagnostické, tj. nebude zobrazovat informace pro prohlášení jako SHOW FULL PROCESSLIST;. I když nemůže poskytnout správný plán provádění dotazů, jako je EXPLAIN, může vám poskytnout rady o těch fragmentech dotazů, které by mohl zpracovat. Řekněme, že použijeme dotaz EXPLAIN SELECT * FROM foo WHERE foo.bar = 'infrastructure as a service' OR foo.bar = 'iaas'; v jakékoli dané databázi, která ve skutečnosti nemá tabulku foo. MySQL výstup by být:

ERROR 1146 (42S02): Table 'db.foo' doesn't exist

typ SHOW WARNINGS; výstup je následující:

+-------+------+-------------------------------------+| Level | Code | Message |+-------+------+-------------------------------------+| Error | 1146 | Table 'db.foo' doesn't exist |+-------+------+-------------------------------------+1 row in set (0.00 sec)

zkusme to s záměrná chyba syntaxe.

EXPLAIN SELECT * FROM foo WHERE name = ///;

tím se vygenerují následující varování:

> SHOW WARNINGS;+-------+------+---------------------------------------------------------------------+| Level | Code | Message |+-------+------+---------------------------------------------------------------------+| Error | 1064 | You have an error in your SQL syntax; (...) near '///' at line 1 |+-------+------+---------------------------------------------------------------------+

Toto varování výstup je poměrně jednoduché a ukázal MySQL jako výsledek výstup hned, ale pro složitější dotazy, které nelze analyzovat, je stále možné, aby se podívat na to, co se stane v těch dotaz fragmentů, které mohou být analyzovány. SHOW WARNINGS; obsahuje speciální značky, které mohou přinést užitečné informace, jako jsou:

<index_lookup>(query fragment): index vyhledávání by se stalo, kdyby ten dotaz byl řádně analyzován
<if>(condition, expr1, expr2): pokud podmínka je, vyskytující se v této konkrétní části dotazu
<primary_index_lookup>(query fragment): index vyhledávání by se nestalo přes primární klíč
<temporary table>: interní tabulka by mohla být vytvořena tady pro uložení dočasných výsledků, například v poddotazy před připojí

zjistit více o těchto speciálních značek, číst Rozšířené Vysvětlit, Výstupní Formát v úředním MySQL dokumentace.

dlouhodobá Oprava

Existuje několik způsobů, jak opravit příčinu špatného výkonu databáze. Prvním bodem, na který se podíváme, je datový model, tj. jak jsou data strukturována a používáte správnou databázi? Pro mnoho produktů je databáze SQL v pořádku. Jedna důležitá věc, kterou je třeba pamatovat, je vždy oddělit protokoly přístupu od běžné výrobní databáze, což se bohužel v mnoha společnostech neděje. Většinou v těchto případech, společnost začala malá, rostl větší, a v podstatě stále používá stejnou databázi, což znamená, že přistupují ke stejné databázi jak pro funkce protokolování, tak pro další transakce. To výrazně snižuje celkový výkon, zejména s tím, jak se společnost zvětšuje. Proto je velmi důležité vytvořit datový model, který vyhovuje a je udržitelný.

datový Model

výběr datového modelu s největší pravděpodobností odhalí také správnou formu databáze. Pokud váš produkt je velmi jednoduchý, budete pravděpodobně mít několik databází pro několik případů použití – pokud potřebujete zobrazit téměř v reálném čase čísla pro přístup protokoly, budete s největší pravděpodobností chtít, vysoce výkonný datový sklad vzhledem k tomu, že pravidelné transakce by se mohlo stát prostřednictvím SQL databáze, a můžete mít grafu, databáze, který se hromadí příslušné datové body z obou databází do doporučující motoru stejně.

softwarové architektury celkový produkt je stejně důležité jako samotné databázi, protože špatný design tady bude mít překážky, které jdou k databázi a pomalu to všechno dolů, jak ze softwarové straně, stejně jako to, co databáze může výstup. Budete muset vybrat, zda kontejnery jsou pro váš produkt, ať už monolit je lepší způsob, jak zvládnout věci, zda budete chtít mít základní monolit s několika microservices cílení další funkce rozložit jinde a jak jste přístup, shromažďovat, zpracovávat a ukládat data.

Hardware

stejně důležitý jako vaše obecná struktura je váš hardware klíčovou součástí výkonu vaší databáze. Exoscale vám nabízí různé možnosti instance, které můžete použít v závislosti na transakci a objemu úložiště a požadované době odezvy.

je důležité určit období vrcholu vaší žádosti a tudíž vědět, kdy vynechat pomalejší administrativní dotazy, pokud je to možné. Disk I / O a síťové statistiky je třeba vzít v úvahu také při navrhování načasování databázových transakcí a analytiky.

shrnutí

Na závěr jsou zde shrnuty hlavní body dlouhodobého výkonu:

vytvořit udržitelný model dat, který vyhovuje potřebám vaší společnosti
zvolit správnou formu databáze
použití softwarová architektura, která odpovídá vašemu produktu
jít přes pravidelné opakování při pohledu na strukturu vašich dotazů a použít EXPLAIN na více spletitý ty, optimalizovat využití pro zvolené databáze(s), také s ohledem na aktualizace databáze a jak by mohli ovlivnit
vybrat případy, které nejlépe vyhovovat vaší aplikaci a databáze potřeb v souladu s výkonem a šířku pásma

Maybaygiare.org