Ukázka použití indexů
Školení, která pořádám 
Přestože správný návrh databáze a s tím související vytvoření správných indexů má obvykle klíčový vliv na rychlost aplikace, je tato oblast často buď opomíjena, nebo jí programátoři jednoduše nerozumí. Největší problém bývá s pochopením indexů nad více sloupci, které jsou pro jen trochu složitější dotazy klíčové.
Jednoduchý příklad
Správný návrh indexů bych rád popsal na příkladě. Mějme tabulku clanky (id, nadpis, skupina, publikovano, …) a tabulku diskuse (id, clanek, vytvoreno, …). Správný návrh indexů se odvíjí od toho, jaké dotazy budeme v aplikaci nejčastěji používat, začneme s něčím jednoduchým:
Co nám řekne příkaz EXPLAIN třeba na dotaz SELECT nadpis FROM clanky WHERE skupina = 1 ORDER BY publikovano DESC bez jakýchkoliv indexů (kromě primárního klíče)?
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | clanky | ALL | NULL | NULL | NULL | NULL | 231 | Using where; Using filesort |
Hrůza. Sloupec type s hodnotou ALL znamená, že se bude procházet celá tabulka, hodnota NULL ve sloupci key říká, že se nepoužije žádný index a konečně Using filesort ve sloupci Extra znamená, že MySQL bude muset výsledek ručně setřídit. Pro zajímavost, jak rychlý bude tento dotaz?
DO BENCHMARK(10000, (SELECT MAX(publikovano) FROM clanky WHERE skupina = RAND())) (19.05 sec)
Co se stane po přidání indexu nad sloupec publikovano, podle kterého se řadí?
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | clanky | ALL | NULL | NULL | NULL | NULL | 231 | Using where; Using filesort |
Nic. Podle sloupce se sice řadí, ale až po vybrání řádků omezených podmínkou WHERE. Přidáme tedy index nad sloupec skupina:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | clanky | ref | skupina | skupina | 4 | const | 29 | Using where; Using filesort |
Dosáhli jsme významného zlepšení. Tabulka už se neprochází celá (type se změnil na ref a řádků už se neprochází 231, ale 29), její dotřídění musí ale MySQL pořád provést ručně. To vyřešíme změnou indexu na (skupina, publikovano DESC) (DESC MySQL v současné době ignoruje, ale do budoucna by ho mohlo použít pro optimalizaci indexu pro sestupné řazení):
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | clanky | ref | skupina | skupina | 4 | const | 27 | Using where |
Takhle by to mělo vypadat.
DO BENCHMARK(10000, (SELECT MAX(publikovano) FROM clanky WHERE skupina = RAND())) (1.81 sec)
Více tabulek
Co pokud bychom chtěli vypsat ke všem článkům ve skupině také datum posledního diskusního příspěvku? Protože se z diskusí bude vybírat podle článku a řadit v nich podle data, navrhneme pro ně už dopředu index (clanek, vytvoreno).
EXPLAIN SELECT clanky.nadpis, MAX(diskuse.vytvoreno) FROM clanky LEFT JOIN diskuse ON clanky.id = diskuse.clanek WHERE clanky.skupina = 1 GROUP BY clanky.id ORDER BY clanky.publikovano DESC
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | clanky | ref | skupina | skupina | 4 | const | 38 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | diskuse | ref | clanek | clanek | 4 | clanky.id | 4 | Using index |
Na první pokus zase nic zázračného. Indexy se sice použijí, u tabulky diskuse se dokonce ani nemusí sahat do datového souboru, protože datum je uloženo rovnou v indexu (informuje nás o tom Using index), mezivýsledek se ale musí uložit do pomocné tabulky (Using temporary) a tam ručně dotřídit. Do pomocné tabulky se mezivýsledek ukládá proto, že dotaz řadíme podle jiného sloupce, než podle kterého se řádky seskupují. Pokud index změníme na (skupina, publikovano DESC, id DESC), můžeme dotaz upravit:
EXPLAIN SELECT clanky.nadpis, MAX(diskuse.vytvoreno) FROM clanky LEFT JOIN diskuse ON clanky.id = diskuse.clanek WHERE clanky.skupina = 1 GROUP BY clanky.publikovano DESC, clanky.id DESC
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | clanky | ref | skupina | skupina | 4 | const | 29 | Using where |
| 1 | SIMPLE | diskuse | ref | clanek | clanek | 4 | clanky.id | 4 | Using index |
Výsledek je perfektní. Využívá se při tom vlastnosti MySQL, že podle sloupců využitých k seskupení záznamů se výsledek rovnou i seřadí. Sloupec id by ostatně měl být použit i u obyčejného výpisu pro zajištění konzistentního řazení článků se stejným datem publikace (sloupec id musí být setříděn ve stejném pořadí jako publikovano – pokud bychom použili publikovano DESC, id, tak se index (publikovano DESC, id) nepoužije, protože MySQL ho vytvoří jako (publikovano, id) a s rozdílem v řazení si neporadí). Pokud bychom v dotazu sloupec id nepotřebovali (třeba pro MAX(publikovano)), tak to nevadí, protože pokud je index delší, než je potřeba, tak MySQL použije pouze jeho začátek.
Jako poslední příklad si ukážeme, jak by se vyhodnotil poddotaz SELECT nadpis, (SELECT MAX(vytvoreno) FROM diskuse WHERE clanek = clanky.id) FROM clanky ORDER BY publikovano, který plní stejnou funkci:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | clanky | ref | skupina | skupina | 4 | const | 38 | Using where |
| 2 | DEPENDENT SUBQUERY | diskuse | ref | clanek | clanek | 4 | clanky.id | 4 | Using index |
Zápis je jednodušší, pro vyhodnocení dotazu navíc nepotřebujeme v indexu sloupec id ani žádné triky. Vyhodnocení poddotazu je nicméně i tak o něco pomalejší.
Závěr
Použití indexů je důležité zejména u dotazů nad velkými tabulkami. Pokud je dotaz s využitím indexů na tisíciřádkové tabulce třeba 1000/10-krát rychlejší, na tabulce s miliónem záznamů už to může být 1000000/20-krát.
Je potřeba říci, že indexy mají i svou odvrácenou stranu – zpomalují aktualizace. Proto bychom s nimi neměli plýtvat, nicméně pokud je pro naši aplikaci důležitější rychlost čtení, než rychlost zápisu, měli bychom indexy navrhnout správně pro všechny dotazy, které používáme. Na indexech je pěkné to, že jakmile je jednou správně navrhneme, nemusíme se o jejich použití už starat – databázový server správný index vybere za nás.
Přijďte si o tomto tématu popovídat na školení Konfigurace a výkonnost MySQL.
Diskuse
Hds:
Díky moc za tenhle článek, právě se v indexech trochu topím a tohle mi pomohlo.Vlasta Neubauer:
super, díky moc za článekkozoslon:
Výborný článek, btw. odkazuje na něj i česká wikipedie u hesla Index(databáze)Lukáš:
Díky za další kvalitní článek. Zajímalo by mne, proč mi to u některých SQL dotazů ve sloupci „Extra“ nepíše vůbec nic? Je to dobře, nebo špatně?johno:
Dobre.
finc:
Důležité je také vědět, že vytvoření příliš mnoho indexů v tabulce přináší zpomaléní a nikoli zrychlení. Tvorba indexů je spíše záležitost zkušenností.Existuje ještě jedna vlastnost, při které indexy hrají důležitou roli a to jsou referenční integrity. Pokud použiji typ InnoDB a v ní existuje foreign key, automaticky je také índexem. Stejně jako primární klíč.
Co se týče optimalizace, občas mi nezbývá jiná možnost, než tabulky předpočítat. Tzn., že pro hlavní výpis potřebuji grupované data, která ve výsledku jsou např. o 2/3 menší. Pro detail pak použiji již použiji původní. Ale to už se bavím o tabulkách s miliony záznmy a dotazy spojující např. 10-20 tabulek dohromady :)
Jinak, zde bych vyzdvyhnul Oracle a to zejména novější verze, které mají jakýsi optimalizér, který dokáže i z neindexované tabulky vyhodnotit dotaz mnohem rychleji a automaticky si databázi zindexovat.
MySQL je odkázána pouze na um vývojáře :) Ono nezoptimalizovaná (myslím bez indexů) databáze se nejvíce projevuje práve u MySQL.
Ale i přesto, mám v MySQL asi 300 a stále počet roste, stejně tak i počet vět v jednotlívých tabulkách a přesto se MySQL chová "slušně". Proto mýty o neschopnosti použít MySQL na větší projekty je holý nesmysl :) Samozřejmě se bavím o MySQL 5.
Jan Tichý:
Vezměme už třeba i jen ten jednoduchý dotazSELECT nadpis FROM clanky WHERE skupina = 1 ORDER BY publikovano DESC;
To, co je napsáno v článku, je jenom polovina celé pravdy :). Předpokládejme, že budu mít index typu
CREATE INDEX clanky_idx ON clanky (skupina, publikovano);
Ačkoliv vypadá velice rozumně a inteligentně, jsou docela časté případy, kdy jej databázový stroj vůbec nepoužije a sáhne raději po sekvenčním průchodu. Typicky je to v případě, kdy statistické rozdělení hodnot ve sloupci skupina je dost nevyrovnané. Například pokud 95 procent všech článků bude jen v jedné skupině. Řešením je například udělat z toho dva parciální indexy omezené klausulí WHERE, jeden pro danou velkou skupinu a ostatní pro zbytek.
Jan Tichý:
Co jsem tím chtěl říci, je to, že i když na první pohled jsou indexy navrženy správně, v praxi se může ukázat, že pravda je někde úplně jinde. Pokud mi tedy třeba vyjde hezký index scan na dotazEXPLAIN SELECT nadpis FROM clanky WHERE skupina = 1;
tak to vůbec nemusí znamenat, že to bude stejně fungovat i pro dotaz
EXPLAIN SELECT nadpis FROM clanky WHERE skupina = 2;
Co je na tom daleko zrádnější, chování se mění právě podle charakteru vložených dat v průběhu života aplikace. To, že stroj používal můj index včera, vůbec neznamená, že ho bude používat zítra, a to i bez toho, že bych jakkoliv měnil strukturu databáze nebo kód aplikace. Používání či nepoužívání indexů se mění i jen se změnou samotných dat v databázi. Nestačí tedy indexy vytvořit jednorázově na začátku, ale je třeba je průběžně přehodnocovat a podle potřeby případně předefinovávat.
Jakub Vrána 
:
Ty, kdo neznají pojem "parciální index" (stejně jako jsem ho neznal já), odkazuji na dokumentaci PostgreSQL: http://www.postgresql.org/docs/8.2/interactive/sql-createindex.html. Syntaxe je:
CREATE INDEX idx ON clanky (publikovano) WHERE skupina = 1
Nicméně nesdílím názor, že by byl při dobré implementaci indexů zásadně výhodnější než index (skupina, publikovano).
Jakub Vrána : Pavel Stehule:
S krizkem po funuse. Az na male vyjimky se v PostgreSQL obejdete bez slozenych indexu. Jednoduche indexy se pouziji i tam, kde se drive museli pouzit slozene indexy (napr. OR, atd). A jednoduchy index ma 100% mensi selektivitu nez slozeny, tudiz si vypomuzeme podminenym indexem. Stale je tu vyhoda vyssi univerzalnosti.Radim Smička:
Zajímalo by mě jak řešit index u OR dotazů. Tedy napřSELECT * FROM uzivatel WHERE jmeno='Petr' or prijmeni='Novak' . Pomohlo by možná udělat dva indexy, tedy jmeno a prijmeni a ten dotaz přeformulovat přes UNION. Ale radši se přeptám tady zkušenějších jak na to ;-).
Jakub Vrána :
Ano, tohle se dá řešit přes UNION. Ale pokud tabulka není příliš velká, tak bych to neřešil, znepřehlednění kódu za zrychlení nestojí.
Jakub Vrána : Radim Smička:
Teď jsem zkoušel ten or SELECT na lokalním počítači s MYsql 5.0 a definovanými dvěma indexy a Mysql si samo udělalo podle EXPLAIN UNION, takže evidentně časem nebude potřeba řešit. Zajímavé, že v online dokumentaci k této verzi jsem o tom nenašel ani řádku :-(.Měl jsem s tím celkem problémy, na tabulce o 100 000 zaznamech mi to při testech házelo v průměru kolem 0.2 sekundy, ale pak jsem to testoval na serveru při větší zátěži (různorodé dotazy) a klidně to vyhodilo čas i 20 sekund.
Jinak moc díky za článek, dost mi pomohl.
Michal:
V tom pripade je dobre si zapnout "slow query log" a cas od casu zkouknout ktere query trvaji dlouho (t.j. dele nez nastaveny pocet sekund) a zoptimalizovat je.Taky je mozne zapnout volbu aby do slow query logu byly reportovany i query ktere nepouzivaji index - i to se muze hodit. Ale tam uz muze byt velke mnozstvi false positives - treba kdyz je v nejake tabulce jen pet deset zaznamu tak se mysql skoro pokazde rozhodne ji projit na ferovku jeden po druhem a zadnymi indexy se zabyvat nebude. Takze spravne interpretovani slow query logu chce trochu zkusenosti.
Pavel Stehule:
Pri provozu dochazi k zamykani. Zalezi na typu tabulek. U MyISAM dochazi k zamykani celych tabulek, tudiz vetsinu casu se dotaz nezpracovaval, ale cekal na uvolneni zamku.Ivan Krištof:
Priznám sa, s MySQL 5.0 nepracujem, ale odporúčam okrem indexov popremýšlať aj nad úrovňou uzamykania záznamov pri konkurentnom prístupe.Pracujem s DB MS SQL a bežne pri dátach, ktorých absolútna aktuálnosť nehrá klúčovú rolu, používam "špinavé čítanie" - READ UNCOMMITED. Tieto čítania následne nebrzdia prípadne vkladanie nových riadkov do tabuľky.
Čo sa týka indexovania, nemalú rolu, ako správne poznamenal mr Tichý, hrá nielen kvantita, ale aj kvalita dát (jej zmena v čase) - prehodnotenie FILL FACTORu a štruktúry indexov, reindexacia v časových intervaloch - netrvdil by som, že o indexy sa netreba starať.
Ako sa hovorí, na každé query je ideálne index zindexovanie. Treba najsť kompromis (profiler na SQL dotazy pomôže).
martinpav:
Trosku viac o optimalizacii a chovani mysql: http://www.mysqlperformanceblog.com/Jirka:
Precetl jsem si clanek. Je dobry, ale nejak moc nechapu indexy na vice tabulkach :(Mam dve tabulky
klub (id - int, jmeno - varchar)
oblibene (id - int, user - id, category - tinyint)
Nasledujici dotaz se mi stale nedari optimalizovat, neustale to u klubu hlasi "using filesort"
SELECT klub.ID, klub.jmeno
FROM klub, oblibene
WHERE klub.ID = oblibene.ID AND
oblibene.user = 1 AND
oblibene.category = 1
ORDER BY klub.jmeno
Jak byste navrhli pro tyto tabulky indexy? Doufam, ze to pak z toho pochopim. Predem dekuji za vasi pomoc.
Jakub Vrána :
Problém je v tom, že MySQL buď může v tabulce oblibene použít index (user, category) a v tabulce klub (id), nebo v tabulce klub index (jmeno), ale nikdy ne oba. Ve většině případů si vybere první způsob a dotřídění musí provést ručně.
Jakub Vrána : Arnošt:
Nevíte jak se mohu zbavit ručního třídění na tabulceid int -- PRIMARY
nazev varchar 255 -- UNIQUE
3 textove sloupce bez indexu
...
SELECT nazev, textove sloupce FROM t ORDER BY nazev
Pise mi using filesort... ale vzdyt tam mam unique index
martin:
Ahoj Jakube, mám se ještě co učit a nejsem na tom tak dobře jako ty (a kdo ano), potřeboval bych pomoc s následujícím dotazem. Z článku vyplývá, že je nejlepší, pokud data databáze sama netřídí a neukládá do tempu (Using temporary).Nevím jakým způsobem bych měl t třídění odstranit, jestli to je skutečně nutné a pomohlo by to, případně jak bys upravil dotaz a volil indexy ty? Rád se přiučím. Tady je:
---
SELECT u.id, u.nick, u.barva, UNIX_TIMESTAMP(u.datum_registrace) AS datum_registrace,
z.text, z.datum, f.soubor AS fotka FROM uzivatele u LEFT JOIN zpravy z ON (z.uzivatel_id = u.id) LEFT JOIN fotky f ON (f.id = u.fotka_id) WHERE z.text IS NOT NULL ORDER BY z.datum DESC
---
U takových typů dotazů nikdy nevím jaký index je nejvhodnější. Mám indexy v tabulce zpravy na: "uzivatel_id", dalsi index na "datum", jenže to je asi špatně... Jsem v těch indexech ztracený, prosím o pomoc.
Díky
Cirda:
Dobrý den,jak zoptimalizovat tabulku tak aby prohledávala
nějak rozumně když v table i v table2 jsou tisíce záznamů. na všechny uvedené sloupce jsou nasazeny indexy, ale
když dám explain tak to ukáže něco takového.
Potřeboval bych aby to ve sloupci S neprohledávalo všechny záznamy
Zkoušel jsem nasadit idexy všelijak ale vždy to vychází mizerně.
Zkoušel jsem i použít klauzuli IN že bylo S.id_coteho IN (12,13,14,15,16,17,...)
Problém asi bude právě v tom že se tam prohledává to S.id_coteho s or
Nevíte jak to lépe zoptimalizovat?
Děkuji
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE S index id_ceho,id_coteho,idceho.idkat idceho.id_coteho 8 NULL 5796 Using where; Using index
1 SIMPLE Z eq_ref PRIMARY,aktivni PRIMARY 4 S.id_ceho 1 Using where
a sql dotaz
explain SELECT count( * )
FROM table AS Z
JOIN table2 AS S ON S.id_ceho = Z.id_neceho
WHERE (
S.id_coteho =12
OR S.id_coteho =13
OR S.id_coteho =14
OR S.id_coteho =15
OR S.id_coteho =16
OR S.id_coteho =17
OR S.id_coteho =18
OR S.id_coteho =19
OR S.id_coteho =20
OR S.id_coteho =21
OR S.id_coteho =22
OR S.id_coteho =97
OR S.id_coteho =159
OR S.id_coteho =178
)
AND Z.aktivni =1
Jakub Vrána :
Ten EXPLAIN není špatný, obzvlášť pokud je aktivních záznamů většina. Podle EXPLAINu to vypadá, že by se všechny záznamy v tabulce S prohledávat neměly. Já bych místo OR použil IN, ale to by vliv mít nemělo.
Jedinou další možnou optimalizaci vidím v denormalizaci – zkopírování sloupce Z.aktivni i do tabulky S. To se dá dělat automaticky pomocí triggerů. Nebo pokud se ten počet má zjistit super rychle, tak si ho někde udržovat rovnou při zápisu do S nebo Z také pomocí triggerů.
Jakub Vrána : Vladimír Štofaník:
Riešim problém, či v databáze mať 700 tabuliek o max. počte 15tis. záznamov alebo spraviť jednu tabuľku s počtom cca 3mil. záznamov. Ide o to, že sa údaje budú často aktualizovať a jednak každá tabuľka MyISAM má 3 samostatné súbory, čo predstavuje 2100 súborov na disku. Prebudovanie indexov pri väčšej tabuľke trvá viac, ale vzhľadom na to, že aktualizácia prebieha v nočných hodinách za minimálnej prevádzky až tak to nevadí. Záleží mi na rýchlych dotazoch pretože viac som na diskusných fórach čítal myšlienku, že radšej rozdeľovať veľké tabuľky a JOINovať ako ich celé prehľadávať (aj keď cez indexy). Jakub Vrána :
Já bych rozhodně udělal jednu velkou tabulku. V novějších verzích lze použít partitioning: http://dev.mysql.com/doc/mysql/en/partitioning.html.
Jakub Vrána : Martin Šramka:
Dobrý den, chtěl bych se zeptat, když mám DB asi o 30-ti tabulkách. Může mi zaindexování jednotlivých sloupců nějakým způsobem ovlivnit SQL dotazy? Myšleno tak, že když pošlu na DB např. "select" tak mi nezaindexovaná DB dá jiný výsledek než zaindexovaná?Děkuji za reakce.
Jakub Vrána :
To by se stát nemělo.
Jakub Vrána : 
v6ak:
Mám pocit, že bez ORDER BY tu může být rozdíl v pořadí (a rozdíly z toho vyplývající), ale to je snad vše.
v6ak: Jakub Vrána :
Ano, to je pravda.
Jakub Vrána : Martin Šramka:
Ok, děkuji moc za odpovědi.Michal:
Ahoj,díky za zajímavý článek. Ohledně toho prvního dotazu. Co v případě, že mám více AND a řadí se to podle 2 sloupců? Např.:
SELECT * FROM tabulka WHERE recommended AND show ORDER BY datetime DESC, id DESC. Nějak nemohu pochopit jak mám nastavit indexy
pro tento dotaz.
Jakub Vrána :
Na to se hodí index (recommended, show, datetime, id). Ale dotaz asi bude lepší zapsat pomocí recommended = 1 AND show = 1.
Jakub Vrána : Dominik:
Dobrý deň, chcel by som sa opýtať: dajme tomu, že mám napr. jednu veľkú tabuľku, v ktorej sa nachádzajú informácie o produktoch v eshope (v podstate je to view) - je tam veľmi veľa stĺpcov, pričom vo väčšine dotazov je WHERE na nejaký stĺpec (dám teda na neho index) a zároveň sa zoraďuje podľa druhého, napr. podľa predajnosti (int - koľko ks produktu už bolo predaných). Keď dám tie 2 indexy samostatne, tak aj sa keď použije index pri WHERE, tak stále používa filesort na zotriedenie v ORDER BY. Podľa článku teda ak vytvorím zložený index napr. (hmotnost, predajnost), tak to už je dobré, moja otázka ale znie: ak mám takých kombinácii čo sa veľmi často vyskytujú veľa (hmotnosť/predajnosť, názov/predajnosť, šírka/priorita, a dajme tomu 10+ ďalších), vytvoriť teda ten zložený index na každú častú kombináciu, ktorá v aplikácii nastáva? Nebude to potom už serveru skôr vadiť, resp. nestratia indexy už svoj význam? Keď DBMS zvolí správny index tak to musí byť asi teda veľmi rýchle a užitočné, len či pri veľkom množstve takýchto indexov, to ešte DBMS nerobí skôr problémy sa vyznať medzi nimi. Samozrejme rátam s tým, že manipulácia s tabuľkou (insert, update) bude pre DBMS oveľa ťažkopádnejšia - čo pri napr. hromadnom UPDATE napr. 100000 riadkov sa už môže postarať o výraznejší problém. Zaujíma ma, ako sa v takýchto prípadoch zvykne postupovať v reálnej praxi, či je teda dobré vytvárať ozaj toľko indexov koľko treba, alebo to s nimi nepreháňať. Jakub Vrána :
Já vytvářím indexy všude tam, kde je aplikace využije. Kromě pomalejší modifikace záznamů a více místa zabraného na disku to ničemu nevadí.
Jakub Vrána : Diskuse je zrušena z důvodu spamu.
© 2005-2024 