Hidas SELECT, joka ennen valmistui 20 millisekunnissa, alkaa yhtäkkiä jumittaa 12 sekunniksi. Tietokanta kasvoi 50 tuhannesta rivistä 5 miljoonaan, ja jokaisesta kyselystä tuli arpapeliä. Kuulostaako tutulta?

Indeksit ovat asia, josta kaikki ovat kuulleet, mutta harva määrittelee niitä harkitusti. Lisäsit pari, asiat tuntuivat nopeutuvan ja jatkoit eteenpäin. Sitten puoli vuotta myöhemmin INSERT on hitaampi kuin SELECT ilman indeksiä, koska jokainen lisäys rakentaa uudelleen viisi tarpeetonta B-puuta.

Puretaan nyt, miten indeksit oikeasti toimivat, millaisia tyyppejä on olemassa ja ennen kaikkea, mitä sääntöjä niiden rakentamisessa kannattaa noudattaa, jotta et aiheuta kaaosta tuotannossa.

💡 Nopea yleiskatsaus:

• Ymmärrä B-puu-, hajautus- ja yhdistelmäindeksien mekaniikka, sillä oikean tyypin valinta on mahdotonta ilman tätä tietoa
• Hallitse 7 keskeistä käytäntöä: viiteavainten indeksoinnista käyttämättömien indeksien poistamiseen
• Opi lukemaan EXPLAIN ja erottamaan hyödyllinen indeksi hyödyttömästä

Mikä on tietokantaindeksi

Tietokantaindeksi on erillinen rakenne, joka tallentaa yhden tai useamman taulun sarakkeen lajitellut arvot sekä osoittimet riveihin. Kun suoritat SELECT ... WHERE category_id = 42, palvelin ilman indeksiä käy taulun läpi rivi riviltä (koko taulun läpikäynti). Indeksin avulla se löytää tarvittavat tietueet ajassa O(log n), kuten nimen etsiminen puhelinluettelosta.

Indeksi luodaan komennolla CREATE INDEX:

1
-- Regular index
2
CREATE INDEX idx_category
3
ON products (category_id);
4
 
5
-- Unique index
6
CREATE UNIQUE INDEX idx_email
7
ON users (email);

Mutta nopeampien lukujen hintana ovat hitaammat kirjoitukset. Jokaisen INSERT-, UPDATE- ja DELETE-operaation on päivitettävä taulun lisäksi kaikki siihen liittyvät indeksit. Kolme indeksiä taulussa, jossa on miljoona riviä, ja massalisäykset hidastuvat kertaluokkaa. Luku- ja kirjoitusnopeuden välinen tasapaino on indeksisuunnittelun keskeinen kysymys.

Hussein Nasserin yksityiskohtainen aiheen käsittely: 497 tuhatta tilaajaa, insinöörimäinen lähestymistapa ilman höttöä. Hän käyttää esimerkkinä PostgreSQL:ää ja näyttää indeksien sisäisen mekaniikan: miksi yksi CREATE INDEX nopeuttaa kyselyä 100-kertaisesti, kun taas toinen ei tee mitään.

Indeksityypit: milloin mitäkin käytetään

Indeksityypin valinta määrittää, kuinka tehokkaasti tietokanta käsittelee kyselysi. Eri tietokannanhallintajärjestelmät toteuttavat ne eri tavoin, mutta periaatteet ovat yleispäteviä.

B-puu (balanced tree)

Oletustyyppi useimmissa relaatiotietokannanhallintajärjestelmissä. PostgreSQL, MySQL, Oracle ja SQL Server käyttävät kaikki B-puuta oletusindeksinä. Se tallentaa avaimet lajitellussa järjestyksessä, tukee vertailuoperaatioita, arvovälejä (BETWEEN), etuliitehakua (LIKE 'prefix%') ja lajittelua. Se on paras valinta valtaosaan tapauksista.

Hajautusindeksi

Toimii vain tarkan yhtäsuuruusvertailun = kanssa. Salamannopea pistekyselyissä, mutta hyödytön arvoväleille ja lajittelulle. PostgreSQL:ssä hajautusindekseistä tuli tuotantokelpoisia versiosta 10 alkaen; MySQL:ssä niitä ei ole saatavilla InnoDB:ssä (vain MEMORYssä).

Klusteri-indeksi

Määrittää rivien fyysisen järjestyksen levyllä. MySQL InnoDB:ssä perusavain on aina klusteroitu: rivit tallennetaan PRIMARY KEY -järjestyksessä. SQL Serverissä on yksi klusteri-indeksi taulua kohden. Oikean klusterointiavaimen valinta (monotonisesti kasvava: BIGSERIAL, AUTO_INCREMENT tai UNIQUEIDENTIFIER yhdessä NEWSEQUENTIALID():n kanssa) tuo hyötyjä arvovälikyselyissä ja estää sivujen pirstaloitumisen.

Yhdistelmäindeksi

Kahden tai useamman sarakkeen indeksi. Kriittinen kyselyille, jotka suodattavat ja lajittelevat usean sarakkeen perusteella:

1
CREATE INDEX idx_order_date_status
2
ON orders (order_date, status);

Sarakkeiden järjestyksellä on väliä: laita WHERE-ehdossa käytetty, korkeimman valikoivuuden sarake ensin. Vasemmanpuoleisimman etuliitteen periaate: indeksi (A, B, C) toimii ehdoille WHERE A ja WHERE A AND B, mutta ei ehdolle WHERE B AND C.

Peittoindeksi

Sisältää kaikki kyselyn tarvitsemat sarakkeet, sekä suodatusta että tulostusta varten. Tietokanta hakee kaiken indeksistä koskematta tauluun. PostgreSQL:ssä tämä tehdään INCLUDE-sarakkeilla; MySQL InnoDB:ssä implisiittinen peitto tapahtuu klusteri-indeksin kautta.

7 Keskeistä indeksointikäytäntöä

1. Indeksoi WHERE-ehdossa käytetyt sarakkeet

Ensimmäinen sääntö: jokainen sarake, jota käytetään säännöllisesti ehdoissa WHERE, JOIN ... ON ja HAVING, tulisi indeksoida. Nämä ovat operaatiot, jotka hyötyvät indekseistä eniten.

Ennen indeksin luomista tarkista sarakkeen valikoivuus. Jos status-sarakkeella on vain kolme arvoa ('new', 'processing', 'done') ja rivejä on 2 miljoonaa, status-sarakkeen indeksi on lähes hyödytön: suunnittelija valitsee koko taulun läpikäynnin halvempana vaihtoehtona. Indeksi on järkevä, kun uniikkien arvojen määrä on riittävän suuri suhteessa taulun kokoon.

2. Indeksoi lajittelussa käytetyt sarakkeet

ORDER BY ilman indeksiä tarkoittaa filesortia (MySQL) tai eksplisiittistä lajittelua (PostgreSQL): palvelin kerää kaikki rivit ja lajittelee ne muistissa (tai levyllä, jos work_mem/sort_buffer_size on liian pieni). Indeksi samoilla sarakkeilla kuin ORDER BY tulee ilmaiseksi, koska data on jo valmiiksi lajiteltu B-puussa.

1
-- No index on created_at: filesort on millions of rows
2
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20;
3
 
4
-- Index solves the problem
5
CREATE INDEX idx_posts_created ON posts (created_at);

3. Indeksoi GROUP BY:ssä ja koosteissa käytetyt sarakkeet

Ryhmittely ilman indeksiä vaatii koko taulun läpikäynnin ja hajautustaulun rakentamisen. GROUP BY -sarakkeiden indeksi muuttaa operaation virtaavaksi koosteeksi: rivit ovat jo valmiiksi ryhmitelty avainjärjestyksessä.

4. Indeksoi kaikki viiteavaimet

Indeksoimaton viiteavain on tikittävä aikapommi. DELETE FROM users WHERE id = 5, kun orders-taulussa on FOREIGN KEY (user_id) REFERENCES users(id) mutta ei indeksiä user_id-sarakkeelle, tarkoittaa orders-taulun täyttä läpikäyntiä jokaiselle poistolle. Kaikki suositut tietokannanhallintajärjestelmät vaativat indeksin viiteavaimelle tai luovat sellaisen implisiittisesti (MySQL InnoDB tekee sen automaattisesti, PostgreSQL ei).

5. Indeksoi uniikit sarakkeet ja perusavaimet

Perusavain indeksoidaan automaattisesti (usein klusteri-indeksinä). Eksplisiittinen UNIQUE INDEX suojaa duplikaateilta ja nopeuttaa myös hakuja. Jokaisella sarakkeella, jolla on liiketoiminnallinen uniikkiusrajoite (esimerkiksi email, slug tai external_id), tulisi olla uniikki indeksi, sekä eheyden että suorituskyvyn vuoksi.

6. Käytä klusteri-indeksiä harkitusti

Suurissa tauluissa (kymmeniä miljoonia rivejä) oikea klusterointiavain on kriittinen. Hyvä valinta on monotonisesti kasvava arvo: AUTO_INCREMENT, BIGSERIAL tai UUID v7. Satunnainen UUID klusterointiavaimena aiheuttaa sivujen pirstaloitumista: jokainen lisäys osuu satunnaiseen kohtaan B-puussa, jakaen täydet sivut kahdeksi puoliksi täytetyksi sivuksi.

7. Poista käyttämättömät indeksit

Indeksi, jota mikään kysely ei käytä, on puhdasta tappiota. Se hidastaa kirjoituksia, vie levytilaa ja puskurimuistia sekä harhauttaa kyselyn suunnittelijaa. PostgreSQL:ssä järjestelmätaulu pg_stat_user_indexes tarjoaa listan käyttämättömistä indekseistä:

1
SELECT schemaname, relname, indexrelname, idx_scan
2
FROM pg_stat_user_indexes
3
WHERE idx_scan = 0
4
ORDER BY relname;

MySQL:ssä vastaavaa tietoa on saatavilla näkymästä sys.schema_unused_indexes (versiosta 5.7 alkaen). Aikatauluta kuukausittainen tarkastus ja poista indeksit, joita ei ole käytetty kertaakaan raportointijakson aikana.

Kuinka varmistaa, että indeksisi toimivat

Kun olet luonut indeksin, varmista, että sitä todella käytetään. Komento EXPLAIN (tai EXPLAIN ANALYZE) näyttää kyselyn suoritussuunnitelman ja todellisen indeksien käytön:

1
EXPLAIN ANALYZE
2
SELECT * FROM orders
3
WHERE customer_id = 12345
4
ORDER BY order_date DESC;

Tulosteessa etsi Index Scan tai Index Only Scan (PostgreSQL) / Using index (MySQL). Jos näet Seq Scan (PostgreSQL) tai Using where; Using filesort (MySQL), indeksiä ei käytetä. Mahdollisia syitä: alhainen valikoivuus, väärä indeksityyppi, väärä sarakejärjestys yhdistelmäindeksissä tai vanhentuneet tilastot (ANALYZE table_name;).

Seuraa mittareita säännöllisesti: pg_stat_user_indexes.idx_scan PostgreSQL:ssä, sys.schema_index_statistics MySQL:ssä. Indeksi, jolla ei ole yhtään skannausta kuukauteen, on ehdokas poistettavaksi.

⁉️🤔 Usein kysytyt kysymykset

Kuinka monta indeksiä taululla tulisi olla?

Ihanteellisesti 2-6 indeksiä aktiivisesti käytössä olevaa taulua kohden. Alle kaksi tarkoittaa lähes varmasti, että jotkin kyselyt ovat alimitoitettuja. Yli kuusi, ja sinun tulisi tarkistaa huolellisesti, ovatko kaikki todella tarpeen: jokainen ylimääräinen indeksi hidastaa kirjoituksia. Hakutauluille (harvoin kirjoituksia, usein lukuja) useampi indeksi on perusteltua. Paljon liikennettä sisältäville operatiivisille tauluille (runsaasti INSERT/UPDATE) pidä määrä minimissä.

Miten yhdistelmäindeksi on parempi kuin useat yksittäisten sarakkeiden indeksit?

Yhdistelmäindeksi (A, B) on YKSI rakenne. Palvelin kulkee sen läpi kerran. Kolme erillistä indeksiä (A), (B) ja (C) ehdolle WHERE A=1 AND B=2 pakottavat palvelimen joko valitsemaan yhden indeksin (ja suodattamaan loput) tai suorittamaan bittikarttaindeksiskannauksen (yhdistäen bittikartat). Yhdistelmäindeksi on lähes aina tehokkaampi, edellyttäen että sarakejärjestys vastaa kyselyitäsi.

Milloin indeksistä on haittaa eikä hyötyä?

Kolme tyypillistä skenaariota. Ensimmäinen: taulu on pieni (korkeintaan muutama tuhat riviä), ja koko taulun läpikäynti on nopeampi kuin indeksin lukeminen ja rivien nouto. Toinen: indeksi sarakkeessa, jolla on alhainen valikoivuus (is_deleted, status kolmella arvolla). Kolmas: massalisäykset ETL/import-prosessin aikana, jolloin indeksit rakennetaan uudelleen jokaisessa erässä. Poista ne ennen latausta ja luo uudelleen sen jälkeen.

Pitäisikö JOIN-ehdoissa käytetyt sarakkeet indeksoida?

Ehdottomasti. Jokainen JOIN ilman indeksiä ulomman taulun liitossarakkeessa on sisäkkäinen silmukka täydellä läpikäynnillä. Kyselyssä LEFT JOIN orders ON users.id = orders.user_id indeksi orders.user_id-sarakkeelle muuttaa sisäkkäisen silmukan indeksihauksi. Indeksoi aina sarakkeet, joiden perusteella liität.

B-puu vai hajautus: kumpi valita tarkkoihin hakuihin?

=-vertailuun hajautus on nopeampi: hajautushaku vie vakioajan, kun taas B-puu käy puun läpi logaritmisella määrällä askelia. Mutta hajautus ei tue arvovälejä, lajittelua eikä UNIQUE-ehtoa. Käytännössä B-puu kattaa valtaosan skenaarioista; hajautus on niche-työkalu pistehakuihin avaimella korkean kuorman järjestelmissä (istunnot, välimuistit). PostgreSQL:ssä hajautusindeksit ovat olleet tuotantokelpoisia versiosta 10 alkaen ja vievät vähemmän tilaa kuin B-puu.

Pitäisikö indeksoida "varmuuden vuoksi"?

Ei. Jokainen indeksi on kompromissi. Se nopeuttaa lukuja hitaampien kirjoitusten ja lisääntyneen levytilan kustannuksella. Älä indeksoi "varmuuden vuoksi"; indeksoi tiettyjä, sovelluksessasi oikeasti ajettavia kyselyitä varten. Profiloi hitaat kyselyt (pg_stat_statements, slow_query_log), lisää indeksejä kirurgisesti ja tarkista EXPLAIN ennen ja jälkeen.

Tärkein opetus on yksinkertainen: indeksit ovat työkalu, eivät itseisarvo. Yksi hyvin suunniteltu yhdistelmäindeksi voi korvata kolme yksittäisen sarakkeen indeksiä ja säästää gigatavuja levytilaa. Ja yksi käyttämätön indeksi kirjoituspainotteisessa taulussa voi hidastaa koko sovellusta.

Jos haluat syventyä aiheeseen, aloita virallisesta SQL Serverin indeksisuunnittelun oppaasta ja PostgreSQL:n indeksityyppien dokumentaatiosta. Ja jos kohtaat kyselyn, jota indeksit eivät pysty korjaamaan, ongelma saattaa olla itse tietomallissa.