Kuinka käyttää indeksejä MySQL:n suorituskyvyn parantamiseen

Etusivu » Python » Kuinka käyttää indeksejä MySQL:n suorituskyvyn parantamiseen

Jos MySQL- tai MariaDB-tietokanta alkaa tuntua hitaalta, älykäs indeksointi on yleensä nopein tapa saada aikaan massiivinen suorituskyvyn parannus. Hyvin suunnitellut indeksit voivat muuttaa tuskallisen hitaat kyselyt nopeiksi vastauksiksi, varsinkin jos taulukoissasi on jo satojatuhansia tai miljoonia rivejä.

Kääntöpuolena on, että huono tai liiallinen indeksointi voi hiljaa tappaa suorituskyvyn, lisätä tallennustilaa ja saada kirjoitustyöt indeksoimaan. Tarvitset siis vankan mentaalisen mallin indeksien toiminnasta, MySQL:n tarjoamista tyypeistä, InnoDB:n muistin käytöstä ja tyypillisistä virheistä, joita tulisi välttää. Juuri tästä tässä perusteellisessa oppaassa on kyse.

Mikä on tietokantaindeksi MySQL:ssä?

Tietokannan indeksi on tietorakenne, jonka avulla MySQL löytää rivit paljon nopeammin kuin koko taulukon skannaaminen, hyvin samalla tavalla kuin kirjan lopussa oleva indeksi. Sen sijaan, että MySQL tarkistaisi jokaisen rivin yksi kerrallaan, se noudattaa tiivistä, järjestettyä rakennetta, joka osoittaa suoraan vastaaviin tietueisiin.

Kun suoritat kyselyn ilman käyttökelpoista indeksiä, MySQL:n on yleensä suoritettava koko taulukon skannaus, lukemalla jokaisen rivin tarkistaaksesi, vastaako se WHERE- tai JOIN-lausekkeidesi ehtoja. Suurissa taulukoissa tästä tulee erittäin hidasta ja I/O-raskasta.

MySQL ja MariaDB käyttävät pääasiassa tasapainotettua puuindeksointia (B-Tree) useimmissa työkuormissa. jossa avaimet tallennetaan hierarkkiseen puurakenteeseen. Tämä rakenne pitää avaimet lajiteltuina ja sallii MySQL:n löytää arvon logaritmisessa ajassa kävelemällä puussa sen sijaan, että se lukisi jokaisen rivin levyltä.

Konkreettisemman haun tekemiseksi kuvittele Asiakas-taulukko, jossa usein haet etunimellä 'Ava', mutta etunimellä ei ole indeksiä. MySQL:n on tarkistettava jokaisen rivin etunimi, kunnes se löytää vastaavat, mikä tarkoittaa, että suorituksenaikainen ympäristö kasvaa lineaarisesti taulukon koon mukana.

Kun lisäät B-puu-indeksin first_name-muuttujalle, MySQL rakentaa lajitellun puun kaikista first_name-arvoista riviosoittimineen. joten se voi hypätä puutasojen läpi seuraten vasenta tai oikeaa oksaa, kunnes se saavuttaa lehtisolmun, joka viittaa kaikkiin riveihin, joissa first_name = 'Ava'. Sen ei enää tarvitse tarkastaa jokaista taulukon riviä.

Tärkeimmät MySQL:ssä käyttämäsi indeksityypit

MySQL tarjoaa useita tapoja indeksoida tietoja, joista jokainen on optimoitu erilaisille kyselymalleille ja datamuodoille, ja näiden vaihtoehtojen ymmärtäminen auttaa sinua valitsemaan oikean indeksin sen sijaan, että lisäisit niitä sokeasti kaikkialle.

1. Yksitasoiset (yksinkertaiset) indeksit

Yhden sarakkeen indeksi yhdistää yhden avainarvon suoraan yhteen tai useampaan taulukon riviin, ja se on yksinkertaisin ja yleisin indeksityyppi, jonka kanssa työskentelet päivittäin.

Ajattele ensisijaista avainsaraketta, kuten asiakastunnusta Asiakas-taulukossa: se yksilöi jokaisen rivin, Ja konepellin alla MySQL ylläpitää indeksiä, joka yhdistää jokaisen customer_id:n vastaavaan rivisijaintiin.

Nämä yksinkertaiset indeksit toimivat erinomaisesti pienille tai keskikokoisille taulukoille ja sarakkeille, joilla on alhainen tai keskikokoinen kardinaliteetti. kuten tila- tai luokkaliput, kunhan kyseiset sarakkeet näkyvät suodattimissa (WHERE) tai liitoksissa.

Käytä yhden sarakkeen indeksiä, kun kyselyt yleensä suodattavat yhden kentän perusteella. esimerkiksi WHERE customer_id = 123 tai WHERE status = 'active', etkä tarvitse yhdistettyä suodatusjärjestystä useiden sarakkeiden välillä.

2. Monisarakkeiset (monitasoiset/yhdistelmä) indeksit

Yhdistelmäindeksi yhdistää useita sarakkeita yhdeksi järjestetyksi rakenteeksi, jonka avulla MySQL voi tehokkaasti ratkaista kyselyitä, jotka suodattavat tai lajittelevat käyttämällä useita kenttiä yhdessä.

Tarkastellaan esimerkiksi indeksiä, joka on määritelty (address, customer_id) -kohdassa Customer-taulukossa. jossa osoite on ensin ja asiakastunnus toisena. MySQL voi sitten nopeasti löytää kaikki tietyssä osoitteessa asuvat asiakkaat ja tarvittaessa käydä heidät tehokkaasti läpi asiakastunnusjärjestyksessä.

Tämä hierarkkinen organisaatio vähentää merkittävästi vertailuja verrattuna kaikkien rivien skannaamiseen, Tämä on erityisen tärkeää suuremmille tietojoukoille, joissa suodatat usein useamman kuin yhden sarakkeen mukaan.

Yhdistelmäindeksit ovat erittäin tehokkaita, mutta järjestyksellä on väliä: Indeksi (address, customer_id) voi auttaa kyselyä suodattamaan pelkän osoitteen tai sekä osoitteen että customer_id:n perusteella, mutta sitä ei hyödynnetä täysin, jos haet vain customer_id:n perusteella.

3. Klusteroidut indeksit InnoDB:ssä

InnoDB:ssä klusteroitu indeksi määrittää sekä indeksin loogisen järjestyksen että rivien fyysisen asettelun levyllä, eli taulukon tiedot itsessään tallennetaan kyseisen indeksin mukaisessa järjestyksessä.

InnoDB käyttää rakenteensa vuoksi ensisijaista avainta (PRIMARY KEY) klusteroituna indeksinä, Joten kun valitset taulukolle ensisijaisen avaimen, määrität itse asiassa, miten rivit tallennetaan ja miten toissijaiset indeksit viittaavat näihin riveihin.

Jos esimerkiksi asiakastunnus on ensisijainen avain, InnoDB tallentaa rivit asiakastunnuksen mukaan järjestettyinä. Tämä on ihanteellista silloin, kun useat kyselyt käyttävät asiakasryhmiä kyseisen tunnuksen perusteella tai etsivät usein yksittäisiä asiakkaita heidän tunnisteensa avulla.

Tämä asettelu parantaa lukutehokkuutta kyselyissä, jotka noudattavat klusteroitua indeksijärjestystä, mutta se tarkoittaa myös sitä, että avainalueen keskelle osuvien arvojen lisääminen tai päivittäminen voi olla kalliimpaa, koska InnoDB:n on pidettävä rivit fyysisesti järjestyksessä.

Vakaan, jatkuvasti kasvavan perusavaimen (kuten automaattisesti kasvavan kokonaislukuavaimen tai aikaan perustuvan sijaisavaimen) valitseminen johtaa usein parempaan klusteroitujen indeksien suorituskykyyn, kun taas satunnaiset tai usein vaihtuvat avaimet voivat pirstaloida rakennetta ja hidastaa kirjoituksia.

4. Toissijaiset (ei-klusteroidut) indeksit

Toissijaiset indeksit ovat kaikki muut InnoDB-taulukon indeksit klusteroitua ensisijaista avainta lukuun ottamatta, ja ne tarjoavat lisää nopeita hakupolkuja muuttamatta taulukon fyysistä järjestystä.

Esimerkiksi sähköposti-indeksin lisääminen Asiakas-taulukkoon luo erillisen rakenteen, joka yhdistää jokaisen sähköpostin vastaavan rivin pääavaimeen. joten MySQL voi ensin löytää ensisijaisen avaimen toissijaisen indeksin kautta ja sitten hakea koko rivin klusteroidusta indeksistä.

Toissijaiset indeksit ovat joustavia ja mahdollistavat useiden eri sarakkeiden kyselyiden nopeuttamisen. mikä on ratkaisevan tärkeää, kun lukumallisi vaihtelevat eivätkä kaikki voi seurata ensisijaista avainta.

Jokainen toissijainen indeksi on kuitenkin päivitettävä INSERT-, UPDATE- ja DELETE-komennoilla. joten jos lisäät niitä liikaa, kirjoitusteho ja tallennustilan käyttö kärsivät huomattavasti.

5. B-puu-, hajautus-, kokoteksti- ja paikkaindeksit

Konepellin alla MySQL tukee useita indeksointirakenteita oletusarvoisen B-puun lisäksi, kukin kohdistui tietyntyyppisiin tietoihin ja kyselymalleihin.

• B-puun indeksit – InnoDB:n ja useimpien tallennusmoottoreiden oletusarvo, ihanteellinen yhtäsuuruushakuihin, alueskannauksiin sekä ORDER BY- ja GROUP BY -operaatioihin.
• Hajautusindeksit – käytetään joissakin hakukoneissa tai sisäisinä rakenteina; sopii erinomaisesti puhtaisiin yhtäläisyysvertailuihin, mutta ei alue- tai järjestyskyselyihin.
• FULLTEXT-indeksit – optimoitu tekstisisällön, lauseiden ja sanojen hakemiseen TEXT- tai VARCHAR-sarakkeista.
• SPATIAL-indeksit – kohdistuu maantieteellisiin tietotyyppeihin, mahdollistaen tehokkaat kyselyt pisteistä, viivoista ja polygoneista.

Ratkaisevaa on, että mikään yksittäinen indeksityyppi ei ole täydellinen kaikelle, Joten sinun on sovitettava indeksirakenne tietojesi luonteeseen ja tapaan, jolla sovelluksesi tekee kyselyitä.

Indeksien luominen MySQL:ssä käytännön esimerkein

Indeksien luominen MySQL:ssä on suoraviivaista SQL-tasolla, mutta vaikutus suorituskykyyn voi olla dramaattinen, joten kannattaa nähdä konkreettisia esimerkkejä ja ymmärtää, mitä kukin niistä tekee.

Asiakastaulukon mallin valmistelu

Oletetaan, että aloitat yksinkertaisella Asiakas-taulukolla, johon tallennat perusyhteystiedot. kuten kokonaislukutunniste, nimet, sähköpostiosoite, puhelinnumero ja osoite.

Voit luoda taulukon esimerkiksi näin:

CREATE TABLE Customer (
customer_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
phone_number VARCHAR(15),
address VARCHAR(255)
);

Kun olet määritellyt taulukon, täytät sen joillakin esimerkkiriveillä, joka antaa MySQL:lle todellista dataa optimointia varten ja antaa sinun testata, miten indeksit muuttavat kyselysuunnitelmia ja suoritusaikoja.

Yksinkertaisen indeksin lisääminen

Oletetaan, että haluat nopeuttaa hakuja asiakastunnuksen perusteella entisestään, ehkä siksi, että ensisijaista avainta ei ole määritelty alun perin tai käytät eri hakumoottoria tai vanhaa kaavaa.

Voit luoda perusindeksin seuraavasti:

CREATE INDEX idx_customer_id ON Customer(customer_id);

Kun tämä komento on suoritettu, MySQL kuittaa indeksin luomisen, Ja kyselyt, joissa WHERE customer_id = ? tai yksinkertaiset liitokset kyseisessä sarakkeessa nopeutuvat paljon, koska ne voivat käyttää uutta rakennetta täyden skannauksen sijaan.

Yhdistelmäindeksin luominen

Kun kyselysi suodattuvat useamman kuin yhden sarakkeen mukaan kerrallaan, Usein on järkevää luoda yhdistelmäindeksi, joka tallentaa kyseiset arvot yhdessä määritellyssä järjestyksessä.

Voit esimerkiksi nopeuttaa hakuja sekä osoitteen että asiakastunnuksen perusteella suorittamalla seuraavan komennon:

CREATE INDEX idx_address_customer_id
ON Customer(address, customer_id);

Tämä indeksi on erityisen tehokas kyselyissä, kuten WHERE osoite = ? AND asiakastunnus = ?, tai skannauksille, jotka ryhmittelevät tai lajittelevat osoitteen ja sitten asiakastunnuksen mukaan, koska MySQL voi luottaa indeksin sisällä olevaan järjestykseen.

Klusteroimaton indeksi sähköpostissa

Sähköpostikentät ovat klassisia ehdokkaita toissijaisille indekseille, koska ne ovat yleensä ainutlaatuisia tai erittäin valikoivia ja niitä käytetään usein kirjautumisiin tai tilien hakuun.

Voit lisätä sähköpostiin toissijaisen indeksin seuraavasti:

CREATE INDEX idx_email ON Customer(email);

Tämän jälkeen MySQL:n ei enää tarvitse skannata koko Customer-taulukkoa selvittääkseen WHERE email = ”, se vain selaa indeksiä, löytää vastaavan avaimen ja lukee sitten siihen liittyvän rivin käyttämällä ensisijaista avainviittausta.

Kattavat indeksit entistä nopeampaa lukemista varten

Kattava indeksi on sellainen, joka sisältää kaikki kyselyn vaatimat sarakkeet. jotta MySQL voi vastata pyyntöön kokonaan indeksirakenteesta koskematta perustaulukkoon (tunnetaan myös nimellä indeksipohjainen skannaus).

Kuvittele, että suoritat usein kyselyn, joka tarvitsee vain etunimen ja sukunimen:

CREATE INDEX idx_covering_name
ON Customer(first_name, last_name);

Kyselyissä, jotka valitsevat vain nämä kaksi kenttää ja suodattavat sen asianmukaisesti, MySQL voi lukea suoraan idx_covering_name-tiedostosta, mikä vähentää levyn I/O-lukua ja parantaa viivettä, erityisesti suurissa tietojoukoissa.

Indeksin optimointistrategiat ja parhaat käytännöt

Indeksien heittäminen jokaiseen sarakkeeseen on taattu tapa heikentää suorituskykyä, ei auttaa sitä, joten tarvitset selkeät periaatteet indeksien suunnitteluun, seurantaan ja karsimiseen ajan kuluessa.

1. Valitse oikeat indeksoitavat sarakkeet

Priorisoi sarakkeet, jotka esiintyvät usein WHERE-, JOIN-, ORDER BY- tai GROUP BY -lausekkeissa, koska juuri ne hyötyvät eniten nopeasta hausta tai lajitellusta käytöstä.

Sarakkeet, jotka sisältävät enimmäkseen ainutlaatuisia tai erittäin valikoivia arvoja, ovat erityisen hyviä indeksiehdokkaita. koska MySQL voi nopeasti rajata rivien määrän pieneen määrään, usein vain yhteen.

Toisaalta hyvin matalan kardinaliteettiasteikon omaavien sarakkeiden (kuten totuusarvolippujen) indeksointi ei välttämättä auta paljoa, koska moottorin on edelleen skannattava useita rivejä kutakin indeksiavainta varten, mikä rajoittaa hyötyä.

2. Pidä indeksit mahdollisimman lyhyinä ja kevyinä

Suuret indeksiavaimet kuluttavat enemmän muistia ja levytilaa ja hidastavat jokaista kirjoitustoimintoa, joten yleensä haluat tehdä indeksoiduista sarakkeista mahdollisimman kompakteja.

Pitkien teksti- tai muuttuvan pituisten kenttien kohdalla harkitse vain etuliitteen indeksointia, jos se riittää arvojen erottamiseen. esimerkiksi 200 merkin kentän 20 ensimmäisen merkin indeksointi, jos se riittää kyselyihisi.

Numeeristen tyyppien käyttäminen suurten tekstikenttien sijaan liitoksissa ja suodattimissa parantaa myös indeksoinnin tehokkuutta. koska kokonaislukujen ja kiinteän kokoisten arvojen vertailu ja tallennus on halvempaa.

3. Vältä yli-indeksointia

Jokainen lisäindeksi on jotain, jonka MySQL:n on päivitettävä INSERT-, UPDATE- ja DELETE-komennoilla. mikä tarkoittaa, että holtiton indeksien luominen voi hidastaa merkittävästi kirjoituspainotteisia työkuormia.

Yli-indeksointi tuhlaa myös tallennustilaa ja voi hämmentää optimoijaa, jos on olemassa useita samankaltaisia ​​indeksejä, mikä vaikeuttaa MySQL:n todella optimaalisen suunnitelman valitsemista.

Hyvä nyrkkisääntö on luoda hakemisto vain silloin, kun voit tunnistaa konkreettisia kyselyitä, joista on hyötyä, ja varmista sitten EXPLAIN-komennolla, että kyseiset kyselyt todella käyttävät uutta indeksiä.

4. Poista tarpeettomat ja käyttämättömät indeksit

On yllättävän yleistä löytää tuotantoskeemoja, joissa on paljon tarpeettomia indeksejä, joita kukaan ei muista luoneensa. varsinkin useiden kehitysvaiheiden jälkeen.

Sinun tulisi säännöllisesti tarkistaa, mitä indeksejä kyselyt käyttävät harvoin tai eivät koskaan. hyödyntämällä MySQL:n suorituskykykaavaa tai ulkoisia valvontatyökaluja käyttötilastojen keräämiseen.

Kun olet tunnistanut todella tarpeettoman indeksin, sen turvallinen poistaminen voi parantaa kirjoitustehoa ja vapauttaa tallennustilaa:

DROP INDEX idx_unnecessary_index ON Customer;

Testaa aina ensin indeksin poistamisen vaikutus testiympäristössä. erityisesti vanhoissa järjestelmissä, joissa piilotetut kyselyt voivat olla siitä riippuvaisia.

5. Analysoi kyselykuvioita ennen indeksointia

Indeksien sokea lisääminen kyselyiden toimintaa ymmärtämättä on yksi suurimmista anti-mallien ongelmista. ja johtaa usein hitaisiin kirjoituksiin eikä todelliseen lukuparannukseen.

Aloita tallentamalla hitaat ja usein esiintyvät kyselyt ja tutki ne sitten huolellisesti. täydellinen MySQL-kyselyiden optimointiopas, kiinnittäen erityistä huomiota WHERE-ehtoihin, JOIN-sarakkeisiin sekä lajittelu- tai ryhmittelylausekkeisiin.

Käytä EXPLAIN-funktiota nähdäksesi, miten MySQL aikoo suorittaa kunkin kyselyn, ja tarkistaaksesi, käyttääkö se indeksejä vai palaako se koko taulukon skannaukseen. ja tarkenna indeksointistrategiaasi vastaavasti.

InnoDB-puskuripooli ja indeksien suorituskyvyn muistipuoli

Indeksit eivät sijaitse pelkästään levyllä: InnoDB on vahvasti riippuvainen RAM-muistista puskurivarannon kautta tallentaakseen sekä dataa että indeksisivuja välimuistiin. ja tällä muistikerroksella on valtava vaikutus suorituskykyyn reaalimaailmassa.

InnoDB-puskurivarasto on suuri muistialue, johon MySQL tallentaa välimuistiin taulukkotietoja, indeksisivuja ja muokattuja rivejä, jotka odottavat tyhjentämistä. ja joitakin sisäisiä rakenteita, kuten mukautuva hajautusindeksi; katso meidän varastointijärjestelmien yleiskatsaus asiaan liittyviä näkökohtia varten.

Hallinnoiduissa palveluissa, kuten Cloud SQL:ssä, oletusarvoinen puskurivarannon koko on tyypillisesti noin 70–75 % instanssin muistista. mutta sitä voi ja usein kannattaakin säätää työmäärän ja käytettävissä olevan RAM-muistin mukaan.

Tavoitteena on tehdä puskurivarastosta riittävän suuri, jotta useimmin käytetyt tiedot ja indeksisivut pysyvät muistissa, samalla jättäen tilaa yhteyspuskureille, performance_schema-taulukoille ja muille MySQL-kuormituksille.

Voit seurata, kuinka monta lukukertaa levyltä ja puskurivarastosta tehdään. Ja jos levylukuja on paljon puskuriosumien määrään verrattuna, innodb_buffer_pool_size-arvon (ja instanssimuistin) lisääminen voi merkittävästi nopeuttaa indekseihin perustuvia lukukyselyitä.

Skeema, kyselyt ja skriptit: MySQL:n suorituskyvyn laajempi kuva

Indeksin viritys ei tapahdu tyhjiössä; skeeman suunnittelu, kyselyrakenne ja sovelluskoodi vaikuttavat kaikki MySQL:n suorituskykyyn. joten kannattaa käsitellä ympäröiviä parhaita käytäntöjä.

1. Suunnittele järkevä relaatiomalli

Käytä aikaa etukäteen selkeän relaatiorakenteen suunnitteluun asianmukaisine taulukoineen, kenttineen ja suhteineen kannattaa valtavasti helpompana ylläpitona ja ennustettavampana suorituskykynä.

Tunnistaa entiteetit, niiden ominaisuudet ja miten ne liittyvät toisiinsa, ja kääntää ne sitten taulukoiksi, ensisijaisiksi avaimiksi, viiteavaimiksi ja niitä tukeviksi indekseiksi, pyrkimällä normalisoinnin tasoon, joka välttää redundanssia menemättä kuitenkaan ylinormalisoituihin äärimmäisyyksiin.

Ensisijaiset avaimet yksilöivät jokaisen rivin, kun taas viiteavaimet ilmaisevat taulukoiden välisiä suhteita, ja molemmat ansaitsevat yleensä indeksit, koska ne esiintyvät jatkuvasti liitoksissa ja rajoitteissa.

2. Valitse kenttätyypit viisaasti

Kenttätyypeillä on suora vaikutus suorituskykyyn, erityisesti silloin, kun ne ovat osa indeksiä, joten kannattaa olla harkitsevainen yleisten tyyppien sijaan.

Käytä yhdenmukaisia ​​tietotyyppejä samantyyppisille tiedoille eri taulukoissa, koska tämä yksinkertaistaa liitoksia ja mahdollistaa MySQL:n suorittaa vertailuja tehokkaammin.

Suosi kiinteäpituisia tyyppejä (kuten CHAR tai kiinteän kokoisia numeerisia tyyppejä) tarvittaessa erittäin suurten vaihtelevan pituisten kenttien sijaan. ja vältä TEXT- tai BLOB-arvojen käyttöä arvoille, joiden perusteella suodatat tai liität tietoja säännöllisesti.

Aina kun mahdollista, määritä sarakkeet NOT NULL -arvoisiksi. koska tyhjäarvoisten kenttien käsittely voi hidastaa joitakin toimintoja ja vaikeuttaa indeksien käyttöä.

3. Pidä pöydät siisteinä ja kompakteina

Lean-taulukot ovat nopeampia, koska muistin ja levyn läpi siirrettävää dataa on vähemmän. varsinkin kun monimutkaiset kyselyt koskettavat useita rivejä tai liitoksia.

Jos käytät ROW_FORMAT-asetuksia, valitse kiinteän kokoiset rivit, kun ne ovat järkeviä. koska ne voivat tehdä peräkkäisistä lukemisista tehokkaampia verrattuna erittäin vaihteleviin rivimuotoihin.

Tarkista säännöllisesti, voidaanko vanhat tiedot arkistoida tai poistaa, Pidät kuumatyöstöjoukkosi mahdollisimman pieninä välimuistin tehokkuuden ja indeksoinnin suorituskyvyn parantamiseksi.

Suurten poistojen tai lukuisten rakenteellisten muutosten jälkeen OPTIMIZE TABLE -komennon suorittaminen voi auttaa järjestämään tallennustilaa uudelleen ja parantamaan I/O-malleja, vaikka suurten tuotantotaulukoiden kanssa kannattaa olla varovainen lukittumis- ja seisokkivaikutusten vuoksi.

4. Kirjoita kyselyitä suorituskyky mielessäsi

Vaikka indeksit olisivatkin hyviä, huonosti kirjoitettu SQL voi sabotoida suorituskykyä, Joten kyselysuunnittelun on kuljettava käsi kädessä indeksisuunnittelun kanssa.

Vältä SELECT * -operaattoria tuotantokyselyissä ja listaa erikseen vain tarvitsemasi sarakkeet. vähentäen siirrettävän datan määrää ja MySQL:n luettavien indeksi- tai datasivujen määrää.

Ole varovainen LIKE-mallien kanssa, jotka alkavat jokerimerkillä (esim. '%term'). koska ne tyypillisesti estävät normaalien B-puu-indeksien käytön; raskaaseen tekstihakuun FULLTEXT-indeksit ovat yleensä parempi vaihtoehto.

Käytä GROUP BY-, ORDER BY- ja HAVING-menetelmiä vain silloin, kun se on todella välttämätöntä. ja varmista, että kyseiset sarakkeet on indeksoitu oikein tai että ne vastaavat yhdistelmäindeksin johtavia sarakkeita aina kun mahdollista.

Hyödynnä EXPLAIN-funktiota tarkistaaksesi, miten MySQL aikoo suorittaa kyselyn, etsii merkkejä, kuten ”type = ALL” tai ”rows”, jotka ovat erittäin korkeita, jotka yleensä viittaavat täysiin skannauksiin ja heikkoon indeksin käyttöön.

5. Optimoi lisäykset ja skriptitason toiminta

Raa'an SQL:n lisäksi sovellusskriptien yhteys MySQL:ään ja vuorovaikutus sen kanssa on erittäin tärkeää kokonaisvasteen kannalta. varsinkin mittakaavassa.

Eräinsertit ovat tyypillisesti tehokkaampia kuin monet yksiriviset insertit, esimerkiksi käyttämällä INSERT INTO table (col1, col2) VALUES (…), (…), (…); jotta MySQL voi käsitellä useita rivejä kerralla ja seurata niitä MySQL-transaktioiden perusteet oikeanlaista eräkäsittelyä varten.

Sivuta kyselytulokset käyttämällä LIMIT-ominaisuutta sen sijaan, että lataat valtavia tulosjoukkoja muistiin tai lähetät ne kaikki asiakkaalle kerralla. mikä lyhentää latausaikoja ja resurssien käyttöä.

Ota käyttöön välimuistikerrokset (muistin sisäiset välimuistit, sovellustason välimuistit, istuntotallennus) harvoin muuttuville mutta usein luetuille tiedoille, toistuvien MySQL-lukujen poistaminen ja järjestelmän kestävyyden parantaminen huippukuormituksen aikana.

Minimoi tarpeettomat tietokantayhteydet ja pidä yhteyksien käyttöikä kohtuullisena, usein yhteyksien yhdistämisen kautta ja suorittamalla raskasta prosessointia vasta tiedon noutamisen jälkeen sen sijaan, että se tehtäisiin yhteyden ollessa auki.

Yleisiä indeksointivirheitä ja niiden välttäminen

Indeksien väärinkäyttö voi hiljaisesti aiheuttaa valtavia suorituskykyongelmia, joten on hyödyllistä tietää yleisimmät sudenkuopat ja miten niitä voi kiertää.

Yli-indeksointi

Indeksien lisääminen jokaiseen sarakkeeseen "varmuuden vuoksi" on klassinen aloittelijan virhe. koska jokainen ylimääräinen indeksi hidastaa kaikkia kirjoitustoimintoja ja kuluttaa lisää tallennustilaa.

Sinun tulisi lyhentää indeksiluetteloa vastaamaan varsinaisten kyselyidesi tarpeita. kyselylokien ja suorituskykytyökalujen avulla ymmärretään, mitkä indeksit toimivat oikeasti ja mitkä ovat tehottomia.

Ali-indeksointi tai puuttuvat kriittiset indeksit

Päinvastainen virhe on olla indeksoimatta tärkeitä sarakkeita. erityisesti liittymisavaimet ja usein suodatetut kentät, mikä pakottaa MySQL:n kalliisiin taulukkoskannauksiin.

Analysoi säännöllisesti hitaiden kyselyiden lokitietoja ja yleisimpiä kyselyitä. ja luo kohdennetut indeksit sarakkeille, jotka esiintyvät toistuvasti WHERE- ja JOIN-lausekkeissa.

Väärä indeksityyppi tai väärä sarakejärjestys

Väärän indeksirakenteen käyttäminen voi hidastaa kyselyitä nopeuttamisen sijaan. kuten odottaa hajautusarvoa muistuttavaa käyttökuviota B-puulta tietyillä työkuormilla tai päinvastoin.

Yhdistelmäindeksien kanssa sarakejärjestyksen virhe on toinen yleinen ongelma, koska MySQL käyttää monissa kyselytyypeissä kokonaan vain indeksin vasemmanpuoleisinta osaa.

Vanhentuneet indeksitilastot ja ylläpidon puute

Indeksitilastot ohjaavat optimoijan päätöksiä, ja kun ne vanhenevat, MySQL saattaa valita huonoja kyselysuunnitelmia, jotka eivät heijasta nykyistä datajakaumaa.

ANALYZE TABLE -komennon ja tarvittaessa OPTIMIZE TABLE -komennon suorittaminen säännöllisesti auttaa pitämään tilastot ja fyysiset asettelut kunnossa, erityisesti nopeasti muuttuvissa tietojoukoissa.

Valvonnan ja EXPLAIN-lähdön huomiotta jättäminen

Indeksien suunnittelu ilman niiden todellisen käytön tarkistamista johtaa arvailuun. ja arvailu johtaa harvoin kestävään suorituskykyyn.

Käytä EXPLAIN-funktiota nähdäksesi, mitkä indeksit kyselyyn valitaan, kuinka monta riviä arvioidaan ja käytetäänkö alue- vai indeksipohjaisia ​​skannauksia. Muokkaa sitten skeemaa tai kyselyitäsi niissä tapauksissa, joissa suunnittelija ei selvästikään pysty hyödyntämään indeksejä tehokkaasti.

MySQL-indeksien hallintavinkkejä pitkän aikavälin suorituskykyyn

MySQL:n nopeana pitäminen ajan myötä on jatkuva mittaamisen, hienosäädön ja siivoamisen prosessi, varsinkin kun datasi kasvaa ja kyselymallit kehittyvät.

• Valitse indeksejä, jotka vastaavat tarkasti reaalimaailman kyselymalleja, keskittyen suodattamiseen, yhdistämiseen ja järjestämiseen käytettyihin sarakkeisiin.
• Harkitse yhdistelmäindeksejä, kun useita sarakkeita haetaan aina yhdessä, valitsemalla ensin valikoivimman tai useimmin suodatetun sarakkeen.
• Seuraa indeksien kokoja ja kirjoitustehoa, ja poista tai yhdistä päällekkäiset indeksit, kun ne eivät enää tarjoa selkeitä etuja.
• Analysoi kyselyitä säännöllisesti käyttämällä EXPLAIN- ja hitaita kyselylokeja, sekä SQL:n että indeksien virittäminen työmäärän muuttuessa.

Kun indeksointia lähestytään jatkuvana, datalähtöisenä säätöprosessina kertaluonteisen tehtävän sijaan, voit pitää MySQL:n käsittelemässä suurempia työkuormia, enemmän käyttäjiä ja monimutkaisempia kyselyitä pysähtymättä.