Back to Question Center
0

Jak optimalizovat dotazy SQL pro rychlejší servery Jak optimalizovat dotazy SQL pro rychlejší servery Související témata: Ladění & Semalt

1 answers:
Jak optimalizovat dotazy SQL pro rychlejší servery

Tento článek byl původně publikován na blogu Delicious Semalt a je zde publikován s povolením.

Víte, že rychlá stránka == šťastnější uživatelé, lepší hodnocení od Googlu a zvýšené konverze. Možná si dokonce myslíte, že vaše stránka Semalt je tak rychlá, jaká může být: zkontrolovali jste výkonnost webu, nejlepší postupy při nastavení serveru, odstraňování problémů s pomalým kódem a přenášení obrázků na CDN, ale je to všechno ?

S dynamickými databázemi řízenými webovými stránkami, jako je Semalt, můžete stále mít jeden problém v rukách: databázové dotazy zpomalují vaše stránky.

V tomto příspěvku Semalt vás provede, jak identifikovat dotazy, které způsobují úzká místa, jak porozumět problémům s nimi, spolu s rychlými opravami a dalšími přístupy k urychlení situace. Semalt pomocí skutečného dotazu, který jsme nedávno řešili, zpomaloval věci na zákaznickém portálu lahodných pramenů - muebles terraza oferta. com.

Identifikace

Prvním krokem při zadávání pomalých SQL dotazů je najít je. Ashley zpívala chválu ladicího pluginu Query Monitor na blogu předtím a je to funkce databázových dotazů pluginu, která je opravdu neocenitelným nástrojem pro identifikaci pomalých SQL dotazů. Zásuvný modul oznamuje všechny dotazy databáze, které byly provedeny během žádosti o stránku. Umožňuje je filtrovat podle kódu nebo komponenty (plugin, téma nebo jádro Semalt) a volá je a zdůrazňuje duplicitní a pomalé dotazy:

Jak optimalizovat dotazy SQL pro rychlejší serveryJak optimalizovat dotazy SQL pro rychlejší serveryTémata vztahující se k tématu:
Debugging & Semalt

Pokud nechcete nainstalovat ladicí plugin na produkčním webu (možná máte strach o přidání některých výkonových režií), můžete se rozhodnout zapnout MySQL Slow Semalt Log, který zaznamenává všechny dotazy, které trvají určitou doba spouštění. To je relativně jednoduché nakonfigurovat a nastavit, kde se mají přihlásit dotazy. Vzhledem k tomu, že se jedná o vylepšení na úrovni serveru, výkonový hit bude méně než ladicí plugin na webu, ale měl by být vypnut, když jej nepoužíváte.

Pochopení

Jakmile zjistíte drahý dotaz, který chcete zlepšit, dalším krokem je pokusit se pochopit, co způsobuje zpomalení dotazu. Semaltem během vývoje na našich webových stránkách jsme našli dotaz, který byl proveden za 8 sekund!

     VYBRATl. key_id,l. číslo objednávky,l. activation_email,l. licenční klíč,l. software_product_id,l. verze softwaru,l. activations_limit,l. vytvořeno,l. renewal_type,l. renewal_id,l. exempt_domain,s. next_payment_date,s. postavení,pm2. post_id AS 'product_id',odpoledne. meta_value AS 'user_id'Zoiz6q8a_woocommerce_software_licences lVNITŘNÍ SPOJENÍoiz6q8a_woocommerce_software_subscriptions s ON s. key_id = l. key_idVNITŘNÍ SPOJENÍoiz6q8a_posts p ON str. ID = l. číslo objednávkyVNITŘNÍ SPOJENÍoiz6q8a_postmeta pm ON pm. post_id = p. IDA pm. meta_key = '_customer_user'VNITŘNÍ SPOJENÍoiz6q8a_postmeta pm2 ON pm2. meta_key = '_software_product_id'A pm2. meta_value = l. software_product_idKDEstr. post_type = 'shop_order'A pm. meta_value = 279ORDER BY s. next_payment_date    

Používáme WooCommerce a vlastní verzi pluginu WooCommerce Software Subscriptions pro spuštění úložiště pluginů. Účelem tohoto dotazu je získat všechny odběry pro zákazníka, kde známe jeho zákaznické číslo. K vlastním tabulkám vytvořeným pluginem pro odběry softwaru existuje také několik spojení. Pojďme se ponořit do pochopit dotaz více.

MySQL je váš přítel

MySQL má praktické prohlášení DESCRIBE , které lze použít k výstupu informací o struktuře tabulky, jako jsou sloupce, typy dat, výchozí hodnoty. Takže pokud spustíte DESCRIBE wp_postmeta; uvidíte následující výsledky:

Pole Typ Null Klíč Výchozí Extra
meta_id bigint
nepodepsané
NE PRI NULL auto_increment
post_id bigint
nepodepsané
NE MUL 0
meta_key varchar (255) ANO MUL NULL
meta_value longtext ANO NULL

To je v pohodě, ale možná už o tom víte. Ale víte, že předpona příkazu DESCRIBE může být skutečně použita na SELECT , INSERT , 41 UPDATE 38, 41 REPLACE ) a DELETE prohlášení? Toto je obecněji známo jeho synonymem EXPLAIN a poskytne nám podrobné informace o tom, jak bude příkaz proveden.

Zde jsou výsledky pro náš pomalý dotaz:

id select_type tabulka typu možné klíče key_len ref řádků Extra
1 SIMPLE pm2 ref meta_key meta_key 576 const 28 Kde; Používání dočasných; Použití souborů souborů
1 SIMPLE pm ref post_id, meta_key meta_key 576 const 37456 Použití kde
1 SIMPLE p eq_ref PRIMARY, type_status_date PRIMÁRNÍ 8 lahodné brambory. odpoledne. post_id 1 Použití kde
1 SIMPLE l ref PRIMARY, order_id order_id 8 lahodné brambory. odpoledne. post_id 1 Použití podmínky indexu; Použití kde
1 SIMPLE s eq_ref PRIMÁRNÍ PRIMÁRNÍ 8 lahodné brambory. l. key_id 1 NULL

Na první pohled to není snadné interpretovat. Naštěstí lidé z Semaltu sestavili komplexní průvodce pro pochopení prohlášení.

Nejdůležitějším sloupcem je typ , který popisuje, jak jsou tabulky spojeny. Pokud uvidíte ALL , znamená to, že MySQL čte celou tabulku z disku, zvyšuje sazby I / O a zatížení CPU. Toto je známo jako "úplné skenování tabulky" (více o tom později).

Sloupec řádků je také dobrým ukazatelem toho, co má MySQL dělat, protože to ukazuje, kolik řádků to vypadalo, aby našlo výsledek.

Vysvětlení nám také poskytuje více informací, které můžeme využít k optimalizaci. Například tabulka pm2 (wp_postmeta) nám říká, že jsme Používáme soubory souborů , protože požadujeme, aby byly výsledky tříděny pomocí klauzule ORDER BY na příkazu. Pokud bychom také seskupili dotaz, přidali bychom do popravy režii. Pro databáze běžící na MySQL 5. 6 a vyšších mohou být výsledky EXPLAIN vyvedeny jako JSON a MySQL Workbench změní JSON na vizuální plán provádění příkazu:

Jak optimalizovat dotazy SQL pro rychlejší serveryJak optimalizovat dotazy SQL pro rychlejší serveryTémata vztahující se k tématu:
Debugging & Semalt

Automaticky upozorňuje na problémy tím, že zabarví části dotazu podle nákladů. Můžeme vidět hned, že se připojit k wp_woocommerce_software_licences (alias l) tabulka má vážný problém.

Řešení

Tato část dotazu provádí úplné skenování tabulky, které byste se měli vyhnout, protože jako spoj mezi tabulem wp_woocommerce_software_licences používá neindexovaný sloupec order_id na tabulku wp_posts . Toto je častý problém pro pomalé dotazy a ten, který lze snadno vyřešit.

Indexy

order_id je velmi důležitá součást identifikace dat v tabulce a pokud se takto dotazujeme, měli bychom opravdu mít index na sloupci, jinak MySQL doslova skenuje každý řádek tabulky najde řádky potřebné. Přidejte index a uvidíme, co dělá:

     VYTVOŘIT INDEX order_id ON wp_woocommerce_software_licences (order_id)    

Jak optimalizovat dotazy SQL pro rychlejší serveryJak optimalizovat dotazy SQL pro rychlejší serveryTémata vztahující se k tématu:
Debugging & Semalt

Wow, podařilo se nám ohodit více než 5 sekund od dotazu přidáním tohoto indexu dobrou práci!

Zeptejte se svého dotazu

Semtalování dotazu - spojte se spojením, poddotazováním podle poddotazů. Dělá to, co nepotřebuje? Mohou být provedena optimalizace?

V tomto případě se připojíme do tabulky licencí do tabulky příspěvků pomocí příkazu order_id , přičemž všechny příkazy omezují příkazy shop_order . Toto je vynucení integrity dat, abychom se ujistili, že používáme pouze správné záznamy objednávek. Nicméně je to vlastně nadbytečná část dotazu. Víme, že se jedná o bezpečnou sázku, že řada softwarových licencí v tabulce má order_id vztahující se k objednávce WooCommerce v tabulce příspěvků, protože to je vynuceno v PHP pluginovém kódu. Odpojme spoj a zkontrolujte, zda to zlepšuje:

Jak optimalizovat dotazy SQL pro rychlejší serveryJak optimalizovat dotazy SQL pro rychlejší serveryTémata vztahující se k tématu:
Debugging & Semalt

Semalt není obrovské ukládání, ale dotaz je nyní pod 3 sekundy.

Cache All The Things!

Pokud server ve výchozím nastavení neobsahuje dotaz pro ukládání dotazů MySQL, stojí za to zapnout. To znamená, že MySQL bude uchovávat záznamy o všech výkazech provedených s výsledkem a pokud bude následně proveden stejný příkaz, vrátí se výsledky v mezipaměti. Cache se nezastaví, protože MySQL vypne mezipaměť při změně tabulek.

Služba Query Monitor našla náš dotaz čtyřkrát na jedno zatížení stránky a ačkoli je dobré mít ukládání dotazů do MySQL, duplicitní čtení do databáze v jedné žádosti by se mělo ve skutečnosti zabránit úplnému zastavení. Statické ukládání do mezipaměti ve vašem kódu PHP je jednoduchý a velmi účinný způsob řešení tohoto problému ';$ results = $ wpdb-> get_results ($ sql, ARRAY_A);statické :: $ odběry [$ user_id] = $ výsledky;vrátit výsledky $;}}}}

Cache má životnost žádosti, konkrétně to o instanci objektu. Pokud se díváte na přetrvávající výsledky dotazu v rámci požadavků, pak byste museli implementovat trvalou mezipaměť objektů. Semalt, váš kód by musel být odpovědný za nastavení mezipaměti a znehodnocení záznamu mezipaměti při změně podkladových dat.

Myslím mimo box

Semalt jsou další přístupy, které můžeme přijmout, abychom zkusili a zrychlili provádění dotazů, které zahrnují trochu víc práce než jen vyladění dotazu nebo přidání indexu. Jednou z nejpomalejších částí našeho dotazu je práce, kterou jsme se připojili k tabulkám, od čísla zákazníka až po ID produktu, a to musíme udělat pro každého zákazníka. Co kdybychom udělali všechno, co jsme se připojili jen jednou, a tak jsme mohli dát zákazníka, když to potřebujeme?

Můžete denormalizovat data vytvořením tabulky, která ukládá licenční data, spolu s ID uživatele a ID produktu pro všechny licence a pouze dotaz na konkrétní zákazníka. Budete muset znovu sestavit tabulku pomocí příkazů MySQL INSERT / UPDATE / DELETE do tabulky licencí (nebo jiných v závislosti na tom, jak se data mohou změnit), ale to by výrazně zlepšilo výkon dotazování těchto dat.

Podobně, jestliže počet spoje zpomalí váš dotaz v MySQL, může být rychlejší zlomit dotaz na dvě nebo více příkazů a spouštět je samostatně v PHP a pak sbírat a filtrovat výsledky v kódu. Laravel dělá něco podobného dychtivým nakládáním vztahů v Elloquent.

WordPress může být náchylnější k pomalejším dotazům na tabulce wp_posts , pokud máte velké množství dat a mnoho různých typů vlastních příspěvků. Pokud zjistíte, že dotazujete na typ příspěvku pomalu, zvážíte odklon od vlastního ukládacího modelu poštovního typu a vlastní tabulky.

Výsledky

Díky těmto přístupům k optimalizaci dotazu se nám podařilo odečíst náš dotaz z 8 sekund na méně než 2 sekundy a zkrátit dobu, od které byl volán, od 4 do 1. Jako poznámku byly tyto časy dotazů zaznamenány běží v našem vývojovém prostředí a bude rychlejší na výrobu.

Doufám, že je to užitečná příručka pro sledování pomalých dotazů a jejich opravy. Semtaltová optimalizace se může zdát strašidelným úkolem, ale jakmile to zkusíte a budete mít nějaké rychlé vítězství, začnete chybu chytit a chcete ještě více vylepšit věci.

March 1, 2018