O indexech v MySQL bylo napsáno už spousta článků. Převážná většina se však zabývá pouze teorií. Rozhodl jsem se tedy připravit pár ukázek, jak indexy ovlivní rychlost provádění dotazů nad daty. Abych se vyhl srovnávání s ostatními články co do teoretické obsáhlosti, zredukuji teorii takřka na nulu. Z naměřených čísel si můžete vyvodit závěry sami, případně si můžete přečíst (na pohled trochu delší) zhodnocení testů na konci článku.

Teoretické minimum

Vytvořit obyčejný index lze několik způsoby. Buďto úpravou stávající tabulky, vytvořením samostatného indexu na tabulce, nebo přímo při vytváření tabulky:

MySQL:
ALTER TABLE tabulka ADD INDEX ( sloupec );
CREATE INDEX nazev_indexu ON tabulka ( sloupec );
CREATE TABLE tabulka ( sloupec datový_typ, KEY nazev_indexu ( sloupec ) );

Odstranit index lze taktéž více způsoby:

MySQL:
ALTER TABLE tabulka DROP INDEX nazev_indexu;
DROP INDEX nazev_indexu ON tabulka ( sloupec );

Dost bylo teorie :) Na řadě jsou slibované ukázky, jak indexy ovlivní rychlost práce nad tabulkami.

Příprava dat

Vytvořil jsem si tabulku test0 o třech sloupcích:

MySQL:
CREATE TABLE test0 (
  id INT(11) NOT NULL AUTO_INCREMENT,
  retezec VARCHAR(10) NOT NULL,
  cislo INT(6) NOT NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

kterou jsem jsem naplnil 4. miliony záznamy, kde atribut 'retezec' obsahuje náhodně vygenerované řetězce délky 10, složené z malých písmen abecedy. Atribut 'cislo' pak obsahuje náhodná čísla od 0 do 999999.
Aby bylo snadné porovnávat časy operací s indexy a bez indexů, zkopíroval jsem si celou tabulku na test1 a test2. Respektive jsem si vytvořil další tabulky se stejnou strukturou jako test0 a překopíroval data:

MySQL:
INSERT INTO test1
SELECT * FROM test0;

V tabulce test1 jsem vytvořil index na sloupci 'cislo' a v tabulce test2 index na sloupci 'cislo' i 'retezec':

MySQL:
ALTER TABLE test1 ADD INDEX icislo (cislo);
ALTER TABLE test2 ADD INDEX icislo (cislo);
ALTER TABLE test2 ADD INDEX iretezec (retezec);

Vše je připraveno pro samotné testování.

Testování

Hlavní důvod proč používat indexy je rychlost vyhledávání. Začneme tedy s příkazem SELECT.

SELECT

I. Pro začátek vyhledáme záznamy dle jedné hodnoty atributu 'cislo'.

MySQL:
mysql> SELECT * FROM test0 WHERE cislo = 735473;
+---------+------------+--------+
| id      | retezec    | cislo  |
+---------+------------+--------+
| 2330500 | ujuojfsinz | 735473 |
| 3504264 | omjmoevlxd | 735473 |
| 3650661 | xggdepckxv | 735473 |
+---------+------------+--------+
3 rows in SET (0.84 sec)

Stejný dotaz provedu nad tabulkou test1 a test2. Kvůli přesnějším výsledkům je dotaz spuštěn 10x a jako výslednou hodnotu beru jejich průměr.

Tabulka Minimum [s] Maximum [s] Průměr [s]
test0 0.828719 0.888683 0.8443537
test1 0.000271 0.000608 0.0005387
test2 0.000522 0.000666 0.000583

II. Nyní zkusíme načíst záznamy podle intervalu hodnot atributu 'cislo'. V případě mých dat jde o 2.445.909 záznamů, což je přibližně 61,2% celé tabulky.

MySQL:
SELECT * FROM test0
WHERE cislo BETWEEN 263467 AND 874684;

Tabulka Minimum [s] Maximum [s] Průměr [s]
test0 2.27370 2.462875 2.341735
test1 2.271746 2.57882 2.345748
test2 2.216138 2.62435 2.295494

III. Zkusíme interval zúžit. Nyní dotaz vybere 396.398 záznamů, tedy zhruba 10% celé tabulky.

MySQL:
SELECT * FROM test0
WHERE cislo BETWEEN 723658 and 822764;

Tabulka Minimum [s] Maximum [s] Průměr [s]
test0 1.037267 1.1911230 1.082266
test1 1.431868 1.685166 1.465738
test2 1.442451 1.491846 1.462038

IV. Zúžíme interval ještě více, na 199.967 záznamů, tedy 5% všech záznamů.

MySQL:
SELECT * FROM test0
WHERE cislo BETWEEN 757838 and 807875;

Tabulka Minimum [s] Maximum [s] Průměr [s]
test0 0.914104 0.958660 0.922324
test1 0.728407 0.808515 0.728407
test2 0.722438 0.954074 0.723137

V. V dalším testu zkusíme výběr podle sloupců 'cislo' i 'retezec'.

MySQL:
mysql> SELECT * FROM test0
    -> WHERE retezec LIKE 'mxpr%' AND
    -> cislo BETWEEN 500000 AND 900000;
+---------+------------+--------+
| id      | retezec    | cislo  |
+---------+------------+--------+
|  285727 | mxprvgpxao | 815039 |
|  529258 | mxprbfndec | 855491 |
| 1136965 | mxprstrlmx | 514873 |
| 2813857 | mxprbpxhww | 851652 |
| 3693942 | mxprvgpxao | 705005 |
+---------+------------+--------+
5 rows in SET (1.03 sec)

Tabulka Minimum [s] Maximum [s] Průměr [s]
test0 0.965487 1.066500 1.013356
test1 0.987207 1.080257 1.041164
test2 0.000810 0.000940 0.000877

VI. Na konec ještě test s agregační funkcí. Pro jednoduchost jen COUNT() pro zjištění počtu různých hodnot atributu 'cislo'. Kvůli omezení výstupu je použita podmínka, že počet hodnot ve skupině musí být větší jak 14.

MySQL:
mysql> SELECT cislo, COUNT(*) as pocet FROM test0
GROUP BY cislo HAVING pocet> 14;
+--------+-------+
| cislo  | pocet |
+--------+-------+
|  46018 |    15 |
| 292161 |    15 |
| 331827 |    15 |
| 407797 |    15 |
| 461206 |    16 |
| 731575 |    15 |
| 734222 |    15 |
| 787101 |    16 |
| 810976 |    15 |
| 847239 |    15 |
| 952733 |    15 |
| 959964 |    15 |
+--------+-------+
12 rows in SET (1 min 20.25 sec)

Tabulka Minimum [s] Maximum [s] Průměr [s]
test0 78.336391 127.023564 101.273832
test1 1.306242 1.410534 1.349165
test2 1.275477 1.354496 1.315935

INSERT

I. Vložení deseti různých záznamů v deseti dotazech.

MySQL:
INSERT INTO test0 (retezec,cislo) VALUES ('lovxgbswrn',807887);
INSERT INTO test0 (retezec,cislo) VALUES ('ugpiecujya',79433);
INSERT INTO test0 (retezec,cislo) VALUES ('znynhczwoe',603331);
INSERT INTO test0 (retezec,cislo) VALUES ('dsiagzlkze',747203);
INSERT INTO test0 (retezec,cislo) VALUES ('vtrmlmtbgl',293059);
INSERT INTO test0 (retezec,cislo) VALUES ('dzevllqzuq',41532);
INSERT INTO test0 (retezec,cislo) VALUES ('twikyivfrj',323563);
INSERT INTO test0 (retezec,cislo) VALUES ('nriqlnkdoi',927033);
INSERT INTO test0 (retezec,cislo) VALUES ('kiwdycjomu',670317);
INSERT INTO test0 (retezec,cislo) VALUES ('vyhojxuvnm',947196);

Tabulka Minimum [s] Maximum [s] Průměr [s]
test0 0.000404 0.000547 0.000443
test1 0.000261 0.093289 0.036356
test2 0.052013 0.094129 0.072772

II. Vložení deseti různých záznamů pomocí jednoho dotazu.

MySQL:
INSERT INTO test0 (retezec,cislo) VALUES
('rdscmskoub', 159076), ('fhueoucyha', 919515),
('dgmjmtvvpl', 416806), ('tobenuttbh', 772246),
('rwfuuflkwf', 376395), ('jbxukrcmju', 625428),
('rvwwjcnkdz', 755552), ('pyheanioit', 344962),
('xdczjngwmc', 289732), ('ocmrfyvhqt', 653739);

Tabulka Doba provádění [s]
test0 0.000681
test1 0.242820
test2 0.293349

UPDATE

Aktualizace dat podle podmínek na dva atributy.

MySQL:
mysql> UPDATE test0 SET cislo = 0
WHERE retezec LIKE 'kjs%' AND cislo> 800000;
Query OK, 44 rows affected (11.24 sec)
Rows matched: 44  Changed: 44  WARNINGS: 0

mysql> UPDATE test1 SET cislo = 0
WHERE retezec LIKE 'kjs%' AND cislo> 800000;
Query OK, 44 rows affected (12.19 sec)
Rows matched: 44  Changed: 44  WARNINGS: 0

mysql> UPDATE test2 SET cislo = 0
WHERE retezec LIKE 'kjs%' AND cislo> 800000;
Query OK, 44 rows affected (0.30 sec)
Rows matched: 44  Changed: 44  WARNINGS: 0

Tabulka Doba provádění [s]
test0 11.247501
test1 12.191598
test2 0.300179

Pokus o update všech záznamů v tabulce s indexy končí mnohaminutovým zpracováváním. Kolik přesně Vám nepovím, neměl jsem ty nervy tak dlouho čekat.

DELETE

Mazání záznamů dle atributu 'cislo'.

MySQL:
mysql> DELETE FROM test0 WHERE cislo = 998051;
Query OK, 4 rows affected (1.45 sec)

mysql> DELETE FROM test0 WHERE cislo = 87364;
Query OK, 3 rows affected (1.10 sec)

mysql> DELETE FROM test0 WHERE cislo = 123456;
Query OK, 3 rows affected (0.95 sec)

Tabulka Číslo 998051 [s] Číslo 87364 [s] Číslo 123456 [s]
test0 1.399951 1.099434 0.951632
test1 0.340928 0.219564 0.228158
test2 0.464200 0.295632 0.346192

Mazání záznamů dle rozmezí hodnot atributu 'cislo'.

MySQL:
mysql> DELETE FROM test0
WHERE cislo BETWEEN 287365 AND 421831;
Query OK, 538448 rows affected (1 min 5.98 sec)

mysql> DELETE FROM test1
WHERE cislo BETWEEN 287365 AND 421831;
Query OK, 538448 rows affected (2 hours 19 min 27.76 sec)

Sami vidíte, že čas provádění nad tabulkou test1 s indexem na atributu 'cislo' je hodně nepraktický. V takovéhle situaci je lepší indexy zrušit, provést požadovanou modifikaci dat a indexy znovu vytvořit.

MySQL:
mysql> ALTER TABLE test2 DROP INDEX icislo;
Query OK, 4000038 rows affected (27.35 sec)
Records: 4000038  Duplicates: 0  WARNINGS: 0

mysql> ALTER TABLE test2 DROP INDEX iretezec;
Query OK, 4000038 rows affected (27.64 sec)
Records: 4000038  Duplicates: 0  WARNINGS: 0

mysql> DELETE FROM test2
WHERE cislo BETWEEN 287365 AND 421831;
Query OK, 538448 rows affected (1 min 22.62 sec)

mysql> ALTER TABLE test2 ADD INDEX icislo (cislo);
Query OK, 3461590 rows affected (33.71 sec)
Records: 3461590  Duplicates: 0  WARNINGS: 0

mysql> ALTER TABLE test2 ADD INDEX iretezec (retezec);
Query OK, 3461590 rows affected (1 min 1.72 sec)
Records: 3461590  Duplicates: 0  WARNINGS: 0

Celkem 3 min 53.04 sec.

Zhodnocení testů

Hned v testu SELECT I. vidíme silnou stránku indexů. Tedy vyhledávání malého počtu záznamů. Zrychlení si lze všimnout také u testu SELECT IV., kdy je příkazem vybráno cca 5% všech záznamů. Pokud je výsledný počet záznamů příliš vysoký (SELECT II.), SŘBD se před samotným vyhledáváním rozhodne index nepoužít. Proto jsou v tomto testu shodné časy nad všemi tabulkami. Je-li počet záznamů odpovídající podmínce tak nešťastný, že je index použit v nepravou chvíli, může dojít dokonce ke zpomalení vyhledávání pomocí indexů, jako se stalo při testu SELECT III.. Obrovské zrychlení nastane ve chvíli, kdy použijeme agregační funkci na sloupec s indexem. V indexovém souboru jsou totiž uloženy hodnoty atributu 'cislo' seřazeně, proto je zjištění jejich počtu znatelně rychlejší, než procházení všech dat (SELECT VI.).
Při vkládání se však již projeví stinná stránka indexů. Tedy zpomalení při všech operacích (záleží na složitosti použitého příkazu a množství ovlivněných dat) krom SELECTU. Indexy je totiž nutno udržovat aktuální a tedy při každé změně dat se upravují. Při testech INSERT I. a II. s přehledem vedou operace nad tabulkou test0. Nad ostatními tabulkami je potřeba udržovat indexy, a čím více jich je, tím se čas protahuje. Lze si také všimnout, že v jistých situacích je vhodnější použít jeden větší dotaz, než více malých.
U testu UPDATE se příkaz vyhodnocuje nejprve podle podmínky retezec LIKE 'kjs%', tedy je použit index na sloupci 'retezec'. Následný výběr dle cislo > 800000 je již prováděn z malého množství záznamu a tak se index na sloupci 'cislo' neuplatní. Proto lze také sledovat zpomalení na tabulce s indexem pouze na sloupci 'cislo'. Po provedení příkazu je totiž třeba index aktualizovat. Oproti tomu na tabulce test2 je vidět urychlení, za které je zodpovědný index na sloupci 'retezec'. Ten napomohl nalezení záznamů, které mají být ovlivněny.
Pokud se pomocí UPDATE či DELETE snažíme ovlivnit velké množství dat, je výsledek nad tabulkami s indexy žalostný, jak lze videt na testu DELETE. V takové situaci může být přijatelným řešením indexy dočasně zrušit, provést modifikaci dat a opět indexy vytvořit. Časová úspora je pak obrovská.

Závěr

Idexy se určitě vyplatí používat, je však třeba dobře zvážit, na které sloupce je použít. Již při návrhu struktury databáze můžeme mít představu jaké operace a s jakým množstvím dat budeme nad tabulkami provádět. Zvolit indexy na základě takové úvahy je jistě lepší, než je bez rozmysli naflákat na půlku tabulky.