MySQL: Rychlost UPDATE sloupce v závislosti na přítomnosti indexů

Nedávno jsme s Lukášem Churým a Ondrou Vašíčkem řešili rychlost operace UPDATE v závislosti na tom, jestli upravovaný sloupec má či nemá nastavený index.

Představte si, že máme v MySQL 5.0 tabulku o několika sloupcích, dva z nich jsou “id” a “xy”. Nad sloupcem “id” je přidělený index. Sloupec “xy” index nemá. Rychlost UPDATE sloupce “xy” trvá nějaký čas.

Otázka zní, jestli UPDATE zabere stejný čas i v případě, kdyby sloupec “xy” měl přidělený index.

Udělal jsem jednoduchý test, který přináší odpověď.

Tabulka, sloupce, indexy a hodnoty:

  • mám tabulku (úložiště typu MyISAM), která má 10 sloupců
  • jeden sloupec je bez indexů (sloupec “xy”)
  • dva sloupce mají unikátní společný index
  • zbývající sloupce mají po jednom indexu
  • v tabulce je 10 000 záznamů
  • ve sloupci “xy” (nemá index!) jsou stejné hodnoty “1” (INT)

Následně jsem pomocí UPDATE změnil hodnoty sloupce “xy” z 1->0, případně 0->1. Operace vždy změnila všechny řádky tabulky, tedy 10 000 řádků.

UPDATE `test` SET `xy`=0 WHERE `xy`=1

Výsledky měření:

  • Operace UPDATE nad sloupcem “xy”, který nemá index, trvá 0.0540 s
  • Operace UPDATE nad sloupcem “xy”, který má index, trvá 0.0997 s
  • Pokud zruším všechny indexy v tabulce, operace UPDATE trvá 0.0419 s

Závěr:

  • Pokud aktualizuji sloupec bez indexu, je to rychlé.
  • Pokud sloupec má index, operace trvá déle (v tomto případě téměř 2× déle, než u sloupce bez indexu).
  • Pokud v tabulce není žádný index, aktualizace je nejrychlejší.

Test jsem provedl na následující konfiguraci:

Apache/2.2.11 (Win32) PHP/5.2.9-2
Verze MySQL klienta: 5.0.67
Rozšíření PHP: mysql

Pokud se mýlím, opravte mě, prosím.




7 Responses to “MySQL: Rychlost UPDATE sloupce v závislosti na přítomnosti indexů”

  • Hrach Says:

    Pěkné, naštěstí člověk většinou indexy používá tam, kde potřebuje. Takže pro něj je rychlost updatu až podružná věc. :)

  • Jasper Says:

    Indexy zrychlují výběry z databáze, ale úpravu dat zpomalují, protože se musí znova přepočítat index…

  • snouman.net Says:

    Aby to nebyl ukvapený závěr, tohle nic moc nedokazuje, jak byla tabulka velká? A co ta platforma? Fujtajbl. Tohle nemá žádnou informační hodnotu dokud nebudou “normální” podmínky, tedy linuxový nebo unixový systém, adekvátně nakonfigurované daemony atd. Neřikám, že se to bude jasně lišit, ale přeci jenom rychlost aut netestujem na měsící :)

  • Zdeněk Veřeřa Says:

    snouman.net: Tabulka má 10 000 řádků, v článku to je psané. Jedná se o Windows, taktéž v článku uvedeno. Příště napřed raději čtu, pak komentuju ;)

  • Kazan Says:

    Trochu obsahlejsi testy na indexy v mysql jsou napriklad zde: http://weboveaplikace.info/2009/03/06/indexy-v-mysql-prakticke-ukazky/

  • MaReK Olšavský Says:

    To je ale standardní chování databází. Pokud je nad tabulkou více indexů (což je vcelku běžná záležitost, protože používáš výběry podle různých klíčů) a importuje se velké množství dat naráz, používá se běžně postup “zrušit indexy -> import -> znovu vytvořit indexy”.

  • David Kudláček Says:

    Souhlasím s panem Olšanským. Zejména při aktualizaci na shopech je to nutné. Provedený test nemá vypovídací hodnotu, mnohem větší rozdíly by se projevily u tabulky se 100000 řádky a třeba 20 indexovanými sloupci, kde budou třeba i indexy nad více sloupci. Pak může insert jediného řádku trvat zlomek vteřiny, ale update indexů vám zabere a jediném insertu vteřiny (klidně 10-15 vteřin v závislosti na délce klíčů apod.) Importovat pak bez rušení indexů stovky nových položek, je o mrtvici.

Leave a Reply