Domov › Služby › Optimalizace sql dotazů mysql. Zlepšení efektivity MySQL. Optimalizace SQL dotazů. Použijte vertikální separaci

Optimalizace sql dotazů mysql. Zlepšení efektivity MySQL. Optimalizace SQL dotazů. Použijte vertikální separaci

Zveřejnil to Nikolay Korotkov
Datum: 8. prosince 2012 ve 14:04

K čemu to všechno je? Co to ovlivňuje? Jak z toho udělat realitu? Na všechny tyto otázky se pokusím dát jasnou odpověď v tomto příspěvku!

A teď trochu pozadí. Obecně jsem nedávno dostal dopis o mém e-mailová adresa, následující obsah:

Během posledních 3 dnů střední úroveň zatížení vytvořené vaším účtem ******* , sestaveno 119% z přípustná úroveň vaše tarifní plán. Doporučujeme přejít na tarify VPS. Vezměte prosím na vědomí, že pokud jsou limity pravidelně překračovány, vyhrazujeme si právo zablokovat váš účet v souladu s článkem Smlouvy...

Oba jsou tady, pomyslel jsem si v tu chvíli! Souhlasíte, není moc příjemné dostávat takové dopisy. A od té doby tento druh Setkal jsem se s tímto problémem poprvé, dokážete si představit, jak jsem byl zmatený? Moje rozhořčení neznalo mezí! Co je to sakra za VPS? Můžu říct, že jsem si zvykl jen na jeden tarif, ale tady mi nabízejí přechod virtuální hosting, která je třikrát dražší. No, ne, kluci, myslím, že je ještě příliš brzy.

Píši zpětný dopis svému hostiteli a žádám ho, aby mi vysvětlil, proč se tak bojím, že moje pracovní zátěž přerůstá přes střechu? Vždyť můj blog je starý jen něco málo přes dva měsíce. A návštěvnost není velká. Obecně píšu, že jsem kategoricky proti přechodu na VPS, myslím si, že pro takové to není radno rané fázi vývoj zdroje a prosím upozorněte na mé chyby, co s nimi dělat a jak je v budoucnu kontrolovat!

Jako odpověď dostávám následující:

Vážený předplatiteli, teď vás neodpojíme, je to banální varování, ale říkají, že s tím musíme něco udělat. Problém přetížení nezávisí přímo na docházce, ale spíše závisí na správnou optimalizaci váš zdroj. Pro sledování zatížení jsme na ovládacím panelu zobrazili počítadlo, které se aktualizuje každých 10 minut:

No, díky za objasnění, říkám si v duchu. Jdu studovat problém. Po zadání dotazu „jak snížit zátěž na hosting“ na internetu jsem si uvědomil, že nejsem jediný a ve skutečnosti je problém docela relevantní. A dříve nebo později se to mnohé dotkne. Když jsem se s problémem podrobněji seznámil, uvědomil jsem si, že z této situace mám dvě cesty:

Vyhledejte pomoc od profesionálů (nezávislých pracovníků) tím, že jim zaplatíte určitou částku peníze, které budou vždy včas.
Pokuste se problém vyřešit sami.

Zvolil jsem tedy druhou možnost a řeknu vám upřímně, zatím jsem toho ani trochu nelitoval. Podařilo se mi snížit zátěž na hostování dvakrát až třikrát. Podívejte se sami:

Rozdíl je zřejmý! Nyní vám ukážu a řeknu vám, co jsem pro to udělal:

— optimalizace databáze mysql, což výrazně ovlivnilo snížení zátěže hostingu a zrychlení wordpressu;
— zbavili jsme se asi 8 nepotřebných pluginů.
— zrychlil WordPress úpravou několika souborů motivů pro můj blog.

Jelikož je materiál poměrně objemný, rozhodl jsem se ho rozdělit na tři části. V tomto článku se dozvíte, jak snížit zátěž vašeho hostingu optimalizací databáze. V příštím článku vám prozradím... A poslední článek bude na toto téma. Když jsem to všechno udělal se svým zdrojem, byl jsem šokován, jak se můj blog začal načítat! V porovnání s tím, co to bylo, začal létat.

Obecně platí, že materiál, který získáte z těchto tří příspěvků, bude prostě úžasný. Nenechte si to ujít!

Optimalizace databáze

Než začnete vyrábět různé akce s databází rozhodně to udělej záložní kopie . Aby bylo možné v případě problémů vše rychle obnovit. Databáze obsahuje celou historii vašeho zdroje, ukládá všechny záznamy na vašem blogu! Obecně vám radím, abyste si ukládali databázi každý den! Zabere vám to doslova 1 minutu, ale vždy budete klidně spát. Chápete, že se může stát cokoliv.

1. Vytvořte záložní kopii databáze

Pro snadné připojení k serveru a zpracování dat používám . Skvělá věc, jednoho dne o tomto klientovi napíšu samostatný příspěvek. V podstatě musíte jít na svůj server a najít záložku "Databáze" nebo "Databáze MySQL", něco takového. Každý server má databázi během přechodu, server může požadovat heslo. Musíte to mít. Při nákupu hostingu je poskytnuto heslo.

V důsledku toho byste měli skončit na stránce, jako je tato, phpMyAdmin:

Přejděte do databáze kliknutím na její název. Otevře se před vámi databázová tabulka (pro zvětšení klikněte):

Klikněte na "Exportovat" a "OK". Uložte si jej do počítače. To je vše, databáze je uložena, nyní ji můžeme začít optimalizovat. Upozorňujeme, že pokud má váš hosting pole „Uložit jako soubor“, nezapomeňte zaškrtnout políčko vedle něj! A také si pamatujte, kolik váží momentálně vaší databázi a poté uvidíte, kolik bude vážit po optimalizaci.

U mě to před optimalizací vážilo 26 Mb - to je HRŮZA, ale co teď? A nyní váží pouze 2 Mb! Dokážete si představit, kolik věcí zbytečný odpad obsahovala? Dokážete si představit zátěž, kterou to vytvořilo na serveru? Po optimalizaci databáze začal můj blog létat jako tryskové letadlo! Obecně platí, že poté, co provedete všechny níže popsané kroky, pocítíte významný rozdíl!

2. Zakažte revize příspěvků a nastavte minimální dobu uložení smazaných souborů v koši

Co je revize příspěvku? Když napíšete příspěvek na blog, WordPress automaticky po určité době uloží záložní kopii každého příspěvku do databáze, obecně se automaticky uloží. A teď si představte, když napíšete 50 blogových příspěvků? Kolik kopií příspěvků si ponecháte? Tohle je TĚŽKÉ! Během psaní příspěvku jste již provedli alespoň 10 automatických uložení!

Navíc, pokud smažete soubory, hromadí se ve vašem koši, což také načte databázi. Samozřejmě je dobré, když soubor okamžitě smažete z koše, ale často se stává, že na to mnoho lidí zapomene a někteří prostě zapomenou! A to je ach, jak to není dobré... Databáze roste, zatížení serveru je čím dál tím větší, blog se načítá pomaleji a pomaleji... Přemýšleli jste někdy, jaké důsledky to může mít?

Zde je hlavní část důsledků, ale ne všechny: pokles, častá odmítnutí, zhoršení stavu, snížení pozic ve výsledcích vyhledávačů... A pak si autor zoufá nad neopodstatněnými očekáváními. Chuť na blog se časem vytratí a je to! PÁD!

Proč to všechno říkám? Databáze musí být neustále monitorována a udržována v řádném stavu. Pochopte, že databáze je jako srdce blogu. Na konstantní zátěž srdce je zaplněné zbytečným svinstvem, časem to nevydrží a ZASTAVÍ! Myslím, že mi rozumíš? Dost tedy hororů a pojďme k optimalizaci databáze.

Otevřete tedy soubor wp-config.php, nachází se v kořenovém adresáři vašeho blogu, tzn. váš hosting/httpdocs nebo public_html (v závislosti na hostování)/wp-config.php. A vložte do něj dva řádky:

1 2	definovat ("WP_POST_REVISIONS" , nepravda) ; definovat ("EMPTY_TRASH_DAYS" , 1 ) ;

Řádek č. 1 zakazuje po revizi, řádek č. 2 znamená, kolik dní bude uloženo smazané soubory ve vašem košíku. Jak vidíte, dal jsem „1“, vy samozřejmě můžete zadat „0“, ale pokud se vám náhle z nedbalosti zachvěje ruka a kliknete na odkaz „smazat“, je vše V PRŮBĚHU!

A po 5-8 hodinách sezení u počítače věřte, že je to možné! Takže raději nechávám číslo „1“. Po smazání souboru je samozřejmě lepší koš okamžitě ručně vyprázdnit, ale i když to zapomenete udělat, po dni bude soubor z koše automaticky smazán! U mě to vypadá takto:

3. Odstraňte revize příspěvků

Pokud jsme v předchozím odstavci zakázali revize příspěvků, pak v tomto odstavci musíme odstranit všechny revize příspěvků, které se nashromáždily za celou dobu blogování. Pokud jste to nikdy neudělali, pak jste je neuvěřitelně zachovali velký počet! Pojďme na to. Zkopírujeme tento řádek:

Vraťme se k databázi MySQL, jak je popsáno v prvním odstavci. Přejděte na kartu SQL, vložte zkopírovaný řádek do pole a klikněte na „OK“:

Databáze se zeptá:

Odpovíme „OK“ a uvidíme, kolik zbytečných revizí obsahuje vaše databáze a jak dlouho trvalo zpracování požadavku. A každý čas dává svůj vlastní náklad:

Provedl jsem čištění před 3 dny, takže ještě nedostal žádné revize. Když jsem databázi vyčistil poprvé, bylo smazáno až 1800 lichých řádků! Dokážete si představit, kolik kopií nepotřebných příspěvků v něm bylo uloženo? Jdeme dál.

4. Optimalizace příspěvků ve wp-post

Složka wp-post obsahuje všechny blogové příspěvky. Úplně stejně jako v předchozím odstavci zkopírujte řádek:

OPTIMALIZOVANÁ TABULKA wp_posts;

A vložte jej do pole SQL dotaz. Klikněte na „OK“ a podívejte se:

To je vše, žádost je dokončena!

5. Vyčistěte wp-postmeta

Co přesně budeme uklízet? Složka wp-postmeta obsahuje:

— čas poslední úpravy některého z příspěvků. Nezáleží na tom, ale zatěžuje server;
— obsah předchozího (lidsky srozumitelného URL). Pokud jste se někdy změnili trvalý odkaz v libovolném příspěvku. Když jej pak změníte, nesmaže se, ale usadí se ve složce wp-postmeta a načte vaši databázi.

Děláme to samé, zkopírujeme tento kód:

Vložte jej do pole SQL dotazu a klikněte na OK. Podívejme se na výsledek:

6. Odstraňte spamové komentáře

To se provádí stejným způsobem, zkopírujte kód:

Vložte jej do pole dotazu SQL, klikněte na „OK“, podívejte se na výsledek:

Jak vidíte "0". Po dokončení tohoto požadavku zapomenete na spamové komentáře!

7. Odstraňte pingbacky

Pingbacky jsou upozornění, že někdo odkazuje na váš příspěvek nebo stránku. Tohle nepotřebujeme, je to zátěž navíc! Pojďme smazat!

8. Zakažte pingbacky

Z předchozího odstavce jsme zjistili, že pingbacky našemu zdroji nepřinášejí žádný užitek, ale pouze jej ucpávají. Pojďme je tedy úplně vypnout. Zkopírujeme tento kód:

AKTUALIZACE wp_posts p SET str. ping_status = "zavřeno"

No, jak se vám líbí tento úklid? Líbilo se ti to? Nyní se podívejte, kolik váží vaše databáze po optimalizaci? Zmenšila se znatelně velikost? Řekl jsem ti to! Podívejte se, jak se váš blog nabíjí! Musí létat! Ale to není pro dnešek vše. Nyní se podíváme na poslední bod, který také výrazně zlepší optimalizaci.

9. Nainstalujte plugin Optimize DB

O tomto pluginu jsem se již krátce zmínil. No, pojďme se blíže podívat na to, jak ji používat. Tento plugin, jak jste možná uhodli, pomáhá optimalizovat databázi! Stáhněte si archiv s pluginem do počítače a aktivujte jej:

To je vše, vaše databáze je dále optimalizována pomocí pluginu:

Po optimalizaci plugin deaktivujte, aby nezatěžoval váš zdroj. Obecně vám doporučuji provádět všechny výše uvedené akce jednou za měsíc, nebo i častěji. A pak se váš blog načte rychlostí blesku a zatížení serveru bude minimální.

A v další části příspěvku vám ukážu, jak nahradit některé nepotřebné pluginy kódy. Určitě vám nic neuteče. Toto bude silný příspěvek, po kterém bude váš server lehký jako pírko!

A tímto se s vámi rozloučím. To je pro dnešek vše, přeji vám úspěch a pamatujte, že jde o kolosální snížení zátěže vašeho zdroje. Ahoj všichni a brzy na viděnou.

A na závěr porce vtipů:

No a jak se vám článek líbí? Jsem si jist, že po přečtení a doporučeních s vaším zdrojem budete spokojeni! Těším se na vaše komentáře!

Líbil se vám článek? Sdílejte se svými přáteli!

Kniha pro každého komentátora!

Kniha obsahuje podrobný popis nejvíce efektivní metody propagujte svůj zdroj!

60 komentářů

Alexander 8. prosince 2012 15:18
A vím, proč se vaše pracovní zátěž tak zvýšila. Jen jsem se tu u vás usadil a neustále se něco učím. Co dělat, když jsou informace zde skvělé. Ale vážně, to je ono výše uvedené tipy Doporučuji všem blogerům, aby to udělali jako první. Udělal jsem to už dávno, takže spím klidně. A také plugin Optimize DB, to je obecně povinný atribut jakýkoli blog. Děkuji Kolyo, jako vždy je vše užitečné a relevantní. Ale moc se těším na další příspěvek. Tak neváhejte a pište
9. prosince 2012 16:19
Bojím se šťourat s databází, ale po instalaci a vyčištění pomocí pluginu WP-Cleanup se snížila z téměř 50 na 7 Mb. Blog se ve skutečnosti načítal mnohem rychleji.
9. prosince 2012 20:39
Přísně vzato, při operacích s databází se neptá samotná databáze (DBMS jsou obecně všechny akce stejné, na nic se neptá), ale klient, phpMySql.
Ohledně pingbacků: „Z předchozího odstavce jsme zjistili, že pingbacky našemu zdroji nepřinášejí žádný užitek, ale pouze jej ucpávají.“ - Přesně řečeno, nic jsme nezjistili.
Jednoduše jste řekli, aniž byste se hádali, že nejsou potřeba, to je vše. Ve skutečnosti mohou být užitečné, stačí tento nástroj použít k zamýšlenému účelu. Například, klíčové slovo « sémantický web„Znamená to pro tebe něco?
10. prosince 2012 08:36
Yuri 16. prosince 2012 23:49
Ahoj kamaráde!
Váš příspěvek je opravdu skvělý. Na internetu je napsáno tolik nesmyslů, že musíte hledat informace kousek po kousku. A tady jsem přišel a na vás je vše srozumitelné a srozumitelné. Právě jsem začal mít problém se zatížením serveru. Dále doporučuji nainstalovat plugin WP Super cache. Jen je potřeba to správně nakonfigurovat. Skvělý plugin! Možná jste o něm něco řekl v jiných příspěvcích, ale ještě jsem to nečetl. Spěchám přejít k druhé části optimalizace. Hodně štěstí tobě a tvému blogu
25. prosince 2012 11:40
28. ledna 2013 11:24
Dobré odpoledne Velmi zajímavé, ale co mám dělat s blogem na Bloggeru? Všechny pluginy pro WP nejsou vhodné pro Blogspot, optimalizační metody si musíte hledat sami na internetu.
S pozdravem Vadim.
Anton 2. dubna 2013 20:34
Děkuji, to je opravdu dobrý příspěvek. Mimochodem, po provedení bodu č. 3 - „4145 řádků bylo smazáno. (Požadavek trval 7,0269 sekund)"
14. července 2013 19:04
Zajímalo by mě, jestli existuje nějaký způsob, jak vyčistit databázi starých pluginů? Určitě tam také zůstaly nějaké jejich stopy?
14. července 2013 19:06
Pro pokračování: je to také velmi podobné vašemu textu zde dayafternight.ru/wordpress/baza-dannih-mysql-optimizacia
12. září 2013 12:57
Díky Nikolayi, nezbytná věc.
Vše je přístupné a přehledně napsané.
Byl již článek o kódech publikován?
12. září 2013 13:05
Nikolai se zapomněl zeptat, prosím, řekni mi to. Když jsem dělal optimalizaci, našel jsem v mém PhpMyAdmin nový databáze information_schema
Řekni mi, odkud to mohlo přijít?
PROTI v poslední době Vložil jsem pouze kód metrik Yandex.
Natalia Gegerová
Tomuto nevěnujte pozornost... Na většině moderní servery ona je! To je způsobeno vydáním MySQL verze 5.0 a vyšší...
INFORMATION_SCHEMA je virtuální databáze, který se generuje při startu serveru a obsahuje metadata všech databází, tzn. informace o struktuře databází. Je pouze pro čtení.
27. října 2013 01:06
oh, základ jsem vyčistil vaší metodou + vlastníma rukama, výsledky jsou zřejmé. Dříve databáze vážila 20 MB, nyní je to 5 MB
29. října 2013 23:34
Moc děkuji za článek. Dnes jsem také obdržel bug od hostitele. V důsledku těchto akcí se databáze o velikosti 25 MB stala 5.2. Existují 2 otázky, je třeba všechny tyto manipulace provádět pravidelně? A druhá otázka, nainstaloval jsem plugin, kliknu na optimalizovat, v důsledku toho je vedle každého řádku napsáno,
poznámka: Tabulka nepodporuje optimalizaci, místo toho provádí znovu vytvoření + analýzu
Nevypadá to, že je vše v pořádku?!
Prosím! Ano, všechny tyto manipulace dělám asi jednou za měsíc. Ale o pluginu zatím nemohu nic říct, zřejmě jste udělali něco špatně. Zkuste si o tom vyhledat informace na internetu. Ale jsou i příjemné akce. Na mém blogu jste zanechali 2100. komentář a za to máte nárok na cenu 100 rublů:
Pošlete mi číslo své wmr peněženky a já vám převedu peníze.
30. října 2013 13:27
Děkujeme, cena byla přijata. Jak jsem se dostal na vaše stránky?! Včera web opět přestal fungovat a na obrazovce se objevilo „Chyba při připojování k databázi“. Napsal jsem hostiteli, potvrdili to těžký náklad na MySQL a něco s tím udělat, ale prozatím jsme přešli na vyšší tarif. Okamžitě jsem začal hledat, co dělat a našel jsem váš článek, který mi 5x zmenšil databázi. Plugin, který zpočátku nechtěl fungovat, stále fungoval, ale hlavní problém, odstranění zbytečných dotazů, nebyl nikdy vyřešen. Plugin WP Super Cache již mám, ale ukládá do mezipaměti stránky, nikoli databázové dotazy. A tak jsem do čtyř hodin do rána hledal plugin, který by mi mohl pomoci s požadavky, a našel jsem ho. WP File Cache ukládá požadavky do mezipaměti, počet požadavků a MB paměti se výrazně sníží. Na stránkách, kde bylo dříve 40 požadavků a 35 MB, je nyní 9 a 12 MB. Jediná věc je, že se zdá, že rychlost načítání se trochu zvýšila, ale ne výrazně, vzhledem k tomu, že rychlost načítání mé stránky je v průměru 0,15-0,5 sekundy. Možná někdo tyto informace bude zajímavé.
7. prosince 2013 15:41
vyšší specifikované akce může ovlivnit fungování pluginu nrelate-flyout?

Od autora: jeden z mých přátel se rozhodl optimalizovat své auto. Nejdřív sundal jedno kolo, takže odřízl střechu, pak motor... Obecně teď chodí. To vše jsou důsledky špatného přístupu! Proto, aby vaše DBMS nadále běžela, musí být správně provedena optimalizace MySQL.

Kdy optimalizovat a proč?

Nemá cenu jít do nastavení serveru a znovu měnit hodnoty parametrů (zvláště pokud nevíte, jak by to mohlo skončit). Pokud vezmeme v úvahu toto téma ze „zvonice“ zlepšování výkonnosti webových zdrojů je tak rozsáhlá, že je třeba jí věnovat celou vědeckou publikaci v 7 svazcích.

Ale takovou trpělivost jako spisovatel zjevně nemám a vy jako čtenář také ne. Uděláme to jednodušeji a zkusíme se jen mírně ponořit do houští optimalizace MySQL servery a jeho součásti. Pomocí optimální instalace Všechny parametry DBMS mohou dosáhnout několika cílů:

Zvyšte rychlost provádění dotazu.

Podporovat celkový výkon server.

Zkraťte dobu čekání na načtení stránek zdrojů.

Snížit spotřebu kapacity hostingového serveru.

Snižte množství místa, které zabíráte místo na disku.

Celé téma optimalizace se pokusíme rozdělit do několika bodů, aby bylo víceméně jasné, co „hrnce vaří“.

Proč zakládat server

V MySQL by optimalizace výkonu měla začínat na serveru. V první řadě byste měli zrychlit jeho provoz a zkrátit dobu potřebnou ke zpracování požadavků. Univerzální lék K dosažení všech výše uvedených cílů je povolit ukládání do mezipaměti. Nevíte „co to je“? Nyní vše vysvětlím.

Pokud je na vaší instanci serveru povoleno ukládání do mezipaměti, pak Systém MySQL automaticky si „pamatuje“ dotaz zadaný uživatelem. A příště se to stane znovu tento výsledek požadavek (na vzorkování) nebude zpracován, ale převzat ze systémové paměti. Ukazuje se, že tímto způsobem server „šetří“ čas na vydání odpovědi a v důsledku toho se zvyšuje rychlost odezvy webu. To platí také pro celková rychlost stahování.

V MySQL je optimalizace dotazů použitelná pro ty motory a CMS, které fungují na bázi tohoto DBMS a PHP. V tomto případě se kód napsaný v programovacím jazyce vygeneruje dynamickou webovou stránku vyžádá si z databáze některé její strukturální části a obsahy (záznamy, archivy a další taxonomie).

Díky povolenému ukládání do mezipaměti v MySQL je provádění dotazů na server DBMS mnohem rychlejší. Díky tomu se zvyšuje rychlost načítání celého zdroje jako celku. A to má pozitivní vliv na uživatelskou zkušenost a pozici webu ve výsledcích vyhledávání.

Povolte a nakonfigurujte ukládání do mezipaměti

Ale vraťme se od "nudné" teorie k zajímavá praxe. Další optimalizace MySQL databáze Pokračujme kontrolou stavu mezipaměti na vašem databázovém serveru. K tomu pomocí speciálního dotazu zobrazíme hodnoty všech systémových proměnných:

Je to úplně jiná věc.

Pojďme na to malá recenze získané hodnoty, které se nám budou hodit pro optimalizaci MySQL databází:

have_query_cache – hodnota udává, zda je ukládání dotazů do mezipaměti „ON“ nebo ne.

query_cache_type – zobrazí aktivní typ cache. Potřebujeme hodnotu "ON". To znamená, že ukládání do mezipaměti je povoleno pro všechny typy výběru (příkaz SELECT). S výjimkou těch, které používají parametr SQL_NO_CACHE (zakazuje ukládání informací o tomto dotazu).

Všechna nastavení máme nastavena správně.

Měříme cache pro indexy a klíče

Nyní musíte zkontrolovat, kolik je přiděleno BERAN pro indexy a klíče. Tento parametr, důležitý pro optimalizaci databáze MySQL, je doporučeno nastavit na 20-30 % velikosti RAM dostupné pro server. Pokud jsou například pro instanci DBMS přiděleny 4 „ha“, pak klidně nastavte 32 „metrů“. Vše ale závisí na vlastnostech konkrétní databáze a její struktuře (typech) tabulek.

Chcete-li nastavit hodnotu parametru, musíte upravit obsah konfigurační soubor my.ini, který se v Denveru nachází na následující cestě: F:\Webserver\usr\local\mysql-5.5

Otevřete soubor pomocí programu Poznámkový blok. Poté v něm najdeme parametr key_buffer_size a nastavíme optimální velikost pro váš PC systém (v závislosti na „hektarech“ RAM). Poté je třeba restartovat databázový server.

DBMS používá několik dalších subsystémů ( nižší úroveň) a všechna jejich základní nastavení jsou také specifikována v tento soubor konfigurace. Pokud tedy potřebujete optimalizovat MySQL InnoDB, pak vítejte zde. Tomuto tématu se budeme podrobněji věnovat v některém z našich dalších materiálů.

Měření úrovně indexů

Použití indexů v tabulkách výrazně zvyšuje rychlost zpracování a generování DBMS odpovědi na zadaný dotaz. MySQL neustále „měří“ úroveň využití indexu a klíče v každé databázi. Přijímat daná hodnota použít dotaz:

ZOBRAZIT STAV JAKO "handler_read%"

Ve výsledném výsledku nás zajímá hodnota v řádku Handler_read_key. Pokud je tam uvedené číslo malé, znamená to, že indexy se v této databázi téměř nikdy nepoužívají. A to je špatné (jako u nás).

O tom, jak dobře jsou optimalizovány dotazy do databáze data mySQL, míra zatížení serveru, a tedy i rychlost načítání webu, velmi závisí. To pomůže snížit zatížení serveru a zkrátit dobu načítání vašeho webu. optimalizace mySQLžádosti.

Proč optimalizovat databázové dotazy?

Majitelé webových stránek spravovaných samostatně psanými administračními systémy prostě musí dobře rozumět tomu, které dotazy do databáze se provádějí rychle a snadno a které značně zvyšují zátěž serveru a výrazně zpomalují rychlost načítání stránek.

To by neublížilo těm webmasterům, kteří používají známé systémy administraci a rádi připojujete všechny druhy pluginů vývojáři třetích stran, stejně jako upravovat témata pro sebe, například na nejoblíbenější bezplatný CMS– WordPress.

Některé akce lze provést různými způsoby, můžete například spočítat počet nalezených záznamů v tabulce pomocí funkce mysql_num_rows (ale nedoporučuje se to), nebo můžete také použít konstrukci SELECT COUNT(). Osobně jsme provedli studii, ve které jsme vytvořili obrovskou datovou tabulku obsahující několik set tisíc záznamů a vážící více než jeden gigabajt a poté jsme se pokusili spočítat počet řádků pomocí zadaných metod.

Výsledek byl viditelný pouhým okem, protože v případě použití mysql_num_rows stránka na 5 sekund zamrzla, poté se výsledek zobrazil. V druhém případě jsme výsledek v podobě počtu záznamů v tabulce obdrželi téměř okamžitě. Ani jsme nemuseli měřit dobu načítání skriptu pomocí mikročasovače, protože výsledek byl více než zřejmý.

Totéž platí pro ostatní designy. Některé databázové operace lze provádět dvěma, třemi, čtyřmi nebo více způsoby a každý z nich se bude lišit rychlostí, přičemž výsledek bude ve všech případech stejně správný.

Jak optimalizovat databázové dotazy

Abychom přesně porozuměli tomu, jak optimalizovat dotazy a které konstrukce fungují rychleji a které pomaleji, provedeme opět malý experiment a uděláme to hned teď.

Pro pomoc se budeme muset obrátit na rozhraní oblíbeného a velmi pohodlného phpmyadmina. Pro začátek musíme vybrat jednu z dostupných databází a vytvořit v ní testovací tabulku. Jeho název v našem případě bude zcela banální - test.

CREATE TABLE `test` (`ID` INT NOT NULL AUTO_INCREMENT , `TITLE` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , `ANNOUNCEMENT` TEXT SET CHARACTER SET utf8 COLLATE utfci8_TEXT utf8 COLLATE utfci8_ utf8_unicode_ci NE NULL , PRIMÁRNÍ KLÍČ (`ID`)) ENGINE = MYISAM ;

Nyní, když již máme testovací tabulku, musíme ji naplnit abstraktními daty. Jak je vidět ze struktury tabulky, kterou jsme právě vytvořili, k vyplnění budeme potřebovat následující údaje:

Záhlaví
Oznámení
Celý text

Pro abstraktní texty, ze zvyku, půjdeme do služby Yandex.Abstracts, vytvořené právě pro takové účely. Měli jsme to štěstí, že jsme narazili na téma „Torzní foton v 21. století“, tak to vezmeme.

Jako název jsme uvedli náhodně vybrané téma, jako oznámení jsme vzali jeden průměrný odstavec textu a plné zněníčlánku budeme mít text dlouhý 4000 znaků s mezerami. Pro počítání počtu znaků v textu jsme použili naše vlastní službu a doporučujeme s tím počítat, protože... existuje možnost zohlednit mezery nebo ne.

Výsledný požadavek zde nebudeme kopírovat, protože nebude mít více než 4000 znaků unikátní text, převzato ze samotného Yandexu, což je docela drzé a ani to nepotřebujete. Raději načrtneme nejjednodušší cyklus v PHP, který rychle přidá do databáze tolik záznamů, kolik chceme. Pro začátek to bude 100 000 článků.

Čím méně databázových dotazů, tím lépe

Již v této fázi vám ukážeme častou chybu, kterou nyní sami záměrně uděláme.

For($i=1;$i<100000;$i++) { mysql_query("INSERT INTO `test` (`ID`, `TITLE`, `ANNOUNCEMENT`, `TEXT`) VALUES (NULL, "Заголовок", "Анонс", "Полный текст")"); }

Jako požadavek jsme vložili kód zkopírovaný z phpmyadmin, který se zobrazil na obrazovce po ručním přidání prvního článku. Hned upozorňuji, že takto byste neměli vytvářet dotazy do databáze. Udělali jsme to jen proto, že jsme potřebovali rychle naplnit tabulku náhodnými daty a tento dotaz se zapisuje rychleji než ten, který je optimálnější. V tomto cyklu jsme skončili s 99999 samostatnými voláními databáze (první jsme provedli ručně z phpmyadmin), což je velmi špatná forma.

Mnohem správnějším řešením by bylo provést stejnou operaci pomocí jediného volání databáze a vypsat všechny řádky oddělené čárkami.

INSERT INTO `test` (`ID`, `TITLE`, `ANNOUNCEMENT`, `TEXT`) VALUES (NULL, "Název", "Oznámení", "Celý text"), (NULL, "Název", "Oznámení" , "Celý text"), (NULL, "Název", "Oznámení", "Celý text"), ...

Pokud se vrátíme k naší první metodě, bude to vypadat takto:

INSERT INTO `test` (`ID`, `TITLE`, `ANNOUNCEMENT`, `TEXT`) VALUES (NULL, "Název", "Oznámení", "Celý text") INSERT INTO `test` (`ID`, ` TITLE`, `ANNOUNCEMENT`, `TEXT`) VALUES (NULL, "Title", "Oznámení", "Celý text") INSERT INTO `test` (`ID`, `TITLE`, `ANNOUNCEMENT`, `TEXT`) HODNOTY (NULL, "Název", "Oznámení", "Celý text") ...

Cítíte ten rozdíl? Optimální je varianta, která využívá pouze jeden přístup k databázi. Rychlost provozu těchto dvou metod, vedoucí ke stejnému výsledku, se výrazně liší a je viditelná bez jakéhokoli měření pouhým okem, věřte mi, je to skutečně tak.

Vyberte pouze pole vyžadovaná skriptem

Zde je vše velmi jednoduché - ta či ona funkce potřebuje určitá data z cílové tabulky. Velmi často se ukáže, že musíte odstranit všechna pole úplně, zvláště pokud je tabulka poměrně velká a těchto polí je více než 10.

VYBERTE * Z „testu“.

V tomto dotazu hvězdička znamená, že data budou načtena ze všech polí testovací tabulky. Co když je v tabulce 20-30 nebo více těchto polí? Skript s největší pravděpodobností potřebuje jen některé z nich a všechny ostatní, které nebudou nijak využity, budou vybírány nadarmo. Tato operace bude pomalejší, než kdybyste zadali pouze ta pole, která v danou chvíli skutečně potřebujete, oddělená čárkami.

VYBERTE `ID`, `TITLE` Z `testu`

V tomto příkladu se vůbec nedotkneme oznámení a celého textu článku, což výrazně zrychlí skript. Dospěli jsme tedy k závěru, že optimalizace databázových dotazů znamená také specifické označení požadovaných polí v dotazech a opuštění univerzálnosti v podobě hvězdičky.

Sloučení více požadavků do jednoho

Již jsme hovořili o tom, že dobrá optimalizace práce s MySQL zahrnuje použití minimálního možného počtu dotazů a uvedli jsme příklad přidávání dat do tabulky.

Kromě přidávání může a měla by být tato technika použita při vzorkování dat. Nyní si uveďme nejjednodušší příklad. Představte si, že máte v databázi dvě tabulky – první tabulka obsahuje informace o registrovaných uživatelích a druhá obsahuje články napsané těmito uživateli.

Řekněme, že potřebujete zobrazit nějaký náhodný článek na obrazovce a podepsat jej jménem autora ve spodní části. Spojení mezi tabulkami je v tomto případě zřejmé a nastává pomocí ID uživatele, tj. ID uživatele v tabulce uživatelů musí odpovídat poli USER_ID v tabulce příspěvků. Toto spojení je standardní a mělo by být srozumitelné každému bez výjimky.

Chcete-li tedy vybrat náhodný článek, napíšete dotaz takto:

$rs_post = mysql_query("SELECT `ID`, `USER_ID`, `TITLE`, `TEXT` FROM `posts` ORDER by RAND() LIMIT 1");

Z tabulky příspěvků bude náhodně vybrán jeden článek. Poté budou naše akce vypadat nějak takto:

$row_post = mysql_fetch_assoc($rs_post); $userID = $row_post["USER_ID"];

Nyní proměnná $userID obsahuje uživatelské ID uživatele, který je autorem tohoto článku a abyste získali jeho data, například JMÉNO (jméno) a PŘÍJMENÍ (příjmení), dostanete se do tabulky uživatelů a dotazu bude vypadat nějak takto:

$rs_user = mysql_query("SELECT `NAME`, `PRIJMENO` FROM `users` WHERE `ID` = "".$row_post["USER_ID"]."" LIMIT 1");

Mimochodem, nezapomeňte v požadavcích uzavřít proměnné do jednoduchých uvozovek, zvláště když data přicházejí zvenčí, pomocí GET nebo POST. To vytvoří další překážku pro útočníky a je to jedno z opatření zaměřených na ochranu proti injekcím SQL. Vraťme se tedy k našemu příkladu. Po zadání požadavku do databáze je pak vše jednoduché - získáme jméno a příjmení a zobrazíme je jako podpis k článku. Úkol je dokončen.

Ale tyto dva dotazy lze optimalizovat do jednoho. K tomu použijeme konstrukci LEFT JOIN:

SELECT `posts`.`ID`, `posts`.`USER_ID`, `posts`.`TITLE`, `posts`.`TEXT`, `users`.`NAME`, `users`.`PRIJMENÍ` FROM ` posts` LEFT JOIN `users` ON `příspěvky`.`USER_ID` = `users`.`ID` ORDER by RAND() LIMIT 1

Jak vidíte, ve výše uvedeném designu není nic složitého a vše je intuitivní. Jediné, na co bych vás chtěl upozornit, je explicitní označení tabulek spárovaných s poli, protože existuje výběr z několika tabulek najednou. Pokud se názvy některých polí shodují, pak byste měli používat takzvané mySQL aliasy, abyste se později při zobrazování výsledku nespletli.

Závěr

Jak vidíte, optimalizovat databázové dotazy je možné a nutné. Pokud si myslíte, že když vám vše funguje tak rychle, tak nemá smysl nic měnit, počkejte, až se databáze vašeho webu několikrát rozroste a s tím se zvýší i návštěvnost. Vysoký provoz znamená častější současný přístup k databázi, jejíž velikost ovlivňuje i rychlost operací.

Špatná optimalizace dotazů se může objevit o něco později, až se web dostatečně rozroste, a postupem času bude stále obtížnější provádět změny, protože velikost souborů a počet funkcí se jen zvětšují. Na stránky jsou přidávány nové funkce zaměřené na pohodlí uživatelů. Jinými slovy, pokud věci dosáhnou určitého bodu varu, možná nebudete schopni najít žádná řešení a optimalizace všech volání do databáze, rozptýlených ve stovkách souborů, bude trvat několik dní nebo možná i týdnů. Proto je lepší pokusit se udělat dobře hned, abyste si v budoucnu nevytvářeli zbytečné problémy.

MySQL je stále nejoblíbenější relační databází na světě, ale je také nejméně optimalizovaná. Mnoho lidí zůstává s výchozím nastavením, aniž by hloubali. V tomto článku se podíváme na některé optimalizační tipy pro MySQL v kombinaci s některými novými funkcemi, které vyšly relativně nedávno.

Optimalizace konfigurace

První věc, kterou by měl každý uživatel MySQL udělat pro zlepšení výkonu, je vyladit konfiguraci. Většina lidí však tento krok přeskakuje. Ve verzi 5.7 (aktuální verze) jsou výchozí nastavení mnohem lepší než u předchůdců, ale stále je možné a snadné je vylepšit.

Doufáme, že používáte Linux nebo něco jako Vagrant -box (jako náš Homestead Improved) a podle toho bude váš konfigurační soubor umístěn v /etc/mysql/my.cnf . Je možné, že vaše instalace skutečně nahraje další konfigurační soubor do tohoto souboru. Takže se podívejte, pokud soubor my.cnf obsahuje málo, pak se podívejte do /etc/mysql/mysql.conf.d/mysqld.cnf .

Ruční ladění

Následující nastavení by měla být provedena ihned po vybalení. Podle těchto tipů přidejte do konfiguračního souboru v sekci:

Innodb_buffer_pool_size = 1G # (zde se změní asi 50%-70% celkové RAM) innodb_log_file_size = 256M innodb_flush_log_at_trx_commit = 1 # lze změnit na 2 nebo 0 innodb_flush_method = O_DIRECT

innodb_buffer_pool_size . Vyrovnávací paměť je druh „skladu“ pro ukládání dat a indexů do mezipaměti. Slouží k ukládání často používaných dat do paměti. A když používáte dedikovaný nebo virtuální server, kde je databáze často úzkým hrdlem, má smysl dát mu většinu paměti RAM. Proto mu dáváme 50–70 % celkové paměti RAM. V dokumentaci MySQL je návod k nastavení tohoto fondu.
innodb_log_file_size . Nastavení velikosti souboru protokolu je dobře popsáno, ale v kostce jde o množství dat uložených v protokolech před jejich vymazáním. Upozorňujeme, že protokol v tomto případě nejsou chybové záznamy, ale jakýsi rozdílový snímek změn, které ještě nebyly vyprázdněny na disk v hlavních souborech innodb. MySQL zapisuje na pozadí, ale to stále ovlivňuje výkon v době psaní. Větší soubor protokolu znamená vyšší výkon díky menšímu počtu vytvářených nových a menších kontrolních bodů, ale také delší dobu obnovy v případě havárie (do databáze je třeba zapsat více dat).
innodb_flush_log_at_trx_commit je popsán a ukazuje, co se stane se souborem protokolu. Hodnota 1 je nejbezpečnější, protože protokol se po každé transakci vyprázdní na disk. S hodnotami 0 a 2 je zaručeno méně ACID, ale vyšší výkon. Rozdíl není dostatečně velký, aby převážil výhody stability na 1.
innodb_flush_method . Aby toho nebylo málo, pokud jde o splachování dat, je potřeba toto nastavení nastavit na O_DIRECT – aby se předešlo dvojitému ukládání do vyrovnávací paměti. Radím vám, abyste to dělali vždy, když je I/O systém velmi pomalý. Ačkoli na většině hostingů, jako je DigitalOcean, budete mít SSD disky, takže I/O systém bude produktivnější.

Existuje nástroj od Percony, který nám pomůže automaticky najít zbývající problémy. Všimněte si, že pokud bychom jej spustili bez tohoto ručního nastavení, bylo by definováno pouze 1 ze 4 nastavení, protože ostatní 3 závisí na preferencích uživatele a prostředí aplikace.

Variabilní inspektor

Instalace proměnného inspektora na Ubuntu:

Wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all. deb sudo apt-get update sudo apt-get install percona-toolkit

U ostatních systémů postupujte podle těchto pokynů.

Poté spusťte sadu nástrojů:

Pt-variable-advisor h=localhost,u=homestead,p=secret

Uvidíte tento výsledek:

# WARN delay_key_write: Bloky indexu MyISAM nejsou nikdy vyprázdněny, dokud to není nutné. # POZNÁMKA max_binlog_size: Max_binlog_size je menší než výchozí 1GB. # POZNÁMKA sort_buffer_size-1: Proměnná sort_buffer_size by měla být obecně ponechána ve výchozím nastavení, pokud odborník nerozhodne, že je nutné ji změnit. # POZNÁMKA innodb_data_file_path: Automatické rozšiřování souborů InnoDB může spotřebovat spoustu místa na disku, které je později velmi obtížné získat zpět. # WARN log_bin: Binární protokolování je zakázáno, takže obnovení a replikace v určitém okamžiku nejsou možné.

Poznámka překladatel:
Na mém místním počítači jsem navíc obdržel následující varování:

# POZNÁMKA innodb_flush_method: Většina produkčních databázových serverů, které používají InnoDB, by měla nastavit innodb_flush_method na O_DIRECT, aby se zabránilo dvojitému ukládání do vyrovnávací paměti, pokud I/O systém nemá velmi nízký výkon.

Skutečnost, že parametr innodb_flush_method musí být nastaven na O_DIRECT a proč, byla diskutována výše. A pokud jste dodrželi sled ladění jako v článku, pak toto varování neuvidíte.

Žádný z těchto ( cca: uvádí autor) varování nejsou kritická, není třeba je opravovat. Jediné, co lze opravit, je nastavení binárního protokolu pro replikaci a snímky.

Poznámka: V nových verzích je výchozí velikost binlogu 1G a toto varování se neobjeví.

Max_binlog_size = 1G log_bin = /var/log/mysql/mysql-bin.log server-id=master-01 binlog-format = "ROW"

max_binlog_size . Určuje, jak velké budou binární protokoly. Zaznamenávají vaše transakce a požadavky a provádějí kontrolní body. Pokud transakce překročí maximum, může protokol při uložení na disk přesáhnout svou velikost; jinak to MySQL bude v rámci tohoto limitu podporovat.
log_bin. Tato možnost obecně umožňuje binární protokolování. Bez něj nejsou možné snímky ani replikace. Upozorňujeme, že to může mít velký dopad na místo na disku. server-id je povinná volba při povolování binárního protokolování, takže protokoly „ví“, ze kterého serveru přišly (kvůli replikaci), a formát binlog je prostě způsob, jakým jsou zapsány.

Jak můžete vidět, nová MySQL má výchozí hodnoty, které jsou téměř připraveny na výrobu. Každá aplikace je samozřejmě jiná a má další triky a vychytávky, které aplikuje.

MySQL Tuner

Podpůrné nástroje: Percona Toolkit pro identifikaci duplicitních indexů

Nástroj Percona Toolkit, který jsme dříve nainstalovali, má také nástroj pro detekci duplicitních indexů, který může být užitečný při používání CMS třetích stran nebo při pouhé kontrole, zda jste omylem nepřidali více indexů, než je nutné. Například výchozí instalace WordPress má duplicitní indexy v tabulce wp_posts:

Pt-duplicate-key-checker h=localhost,u=homestead,p=secret ############################### ######################################### # homestead.wp_posts # #### ################################################## ################## # Klíč type_status_date končí předponou seskupeného indexu # Definice klíčů: # KEY `type_status_date` (`post_type`,`post_status`,`post_date` ,`ID`), # PRIMARY KEY (`ID`), # Typy sloupců: # `post_type` varchar(20) collate utf8mb4_unicode_520_ci not null default "post" # `post_status` varchar(20) collate utf8mb4_unicode_520_ci notpublish " " # `post_date` datetime not null default "0000-00-00 00:00:00" # `id` bigint(20) unsigned not null auto_increment # Chcete-li zkrátit tento duplicitní seskupený index, spusťte: ALTER TABLE `homestead`. ` wp_posts` DROP INDEX `type_status_date`, ADD INDEX `type_status_date` (`post_type`,`post_status`,`post_date`);

Jak můžete vidět z posledního řádku, tento nástroj vám také poskytuje tipy, jak se zbavit duplicitních indexů.

Pomocné nástroje: Percona Toolkit pro nepoužívané indexy

Percona Toolkit také dokáže detekovat nepoužívané indexy. Pokud zaznamenáváte pomalé dotazy (viz část o úzkých hrdlech níže), můžete spustit nástroj a ten zkontroluje, zda a jak tyto dotazy používají indexy v tabulkách.

Pt-index-usage /var/log/mysql/mysql-slow.log

Podrobné informace o používání tohoto nástroje naleznete v části .

Úzká místa

Tato část popisuje, jak detekovat a monitorovat úzká místa databáze.

Nejprve povolme protokolování pomalých dotazů:

Slow_query_log = /var/log/mysql/mysql-slow.log long_query_time = 1 log-queries-not-using-indexes = 1

Výše uvedené řádky musí být přidány do konfigurace mysql. Databáze bude sledovat dotazy, jejichž dokončení trvalo déle než 1 sekundu, a dotazy, které nepoužívají indexy.

Jakmile jsou v tomto protokolu nějaká data, můžete je analyzovat pro použití indexu pomocí výše uvedeného obslužného programu pt-index-usage nebo pomocí pt-query-digest, který vydá něco takového:

Pt-query-digest /var/log/mysql/mysql-slow.log # 360 ms uživatelský čas, 20 ms systémový čas, 24,66 M rss, 92,02 M vsz # Aktuální datum: Čt 13. února 22:39:29 2014 # Název hostitele: * # Soubory: mysql-slow.log # Celkově: 8 celkem, 6 jedinečných, 1,14 QPS, 0,00x souběžnost ________________ # Časový rozsah: 2014-02-13 22:23:52 až 22:23:59 max. průměr celkem 95% stddev medián # ============ ================================== == ======= ======= # Čas provedení 3ms 267us 406us 343us 403us 39us 348us # Čas uzamčení 827us 88us 125us 103us 119us 12us 98us # 5 2594 1 odeslaných řádků # prozkoumat 87 4 30 10,88 28,75 7,37 7,70 # Velikost dotazu 2,15 k 153 296 245,11 284,79 48,90 258,32 # === = ==================================== ==== =============== # Profil # Pořadí ID dotazu Doba odezvy Volání R/Call V/M Položka # ==== ==== ====== ====== ===== ====== === == =============== # 1 0x728E539F7617C14D 0,0011 41,0 % 3 0,0004 0,00 VYBERTE článek_blogu č. 2 0x1290EEE0B201F3ff 0,0003 12,8% 1 0,0003 0,00 Select Portfolio _item # 3 0x31de4535BDBFA465 0,0003 12,6% 1 0,0003 0,00 Select Portfolio_ITEM 48005A09C9588 0,0003 11,8% 1 0,0003 0,00 Select Blog_Category # 6 0x55F49 C753CA2ED64 0,0003 9,7% 1 0,0003 0,00 VYBERTE článek_blogu č. ==== ========================================= == ===== =============== # Dotaz 1: 0 QPS, 0x souběžnost, ID 0x728E539F7617C14D na byte 736 ______ # Skóre: V/M = 0,00 # Časový rozsah: všechny události se staly 2014-02-13 22:23:52 # Atribut pct celkem min max. průměr 95 % stddev medián # ========================= ===== ======== ======= === ==== ======= ======= # Počet 37 3 # Doba provedení 40 1ms 352us 406us 375us 403us 22us 366us # Doba uzamčení 42 351us 103us 125us 117us 119us 9us 119us # Odeslané řádky 25 9 1 4 3 3,89 1,37 3,89 2 7 1 prozkoumat 0 # Velikost dotazu 47 1,02 k 261 262 261,25 258,32 0 258,32 # String: # Hosts localhost # Users * # Query_time distribution # 1us # 10us # 100us #### ########################### ###################### ########## # 1ms # 10ms # 100ms # 1s # 10s+ # Tabulky # ZOBRAZIT STAV TABULKY JAKO " blog_article"\G # SHOW CREATE TABLE `blog_article`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT b0_.id AS id0, b0_.slug AS slug1, b0_.title AS title2, b0_.excerpt AS excerptternal, b0_.ex AS external_link4, b0_.description AS description5, b0_.vytvořeno AS vytvořeno6, b0_.aktualizováno AS aktualizováno7 Z blog_article b0_ ORDER BY b0_.created DESC LIMIT 10

Pokud dáváte přednost ruční analýze těchto protokolů, můžete udělat totéž, ale nejprve budete muset protokol exportovat do lépe analyzovatelného formátu. To lze provést takto:

Mysqldumpslow /var/log/mysql/mysql-slow.log

S pokročilými možnostmi můžete filtrovat data a exportovat pouze to, co potřebujete. Například 10 nejčastějších dotazů seřazených podle průměrné doby provádění:

Mysqldumpslow -t 10 -s na /var/log/mysql/localhost-slow.log

Závěr

V tomto komplexním příspěvku o optimalizaci MySQL jsme se zabývali různými metodami a technikami, pomocí kterých můžeme zajistit, aby naše MySQL létala.

Přišli jsme na optimalizaci konfigurace, upgradovali jsme indexy a zbavili jsme se některých úzkých míst. To vše byla většinou teorie, nicméně vše je aplikovatelné na aplikace v reálném světě.

Práce s databází je často nejslabším místem výkonu mnoha webových aplikací. A to se nemusí starat jen o DBA. Programátoři musí zvolit správnou strukturu tabulky, napsat optimalizované dotazy a napsat dobrý kód. Následují metody pro optimalizaci práce s MySQL pro programátory.

1. Optimalizujte dotazy pro mezipaměť dotazů

Většina serverů MySQL má povoleno ukládání dotazů do mezipaměti. Jedním z nejlepších způsobů, jak zlepšit výkon, je jednoduše poskytnout ukládání do mezipaměti samotné databázi. Když se dotaz mnohokrát opakuje, jeho výsledek je převzat z mezipaměti, což je mnohem rychlejší než přímý přístup k databázi. Hlavním problémem je, že mnoho lidí jednoduše používá dotazy, které nelze uložit do mezipaměti:

// požadavek nebude uložen do mezipaměti$r = mysql_query( "SELECT uživatelské jméno FROM user WHERE signup_date >= CURDATE()"); // a tak to bude! $dnes = datum("R-m-d" ); $r = mysql_query();

"SELECT uživatelské jméno FROM user WHERE signup_date >= "$today""

Důvodem je, že první dotaz používá funkci CURDATE(). To platí pro všechny funkce jako NOW(), RAND() a další, jejichž výsledek je nedeterministický. Pokud se výsledek funkce může změnit, MySQL takový dotaz neukládá. V tomto příkladu tomu lze zabránit výpočtem data před provedením dotazu.

2. Použijte EXPLAIN pro vaše SELECT dotazy// vytvořit připravený příkaz if ($stmt = $mysqli ->prepare()) { "SELECT uživatelské jméno FROM user WHERE state=?"// svázat hodnoty $stmt ->bind_param("s" , $stav );// vykoná $stmt ->execute(); // svázat výsledek$stmt ->fetch();

printf("%s je z %s\n" , $uživatelské jméno , $stav );

$stmt ->zavřít(); )
13. Požadavky bez vyrovnávací paměti

Obvykle se při zadávání požadavku skript zastaví a čeká na výsledek svého provedení. Můžete to změnit pomocí dotazů bez vyrovnávací paměti.

V dokumentaci funkce mysql_unbuffered_query() je dobrý popis:

"mysql_unbuffered_query() odešle dotaz SQL do MySQL bez načítání nebo automatického ukládání do vyrovnávací paměti řádků výsledků, jak to dělá mysql_query(). Na jedné straně to ušetří značné množství paměti pro dotazy SQL, které vytvářejí velké sady výsledků. Na druhou stranu můžete začít pracovat na sadě výsledků dělení po načtení prvního řádku: nemusíte čekat na spuštění celého SQL dotazu."

Existují však určitá omezení. Než budete moci spustit další dotaz, budete si muset přečíst všechny záznamy nebo zavolat mysql_free_result(). Také nemůžete použít mysql_num_rows() nebo mysql_data_seek() na výsledek funkce.
14. Uložte IP do NESIGNED INT
Mnoho programátorů ukládá IP adresy do pole typu VARCHAR(15), aniž by věděli, že je lze uložit v celočíselné podobě. INT zabírá 4 bajty a má pevnou velikost pole. Ujistěte se, že používáte UNSIGNED INT, protože IP lze zapsat jako 32bitové číslo bez znaménka. Pomocí INET_ATON() ve svém požadavku převeďte IP adresu na číslo a INET_NTOA() na inverzní konverze. Stejné funkce existují v PHP - ip2long() a long2ip() (v PHP se tyto funkce mohou vrátit

záporné hodnoty . poznámka od uživatele habrayus The_Lion).;

$r =

"UPDATE users SET ip = INET_ATON("($_SERVER["REMOTE_ADDR"])") WHERE user_id = $user_id" 15. Tabulky s pevnou velikostí (statické) jsou rychlejší Pokud má každý sloupec v tabulce pevnou velikost, pak se taková tabulka nazývá „statická“ nebo „
pevná velikost " Příklad sloupců s pevnou délkou: VARCHAR, TEXT, BLOB. Pokud takové pole zahrnete do tabulky, nebude již opraveno a MySQL jej zpracuje jinak. Použití takových tabulek zvýší efektivitu, protože... MySQL v nich dokáže rychleji vyhledávat záznamy. Kdy vybrat
požadovaný řádek
Pomocí metody "vertikálního rozdělení" můžete přesouvat sloupce z variabilní délkařádků do samostatné tabulky.

16. Vertikální separace

Vertikální dělení se týká rozdělení tabulky do sloupců za účelem zlepšení výkonu.
Příklad 1. Pokud jsou adresy uloženy v tabulce uživatelů, není pravda, že je budete potřebovat velmi často. Můžete rozdělit tabulku a ukládat adresy samostatný stůl. Velikost uživatelské tabulky se tak zmenší. Produktivita se zvýší.
Příklad 2: V tabulce máte pole „last_login“. Aktualizuje se pokaždé, když se uživatel přihlásí na web. Ale všechny změny v tabulce vyčistí její mezipaměť. Uložením tohoto pole do jiné tabulky omezíte změny v tabulce uživatelů na minimum.
Pokud ale na těchto stolech neustále používáte spojení, povede to ke špatnému výkonu.

17. Oddělte velké dotazy DELETE a INSERT

Pokud potřebujete provést velký požadavek na smazání nebo vložení dat, musíte být opatrní, abyste aplikaci nerozbili. Provedení velká žádost může uzamknout stůl a vést k nefunkčnost celou aplikaci.
Apache může spouštět více paralelních procesů současně. Proto funguje efektivněji, pokud jsou skripty prováděny co nejrychleji.
Pokud zamknete stoly dlouhodobě(například po dobu 30 sekund nebo déle), pak při vysoké návštěvnosti webu může vzniknout velká fronta procesů a požadavků, což může vést k pomalá práce webu nebo dokonce selhání serveru.
Pokud máte dotazy jako je tento, použijte LIMIT k jejich spouštění v malých dávkách.

while (1) ( mysql_query( "DELETE FROM logs WHERE log_date<= "2009-10-01" LIMIT 10000" ); if (mysql_affected_rows() == 0) ( // odstraněno přerušení; )// krátká pauza

uspat(50000); )

18. Malé kolony jsou rychlejší
U databáze je práce s pevným diskem snad nejslabším místem. Malé a kompaktní desky jsou obvykle lepší z hlediska výkonu, protože... snížit práci na disku.
Dokumentace MySQL obsahuje seznam požadavků na ukládání dat pro všechny typy dat.
Pokud vaše tabulka obsahuje několik řádků, pak nemá smysl dělat hlavní klíč typu INT, může být lepší, aby byl MEDIUMINT, SMALLINT nebo dokonce TINYINT. Pokud nepotřebujete ukládat čas, použijte místo DATETIME DATE.

Dejte si však pozor, aby věci nedopadly jako Slashdot.

19. Vyberte správný typ tabulky

20. Použijte ORM

Trvalá připojení jsou navržena tak, aby snížila náklady na navázání komunikace s MySQL. Jakmile je připojení vytvořeno, zůstane po dokončení skriptu otevřené. Příště tento skript použije stejné připojení.
mysql_pconnect() v PHP
Ale to zní dobře jen teoreticky. Z mé osobní zkušenosti (a zkušeností ostatních) není použití této funkce opodstatněné. Budete mít vážné problémy s limity připojení, limity paměti a tak dále.
Apache vytváří mnoho paralelních vláken. To je hlavní důvod, proč trvalá připojení nefungují tak, jak bychom chtěli. Před použitím mysql_pconnect() se poraďte se správcem systému.

Oblíbené v kategorii: