MySQL ir MariaDB užklausų bei lentelių optimizavimas

iv_vytenisg

MariaDB bei MySQL yra vienos populiariausių duomenų bazių valdymo sistemų naudojančių SQL kalbą duomenų įvedimui bei išvedimui (rezultatų pateikimui).  Nors SQL užklausos yra paprastos komandos, kurias nesunku išmokti, tačiau nevisos užklausos ir duomenų bazės funkcijos atlieka veiksmus taip pat efektyviai. Tai tampa ypač svarbu kai informacijos kiekis duomenų bazėje didėja ir tai pradeda stabdyti svetainės greitį augant lankytojų skaičiui.

Šioje pamokoje bus aptariamos kelios paprastos  priemonės, kurių galima imtis norint pagreitinti MySQL ir MariaDB užklausas. Testavimams atlikti rekomenduojame naudoti virtuliai dedikuotą serverį, kuriame jau esate įdiegę MySQL arba MariaDB tarnybą. Kaip tai alikti, rasite šioje pamokose: Ubuntu/Debian, CentOS 7.

Lentelių dizaino bendrosios ypatybės

Vienas iš pamatinių būdų pagerinti užklausų greitį taikomas pačių lentelių struktūros dizainui. Todėl tai reiškia, kad efektyviausia pradėti optimizuoti duomenis dar prieš pradedant naudoti duomenų bazes. Todėl pateikiame kelis klausimus į kuriuos turite atsakyti sau prieš pradedant kurti duomenų bazių lenteles:

Kaip iš esmės bus naudojamos lentelės?

Nustatymas kaip lentelių duomenys bus naudojami dažniausiai nurodo kelią link geriausio metodo modeliuojant duomenų struktūras. Jei tam tikrus duomenis atnaujinsite dažnai, geriausia, kad jie būtų atskiroje lentelėje. To neatlikus gali sukelti užklausų kešavimą, kuris bus pašalinamas ir cėl atstatomas pakartotinai dėlto, kad bus pastebime jog aptinkama nauja informacija. Jei tai atsitiks atskiroje lentelėje, kiti stulpeliai gali ir toliau naudotis kešavimo privalumais.

Paprastai mažesnių lentelių operacijų atnaujinimas yra greitesnis būdas. Išsami sudėtingų duomenų analizė paprastai yra užduotis, geriausiai pritaikoma didelėms lentelėms, nes prijungimas gali būti pakankamai brangi operacija.

Kokie duomenų tipai yra reikalingi?

Kartais galite sutaupyti daug laiko ilgainiui, jei iš anksto nustatote tam tikrus duomenų dydžių apribojimus. Pavyzdžiui, jei yra ribojamas galimų įrašų skaičius konkrečiame laukelyje, kuriame nurodomos eilutės reikšmės, vietoj „varchar“ galite naudoti „enum“ tipą. Šis duomenų tipas yra kompaktiškenis ir todėl užklausa greičiau apdorojama. Tam, kad būtų aiškiau pateikiame papildomą pavyzdį šiao situacijai - jei turite tik keletą skirtingų tipų vartotojų, galite sukurti stulpelį, kuriame naudojamas „enum“ duomenų tipas su galimomis reikšmėmis: administratorius, moderatorius, vartotojas.

Kurį stulpelį naudosite užklausų generavimui?

Iš anksto žinant, kurie laukeliai gaus užklausas pakartotinai, gali žymiai pagerinti jūsų duomenų bazės greitį.  Stulpelių indeksavimas, kuriuos tikitės naudoti paieškai, labai padeda šioje situacijoje. Kurdami lentelę galite pridėti rodyklę naudodami šią sintaksę:

CREATE TABLE example_table (
    id INTEGER NOT NULL AUTO_INCREMENT,
    name VARCHAR(50),
    address VARCHAR(150),
    username VARCHAR(16),
    PRIMARY KEY (id),
    INDEX (username)
);

Tai bus naudinga, jeigu žinome, kad naudotojai ieškos informcijos pagal naudotojo vardą:

explain example_table;

+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| name     | varchar(50)  | YES  |     | NULL    |                |
| address  | varchar(150) | YES  |     | NULL    |                |
| username | varchar(16)  | YES  | MUL | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

Kaip matote, turimte du indikatorius mūsų lentelei. Pirmasis yra pagrindinis raktas, kuris šiuo atveju yra id laukas. Antrasis yra rodyklė, kurią mes įtraukėme į username lauką. Tai pagerins užklausas, kuriose naudojamas šis laukas.

Nors konceptualiu požiūriu yra naudinga galvoti apie tai, kurie laukai turėtų būti indeksuojami kuriant, lygiai taip pat paprasta pridėti indeksus prie jau esamų lentelių. Pavyzdžiui galite pridėti tokį:

CREATE INDEX index_name ON table_name(column_name);

Kitas būdas įvykdyti tą patį yra šis:

ALTER TABLE table_name ADD INDEX ( column_name );

Užklausose naudokite paaiškinimus, jei norite rasti taškus iki indekso

Jei jūsų programa teikia užklausas labai nuspėjamu keliu, turėtumėte išanalizuoti savo užklausas, kad įsitikintumėte, jog užklausos naudoja indeksus visose įmanomose vietose. Tai lengva naudojant explain funkciją

Tam, kad pamtytume kaip tai veikia, importuojame MySQL duomenų bazės pavyzdį:

wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2
tar xjvf employees_db-full-1.0.6.tar.bz2
cd employees_db
mysql -u root -p -t < employees.sql

Prisijungiame atgal į MySQL tam, kad galėtume paleisti kelias užklausas:

mysql -u root -p
use employees;

Pirmiausia turime nurodyti, kad MySQL neturėtų naudoti kešavimo tam, kad galėtume tiksliai įvertinti, koks laikas reikalingas šių užduočių įvykdymui:

SET GLOBAL query_cache_size = 0;
SHOW VARIABLES LIKE "query_cache_size";

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_size | 0     |
+------------------+-------+
1 row in set (0.00 sec)

Dabar galime vykdyti paprastą užklausą dideliame duomenų rinkinyje:

SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;

+----------+
| count(*) |
+----------+
|   588322 |
+----------+
1 row in set (0.60 sec)

Norėdami pamatyti, kaip MySQL vykdo užklausą, prieš pridėdami užklausą galite pridėti explain raktinį žodį į pačią užklausą:

EXPLAIN SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;

+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | salaries | ALL  | NULL          | NULL | NULL    | NULL | 2844738 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

Pažvelgę į key lauką pamatysite, kad jo reikšmė yra NULL. Tai reiškia, kad šiai užklausai nenaudojamas nė vienas indeksas.

Pridėkime vieną indeksą ir paleiskite užklausą dar kartą, kad patikrintume, ar ji ją paspartina:

ALTER TABLE salaries ADD INDEX ( salary );
SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;

+----------+
| count(*) |
+----------+
|   588322 |
+----------+
1 row in set (0.14 sec)

Kaip matote, tai žymiai pagerina užklausų našumą. Kita bendra taisyklė, kurią reikia naudoti su indeksais - atkreipti dėmesį į lentelių prijungimus. Turėtumėte sukurti indeksus ir nurodyti tą patį duomenų tipą visuose stulpeliuose, kurie bus naudojami sujungiant lenteles.

Pvz., Jei turite lentelę pavadinimu „cheeses“ ir lentelę pavadinimu „ingredients“, galite prijungti prie panašaus kiekvienos lentelės ingredient_ID lauko, kuris galėtų būti INT. Tada galėtume sukurti abiejų šių sričių indeksus ir mūsų prijungimas paspartėtų.

Užklausų optimizavimas greičiui

Kita galima lygties pusė, bandant pagreitinti užklausas, yra pačių užklausų optimizavimas. Kai kurios operacijos yra skaičiuojamos intensyviau už kitas. Dažnai yra keli būdai, kaip pasiekti šį rezultatą, kai kuriais atvejais pavyks išvengti ilgai trunkančių operacijų. Atsižvelgiant į tai, kam naudojate užklausos rezultatus, kai kuriais atvejais gali reikėti tik riboto rezultatų skaičiaus. Pavyzdžiui, jei jums reikia tik išsiaiškinti, ar įmonėje yra kas nors, kas uždirba mažiau nei 12 000€, galite naudoti:

SELECT * FROM SALARIES WHERE salary < 40000 LIMIT 1;

+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10022 |  11950 | 2000-09-02 | 2001-09-02 |
+--------+--------+------------+------------+
1 row in set (0.00 sec)

Ši užklausa vykdoma ypač greitai, nes iš esmės trumpuoju apėjimu pasiekiamas pirmasis teigiamas rezultatas.

Jei jūsų užklausose naudojami palyginimai „or“, o abi komponentų dalys testuoja skirtingus laukus, jūsų užklausa gali būti ilgesnė nei būtina. Pavyzdžiui: jei ieškote darbuotojo, kurio vardas arba pavardė prasideda „Bre“, turėsite ieškoti dviejų atskirų stulpelių.

SELECT * FROM employees WHERE last_name like 'Bre%' OR first_name like 'Bre%';

Ši operacija gali būti greitesnė, jei vardų ieškosime vienoje užklausoje, o atinkamų pavardžių kitoje, ir tada sujungsime išvestį. Tai galime padaryti su union operatoriumi:

SELECT * FROM employees WHERE last_name like 'Bre%' UNION SELECT * FROM employees WHERE first_name like 'Bre%';

Kai kuriais atvejais MySQL automatiškai naudos union operaciją. Aukščiau pateiktas pavyzdys iš tikrųjų yra atvejis, kai „MySQL“ tai padarys automatiškai. Galite pamatyti, ar taip yra, patikrinę, ar rūšiuojama, dar kartą naudodamiesi explain.

Apibendrinimas

Yra nepaprastai daug būdų, kaip galite patobulinti MySQL ir MariaDB lenteles ir duomenų bazes atsižvelgiant į jūsų naudojimo atvejį. Šiame straipsnyje yra tik keli patarimai, kurie gali būti naudingi norint pradėti optimizavimą.

Šios duomenų bazių valdymo sistemos turi puikią dokumentaciją, kaip optimizuoti ir suderinti skirtingus scenarijus. Specifikacija labai priklauso nuo to, kokį funkcionalumą norite optimizuoti, nes priešingu atveju jie būtų buvę automatiškai optimizuoti jau įdiegus sistemą. Kai tik patvirtinsite savo reikalavimus ir susipažinsite su operacijomis, kurios bus daromos pakartotinai, galite išmokti patobulinti nustatymus konkrečioms užklausoms.