Kā izmantot indeksus, lai uzlabotu MySQL veiktspēju

Sākums » Pitons » Kā izmantot indeksus, lai uzlabotu MySQL veiktspēju

Ja jūsu MySQL vai MariaDB datubāze sāk darboties lēni, viedā indeksēšana parasti ir ātrākais veids, kā panākt ievērojamu veiktspējas pieaugumu. Labi izstrādāti indeksi var sāpīgi lēnus vaicājumus pārvērst ātrās atbildēs, īpaši, ja jūsu tabulās jau ir simtiem tūkstošu vai miljonu rindu.

No otras puses, slikta vai pārmērīga indeksēšana var nemanāmi samazināt veiktspēju, palielināt krātuvi un likt rakstīšanas darbībām pārmeklēt datus, tāpēc jums ir nepieciešams stabils mentāls modelis par to, kā darbojas indeksi, kādus tipus piedāvā MySQL, kā InnoDB izmanto atmiņu un no kādām tipiskām kļūdām jāizvairās. Tieši par to ir šī padziļinātā rokasgrāmata.

Kas ir datubāzes indekss MySQL?

Datu bāzes indekss ir datu struktūra, kas ļauj MySQL atrast rindas daudz ātrāk nekā skenējot visu tabulu, ļoti līdzīgi indeksam grāmatas beigās. Tā vietā, lai pārbaudītu katru rindu pa vienai, MySQL ievēro kompaktu, sakārtotu struktūru, kas norāda tieši uz atbilstošajiem ierakstiem.

Palaižot vaicājumu bez izmantojama indeksa, MySQL parasti ir jāveic pilna tabulas skenēšana, nolasot katru rindu, lai pārbaudītu, vai tā atbilst jūsu WHERE vai JOIN klauzulu nosacījumiem. Lielās tabulās tas kļūst ārkārtīgi lēns un darbināms ar lielu ievades/izvades apjomu.

MySQL un MariaDB lielākajai daļai darba slodžu galvenokārt izmanto līdzsvarota koka (B-Tree) indeksēšanu, kur atslēgas tiek glabātas hierarhiskā koka struktūrā. Šī struktūra uztur atslēgas sakārtotas un ļauj MySQL atrast vērtību logaritmiskā laikā, ejot cauri kokam, nevis lasot katru rindu no diska.

Lai to padarītu konkrētāku, iedomājieties klientu tabulu, kurā bieži meklējat pēc first_name = 'Ava', bet first_name nav indeksa. MySQL ir jāpārbauda katras rindas first_name, līdz tiek atrasti atbilstošie, kas nozīmē, ka izpildlaiks pieaug lineāri līdz ar tabulas izmēru.

Kad first_name ir pievienots B-koka indekss, MySQL izveido sakārtotu koku no visām first_name vērtībām kopā ar rindu rādītājiem, lai tas varētu pārlēkt pa koka līmeņiem, sekojot kreisajam vai labajam zaram, līdz tas sasniedz lapas mezglu, kas atsaucas uz visām rindām, kur first_name = 'Ava'. Tam vairs nav jāpārbauda katra tabulas rinda.

Galvenie indeksu veidi, ko izmantosiet MySQL

MySQL piedāvā vairākus datu indeksēšanas veidus, katrs no tiem ir optimizēts dažādiem vaicājumu modeļiem un datu formām, un šo opciju izpratne palīdz izvēlēties pareizo indeksu, nevis akli pievienot tos visur.

1. Vienlīmeņa (vienkāršie) indeksi

Vienas kolonnas indekss saista vienu atslēgas vērtību tieši ar vienu vai vairākām tabulas rindām, un tas ir visvienkāršākais un visizplatītākais indeksa stils, ar kuru strādāsiet ikdienā.

Iedomājieties primārās atslēgas kolonnu, piemēram, customer_id, klientu tabulā: tā unikāli identificē katru rindu, un zem pārsega MySQL uztur indeksu, kas katru customer_id saista ar atbilstošo rindas atrašanās vietu.

Šie vienkāršie indeksi lieliski darbojas mazām vai vidēji lielām tabulām un kolonnām ar zemu vai vidēju kardinalitāti, piemēram, statusa vai kategorijas karodziņiem, ja vien šīs kolonnas parādās filtros (WHERE) vai savienojumos.

Izmantojiet vienas kolonnas indeksu, ja vaicājumi parasti filtrē pēc viena lauka, piemēram, WHERE customer_id = 123 vai WHERE status = 'active', un jums nav nepieciešama apvienota filtrēšanas secība vairākās kolonnās.

2. Daudzkolonnu (daudzlīmeņu/salikti) indeksi

Salikts indekss apvieno vairākas kolonnas vienā sakārtotā struktūrā, ļaujot MySQL efektīvi atrisināt vaicājumus, kas filtrē vai kārto, izmantojot vairākus laukus kopā.

Piemēram, apsveriet indeksu, kas definēts (adrese, klienta_id) Klientu tabulā, kur adrese ir norādīta vispirms un klienta_id kā otrais. MySQL pēc tam var ātri atrast visus klientus, kas dzīvo noteiktā adresē, un, ja nepieciešams, efektīvi pārskatīt tos klienta_id secībā.

Šī hierarhiskā organizācija ievērojami samazina salīdzināšanu, salīdzinot ar visu rindu skenēšanu, kas ir īpaši svarīgi lielākām datu kopām, kurās bieži filtrējat pēc vairāk nekā vienas kolonnas.

Saliktie indeksi ir ārkārtīgi spēcīgi, taču secībai ir nozīme: indekss (adrese, klienta_id) var palīdzēt vaicājuma filtrēšanā tikai pēc adreses vai gan pēc adreses, gan klienta_id, taču tas netiks pilnībā izmantots, ja meklēsiet tikai pēc klienta_id.

3. Klasterizēti indeksi pakalpojumā InnoDB

InnoDB sistēmā klasterizētais indekss nosaka gan indeksa loģisko secību, gan rindu fizisko izkārtojumu diskā, kas nozīmē, ka tabulas dati tiek glabāti šī indeksa secībā.

Pēc būtības InnoDB izmanto PRIMARY KEY kā klasterizētu indeksu, tāpēc, izvēloties tabulas primāro atslēgu, jūs faktiski definējat, kā rindas tiek glabātas un kā sekundārie indeksi atsauksies uz šīm rindām.

Piemēram, ja primārā atslēga ir customer_id, InnoDB saglabā rindas, kas sakārtotas pēc customer_id, kas ir ideāli, ja daudzi vaicājumi piekļūst klientu diapazoniem pēc šī ID vai bieži meklē atsevišķus klientus, izmantojot to identifikatoru.

Šis izkārtojums uzlabo lasīšanas veiktspēju vaicājumiem, kas seko klasterizētajai indeksa secībai, taču tas arī nozīmē, ka vērtību, kas atrodas atslēgu diapazona vidū, ievietošana vai atjaunināšana var būt dārgāka, jo InnoDB ir jāuztur rindas fiziskā secībā.

Stabilas, pastāvīgi pieaugošas primārās atslēgas izvēle (piemēram, automātiski pieaugoša INT vai uz laiku balstīta surogātatslēga) bieži vien nodrošina labāku klasterizēta indeksa veiktspēju, savukārt nejaušas vai bieži mainīgas atslēgas var fragmentēt struktūru un palēnināt rakstīšanu.

4. Sekundārie (neklasterizētie) indeksi

Sekundārie indeksi ir visi pārējie indeksi InnoDB tabulā, izņemot klasterizēto primāro atslēgu, un tie nodrošina papildu ātras meklēšanas ceļus, nemainot pašas tabulas fizisko secību.

Piemēram, pievienojot indeksu e-pastam klientu tabulā, tiek izveidota atsevišķa struktūra, kas katru e-pastu saista ar atbilstošās rindas primāro atslēgu, lai MySQL vispirms varētu atrast primāro atslēgu, izmantojot sekundāro indeksu, un pēc tam izgūt visu rindu no klasterizētā indeksa.

Sekundārie indeksi ir elastīgi un ļauj paātrināt vaicājumus vairākās dažādās kolonnās, kas ir ļoti svarīgi, ja lasīšanas modeļi atšķiras un ne visi var sekot primārajai atslēgai.

Tomēr katrs sekundārais indekss ir jāatjaunina, veicot darbības INSERT, UPDATE un DELETE, tāpēc, ja pievienojat pārāk daudz no tiem, rakstīšanas veiktspēja un krātuves izmantošana ievērojami cietīs.

5. B-koks, jaucējkods, pilna teksta un telpiskie indeksi

Zem pārsega MySQL atbalsta vairākas indeksu struktūras papildus noklusējuma B-kokam, katra no tām ir paredzēta noteikta veida datiem un vaicājumu modeļiem.

• B-koka indeksi – noklusējuma iestatījums InnoDB un lielākajai daļai krātuves dzinēju, ideāli piemērots vienādības meklēšanai, diapazona skenēšanai, ORDER BY un GROUP BY operācijām.
• Haša indeksi — izmanto daži dzinēji vai kā iekšējās struktūras; lieliski piemērots tīrai vienlīdzības salīdzināšanai, bet ne diapazona vai secības vaicājumiem.
• FULLTEXT indeksi – optimizēta teksta satura, frāžu un vārdu meklēšanai TEXT vai VARCHAR kolonnās.
• TELPISKĀS INDEKSI – paredzēts ģeogrāfisko datu tipiem, nodrošinot efektīvus vaicājumus par punktiem, līnijām un daudzstūriem.

Izšķirošais aspekts ir tāds, ka neviens indeksa tips nav ideāls visam, tāpēc indeksa struktūra ir jāpielāgo datu raksturam un veidam, kā jūsu lietojumprogramma tos vaicā.

Indeksu izveide MySQL ar praktiskiem piemēriem

Indeksu izveide MySQL ir vienkārša SQL līmenī, taču ietekme uz veiktspēju var būt dramatiska, tāpēc ir vērts apskatīt dažus konkrētus piemērus un saprast, ko katrs no tiem dara.

Klientu tabulas parauga sagatavošana

Pieņemsim, ka jūs sākat ar vienkāršu klientu tabulu, kurā glabājat pamata kontaktinformāciju, piemēram, vesela skaitļa identifikatoru, vārdus, e-pastu, tālruņa numuru un adresi.

Jūs varētu izveidot tabulu šādi:

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)
);

Pēc tabulas definēšanas to aizpilda ar dažām parauga rindām, kas sniedz MySQL faktiskos datus optimizēšanai un ļauj pārbaudīt, kā indeksi maina vaicājumu plānus un izpildes laikus.

Vienkārša indeksa pievienošana

Pieņemsim, ka vēlaties vēl vairāk paātrināt meklēšanu pēc customer_id, iespējams, tāpēc, ka sākotnēji netika definēta primārā atslēga vai arī strādājat ar citu dzinēju vai mantotu shēmu.

Jūs varat izveidot pamata indeksu šādi:

CREATE INDEX idx_customer_id ON Customer(customer_id);

Kad šī komanda ir pabeigta, MySQL apstiprina indeksa izveidi, un vaicājumi ar WHERE customer_id = ? vai vienkārši savienojumi šajā kolonnā kļūst daudz ātrāki, jo tie var izmantot jauno struktūru pilnas skenēšanas vietā.

Salikta indeksa izveide

Ja vaicājumi filtrē pēc vairāk nekā vienas kolonnas vienlaikus, bieži vien ir lietderīgi izveidot saliktu indeksu, kas šīs vērtības glabā kopā noteiktā secībā.

Piemēram, lai paātrinātu meklēšanu gan pēc adreses, gan klienta ID, varat palaist:

CREATE INDEX idx_address_customer_id
ON Customer(address, customer_id);

Šis indekss ir īpaši efektīvs tādiem vaicājumiem kā WHERE adrese = ? AND klienta_id = ?, vai skenējumiem, kas grupē vai kārto pēc adreses un pēc tam pēc klienta_id, jo MySQL var paļauties uz esošo secību indeksā.

Neklasterizēts indekss e-pastā

E-pasta lauki ir klasiski kandidāti sekundārajiem indeksiem, jo ​​tie parasti ir unikāli vai ļoti selektīvi un bieži tiek izmantoti pieteikšanās vai konta meklēšanai.

E-pastam varat pievienot sekundāro indeksu, izmantojot:

CREATE INDEX idx_email ON Customer(email);

Pēc tam MySQL vairs nav jāpārbauda visa klientu tabula, lai atrisinātu WHERE e-pasts = ”, tā vienkārši pārlūko indeksu, atrod atbilstošo atslēgu un pēc tam nolasa saistīto rindu, izmantojot primārās atslēgas atsauci.

Aptveroši indeksi vēl ātrākai lasīšanai

Pārklājošais indekss ir tāds, kas ietver visas vaicājumam nepieciešamās kolonnas, lai MySQL varētu atbildēt uz pieprasījumu pilnībā no indeksa struktūras, nepieskaroties bāzes tabulai (saukta arī par tikai indeksa skenēšanu).

Iedomājieties, ka jūs bieži izpildāt vaicājumu, kuram nepieciešams tikai vārds un uzvārds:

CREATE INDEX idx_covering_name
ON Customer(first_name, last_name);

Vaicājumiem, kas atlasa tikai šos divus laukus un filtrē atbilstoši, MySQL var lasīt tieši no idx_covering_name, samazinot diska ievades/izvades apjomu un uzlabojot latentumu, īpaši lielos datu kopumos.

Indeksa optimizācijas stratēģijas un labākā prakse

Indeksu pievienošana katrai kolonnai garantēti kaitē veiktspējai, nevis tai palīdz, tāpēc ir nepieciešami skaidri principi indeksu izstrādei, uzraudzībai un samazināšanai laika gaitā.

1. Izvēlieties pareizās kolonnas indeksēšanai

Piešķiriet prioritāti kolonnām, kas bieži parādās WHERE, JOIN, ORDER BY vai GROUP BY klauzulās, jo tieši tās visvairāk iegūst no ātras meklēšanas vai kārtotas piekļuves.

Kolonnas, kurās ir galvenokārt unikālas vai ļoti selektīvas vērtības, ir īpaši labi kandidāti indeksam, jo ​​MySQL var ātri sašaurināt līdz nelielam rindu skaitam, bieži vien tikai vienai.

No otras puses, ļoti zemas kardinalitātes kolonnu (piemēram, Būla karodziņu) indeksēšana var daudz nepalīdzēt, jo dzinējam joprojām ir jāpārbauda daudzas rindas katrai indeksa atslēgai, kas ierobežo ieguvumu.

2. Saglabājiet indeksus pēc iespējas īsākus un šaurākus

Lielas indeksa atslēgas patērē vairāk atmiņas un diska vietas un palēnina katru rakstīšanas darbību, tāpēc parasti indeksētās kolonnas ir vēlams veidot pēc iespējas kompaktākas.

Gariem teksta vai mainīga garuma laukiem apsveriet iespēju indeksēt tikai prefiksu, ja ar to pietiek vērtību atšķiršanai, piemēram, indeksējot pirmās 20 rakstzīmes 200 rakstzīmju laukā, ja ar to pietiek jūsu vaicājumiem.

Skaitlisku tipu izmantošana lielu teksta lauku vietā savienojumiem un filtriem arī uzlabo indeksēšanas efektivitāti, jo veselus skaitļus un fiksēta izmēra vērtības ir lētāk salīdzināt un uzglabāt.

3. Izvairieties no pārmērīgas indeksēšanas

Katrs papildu indekss ir kaut kas tāds, kas MySQL ir jāatjaunina, veicot INSERT, UPDATE un DELETE darbības, kas nozīmē, ka neapdomīga indeksa izveide var ievērojami palēnināt lielas rakstīšanas slodzes.

Pārāk liela indeksēšana arī izšķiež krātuvi un var mulsināt optimizētāju, ja pastāv vairāki līdzīgi indeksi, apgrūtinot MySQL patiesi optimālā plāna izvēli.

Labs īkšķa noteikums ir veidot indeksu tikai tad, ja varat identificēt konkrētus vaicājumus, kas no tā gūs labumu, un pēc tam ar EXPLAIN pārbaudīt, vai šie vaicājumi patiešām izmanto jauno indeksu.

4. Noņemiet liekos un neizmantotos indeksus

Pārsteidzoši bieži var atrast ražošanas shēmas ar daudziem liekiem indeksiem, kurus neviens neatceras izveidojis, it īpaši pēc vairākām izstrādes iterācijām.

Jums periodiski jāpārskata, kuri indeksi vaicājumos tiek reti vai nekad izmantoti, izmantojot MySQL veiktspējas shēmu vai ārējos uzraudzības rīkus, lai apkopotu lietošanas statistiku.

Kad esat identificējis patiesi nevajadzīgu indeksu, tā droša izmešana var uzlabot rakstīšanas veiktspēju un atbrīvot brīvo krātuvi:

DROP INDEX idx_unnecessary_index ON Customer;

Vienmēr vispirms pārbaudiet indeksa noņemšanas ietekmi izmēģinājuma vai testa vidē, īpaši mantotajās sistēmās, kurās slēptie vaicājumi var uz to paļauties.

5. Pirms indeksēšanas analizējiet vaicājumu modeļus

Indeksu pievienošana akli, neizprotot vaicājumu darbību, ir viens no lielākajiem trūkumiem, kas bieži noved pie lēnas rakstīšanas un reālas lasīšanas veiktspējas uzlabošanas.

Sāciet ar lēnu vaicājumu un bieži izpildītu vaicājumu uztveršanu un pēc tam rūpīgi tos pārbaudiet, iepazīstoties ar pilnīgo MySQL vaicājumu optimizācijas rokasgrāmatu , pievēršot īpašu uzmanību WHERE nosacījumiem, JOIN kolonnām un kārtošanas vai grupēšanas klauzulām.

Izmantojiet funkciju EXPLAIN, lai redzētu, kā MySQL plāno izpildīt katru vaicājumu, un pārbaudītu, vai tas izmanto indeksus vai atgriežas pie pilnas tabulas skenēšanas, un attiecīgi precizētu indeksēšanas stratēģiju.

InnoDB bufera pūls un indeksa veiktspējas atmiņas puse

Indeksi neatrodas tikai diskā: InnoDB lielā mērā paļaujas uz RAM, izmantojot bufera pūlu, lai kešatmiņā saglabātu gan datus, gan indeksu lapas, un šim atmiņas slānim ir milzīga ietekme uz reālo veiktspēju.

InnoDB bufera pūls ir liels atmiņas reģions, kurā MySQL kešatmiņā saglabā tabulas datus, indeksa lapas, modificētas rindas, kas gaida iztīrīšanu, un dažas iekšējās struktūras, piemēram, adaptīvo jaucējindeksu; skatiet mūsu krātuves sistēmu pārskatu , lai iepazītos ar saistītiem apsvērumiem.

Pārvaldītos pakalpojumos, piemēram, Cloud SQL, noklusējuma bufera kopas lielums parasti ir aptuveni 70–75% no instances atmiņas, taču to var un bieži vien vajag pielāgot atkarībā no darba slodzes un pieejamās RAM.

Mērķis ir izveidot pietiekami lielu bufera pūlu, lai visbiežāk piekļūtie dati un indeksu lapas paliktu atmiņā, vienlaikus atstājot vietu savienojuma buferiem, performance_schema tabulām un citām MySQL papildu vajadzībām.

Varat uzraudzīt, cik lasījumu tiek apkalpoti no diska, salīdzinot ar bufera pūlu, un, ja redzat daudz diska lasījumu attiecībā pret bufera trāpījumiem, innodb_buffer_pool_size (un instances atmiņas) palielināšana var ievērojami paātrināt lasīšanas vaicājumus, kas balstās uz indeksiem.

Shēma, vaicājumi un skripti: plašāks MySQL veiktspējas attēls

Indeksa regulēšana nepastāv vakuumā; shēmas dizains, vaicājumu struktūra un lietojumprogrammas kods ietekmē MySQL veiktspēju, tāpēc ir vērts pievērsties apkārtējai labākajai praksei.

1. Izstrādājiet saprātīgu relāciju modeli

Laika veltīšana tīras relāciju shēmas izstrādei ar atbilstošām tabulām, laukiem un relācijām ievērojami atmaksājas, nodrošinot vienkāršāku apkopi un paredzamāku veiktspēju.

Identificējiet entītijas, to īpašības un to, kā tās ir saistītas viena ar otru, pēc tam pārveidojiet to tabulās, primārajās atslēgās, ārējās atslēgās un atbalsta indeksos, tiecoties panākt normalizācijas līmeni, kas novērš redundanci, bet neiekrīt pārmērīgi normalizētā stāvoklī.

Primārās atslēgas unikāli identificē katru rindu, savukārt ārējās atslēgas izsaka relācijas starp tabulām, un abām parasti ir nepieciešami indeksi, jo tās pastāvīgi parādās savienojumos un ierobežojumos.

2. Gudri izvēlieties lauku veidus

Lauku tipiem ir tieša ietekme uz veiktspēju, īpaši, ja tie ir daļa no indeksa, tāpēc ir vērts tos apzināti izmantot, nevis pēc noklusējuma izvēlēties vispārīgus tipus.

Viena veida informācijai visās tabulās izmantojiet konsekventus datu tipus, jo tas vienkāršo apvienošanu un ļauj MySQL efektīvāk veikt salīdzināšanas.

Dodiet priekšroku fiksēta garuma tipiem (piemēram, CHAR vai fiksēta izmēra skaitliskiem tipiem), ja tas ir piemēroti, nevis ļoti lieliem mainīga garuma laukiem, un izvairieties no TEXT vai BLOB izmantošanas vērtībām, kuras regulāri filtrējat vai apvienojat.

Kad vien iespējams, deklarējiet kolonnas kā NOT NULL, jo darbība ar nullējamiem laukiem var palēnināt dažas darbības un sarežģīt indeksa izmantošanu.

3. Turiet galdus kompaktus un kārtīgus

Liesas tabulas ir ātrākas, jo atmiņā un diskā pārvietojams mazāk datu, īpaši, ja sarežģīti vaicājumi skar vairākas rindas vai savienojumus.

Ja izmantojat ROW_FORMAT opcijas, izvēlieties fiksēta izmēra rindas, ja tām ir jēga, jo tās var padarīt secīgu lasīšanu efektīvāku salīdzinājumā ar ļoti mainīgiem rindu formātiem.

Periodiski pārskatiet, vai vecos ierakstus var arhivēt vai dzēst, saglabājot pēc iespējas mazākus karstā darba ierakstus, lai uzlabotu kešatmiņas efektivitāti un indeksēšanas veiktspēju.

Pēc apjomīgām dzēšanas reizēm vai daudzām strukturālām izmaiņām OPTIMIZE TABLE palaišana var palīdzēt reorganizēt krātuvi un uzlabot I/O modeļus, lai gan ar lielām ražošanas tabulām jābūt uzmanīgiem bloķēšanas un dīkstāves seku dēļ.

4. Rakstiet vaicājumus, ņemot vērā veiktspēju

Pat ar lieliskiem indeksiem slikti uzrakstīts SQL var sabotēt veiktspēju, tāpēc vaicājumu izstrādei jāiet roku rokā ar indeksu izstrādi.

Izvairieties no SELECT * ražošanas vaicājumos un skaidri norādiet tikai nepieciešamās kolonnas, samazinot pārsūtīto datu apjomu un indeksu vai datu lapu skaitu, kas MySQL ir jānolasa.

Esiet uzmanīgi ar LIKE modeļiem, kas sākas ar aizstājējzīmi (piemēram, '%term'), jo tie parasti neļauj izmantot parastos B-koka indeksus; apjomīgai teksta meklēšanai parasti labāka izvēle ir FULLTEXT indeksi.

Izmantojiet GROUP BY, ORDER BY un HAVING tikai tad, ja tas ir patiešām nepieciešams, un pārliecinieties, vai iesaistītās kolonnas ir pareizi indeksētas vai, kad vien iespējams, atbilst saliktā indeksa vadošajām kolonnām.

Izmantojiet EXPLAIN, lai pārbaudītu, kā MySQL plāno palaist vaicājumu, meklējot tādas pazīmes kā “type = ALL” vai “rows” (rindas) ar ļoti augstu vērtību, kas parasti norāda uz pilnīgu skenēšanu un sliktu indeksa izmantošanu.

5. Optimizējiet ieliktņus un darbību skriptu līmenī

Papildus neapstrādātam SQL, tas, kā jūsu lietojumprogrammu skripti izveido savienojumu ar MySQL un mijiedarbojas ar to, ir ļoti svarīgi kopējai atsaucībai, īpaši mērogā.

Partiju ievietošana parasti ir efektīvāka nekā daudzas vienas rindas ievietošanas, piemēram, izmantojot INSERT INTO table (col1, col2) VALUES (…), (…), (…); lai MySQL varētu apstrādāt vairākas rindas vienā piegājienā un ievērot MySQL transakciju pamatprincipus pareizai partiju apstrādei.

Lapojiet vaicājuma rezultātus, izmantojot LIMIT, nevis ielādējot atmiņā milzīgus rezultātu kopumus vai vienlaikus nosūtot tos visus klientam, kas samazina ielādes laiku un resursu patēriņu.

Ieviesiet kešatmiņas slāņus (kešatmiņas iekšienē, lietojumprogrammu līmeņa kešatmiņas, sesiju krātuvi) datiem, kas mainās reti, bet tiek bieži lasīti, atbrīvojot MySQL no atkārtotas lasīšanas un padarot sistēmu noturīgāku maksimālās slodzes apstākļos.

Samaziniet nevajadzīgo datubāzes savienojumu skaitu un saglabājiet saprātīgu savienojumu darbības laiku, bieži vien izmantojot savienojumu apvienošanu un veicot apjomīgu apstrādi pēc datu iegūšanas, nevis kamēr savienojums ir atvērts.

Biežāk pieļautās indeksēšanas kļūdas un kā no tām izvairīties

Indeksu nepareiza izmantošana var nemanāmi radīt milzīgas veiktspējas problēmas, tāpēc ir noderīgi zināt parastās kļūmes un to, kā tās apiet.

Pārāk liela indeksēšana

Indeksu pievienošana katrai kolonnai “katram gadījumam” ir klasiska iesācēja kļūda, jo katrs papildu indekss palēnina visas rakstīšanas darbības un patērē papildu krātuvi.

Jums vajadzētu saīsināt indeksu sarakstu atbilstoši jūsu faktiskajām vaicājumu vajadzībām, izmantojot vaicājumu žurnālus un veiktspējas rīkus, lai saprastu, kuri indeksi veic reālu darbu un kuri ir lieki.

Nepietiekama indeksēšana vai trūkstoši kritiski indeksi

Pretēja kļūda ir svarīgu kolonnu, īpaši pievienošanās atslēgu un bieži filtrētu lauku, neindeksēšana , kas piespiež MySQL veikt dārgas tabulu skenēšanas.

Regulāri analizējiet lēno vaicājumu žurnālu un biežāk sastopamos vaicājumus, kā arī izveidojiet mērķtiecīgus indeksus kolonnām, kas atkārtoti parādās WHERE un JOIN klauzulās.

Nepareizs indeksa tips vai nepareiza kolonnu secība

Neatbilstošas ​​indeksu struktūras izmantošana var palēnināt vaicājumus, nevis paātrināt tos, piemēram, noteiktām darba slodzēm no B-koka var sagaidīt jaucējkodam līdzīgu piekļuves modeli vai otrādi.

Izmantojot saliktos indeksus, kolonnu secības nepareiza norādīšana ir vēl viena bieži sastopama problēma, jo MySQL daudziem vaicājumu veidiem pilnībā izmanto tikai indeksa kreiso daļu.

Novecojusi indeksa statistika un nepietiekama apkope

Optimizētāja lēmumus vada indeksa statistika, un, kad tā noveco, MySQL var izvēlēties sliktus vaicājumu plānus, kas neatspoguļo pašreizējo datu sadalījumu.

Regulāra ANALYZE TABLE un, ja nepieciešams, OPTIMIZE TABLE palaišana palīdz uzturēt statistiku un fizisko izkārtojumu labā stāvoklī, īpaši strauji mainīgās datu kopās.

Ignorējot uzraudzību un EXPLAIN izvadi

Indeksu izstrāde, nepārbaudot, kā tie faktiski tiek izmantoti, ir saistīta ar minējumiem, un minējumi reti nodrošina ilgtspējīgu veiktspēju.

Izmantojiet funkciju EXPLAIN, lai redzētu, kuri indeksi tiek izvēlēti vaicājumam, cik rindu tiek aprēķinātas un vai tiek izmantota diapazona vai tikai indeksu skenēšana, pēc tam pielāgojiet shēmu vai vaicājumus, ja plānotājs acīmredzami nevar efektīvi izmantot indeksus.

MySQL indeksa pārvaldības padomi ilgtermiņa veiktspējai

MySQL ātruma uzturēšana laika gaitā ir nepārtraukts mērīšanas, pielāgošanas un sakopšanas process, īpaši, pieaugot datiem un attīstoties vaicājumu modeļiem.

• Atlasiet indeksus, kas cieši atbilst reālās pasaules vaicājumu modeļiem, koncentrējoties uz kolonnām, ko izmanto filtrēšanai, apvienošanai un sakārtošanai.
• Apsveriet saliktos indeksus, ja vaicājumi vienmēr tiek veikti kopā ar vairākām kolonnām. vispirms izvēloties selektīvāko vai visbiežāk filtrēto kolonnu.
• Uzraudzīt indeksu izmērus un rakstīšanas veiktspēju, un noņemt vai apvienot pārklājošos indeksus, ja tie vairs nesniedz skaidras priekšrocības.
• Regulāri analizējiet vaicājumus, izmantojot EXPLAIN un lēno vaicājumu žurnālus, gan SQL, gan indeksu pielāgošana atbilstoši darba slodzes izmaiņām.

Ja indeksēšanu uztverat kā nepārtrauktu, uz datiem balstītu regulēšanas procesu, nevis vienreizēju uzdevumu, MySQL var apstrādāt lielākas darba slodzes, vairāk lietotāju un sarežģītākus vaicājumus, neapstājoties.