Optimizarea ratei de eșantionare a datelor

6.1 Introducere

Acum să trecem de la teorie la practică.

Trăim în lumea reală și toate produsele software sunt create în cele din urmă pentru oameni vii. Și acești oameni vii sunt foarte enervați de site-urile care se încarcă lent și de programele care încetinesc.

Și dacă o interogare la baza de date durează mai mult de o secundă, acest lucru este inacceptabil . Utilizatorii pur și simplu nu vor folosi un produs care are pagini/funcționalități atât de lente.

Dar adesea, pentru a afișa o pagină, trebuie să efectuați câteva zeci de interogări la baza de date. Și dacă sunt executate secvențial, atunci nu mai aveți o a doua limită, ci să zicem 100 ms per cerere.

Iată primele 5 moduri prin care programatorii accelerează interogările bazei de date:

1. Adăugarea de indici la tabelele din baza de date.
2. Rescrierea și optimizarea interogărilor.
3. Activați (și configurați) stocarea în cache pe partea bazei de date.
4. Activați stocarea în cache pe partea clientului.
5. Efectuarea denormalizării bazei de date.

Sunteți deja familiarizat cu toate aceste lucruri în cea mai mare parte, așa că următoarele vor fi doar sfaturi practice.

6.2 Indici

Nu este un secret pentru nimeni că lucrul cu o bază de date ocupă cea mai mare parte a muncii de aproape orice site. Și lucrează cu baza de date care este cel mai adesea blocajul aplicațiilor web.

În acest articol, aș dori să dau sfaturi practice despre utilizarea MySQL.

Voi spune imediat:

• acest articol este scris despre MySQL, deși lucrurile generale sunt probabil să fie adevărate pentru orice DBMS.
• tot ce este scris în articol este punctul meu de vedere personal și nu este adevărul suprem.
• sfatul nu se pretinde a fi nou și este rezultatul unei generalizări a literaturii citite și a experienței personale.
• în cadrul acestui articol, nu voi atinge problemele de configurare MySQL.

Problemele la utilizarea MySQL pot fi împărțite în următoarele trei grupuri (în ordinea importanței):

1. Neutilizarea sau utilizarea greșită a indicilor.
2. Structură greșită a bazei de date.
3. Interogări SQL incorecte \ suboptimale.

Să aruncăm o privire mai atentă la fiecare dintre aceste grupuri.

Utilizarea indicilor

Neutilizarea sau folosirea greșită a indecșilor este ceea ce încetinește cel mai adesea interogările. Pentru cei care nu sunt familiarizați cu mecanismul de funcționare a indicilor sau nu au citit încă despre acesta în manual, vă sfătuiesc cu insistență să îl citiți.

Sfaturi pentru utilizarea indexurilor:

• Nu trebuie să indexați totul . Destul de des, fără să înțeleagă sensul, oamenii pur și simplu indexează toate câmpurile unui tabel. Indexurile accelerează preluările, dar încetinesc inserările și actualizările rândurilor, astfel încât alegerea fiecărui index trebuie să fie semnificativă.
• Unul dintre principalii parametri care caracterizează indicele este selectivitatea, care este numărul de elemente diferite din indice. Nu are sens să indexezi un câmp care are două sau trei valori posibile. Un astfel de indice va fi puțin avantajos.
• Alegerea indecșilor ar trebui să înceapă cu o analiză a tuturor interogărilor pe un tabel dat. De foarte multe ori, după o astfel de analiză, în loc de trei sau patru indici, puteți face unul compus.
• Când folosiți indecși compuși, ordinea câmpurilor din index este critică.
• Nu uitați să acoperiți indici. Dacă toate datele dintr-o interogare pot fi preluate dintr-un index, atunci MySQL nu va accesa tabelul direct. Astfel de solicitări vor fi executate foarte repede. De exemplu, pentru o interogare SELECT name FROM user WHERE login='test'cu un index (login, nume), accesul la tabel nu este necesar. Uneori este logic să adăugați un câmp suplimentar la un index compus, ceea ce va face ca indexul să acopere și va accelera interogările.
• Pentru indici de rând, este adesea suficient să indexați doar o parte a rândului. Acest lucru poate reduce semnificativ dimensiunea indexului.
• Dacă %este la început, LIKE(SELECT * FROM table WHERE field LIKE '%test')indecșii nu vor fi folosiți.
• Indexul FULLTEXT este utilizat numai cu sintaxa MATCH ... AGAINST .

6.3 Structura bazei de date

O bază de date bine concepută este cheia pentru a lucra rapid și eficient cu baza de date. Pe de altă parte, o bază de date prost proiectată este întotdeauna o durere de cap pentru dezvoltatori.

Sfaturi pentru proiectarea bazei de date:

1. Utilizați cele mai mici tipuri de date posibile. Cu cât tipul de date este mai mare, cu atât tabelul este mai mare, cu atât sunt necesare mai multe accesări la disc pentru a obține datele. Utilizați o procedură foarte convenabilă: SELECT * FROM table_name PROCEDURE ANALYSE();pentru a determina tipurile minime de date posibile.
2. Respectați formele normale în timpul fazei de proiectare. Deseori programatorii recurg la denormalizare deja în această etapă. Cu toate acestea, în majoritatea cazurilor, la începutul proiectului, este departe de a fi evident cum poate rezulta acest lucru. Denormalizarea unui tabel este mult mai ușoară decât a suferi de una sub-optim denormalizată. Și JOINuneori funcționează mai repede decât tabelele denormalizate incorect.
3. Nu utilizați NULLcoloane decât dacă aveți nevoie în mod conștient de ele.

6.4 Interogări SQL.

La fel de des există dorința de a rescrie toate interogările în SQL nativ, astfel încât interogarea să fie cât mai rapidă posibil. Dacă decideți să faceți acest lucru, atunci iată câteva sfaturi:

1. Evitați solicitările în buclă. SQL este un limbaj al seturilor, iar interogările de scriere ar trebui abordate nu în limbajul funcțiilor, ci în limbajul seturilor.
2. Evitați *(asteriscurile) în interogări. Simțiți-vă liber să enumerați exact câmpurile pe care le alegeți. Acest lucru va reduce cantitatea de date preluate și trimise. De asemenea, nu uitați să acoperiți indici. Chiar dacă selectați toate câmpurile din tabel, este mai bine să le enumerați. În primul rând , îmbunătățește lizibilitatea codului. Când utilizați asteriscuri, este imposibil să aflați ce câmpuri sunt în tabel fără a le căuta. În al doilea rând , astăzi tabelul tău are cinci coloane INT , iar o lună mai târziu au fost adăugate încă un TEXT și BLOB , iar asteriscul a rămas așa cum era.
3. Când este paginat, pentru a obține numărul total de înregistrări, utilizați SQL_CALC_FOUND_ROWSși SELECT FOUND_ROWS();When used SQL_CALC_FOUND_ROWS MySQL, memorează în cache numărul selectat de rânduri (înainte de a se aplica LIMIT) și, când este utilizat, SELECT FOUND_ROWS()returnează numai această valoare stocată în cache fără a fi nevoie să reexecuți interogarea.
4. Nu uitați că INSERTexistă o sintaxă pentru mai multe inserții. O interogare va rula cu un ordin de mărime mai rapid decât mai multe interogări într-o buclă.
5. Utilizați LIMITacolo unde nu aveți nevoie de toate datele.
6. Utilizați INSERT… ON DUPLICATE KEY UPDATE…în locul și INSERTsau UPDATEdupă selecție și adesea în locul REPLACE.
7. Nu uitați de această caracteristică uimitoare GROUP_CONCAT. Poate ajuta la interogări complexe.