Kako da optimizujete MySQL tabele

Optimizovanje MySQL tabela pomaže da presložite podatke na serveru i ubrzate rad baze. Posebno je važno da znate gde da koristite određenu funkciju za optimizaciju i kako da je primenite u određenom slučaju. Zato ćemo u ovom tekstu pokazati nekoliko praktičnih saveta i funkcija za optimizaciju MySQL tabela.

Preduslov je da imate instaliranu i konfigurisan MySQL verziju 8.0, pristup command prompt-u i komandnoj liniji i da imate fragmentirane tabele u vašoj bazi podataka.

Zašto je potrebna optimizacija MySQL tabela?

Glavni uzrok neoptimizovane MySQL tabele su česti upiti za ažuriranje i brisanje podataka. To na kraju ima za posledicu fragmentaciju, koja dovodi do toga da tabele zauzimaju više prostora nego što je to zaista potrebno i da upiti traju duže nego što bi trebalo.

Tehnike optimizacije MySQL tabela se prevashodno odnose na uređenje podataka unutar baze podataka. One donose čistu bazu, bez redundantnog, neiskorišćenog prostora, što na kraju rezultira ubrzanjem upita.

Kada bi trebalo da optimizujete MySQL tabele?

Tabele kod kojih se podaci u bazi konstantno ažuriraju, kao što je slučaj kod transakcionih baza podataka, su najčešći kandidati za optimizaciju.

Ipak, u zavisnosti od veličine baze podataka, kod većih tabela, optimizacionom upitu je potrebno dosta vremena za realizaciju. Iz tog razloga, zaključavanje tabele na više sati, nije dobro rešenje za transakcione sisteme.

Umesto da odmah pređemo na optimizaciju, u takvim slučajevima možemo da pokušamo neke od ovih trikova za INNODB engine tabele:

Obrišite indeks, optimizujte i ponovo dodajte indeks. Ako baza neko vreme radi bez indeksa, brisanje, optimizacija i ponovno dodavanje indeksa, pomaže da se ona u nekim slučajevima ubrza.

Analizirajte koje vrednosti mogu imati koristi od optimizacije. Ponekad primarni indeks nije izvor problema. Optimizovanje pogrešne vrednosti može da dovede do fragmentacije kod sekundarnih indeksa.

Pronađite tabele za optimizaciju

Postoji nekoliko načina da prikažete tabele i analizirate ih za optimizaciju. Počnite tako što će te se povezati na vašu MySQL bazu:

use <database name>

U zavisnosti od konkretnog slučaja, filtirajte potrebne informacije prateći neke od ovih saveta:

Prikažite neiskorišćen prostor u tabeli

Proverite status željene tabele pomoću komande:

show table status like "<table name>" \G

Izlaz pokazuje neke osnovne informacije o tabeli. Sledeća dva broja su važna:

Data_length – predstavlja ukupnu koliičinu prostora koji zauzima baza podataka
Data_free – pokazuje alocirane neiskorišćene bajtove unutar tabele baze podataka. Ova informacija pomaže da identifikujemo kojim tabelama je potrebna optimizacija i koliko prostora će nakon toga biti oslobođeno.

Prikažite neiskorišćen prostor za sve tabele

Information schema čuva meta podatke o šemi baze podataka. Da biste proverili alocirane nekorišćene podatke za sve tabele u odabranoj šemi, pokrenite:

select table_name, data_length, data_free
from information_schema.tables
where table_schema='<schema name>'
order by data_free desc;

Ovaj upit prikazuje ime tabele, ukupan prostor i neiskorišćeni alocirani prostor. Podrazumevano, memorija je prikazana u bajtovima.

Prikažite podatke u megabajtovima

Print-ajte podatke u megabajtima pomoću komande:

select table_name, round(data_length/1024/1024), round(data_free/1024/1024)
from information_schema.tables
where table_schema='<schema name>'
order by data_free desc;

Tabele sa ovog primera nisu ozbiljnije fragmentirane. Ipak, korišćenje optimize table pomaže da se oslobodi nešto prostora iz test_table.

Optimizujte tabele

Postoji više načina da optimizujete tabele pomoću defragmentacije. Ipak, većina tih koraka se izvide vrlo slično.

Prvo proces pravi privremenu kopiju tabele u kojoj se dešava optimizacija. Kada se ova operacija završi, funkcija menja originalnu tabelu optimizovanom tabelom, ujedno joj menjajući naziv u naziv originalne tabele.

Optimizujte tabelu pomoću MySQL-a

MySQL ima funkciju za optimizaciju tabele. Da biste otimizovali tabelu koristite:

OPTIMIZE TABLE <table name>;

Izlaz pokazuje informativni status poruke o akcijama i rezultatima optimizacije u tabelarnom prikazu.

Optimizujte više tabela odjednom

Da biste optimizovali više tabela odjednom, koristite:

OPTIMIZE TABLE <table 1>, <table 2>, <table 3>;

Rezultat pokazuje status optimizacija za svaku optimizovanu tabelu.

Optimizujte tabele koristeći terminal

Pokrenite optimizaciju tabele kroz Linux terminal:

sudo mysqlcheck -o <schema> <table> -u <username> -p <password>

Na primer da biste pokrenuli proveru shema-e pod nazivom test i tabele pod nazivom test_table, pomoću root korisničkih kredencijala, pokrenite:

sudo mysqlcheck -o test test_table -u root

Da biste pokrenuli optimizaciju više tabela, odvojte svaki naziv tabele pomoću space-a:

sudo mysqlcheck -o <schema> <table 1> <table 2> -u <username> -p <password>

Nakon optimizacije

Rezultati optimizacije menjaju vrednosti data_length i data_free unutar optimizovanih tabela. Obe vrednosti su smanjenje, što implicira:

• da je optimizacija oslobodila neiskorišćenu alociranu memoriju
• da je ukupna memorija baze podataka manja, zbog oslobođenog prostora.

Zaključak

Kao što smo videli, optimizacija MySQL tabela zahteva pažljivu pripremu. Ovaj vodič vam je pokazao nekoliko korisnih saveta za optimizaciju.