LINUX SOFTWARE

Jak je to v MySQL s primárními klíči? A jak rychlé či pomalé je pracovat s tabulkami, na nichž je nějaký ten index? O tom je řeč v dnešním díle seriálu.

5.8.2005 07:00 | Petr Zajíc | přečteno 28137×

V dnešním díle dokončíme rozbor toho, co pro MySQL znamenají indexy a podíváme se na slíbené testy rychlosti. Uvidíte, že použití indexů v praxi může výrazně zrychlit některé operace, jiné však může naopak výrazně zpomalit.

Primární klíče

Z indexů nám zbývá podívat se na primární klíče. Připomenu z definice, že primární klíč je jedinečný klíč na sloupci, jež nesmí obsahovat hodnoty null a jež smí být v každé tabulce maximálně jeden. V praxi se téměř vždy k vytvoření primárního klíče používá sloupec s automatickým číslováním, protože tam jsou neprázdné hodnoty zajištěny již z principu. Takové pole vytvoříte pomocí příkazu ve stylu:

CREATE TABLE cislovani
(id INT AUTO_INCREMENT, PRIMARY KEY (id));

Tato technika již byla v seriálu rozebrána, a to v díle Tipy ke tvrobě tabulek. Je ale třeba si uvědomit, že zatímco pole typu AUTO_INCREMENT musí být v MySQL definováno jako primární klíč, primárním klíčem NEMUSÍ být vždy automaticky číslované pole. Jestliže jste schopni zajistit unikátnost ve sloupci jinými prostředky než pomocí automatického číslování, můžete to udělat.

Pozn.: Praxe je ovšem taková, že sloupce zahrnuté v primárním klíči vetšinou pouze odlišují řádky, a nenesou žádnou jinou informaci, která se váže k datům. Proto většinou kombinace primárního klíče a automaticky číslovaného pole bohatě postačí a není třeba vymýšlet nic jiného.

Rychlost operací s indexy

Abyste měli alespoň nějakou představu o tom, jak indexy zrychlují nebo zpomalují určité databázové operace, a aby seriál nebyl jen samá teorie, rozhodl jsem se udělat takový primitivní rychlostní test. Test spočívá v tom, že jsem si připravil soubor s určitým kvantem informací a ten se pokusím dostat do databáze. Budu přitom měřit, jak dlouho bude taková operace trvat - a na základě toho uvidíte, jak a co vlastě všechno indexy ovlivňují.

Aby byl test jednoduchý, připravil jsem si tabulku jen s jedním jediným sloupcem - bude do něj ukládáno celé číslo. Pokud si něco takového chcete vyzkoušet, zadejte příkaz ve smyslu:

create table cisla
(cislo int);

Následně jsem si připravil soubor s náhodnými čísly. Abych co nejméně ovlivnil výsledky měření, nepoužil jsem k tomu MySQL, ale nechal jsem si vygenerovat soubor obsahující jeden milión náhodných čísel pomocí kraťoučkého programu v jazyce Java

import
java.io.FileOutputStream;


import java.io.IOException;


import java.io.PrintStream;


import java.util.Random;


public class Rand {


    public static void main(String[] args) {


        Random generator = new
Random();


        StringBuffer buffer=new
StringBuffer();


        FileOutputStream fout;


        String separator =
System.getProperty("line.separator");


        int i;


        for
(i=0;i<1000000;i++){


            buffer.append(generator.nextInt());


            buffer.append(separator);


        }


        try


        {


            fout
= new FileOutputStream ("cisla");


            new
PrintStream(fout).print(buffer.toString());


            fout.close();       


        }


        catch (IOException e){}


    }


}

Pozn.: K tvorbě souboru s náhodnými čísly můžete samozřejmě použít jakýkoli programovací jazyk, který zvládá použití generátoru náhodných čísel a zápis do souboru. Původně jsem plánoval, že bych pro testovací účely svůj soubor s daty zveřejnil, ale byl by moc veliký. I přesto, že jsem jej zkoušel komprimovat.

Hromadné vkládání záznamů

MySQL obsahuje příkaz, pomocí něhož lze nahrát data z textového souboru do databáze. Protože jsme jej ještě v seriálu nerozebírali, zmíním se jen letmo o tom, že

load data infile
'./cisla' into table cisla;

nahraje obsah souboru cisla do naší právě vytvořené tabulky. To za předpokladu, že soubor je umístěn v adresáři, v němž má MySQL data pro právě používanou databázi a že jsme právě přepnuti v databázi, jež obsahuje tabulku cisla. Tento příkaz jsem změřil, výsledky jsou v tabulce níže. Postupoval jsem přitom tak, že jsem vytvořil pět souborů s náhodnými daty a vkládal je postupně jeden za druhým. Jestliže tabulku cisla vyprázdníme a k poli cislo přidáme index,

truncate table cisla;


alter table cisla add index (cislo);

a zkusíme příkaz pro vložení záznamů opakovat, zjistíme, že trvá déle, mnohem déle. Opět jsem celou akci opakoval pětkrát s tím, že jsem vložil první až pátou sadu s milionem náhodných čísel. Výsledky (které opět máte v tabulce) poukazují na fakt, že přebudování indexů pochopitelně něco stojí.

Co kdybychom ale šli na celou věci jinak? Co kdybychom

Existující index zrušili,
Hromadně vložili data,
A následně znovu přidali k tabulce index?

Vložení dat v takovém případě bude trvat v podstatě konstatní dobu. Jak ale vidíme, zrušení a znovuvytvoření indexu je až řádově rychlejší než jeho udržování během operace hromadného vkládání záznamů - a ten rozdíl je tím markantnější, čím více dat je v podkladové tabulce.

	Vložení (bez indexu)	Vložení (s indexem)	Vložení s přebudováním indexu
	Vložení (bez indexu)	Vložení (s indexem)	DROP INDEX	LOAD DATA	ADD INDEX	CELKEM
První milion záznamů	0,64 s	2,84 s	0,01 s	0,64 s	2,52 s	3,17 s
Další milion (celkem 2 mil.)	0,64 s	12,41 s	0,30 s	0,64 s	5,16 s	6,10 s
Další milion (celkem 3 mil.)	0,64 s	25,64 s	0,53 s	0,64 s	8,30 s	9,47 s
Další milion (celkem 4 mil.)	0,66 s	125,23 s	0,78 s	0,64 s	10,66 s	12,08 s
Další milion (celkem 5 mil.)	0,64 s	261,77 s	1,05 s	0,64 s	13,52 s	15,21 s

Výběr záznamů

Když už máme tak pěknou sadu dat, můžeme si ještě ukázat, jak indexy plní svůj původní význam - tedy dohledávat data podle jejich hodnoty. Pokud máte na tabulce index, zrušte jej a zkuste si spustit následující dotaz:

select * from cisla
where cislo > 2000000000;

Na mém stroji tento dotaz trvá asi 1,45 s a to proto, že databáze musí tuto tabulku projít (data v tabulce totiž nejsou nikterak seřazena - vždyť jsou to náhodná čísla). Stejný dotaz s použitím indexu však trvá pouhých 0,85 s. protože databáze díky indexu ví, kde má hodnoty větší než dvě miliardy ve výsledné sadě záznamů hledat.

Závěr

Tyto rychlostní testy samozřejmě je třeba brát s jistou rezervou. Především proto, že neodrážejí skutečné operace, s nimiž se budete při práci s databází setkávat v praxi. Dále proto, že jsme měřili pouze rychlost provedení dotazu, a ne již takové věci, jako je zatížení procesoru, spotřeba paměti a tak dále. Konečně, měření probíhalo na mé pracovní stanici, kde bylo zároveň spuštěno několik dalších programů, takže výsledky budou zcela jistě nepřesné.

Nicméně, mohli jste si udělat představu o tom, jak indexy urychlují (či zpomalují) jednotlivé databázové operace.

MySQL (32) - ještě k indexům

Primární klíče

Rychlost operací s indexy

Hromadné vkládání záznamů

Výběr záznamů

Závěr