Mene sisältöön 
MySQL:stä tulee hidas, kun kyselyt on rakennettu huonosti, indeksit puuttuvat, kokoonpano ei sovi tai resursseja on vähän - juuri tässä vaiheessa alan optimoida mysql-suorituskykyä tehokkaasti. Näytän sinulle erityisiä diagnoosivaiheita ja käytännön ratkaisuja, jotta voit löytää todelliset syyt ja poistaa pullonkaulat kohdennetusti.
Keskeiset kohdat
- Kyselyt ja suunnitteluindeksit oikein
- Konfigurointi Sopeudu työmäärään
- Resurssit Seuranta ja skaalaus
- Seuranta ja käyttää hitaita lokitietoja
- Huolto ja suunnitelmien päivitykset
Miksi MySQL on hidas: Syiden tunnistaminen
Erotan ensin kyselyongelmat, puuttuvat Indeksitkonfigurointivirheet ja resurssirajoitukset. Tehottomat SELECTit, villit JOIN-ketjut ja SELECT * lisäävät tietomäärää ja pidentävät suoritusaikaa. Ilman sopivia indeksejä MySQL joutuu skannaamaan suuria taulukoita, mikä hidastaa toimintaa huomattavasti, kun liikennettä on paljon. Liian pieni innodb_buffer_pool_size pakottaa järjestelmän lukemaan jatkuvasti levyltä, mikä lisää latenssia. Lisäksi vanhentuneet versiot tai uudempien versioiden aktivoitu kyselyvälimuisti hidastavat Teho tarpeeton.
Tarkista nopeasti: Oireet ja mitatut arvot
Aloitan hitaasta kyselylokista, suorituskykyskeemasta ja järjestelmämittareista suurimpien ongelmien tunnistamiseksi. Jarrut voidaan nähdä. Korkea CPU-arvo ja alhainen I/O-arvo viittaavat usein kyselyihin tai puuttuviin indekseihin. Paljon IOPS:ia alhaisella CPU:lla viittaa liian pieneen puskurialtaan kokoon tai pirstaleisiin tietoihin. Korkea Handler_read_rnd_next-arvo viittaa usein toistuviin taulukon täydellisiin skannauksiin. Kuormitushuippujen aikana kasvavat viiveet paljastavat myös säikeiden, yhteyksien tai tallennuksen pullonkauloja.
Lukitusten, transaktioiden ja eristämisen ymmärtäminen
Tarkastelen lukituksia aikaisin, koska täydellisistäkään indekseistä ei ole paljon apua, jos istunnot estävät toisiaan. Pitkät transaktiot pitävät vanhat versiot undo-lokissa, lisäävät puskurialtaan painetta ja pidentävät Lukituksen odotusajat. Tarkistan lukkiutumiset (SHOW ENGINE INNODB STATUS), odotusajat ja suorituskykyskeemassa olevat kohteet (data_locks, data_lock_waits). Tyypillisiä malleja ovat puuttuvat indeksit JOIN-sarakkeista (laajojen alueiden lukot), epäjohdonmukainen käyttöjärjestys useissa taulukoissa tai suuret UPDATE/DELETE-erät ilman LIMIT-rajoja.
Valitsen eristystason sopivasti: READ COMMITTED vähentää aukkolukituksia ja voi vähentää hotspotteja, kun taas REPEATABLE READ tarjoaa turvallisempia tilannekuvia. Ylläpitotöissä käytän pienempiä transaktiopaketteja, jotta Group Commit vaikuttaa ja lukot pysyvät lyhyinä. Käytän mahdollisuuksien mukaan NOWAIT- tai SKIP LOCKED -menetelmää taustatöihin, jotta vältän jonoihin juuttumisen. Asetan tarkoituksella lukkojen odotusajat (innodb_lock_wait_timeout), jotta sovellus tunnistaa virheet nopeasti ja voi yrittää uudelleen puhtaasti.
Lue ja käytä EXPLAINia oikein
EXPLAINin avulla tunnistan, miten MySQL suorittaa kyselyn ja onko merkityksellinen Pääsyreitti on olemassa. Kiinnitän huomiota tyyppiin (esim. ALL vs. ref), avaimeen, riveihin ja lisätietoihin, kuten filesortin tai väliaikaisen käytön käyttöön. Jokainen rivi, jolla ei ole indeksiä, on viritysehdokas. Tarkistan sitten WHERE-, JOIN- ja ORDER-ehdot ja luon sopivat indeksit. Seuraava pieni matriisi auttaa minua luokittelemaan tyypilliset signaalit nopeammin ja määrittelemään vastatoimet.
| Signaali | Todennäköinen syy | Työkalu/tarkistus | Nopea toiminta |
|---|---|---|---|
| type = ALL | Täydellinen pöydän skannaus | EXPLAIN, Slow-Log | WHERE/JOIN-sarakkeiden indeksi |
| filesortin käyttäminen | Lajittelu ilman vastaavaa indeksiä | EXPLAIN Extra | Indeksi ORDER BY -järjestyksessä |
| Väliaikaisten | Välitaulukko GROUP BY:tä varten | EXPLAIN Extra | Yhdistetty indeksi, yksinkertaistettu aggregaatti |
| Korkea rivien arvo | Suodatin liian myöhään/liian epätarkka | EXPLAIN-rivit | Valikoivampi WHERE- ja indeksijärjestys |
| Käsittelijä_read_rnd_next high | Monet peräkkäiset skannaukset | NÄYTÄ TILA | Lisää indeksejä, kirjoita kysely uudelleen |
Vakauta suunnitelmat: Tilastot, histogrammit ja vihjeet
Varmistan hyvät suunnitelmat pitämällä tilastot ajan tasalla ja mallintamalla valikoivuutta realistisesti. ANALYZE TABLE päivittää InnoDB:n tilastot; voimakkaasti vinoutuneille tiedoille luon histogrammit kriittisille sarakkeille, jotta optimoija voi paremmin arvioida kardinaliteetit. Jos suunnitelma hyppää indeksien välillä, tarkistan pysyvät tilastot, päivitän histogrammit erikseen tai poistan ne, jos ne ovat haitallisia. Poikkeustapauksissa asetan optimoijalle vihjeitä (esim. USE INDEX, JOIN_ORDER) tai teen indeksin aluksi näkymättömäksi, jotta vaikutuksia voidaan testata ilman riskiä. Käytän EXPLAIN ANALYZE -ohjelmaa nähdäkseni todelliset ajoajat operaattoritasolla ja paljastaakseni virhearvioinnit.
Kyselyjen nopeuttaminen: konkreettiset toimet
Vähennän ensin tietojen määrää: vain tarvittavat sarakkeet, selkeät WHERE-suodattimet, mielekkäät LIMIT. Yksinkertaistan sitten sisäkkäisiä alikyselyjä tai korvaan ne JOINeilla, joissa on sopivat indeksit. Jos mahdollista, siirrän WHERE-sarakkeiden kalliit funktiot ennalta laskettuihin kenttiin. Jaan usein toistuvat raportit pienempiin kyselyihin, jotka välimuistitallennetaan sovellustasolla. Menetelmien tiiviin esittelyn löydät seuraavista lähteistä. MySQL-strategiatjotka niputtavat juuri tällaiset vaiheet jäsennellysti yhteen.
Harjoittelu ORM:ien ja sovelluskerroksen kanssa
Puran tyypillisiä ORM-ansoja: Tunnistan N+1-kyselyt ryhmiteltyjen hitaiden lokimerkintöjen kautta ja korvaan ne nimenomaisilla JOINeilla tai eräkuormaustoiminnoilla. Korvaan SELECT *:n laihoilla projektioilla. Rakennan sivuttamisen hakumenetelmäksi (WHERE id > last_id ORDER BY id LIMIT n) suurten OFFSETien sijaan, jotka muuttuvat yhä hitaammiksi offsetin kasvaessa. Käytän preparoituja lausekkeita ja kyselysuunnitelmien välimuistiin tallentamista, jotta jäsentäjä työskentelee vähemmän. Määritän yhteyspoolien asetukset siten, että ne eivät tulvi tietokantaan tuhansilla käyttämättömillä yhteyksillä eivätkä aja sovellusta jonoihin; asetan kovat aikakatkaisut, jotta keskeytykset loppuisivat aikaisin.
Indeksit: luodaan, tarkistetaan, siistitään
Asetan indeksit erityisesti sarakkeille, jotka esiintyvät WHERE-, JOIN- ja ORDER BY -kentissä, ja kiinnitän huomiota siihen, että Jakso. Valitsen yhdistelmäindeksit yleisimpien kyselyjen valikoivuuden ja käyttösuunnitelman mukaan. Vältän yliindeksointia, koska jokainen lisäindeksi hidastaa kirjoitusoperaatioita. Tunnistan käyttämättömät indeksit käyttötilastojen avulla ja poistan ne testauksen jälkeen. TEXT- tai JSON-kenttien osalta tarkistan osittais- tai funktioindeksit, jos versio tukee niitä.
Skeeman suunnittelu, ensisijaiset avaimet ja tallennusmuodot
Ajattelen jo suorituskykyä tietomallissa: InnoDB tallentaa tiedot fyysisesti ensisijaisen avaimen mukaan (klusteroitu indeksi). Monotonisilla avaimilla (AUTO_INCREMENT, ULID, jossa on time share) vältetään sivun jakaminen ja vähennetään pirstoutumista. Puhtaat UUIDv4-avaimet hajottavat satunnaisuutta B-puuhun ja heikentävät välimuistin paikannettavuutta; jos tarvitsen UUID-avaimia, käytän muunnelmia, joissa on lajiteltavia komponentteja, tai tallennan ne binäärimuodossa (UUID_TO_BIN) tiiviimpiä indeksejä varten. Valitsen pieniä ja sopivia tietotyyppejä (INT vs. BIGINT, DECIMAL vs. FLOAT rahan vuoksi) RAM-muistin ja I/O:n säästämiseksi. Unicodea varten valitsen utf8mb4:n ja käytännöllisen kollationin (esim. _0900_ai_ci) ja tarkistan, halutaanko vertailu ilman isoja ja pieniä kirjaimia.
Rivien muotoilu (DYNAMIC) auttaa hyödyntämään sivun ulkopuolista tallennustilaa tehokkaasti; tarvittaessa jaan hyvin laajat rivit ohuiksi kuumiksi ja kylmiksi yksityiskohtaisiksi taulukoiksi. JSON:ia varten määrittelen luotuja sarakkeita (virtuaalisia/pysyviä) ja indeksoin ne erikseen sen sijaan, että toistaisin strukturoimatonta hakulogiikkaa jokaisessa kyselyssä. Pakkaus auttaa hyvin suurissa taulukoissa, jos CPU on käytettävissä; mittaan purkukustannusten ja I/O-säästöjen tasapainon kohdelaitteistossa.
Mukauta kokoonpanoa: InnoDB ja muuta
Asetan yleensä innodb_buffer_pool_size 50-70 % RAM-muistiin, jotta usein toistuvat Tiedot muistissa. Säädän innodb_log_file_size -arvon kirjoituskuormitus- ja palautustavoitteiden mukaan. Käytän innodb_flush_log_at_trx_commit -menetelmää kestävyyden ja viiveen välisen suhteen hallitsemiseksi riskin hyväksymisen mukaan. Säädän säike- ja yhteysparametrit niin, ettei jonoja ole. Poistan johdonmukaisesti käytöstä vanhentuneen kyselyvälimuistin nykyisissä versioissa.
Tehosta kirjoituskuormitusta
Niputan kirjoitukset hallittuihin tapahtumiin sen sijaan, että tekisin automaattisen siirron jokaiseen INSERT-toimitukseen. Tämä vähentää fsynkkejä ja mahdollistaa ryhmäkommitointeja. Bulk-tietoihin käytän bulk-menetelmiä (useita VALUES-luetteloita tai LOAD DATA), ohitan väliaikaisesti vieraan avaimen tarkistukset ja toissijaiset indeksit, jos eheys sallii, ja rakennan ne sitten uudelleen. Valitsen binlog-parametrit tarkoituksella: ROW-muoto on vakaampi replikointia varten, sync_binlog valvoo kestävyyttä; yhdessä innodb_flush_log_at_trx_commitin kanssa löydän hyväksyttävän kompromissin turvallisuuden ja läpimenon välillä. Tarkistan myös innodb_io_capacity(_max), jotta flush-säikeet eivät tukahduta I/O:ta eivätkä hidasta sitä.
Resurssit ja laitteisto: milloin skaalata?
Tarkistan ensin, onko ohjelmistoviritys käytetty loppuun, ennen kuin lisään uusia ohjelmia. Laitteisto osta. Jos optimoinnit eivät riitä, skaalaan RAM-muistia, käytän SSD/NVMe-tallennustilaa ja lisään suorittimen ytimiä rinnakkaisuuden vuoksi. Mittaan verkon latenssin ja tallennustilan läpimenon erikseen, jotta voin valita oikean säätöruuvin. Raskaita kuormituspiikkejä varten suunnittelen horisontaalisen helpotuksen replikaatioiden avulla. Näin saadaan hyvä yleiskuva vaativiin skenaarioihin. Opas suuria kuormia vartenjota käytän mielelläni tarkistuslistana.
Toiminta pilvipalvelussa: IOPS, hyvitykset ja rajoitukset
Otan huomioon pilvipalvelun erityispiirteet: verkkoon sidotulla lohkotallennustilalla on rajoitettu IOPS ja läpäisykyky, jotka tarkistan ja varaan. Suoritinhyvityksiä sisältävät instanssityypit kuristuvat jatkuvassa kuormituksessa; valitsen vakiosuorituskykyluokat tuottaville tietokannoille. Volyymien burst-puskurit peittävät vain lyhyellä aikavälillä; varatut IOPS/läpimenot ovat pakollisia ennustettavan suorituskyvyn kannalta. Mittaan latenssijitteriä ja suunnittelen liikkumavaraa, jotta tarkistuspisteet ja varmuuskopiot eivät painu punaisille alueille. Käyttöjärjestelmän puolella tarkistan tiedostojärjestelmän ja ajastimen asetukset, NUMA:n ja läpinäkyvät valtavat sivut, jotta InnoDB voi toimia johdonmukaisesti.
Pysyvän seurannan perustaminen
Käytän suorituskykyskeemaa, järjestelmään liittyviä mittareita ja keskitettyä Kojelauta trendejä varten. Suoritan hitaiden kyselyjen lokia jatkuvasti ja ryhmittelen samankaltaiset kyselyt yhteen. Viiveen, keskeytysten, yhteyksien lukumäärän ja I/O-huippujen hälytykset ilmoittavat ongelmista varhaisessa vaiheessa. Historialliset käyrät osoittavat, onko muutos todella parantanut suorituskykyä. Ilman seurantaa viritys jää tilannekuvaksi ja menettää vaikutuksensa uuden koodin myötä.
Testaus, käyttöönotto ja regressiosuojaus
En koskaan tee muutoksia "sokeasti": mittaan ensin perustason, säädän sitten säätöruuvin erikseen ja mittaan uudelleen. Todellisissa skenaarioissa käytän tuotantodatan tilannekuvia (anonymisoituina) ja kuormitusgeneraattoreita, jotka kuvaavat tyypillisiä työkuormia. Kyselyjen toistaminen auttaa näkemään vaikutukset suunnitelmiin ja viiveisiin. Käyttöönotossa luotan kanarialintuihin ja ominaisuuslippuihin, jotta voin ongelmien ilmetessä siirtyä takaisin välittömästi. Skeemamuutoksissa käytän online-proseduureja (esim. hyväksi havaittujen työkalujen avulla), seuraan replikaatioviiveitä ja minulla on selkeä rollback-suunnitelma. Tarkistussummilla ensisijaisen ja replikaatioiden välillä varmistetaan, että tietojen yhdenmukaisuus säilyy.
Osioinnin ja välimuistitallennuksen käyttäminen oikein
Osioin hyvin suuret taulukot päivämäärän tai avaimen mukaan skannauksen ja ylläpidon helpottamiseksi. lievittää. Säilytän lämpimät tiedot pienemmissä osioissa ja kylmät tiedot harvemmin käytetyillä muistialueilla. Sovellustasolla vähennän toistuvia kyselyjä muistin sisäisillä välimuisteilla. Tallennan usein esiintyvät aggregoinnit materialisoituina näkyminä tai esilaskentataulukoina, jos se on kannattavaa. Täydennän suurten kuormitusten strategioita koskevaa jäsenneltyä katsausta päivittäisessä toiminnassa hyväksi havaituilla malleilla.
Arkkitehtoniset päätökset kasvua varten
Vapautan kirjoitusoikeuksia replikoinnin avulla luku-orjien kanssa raportteja ja API-rajapintoja varten, jotka vaativat paljon resursseja. Lue. Jakaminen asiakasryhmien tai alueiden mukaan voi olla hyödyllistä maailmanlaajuisissa sovelluksissa. Siirrän eräajotöitä asynkronisiin työntekijöihin sen sijaan, että käyttäisin MySQL:ää väärin jonona. Erotan kriittiset taulukot, joilla on erilaiset käyttötavat, hotspottien välttämiseksi. Äärimmäisiä vaatimuksia varten tarkistan erityiset tallennusmuodot tietyille tietotyypeille.
Replikoinnin hienosäätö yksityiskohtaisesti
Pidän replikaation vakaana käyttämällä GTID-tunnuksia, säätämällä binlogin kokoa ja flush-strategioita oikein ja aktivoimalla replikaatioiden rinnakkaistamisen. Lisään replica_parallel_workers (tai applier threads) -määrää niin pitkälle kuin työkuorma sallii riippumattomat transaktiot. Puoli-synkroninen replikointi voi vähentää datahävikkiä, mutta lisää latenssia - päätän tästä SLA:n ja kirjoitusnopeuden mukaan. Seuraan replikan viivettä, koska muuten lukevat työmäärät näkevät vanhentuneita tietoja; "lue kirjoituksesi" -tilanteessa reititän kirjoitusistunnot väliaikaisesti ensisijaiseen tai käytän viiveikkunoita sovelluslogiikassa. Suunnittelen pitkät DDL:t, jotta binlog ja replikat eivät jää jälkeen.
Ylläpito ja päivitykset
Pidän MySQL-version ja liitännäiset ajan tasalla, jotta voin Virhe ja välttää vanhoja jarruja. Poistan käyttämättömät taulukot selventämisen jälkeen tilastojen ja varmuuskopioiden virtaviivaistamiseksi. Arkistot tai rollupit säilyttävät vain olennaiset historiat, jotta skannaukset pysyvät nopeina. Säännöllinen ANALYZE/OPTIMIZE valituille taulukoille auttaa minua pitämään silmällä tilastoja ja pirstaloitumista. Kerään lisää käytännön vinkkejä näihin kompakteihin SQL-vihjeitä jokapäiväiseen elämään.
Lyhyesti tiivistettynä
Löydän pullonkaulat tekemällä kyselyjä, Indeksitkokoonpano ja resurssit yhdessä. EXPLAIN, hitaat lokit ja seuranta tarjoavat minulle luotettavaa tietoa vaistonvaraisen tuntemuksen sijaan. Pienet toimenpiteet, kuten SELECT *:n poistaminen, yhdistettyjen indeksien asettaminen tai suurempi puskurivarasto, tuottavat nopeasti havaittavia vaikutuksia. Sen jälkeen päätän, tarvitaanko laitteisto- tai arkkitehtuurimuutoksia. Jos etenet tällä tavalla, voit nopeuttaa MySQL-tietokantaasi ja pitää sen sujuvana.
