Integritní omezení a transakce v MySQL

Doteď to byla, co se databází týče pouze teorie, dneska si dáme trošku praxe v MySQL. Použití transakcí v MySQL. Jak nadefinovat referenční integritu v MySQL. Podmínky nutné pro definici. Různé možnosti, ale i úskalí.

Po všech čistě teoretických článcích o databázích se dnes podíváme na praxi v MySQL. Ale budu se věnovat pouze referenční integritě a transakcím, protože je to oblast, která je často opomíjena, nebo se v ní chybuje.

MySQL nemá ve svém defaultním formátu tabulek, ani podporu pro referenční integritu ani pro transakce. Z tohoto důvod se musí požívat storage engine InnoDB. To provedeme jednoduše:

CREATE TABLE customers 
(
  customer_ID INT,
  customer_name CHAR (20)
)
ENGINE=InnoDB;

Místo direktivy ENGINE, je možno použít i TYPE, ale ten je deprecated a je podporován pouze z důvodů zpětné kompatibility, ze stejného důvodu ho uvádím já. Nad takto vytvořenými tabulkami lze provádět transakce a definovat referenční integritu.

Transakce

Začnu transakcemi. Nebudu vysvětlovat, co to jsou transakce, neboť je tomu věnovaný vlastní článek a budu se věnovat rovnou implementaci v MySQL. Každý klient připojující se k MySQL serveru má defaultně nastavenou hodnotu AUTOCOMMIT na true. To znamená, že po každém dotazu a operaci se provede COMMIT. Pokud chci provádět více dotazové transakce, musím buďto tento stav změnit pomocí direktivi SET AUTOCOMMIT = 0, což způsobí, že každý dotaz, který přijde po této direktivě, začne transakci, která neskončí, dokud nepřijde příkaz COMMIT nebo ROLLBACK. Další možností je použití direktivi START TRANSACTION, která začne transakci, která se ukončí stejně jako v předchozím případě, ale každý další dotaz po COMMITu/ ROLLBACKu bude opět považován za ukončenou transakci. A vzhledem k tomu, že příklad řekne víc než 1000 slov. Vypůjčím si jeden z manuálu k MySQL:

shell> mysql test

mysql> CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A))
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO CUSTOMER VALUES (10, 'Heikki');
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO CUSTOMER VALUES (15, 'John');
Query OK, 1 row affected (0.00 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM CUSTOMER;
+------+--------+
| A    | B      |
+------+--------+
|   10 | Heikki |
+------+--------+
1 row in set (0.00 sec)
mysql>

Integritní omezení

InnoDB umí FOREIGN KEY Constraints, neboli omezení, donucení, přinucení cizým klíčem. Constraintse přidává do podřízené tabulky, kde definuje, který sloupec/sloupce jsou cizím klíčem. Proto, aby se dal definovat constraint, musí na klíčových sloupcích existovat index. Sloupce ktré si mají odpovídat také musí mít stejný datový typ. On by tam z víkonostních důvodů měl být tak jako tak, ale takhle si ho to vynutí. Samotný constraint se definuje tamkto:

[CONSTRAINT symbol] FOREIGN KEY (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

[CONSTRAINT symbol] – Nepovinná část klauzule, kde symbol je název cizího klíče, který musí být jedinečný v celé databázi. Pokud není uvedeno, vytvoří se automaticky. Ale specifikovat jméno se může hodit, neboť s constrainty lze pracovat pomocí ALTER TABLE, ale musíme znáty jejich jméno.

FOREIGN KEY (index_col_name, ...) – sloupec/ce s cizím klíčem v podřízené tabulce

REFERENCES tbl_name (index_col_name, ...) – odpovídá tabulce tbl_name sloupci/ům index_col_name

[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

Tyto klauzule specifikují co se má stát při UPDATE/DELETE na ndřazeném záznamu, respektive při změně referencovaných sloupců. RESTRICT a NO ACTION jsou to samé a zamezí provedení akce. CASCADE smaže/aktualizuje všechny podřízené záznamy, ale jakmile je někde v cestě RESTRICT nebo NO ACTION, celá operace se zruší. SET NULL je jasné, opět platí cancel operace, pokud níže v kaskádě je RESTRICT nebo NO ACTION.

Nebudu opakovat průpovídku o příkladu a rovnou ho uvedu:

Mějme relace:

CREATE TABLE Discusion
(
   ID         INT unsigned NOT NULL auto_increment,
   article_ID INT unsigned NOT NULL,
   parent_ID  INT unsigned,               
   poster     VARCHAR(50)  NOT NULL,
   header     VARCHAR(100) NOT NULL,
   inserted   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
   message    TEXT         NOT NULL,
   PRIMARY KEY (ID),
   KEY ArticleID (article_ID),
   KEY Parent (article_ID, parent_ID),
   INDEX Article_IN (article_ID),
   INDEX Parent_IN (parent_ID, article_ID),
   FOREIGN KEY (article_ID) REFERENCES
    Article (ID) ON DELETE CASCADE ON UPDATE CASCADE,
   FOREIGN KEY (article_ID, parent_ID) REFERENCES
    Discusion (article_ID, ID) ON DELETE CASCADE ON UPDATE CASCADE
)engine=InnoDB;

Každý záznam v relaci Discusion musí mít vazbu na článek a vzhledem k tomu, že se jedná o stromovou diskuzi, musí mít vazbu na příspěvek, na který odpovídá (parent_ID ), ale zároveň musí mít i stejné article_ID jako příspěvek na který reaguje. Tudíž prvně vytvoříme odpovídající klíče, indexy a následně specifikujeme cizí klíče. ON DELETE RESTRICT zabrání smazaní článku, na který už někdo reagoval a smazaní příspěvku na který bylo reagováno. ON UPDATE CASCADE umožní UPDATE jak článku, tak rodičovského příspěvku bez ztráty vazeb.

Nevýhody

Doteď jsem popisoval jenom možnosti, které InnoDB nabízí a ignoroval nevýhody. A to, že je o něco pomalejší než MyIsam a neumí fulltext index. Tudiž se sloupce, na kterých potřebujeme fulltextové vyhledávání, musíme vložit do speciálních tabulek, které použijí engine MyIsam a vazby se řeší buďto na aplikační úrovni, nebo od MySQL verze 5 pomocí TRIGERů. Jiným řešením je pro vkládání a modifikaci dat používat jednu databázi a na vyhledávání a výběr používat replikovanou databázi, kde bude vše na MyIsam enginu.

Ještě minulý rok na podzim společnost Innobase (tvůrce InnoDB enginu) slibovala, že letos už bude fulltext fungovat. Pak je 11. 10. 2005 koupil Oracle a máme po srandě. Takže MySQL AB nezbylo nic jiného než si vytvořit vlastní engine a v únoru koupila firmu Netfrastructure, jejíž majitel Jim Starky je tvůrcem tří transakčních enginů a to db/ELN, InterBase a engine pro Netfrastructure nazývaný JSTar. V současné době pracoval na Vulcanu pro Firebird a Netfrastructure. Jim pro MySQL AB vyvíjí na pozici Senior Architekta databázový engine Falcon, který je postaven na jádře JSTar modifikovaném pro MySQL.

Co říci závěrem? InnoDB přináší výhody transakčního zpracování a Constraintů, ale má jednu, pro někoho zásadní, vadu na kráse. Řešení fulltextu dá sice trošku práci, ale podle mého názoru je to furt lepší, než řešit IO v aplikaci nebo pomocí triggerů. A tu trochu výkonu obětuji, hlavně proto, že v příadě, že budu stavět takovou DB, kde bude kritický výkon, sáhnu po možnosti replikace.