Indicii cluster 1 nu sunt un tip separat de index. Mai degrabă, este o abordare a stocării datelor. Detaliile în diferite implementări sunt diferite, dar în InnoDB indexul cluster conține de fapt indicele B-Tree și liniile însuși în aceeași structură.
În cazul în care partea de sus a tabelului construit un indice de cluster, foaia de index în paginile în sine sunt stocate rând. Termenul „cluster“ se referă la acea linie cu valori cheie similare stocate în cartier 2. Doar un singur indice de cluster poate fi construit peste masa, deoarece este imposibil să se păstreze aceeași linie în două locuri (dar care acoperă indicii pentru a imita mai multe indicii grupat, după cum vor fi discutate mai târziu în acest capitol).
Deoarece subsistemele sunt responsabile de implementarea indexurilor, nu toate susțin indexurile grupate. În prezent, numai solidDB și InnoDB se pot lauda cu acest lucru. În această secțiune, vom vorbi doar despre InnoDB, dar principiile discutate vor fi, cel puțin parțial, aplicabile oricărui subsistem de stocare care acceptă acum sau în viitor indicii clusterați.
În Fig. 3.3 arată cum sunt localizate înregistrările din indexul grupat. Rețineți că paginile cu foi conțin rândurile, iar paginile nodului conțin numai coloane indexate. În acest exemplu, coloana indexată conține valori întregi.
Unele SGB-uri vă permit să alegeți ce index să faceți cluster, dar în prezent nici unul dintre subsistemele de stocare MySQL nu are această capacitate. InnoDB a grupat datele de pe cheia primară. Aceasta înseamnă că "coloana indexată" din Fig. 3.3 este coloana care conține cheia primară.
Fig. 3.3. Aranjarea înregistrărilor într-un index cluster Dacă nu definiți o cheie primară, InnoDB va încerca să utilizeze un index unic în locul acestuia, ceea ce nu permite valori nulă. Dacă un astfel de index nu există, InnoDB va determina cheia primară ascunsă pentru dvs. și apoi va clustera tabelul de pe acesta. InnoDB a grupat înregistrările împreună numai în cadrul paginii. Paginile diferite cu valori cheie similare pot fi foarte diferite.
O cheie principală a clusterului poate uneori să crească performanța și, uneori, să o reducă în mod semnificativ. Astfel, decizia de a grupa ar trebui luată în mod deliberat, mai ales atunci când se înlocuiește tabela subsistemului de stocare cu InnoDB pe unele altele și invers.
Datele clasificate au câteva avantaje foarte importante:
• Puteți stoca date apropiate în apropiere. De exemplu, atunci când implementați o cutie poștală, puteți grupa tabelul cu ajutorul coloanei user_id, apoi va fi necesar doar un număr mic de pagini pentru a prelua toate mesajele de la același utilizator. Dacă nu utilizați gruparea, pentru fiecare mesaj poate fi necesară o operație separată I / O pe disc.
• Acces rapid la date. Indexul cluster stochează atât indexul, cât și datele împreună într-o singură structură B-Tree, astfel încât rândurile extrase din indexul cluster se întâlnesc de obicei mai repede decât căutările comparabile într-un index nonclustered.
• Utilizarea interogărilor care acoperă indici poate obține valoarea cheii primare din nodul frunzelor.
Aceste beneficii vor spori foarte mult performanța dacă vă proiectați mesele și interogările cu contul dvs. Cu toate acestea, indicii clustered au dezavantaje:
• Clusteringul oferă îmbunătățiri semnificative atunci când volumul de lucru este caracterizat printr-un număr mare de operații I / O. Dacă datele sunt stocate în memorie, atunci ordinea de acces la ele nu contează, iar indexurile grupate nu vor fi de mare folos.
• Viteza operațiunilor de inserție depinde în mare măsură de ordinea procesării datelor. Introducerea rândurilor în ordinea corespunzătoare cheii primare reprezintă cea mai rapidă metodă de încărcare a datelor în tabelul InnoDB. Dacă încărcați o cantitate mare de date într-o ordine diferită, atunci la sfârșitul boot-ului este logic să reorganizați tabelul folosind comanda OPTIMIZE TABLE.
• Actualizarea coloanelor unui index cluster este costisitoare, deoarece InnoDB trebuie să deplaseze fiecare rând actualizat într-o locație nouă.
• Pentru tabelele cu index indexat în grup, inserarea de noi rânduri sau actualizarea unei chei primare care necesită o întrerupere de linie poate duce la divizarea paginii. Acest lucru se întâmplă atunci când valoarea cheii rând este astfel încât șirul ar fi trebuit plasat într-o pagină plină cu date. Pentru ca o linie să se potrivească, subsistemul de stocare este forțat să împartă pagina în două. Din cauza divizării paginilor, masa ocupă mai mult spațiu pe disc.
• Scanarea completă a tabelelor de cluster poate fi mai lentă, mai ales dacă rândurile sunt mai puțin împachetate sau stocate în mod inconsecvent din cauza divizării paginii.
• Indicii secundari (nonclusteri) pot fi mai mari decât vă așteptați, deoarece nodurile frunzelor stochează valorile coloanelor care alcătuiesc cheia primară.
Aceasta înseamnă că, în procesul de căutare a unui șir pe indicele secundar, subsistemul de stocare trebuie să găsească mai întâi un nod de frunze în el și apoi să utilizeze valoarea cheii primare stocate acolo pentru a găsi șirul de pe el. Aceasta este o lucrare dublă: două treceri pe arborele B în loc de unul (în indicele hash-index adaptiv InnoDB ajută la reducerea acestor pierderi).
Comparația plasării datelor în InnoDB și MyISAM
Diferențele în organizarea plasării datelor în cluster și non-clustered, precum și diferența corespunzătoare între indicii primari și secundari, pot duce la confuzie și neașteptate. Luați în considerare modul în care InnoDB și MyISAM vor plasa datele în următorul tabel:
CREATE TABLE layout_test (coll int NU NULL, col2 int NU NULL,
Să presupunem că în tabel au fost adăugate 10.000 de rânduri. Valoarea cheii primare pentru fiecare linie inserată a fost selectată aleator din intervalul de la 1 la 10 000. Apoi, optimizarea a fost efectuată utilizând comanda OPTIMIZE TABLE. Cu alte cuvinte, datele sunt plasate pe disc într-un mod optim (defragmentat), dar liniile pot fi aranjate în ordine aleatorie. Elementele coloanei col2 sunt atribuite valori aleatorii între 1 și 100, deci există multe duplicate.
Plasarea datelor în MyISAM
Introducerea datelor în subsistemul MyISAM este mai ușoară, așa că vom începe cu aceasta. MyISAM stochează datele de pe disc în ordinea în care au fost inserate, așa cum se arată în Fig. 3.4.
Pe lângă liniile pe care le-am adus, începând de la zero. Deoarece siruri de caractere au o dimensiune fixă, MyISAM poate găsi oricare dintre ele prin deplasarea numărului necesar de octeți de la începutul mesei (MyISAM nu folosește întotdeauna o „numere de linie“, pe care le-am arătat, în funcție de faptul dacă liniile au o dimensiune fixă sau variabilă, acest subsistem stocarea utilizează strategii diferite).
Cu acest aranjament, construcția indicelui nu este dificilă. Am ilustra acest lucru cu o serie de diagrame, abandonând detalii fizice precum paginile și afișând doar "nodurile" din index. Fiecare nod de frunze din index poate pur și simplu să conțină numărul liniei. În Fig. 3.5 ilustrează cheia primară a tabelului.
Am omis unele detalii, de exemplu, că un nod intern al unui arbore B poate avea mai multe noduri descendente interne, dar
Fig. 3.4. Plasarea datelor pentru tabela layout_test în MyISAM
Fig. 3.5. Plasarea cheii primare pentru tabela layout_test în MyISAM
pentru o înțelegere comună a plasării datelor într-un subsistem de stocare fără cluster, acest lucru nu este esențial.
Cum rămâne cu indicele col2? Există ceva special aici? Se pare că nimic nu este același indice ca oricare altul. În Fig. 3.6 arată indicele col2.
Fig. 3.6. Indexarea plasării după coloana col2 pentru layout_test în MyISAM
De fapt, MyISAM nu are diferențe structurale între cheia primară și orice alt index. Cheia primară este pur și simplu un indice unic care nu permite valorile null numite PRIMARY.
Plasarea datelor în InnoDB
Subsistemul InnoDB stochează aceleași date în mod diferit din cauza organizației sale grupate. InnoDB generează tabelul așa cum se arată în Fig. 3.7.
Fig. 3.7. Plasarea cheii primare pentru tabelul layout_test în InnoDB
La prima vedere, diferențele speciale de la fig. 3.5 nu este prezent. Dar uita-te atent, si vei observa ca figura arata intregul tabel, nu doar indexul. Din moment ce indexul cluster în InnoDB este un tabel, nu există un depozit separat pentru rânduri, ca în MyISAM.
Fiecare nod frunză din cluster conține o valoare primară index cheie și indicatorul de tranzacție identificatorul retroactivitate care utilizează InnoDB pentru suport de tranzacții și mecanismul MVCC și alte coloane (în acest caz, col2). Dacă cheia primară este creată pe un prefix coloană, în InnoDB este stocată împreună cu restul, iar valoarea totală a acestei coloane.
Indicii secundari din InnoDB sunt foarte diferiți de cei din grupuri. Nodurile de frunze ale indexurilor secundare din acest sistem conțin, în loc de "pointers to strings", valorile cheii primare care acționează ca astfel de "pointeri". Această strategie reduce cantitatea de muncă necesară pentru a menține indicii secundari atunci când se deplasează un rând sau în momentul divizării unei pagini de date. Utilizarea valorilor cheii primare a șirului ca un indicator mărește dimensiunea indexului, însă înseamnă că InnoDB poate muta șirul fără a actualiza indicii la acesta.
Fig. 3.8 ilustrează indicele coloanei col2 pentru tabelul demonstrativ. Fiecare nod frunz conține coloane indexate (în acest caz numai col2), urmate de valorile cheii primare (coll).
Fig. 3.8. Plasarea indicelui secundar pentru tabelul layout_test în InnoDB
Aceste diagrame ilustrează nodurile frunză indicele B-copac, dar am omis în mod deliberat detaliile referitoare la nodurile non-frunze. Fiecare nod non-frunză a indicelui B-Tree în InnoDB conține coloane indexate, plus un pointer la nodul de nivel următor (care poate fi fie un non-frunză sau un nod frunză). Acest lucru se aplică tuturor indiciilor, atât cluster cât și secundare.
În Fig. 3.9 prezintă reprezentarea abstractă a organizării meselor în InnoDB și MylSAM. Este ușor să vedeți diferențele dintre modul în care sunt stocate datele și indexurile în aceste două sisteme.
Dacă nu înțelegeți diferența dintre spațiul de depozitare clustered și nonclustered și de ce este atât de important, nu vă descurajați. Acest lucru va deveni mai clar atunci când veți afla mai multe, mai ales la sfârșitul acestei secțiuni și în capitolul următor. Aceste concepte sunt foarte dificile și este nevoie de timp pentru a le înțelege pe deplin.
Introducerea rândurilor în ordinea tastei primare din InnoDB
Dacă utilizați InnoDB, și nu aveți nevoie de nici o grupare specifică, are sens pentru a defini o cheie surogat, care este cheia primară, a căror valoare nu are nici o legătură directă cu datele aplicației. De obicei, cel mai simplu mod este de a utiliza o coloană cu atributul AUTO_INCREMENT. Acest lucru asigură acest lucru
Fig. 3.9. Grupate și masa de bază non-grupată valoarea câmpului pentru care este construit cheia primară, este monoton crescătoare, care, la rândul său, asigură o performanță mai bună conexiune folosind cheia primară.
Este mai bine să evitați cheile de cluster accidentale (inconsistente). De exemplu, utilizarea valorilor UUID este o alegere proastă în termeni de performanță: face introducerea în indicele grupat este aleatoare, care este cel mai rău scenariu, și nu conduce la gruparea de date utile.
În scop demonstrativ, am efectuat teste de performanță pentru două situații. În primul caz, introducerea în tabelul userinfo cu un identificator întreg, definit după cum urmează:
CREATE TABLE userinfo (
id int nesemnate NOT NULL AUTO_INCREMENT,
nume varchar (64) NOT NULL DEFAULT '
mail varchar (64) NOT NULL DEFAULT '',
parola varchar (64) NOT NULL DEFAULT '',
dob data DEFAULT NULL,
adresa varchar (255) NOT NULL DEFAULT '',
orașul varchar (64) NOT NULL DEFAULT '',
state_id tinyint nesemnate NOT NULL DEFAULT '0',
zip varchar (8) NOT NULL DEFAULT '',
country_id smallint nesemnate NOT NULL DEFAULT '0',
sex ('M', 'F') NOT NULL DEFAULT 'M',
cont_type varchar (32) NOT NULL DEFAULT '',
tinyint verificat NOT NULL DEFAULT '0',
allow_mail tinyint nesemnate NOT NULL DEFAULT '0',
parrent_account int nesemnate NOT NULL DEFAULT '0',
aproape_airport varchar (3) NOT NULL DEFAULT '',
UNIQUE KEY email (e-mail),
KEY țară_id (country_id),
KEY state_id (state_id),
KEY state_id_2 (state_id, oraș, adresa)
Rețineți cheia primară autoincrement intreg.
Al doilea tabel, userinfo_uuid, masa de userinfo identice, cu excepția faptului că cheia primară este UUID, în loc de număr întreg:
Crearea tabelului userinfo_uuid (uuid varchar (36) NOT NULL,
Am testat ambele tabele. Mai întâi, am introdus în fiecare din câte un milion de rânduri pe un server care are suficientă memorie pentru a găzdui indicii din el. Apoi am introdus trei milioane de rânduri în aceleași mese, iar acest lucru a mărit indicele atât de mult încât a încetat să fie stocat în memorie. În tabel. 3.2 compară rezultatele testelor.
Notă: în cazul unei chei primare de tip UUID, nu numai inserarea rândurilor a durat mai mult, dar și dimensiunea indexului a crescut semnificativ. Unul dintre motive este dimensiunea mai mare a cheii primare, dar, fără îndoială, fragmentarea paginilor și fragmentarea rezultată au avut de asemenea un efect.
Tabelul 3.2. Rezultatele testelor de inserare a rândurilor în tabelele InnoDB
Pentru a înțelege de ce este așa, să vedem ce sa întâmplat în index atunci când am introdus datele în primul tabel. În Fig. 3.10 arată modul în care rândurile introduse mai întâi completează o pagină și apoi treceți la următoarea.
Fig. 3.10. Introducerea valorilor succesive ale indexului într-un index grupat După cum se poate vedea din Fig. 3.10, InnoDB salvează noua înregistrare imediat după cea precedentă, deoarece valorile cheii primare sunt consecutive. Atunci când factorul de umplere a paginii atinge valoarea maximă admisă (în InnoDB, raportul inițial de umplere este de 15/16 pentru a lăsa loc pentru modificări ulterioare), următoarea înregistrare este plasată pe o pagină nouă. După terminarea încărcării secvențiale a datelor, paginile au fost aproape umplute cu înregistrări ordonate, ceea ce este foarte de dorit.
Destul de diferite s-au întâmplat atunci când am introdus datele în al doilea tabel cu un indice cluster în coloana care conține UUID (Figura 3.11).
Deoarece valoarea cheii primare din fiecare rând următor nu este neapărat mai mare decât în rândul anterior, InnoDB nu poate plasa întotdeauna un rând nou la sfârșitul indexului. Trebuie să găsească o poziție potrivită pentru linie - undeva în medie undeva în mijlocul datelor existente - și să-și elibereze spațiul. Acest lucru cauzează o mulțime de activități suplimentare și duce la o plasare neoptimală a datelor. Iată un rezumat al neajunsurilor:
Fig. 3.11. Introducerea valorilor indexului inconsistent într-un index cu grupuri
• Page, care ar trebui să atingă linia, acesta poate fi scăzut pe disc și scos din cache, atunci InnoDB va trebui să-l găsească și să citească de pe disc înainte de a introduce un nou rând. Acest lucru duce la un număr mare de operații I / O aleatorii.
• InnoDB uneori trebuie să împartă paginile pentru a face loc unor noi rânduri. Aceasta necesită deplasarea unei cantități mari de date.
• Din cauza divizării paginii, ele par a fi umplute aleatoriu și slab, ceea ce duce deseori la fragmentare.
După încărcarea acestor valori aleatorii în indexul grupat, este logic să executați comanda OPTIMIZE TABLE, care va reconstrui masa și va umple paginile în mod optim.
Morala acestei povești este că dacă utilizați InnoDB trebuie să depună eforturi pentru introducerea de date într-un mod compatibil cu cheia primară, și încercați să utilizați o cheie de cluster care crește uniform pentru noi linii.
MySQL. Optimizarea performanței
Articole similare
Trimiteți-le prietenilor: