Hlavní menu
Hlavní stránka Seznam rubrik Download Weblinks Ankety Top 15

  Kalendář
<<  Září  >>
PoÚtStČtSoNe
 1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30     

Použít MyISAM nebo Inno_DB v MySQL databázi?
Datum vydání: 23. 04. 2014 (2739 přečtení)

Nevíte, kterou možnost použít při vytváření nové tabulky? Tady je rychlý a jednoduchý popis rozdílů.

MyISAM

Databáze MyISAM je optimalizována pro zobrazování (víceméně) statických dat, která se příliš nemění a není s nimi potřeba dělat ani složitější operace. Efektivita MyISAM však výrazně klesá s množstvím záznamů v tabulce, takže je doporučena pouze do velikosti desítek tisíců záznamů v jedné tabulce!

Pokud tedy máte (resp. plánujete) jednoduchou tabulku, která bude obsahovat např. články s titulkem, textem a jménem autora, kdy bude při každém vykreslení stránky potřeba získat z databáze konkrétní článek, ale měnit nebo přidávat se nebudou tak často (pojem 'často' by znamenal několikrát za sekundu), pak je MyISAM ta pravá.

SELECT * FROM clanek WHERE id = 123 LIMIT 1;

Hlavní nevýhodou MyISAM je to, že při změně (INSERT, UPDATE nebo DELETE) musí zamknout celou tabulku, čímž znemožní její čtení (SELECT). Pokud tedy plánujete nad tabulkou dělat hromadné změny (např. UPDATE user SET active = 0 WHERE last_login IS NULL), použijte raději InnoDB, které může zamykat pouze konkrétní řádky na nichž dochází ke změně a neblokuje tak SELECT příkazy během čekání na dokončení složitějších změn.

Související nevýhoda je to, že při každém INSERT, UPDATE nebo DELETE musí databáze zamknout tabulku (a pak ji zase odemknout), což nějakou dobu trvá a při opakovaném volání může výrazně zpomalovat komunikaci. Pokud tedy chcete nad MyISAM provádět větší množství změn (např. import SQL souboru s více příkazy nebo provádění příkazů v cyklu), uzavřete všechny SQL příkazy do transakce, čímž dojde pouze k jedinému uzamčení:

query('START TRANSACTION');
for (i = 0; i < users->count(); ++i) {
    query("UPDATE user SET updated = 1 WHERE id = {i}");
}
query("COMMIT");

Nebo použijte InnoDB, které tím netrpí.

Další nevýhodou může být to, že MyISAM používá pouze jedno vlákno pro zpracování příkazů, takže nedokáže správně využít vícejádrové nebo víceprocesorové servery. Pokud tedy máte počítač nebo server se 4 a více CPU jádry a odpovědi z databáze trvají dlouho i když CPU není 100% využité, zkuste tabulku převést na InnoDB.

InnoDB

Databáze InnoDB je oproti MyISAM více zaměřená na ostatní práci s daty, tedy přidávání, změnu a mazání (díky tomu, že změna jedné řádky nijak nebrání přístupu k ostatním). Je tedy vhodná pro data, která se často mění, např. tabulka s aktuálně přihlášenými uživateli a jejich session daty.

UPDATE user SET last_login = NOW() WHERE id = 123

InnoDB je také mnohem efektivnější při provádění hromadných změn (např. UPDATE user SET active = 0 WHERE last_login IS NULL) nebo opakovaní příkazů (např. for (i = 0; i < users->count(); ++i) { query("UPDATE user SET updated = 1 WHERE id = {i}"); } a je tedy vhodnější pro tabulky, kde čekáte větší množství dat (nad 100 tisíc záznamů v jedné tabulce), jejichž hromadná změna může nějakou dobu (řádově sekundy) zabrat.

Další možnost, kterou poskytuje InnoDB jsou tzv. cizí klíče (FOREING KEY), které umožňují automatické provázání tabulek, např. pokud máte seznam článků a ke každému článku tabulku komentářů, použití sloupce clanky_id v tabulce komentare automaticky prováže tento sloupec s hodnotou sloupce id v tabulce clanky.

INSERT INTO komentar SET text = '...', clanek_id = 123; //nedovolí, pokud neexistuje článek 123

DELETE FROM clanek WHERE id = 123; //nedovolí, pokud existují komentáře k danému článku provázané cizím klíčem

Výše uvedený výraz "automaticky" neznamená, že to nastaví tabulka za vás - provázání musíte ručně nastavit (viz dále). Automatické je pouze hlídání přidávání, změn nebo mazání záznamů.

Cizí klíče často využívají různé CMS a frameworky - pak je potřeba se podívat do dokumentace, zda váš CMS či framework přímo nevyžaduje použití InnoDB.

InnoDB a PhpMyAdmin

Pokud chcete v PhpMyAdmin použít cizí klíče pro propojení a správu tabulek, možná zjistíte, že nevíte, kde začít. Tahle možnost je totiž tak trochu skrytá (alespoň ve starších verzích PhpMyAdmin).

Pro příklad budu mít seznam uživatelů (tabulka `user`) a chci je přiřadit přes `user`.`id` do tabulky `rights` do sloupce `rights`.`user`. Tabulka `rights` je tzv. odkazující (referring), protože svými hodnotami (`rights`.`user`) odkazuje do jiné tabulky. Tabulka `user` je pak odkazovaná (referred), protože její data (`user`.`id`) jsou používána jinde.

Nejprve se podívejte na obě tabulky a pod "Úpravy" se přesvědčte, že jsou přepnuty na InnoDB ("Úložiště: InnoDB"). Pokud ne, přepněte ho a klikněte na "Proveď" v dané sekci. Uvědomte si ale, že tato operace může trvat řádově minuty až hodiny v závislosti na velikosti tabulky (mluvím zde ale o milionech záznamů, pro tabulky o tisících záznamů je operace okamžitá).

Před změnou si ověřte, že máte dostatek místa na disku, protože InnoDB potřebuje o dost víc místa než MyISAM. Abyste předešli problémům, můžete původní tabulku zkopírovat do nové (pouze strukturu), pak změnit její úložiště a následně teprve překopírovat data (INSERT FROM SELECT).

Pokud konvertujete více tabulek a máte přístup do konfigurace MySQL serveru, můžete zmenšit hodnotu key_buffer_size, která určuje velikost cache pro MyISAM, a naopak zvětšit innodb_buffer_pool_size, která dělá totéž pro InnoDB. Poté je potřeba restartovat službu nebo démona MySQL.

Až budete míst obě tabulky (odkazující i odkazovanou) v InnoDB, přepněte se na záložku "Struktura" a klikněte na odkaz "Klíče" (dole pod tabulkou). Přesvědčte se, že odkazovaný i odkazující sloupec mají vytvořený index - tzn. že pod klíči musí existovat řádek, který má ve sloupci "Pole" uvedeno jméno daného sloupce (`id` pod tabulkou `user` a `user` pod tabulkou `rights`).

Pokud ne (a vaše databáze je tudíž špatně navržená a pomalá), klikněte na "Proveď" za řádkem "Vytvořit klíč...". Pod jméno zadejte jméno pole (např. "id") a v seznamu "Pole" vyberte dané pole (tedy "id"). Jako typ klíče zvolte "UNIQUE" pro odkazovaný sloupec nebo "INDEX" pro odkazující (můžete i UNIQUE, pokud je relace 1:1 a hodnoty budou také jedinečné). Pokud jde o primární klíč (`user`.`id`), měli byste zvolit "PRIMARY", ale takový klíč by měl již v tabulce existovat, pokud je správně navržená.

Pokud máte obě tabulky v InnoDB a máte vytvořené klíče na sloupcích, můžete nyní vytvořit cizí klíče. Přepněte se na odkazující tabulku (`rights`) a jděte na záložku "Struktura". Pod tabulkou najděte odkaz "Zobrazit relace" (ano, nejmenuje se to Cizí klíče, a proto se to špatně hledá) a klikněte na něj.

V novějších verzích PhpMyAdmin je již přímo nahoře na záložce Struktura tlačítko Zobrazení relací. Zde vypadá formulář pro cizí klíče trochu jinak, ale princip je pořád stejný.

U odkazujícího sloupce (`user`), který je zde reprezentován řádkou, zvolte pod "Omezení cizího klíče" jméno odkazované databáze, tabulky a sloupce (tedy `user`.`id`). Až je vyberete, zobrazí se další políčka pro omezení. Zadejte tedy nějaké "Jméno omezení" (musí být unikátní pro celou DB, takže doporučuji použít jméno odkazované a odkazující tabulky a sloupce, např. "rights_user_id") a nyní musíte zvolit typ omezení pro smazání (ON DELETE) a změnu (ON UPDATE). Obě omezení mají hodnoty "RESTRICT", "CASCADE", "SET NULL" a "NO ACTION".

V novější verzi PhpMyAdmin naopak máte prázdný řádek, ve kterém musíte vybrat jména odkazujícího i odkazovaného sloupce. Jméno a omezení jsou pak trvale zobrazena na začátku řádky.

Omezení ON DELETE znamená, že pokud smažete záznam z odkazované tabulky (tedy smažete uživatele), musí se to nějak projevit i v tabulce `rights`. Podobně ON UPDATE se aplikuje v případě, že změníte hodnotu v odkazované tabulce (tedy změníte id uživatele).

Omezení RESTRICT znamená, že taková akce není povolena - tedy nemůžete smazat ani změnit id uživatele, který je někde odkazován. Toto omezení je nejčastěji používané, protože zamezuje nechtěné (nebo nepromyšlené) změně dat.

Naopak omezení CASCADE provede to, že po změně hodnoty v odkazované tabulce změní odpovídající hodnoty v odkazující tabulce (tedy po změně id uživatele se změní jeho id i ve sloupci `rights`.`user`). Toto omezení zabraňuje vzniku nekonzistence mezi tabulkami. Může být ale i nebezpečné, pokud nemá odkazovaná tabulka UNIQUE klíč (vztah m:n) na daném sloupci a dojde ke sloučení dvou položek. V případě ON DELETE znamená omezení CASCADE to, že pokud vymažete uživatele, smaží se i příslušné záznamy v `rights`. Tím si usnadňujete práci při mazání záznamů, protože nemusíte myslet na to, kde všude je daná položka použita. Na druhou stranu může dojít k problému, že vymažete něco, co ještě potřebujete (např. pokud je daný uživatel v jiné tabulce přiřazen k něčemu důležitému, jako třeba správce serveru s jediným přístupem).

Omezení SET NULL znamená, že při změně nebo smazání se hodnota nastaví na NULL (např. chcete, aby po smazání redaktora zůstali jeho články v DB s tím, že je napsal neznámý autor) a omezení NO ACTION znamená, že k žádné změně nedojde (např. odkazovaná tabulka obsahuje jen popis hodnot, ale hodnota sama má stále význam - třeba když z tabulky `dph` smažete záznam "21 = základní sazba", v tabulce `objednávka` může pořád zůstat "21" ve sloupci `sazba_dph`).

Pokud se vám při pokusu o přidání cizího klíče zobrazí chyba "1215: Cannot add foreign key constraint", znamená to, že odkazující a odkazovaný sloupec nemají stejný typ. Rozdíl může být třeba v tom, že nemají stejný datový typ (např. CHAR vs. VARCHAR nebo DATETIME vs. TIMESTAMP), nemají stejnou délku (např. CHAR(3) vs. CHAR(5) nebo SMALLINT vs. TINYINT) nebo se liší definicí (např. UNSIGNED vs. UNSIGNED ZEROFILL). Pro propojení musejí mít oba sloupce naprosto stejnou definici, tedy typ, délku i další vlastnosti (BINARY, UNSIGNED, atd.).

Pokud se vám při pokusu o přidání cizího klíče zobrazí chyba "1452 - Cannot add or update a child row: a foreign key constraint fails", znamená to, že odkazující sloupec obsahuje hodnoty, které neexistují v odkazované tabulce. Např. že máte v tabulce `rights` nadefinována práva pro uživatele, který v tabulce `user` neexistuje. Před pokračováním musíte dané řádky odstranit nebo opravit (buď na hodnoty, které existují, nebo na NULL). Pro získání seznamu chybných hodnot můžete použít SQL příkaz SELECT DISTINCT user FROM rights LEFT JOIN user ON user.id = rights.user WHERE user.id IS NULL (tedy k odkazující tabulce připojit odkazovanou a najít řádky, pro které neexistuje v odkazované tabulce záznam).

[Akt. známka: 5,00 / Počet hlasů: 1] 1 2 3 4 5
Autor: Nothrem Sinsky | Komentáře: 0 | Přidat komentář | Informační e-mail Vytisknout článek

  Vyhledávání

Vyhledat text

RybolovUbytování CyklistéPenzionChalupa Nová BystřicePenzion Jižní ČechyJižní Čechy chataUbytování samotaKomorníkUbytováníStrmilovKunžakČeská Kanada ubytováníJindřichův Hradec chata

  Administrace
Administrace

  Copyright
2006 © Nothrem Sinsky

Tento web byl vytvořen pomocí redakčního systému phpRS
Layout: SandStorm - verze 0.9.0
based on layout Beach - verze 1.1.0