Teknologian jakaminen

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

MySQL-lokien sisältö on erittäin tärkeä ja sitä kysytään usein haastatteluissa. Samalla lokiin liittyvän tiedon hallinta auttaa meitä myös ymmärtämään MySQL:n taustalla olevat periaatteet ja auttaa meitä tarvittaessa vianmäärityksessä ja ongelmien ratkaisemisessa.
MySQL:n yleiset lokityypit sisältävät pääasiassa seuraavat luokat (InnoDB-tallennuskoneelle):

• Virheloki (virheloki): tallentaa MySQL:n käynnistys-, käynnistys- ja sammutusprosessit.
• Binääriloki (binarylog, binlog): tallentaa pääasiassa SQL-käskyjä, jotka muuttavat tietokannan tietoja.
• Yleinen kyselyloki: Kaikki yhteyden muodostaneen asiakkaan MySQL-palvelimelle lähettämät SQL-tietueet Koska SQL:n määrä on suhteellisen suuri, se ei ole oletuksena käytössä, eikä sitä suositella.
• Hidas kyselyloki (sow querylog): Kyselyn suoritusaika ylittää long_query_time sekuntia, käytetään ratkaistaessa SQL:n hitaan kyselyn ongelmia.
• Tapahtumaloki (palautusloki ja kumoamisloki): redo log on redo log, ja undo log on palautusloki.
• Välitysloki: Välitysloki on replikointiprosessin aikana luotu loki, joka on monilta osin samanlainen kuin binääriloki. Välitysloki on kuitenkin suunnattu orjatietokantaan master-slave-replikaatiossa.
• DDL-loki (metadatalog): DDL-käskyjen suorittamat metatietotoiminnot

Binaariloki (binlog) ja tapahtumaloki (uudelleenkirjoitusloki ja peruutusloki) ovat tärkeämpiä ja vaativat keskittymistämme.

1. Hidas kyselyloki

Hidas kyselyloki tallentaa kaikki kyselylauseet, joiden suoritusaika ylittää long_query_time (oletus on 10s, yleensä asetettu 1s).
Hitaan SQL:n löytäminen on ensimmäinen askel SQL-lauseiden suorituskyvyn optimoimiseksi. Käytä sitten EXPLAIN-komentoa hitaan SQL:n analysoimiseen ja suoritussuunnitelman tietojen saamiseksi.
Voit käyttää show-muuttujia, kuten "slow_query_log"-komentoa, jotta voit tarkistaa, onko hidas kyselyloki käytössä oletuksena.

Voidaan ottaa käyttöön asetuksella SET GLOBAL slow_query_log=ON

Parametri long_query_time määrittää, kuinka kauan kyselyssä kestää, ennen kuin se voidaan määrittää hitaaksi kyselyksi.

Sitä voidaan myös muokata: aseta globaali long_query_time = 12

Varsinaisissa projekteissa hidas kyselyloki voi olla suhteellisen suuri ja sitä on hankala analysoida suoraan. Voimme käyttää virallista MySQL:n hitaan kyselyn analysointi- ja viritystyökalua. mysqldumpslow . Blogini on myös yksinkertaisesti yhdistetty mysqldumpslow-työkaluun:

[MySQL] mysqldumpslow -työkalu - hitaiden kyselylokitiedostojen yhteenveto -CSDN-blogi

1.1 Kuinka kysytään hitaiden kyselylauseiden nykyinen määrä?

MySQL:ssä on muuttuja, joka tallentaa nykyisen määrän hitaita kyselylauseita. Voit käyttää show globaalia tilaa kuten'%slo
w_queries%';

1.2 Kuinka optimoida hitaat kyselyt

MySQL tarjoaa meilleSELITTÄÄkomento saadaksesi tietoa suoritussuunnitelmasta.
Suoritussuunnitelma viittaa SQL-käskyn tiettyyn suoritustapaan sen jälkeen, kun MySQL-kyselyn optimoija on optimoinut sen. Suoritussuunnitelmia käytetään yleensä skenaarioissa, kuten SQL:n suorituskyvyn analysoinnissa ja optimoinnissa. EXPLAINin tulosten avulla voit oppia tietoja, kuten tietotaulukon kyselysekvenssin, tietokyselyn toimintotyypin, mihin indekseihin voidaan osua, mihin indekseihin todellisuudessa osuma, kuinka monta tietueriviä kussakin tiedossa taulukosta kysytään ja muuta tietoa. Erityisesti voimme optimoida SQL:n seuraavilla yleisillä menetelmillä:

1. Vältä käyttämästä SELECT*
SELECT * kuluttaa enemmän suoritinta.
SELECT *Hyödyttömät kentät lisäävät verkon kaistanleveyden resurssien kulutusta ja tiedonsiirtoaikaa, varsinkin suuret kentät (kuten varchar,
möykky, teksti).
SELECT * ei voi käyttää MySQL-optimointiohjelmaa indeksin optimointiin (MySQL-optimointiohjelmaan perustuva "peittoindeksi"-strategia on erittäin nopea ja tehokas, ja se on erittäin suositeltava kyselyn optimointimenetelmä alalla)
SELECT <kenttäluettelo> voi vähentää taulukkorakenteen muutosten vaikutusta.

2. Sivutuksen optimointi

Tavallinen sivutus vie suhteellisen vähän aikaa, kun dataa on vähän.

Jos datan määrä kasvaa suureksi, miljooniksi tai jopa kymmeniksi miljooniksi, tavallinen henkilöhaku kestää hyvin kauan.

Kuinka optimoida Voit muokata yllä olevaa SQL-lausetta alikyselyksi.

Ensin kysytään ensisijainen avaimen arvo, joka vastaa ensimmäistä raja-parametria, ja sitten suodatetaan ja rajataan tämän ensisijaisen avaimen arvon perusteella, jotta tehokkuus on nopeampi. Tämä menetelmä toimii kuitenkin vain, jos tunnukset ovat positiivisessa järjestyksessä.

Alikyselyn tulos luo kuitenkin uuden taulukon, joka vaikuttaa suorituskykyyn. Laajaa alikyselyjen käyttöä tulee välttää. Lisäksi tätä menetelmää voidaan soveltaa vain, kun ID on positiivisessa järjestyksessä. Monimutkaisissa hakuskenaarioissa on usein tarpeen suodattaa ehdot täyttävät tunnukset suodatusehtojen avulla. Tällä hetkellä tunnukset ovat erillisiä ja epäjatkuvia.

3. Tee vähemmän liitoksia

Alibaban kehitysopas:

Voit lukea keskustelun Zhihusta:

https://www.zhihu.com/question/68258877https://www.zhihu.com/question/682588774. On suositeltavaa olla käyttämättä vieraita avaimia ja kaskadeja

Alibaba Java -kehitysopas:

5. Valitse sopiva kenttätyyppi

6. Yritä käyttää UNIONIN sijasta UNION ALL

UNION laittaa kaikki kahden tulosjoukon tiedot väliaikaiseen taulukkoon ja suorittaa sitten duplikoinnin poistotoiminnon, joka vie enemmän aikaa ja kuluttaa enemmän suoritinresursseja.
UNION ALL ei enää poista tulosjoukon kopioita, ja saadut tiedot sisältävät päällekkäisiä kohteita.
Jos datan kaksoiskappaleet eivät kuitenkaan ole sallittuja varsinaisessa liiketoimintaskenaariossa, UNIONia voidaan silti käyttää.

7. Erätoiminnot

Tietokannan tietojen päivityksissä, jos erätoimintoja voidaan käyttää, käytä niitä mahdollisimman paljon tietokantapyyntöjen määrän vähentämiseksi ja suorituskyvyn parantamiseksi.

8. Käytä indeksejä oikein

Tässä osiossa on paljon sisältöä, joka esitellään myöhemmin erillisessä blogissa.

2. binlog-binääriloki

binlog (binääriloki on binäärilokitiedosto) tallentaa pääasiassa kaikki MSQL-tietokannassa muutetut toiminnot (kaikki tietokannan suorittamat DDL- ja DML-käskyt), mukaan lukien taulukkorakenteen muutokset (CREATE, ALTER, DROP TABLE.), taulukkotiedot. muutokset ( INSERT.UPDATE, DELETE..), mutta se ei sisällä SELECT-, SHOW- ja muita toimintoja, jotka eivät aiheuta muutoksia tietokantaan.

Voit käyttää show binary logs -komentoa nähdäksesi luettelon kaikista binäärilokeista:

2.1 Binlog-muoto

Binääritallennusmenetelmiä on 3 tyyppiä:

• Lauseketila: Jokainen SQL-käsky, joka muuttaa tietoja, tallennetaan binlogiin, kuten lisäykset, päivitykset ja poistot.·
• Rivitila (suositus): Kunkin rivin erityiset muutostapahtumat tallennetaan binlogiin. ·
• Yhdistelmätila: Lause- ja rivitilan sekoitus. Lausuntotilaa käytetään oletuksena, ja se siirtyy automaattisesti rivitilaan joissakin erikoistilanteissa.

Row-tilaan verrattuna ilmoitustilassa lokitiedosto on pienempi, myös levyn IO-paine on pienempi ja suorituskyky on parempi. Sen tarkkuus on kuitenkin huonompi kuin rivitila.

Ennen MySQL 5.1.5:tä binlogin muoto oli vain STATEMENT 5.1.5, joka alkoi tukea binlogia ROW-muodossa. Versiosta 5.1.8 alkaen MySQL alkoi tukea binlogia MIXED-muodossa. Ennen MySQL 5.7.7:ää oletusarvoisesti käytettiin lausuntotilaa. MySQL5.7.7 käyttää oletusarvoisesti rivitilaa.

Voit käyttää näyttömuuttujia kuten'%binlog format%' nähdäksesi binlogin käyttämän muodon

2.2 Binlogin rooli

Binlogin pääsovellusskenaario on isäntä-orja-, isäntä-orja- ja isäntä-orja-toiminnot, jotka ovat erottamattomia binlogista, jotta tietojen synkronointi ja tietojen johdonmukaisuus voidaan varmistaa.

Isäntä-orja-replikoinnin periaate on esitetty alla olevassa kuvassa:

1. Pääkirjasto kirjoittaa tietokannan tietojen muutokset binlogiin
2. Yhdistä orjakirjasto pääkirjastoon
3. Orjakirjasto luo I0-säikeen pyytääkseen päivitetyn binlogin pääkirjastosta.
4. Pääkirjasto luo binlog-vedossäikeen binlogin lähettämistä varten, ja orjakirjaston I/0-säie on vastuussa vastaanottamisesta Hirsi.
6. Lue välitysloki kirjaston SQL-säikeestä ja synkronoi tiedot paikallisesti (eli suorita SQL uudelleen)

2.3 Kuinka valita binlog-huuhtelun ajoitus?

InnoDB-tallennuskoneessa tapahtuman suorittamisen aikana loki kirjoitetaan ensin binlogcacheen. Vasta kun tapahtuma on lähetetty, binlogcache-loki säilyy levyllä olevaan binlog-tiedostoon. Muistiin kirjoittaminen on nopeampaa, ja tämä tapahtuu myös tehokkuussyistä.

Koska tapahtuman binlogia ei voida jakaa, oli tapahtuma kuinka suuri tahansa, se on kirjoitettava kerran, joten järjestelmä varaa jokaiselle säikeelle muistilohkon binlog-välimuistiksi. Voimme ohjata yhden säikeen binlogcache-kokoa parametrilla binlog_cache_size. Jos tallennussisältö ylittää tämän parametrin, se on tallennettava väliaikaisesti levylle (swap).

Joten milloin binlog huuhdellaan levylle Voit ohjata biglog-huuhtelun ajoitusta sync_binlog-parametrilla. Arvoalue on 0-N ja oletusarvo on 0:
·0: Ei pakollista vaatimusta, järjestelmä päättää milloin kirjoittaa levylle.
·1: Aina kun tapahtuma lähetetään, binlog on kirjoitettava levylle:
·N: Binlog kirjoitetaan levylle joka N tapahtuma.Menetyksen riski

Ennen MySQL5.7:ää sync_binlogin oletusarvo oli 0. MySQL5.7:n jälkeen sync_binlogin oletusarvo on 1. Yleensä ei ole suositeltavaa asettaa sync_binlog-arvon arvoksi 0. Jos suorituskykyvaatimukset ovat suhteellisen korkeat tai esiintyy levyn IO-pullonkaula, sync_binlog-arvoa voidaan nostaa asianmukaisesti. Tämä kuitenkin lisää tietojen menetyksen riskiä.

2.4 Missä olosuhteissa binlog luodaan uudelleen?

Kun kohtaavat seuraavat kolme tilannetta, MySQL luo uudelleen uuden lokitiedoston ja tiedoston sarjanumeroa kasvatetaan.

• MySQL-palvelin pysähtyy tai käynnistyy uudelleen
• Huuhtele lokit -komennon käytön jälkeen
• Kun binlog-tiedoston koko ylittää binlogin enimmäiskoon muuttujan kynnyksen.

3. redo log redo log

Tiedämme, että InnoD8-tallennuskone hallitsee tallennustilaa sivuyksiköissä. Tieto, jonka lisäämme MySQL:ään, on viime kädessä sivulla. Levyn IO:n ylikuormituksen vähentämiseksi muistissa on myös alue nimeltä puskurivarasto. Kun tietojamme vastaavaa sivua ei ole puskurivarastossa, MSQL tallentaa ensin levyllä olevan sivun välimuistiin puskurivarastoon, jotta myöhemmin käytämme sivua suoraan puskurivarastoon, mikä parantaa huomattavasti luku- ja kirjoitussuorituskykyä. .

Kun tapahtuma on sitoutunut, muokkauksiamme vastaavalle puskurivarannon sivulle ei välttämättä säilytetä levylle. Jos MySQL kaatuu yhtäkkiä tällä hetkellä, katoavatko tämän tapahtuman muutokset suoraan?
Ilmeisesti ei, jos näin on, se ilmeisesti loukkaisi kaupan kestävyyttä.

MySQLInnoDB-moottori käyttää redo-lokia varmistaakseen tapahtumien kestävyyden. Pääasia, mitä redo-loki tekee, on tallentaa sivumuutokset, kuten kuinka monta tavua on muokattu tietyllä sivulla tietyllä siirtymällä ja mikä on tietty muokattu sisältö. Jokainen uudelleenkirjoituslokin tietue sisältää taulukkotilan numeron, tietosivun numeron, siirtymän, tietyt muokatut tiedot ja voi jopa tallentaa muokatun tiedon pituuden (riippuen uudelleenkirjoituslokin tyypistä).
Kun tapahtuma on sitoutunut, huuhtelemme uudelleenlokin levylle huuhtelustrategian mukaisesti, jotta vaikka MySQL kaatuisi, levylle kirjoittamatta jääneet tiedot voidaan palauttaa uudelleenkäynnistyksen jälkeen, mikä varmistaa levyn kestävyyden. kauppa. Toisin sanoen uusintaloki antaa MySQL:n kaatumisen palautusominaisuudet.

1. Varmista tietojen kestävyys (Durability)

Redo Log tallentaa kaikki muutostoimenpiteet tietokantaan. Kun tietokanta suorittaa kirjoitusoperaatioita (INSERT, UPDATE, DELETE), nämä toiminnot kirjataan ensin Redo Log -kirjaan ja lisätään sitten datatiedostoon. Tällä tavalla, vaikka järjestelmä epäonnistuu ennen kuin tietojen muokkaustoiminto on kirjoitettu kokonaan levylle, Redo Log voi varmistaa, että tiedot eivät katoa. Palautuksen aikana tietokanta tekee nämä keskeneräiset muokkaustoimenpiteet uudelleen Redo Logista tietojen johdonmukaisuuden varmistamiseksi.

2. Tietojen palautus (palautus)

Redo Log auttaa tietokantaa palautumaan tasaiseen tilaan järjestelmän kaatumisen tai odottamattoman sähkökatkon jälkeen. Palautusprosessin aikana tietokanta tarkistaa Redo Log -tietueet ja ottaa uudelleen käyttöön kaikki lähetetyt mutta ei säilyneet tietomuutokset datatiedostoihin tietojen palauttamiseksi.

3. Paranna kirjoitussuorituskykyä

Kirjoitustoimintojen suorituskyvyn parantamiseksi tietokannat käyttävät usein välimuistimekanismeja (kuten puskurivarantoja) muokkaustoimintojen väliaikaiseen tallentamiseen muistiin sen sijaan, että ne kirjoittaisivat ne välittömästi levylle. Redo Login olemassaolo tekee tämän välimuistimekanismin mahdolliseksi, koska niin kauan kuin Redo Login säilyminen varmistetaan, ei ole vaaraa tietojen katoamisesta, vaikka välimuistin tietoja ei olisi kirjoitettu levylle.

Kun suoritat tapahtuman, lokipuskurissa oleva redo-loki tyhjennetään levylle. Voit käyttää innodb_flush_log_at
sitoutua parametrien hallintaan. Meidän on kiinnitettävä huomiota oikean huuhtelukäytännön asettamiseen innodb_flush_log_at_trx_commit MySQL:ssä määritetystä huuhtelustrategiasta riippuen, MySQL:n kaatumisen jälkeen saattaa ilmetä pieniä tietojen häviämisongelmia.

innodb_flush_log_at_trx_commit Se on tärkeä konfiguraatioparametri MySQL InnoDB -tallennuskoneessa. Se määrittää lokin tyhjennys- ja kirjoitusstrategiat, kun tapahtuma lähetetään, mikä vaikuttaa tietojen kestävyyteen ja suorituskykyyn. Sillä on kolme arvoa, nimittäin 0, 1 ja 2. Jokainen arvo edustaa erilaista harjausstrategiaa.

• innodb_flush_log_at_trx_commit = 0

  • kuvata : Kun tapahtuma sitoutuu, loki kirjoitetaan lokipuskuriin, mutta ei heti levylle. Lokitiedostot kirjoitetaan levylle joka sekunti, ja lokipuskuri tyhjennetään joka sekunti.
  • etu: Parempi suorituskyky, koska levyn I/O-toiminnot vähenevät.
  • puute: Kun järjestelmä kaatuu, kaikki viimeisen 1 sekunnin tapahtumat voivat kadota.
  • Sovellettava kohtaus: Soveltuu sovellusskenaarioihin, joissa on korkeat suorituskykyvaatimukset ja löyhät tiedon pysyvyysvaatimukset.

• innodb_flush_log_at_trx_commit = 1

  • kuvata : Aina kun tapahtuma sitoutuu, loki kirjoitetaan levylle välittömästi ja lokipuskuri tyhjennetään levylle välittömästi. Tämä on turvallisin asetus ja varmistaa tapahtumien kestävyyden.
  • etu: Korkein suojaus, joka varmistaa, että jokainen lähetetty tapahtuma säilyy, ja lähetetyt tapahtumat eivät katoa, vaikka järjestelmä kaatuisi.
  • puute: Suorituskyky heikkenee, koska jokainen vahvistus laukaisee levyn I/O-toiminnon.
  • Sovellettava kohtaus: Soveltuu sovellusskenaarioihin, jotka vaativat suurta tietojen pysyvyyttä, kuten rahoitusjärjestelmät, sähköinen kaupankäynti jne.

• innodb_flush_log_at_trx_commit = 2

  • kuvata : Aina kun tapahtuma sitoutuu, loki kirjoitetaan lokipuskuriin ja tyhjennetään välittömästi levylle, mutta sitä ei kirjoiteta välittömästi lokitiedostoon. Lokitiedostot kirjoitetaan levylle kerran sekunnissa.
  • etu: Parantaa suorituskykyä jossain määrin samalla kun se vähentää mahdollisesti menetettyjen tietojen määrää (tapahtumat menetetään enintään 1 sekunnin sisällä).
  • puute: Kun järjestelmä kaatuu, viimeisen 1 sekunnin tapahtumat voivat kadota.
  • Sovellettava kohtaus: Soveltuu sovellusskenaarioihin, joissa on tietyt suorituskyvyn ja tietojen kestävyyden tasapainovaatimukset.

Yhteenveto harjastrategiasta

• 0: Paras suorituskyky, mutta suurin riski, kaikki viimeisen 1 sekunnin tapahtumat voidaan menettää.
• 1: Turvallisin, joka varmistaa, että jokainen sitoutunut tapahtuma jatkuu ja suorituskyky on suhteellisen alhainen.
• 2: Kompromissi suorituskyvyn ja turvallisuuden välillä.

4. Tietoa katoaa

1. Toistoloki kirjoitetaan lokipuskuriin, mutta sitä ei ole vielä kirjoitettu sivun välimuistiin. Tällä hetkellä tietokanta kaatuu ja tietoja katoaa (tämä tietojen menetys voi tapahtua, kun tyhjennyskäytännön innodb_flush log_at trx_commit arvo on. 0);

2. Toistoloki on kirjoitettu sivun välimuistiin, mutta sitä ei ole vielä kirjoitettu levylle. Käyttöjärjestelmä kaatuu ja tietoja voi hävitä (tämä tietojen menetys voi tapahtua, kun tyhjennyskäytännön innodb2 flush log_at trx_commit arvo on. 2).

5. Mitä eroa on binlogilla ja redologilla?

• Binlogia käytetään pääasiassa tietokannan palauttamiseen, joka kuuluu tietotason tietojen palautukseen, on binlogin yleisin sovellusskenaario. Redologia käytetään pääasiassa tapahtumatason tietojen palauttamiseen.
• Redolog on ainutlaatuinen InnoDB-moottorille, ja binlog on yhteinen kaikille tallennuskoneille, koska binlog on toteutettu MySQL:n palvelinkerroksella.
• Redolog on fyysinen loki, joka tallentaa pääasiassa tietyn sivun muutoksen. Binlog on looginen loki, joka tallentaa pääasiassa kaikki tietokannan suorittamat DDL- ja DML-käskyt.
• Binlog kirjoitetaan liittämällä, eikä kokoa ole rajoitettu. Redolog käyttää kirjoittamiseen silmukkakirjoitusmenetelmää, jossa on kiinteä koko Kun kirjoitat loppuun, se palaa alkuun kirjoittaakseen lokeja silmukassa.

4. kumoa loki kumoa loki

Kumoamisloki (palautusloki) on loki, jota käytetään tietokantajärjestelmässä tietojen muokkaustoimintojen tallentamiseen. Se tallentaa kaikkien tietojen muokkaustoimintojen käänteiset toiminnot (eli peruutusoperaatiot) tapahtuman suorittamisen aikana. Undo Logilla on keskeinen rooli tapahtuman peruutuksessa. Undo Log voi palauttaa tiedot tapahtuman alkamista edeltävään tilaan, mikä varmistaa tapahtuman atomisuuden.

Kuinka Undo Log varmistaa tapahtumien atomiteetin

Tapahtuman atomisuus tarkoittaa, että kaikki tapahtuman toiminnot joko suoritetaan tai niitä ei suoriteta. Undo Log varmistaa tapahtumien atomiteetin seuraavilla mekanismeilla:

1. Tallenna kumoamistoiminnot Tapahtuman suorittamisen aikana tietojen muuttaminen tallentaa vastaavan kumoamistoiminnon Kumouslokiin ennen varsinaista muutosta. Jos tapahtuma esimerkiksi päivittää tietuerivin arvon, vanha arvo kirjataan peruutuslokiin ennen päivitystä.

2. Palautustapahtuma Jos tapahtuma epäonnistuu jostain syystä (kuten virheestä tai nimenomaisesta palautuksesta), tietokantajärjestelmä lukee Kumoa-lokin ja palauttaa tiedot tapahtuman alkamista edeltävään tilaan tallennetun kumoamistoiminnon mukaisesti. Tällä tavalla voit varmistaa, että epäonnistuneella tapahtumalla ei ole vaikutusta tietokantaan, mikä varmistaa tapahtuman atomisuuden.

Lainata:

Yksityiskohtainen selitys luku-kirjoitus-erottelusta ja alitietokannasta ja JavaGuide |