Tvorba databází v MySQL - IV

10. 4. 2003 | David Hauzar | Návody | 29061×

V tomto díle se naučíte správně používat klíče a zabudované fulltextové vyhledávání. Nakonec se seznámíte s problematikou zamykání záznamů.

Pokud jste se už naučili jazyk SQL, můžete se svými znalostmi vytvořit dobře strukturovanou databázi, pomocí příkazů SQL ji můžete dokonce zaplnit daty a dotazy z ní získávat data. Nenechte se tím zmást a vězte, že jste stále ještě na začátku cesty poznání databázového systému MySQL.

Dnes se seznámíte s trochu pokročilejšími aspekty tvorby databází - klíči a zamykáním záznamů.

Klíče (indexy)

Při předchozím studiu jste se setkali s primárními klíči. Dnes se budu zabývat klíči komplexněji.

Klíče (nebo také indexy) urychlují vyhledávání v databázi. Pokud přidáme do tabulky klíč, vlastně ji setřídíme.

Je tedy vhodné používat klíče v těchto případech:

• použití klíčů zvyšuje rychlost vykonání příkazu WHERE. Pokud se tedy pole často vyskytuje v příkazu WHERE, je vhodné přiřadit mu klíč.
• použití klíčů také zvyšuje rychlost vykonání příkazu ORDER BY a GROUP BY.
• použití klíčů zvyšuje rychlost vykonání funkcí MIN() a MAX()
• klíče jsou také prostředkem k vytváření relací

Naopak není vhodné použít klíč v těchto případech:

• tabulky, do nichž se často zapisuje
• není vhodné indexovat sloupec, jehož hodnota je často stejná (například pohlaví, titul, ...)
• pokud je už v dané tabulce příliš mnoho sloupců s klíči

K manipulaci s klíči lze v MySQL použít tyto příkazy:

(

Poznámka ke konvenci zápisu syntaxe příkazů:

ALTER TABLE tabulka ADD {KEY | INDEX} index ({sloupec1, sloupec2,...});
ALTER TABLE tabulka ADD UNIQUE index ({sloupec1, sloupec2,...});
ALTER TABLE tabulka ADD PRIMARY KEY index ({sloupec1, sloupec2,...});

[slovo] - slovo uzavřené v těchto závorkách je nepovinné

{slovo1, slovo2, slovo3} - minimálně jedno z uvedených slov musí být uvedeno

[slovo1| slovo2] - jedno z těchto slov bude může být nepovinně uvedeno

)

Pokud budete chtít klíč odstranit, nahradíte slovo ADD slovem DROP

Toto je upřednostňovaný postup při vytváření klíčů. Můžete ale také použít tyto příkazy:

CREATE INDEX index ON tabulka ({sloupec1, sloupec2,...}]);
CREATE UNIQUE INDEX [index] ON tabulka ({sloupec1, sloupec2,...});
CREATE PRIMARY KEY ON tabulka ({sloupec1, sloupec2});

Klíče můžete také definovat při vytváření tabulky:

CREATE TABLE tabulka (sloupec datovýtyp [NULL | NOT NULL],
  KEY col_index(sloupec));

Informace o klíčích v dané tabulce zobrazíte pomocí příkazu:

SHOW {KEYS | INDEX} FROM tabulka

Jednopoložkové klíče

Jednopoložkové klíče jsou nejjednodušší klíče. Jednopoložkový klíč k sloupci Jmeno_Prokuktu vytvoříte například takto:

ALTER TABLE Produkty ADD KEY produkt_idx (Jmeno_Produktu);

Vícepoložkové (složené) klíče

Pokud k definici klíče použijete více než jedno pole, vytvoříte vícepoložkový klíč. Vícepoložkový klíč vytvoříte například takto:

ALTER TABLE Produkty ADD KEY sloz_idx (Jmeno_Produktu, Zeme_Puvodu, Barva);

Rozdíl mezi jednoduchým a složeným klíčem můžete vidět v následující tabulce:

Jednopoložkové a složené klíče

Jmeno_Produktu	Zeme_Puvodu	Barva	produkt_idx	sloz_idx
Monitor	China	Šedá	Monitor	MonitorChinaŠedá
Myš	Japan	Šedá	Myš	MyšJapanŠedá
Klávesnice	Japan	Černá	Klávesnice	KlávesniceJapanČerná

Zkuste po vytvoření složeného indexu zadat příkaz:

SHOW KEYS FROM tabulka;

Při používání vícepoložkových klíčů je dobré řídit se následujícími pravidly:

• Na prvním místě uveďte nejvíce omezující podmínku (např. sloupec Jmeno_Produktu je více omezující než sloupec Zeme_puvodu).
• při psaní dotazů kritéria klauzule sestavujte tak, aby se v ní objevilo první pole vícepoložkového klíče. To zajistí, že bude tohoto klíče použito.
• Vícepoložkové klíče zatěžují databázi více, než jednopoložkové klíče.

Částečné klíče

Částečné klíče se používají pro indexování určitého počtu prvních znaků sloupce textového datového typu.

Částečné klíče je vhodné použít, pokud by mohl být indexovaný sloupec příliš dlouhý. Částečný klíč vytvoříte například takto:

ALTER TABLE Produkty ADD KEY produkt_idx (Jmeno_Produktu(5));

Složené částečné klíče

Složený klíč můžete samozřejmě skládat i z částečných klíčů.

Používání částečných klíčů je výhodné, protože bývají kratší a jejich výkon je tedy zpravidla vyšší.

Jedinečné klíče

Jedinečné klíče nedovolují v indexovaných polích existenci duplicitních položek. Tabulka může obsahovat více než jeden jedinečný klíč. Jedinečný klíč vytvoříte příkazem:

ALTER TABLE tabulka ADD UNIQUE index (pole[...]);

Primární klíče:

Primární klíč je speciální typ jedinečného klíče. Na rozdíl od běžného jedinečného klíče smí být v tabulce pouze jeden primární klíč a pole definované jako primární klíč nesmí obsahovat prázdné hodnoty (NULL).

Primární klíč se používá pro vytvoření relace mezi tabulkami.

Vícepoložkové primární klíče

Pokud nelze jedinečnost primárního klíče zajistit pomocí jednoho pole, používá se vícepoložkový primární klíč. Vícepoložkový primární klíč nelze vytvořit pomocí příkazu CREATE TABLE, ale jedině pomocí příkazu ALTER TABLE. Složený primární klíč vytvoříte například takto:

ALTER TABLE Produkty ADD PRIMARY KEY (Jmeno_Produktu, Zeme_Puvodu);

Pokud vytvoříte takový primární klíč, nebudete moci mít v dané tabulce více produktů stejného názvu a stejné země původu!

Můžete samozřejmě vytvořit i složený částečný primární klíč.

Syntetické (náhradní) klíče

Vytvoření primárního klíče z reálných dat bývá často nevhodné. Často se jen těžko hledá taková kombinace sloupců, která je pokaždé jiná.

V takových případech je nejlepší vytvořit syntetický klíč. To je sloupec speciálně vytvořený pro to, aby se stal primárním. Je to tedy sloupec, který obsahuje data, která bychom jinak nepotřebovali. V našem příkladě bychom vytvořili pro každý produkt jedinečné identifikační číslo.

Naši tabulku vytvoříte příkazem:

CREATE TABLE Produkty (ID_Produktu INT(8) AUTO_INCREMENT
  PRIMARY KEY NOT NULL, Jmeno_Produktu VARCHAR(20), ...)

Fulltextové klíče

Fulltextové klíče jsou v MySQL poměrně nové - jejich podpora byla do MySQL přidána ve verzi 3.23.23.

Fulltextový klíč se smí vytvářet pouze ze sloupců typu VARCHAR a TEXT. Fulltextový klíč vytvoříte například takto:

ALTER TABLE Knihy ADD FULLTEXT INDEX (Nazev_Knihy, Ukazka);

V takto indexovaných sloupcích můžete použít velmi rychlé fulltextové vyhledávání. Vyhledávání se realizuje pomocí klíčových slov MATCH(pole_ve_kterem_chcete_hledat1, pole_ve_kterem_chcete_hledat2, ...) a AGAINST(hledaný výraz).

Pokud použijete klíčová slova MATCH a AGAINST v klauzuli WHERE, výsledný výpis bude sestupně setřízený podle četnosti hledaných slov v prohledávaných sloupcích.

Pokud hledaný výraz obsahuje víc slov, bere MySQL při výpočtu četnosti hledaných slov v prohledávaných sloupcích ohled i na to, jaké ze slov je obsaženo ve více záznamech. To, které je obsaženo ve více záznamech, má menší váhu (pokud je obsaženo v 50 a více procentech záznamů, tak nemá dokonce žádnou váhu - viz dále ), protože má menší sémantickou hodnotu. (slovo, které je v méně záznamech pravděpodobně určuje blíže, to co chcete najít)

Pozor:

• Fulltextové vyhledávání můžete používat jen s tabulkami MyISAM (defaultní typ tabulek v MySQL).
• Sloupce předávané jako parametry funkce MATCH() musí být ze stejné tabulky a musí být částí stejného fulltextového klíče.
• Parametr konstrukce AGAINST musí být řetězec.
• Pokud je hledané slovo kratší než 4 znaky, MySQL ho ignoruje.
• Pokud se hledané slovo vyskytuje v 50 a více procentech záznamů, MySQL ho také ignoruje. Má to své opodstatnění - hledání pak ztrácí smysl. (Pokud se nějaké slovo vyskytuje v padesáti a více procentech záznamů, těžko vám pomůže najít to, co hledáte - to už můžete záznamy prohledávat "ručně").
• Předchozí dvě omezení neplatí v boolean režimu (viz dále). Můžete je také upravit nebo zrušit přepsáním příslušných symbolických konstant ve zdrojovém kóku a následnou kompilací MySQL - viz manuál čát 6.8.2 Fine-tuning MySQL Full-text Search
• To, zda-li je fulltextové vyhledávání case-sensitive, nebo case-insensitive závisí na použitém charsetu. Čeština je na rozdíl od většiny case-sensitive .

Od verze 4.0 podporuje MySQL v hledaných výrazech dokonce i booleanovské operátory. Můžete použít tyto operátory:

• + (+slovo znamená, že slovo musí být obsaženo v každém záznamu)
• - (-slovo znamená, že slovo nemusí být obsaženo v každém záznamu)
• < a > mohou být užity ke zvýšení/snížení váhy slova
• ~ může být použit k udělení negativní váhy slovu
• * zástupný operátor

Příklady:

SELECT * FROM Knihy WHERE MATCH (Nazev_Knihy, Ukazka) AGAINST ('databáze');

Tento dotaz vrátí všechny záznamy obsahující ve sloupci Nazev_Knihy nebo ve sloupci Ukazka slovo databáze. Výpis bude sestupně setřízený podle procentuální četnosti hledaných slov.

SELECT ID_Knihy, MATCH (Nazev_Knihy, Ukazka) AGAINST ('databáze') as Score FROM Knihy;

Tento dotaz zobrazí sloupec ID_Knihy a sloupec Score, ve kterém bude číslo (0 znamená, že v daných prohledávaných sloupcích se slovo databáze nevyskytuje, desetinné číslo určuje četnost slova databáze v prohledávaném sloupci vztaženou na počet slov tohoto sloupce => čím větší číslo, tím větší procentuální četnost) všech záznamů z tabulky Knihy. Výpis nebude setřízený

SELECT ID_Knihy, MATCH (Nazev_Knihy, Ukazka) AGAINST ('databáze') as score FROM Knihy
  WHERE MATCH (Nazev_Knihy, Ukazka) AGAINST ('databáze');

Jako předchozí dotaz, ale záznamy setřídí a vypíše jen záznamy, ve kterých se tato hledaná slova vyskytovala.

Zamykání záznamů

K databázi může přistupovat více lidí současně. Pokud je nějaký dotaz na databázi závislý na aktuálním stavu dat, který by jiný dotaz mohl změnit, je nutné uzamknout příslušný záznam.

Taková situace by mohla nastat, pokud bychom v databázi evidovali množství zásob na skladu a podle toho určovali dodací lhůty. Jeden proces by zjistil množství zásob na skladu, vypočítal by dodací lhůtu a pak by odečetl objednané zboží od zboží na skladu. Pokud by ale do databáze přistoupil druhý proces těsně po prvním (ještě před tím, než první odečte objednané zboží od zboží na skladu), získal by špatná data.

Syntaxe příkazu:

LOCK TABLES tabulka1 [AS alias] {READ | [LOW_PRIORITY] WRITE}
  [, tabulka2 {READ | [LOW_PRIORITY] WRITE} ...]

Tabulku odemknete příkazem:

UNLOCK TABLES

Tabulky lze zamykat pro čtení (READ) - znamená to, že ostatní procesy budou moci z tabulky pouze číst (a nebudou do ní moci zapisovat).

Dále lze tabulky zamykat pro zápis (WRITE) - pak bude moci tabulku používat pouze proces, který ji uzamkl - získá k ní výhradní přístup.

Pokud dostane databáze více příkazů LOCK TABLES, řadí se tyto příkazy do fronty. Požadavky na výhradní přístup mají přednost před požadavky na otevření tabulek jen pro čtení.

Pokud zadáte příkaz LOW_PRIORITY WRITE, bude MySQL dávat přednost požadavkům na čtení před požadavky na výhradní přístup. Tento příkaz zadejte, pokud je ve vaší databázi důležitější otevření tabulky pro čtení než otevření tabulky ve výhradním režimu.

Zadáte-li příkaz SELECT HIGH_PRIORITY, můžete z tabulek číst, i když je na tuto tabulku vystaven požadavek na výhradní přístup.

Použití příkazu LOCK TABLES

• Příkaz se používá k zajištění výhradního přístupu.
• U několikařádkových operací (zejména těch, které obsahují příkaz INSERT) může dojít ke zvýšení výkonu.
• Příkaz LOCK TABLES se používá při zálohování databáze. Pokud by někdo zapisoval do databáze v průběhu zálohování, mohla by se porušit konzistence dat.

Závěr

Dnes jste nabyli další velmi významné dovednosti potřebné ke zvládnutí MySQL. Klíče jsou nezbytné při tvorbě relací, mohou významně zvýšit vykonání klauzule WHERE. V neposlední řadě můžete použít fulltextové klíče k fulltextovému hledání.

Zamykání záznamů se používá k odstranění problémů vzniklých v důsledku mnohonásobného přístupu.

V příštím dílu se seznámíte s nejdůležitějšími funkcemi v MySQL.

       

Komentáře

[Martin Kučera]

Podle dokumentace k mysql je možné používat fulltext pouze u tabulek MyISAM, na to by nebylo špatné upozornit. Zkrátka, člověk si musí vybrat: transakce (InnoDB) nebo fulltext

[Jan Kubik]

z clanku mam pocit, ze autor rika klic=index - je to spravne ?

[romo]

klic <> index

[Jan Kubik]

ty kluku usata, ty jiste tusis, ze ja jsem chtel vedet, kde je ten rozdil !!!

[romo]

chacha... ked to vies, na co sa pytas?

[xyz]

autor ma v podstate pravdu pretoze kluc (klic) primarny a ostatne su automaticky indexovane.

[romo]

nema pravdu: kluc, ktory by nebol indexom, je hlupost, ale nie kazdy index je klucom

[Zdeněk Štěpánek]

Zdravim Ja jsme se mysql naucil jako samouk, takze mozna mam spatny navyky, ale podobne veci vestinou delam timto zpusobem: Mam v DB polozky jmeno, prijmeni, telefon, popis apod. Tyhle vsechny spojim za sebe a prozenu to MD5 funkci a ulozim jako ID. No a tohle ID pak pouzivam vsude jinde. Mam zaruceno ze je jedinecne. Zdenek

[ja]

tak to je z principu md5 pouze pravdepodobne, nikoli jiste

[rat]

no kdyz odhlednes od toho, ze jde o uplne zbytecny vypocet, ktery je treba provadet pri kazde zmene a jedinecnost ani neni zarucena, tak je to skoro stejne dobre jako ten autoincrement...

[David Hruska]

Muz mi nekdo rici zda je fulltext vyhledavani case sensitive nebo insensitive? pripadne zda je mozno udelat insensitive vyhledavani? Dekuju

[David Hauzar]

Fulltext vyhledavani je case insensitive.

[Henryk Paluch]

Bohuzel to zavisi na pouzitem charsetu. Treba 'czech' je jako jeden z mala case sensitive (overeno i pokusem). Citat z dokumentace: Case-Sensitivity in Searches By default, MySQL searches are case-insensitive (although there are some character sets that are never case-insensitive, such as `czech'). Chcete-li pouzivat fulltext, tak doporucuji podrobne si precist dokumentaci. Je tam popsana rada zajimavosti, napr. MATCH/AGAINST standardne ignoruje slova kratsi nez 4 znaky a take vyrazy, ktere jsou nalezeny v 50% (a vice %) radku.

[Henryk Paluch]

Jeste upresneni k memu poslednimu odstavci: Popisovane omezeni (<=4 znaky, >=50%) se tyka jen varianty AGAINST('slovo'). V boolean rezimu, tj. AGAINST('+slovo1 +slovo2' IN BOOLEAN MODE) uvedene omezeni neplati.

[Vojtech Semecky]

To že je čeština case-sensitive je docela problém/nedostatek. Nevíte, jestli tato vlastnost platí i u vyšších verzí MySQL s podporou Unicodu? Myslím, jestli je case-sensitive i čeština uložená jako UTF-8.

[OldFrog {Ondra Nemecek}]

minimalni delku slova 4 znaky a limit 50% vyskytu lze upravit:

http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning.html