LINUXSOFT.cz Přeskoč levou lištu
Uživatel: Heslo:  
   CZUKPL

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

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 | Články autora | přečteno 25024×

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

  1. Existující index zrušili,
  2. Hromadně vložili data,
  3. 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
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.

Verze pro tisk

pridej.cz

 

DISKUZE

Super 5.8.2005 07:43 MaReK Olšavský
L Re: Super 5.8.2005 08:09 Petr Zajíc
žlutá ikonka klíče 25.2.2016 11:52 Kvido Vícha




Příspívat do diskuze mohou pouze registrovaní uživatelé.
> Vyhledávání software
> Vyhledávání článků

14.11.2017 16:56 /František Kučera
Máš rád svobodný software a hardware nebo se o nich chceš něco dozvědět? Zajímá tě DIY, CNC, SDR nebo morseovka? Přijď na sraz spolku OpenAlt – tradičně první čtvrtek před třetím pátkem v měsíci: 16. listopadu od 18:00 v Radegastovně Perón (Stroupežnického 20, Praha 5).
Přidat komentář

12.11.2017 11:06 /Redakce Linuxsoft.cz
PR: 4. ročník odborné IT konference na téma Datová centra pro business proběhne již ve čtvrtek 23. listopadu 2017 v konferenčním centru Vavruška, v paláci Charitas, Karlovo náměstí 5, Praha 2 (u metra Karlovo náměstí) od 9:00. Konference o návrhu, budování, správě a efektivním využívání datových center nabídne odpovědi na aktuální a často řešené otázky, např Jaké jsou aktuální trendy v oblasti datových center a jak je využít pro vlastní prospěch? Jak zajistit pro firmu či jinou organizaci odpovídající služby datových center? Podle jakých kritérií vybrat dodavatele služeb? Jak volit součásti infrastruktury při budování či rozšiřování vlastního datového centra? Jak efektivně spravovat datové centrum? Jak eliminovat možná rizika? apod.
Přidat komentář

13.9.2017 8:00 /František Kučera
Máš rád svobodný software a hardware nebo se o nich chceš něco dozvědět? Zajímá tě DIY, CNC, SDR nebo morseovka? Přijď na sraz spolku OpenAlt – tentokrát netradičně v pondělí: 18. září od 18:00 v Radegastovně Perón (Stroupežnického 20, Praha 5).
Přidat komentář

3.9.2017 20:45 /Redakce Linuxsoft.cz
PR: Dne 21. září 2017 proběhne v Praze konference "Mobilní řešení pro business". Hlavní tématy konference budou: nejnovější trendy v oblasti mobilních řešení pro firmy, efektivní využití mobilních zařízení, bezpečnostní rizika a řešení pro jejich omezení, správa mobilních zařízení ve firmách a další.
Přidat komentář

15.5.2017 23:50 /František Kučera
Máš rád svobodný software a hardware nebo se o nich chceš něco dozvědět? Zajímá tě DIY, CNC, SDR nebo morseovka? Přijď na sraz spolku OpenAlt, který se bude konat ve čtvrtek 18. května od 18:00 v Radegastovně Perón (Stroupežnického 20, Praha 5).
Přidat komentář

12.5.2017 16:42 /Honza Javorek
PyCon CZ, česká konference o programovacím jazyce Python, se po dvou úspěšných ročnících v Brně bude letos konat v Praze, a to 8. až 10. června. Na konferenci letos zavítá např. i Armin Ronacher, známý především jako autor frameworku Flask, šablon Jinja2/Twig, a dalších projektů. Těšit se můžete na přednášky o datové analytice, tvorbě webu, testování, tvorbě API, učení a mentorování programování, přednášky o rozvoji komunity, o použití Pythonu ve vědě nebo k ovládání nejrůznějších zařízení (MicroPython). Na vlastní prsty si můžete na workshopech vyzkoušet postavit Pythonem ovládaného robota, naučit se učit šestileté děti programovat, efektivně testovat nebo si v Pythonu pohrát s kartografickým materiálem. Kupujte lístky, dokud jsou.
Přidat komentář

2.5.2017 9:20 /Eva Rázgová
Putovní konference československé Drupal komunity "DrupalCamp Československo" se tentokrát koná 27. 5.2017 na VUT FIT v Brně. Můžete načerpat a vyměnit si zkušenosti z oblasti Drupalu 7 a 8, UX, SEO, managementu týmového vývoje, využití Dockeru pro Drupal a dalších. Vítáni jsou nováčci i experti. Akci pořádají Slovenská Drupal Asociácia a česká Asociace pro Drupal. Registrace na webu .
Přidat komentář

1.5.2017 20:31 /Pavel `Goldenfish' Kysilka
PR: 25.5.2017 proběhne v Praze konference na téma Firemní informační systémy. Hlavními tématy jsou: Informační systémy s vlastní inteligencí, efektivní práce s dokumenty, mobilní přístup k datům nebo využívání cloudu.
Přidat komentář

   Více ...   Přidat zprávičku

> Poslední diskuze

5.12.2017 11:50 / Thomas
kitchen renovations

18.9.2017 14:37 / Rojas
high security vault

15.9.2017 7:33 / Wilson
new zealand childcare jobs

31.8.2017 12:11 / Jaromir Obr
Re: ukůládání dat ze souboru

30.7.2017 11:12 / Jaromir Obr
Národní znaky

Více ...

ISSN 1801-3805 | Provozovatel: Pavel Kysilka, IČ: 72868490 (2003-2017) | mail at linuxsoft dot cz | Design: www.megadesign.cz | Textová verze