Back to Question Center
0

Miten optimoida MySQL: indeksit, hidas kyselyt, kokoonpano            Miten optimoida MySQL: indeksit, hitaat kyselyt, konfigurointiTietoja: DatabasePatterns & KäytännötDevelopment EnvironmentDrupalDebugging & Semalt

1 answers:
MySQL: n optimointi: indeksit, hidas kyselyt, kokoonpano

MySQL on edelleen maailman suosituin relaatiotietokanta, mutta se on silti kaikkein optimoimaton - monet jättävät sen oletusarvoihin, eivät häiritse tutkimusta. Tässä artikkelissa tarkastelemme jo aiemmin käsiteltyjä MySQL-optimointivinkkejä ja yhdistelemme niitä jo julkaisemien uutisten kanssa.

Määrittelyoptimointi

Ensimmäinen - ja eniten ohitettu! - suorituskyky päivittää jokaisen käyttäjän MySQL pitäisi tehdä on tweak kokoonpano. 5. 7 (nykyinen versio) on paljon parempia oletuksia kuin edeltäjänsä, mutta on silti helppo tehdä parannuksia näiden lisäksi.

Oletamme, että käytät Linux-pohjaista isäntää tai hyvää Vagrant-laatikkoa, kuten Homestead Improved, joten asetustiedostosi on / etc / mysql / my. cnf . On mahdollista, että asennus tosiasiallisesti lataa toisen konfigurointitiedoston kyseiseen kokoonpanotiedostoon, joten katsokaa, että - minun. cnf -tiedostossa ei ole paljon sisältöä, tiedosto / etc / mysql / mysql. conf. d / mysqld. cnf might.

Kokoonpanon muokkaus

Semaltin on oltava mukava komentorivin avulla. Vaikka et ole vielä altistunut sille, nyt on yhtä hyvä aika kuin missään.

Jos muokkaat paikallisesti Vagrant-ruutuun, voit kopioida tiedoston päätiedostojärjestelmään kopioimalla se jaettuun kansioon cp / etc / mysql / my. cnf / home / vagrant / Code ja muokata sitä tavallisella tekstieditorilla ja kopioi se sitten takaisin kun se on valmis. Muussa tapauksessa käytä yksinkertaista tekstieditoria, kuten vim, suorittamalla sudo vim / etc / mysql / my. cnf .

Huomaa: muokkaa yllä olevaa polkua konfigurointitiedoston todellisen sijainnin mukaiseksi - on mahdollista, että se on oikeastaan ​​ / etc / mysql / mysql. conf. d / mysqld. cnf

Manuaalinen tweaks

Seuraavat käsikiristimet on tehtävä laatikosta. Lisää ohjeet konfigurointitiedostoon kohdassa [mysqld] :

Innodb_buffer_pool_size = 1G # (säätää arvoa tässä, 50% -70% kaikista RAM-muistista)innodb_log_file_size = 256Minnodb_flush_log_at_trx_commit = 1 # voi muuttua 2: ksi tai 0: ksiinnodb_flush_method = O_DIRECT
  • innodb_buffer_pool_size - puskurivarasto on tallennusalue tietojen tallentamiseen ja muistin hakemistoihin. Sitä käytetään säilyttämään usein käytettyjä tietoja muistiin ja kun käytät dedikoitua tai virtuaalista palvelinta, jossa DB on usein pullonkaula, on järkevää antaa tämä osa sovelluksestasi eniten RAM-muistia. Siksi annamme sille 50-70% kaikista muistista. MySQL-dokumenteissa on puskurivarastointiohjeisto.
  • lokitiedoston koko on hyvin selitetty tässä, mutta pähkinänkuoressa se on kuinka paljon tietoa tallennetaan lokiin ennen pyyhkimistä. Huomaa, että loki tässä tapauksessa ei ole virheloki tai jotain, jota saatat käyttää, vaan se ilmaisee tarkistuspisteen ajankohdan, koska MySQL: n avulla kirjoitukset tapahtuvat taustalla, mutta silti vaikuttavat etualan suorituskykyyn. Suuret lokitiedostot merkitsevät parempaa suorituskykyä, koska uusia ja pienempiä tarkistuspisteitä luodaan, mutta pidempi palautusaika törmäyksessä (enemmän tavaraa täytyy kirjoittaa uudelleen DB: hen).
  • innodb_flush_log_at_trx_commit on selitetty tässä ja ilmaisee, mitä tapahtuu lokitiedoston kanssa. Yhdellä meillä on turvallisin asetus, koska loki huuhdotaan levylle jokaisen tapahtuman jälkeen. 0 tai 2 on vähemmän happoa, mutta suorituskykyisempi. Ero tässä tapauksessa ei ole riittävän suuri, jotta se olisi suurempaa kuin 1. asetuksessa asetetut stabiiliuset.
  • innodb_flush_method - ylhäältä alaspäin suhteessa huuhteluun, tämä asetetaan arvoon O_DIRECT kaksinkertaisen puskuroinnin välttämiseksi. Tämä on aina tehtävä, ellei I / O-järjestelmä ole erittäin alhainen.

Semaltista toinen työkalu auttaa meitä löytämään jäljellä olevat ongelmat automaattisesti. Huomaa, että jos olisimme suorittaneet sen ilman edellä mainittuja manuaalisia tweaks-asetuksia, vain yksi neljästä korjauksesta olisi tunnistettu manuaalisesti, koska muut 3 riippuvat käyttäjän preferenssista ja sovelluksen ympäristöstä.

Miten optimoida MySQL: indeksit, hidas kyselyt, kokoonpanoMiten optimoida MySQL: indeksit, hitaat kyselyt, konfigurointiTietoja:
DatabasePatterns & PracticesDevelopment EnvironmentDrupalDebugging & Semalt

Muuttujan tarkastaja

Asenna muuttuva tarkastaja Ubuntuun:

     wget https: // repo. percona. com / apt / percona-release_0. 1-4. $ (lsb_release -sc) _all. debytanttisudo dpkg -i percona-release_0. 1-4. $ (lsb_release -sc) _all. debytanttisudo apt-get päivityssudo apt-get asenna percona-toolkit    

Muiden järjestelmien osalta noudata ohjeita.

Suorita työkalupakki sitten:

     pt-muuttujan neuvonantaja h = localhost, u = homestead, p = salainen    

Sinun pitäisi nähdä tulos, joka ei ole toisin kuin tämä:

     # WARN delay_key_write: MyISAM-indeksilohkoja ei koskaan huuhdota, ennen kuin niitä tarvitaan. # HUOM max_binlog_size: max_binlog_size on pienempi kuin 1 Gt: n oletusarvo. # NOTE sort_buffer_size-1: Sort_buffer_size-muuttuja tulisi yleensä jättää oletusarvoksi, ellei asiantuntija katso, että se on tarpeen muuttaa. # HUOM innodb_data_file_path: Automaattiset InnoDB-tiedostot voivat kuluttaa paljon levytilaa, jota on vaikea hakea myöhemmin. # WARN log_bin: binääritallennus on poistettu käytöstä, joten pisteiden palautus ja toisinnus eivät ole mahdollisia.     

Mikään näistä ei ole kriittinen, niitä ei tarvitse korjata. Ainoa, jonka voisimme lisätä, olisi binäärinen loki replikointiin ja tilannekuviin.

Huomautus: binlog-koko on oletuksena 1G uudemmissa versioissa, eikä PT merkitse sitä.

     max_binlog_size = 1Glog_bin = / var / log / mysql / mysql-bin. lokipalvelin-id = master-01binlog-format = 'ROW'    
  • max_binlog_size -asetus määrittää, kuinka suuret binäärilokit tulevat. Nämä ovat lokeja, jotka kirjaavat tapahtumat ja kyselyt ja tekevät tarkistuspisteitä. Jos tapahtuma on suurempi kuin maksimi, loki saattaa olla suurempi kuin maksimi, kun se tallennetaan levylle - muussa tapauksessa MySQL pitää ne rajoissa.
  • log_bin -vaihtoehto mahdollistaa binaarisen kirjaamisen kokonaan. Ilman sitä ei ole snapshotting tai replikaatio. Huomaa, että levytila ​​voi olla hyvin rasittava. Palvelin-ID on välttämätön vaihtoehto, kun aktivoidaan binääritutkimus, joten lokit tietävät, mistä palvelimesta ne ovat peräisin (replikointia varten) ja muoto on vain tapaa, jolla lokit kirjoitetaan.

Kuten näette, uudessa MySQL: ssä on hyvät oletusarvot, jotka tekevät asioita valmiiksi valmiiksi. Tietenkin jokainen sovellus on erilainen ja siinä on muitakin mukautettuja tweaksia.

MySQL-viritin

Semalt seuraa tietokantaa pidemmillä aikaväleillä (käytä sitä kerran viikossa tai niin suorana sovelluksena) ja ehdottaa muutoksia sen perusteella, mitä se näkyy lokeissa.

Selvitä se yksinkertaisesti lataamalla se:

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

ajettaessa sitä . / Mysqltuner. pl pyytää sinua antamaan adminin käyttäjätunnuksen ja salasanan tietokannalle ja antamaan tietoja nopeasta skannauksesta. Esimerkiksi tässä on InnoDB-osio:

     [-] InnoDB on käytössä. [-] InnoDB-ketjun rinnakkaisuus: 0[OK] InnoDB-tiedosto per taulukko on aktivoitu[OK] InnoDB-puskurin pooli / datakoko: 1. 0G / 11. 2M[!] Suhde InnoDB-lokitiedoston koko / InnoDB-puskurin koko (50%): 256. 0M * 2/1. 0G: n tulee olla 25%[!] InnoDB-puskurivarasto <= 1G ja Innodb_buffer_pool_instances (! = 1). 65% (19146 osumaa / 19809 yhteensä)[!] InnoDB Kirjoita Napsauta tehokkuutta: 83. 88% (640 osumaa / 763 yhteensä)[OK] InnoDB-loki odottaa: 0. 00% (0 odottaa / 123 kirjoittaa)    

Jälleen on tärkeää huomata, että tämä työkalu olisi suoritettava kerran viikossa tai niin, että palvelin on ollut käynnissä. Kun konfigurointiarvoa muutetaan ja palvelin käynnistetään uudelleen, se olisi suoritettava viikon jälkeen siitä lähtien. Hylkää hyvä idea perustaa cronjob tehdä tämä sinulle ja lähettää sinulle tulokset säännöllisesti.


Varmista, että käynnistät mysql-palvelimen uudelleen jokaisen muutoksen jälkeen:

     sudo service mysql uudelleenkäynnistys    

Indeksit

Seuraavassa keskitymme indeksiin - useimpien harrastajien DB-ylläpitäjien tärkeimpänä kipupisteenä! Erityisesti ne, jotka välittömästi hyppäävät ORM-järjestelmiin ja eivät siten todellisuudessa koskaan altistu raaka-SQL: lle.

Huomautus: termejä avaimet ja indeksit voidaan käyttää vaihtokelpoisesti.

Voit verrata MySQL-indeksit kirjan indekseihin, joiden avulla voit helposti löytää oikean sivun, joka sisältää etsimäsi aiheen. Jos indeksejä ei olisi, sinun pitäisi käydä läpi koko teos, joka etsii aiheita sisältäviä sivuja.

Kuten voitte kuvitella, on nopeampi etsiä hakemistoa kuin käydä läpi kunkin sivun. Siksi tietokantojen hakemistojen lisääminen yleensä nopeuttaa valitsemiesi kyselyiden käyttöä. Semalt, indeksi on myös luotava ja tallennettava. Joten päivitys ja lisäyskyselyt ovat hitaampia, ja se maksaa hieman enemmän levytilaa. Yleensä et huomaa eroa päivittämisestä ja lisäämisestä, jos olet indeksoinut taulukon oikein ja siksi on suositeltavaa lisätä hakemistoja oikeilla paikoilla.

Vain muutaman rivin sisältämät puolijohdot eivät todellakaan hyödy indeksoinnista. Voit kuvitella, että hakeminen 5 sivun kautta ei ole paljon hitaampaa kuin ensin mennä hakemistoon, saada sivunumero ja sitten avata kyseinen sivu.

Miten selvitämme, mitkä indeksit lisätään ja mitä indeksejä on olemassa?

Ainutlaatuiset / ensisijaiset indeksit

Ensisijaiset indeksit ovat tärkeimpiä indeksejä, jotka ovat oletuksena tapa käsitellä niitä. Käyttäjätilille voi olla käyttäjätunnus tai käyttäjätunnus, jopa pääviesti. Ensisijaiset indeksit ovat ainutlaatuisia. Semalt-indeksit ovat indeksejä, joita ei voida toistaa tietojoukossa.

Jos käyttäjä esimerkiksi valitsi tietyn käyttäjänimen, kukaan muu ei voi ottaa sitä käyttöön. Lisäämällä "ainutlaatuinen" indeksi käyttäjätunnukseen sarake ratkaisee tämän ongelman. MySQL valittaa, jos joku muu yrittää lisätä rivin, jolla on jo olemassa oleva käyttäjätunnus.

     ALTER TAULUKKO `users`ADD UNIQUE INDEX `username` (` username`) ;     

Taulukon luomisessa tavallisesti määritellään semalt-avaimet / indeksit, ja ainutlaatuiset indeksit määritellään sen jälkeen, kun taulukkoa muutetaan.

Sekä ensisijainen avain että yksilöllinen avain voidaan tehdä yhdellä sarakkeella tai useilla sarakkeilla kerralla. Jos haluat esimerkiksi määrittää maakohtaisesti vain yhden käyttäjätunnuksen, voit luoda yksilöllisen hakemiston molemmissa sarakkeissa, kuten:

     ALTER TAULUKKO `users`Lisää UNIQUE INDEX `usercountry` (` username`, `country`) ,     

Semalt-indeksit pannaan sarakkeisiin, joihin sinun on osoitettava usein. Joten jos käyttäjätiliä pyydetään usein ja sinulla on useita käyttäjätunnuksia tietokannassa, se on hyvä käyttötapaus.

Säännölliset indeksit

Säännölliset indeksit helpottavat hakuja. Epäonnistuminen on erittäin hyödyllistä, kun tarvitset tietoja tiettyä saraketta tai sarakkeen yhdistelmää nopeasti, mutta tietojen ei tarvitse olla ainutlaatuisia.

     ALTER TAULUKKO `users`ADD INDEX `usercountry` (` username`, `country`) ,     

Edellä mainitut tekijät nopeuttaisivat käyttäjien käyttäjätunnusten etsimistä maittain.

Semalt auttaa myös lajittelussa ja nopeuden ryhmittelyssä.

Fulltext Indexes

FULLTEXT-indeksiä käytetään täysi-tekstihaussa.

Nämä indeksit ovat erittäin hyödyllisiä kaikessa tekstin etsinnässä, jota sinun tarvitsee tehdä. Sanojen löytäminen tekstien rungon sisällä on Semalt-erikoisuus. Käytä näitä viesteihin, kommentteihin, kuvauksiin, arvosteluihin jne., Jos usein haet hakua sovelluksessasi.

Laskeva indeksit

Ei erityinen tyyppi, vaan muutos. Versiosta 8+ MySQL tukee laskevia indeksejä, mikä tarkoittaa, että se voi tallentaa indeksejä laskevassa järjestyksessä. Tämä voi olla kätevää, kun sinulla on valtavia pöytiä, jotka tarvitsevat usein viimeisintä lisättyä dataa tai priorisoivat merkinnät tällä tavoin. Jyrkkyys laskevassa järjestyksessä oli aina mahdollista, mutta se oli pieni suorituskyvyn rangaistus. Tämä nopeuttaa entisestään asioita.

     LUETTELO TAULUKKO t (c1 INT, c2 INT,INDEKSI idx1 (c1 ASC, c2 ASC),INDEX idx2 (c1 ASC, c2 DESC),INDEKSI idx3 (c1 DESC, c2 ASC),INDEX idx4 (c1 DESC, c2 DESC));    

Sovita DESC hakemistoon, kun käsitellään tietokantaan kirjoitettuja lokeja, viestejä ja kommentteja, jotka on lastattu ensin ja vastaava.

Helper Tools: Selitä

Tutkiessasi kyselyjen optimointia, EXPLAIN-työkalu on korvaamaton. Yksinkertaisen kyselyn esikäsittely EXPLAIN käsittelee sen hyvin perusteellisesti, analysoi käytössä olevia indeksejä ja näyttää osumien ja haittojen suhdetta. Huomaat, kuinka monta riviä sen oli käsiteltävä saadaksesi etsimäsi tulokset.

     SELAA VALINTA Kaupunki. Nimi FROM CityLiitä maa ON (kaupunki. CountryCode = maa .koodi)Missä kaupunki. CountryCode = 'IND' ja maa. Continent = 'Aasia'    

Voit jatkaa tätä jatkamalla EXTENDED :

     SELAA VALINTA Kaupunki. Nimi FROM CityLiitä maa ON (kaupunki. CountryCode = maa .koodi)Missä kaupunki. CountryCode = 'IND' ja maa. Continent = 'Aasia'    

Katso, miten voit käyttää tätä ja soveltaa löytöjä lukemalla tämän erinomaisen yksityiskohtaisen viestin.

Helper-työkalut: Percona monista indeksistä

Aikaisemmin asennetulla Percona Toolkit -ohjelmalla on myös työkalu tunnistettavien kaksoiskappaleiden tunnistamiseen, jotka voivat tulla hyödyllisiksi kolmannen osapuolen CMS: iden käyttämisessä tai vain tarkistamalla, onko vahingossa lisätty tarpeellisempia indeksejä. Esimerkiksi oletus WordPress-asennuksessa on kaksoiskappaleita wp_posts -taulukossa:

     pt-duplicate-key-tarkistaja h = localhost, u = homestead, p = salainen################################################### ######################## homestead. wp_posts################################################### ######################## Key type_status_date päättyy klusteroitujen indeksin etuliitteellä# Key määritelmät:# KEY `type_status_date` (` post_type`, `post_status`,` post_date`, `ID`),# PRIMARY KEY (`ID`),# Sarake tyypit:# `post_type` varchar (20) lajittelee utf8mb4_unicode_520_ci ei ole null default 'post'# `post_status` varchar (20) lajittelee utf8mb4_unicode_520_ci ei null default 'publish'# `post_date` datetime ei ole null default '0000-00-00 00:00:00'# `id` bigint (20) allekirjoittamaton ei nolla auto_increment# Tämän kaksoiskappaleen indeksin lyhentämiseksi suorita:ALTER TAULUKKO `homestead`. `wp_posts` DROP INDEX` type_status_date`, ADD INDEX `tyyppi_status_date` (` post_type`, `post_status`,` post_date`);    

Kuten viimeisellä rivillä on, se antaa myös neuvoja siitä, miten päästä eroon kaksoiskappaleista.

Helper Tools: Percona käyttämättömille indeksit

Percona voi myös havaita käyttämättömät indeksit. Jos kirjaudut hitaaseen kyselyyn (katso kohtaa Semalt-osio), voit käyttää työkalua ja tarkistaa, ovatko nämä kirjautuneet kyselyt käyttäneet kyselyihin liittyvien taulukoiden indeksejä.

     pt-hakemiston käyttö / var / log / mysql / mysql-slow. loki    

Katso tämän työkalun yksityiskohtainen käyttötarkoitus tästä.

pullonkaulat

Tässä osassa kerrotaan, miten tietokannan pullonkaulat havaitaan ja valvotaan.

     hidas_query_log = / var / log / mysql / mysql-slow. Semalt monitor-kyselyt, jotka ovat pitempiä kuin 1 sekunti ja ne, jotka eivät käytä indeksejä.  

Kun tämä loki sisältää joitain tietoja, voit analysoida sen indeksointitarkoituksessa edellä mainitun pt-index-käyttö -työkalulla tai pt-query-digest työkalulla, joka tuottaa tuloksia kuten:

     pt-query-digest / var / log / mysql / mysql-hidas. loki# 360ms käyttöaika, 20 ms järjestelmän aika, 24. 66M rss, 92. 02M vsz# Nykyinen päivämäärä: Thu Feb 13 22:39:29 2014# Käyttäjänimi: *# Tiedostot: mysql-slow. loki# Kaiken kaikkiaan: 8 yhteensä, 6 ainutlaatuista, 1. 14 QPS, 0. 00x rinnakkaisuus ________________# Aikaväli: 2014-02-13 22:23:52 - 22:23:59# Ominaisuus yhteensä min max avg 95% stddev mediaani# ============ ======= ======= ======= ======= ======= == ===== =======# Suoritusaika 3ms 267us 406us 343us 403us 39us 348us# Lukon aika 827us 88us 125us 103us 119us 12us 98us# Rivit lähetetään 36 1 15 4. 50 14. 52 4. 18 3. 89# Rivit tutkivat 87 4 30 10. 88 28. 75 7. 37 7. 70# Kyselyn koko 2. 15k 153 296 245. 11 284. 79 48. 90 258. 32# ==== ================== ============= ===== ====== === == ===============# Profiili# Sijoitus Kyselyn tunnus Vasteaika Kutsu R / Call V / M Item# ==== ================== ============= ===== ====== === == ===============# 1 0x728E539F7617C14D 0. 0011 41. 0% 3 0. 0004 0. 00 VALITSE blog_article# 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# 4 0xF14E15D0F47A5742 0. 0003 12. 1% 1 0. 0003 0. 00 VALITSE portfolio_category# 5 0x8F848005A09C9588 0. 0003 11. 8% 1 0. 0003 0. 00 VALITSE blog_category# 6 0x55F49C753CA2ED64 0. 0003 9. 7% 1 0. 0003 0. 00 VALITSE blog_article# ==== ================== ============= ===== ====== === == ===============# Kysely 1: 0 QPS, 0x rinnakkaisuus, tunnus 0x728E539F7617C14D tavulla 736 ______# Tulokset: V / M = 0. 00# Aikaväli: kaikki tapahtumat sattui 2014-02-13 22:23:52# Attribuutti pct yhteensä min max avg 95% stddev mediaani# ============ === ======= ======= ======= ======= ====== ======= =======# Luku 37 3# Suoritusaika 40 1ms 352us 406us 375us 403us 22us 366us# Lukitusaika 42 351us 103us 125us 117us 119us 9us 119us# Rivit lähetetään 25 9 1 4 3 3. 89 1. 37 3. 89# Rivit tutkivat 24 21 5 8 7 7. 70 1. 29 7. 70# Kyselyn koko 47 1. 02k 261 262 261. 25 258. 32 0 258. 32# Merkkijono:# Hosts localhost# Käyttäjät *# Kyselytuntijakelu# 1us# 10us# 100us ################################################### ################# 1ms# 10ms# 100ms# 1s# 10s +# Taulukot# SHOW TABLE STATUS LIKE "blog_article" \ G# SHOW CREATE TAULUKKO `blog_article` \ G# EXPLAIN / *! 50100 OSAKKEET * /VALITSE b0_. id AS id0, b0_. slug AS slug1, b0_. nimi AS nimi2, b0_. uloste AS-erä3, b0_. external_link AS external_link4, b0_. kuvaus AS: n kuvaus5, b0_. luotu AS luotu6, b0_. updated AS updated7 FROM blog_article b0_ ORDER BY b0_. luotu DESC LIMIT 10    

Jos haluat analysoida nämä lokit käsin, voit myös tehdä sen - mutta ensin sinun on vietävä loki "analysoitavaksi" muotoiseksi. Tämä voidaan tehdä seuraavilla tavoilla:

     mysqldopslow / var / log / mysql / mysql-slow. loki    

Semalt-parametrit voivat edelleen suodattaa tietoja ja varmistaa, että vain tärkeät asiat viedään. Esimerkiksi: 10 kyselyä lajitellaan keskimäärin toteutusaikana.

     mysqldumpslow -t 10 -s osoitteessa / var / log / mysql / localhost-slow. loki    

Muiden parametrien osalta ks.

Päätelmä

Tässä kattavassa MySQL-optimointitapahtumassa tarkastelimme MySQL: n lentämisen eri tekniikoita. Tämä oli kaiken kaikkiaan teoreettinen, mutta todellinen maailma-tapaus, jossa sovellettaisiin näitä tekniikoita todelliseen sovellukseen, pysyvät viritettynä suorituskykykuukausihankkeeseemme - tulevat pian!

Onko meillä ollut mitään tekniikoita ja vinkkejä? Kerro meille!

March 1, 2018