Aeglane SELECT, mis varem täitus 20 millisekundiga, hakkab järsku 12 sekundit rippuma. Andmebaas kasvas 50 tuhandelt realt 5 miljonile ja iga päring muutus loteriiks. Kõlab tuttavalt?

Indeksid on midagi, millest kõik on kuulnud, kuid mida vähesed teadlikult seadistavad. Lisasid paar tükki, asjad tundusid kiiremad ja liikusid edasi. Kuus kuud hiljem on INSERT aeglasem kui SELECT ilma indeksita, sest iga sisestus ehitab ümber viis ebavajalikku B-puud.

Vaatame üle, kuidas indeksid tegelikult töötavad, mis tüüpe on olemas ja mis kõige tähtsam, milliseid reegleid nende loomisel järgida, et toodangukeskkonnas kaost mitte tekitada.

💡 Kiire ülevaade:

• Mõista B-puu, pais- ja liitindeksite mehhanismi, sest ilma selle teadmiseta on võimatu õiget tüüpi valida
• Valda 7 peamist praktikat: alates võõrvõtmete indekseerimisest kuni kasutamata indeksite eemaldamiseni
• Õpi lugema käsku EXPLAIN ja eristama kasulikku indeksit kasutust

Mis on andmebaasi indeks

Andmebaasi indeks on eraldiseisev struktuur, mis salvestab ühe või mitme tabeli veeru sorteeritud väärtused koos viitadega ridadele. Kui käivitad SELECT ... WHERE category_id = 42, siis ilma indeksita server skaneerib tabelit rida-realt (täielik tabeli skaneerimine). Indeksiga leiab see vajalikud kirjed O(log n) ajaga, nagu nime otsimine telefoniraamatust.

Indeks luuakse käsuga 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);

Kuid kiirema lugemise hind on aeglasem kirjutamine. Iga INSERT, UPDATE ja DELETE peab uuendama mitte ainult tabelit, vaid ka kõiki seotud indekseid. Kolm indeksit miljoni reaga tabelis ja mahulised sisestused aeglustuvad suurusjärgu võrra. Tasakaal lugemis- ja kirjutamiskiiruse vahel on indeksi kujundamise keskne küsimus.

Teema põhjalik lahtiseletus Hussein Nasserilt: 497 tuhat tellijat, insenerlik lähenemine ilma udujututa. PostgreSQL-i näitel näitab ta indeksite sisemist mehhanismi, miks üks CREATE INDEX kiirendab päringut 100 korda, samas kui teine ei tee midagi.

Indeksi tüübid: millal mida kasutada

Indeksi tüübi valik määrab, kui tõhusalt andmebaas sinu päringut töötleb. Erinevad andmebaasisüsteemid realiseerivad neid erinevalt, kuid põhimõtted on universaalsed.

B-puu (tasakaalustatud puu)

Vaikimisi tüüp enamikes relatsioonilistes andmebaasisüsteemides. PostgreSQL, MySQL, Oracle ja SQL Server kasutavad kõik vaikimisi B-puu indeksit. See salvestab võtmed sorteeritud järjekorras, toetab võrdlusoperatsioone, vahemikke (BETWEEN), prefiksiga otsingut (LIKE 'prefix%') ja sorteerimist. See on parim valik valdavale enamusele juhtudest.

Paisindeks

Töötab ainult täpse võrdsuse võrdluste = korral. Välkkiire punktpäringute puhul, kuid kasutu vahemike ja sorteerimise jaoks. PostgreSQL-is muutusid paisindeksid tootmiskõlbulikuks alates versioonist 10; MySQL-is pole need InnoDB-s saadaval (ainult MEMORY-s).

Klasterindeks

Määrab ridade füüsilise järjestuse kettal. MySQL InnoDB-s on primaarvõti alati klasterdatud: read salvestatakse PRIMARY KEY järjekorras. SQL Serveris on üks klasterindeks tabeli kohta. Õige klastervõtme valimine (monotoonselt kasvav: BIGSERIAL, AUTO_INCREMENT või UNIQUEIDENTIFIER koos NEWSEQUENTIALID()-ga) annab eeliseid vahemikpäringute puhul ja hoiab ära lehekülgede killustumise.

Liitindeks

Indeks kahele või enamale veerule. Kriitilise tähtsusega päringute puhul, mis filtreerivad ja sorteerivad mitme veeru järgi:

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

Veergude järjekord on oluline: esimeseks pane kõrgeima selektiivsusega veerg, mida kasutatakse WHERE tingimuses. Vasakpoolseima prefiksi põhimõte: indeks (A, B, C) töötab WHERE A ja WHERE A AND B korral, kuid mitte WHERE B AND C korral.

Kattev indeks

Sisaldab kõiki veerge, mida päring vajab nii filtreerimiseks kui ka väljundiks. Andmebaas hangib kõik indeksist ilma tabelit puudutamata. PostgreSQL-is tehakse seda INCLUDE veergudega; MySQL InnoDB-s toimub kaudne katmine läbi klasterindeksi.

7 Peamist indekseerimise praktikat

1. Indekseeri veerud, mida kasutatakse WHERE tingimuses

Esimene reegel: iga veerg, mida regulaarselt kasutatakse WHERE, JOIN ... ON ja HAVING tingimustes, peaks olema indekseeritud. Need on operatsioonid, mis saavad indeksitest kõige rohkem kasu.

Enne indeksi loomist kontrolli veeru selektiivsust. Kui status veerul on ainult kolm väärtust ('new', 'processing', 'done') ja ridu on 2 miljonit, on indeks status veerul peaaegu kasutu: planeerija valib odavama variandina täieliku tabeli skaneerimise. Indeks on mõttekas, kui unikaalsete väärtuste arv on tabeli suuruse suhtes piisavalt suur.

2. Indekseeri veerud, mida kasutatakse sorteerimiseks

ORDER BY ilma indeksita tähendab filesort'i (MySQL) või eksplitsiitset sorteerimist (PostgreSQL): server kogub kõik read ja sorteerib need mälus (või kettal, kui work_mem/sort_buffer_size on liiga väike). Indeks samadel veergudel nagu ORDER BY tuleb tasuta, kuna andmed on B-puus juba sorteeritud.

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. Indekseeri veerud, mida kasutatakse GROUP BY ja agregeerimiste puhul

Grupeerimine ilma indeksita nõuab täielikku skaneerimist ja paisktabeli ehitamist. Indeks GROUP BY veergudel muudab operatsiooni voogagregeerimiseks: read on juba võtme järjekorras grupeeritud.

4. Indekseeri kõik võõrvõtmed

Indekseerimata võõrvõti on viitsütikuga pomm. DELETE FROM users WHERE id = 5 koos FOREIGN KEY (user_id) REFERENCES users(id)-ga orders tabelis, kuid ilma indeksita user_id veerul, tähendab iga kustutamise korral orders tabeli täielikku skaneerimist. Kõik populaarsed andmebaasisüsteemid nõuavad võõrvõtme indeksit või loovad selle kaudselt (MySQL InnoDB teeb seda automaatselt, PostgreSQL mitte).

5. Indekseeri unikaalsed veerud ja primaarvõtmed

Primaarvõti indekseeritakse automaatselt (sageli klasterindeksina). Eksplitsiitne UNIQUE INDEX kaitseb duplikaatide eest ja kiirendab ka otsinguid. Igal veerul, millel on äriloogiline unikaalsuse piirang (näiteks email, slug või external_id), peaks olema unikaalne indeks nii terviklikkuse kui ka jõudluse tagamiseks.

6. Kasuta klasterindeksit teadlikult

Suurte tabelite (kümned miljonid read) puhul on õige klastervõti kriitilise tähtsusega. Hea valik on monotoonselt kasvav väärtus: AUTO_INCREMENT, BIGSERIAL või UUID v7. Juhuslik UUID klastervõtmena põhjustab lehekülgede killustumist: iga sisestus satub B-puus juhuslikku kohta, lõhestades täis leheküljed kaheks pooltühjaks.

7. Eemalda kasutamata indeksid

Indeks, mida ükski päring ei kasuta, on puhas kahju. See aeglustab kirjutamist, võtab kettaruumi ja puhverpõhja mälu ning eksitab päringuplaneerijat. PostgreSQL-is pakub süsteemitabel pg_stat_user_indexes kasutamata indeksite loendit:

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

MySQL-is on sarnane teave saadaval sys.schema_unused_indexes kaudu (alates versioonist 5.7). Planeeri igakuine audit ja eemalda indeksid, mida pole aruandeperioodi jooksul kordagi kasutatud.

Kuidas kontrollida, kas sinu indeksid töötavad

Kui oled indeksi loonud, kontrolli, kas seda ka tegelikult kasutatakse. Käsk EXPLAIN (või EXPLAIN ANALYZE) näitab päringu täitmisplaani ja tegelikku indeksi kasutust:

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

Väljundis otsi Index Scan või Index Only Scan (PostgreSQL) / Using index (MySQL). Kui näed Seq Scan (PostgreSQL) või Using where; Using filesort (MySQL), siis indeksit ei kasutata. Võimalikud põhjused: madal selektiivsus, vale indeksi tüüp, vale veergude järjekord liitindeksis või aegunud statistika (ANALYZE table_name;).

Jälgi mõõdikuid regulaarselt: pg_stat_user_indexes.idx_scan PostgreSQL-is, sys.schema_index_statistics MySQL-is. Indeks, millel on kuu aja jooksul null skaneerimist, on eemaldamise kandidaat.

⁉️🤔 Korduma kippuvad küsimused

Mitu indeksit peaks tabelil olema?

Ideaalis 2 kuni 6 indeksit aktiivselt kasutatava tabeli kohta. Vähem kui kaks tähendab peaaegu kindlasti, et mõned päringud on ebaoptimaalsed. Rohkem kui kuus ja peaksid hoolikalt kontrollima, kas kõiki neid tegelikult vaja on: iga lisa-indeks aeglustab kirjutamist. Otsingutabelite (harvad kirjutamised, sagedased lugemised) puhul on rohkem indekseid õigustatud. Suure koormusega operatiivtabelite (intensiivne INSERT/UPDATE) puhul hoia arv miinimumis.

Kuidas on liitindeks parem kui mitu üheveerulist indeksit?

Liitindeks (A, B) on ÜKS struktuur. Server läbib selle ühe korra. Kolm eraldi indeksit (A), (B) ja (C) päringu WHERE A=1 AND B=2 jaoks sunnivad serverit kas valima ühe indeksi (ja filtreerima ülejäänu) või sooritama bitmap-indeksi skaneerimise (bitmappide ühendamine). Liitindeks on peaaegu alati tõhusam, eeldusel, et veergude järjekord vastab sinu päringutele.

Millal indeks pigem kahjustab kui aitab?

Kolm tüüpilist stsenaariumi. Esiteks: tabel on väike (kuni paar tuhat rida) ja täielik tabeli skaneerimine on kiirem kui indeksi lugemine pluss ridade toomine. Teiseks: indeks madala selektiivsusega veerul (is_deleted, kolme väärtusega status). Kolmandaks: mahulised sisestused ETL/importimise ajal, kus indeksid ehitatakse ümber iga partii järel. Eemalda need enne laadimist ja loo pärast uuesti.

Kas peaks indekseerima veerud, mida kasutatakse JOIN tingimustes?

Absoluutselt. Iga JOIN ilma indeksita välistabeli ühendusveerul on pesastatud tsükkel täieliku skaneerimisega. LEFT JOIN orders ON users.id = orders.user_id puhul muudab indeks orders.user_id veerul pesastatud tsükli indeksi otsinguks. Indekseeri alati veerud, mille alusel ühendad.

B-puu või paisindeks: kumba valida täpsete otsingute jaoks?

= korral on paisindeks kiirem: paisktabeli otsing võtab konstantse aja, samas kui B-puu läbib puu logaritmilise arvu sammudega. Kuid paisindeks ei toeta vahemikke, sorteerimist ega UNIQUE piirangut. Praktikas katab B-puu valdava enamuse stsenaariume; paisindeks on nišitööriist punktpäringuteks võtme järgi suure koormusega süsteemides (sessioonid, vahemälud). PostgreSQL-is on paisindeksid olnud tootmiskõlbulikud alates versioonist 10 ja võtavad vähem ruumi kui B-puu.

Kas peaks indekseerima "igaks juhuks"?

Ei. Iga indeks on kompromiss. See kiirendab lugemist aeglasema kirjutamise ja täiendava kettaruumi arvelt. Ära indekseeri "igaks juhuks"; indekseeri konkreetsete päringute jaoks, mis sinu rakenduses tegelikult töötavad. Profileeri aeglased päringud (pg_stat_statements, slow_query_log), lisa indeksid kirurgilise täpsusega ja kontrolli EXPLAIN enne ja pärast.

Peamine järeldus on lihtne: indeksid on tööriist, mitte eesmärk omaette. Üks hästi kujundatud liitindeks võib asendada kolm üheveerulist indeksit ja säästa gigabaite kettaruumi. Ja üks kasutamata indeks kirjutamismahukal tabelil võib aeglustada kogu rakendust.

Kui soovid sügavamale süüvida, alusta ametlikust SQL Serveri indeksi kujundamise juhendist ja PostgreSQL-i dokumentatsioonist indeksitüüpide kohta. Ja kui puutud kokku päringuga, mida indeksid ei paranda, võib probleem peituda andmemudelis endas.