Teknologian jakaminen

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

---

1. Infrastruktuuri: Miten SQL-kyselykäsky suoritetaan?

Yleiskatsaus

Yleisesti ottaen MySQL voidaan jakaa kahteen kerrokseen

• Palvelinkerros
  Kattaa suurimman osan MySQL:n ydinpalvelutoiminnoista
  • Liitin
  • Kyselyvälimuisti
  • Analysaattori
  • optimoija
  • Toimilaite
  • Kaikki sisäänrakennetut toiminnot (kuten päivämäärä, aika, matemaattiset ja kryptografiset toiminnot jne.)
  • Toiminnot eri tallennusmoottoreissa
    • tallennettu menettely
    • laukaista
    • näkymä
    • ……
• varastointimoottorin kerros
  Plug-in-arkkitehtuuri, joka vastaa tietojen tallentamisesta ja hausta
  • Innodb
  • MyISAM
  • Muisti

Liitin

mysql -h$ip -P$port -u$user -p
1

Yhteyskomennon mysql on asiakastyökalu, jota käytetään yhteyden muodostamiseen palvelimeen.Kun olet suorittanut klassisen TCP-kättelyn, liitin
Se alkaa tunnistaa henkilöllisyyttäsi Tällä hetkellä käytetään antamaasi käyttäjätunnusta ja salasanaa.

• Jos käyttäjätunnus tai salasana on väärä, saat "Käyttäjälle estetty pääsy" -virheilmoituksen ja sitten asiakasohjelma
  Lopeta suoritus.
• Jos käyttäjätunnuksen ja salasanan todennus läpäisee, liitin onnistuuLupataulukko Selvitä, mitä käyttöoikeuksia sinulla on siellä.Jälkeenpäin tässä yhteydessä
  Lupapäätöslogiikka riippuu tällä hetkellä luetuista luvista.

Jos asiakas on passiivinen liian pitkään, liitin katkaisee sen automaattisesti. Tätä aikaa ohjaa parametri wait_timeout, ja oletusarvo on 8 tuntia.

Jos asiakas lähettää pyynnön uudelleen yhteyden katkeamisen jälkeen, se saa virhemuistutuksen: Lost connection to MySQL server during query . Jos haluat jatkaa tällä hetkellä, sinun on muodostettava yhteys uudelleen ja suoritettava pyyntö.

Tietokannassa pitkä yhteys tarkoittaa, että yhteyden onnistumisen jälkeen, jos asiakas jatkaa pyyntöjen tekemistä, käytetään aina samaa yhteyttä. Lyhyt yhteys tarkoittaa, että yhteys katkeaa muutaman kyselyn suorittamisen jälkeen ja uusi muodostetaan uudelleen seuraavaa kyselyä varten.

Yhteyden muodostusprosessi on yleensä monimutkainen, joten suosittelen, että yrität minimoida yhteyden muodostustoiminnot käytön aikana, eli yritä käyttää pitkiä yhteyksiä.

Mutta kun kaikki pitkät yhteydet on käytetty, saatat huomata, että joskus MySQL:n käyttämä muisti kasvaa hyvin nopeastiMySQL:n suorituksen aikana väliaikaisesti käyttämää muistia hallitaan yhteysobjektissa. . Nämä resurssit vapautetaan, kun yhteys katkeaa.Niin josPitkien yhteyksien kertyminen voi johtaa liialliseen muistin käyttöön., tappoi järjestelmän (OOM) väkisin. Ilmiöstä päätellen MySQL käynnistyi uudelleen epänormaalisti.

Kuinka ratkaista tämä ongelma? Voit harkita seuraavia kahta vaihtoehtoa.

• Katkaise ajoittain pitkät liitännät . Kun sitä on käytetty jonkin aikaa tai kun ohjelma on todennut, että suuri muistia vievä kysely on suoritettu, yhteys katkeaa, kysely vaaditaan ja yhteys muodostetaan uudelleen.
• Jos käytät MySQL 5.7:ää tai uudempaa, voit suorittaa sen mysql_reset_connection alustaaksesi yhteysresurssit uudelleen. Tämä prosessi ei vaadi uudelleenyhteyttä ja käyttöoikeuksien tarkistamista, mutta palauttaa yhteyden tilaan, jossa se juuri luotiin.

Kyselyvälimuisti

Kun MySQL on saanut kyselypyynnön, se siirtyy ensin kyselyn välimuistiin nähdäkseen, onko tämä käsky suoritettu aiemmin. Aiemmin suoritetut lauseet ja niiden tulokset voidaan tallentaa välimuistiin suoraan muistiin avainarvo-parien muodossa. Avain on kyselylause, ja arvo on kyselyn tulos. Jos kyselysi löytää avaimen suoraan tästä välimuistista, arvo palautetaan suoraan asiakkaalle.

Jos käsky ei ole kyselyn välimuistissa, suoritusvaihe jatkuu. Kun suoritus on valmis, suorituksen tulokset tallennetaan kyselyn välimuistiin. Voit nähdä, että jos kysely osuu välimuistiin, MySQL voi palauttaa tuloksen suoraan suorittamatta myöhempiä monimutkaisia ​​​​toimintoja, mikä on erittäin tehokasta.

Mutta suurimman osan ajasta aionOn suositeltavaa, että et käytä kyselyjen välimuistia ,miksi? Koska kyselyjen välimuistista on usein enemmän haittaa kuin hyötyä.

Kyselyvälimuisti mitätöidään hyvin usein Niin kauan kuin taulukkoon on päivitys, kaikki tämän taulukon kyselyvälimuistit tyhjennetään. Joten on mahdollista, että vaivauduit tallentamaan tulokset, ja ennen kuin edes käytit niitä, päivitys pyyhki ne pois. Tietokannoissa, joissa on kova päivityspaine, kyselyvälimuistin osumaprosentti on hyvin alhainen. Ellei yritykselläsi ole staattista taulukkoa, joka päivitetään vain kerran pitkään. Jos kyseessä on esimerkiksi järjestelmän määritystaulukko, tämän taulukon kysely sopii kyselyn välimuistiin.

Onneksi MySQL tarjoaa myös tämän "use on demand" -menetelmän. Voit asettaa query_cache_type-parametrin arvoksi DEMAND, jotta kyselyn välimuistia ei käytetä oletusarvoisissa SQL-käskyissä. Jos olet varma, että haluat käyttää kyselyvälimuistia, voit määrittää sen nimenomaisesti SQL_CACHE:llä, kuten seuraava käsky:

select SQL_CACHE * from T where ID=10；
1

täytyy olla tietoinen,MySQL 8.0 -versio poistaa suoraan koko kyselyn välimuistitoiminnon, mikä tarkoittaa, että tämä toiminto ei ole enää käytettävissä versiosta 8.0 alkaen.

Analysaattori

Jos kyselyn välimuistiin ei osuta, käskyn varsinainen suoritus alkaa. Ensinnäkin MySQL:n on tiedettävä, mitä haluat tehdä, joten sen on jäsennettävä SQL-lause.

optimoija

Toimilaite

2. Kirjausjärjestelmä: Miten SQL-päivityskäsky suoritetaan?

tee loki uudelleen

En tiedä muistatko vielä artikkelin "Kong Yiji". Hotellin johtajalla on vaaleanpunainen taulu, jota käytetään erityisesti vieraiden luottotietojen tallentamiseen. Jos luotolla maksajia ei ole paljon, hän voi kirjoittaa taululle asiakkaan nimen ja tilin. Mutta jos luottotilejä on liikaa, tulee aina aikoja, jolloin fanilautakunta ei pysty seuraamaan niitä. Tällä hetkellä kauppiaalla on oltava kirjanpito nimenomaan luottotilien kirjaamista varten.

Jos joku haluaa maksaa luoton tai velan, kauppiaalla on yleensä kaksi vaihtoehtoa:

• Yksi tapa on avata suoraan pääkirja ja lisätä tai vähentää luottotili;
• Toinen lähestymistapa onKirjoita ensin tilit tällä kertaa vaaleanpunaiselle taululle ja ota sitten tilikirjat pois sulkemisajan jälkeen ja laske ne.。

Kun bisnes kukoistaa ja tiskillä on kiire, kauppias valitsee ehdottomastijälkimmäinen , koska edellinen toiminta on liian hankalaa. Ensin sinun on löydettävä tietue tämän henkilön kokonaisluottotilistä. Ajattele sitä, siellä on kymmeniä tiiviisti pakattuja sivuja löytääkseen nimen, kauppias saattaa joutua laittamaan silmälasit päähän ja etsimään sen löydettyään, ja hän ottaa sen esiin ja kirjoittaa tuloksen takaisin pääkirja.

Tämä koko prosessi on hankala ajatella. Sitä vastoin on helpompi kirjoittaa se ensin vaaleanpunaiselle taululle. Ajattele sitä, jos kauppiaalla ei ole apua vaaleanpunaisesta taulusta, hänen on käännettävä kirjanpito joka kerta kun hän kirjaa tilit, eikö tehokkuus ole sietämättömän alhainen?

Vastaavasti tämä ongelma esiintyy myös MySQL:ssä. Jos jokainen päivitystoiminto on kirjoitettava levylle, ja levyn on myös löydettävä vastaava tietue ennen päivitystä, koko prosessin IO-kustannukset ja hakukustannukset ovat erittäin korkeat. Tämän ongelman ratkaisemiseksi MySQL:n suunnittelijat käyttivät päivityksen tehokkuuden parantamiseksi samanlaista ideaa kuin hotellin kauppiaan vaaleanpunainen taulu.

Koko yhteistyöprosessi vaaleanpunaisen taulun ja kirjanpidon välillä on itse asiassa se, mitä usein mainitaan MySQL:ssä. WAL tekniikka,WAL Koko nimi onWrite-Ahead Logging, tärkeintä onKirjoita ensin loki ja sitten levylle, eli kirjoita ensin vaaleanpunainen taulu ja sitten tilikirja, kun et ole kiireinen.

Tarkemmin sanottuna, kun tietue on päivitettävä, InnoDB-moottori kirjoittaa ensin tietueen redo-lokiin (vaaleanpunainen kortti) ja päivittää muistin. Tällä hetkellä päivitys on valmis. Samalla InnoDB-moottori päivittää käyttötietueen levylle sopivana ajankohtana, ja tämä päivitys tehdään usein järjestelmän ollessa suhteellisen tyhjäkäynnillä, aivan kuten kauppias tekee sulkemisen jälkeen.

Jos luottotilejä ei ole tänään paljon, kauppias voi odottaa sulkemisaikaan asti selvittääkseen tavarat. Mutta mitä tehdä, jos luottotilejä on tiettynä päivänä paljon ja vaaleanpunainen taulu on täynnä? Tällä hetkellä kauppiaalla ei ollut muuta vaihtoehtoa kuin lopettaa työnsä, päivittää joitakin vaaleanpunaisen taulun luottotietueita pääkirjaan ja sitten poistaa nämä tietueet vaaleanpunaisesta taulusta tehdäkseen tilaa uusille tileille.

Samoin InnoDB:n redo-lokin koko on esimerkiksi 4 gigatavun kokoinen. Aloita kirjoittaminen alusta ja palaa sitten alkuun kirjoittaaksesi silmukan alla olevan kuvan mukaisesti.

write pos on nykyisen tietueen sijainti Se liikkuu taaksepäin kirjoitettaessa tiedoston nro 3 loppuun, se palaa tiedoston nro 0 alkuun. Tarkistuspiste on senhetkinen poistettava paikka, joka myös liikkuu eteenpäin ja silmukoi, ennen tietueen poistamista tietue on päivitettävä tietotiedostoon.

Kirjoitusasetuksen ja tarkistuspisteen välinen tila on "vaaleanpunaisen taulun" tyhjä osa, jota voidaan käyttää uusien toimintojen tallentamiseen. Jos kirjoitusasema saavuttaa tarkistuspisteen, se tarkoittaa, että "vaaleanpunainen taulu" on täynnä, eikä uusia päivityksiä voida suorittaa tällä hetkellä. Sinun on ensin pysäytettävä ja poistettava joitain tietueita, jotta voit edetä tarkistuspisteessä.

Toistolokin avulla InnoDB voi varmistaa, että vaikka tietokanta käynnistyisi uudelleen epänormaalisti, aiemmin lähetetyt tietueet eivät katoacrash-safe。

Ymmärtääksesi törmäyssuojan käsitteen, ajattele aiempaa luottotietoesimerkkiämme. Niin kauan kuin luottotietue on kirjoitettu vaaleanpunaiselle taululle tai kirjoitettu reskontralle, vaikka kauppias unohtaisi sen myöhemmin, kuten äkillisesti keskeyttäisi liiketoiminnan muutamaksi päiväksi, hän voi silti selvittää luottotilin kirjanpidon tietojen avulla ja vaaleanpunainen taulu liiketoiminnan jatkamisen jälkeen.

binlog

Kuten aiemmin mainitsimme, MySQL:llä kokonaisuutena on kaksi osaa: yksi on palvelinkerros, joka tekee asioita pääasiassa MySQL:n toiminnallisella tasolla, toinen on moottorikerros, joka vastaa tietyistä tallennusasioista.Vaaleanpunainen taulu, josta puhuimme ylläredo log on InnoDB-moottorille ainutlaatuinen loki,ja Palvelintasolla on myös oma lokinsa, nimeltään binlog (arkistoloki)。

Luulen, että kysyt, miksi siellä on kaksi tukia?

Koska MySQL:ssä ei alussa ollut InnoDB-moottoria. MySQL:n oma moottori on MyISAM, mutta MyISAMilla ei ole kaatumisturvallisia ominaisuuksia ja binlog-lokeja voidaan käyttää vain arkistointiin. Toinen yritys esitteli InnoDB:n MySQL:ään liitännäisenä. Koska pelkkään binlogiin luottamisessa ei ole kaatumisturvallisia ominaisuuksia, InnoDB käyttää toista lokijärjestelmää, eli redo logia, saavuttaakseen kaatumisturvallisia ominaisuuksia.

Näillä kahdella lokilla on seuraavat kolme eroa.

1. Toistoloki on ainutlaatuinen InnoDB-moottorille, ja binlog on toteutettu MySQL:n palvelintasolla, ja sitä voivat käyttää kaikki moottorit.
2. redo log on fyysinen loki, kirjaa "mitä muutoksia on tehty tietylle tietosivulle";binlog on looginen loki, tallennetaan tämän lauseen alkuperäinen logiikka, kuten "lisää 1 sen rivin c-kenttään, jossa on ID=2".
3. redo log kirjoitetaan silmukalla, tila käytetään loppuun;binlog voidaan kirjoittaa lisäksi . "Liitä kirjoitus" tarkoittaa, että kun binlog-tiedosto saavuttaa tietyn koon, se vaihtaa seuraavaan eikä korvaa edellistä lokia.

Näiden kahden lokin käsitteellisen ymmärryksen avulla tarkastellaan suorittajan ja InnoDB-moottorin sisäisiä prosesseja suoritettaessa tätä yksinkertaista päivityskäskyä.

1. Suoritin etsii ensin moottoria saadakseen rivin ID=2. ID on ensisijainen avain, ja kone käyttää suoraan puuhakua löytääkseen tämän rivin. Jos tietosivu, jossa rivi ID=2 sijaitsee, on jo muistissa, se palautetaan suoraan suorittajalle, muuten se on ensin luettava muistiin levyltä ja sitten palautettava.
2. Suoritin saa koneen antamat rivitiedot, lisää tähän arvoon 1:n, esim. se oli aiemmin N, mutta nyt se on N+1, saa uuden rivin dataa ja sitten kutsuu moottorin käyttöliittymää kirjoittamaan tämän uusi tietorivi.
3. Moottori päivittää tämän uuden tietorivin muistiin ja tallentaa päivitystoiminnon tällä hetkellä tee loki uudelleen sisäänvalmistella osavaltio. Ilmoita sitten toimeenpanijalle, että toteutus on suoritettu ja tapahtuma voidaan lähettää milloin tahansa.
4. Suoritin luo tästä operaatiosta binlogin ja asettaa levylle kirjoitettu binlog。
5. Suoritin kutsuu moottorin commit-tapahtumarajapintaa, ja moottori kirjoittaa tee loki uudelleen Muuta lähetettäväksi (tehdä) tila, päivitys on valmis.

Tässä annan tämän päivityskäskyn suorituksen vuokaavion Kuvan vaalea laatikko osoittaa, että se suoritetaan InnoDB:n sisällä, ja tumma laatikko osoittaa, että se suoritetaan suorittajassa.

päivityslauseen suoritusprosessi

Olet ehkä huomannut, että kolme viimeistä vaihetta näyttävät hieman "pyöreiltä".

kaksivaiheinen sitoutuminen

Miksi tarvitaan "kaksivaiheinen toimittaminen"?Tämä mahdollistaa eron kahden lokin välilläloogisesti johdonmukainen . Tämän ongelman selittämiseksi meidän on aloitettava artikkelin alussa olevasta kysymyksestä: Kuinka palauttaa tietokanta minkä tahansa sekunnin tilaan puolen kuukauden sisällä?

Kuten aiemmin totesimme, binlog tallentaa kaikki loogiset toiminnot ja ottaa käyttöön "lisäkirjoituksen". Jos DBA lupaa, että se voidaan palauttaa puolen kuukauden kuluessa, varmuuskopiointijärjestelmä tallentaa ehdottomasti kaikki viimeisen puolen kuukauden binlogit ja järjestelmä tekee säännöllisesti varmuuskopiot koko tietokannasta. "Tavallinen" riippuu tässä järjestelmän tärkeydestä, joka voi olla kerran päivässä tai kerran viikossa.

Kun haluat palauttaa tiettyyn sekuntiin, esimerkiksi kello kaksi iltapäivällä eräänä päivänä, huomaat, että taulukko on vahingossa poistettu keskipäivällä ja sinun on palautettava tiedot, voit tehdä näin:

• Etsi ensin uusin täydellinen varmuuskopio, jos olet onnekas, se voi olla viime yön varmuuskopio ja palauta tästä varmuuskopiosta väliaikaiseen tietokantaan.
• Sitten varmuuskopiointiajasta alkaen varmuuskopiointilokit otetaan peräkkäin ja toistetaan siihen aikaan, kun taulukko vahingossa poistettiin keskipäivällä.
  Tällä tavalla väliaikainen tietokanta on sama kuin online-tietokanta ennen sen vahingossa poistamista. Sitten voit ottaa taulukkotiedot pois väliaikaisesta tietokannasta ja palauttaa ne online-tietokantaan tarpeen mukaan.

Okei, kun on puhuttu tietojen palautusprosessista, palataanpa takaisin ja puhutaan siitä, miksi loki tarvitsee "kaksivaiheisen vahvistuksen". Tässä voisimme yhtä hyvin käyttää selittämiseen ristiriitaista todistetta.

Koska redo log ja binlog ovat kaksi itsenäistä logiikkaa, jos kaksivaiheista toimitusta ei käytetä, joko uudelleenkirjoitusloki on kirjoitettava ensin ja sitten binlog tai käänteinen järjestys. Katsotaanpa, mitä ongelmia näissä kahdessa menetelmässä on.

Käytä silti edellistä päivityslausetta esimerkkinä. Oletetaan, että kentän c arvo nykyisellä rivillä, jonka tunnus on 2, on 0, ja oletetaan, että päivityskäskyn suorittamisen aikana tapahtuu kaatuminen ensimmäisen lokin kirjoittamisen jälkeen, mutta ennen toisen lokin kirjoittamista.

• Kirjoita ensin redo log ja sitten binlog.
  Oletetaan, että MySQL-prosessi käynnistyy uudelleen epänormaalisti, kun redo-loki kirjoitetaan, mutta ennen binlogin kirjoittamista. Kuten aiemmin totesimme, uudelleenkirjoituslokin kirjoittamisen jälkeen, vaikka järjestelmä kaatuisi, tiedot voidaan silti palauttaa, joten c:n arvo tällä rivillä palautuksen jälkeen on 1. Koska binlog kuitenkin kaatui ennen kuin se oli valmis, tätä lausuntoa ei tallennettu binlogiin tällä hetkellä. Siksi, kun loki varmuuskopioidaan myöhemmin, tämä lausunto ei sisälly tallennettuun binlogiin. Sitten huomaat, että jos sinun on käytettävä tätä binlogia väliaikaisen kirjaston palauttamiseen, koska tämän käskyn binlog on kadonnut, väliaikaista kirjastoa ei päivitetä tällä kertaa. Palautetun rivin c:n arvo on 0, mikä on sama kuin alkuperäisen kirjaston arvo.
• Kirjoita ensin binlog ja tee sitten loki uudelleen.
  Jos binlogin kirjoittamisen jälkeen tapahtuu kaatuminen, koska uudelleenkirjoituslokia ei ole vielä kirjoitettu, tapahtuma on virheellinen kaatumisen palautuksen jälkeen, joten c:n arvo tällä rivillä on 0. Mutta loki "Muuta c 0:sta 1:ksi" on tallennettu binlogiin. Siksi, kun binlogia käytetään palauttamiseen myöhemmin, tulee vielä yksi tapahtuma. Palautetun rivin c:n arvo on 1, mikä eroaa alkuperäisen tietokannan arvosta.
  Voidaan nähdä, että jos "kaksivaiheista toimitusta" ei käytetä, tietokannan tila voi olla ristiriidassa sen lokin avulla palautetun kirjaston tilan kanssa.

Voit sanoa, onko tämä todennäköisyys erittäin pieni. Ei ole tilanteita, joissa väliaikainen kirjasto on palautettava milloin tahansa.

Itse asiassa ei, tätä prosessia ei tarvita vain tietojen palauttamiseen väärinkäytön jälkeen. Kun sinun on laajennettava kapasiteettia, eli kun sinun on rakennettava lisää valmiustilatietokantoja lisätäksesi järjestelmän lukukapasiteettia, yleinen käytäntö on nyt käyttää täydellistä varmuuskopiointia ja käyttää binlogia tämän saavuttamiseksi epäjohdonmukaisuus isäntä- ja orjatietokantojen välillä verkossa.

Yksinkertaisesti sanottuna sekä redo-lokia että binlogia voidaan käyttää ilmaisemaan tapahtuman vahvistustila.Kaksivaiheisen toimituksen tarkoituksena on pitää kaksi tilaa loogisesti johdonmukaisina.。