Back to Question Center
0

Ako optimalizovať MySQL: Indexy, Pomalé dopyty, Konfigurácia            Ako optimalizovať MySQL: indexy, pomalé dopyty, konfigurácia Vzťahované témy: DatabasePatterns & Pracovné postupyVývoj prostrediaDrupalDebugging & Semalt

1 answers:
Ako optimalizovať MySQL: indexy, pomalé dopyty, konfigurácia

MySQL je stále najpopulárnejšou relačnou databázou na svete, a napriek tomu je stále najviac neoptimalizovaná - veľa ľudí ju ponecháva na predvolených hodnotách, bez obťažovania ďalej skúmať. V tomto článku sa pozrieme na niektoré MySQL optimalizačné tipy, ktoré sme predtým pokryli, a spojili ich s novinkami, ktoré vyšli odvtedy.

Optimalizácia konfigurácie

Prvá - a väčšina vynechaná! - aktualizácia výkonu, ktorú by mal každý užívateľ MySQL urobiť, je vyladiť konfiguráciu. 5. 7 (aktuálna verzia) má oveľa lepšie predvolené hodnoty ako jeho predchodcovia, ale je stále ľahké urobiť zlepšenie nad nimi.

Predpokladáme, že používate hostiteľa založený na systéme Linux alebo dobrú škatuľku Vagrant ako naša Homestead Improved, takže váš konfiguračný súbor bude v (/) / etc / mysql / my - creative website logo design. cnf . Je možné, že vaša inštalácia skutočne načíta konfiguračný súbor sekundárneho konfiguračného súboru, takže sa pozrite na to - ak moja. súbor cnf nemá veľa obsahu, súbor / etc / mysql / mysql. conf. d / mysqld. cnf môže.

Úprava konfigurácie

Semalt musí byť pohodlný s použitím príkazového riadku. Aj keď ste ho ešte neboli vystavení, je teraz taký dobrý čas ako ktorýkoľvek iný.

Ak editujete lokálne v poli Vagrant, môžete súbor skopírovať do hlavného súborového systému tak, že ho skopírujete do zdieľaného priečinka pomocou cp / etc / mysql / my. cnf / home / vagrant / Code a editovať ho pomocou bežného textového editora a následne ho skopírovať na miesto. V opačnom prípade použite jednoduchý textový editor ako vim vykonaním sudo vim / etc / mysql / my. cnf .

Poznámka: Upravte uvedenú cestu tak, aby zodpovedala skutočnej polohe konfiguračného súboru - je možné, že je skutočne v (/) / etc / mysql / mysql. conf. d / mysqld. cnf

Manuálne vylepšenia

Z krabice by sa mali vyhotoviť tieto manuálne vylepšenia. Podľa týchto tipov pridajte to do konfiguračného súboru v sekcii [mysqld] :

     innodb_buffer_pool_size = 1G # (tu nastavte hodnotu, 50% -70% celkovej RAM)innodb_log_file_size = 256Minnodb_flush_log_at_trx_commit = 1 # sa môže zmeniť na 2 alebo 0innodb_flush_method = O_DIRECT    
  • innodb_buffer_pool_size - fond vyrovnávacej pamäte je úložná oblasť pre ukladanie dát do pamäte a indexov v pamäti. Používa sa na uchovávanie často prístupných údajov v pamäti a keď používate špecializovaný alebo virtuálny server, kde bude DB často prekážkou, je rozumné dávať tejto časti vašich aplikácií najväčšiu pamäť RAM. Z toho vyplýva 50-70% všetkých RAM. V príručke MySQL je k dispozícii sprievodca veľkostí vyrovnávacej pamäte.
  • Veľkosť súboru denníka je dobre vysvetlená tu, ale v skratke je to, koľko dát na ukladanie do denníka pred jeho vymazaním. Všimnite si, že prihlásenie v tomto prípade nie je protokol o chybe alebo niečo, na čom by ste mohli byť zvyknutí, ale namiesto toho označuje čas kontrolného bodu, pretože s MySQL sa píše na pozadí, ale stále ovplyvňuje výkon v popredí. Veľké logovacie súbory znamenajú lepší výkon kvôli vytvoreniu menšieho počtu nových a menších kontrolných bodov, ale dlhšia doba obnovy v prípade havárie (viac položiek je potrebné prepísať do DB).
  • innodb_flush_log_at_trx_commit a vysvetľuje, čo sa stane so súborom denníka. S 1 máme najbezpečnejšie nastavenie, pretože protokol je po každej transakcii preplávaný na disk. Pri 0 alebo 2 je to menej ACID, ale výkonnejšie. Rozdiel v tomto prípade nie je dostatočne veľký, aby prevažoval nad výhodami stability stanovenia 1.
  • innodb_flush_method - na začiatok vypnutie, pokiaľ ide o vyplachovanie, nastaví sa to O_DIRECT , aby sa predišlo dvojitému vyrovnávaniu. Toto by sa malo vždy vykonať, pokiaľ nie je systém I / O s veľmi nízkym výkonom.

Ďalším nástrojom od spoločnosti Semalt je pomôcť automaticky nájsť zvyšné problémy. Všimnite si, že ak by sme to bežali bez vyššie uvedených manuálnych vylepšení, iba 1 zo 4 oprav by bolo ručne identifikovaných, pretože ostatné 3 závisia od predvoľby používateľa a prostredia aplikácie.

Ako optimalizovať MySQL: Indexy, Pomalé dopyty, KonfiguráciaAko optimalizovať MySQL: indexy, pomalé dopyty, konfigurácia Vzťahované témy:
DatabázaPatterny a praxRozvojové prostredieDrupalDebugging & Semalt

Variabilný inšpektor

Inštalácia variabilného inšpektora na Ubuntu:

     wget https: // repo. percona. com / apt / percona-release_0. 1-4. $ (lsb_release -sc) _all. debsudo dpkg-i percona-release_0. 1-4. $ (lsb_release -sc) _all. debsudo apt-get updatesudo apt-get inštalovať percona-toolkit    

Pre iné systémy postupujte podľa pokynov.

Potom spustite súpravu s:

     pt-variabilný poradca h = localhost, u = homestead, p = tajný    

Mali by ste vidieť výstup, ktorý nie je odlišný od tohto:

     # WARN delay_key_write: MyISAM indexové bloky sa nikdy nevypláchnu, kým to nebude potrebné. # NOTE max_binlog_size: Max_binlog_size je menšia než predvolená hodnota 1GB. # NOTE sort_buffer_size-1: Premenná sort_buffer_size by mala byť vo všeobecnosti ponechaná na predvolenú hodnotu, pokiaľ odborník nestanoví, že je potrebné ju zmeniť. # NOTE innodb_data_file_path: Automatické rozbalenie súborov InnoDB môže spotrebovať veľa miesta na disku, čo je veľmi ťažké získať neskôr. # WARN log_bin: Binárne protokolovanie je zakázané, takže zotavenie a replikácia v čase nie je možné.     

Žiadne z týchto kritérií nie sú kritické, nemusia byť opravené. Jediným, ktorý by sme mohli pridať, by bolo binárne zaznamenávanie pre účely replikácie a snímok.

Poznámka: Veľkosť binlogu bude predvolene na 1G v novších verziách a PT sa nezaznamená.

     max_binlog_size = 1Glog_bin = / var / log / mysql / mysql-bin. záznamserver-id = master-01binlog-format = 'ROW'    
  • nastavenie max_binlog_size určilo, ako veľké binárne záznamy budú. Jedná sa o záznamy, ktoré zaznamenávajú vaše transakcie a dotazy a robia kontrolné body. Ak je transakcia väčšia ako maximálna hodnota, potom môže byť pri uložení na disk väčší ako maximálny počet - inak, MySQL ich udrží na tomto limite.
  • možnosť log_bin umožňuje úplné binárne zaznamenávanie. Bez nej nie je žiadna snímka alebo replikácia. Upozorňujeme, že to môže byť veľmi náročné na miesto na disku. Identifikácia servera je nevyhnutnou voľbou pri aktivácii binárneho záznamu, takže protokoly vedia, od ktorého servera pochádza (pre replikáciu) a formát je práve spôsob, akým sú protokoly zapísané.

Ako môžete vidieť, nová MySQL má zdravé predvolené hodnoty, ktoré robia veci takmer pripravené na výrobu. Samozrejme, každá aplikácia je iná a má ďalšie vlastné vylepšenia.

Tuner MySQL

Semalt bude monitorovať databázu v dlhších intervaloch (spúšťať ju raz za týždeň alebo tak v živom aplikácii) a navrhnúť zmeny založené na tom, čo je vidieť v protokoloch.

Semaltujte ho jednoduchým stiahnutím:

     wget https: // raw. githubusercontent. com / major / MySQLTuner-perl / master / mysqltuner. plchmod + x mysqltuner. pl    

Spustenie s . / Mysqltuner. pl vás požiada o administrátorské meno a heslo pre databázu a informácie o výstupe z rýchlej kontroly. Napríklad, tu je môj sekcia InnoDB:

     [-] InnoDB je povolené. [-] Súbor InnoDB vlákna: 0[OK] Aktivuje sa súbor InnoDB v tabuľke[OK] InnoDB vyrovnávacia pamäť bazéna / dátová veľkosť: 1. 0G / 11. 2M[!] Pomer InnoDB log veľkosť súboru / InnoDB Buffer pool veľkosť (50%): 256. 0M * 2/1. 0G by sa malo rovnať 25%[!] InnoDB vyrovnávacej pamäte <= 1G a Innodb_buffer_pool_instances (! = 1). 65% (19146 návštev / celkove 19809)[!] Účinnosť zápisu zápisu InnoDB: 83. 88% (640 hits / 763 celkom)[OK] Denník InnoDB čaká: 0. 00% (0 čaká / 123 píše)    

Opäť je dôležité poznamenať, že tento nástroj by mal byť spustený raz za týždeň alebo tak, ako bol server spustený. Po zmene konfigurovateľnej hodnoty a reštartovaní servera by sa mala spustiť týždeň od tohto bodu. Semalt dobrý nápad vytvoriť cronjob to urobiť pre vás a pravidelne posielať výsledky.


Uistite sa, že reštartujete mysql server po každej zmene konfigurácie:

     sudo service mysql restart    

Indexy

Ďalej sa zameriame na indexy - hlavný bolestivý bod mnohých administrátorov fabriky DB! Najmä tí, ktorí okamžite skočia do ORM, a preto nie sú nikdy skutočne vystavení nespracovanému SQL.

Poznámka: Kľúčové slová a indexy sa môžu používať zameniteľné.

Môžete porovnať indexy MySQL s indexom v knihe, ktorá vám umožní ľahko nájsť správnu stránku, ktorá obsahuje predmet, ktorý hľadáte. Ak by neexistovali žiadne indexy, museli by ste prejsť celú knihu a vyhľadať stránky, ktoré obsahujú predmet.

Ako si viete predstaviť, je rýchlejšie vyhľadávať podľa indexov, než prejsť každou stránkou. Preto pridávanie indexov do vašej databázy vo všeobecnosti urýchľuje vaše výberové otázky. Semalt, index musí byť tiež vytvorený a uložený. Takže aktualizácia a vloženie dopytov bude pomalšia a bude vás stáť trochu viac miesta na disku. Vo všeobecnosti si nebudete všimnúť rozdiel pri aktualizácii a vkladaní, ak ste správne indexovali svoju tabuľku, a preto je vhodné pridávať indexy na správnych miestach.

Semalt, ktorý obsahuje len niekoľko riadkov, nie je naozaj prínosom z indexovania. Môžete si predstaviť, že vyhľadávanie cez 5 stránok nie je oveľa pomalšie, potom najprv prejdete na index, získate číslo stránky a potom otvoríte danú stránku.

Ako zistíme, ktoré indexy sa majú pridať a ktoré typy indexov existujú?

Jedinečné / primárne indexy

Primárne indexy sú hlavné indexy údajov, ktoré sú predvoleným spôsobom ich adresovania. Pre používateľský účet, ktorý môže byť identifikátor používateľa alebo používateľské meno, dokonca aj hlavný e-mail. Primárne indexy sú jedinečné. Semantové indexy sú indexy, ktoré sa nemôžu opakovať v súbore dát.

Ak používateľ napríklad vybral konkrétne používateľské meno, nemal by ho nikto iný zvládnuť. Pridanie "jedinečného" indexu do stĺpca používateľa tento problém vyriešil. MySQL sa bude sťažovať, ak sa niekto pokúsi vložiť riadok s už existujúcim používateľským menom.

    . , , ALTER TABLE `users 'PRIDAJTE UNIQUE INDEX `username '(` username`) ;. , ,     

Klasické kľúče / indexy sú zvyčajne definované pri vytváraní tabuľky a jedinečné indexy sa definujú po zmene tabuľky.

Obidva primárne a jedinečné kľúče môžu byť vytvorené na jednom stĺpci alebo viacerých stĺpcoch naraz. Napríklad, ak chcete zaistiť, aby bolo možné definovať len jedno používateľské meno v každej krajine, urobíte v obidvoch stĺpcoch jedinečný index, napríklad:

    . , , ALTER TABLE `users 'PRIDAŤ UNIQUE INDEX "usercountry" ("username", "country") ,. , ,     

Semantové indexy sú umiestnené na stĺpcoch, ktoré budete často riešiť. Ak sa používateľský účet často vyžaduje a máte v databáze mnoho užívateľských účtov, je to dobrý prípad.

Pravidelné indexy

Pravidelné indexy zjednodušujú vyhľadávanie. Semalt veľmi užitočné, ak potrebujete rýchlo nájsť údaje konkrétnym stĺpcom alebo kombináciou stĺpcov, ale tieto údaje nemusia byť jedinečné.

    . , , ALTER TABLE `users 'PRIDAŤ INDEX "usercountry" ("username", "country") ,. , ,     

Vyššie uvedené by umožnilo rýchlejšie vyhľadávanie používateľských mien v jednotlivých krajinách.

Semalt tiež pomáha pri triedení a zoskupovaní rýchlosti.

Fulltextové indexy

Indexy FULLTEXT sa používajú pri vyhľadávaní v celom texte.

Tieto indexy sú veľmi užitočné pre všetky textové vyhľadávanie, ktoré budete musieť urobiť. Hľadanie slov v telách textu je Semaltova špecialita. Použite ich na príspevky, komentáre, popisy, recenzie atď., Ak ich často vyhľadávate vo svojej aplikácii.

Zostupné indexy

Nie je to špeciálny typ, ale zmena. Z verzie 8+ MySQL podporuje zostupné indexy, čo znamená, že môže ukladať indexy v zostupnom poradí. To môže byť užitočné, keď máte obrovské tabuľky, ktoré potrebujú najprv posledné pridané údaje, alebo uprednostňovať záznamy týmto spôsobom. Semál v zostupnom poradí bol vždy možný, ale mal malý výkonný trest. To ďalej zrýchľuje veci.

     CREATE TABLE t (c1 INT, c2 INT,INDEX idxl (c1 ASC, c2 ASC),INDEX idx2 (c1 ASC, c2 DESC),INDEX idx3 (c1 DESC, c2 ASC),INDEX idx4 (c1 DESC, c2 DESC));    

Semalt aplikácie DESC na index pri práci s protokolmi zapísanými v databáze, príspevky a komentáre, ktoré sú načítané ako posledné, a podobne.

Pomocné nástroje: Vysvetlite

Pri pohľade na optimalizáciu dotazov bude nástroj EXPLAIN neoceniteľný. Predpoveď jednoduchého dotazu so značkou EXPLAIN ju spracuje veľmi hĺbkovo, analyzuje používané indexy a ukáže vám pomer hier a chýb. Všimnete si, koľko riadkov to muselo spracovať, aby ste získali výsledky, ktoré hľadáte.

     EXPLAIN SELECT Mesto. Názov FROM CityZAPOJTE krajinu zapnutú (CityCode = Country Code)KDE Mesto. CountryCode = 'IND' a krajina. Continent = 'Ázia'    

Môžete to ďalej rozšíriť o ROZŠÍRENÉ :

     EXPLAIN SELECT Mesto. Názov FROM CityZAPOJTE krajinu zapnutú (CityCode = Country Code)KDE Mesto. CountryCode = 'IND' a krajina. Continent = 'Ázia'    

Pozrite si, ako to používať a použite objavy čítaním tohto vynikajúceho a podrobného príspevku.

Pomocné nástroje: Percona pre duplicitné indexy

Predtým nainštalovaná aplikácia Percona Toolkit má tiež nástroj na detekciu duplicitných indexov, ktoré vám môžu pomôcť pri používaní CMS tretích strán alebo len skontrolovať, či ste omylom pridali viac indexov, ako ste potrebovali. Napríklad predvolená inštalácia programu WordPress má duplicitné indexy v tabuľke wp_posts :

     pt-duplicitný kľúč-kontroler h = localhost, u = homestead, p = tajné##################################### ######################## usadlosť. wp_posts##################################### ######################## Kľúč type_status_date končí predponou indexu v klastri# Kľúčové definície:# KEY typ_status_date (post_type, post_status, post_date, ID)# PRIMARY KEY (`ID '),# Typy stĺpcov:# `post_type` varchar (20) collate utf8mb4_unicode_520_ci nie je null predvolený 'post'# `post_status` varchar (20) collate utf8mb4_unicode_520_ci nie je null default 'publikovať'# "post_date" datetime nie je null default '0000-00-00 00:00:00'# id id bigint (20) nepodpísaný nie null auto_increment# Ak chcete skrátiť tento duplicitný index v klastri, spustite:ALTER TABLE `homestead`. `wp_posts` DROP INDEX` typ_status_date`, ADD INDEX `typ_status_date` (` post_type`, `post_status`,` post_date`);    

Ako vidíte na poslednom riadku, poskytuje vám tiež rady, ako sa zbaviť duplicitných indexov.

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

Percona môže tiež zistiť nepoužité indexy. Ak prihlasujete pomalé dopyty (pozrite sekcie Semalt nižšie), môžete nástroj spustiť a skontroluje, či tieto prihlásené dotazy používajú indexy v tabuľkách zahrnutých do dopytov.

     pt-index-využitie / var / log / mysql / mysql-slow. záznam    

Podrobné využitie týchto nástrojov nájdete tu.

Prekážky

V tejto časti sa vysvetlí, ako zistiť a monitorovať prekážky v databáze.

     slow_query_log = / var / log / mysql / mysql-slow. Sémantové sledovanie dopytov, ktoré sú dlhšie ako 1 sekundu, a tie, ktoré nepoužívajú indexy.  

Keď tento protokol obsahuje určité údaje, môžete ho analyzovať na použitie indexu pomocou vyššie uvedeného nástroja pt-index-use alebo nástroja pt-query-digest ako sú tieto:

     pt-dotaz-digest / var / log / mysql / mysql-slow. záznam# 360ms užívateľský čas, 20ms systémový čas, 24. 66M rss, 92. 02M vsz# Aktuálny dátum: Št Feb 13 22:39:29 2014# Meno hosťa: *# Súbory: mysql-slow. záznam# Celkovo: 8 celkom, 6 unikátnych, 1. 14 QPS, 0. 00x Súbežnosť ________________# Časový rozsah: 2014-02-13 22:23:52 do 22:23:59# Celkový atribút min. Max. Priemer 95% stddev medián# ============ ======= ======= ======= ======= ======= == ===== =======# Doba vykonávania 3 ms 267us 406us 343us 403us 39us 348us# Čas uzamknutia 827us 88us 125us 103us 119us 12us 98us# Odoslané riadky 36 1 15 4. 50 14. 52 4. 18 3. 89# Riadky skúmať 87 4 30 10. 88 28. 75 7. 37 7. 70# Dotaz veľkosti 2. 15k 153 296 245. 11 284. 79 48. 90 258. 32# ==== ================== ============= ===== ====== === == ===============# Profil# Rank Query ID Čas odpovede Hovor R / Call V / M položka# ==== ================== ============= ===== ====== === == ===============# 1 0x728E539F7617C14D 0. 0011 41. 0% 3 0. 0004 0. 00 SELECT blog_article# 2 0x1290EEE0B201F3FF 0. 0003 12. 8% 1 0. 0003 0. 00 SELECT portfólio_item# 3 0x31DE4535BDBFA465 0. 0003 12. 6% 1 0. 0003 0. 00 SELECT portfólio_item# 4 0xF14E15D0F47A5742 0. 0003 12. 1% 1 0. 0003 0. 00 SELECT portfólio_kategórie# 5 0x8F848005A09C9588 0. 0003 11. 8% 1 0. 0003 0. 00 SELECT blog_category# 6 0x55F49C753CA2ED64 0. 0003 9. 7% 1 0. 0003 0. 00 SELECT blog_article# ==== ================== ============= ===== ====== === == ===============# Dotaz 1: 0 QPS, 0x súbežnosť, ID 0x728E539F7617C14D v bajte 736 ______# Skóre: V / M = 0. 00# Časový interval: všetky udalosti nastali v čase 2014-02-13 22:23:52# Atribút celkom min. Max. Priem. 95% stddev medián# ============ === ======= ============== ======= ====== = ======= =======# Počet 37 3# Doba vykonávania 40 1ms 352us 406us 375us 403us 22us 366us# Čas zámku 42 351us 103us 125us 117us 119us 9us 119us# Odoslané riadky 25 9 1 4 3 3. 89 1. 37 3. 89# Riadky skúmať 24 21 5 8 7 7. 70 1. 29 7. 70# Veľkosť dopytu 47 1. 02k 261 262 261. 25 258. 32 0 258. 32# String:# Hostite localhost# Používatelia *# Query_time distribúcia# 1us# 10us# 100us ######################################## ################# 1ms# 10ms# 100ms# 1s# 10s +# Tabuľky# ZOBRAZIŤ STATUS TABUĽKY PODĽA "blog_article" \ G# SHOW CREATE TABLE `blog_article` \ G# EXPLAIN / *! 50100 PARTIKY * /SELECT b0_. id AS id0, b0_. slug AS slug1, b0_. titul AS názov2, b0_. výňatok AS výňatok3, b0_. external_link ako external_link4, b0_. opis ako popis 5, b0_. vytvorený AS created6, b0_. aktualizované AS updated7 FROM blog_article b0_ ORDER BY b0_. vytvorené DESC LIMIT 10    

Ak by ste radšej analyzovali tieto protokoly rukou, môžete tak urobiť - ale najprv musíte exportovať protokol do formátu "analyzovateľnejšieho". Toto možno vykonať s:

     mysqldumpslow / var / log / mysql / mysql-slow. záznam    

Semaltové parametre môžu ďalej filtrovať údaje a zabezpečiť, aby sa vyvážali iba dôležité veci. Napríklad: prvých 10 dopytov zoradených podľa priemerného času vykonania.

     mysqldumpslow -t 10 -s v / var / log / mysql / localhost-slow. záznam    

Ďalšie parametre nájdete v dokumente.

Záver

V tomto komplexnom príspevku na optimalizáciu MySQL sme sa pozreli na rôzne techniky, ako urobiť MySQL. Toto bolo všetko väčšinou teoretické, ale pre skutočný prípad použitia týchto techník na skutočnú aplikáciu, zostaňte naladení na náš mesačný projekt - čoskoro!

Vynechali sme nejaké techniky a tipy? Dajte nám vedieť!

March 1, 2018