SQL in indeksi

December 1, 2007 4 minute read

V zadnjem tednu se mi je zgodilo s tremi različnimi ljudmi, da so mi potožili, da poizvedbe, v enem primeru v PostgreSQL, v dveh pa v MySQL, trajajo predolgo. Kaj bi lahko bilo narobe? Poglejmo, zaprosim za primer poizvedbe in podatke o indeksih. Poizvedba je super, le indeks je le primarni, na polju id, zapisov pa v tabeli nekaj sto tisoč. Povprašam, zakaj ne uporablja indeksov, odgovor je ali “saj imam primarni indeks!” ali pa “kaj pa je to indeks?”.

Do sedaj so se mi zdele te teme preveč trivialne, da bi o njih pisal, se mi je zdelo, da bodo ljudje pa že vedeli, kaj indeks je in za kaj se ga uporablja. No, motil sem se, pa sedaj popravljam to napako.

Recimo, da imamo tabelo svoje zbirke MP3jev, ki obsega kakšnih tristo tisoč naslovov. Bili smo pridni in smo celo naredili šifrant izvajalcev, da lažje najdemo vse MP3je izbranega popevkarja. Nekako takole:

CREATE TABLE mp3 (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    izv_id INT,
    naslov TEXT,
    datoteka TEXT
  );

Polje izv_id je seveda relacija na polje id v tabeli izvajalcev. Tabelo napolnimo s tistimi našimi tristo tisoč naslovi in poiščemo recimo skupino KoRn. Recimo, da, zaradi poenostavitve, kar vemo, da ima skupina KoRn šifro 423. Poizvedba bi bila nekako takšna:

SELECT
        *
    FROM
        mp3
    WHERE
        izv_id = 423;

Dela? Seveda dela, ampak koliko časa traja? Vsekakor predolgo, ampak zakaj?

SQL strežniku, pa če je to PostgreSQL, MySQL, MSSQL, ali katerikoli drugi, se seveda niti slučajno ne sanja, kje v datoteki bi lahko te podatke našel. Zato se pač sprehodi od prvega zapisa proti zadnjemu in vsakega preveri, če ima vrednost polja izv_id enako 423. Ko najde prvi zapis, ki ustreza pogoju, ne ve, ali bo mogoče našel še drugega, zato se pri prvem seveda ne ustavi, ampak opravlja svojo nalogo do konca datoteke, čez vseh tristo tisoč zapisov. To početje nekako ne more trajati manj kot preveč, kajne?

Naš najboljši prijatelj pri analizi poizvedb je EXPLAIN. Poglejmo:

EXPLAIN SELECT
        *
    FROM
        mp3
    WHERE
        izv_id = 423;

Kaj dobimo? Naslednje:

+------+-----+----+----+----+-------+----+------+-----------+
|s_type|table|type|keys|key |key_len|ref |rows  |Extra      |
+------+-----+----+----+----+-------+----+------+-----------+
|SIMPLE|mp3  |ALL |NULL|NULL|NULL   |NULL|300000|Using where| 
+------+-----+----+----+----+-------+----+------+-----------+

Ne dobimo čisto točno tega, prvi stolpec sem ukinil, ker pomeni le zaporedno številko selecta v poizvedbi, dva zapisa v glavi sem pa skrajšal, eden je s_type, ki bi moral biti select_type in keys, ki bi moral biti possible_keys. Imam preozek blog, pa ni šlo drugače :)

Naj razložim, kaj nam gornja tabela govori. V polju select_type imamo vrednost SIMPLE, ki nam pove, da izvajamo enostavno poizvedbo, brez UNIONov ali SUBQUERYjev. Polje table ima vrednost mp3 in verjetno nam je že jasno, da nam pove ime tabele, nad katero izvajamo poizvedbo. Tretji stolpec, torej type, je za nas ključen. Vrednost ALL nam pove, da se izvaja “full table scan”, to je situacija, ki sem jo opisoval zgoraj. Pri tem primeru bom omenil le še dva stolpca. Stolpec rows nam pove, koliko zapisov bo zajetih v ta full table scan, v našem primeru torej okroglih tristo tisoč, v polju Extra pa nam še pove, da bo vsak zapis še preveril s pogojem, ki smo ga navedli v WHERE. Vsekakor izredno slab scenarij.

Kaj pa bi pomagalo pri tej poizvedbi? Datoteka urejena po polju izv_id, vsekakor. Zakaj? Ko najdemo prvo vrednost 423, se samo še sprehodimo po zapisih do zadnjega zapisa, ki vsebuje to vrednost, pa lahko prenehamo. In točno to indeks je, datoteka, urejena po vrednostih v izbranih poljih, pa ne samo to, zadeva zna celo zelo hitro najti tudi prvi zapis z iskano vrednostjo.

Naredimo najprej indeks po polju izv_id, recimo takole:

ALTER TABLE mp3 ADD INDEX (izv_id);

Poskusimo še enkrat EXPLAIN poizvedbe? Pa dajmo, dobimo naslednje:

+------+-----+----+------+------+-------+-----+----+-----+
|s_type|table|type|keys  |key   |key_len|ref  |rows|Extra|
+------+-----+----+------+------+-------+-----+----+-----+
|SIMPLE|mp3  |ref |izv_id|izv_id|4      |const|20  |     | 
+------+-----+----+------+------+-------+-----+----+-----+

Nekaj se je spremenilo, kajne? Če pogledamo le stolpec rows, ki je sedaj namesto tristo tisoč le dvajset, lahko že skoraj kar nehamo z razlago. Pa ne bomo, naj razložim še ostale stolpce, ki so nam ostali ali pa so se njihove vrednosti spremenile.

Recimo stolpec type je prej vseboval vrednost ALL, sedaj je v njem vrednost ref. Vsekakor sprememba, ampak kaj pomeni? Vrednost ref pomeni, da so iz tabele prebrani vsi zapisi iz indeksa, ki ustrezajo vrednosti, v našem primeru sicer konstante, lahko pa tudi vrednosti iz prejšnje tabele v poizvedbi. To je vsekakor boljše kot ALL, ko smo delali full table scan, sedaj preberemo le zapise z izbrano vrednostjo polja. V stolpcu possible_keys imamo našteta imena indeksov, za katere strežnik meni, da bi jih mogoče lahko v tej poizvedbi uporabil, v stolpcu key pa je naveden dejansko izbrani indeks. Stolpec key_len nam pove dolžino ključa, v našem primeru štiri bajte, kar ustreza polju vrste INTEGER. Stolpec ref nam pove, s katerimi polji ali konstantami primerjamo vrednosti v indeksu, v našem primeru stolpec pravi const, kar pomeni, da vrednosti primerjamo s konstanto, pa saj vemo, 423 je konstanta, kajne? Stolpec rows smo si že ogledali, stolpec Extra je pa prazen, torej nam strežnik ne želi omeniti nobene posebnosti, ker je očitno ni.

Verjetno ni potrebno posebej poudarjati, da lahko uporaba indeksov izredno pohitri naše poizvedbe, vendar pazimo, hkrati jih tudi upočasni. Govorim o poizvedbah tipa INSERT, UPDATE in DELETE, te sedaj ne posodobijo le osnovne tabele, ampak morajo hkrati posodobiti tudi indekse, kar pa seveda traja nekoliko dlje časa. Iz tega sledi, da uporabite le toliko indeksov in tiste, ki jih res potrebujete, kar preventivno indeksiranje po vseh poljih nikakor ni smotrno.

Vini