Cum să obțineți date mai rapide din MySQL


 

Cum să obțineți date mai rapide din MySQL

 

O interogare ineficientă cu erori poate afecta baza de date de producție prin risipirea resurselor sale.

Poate încetini performanța bazei de date și ar cauza pierderea serviciilor și altor utilizatori. Trebuie să aveți grijă de întrebările dvs. individuale în timp ce vă optimizați serverul de baze de date. Acest lucru este chiar mai important decât celelalte aspecte ale instalării serverului de baze de date, cum ar fi configurația hardware sau software.

 Indiferent de puterea hardware-ului pe care rulează baza de date, o interogare necorespunzătoare ar afecta negativ funcționarea aplicațiilor bazei de date și ar afecta afacerea dvs.   

Prin urmare, optimizarea interogărilor este cheia succesului pentru realizarea unei baze de date de producție eficiente și rapide.

Motivul de bază din spatele performanței insuficiente a unei interogări este lucrul cu prea multe date. Lucrul cu date suplimentare și inutile duce la irosirea resurselor CPU, consumă mai multă memorie, adaugă cheltuieli de rețea și forțează serverul SQL să funcționeze mai mult, ceea ce duce la un timp de răspuns lent al serverului. Acest lucru rezultă ca o piedică în procesarea paginilor web dinamice. 

Deci, trebuie să aflați două lucruri importante pentru a analiza și a remedia această situație.

În primul rând, aplicația dvs. accesează mai multe date decât este necesar? Aceste date suplimentare ar putea fi sub forma unui număr mai mare de rânduri sau coloane.

În al doilea rând, dacă serverul MySQL analizează mai multe rânduri decât are nevoie.

EXPLAIN și INDEX sunt două metode de interogare a optimizării care permit MySQL să afle rândurile cu un tip de acces mai eficient care analizează mai puține date. 

Acum să vedem câteva dintre cele mai eficiente metode de normalizare și optimizare a interogărilor dvs. în baza de date:

Indici MySQL

Indexarea este o caracteristică excelentă care permite serverului dvs. SQL să reducă scanarea căutării tabelelor pentru a prelua un rând și a reduce timpul de execuție a interogării selectate. Îți face baza de date lentă mai rapidă și funcționează mai bine.

Oferă următoarea optimizare pentru a selecta interogări:

Garantează preluarea înregistrărilor identificabile unice

Ajută la sortarea înregistrărilor

Căutarea este mai rapidă

Un index poate fi creat pe o coloană în următoarele situații:

Când o coloană este interogată frecvent

Pentru coloanele cu chei străine care trebuie să facă referire la alte tabele

Dacă pe coloană există o cheie unică. 

MySQL poate ocupa mai mult spațiu și poate, de asemenea, să scadă performanța inserării, ștergerii și actualizării interogărilor, dar crește foarte mult performanța, mai ales atunci când interogați o bază de date mare. 

Exemplu:

mysql> SELECT client_id, client_address

Dacă rulați deasupra interogării selectate pe un tabel cu 1000 de rânduri, atunci interogarea dvs. SQL va forța serverul să scaneze tabelul complet cu 1000 de rânduri pentru a prelua o singură înregistrare pe care o căutați.

Dar dacă aplicați indexul alături de declarația select, atunci serverul va localiza acea înregistrare vizată doar cu o căutare pe un rând.

mysql> CREATE INDEX customer_id ON customr_address

Aici serverul va scana un singur rând.

Puteți folosi întotdeauna instrucțiunea „EXPLAIN” pentru a analiza performanța și funcționarea interogărilor dvs. pentru a selecta, insera, șterge, actualiza și înlocui instrucțiunile.

În primul rând, optimizați schema bazei de date pentru a realiza interogări de mare viteză MySQL

Nu puteți realiza interogări de mare viteză MySQL fără o bază de date bine structurată.

Dimensiunea crescândă a datelor dvs. ar împiedica performanța bazei de date, indiferent de interogările SQL optimizate.

Există o serie de metode pe care le puteți aplica pentru a vă optimiza baza de date:

Lucrați cu JOINS

Încercați să includeți un număr mai mic de tabele în instrucțiunile dvs. JOIN, deoarece instrucțiunile SQL slab structurate care au multe JOINS nu ar funcționa eficient. Cel mai bine este să lipiți până la douăsprezece JOINS pentru fiecare interogare. 

Evitați prea multe coloane

Un tabel larg cu prea multe coloane ar încetini performanța unei interogări.

Mai degrabă este o practică bună să împărțiți un tabel mare în mai multe structuri logice și să utilizați identificatorul unic pentru a vă referi la tabelul principal.

Această practică va reduce timpul de procesare a procesorului pentru interogările dvs.

Evitați valorile NULL

NULL arată absența oricărei valori într-o coloană. Această valoare poate dăuna rezultatelor bazei de date și, prin urmare, ar trebui evitată pe cât posibil. NULL în coloana dvs. poate deteriora rezultatul așteptat dacă nu utilizați instrucțiunea MySQL 'ifnull' pentru a returna o valoare alternativă dacă o înregistrare are un nul.

Folosiți tipuri de date optime

MySQL folosește diferite tipuri de date, cum ar fi numărul întreg, float, text, data, data_time, varchar și altele, în funcție de necesitate.

Încercați să utilizați tipurile de date mai scurte în timp ce vă proiectați tabelele cât mai mult posibil, deoarece tipurile de date mai scurte sunt mai eficiente decât tipurile de date lungi.

Exemplu:

Utilizarea valorii întregi ar fi mai utilă pentru câmpuri precum student_id sau client_no, deoarece ar fi necesare mai puține calcule de MySQL decât tipul de date text, cum ar fi Varchar.

De asemenea, tipul de date date_time ar fi optim de utilizat pentru câmpuri precum order_date, deoarece nu ar necesita MySQL să ruleze funcții complicate și care consumă mult timp pentru a converti acest câmp la data în timp ce extrageți înregistrările.

Tabelele de normalizare

Utilizați întotdeauna același tip de date pentru a stoca aceleași valori de date în tabele diferite. De exemplu, puteți utiliza tipul de date INT pentru stocarea client_id atât pentru tabela client, cât și pentru tabela de comenzi.   

Optimizare SQL

Fiți mai specific în timp ce definiți câmpurile SELECT

Utilizând operatori de simboluri cum ar fi>, <, =,! =, O interogare poate fi optimizată.

Exemplu

Interogare neoptimizată

Selectați * din Table_Customer

Selectați * din Table_Customer WHERE OrderValue> 1000

Interogare optimizată

SELECT nume_coloana1, nume_coloana2, nume_coloana3 din Table_Customer WHERE OrderValue> = 1000

Aici, în prima interogare, toate înregistrările sunt preluate din baza de date și aceasta va încetini baza de date și chiar poate ucide baza de date dacă baza de date are miliarde de înregistrări.

În a doua interogare, numai acele înregistrări sunt preluate în cazul în care OrderValue este mai mare de 1000.

A treia interogare este mai optimizată, deoarece va prelua valori numai din coloanele necesare.

Pentru a obține o performanță mai bună, limitați întotdeauna rezultatele folosind clauza WHERE.  

Utilizați metacaractere (%) la sfârșitul textului

Comerciile sunt folosite pentru a obține date sau informații din textul clar (FULLTEXT) și pot fi utilizate în trei moduri, la începutul, sfârșitul sau mijlocul textului.

Dacă păstrați caracterul comod în partea din față a textului pe care doriți să îl căutați, baza de date ar trebui să efectueze o căutare mai largă pentru a găsi potrivirile.

Deci, este întotdeauna cel mai bine să păstrați caracterul wildcard la sfârșitul textului vizat pentru a minimiza timpul de căutare.

Folosiți metacaracterul fie ca prefix, fie ca postfix, dar nu folosiți niciodată ambele simultan la text.

Exemplu: 

SELECTAȚI * DIN StudentName UNDE COLOANA CA „% texttofind”; wildcard ca prefix

SELECTEAZĂ * DIN StudentName UNDE COLOANA CA 'texttofind%'; wildcard ca postfix

SELECTAȚI * DIN StudentName UNDE COLOANA CA „% texttofind%”; wildcard ca ambele

Utilizarea LIMIT

Într-o serie de situații în care doriți doar să obțineți primele câteva rezultate cu interogările dvs., cum ar fi cele mai recente căutări care ar necesita doar până la zece sau cincisprezece rânduri de date.

Aici poate fi utilizată clauza LIMIT care ar pune o limită asupra numărului de rezultate care trebuie returnate în locul tuturor rezultatelor posibile.

Ați avea nevoie de această clauză în timpul testării în care ați limita eșantionul de interogări, deoarece nu doriți să utilizați prea multe resurse de server.

Exemplu:

SELECTAȚI ID-ul postului din TableName ORDER BY date DESC LIMIT 15

LIMIT poate fi folosit și pentru a obține o gamă de valori.

Exemplu:

SELECTAȚI ID-ul postului din TableName ORDER BY date DESC LIMIT 15, 10

SELECTAȚI ID-ul postului din TableName ORDER BY date DESC LIMIT 10 OFFSET 15

În interogarea de mai sus, SELECT va căuta următoarele zece PostsID din înregistrarea a cincisprezecea în continuare.

Folosiți UNION în loc de SAU

Utilizarea UNION poate accelera performanța. Atât UNION cât și OR oferă același rezultat, dar uniunea poate combina setul de rezultate de mai multe interogări și se execută mai repede.

Exemplu:

SELECT * FROM table1, table2 WHERE table1.column1 = table2.column2

UNIUNE

SELECT * FROM table1, table2 WHERE table1.column3 = table2.column4

Acest lucru ar fi mai rapid și ar combina setul de rezultate al ambelor interogări.

În timp ce interogarea de mai jos ar fi mai lentă folosind SAU

Exemplu:

SELECT * FROM table1, table2 WHERE table1.column1 = table2.column2 SAU table1.column3 = table2.column4

Utilizați clauza WHERE, GROUP BY sau ORDER BY pe coloanele indexate.

Folosiți mai des operatori pozitivi

Operatorii pozitivi încetează căutarea în baza de date odată ce rezultatul este găsit, în timp ce operatorii negativi precum NOT, NOT EXIST, NOT IN,! =, Caută în toate rândurile, apoi dau rezultatele. Deci, utilizați operatori negativi numai atunci când logica de afaceri a cerut aplicarea acestora pentru a obține rezultatele.

Cel mai bine este să evitați utilizarea OR în clauza WHERE pentru a vă alătura condițiilor

Utilizarea OR poate invalida indexul dvs. și, prin urmare, poate necesita o scanare completă a tabelului.

Folosiți căutările în text complet (FTS) 

FTS sunt mult mai rapide decât caracterele wildcard. Cu această metodă, doar un rând necesar este preluat în locul tuturor rândurilor din baza de date. Acest lucru duce la o procesare mai rapidă a interogărilor de către MySQL.

Optimizați declarația de ștergere sau modificare dacă datele sunt prea mari

Dacă ștergeți sau modificați date uriașe la un moment dat, aceasta va duce la o povară mare asupra utilizării procesorului. Va exista eroare de depășire a așteptării blocării. Acest lucru va împiedica capacitatea altora de a accesa baza de date. 

 

Funcția de cache MySQL

Dacă aplicațiile și site-urile dvs. web trebuie să efectueze o mulțime de interogări selectate, atunci funcția de cache este foarte utilă pentru a accelera performanța în timpul operațiilor de citire.

Aici funcția de încasare este utilizată cu interogarea selectată alături de setul de date rezultat.

Dar dacă aplicația dvs. actualizează frecvent tabelul, atunci acest lucru va invalida orice interogare în cache și set de date.

A rezuma...

Asigurați-vă că interogările dvs. recuperează numai acele date de care aveți nevoie. Este cel mai important timp de răspuns, optimizând interogările dvs. Optimizați nu numai interogările, ci evaluați fiecare subtask din cadrul interogării. Aceste sarcini secundare pot dura mult timp pentru a fi procesate. 

Faceți ca fiecare sarcină secundară să funcționeze frecvent, eliminați-o ori de câte ori este posibil sau păstrați un număr mic de sarcini secundare pentru fiecare interogare.

Toate metodele și tehnicile de mai sus vă pot ajuta să analizați și să vă optimizați interogările MySQL și să obțineți o performanță mai bună pentru aplicațiile dvs.

 




Hiring Developers in Romania

Hiring Developers in Romania

Hiring Developers in Romania   How Much do Programmers in Romania Cost? Senior programmers make €1,700 to €2,200 a month (five times the average salary in Romania), while junior progra...
Salarii pentru dezvoltatori în România

Salarii pentru dezvoltatori în România

  Salarii programator în București   Programatorii seniori câștigă între 1.700 și 2.200 de euro pe lună (de cinci ori salariul mediu în România), în timp ce programatorii juniori câș...
Ascensiunea industriei de programare în România

Ascensiunea industriei de programare în România

  Industria programării în România   Trăim în era digitală. În lumea modernă digitalizată, în zilele noastre este greu să ne imaginăm că întreprinderile funcționează fără a util...