MySQL: deset pravidel pro schéma, které nezradí

Sepsal jsem si závazné konvence pro návrh MySQL schémat a u každého pravidla si vynutil odpověď na otázku proč. Ta odpověď je totiž polovina celé hodnoty: pravidlo, které se neumí obhájit, je dogma, a to první nespokojený kolega právem shodí ze stolu.

Vším, co následuje, se vine jedna myšlenka: databáze je poslední stráž integrity. Aplikace validuje kvůli uživateli: hezké hlášky, v jeho jazyce, na jeho cestě kódem. Databáze garantuje. Napříč importem, migrací, ručním SQL v konzoli, druhou aplikací, buggy kódem a tím kolegou, co si do produkce pouští UPDATE bez WHERE.

Dohody se v datech neudrží. Udrží se jen to, co schéma vynucuje.

Vše níže platí pro MySQL 8.0.16 a novější (kvůli vynucovaným CHECK constraintům) a pro InnoDB.

Nultý předpoklad: strict mód

Bez striktního sql_mode nemá zbytek článku smysl, protože všechna pravidla pod ním degradují ze záruky na snahu s tichým fallbackem:

bez strict módu (tiše)	se strict módem (nahlas)
ENUM mimo seznam → uloží ''	error Data truncated
přetečený INT UNSIGNED → clamp na maximum	error out of range
delší vstup do VARCHAR(20) → ořízne	error
'0000-00-00' → uloží se	error
chybějící NOT NULL sloupec → 0 nebo ''	error

Nastavte to na serveru, ne na spojení, které vám kdokoli může přepsat:

STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

A hlavně od začátku. Zapnout strict nad databází, kde už leží oříznutá a nulová data, znamená pády až při prvním UPDATE dotčeného řádku. Tedy za půl roku, v pátek večer.

1. Tabulka se jmenuje post, ne posts

Ano, tímhle se dá rozpoutat hádka na celé odpoledne a tábor množného čísla má na své straně zvyk i pár frameworků. Tak si to pojďme odbýt hned. Množné je o chlup víc SQL-safe, SELECT * FROM posts se čte jako věta. Proti tomu stojí dva důvody, které přežijí i vaše preference:

• Jednotné číslo mapuje 1:1. S množným se jméno musí celou cestu překládat: třída Post, tabulka posts, a v každé vrstvě jeden převodník navíc.
• A drží rodinu tabulek pohromadě. Ve výpisu schématu stojí post v čele svých příbuzných post_slug a post_tag, kdežto posts se od nich abecedně odtrhne a skončí až za nimi. U tří tabulek je to detail, u tří set je to rozdíl mezi orientací a hledáním.

Zbytek pravidel o jménech už kontroverzní není. Všechna stojí na tom, že jméno v databázi je veřejné rozhraní, ne interní detail. Opírá se o něj váš kód, ale taky uložené dotazy, reporty, exporty, integrace a ten Excel, co si nad databází postavilo obchodní oddělení. Přejmenovat sloupec je proto zásah stejného kalibru jako změna signatury veřejné metody v knihovně: rozbijete i konzumenty, o kterých nevíte. Proto se pojmenovává to, co vydrží.

• snake_case, malá písmena. Nic, co by vyžadovalo quotování. blogId se v Postgresu musí psát v uvozovkách, protože neuvozené identifikátory sklápí na malá písmena, takže vám blogId a blogid klidně začnou žít vedle sebe jako dvě různé věci.
• PK vždy id, FK <jednotné číslo>_id, self-reference parent_id. Když na jednu tabulku míří víc cizích klíčů, jméno nese roli, ne cíl: author_id, reporter_id. Kam to ukazuje, stejně říká FK constraint.
• Modulový prefix až tehdy, když to začne bolet (shop_order, shop_product). Uvnitř modulu pak FK prefix nepotřebuje, tabulka namespace už nese.

A vydrží význam, ne implementace. Hranice mezi nimi je jemnější, než se zdá: jednotka je význam, typ je implementace. weight_grams a timeout_seconds ano, protože holé číslo by bylo dvojznačné. amount_int a data_json ne, ty jen převyprávějí to, co už stojí ve schématu.

2. NOT NULL a DEFAULT, který nelže

Nullable jen tehdy, když je absence hodnoty fakt o světě, ne technický stav. U čísel a dat se to rozhoduje samo, prázdná hodnota tam neexistuje a NULL je jediná pravda o absenci.

Zajímavé to začne být u řetězců, protože tam existuje '' a vy máte najednou stavy dva. Rozhodněte se pro jeden a napište to do schématu. Ve většině případů je odpověď NOT NULL DEFAULT '', aplikace pak testuje !== '' a je hotovo:

`meta_title` varchar(160) NOT NULL DEFAULT '',

Ta dvojznačnost je totiž ještě horší, než vypadá: v PHP jsou null i '' obě falsy, takže rozdíl, který v databázi pracně držíte, aplikace většinou stejně nevidí. Ten druhý stav je tedy nejen nadbytečný, ale ještě neviditelný.

Výjimka existuje, jen je vzácnější, než si lidi myslí: když NULL a '' opravdu znamenají různé věci, třeba '' jako „vědomě prázdné, nedědit z rodiče“ proti NULL jako „nevyplněno“. Pak nullable, ale ten význam povinně do COMMENT, jinak to za rok nikdo nerozklíčuje.

A proč se NULL vůbec vyplatí šetřit? Protože se chová jinak, než čekáte:

• WHERE col <> 'x' nevrátí NULL řádky. Nejčastější tichý bug vůbec.
• col NOT IN (poddotaz) vrátí s jediným NULL v poddotazu prázdno.
• UNIQUE bere NULL hodnoty jako navzájem různé, takže jich projde libovolně mnoho. GROUP BY je naopak slučuje do jedné skupiny.

Tolik k NULL. Druhá polovina tohohle pravidla je DEFAULT, a ten je stejný případ: je to sémantické tvrzení, ne záplata.

Default jen tehdy, když je hodnota doménově smysluplná v okamžiku vzniku řádku. Povinné pole bez hodnoty nechte spadnout.

Nejčastější chyba je vyplnit default proto, ať INSERT nespadne. Tím vkládáte lež k nerozeznání od skutečné hodnoty a rušíte přesně tu ochranu, kterou vám dal strict mód. status DEFAULT 'draft' ano, objednávka fakt začíná draftem. price DEFAULT 0 u povinné ceny ne, to je nula, o které nikdo nepozná, jestli je to dar, nebo chyba importu.

3. _at je okamžik, _date je den

Dva sufixy, dva typy, a rozdíl mezi nimi není kosmetický.

• Okamžik v životě řádku: sufix _at, typ datetime. created_at, published_at, deleted_at. Klidně i v budoucnosti, expires_at je pořád okamžik. Sufix se řídí typem, ne minulostí.
• Kalendářní den nezávislý na zóně: {podstatné jméno}_date, typ date. birth_date, invoice_date, due_date.

Kalendářní den uložený jako datetime je bug čekající na spuštění, a stojí za to si rozmyslet proč. K datu se přilepí půlnoc. Jakmile pak aplikace začne převádět časové zóny, což se dřív nebo později stane, půlnoc se posune o hodinu dozadu: z 15. 3. 00:00 je rázem 14. 3. 23:00. Datum narození je najednou o den dřív a nikdo dlouho nepřijde na to proč, protože ta hodina v datu není vidět. Kalendářní den zónu nemá, tak mu ji nedávejte.

Hranice není vždycky ostrá a nevadí to: valid_from a valid_to sufix nemají a je to v pořádku. Volba typu pro ně platí stejná, jen se rozhoduje podle domény. Předplatné běží od okamžiku, tedy datetime. Kupón platí od dne, tedy date.

Meta-sloupce mají mít napříč schématem jeden slovník: všude created_at, nikdy míchat create_time a date_added. A zrcadlově: stejné jméno nesmí ve dvou tabulkách znamenat dvě různé věci. Když je updated_at v jedné tabulce „kdykoli se řádku někdo dotkl“ a ve druhé „kdy obchodník naposledy sáhl na ceník“, tak dotaz, který obě tabulky spojí a seřadí podle data, vrátí nesmysl. A nikdo si toho nevšimne, protože jméno slibovalo, že jde o totéž.

4. TIMESTAMP nikdy

Dva důvody, každý stačí sám o sobě: rok 2038 a skrytá konverze podle zóny spojení, kvůli které se to, co uložíte, a to, co dostanete zpátky, liší podle toho, kdo se ptá. DATETIME nekonvertuje nic, uloží co dostane a vrátí co uložil. Disciplína kolem zón tím ale nekončí, jen se přesouvá k vám: konvence nepředepisuje UTC ani lokální čas, obojí je legitimní, předepisuje konzistenci. Celá databáze v jedné zóně a konverze až při zobrazení. DEFAULT CURRENT_TIMESTAMP i NOW() totiž vkládají čas v zóně session, takže když se serverová default_time_zone rozejde se zónou historických dat, nové řádky tiše dostanou jinou zónu než staré a nic to nenahlásí.

5. „Změnilo se X“ není „něco se změnilo“

Dva sloupce, které vypadají stejně a nejsou. Rozhodovací otázka zní: co má ten sloupec vlastně měřit?

• modified_at měří doménovou změnu, tedy „datum poslední úpravy z pohledu čtenáře“. Nesmí s ním hnout přepnutí příznaku ani přepočet počitadla zhlédnutí. ON UPDATE CURRENT_TIMESTAMP je tu proto špatně a sloupec musí udržovat trigger, který sleduje jen vyjmenované sloupce.
• updated_at měří, že se něco změnilo. Je to row version, token proti souběžné editaci. Tady je ON UPDATE CURRENT_TIMESTAMP naopak přesně správně a trigger by byl horší: musel by vyjmenovat všechny editovatelné sloupce a při přidání jedenáctého tiše zastarat. Ochrana proti přepsání cizí editace by mlčky přestala krýt nová pole.

Trigger pro ten první případ hlídá jediný sloupec, content, a všechno podstatné je v komentářích:

DELIMITER ;;

-- BEFORE, protože jen v něm má SET NEW.* nějaký efekt
CREATE TRIGGER `page_before_update_touch_modified_at`
BEFORE UPDATE ON `page` FOR EACH ROW
BEGIN
    -- COLLATE bin na obou stranách: bez něj se oprava diakritiky nepočítá jako změna (pravidlo 11)
    IF NEW.content COLLATE utf8mb4_0900_bin <> OLD.content COLLATE utf8mb4_0900_bin
       -- <=> je NULL-safe rovnost; zajistí, že trigger nepřebije datum zadané ručně (migrace, import)
       AND NEW.modified_at <=> OLD.modified_at THEN
        SET NEW.modified_at = CURRENT_TIMESTAMP;
    END IF;
END;;

DELIMITER ;

Z toho plyne obecnější povinnost: každý magicky udržovaný sloupec potřebuje COMMENT, který pojmenuje, co ho plní. U updated_at obzvlášť („concurrency token, ON UPDATE je záměr“), jinak ho příští čtenář v dobré víře „opraví“ na trigger a tiše rozbije optimistic locking.

6. Boolean je přídavné jméno

Chvíli jsem zvažoval povinný prefix is_, než mi došlo, že si tím odporuju: maďarskou notaci typu str_title zakazuju s tím, že typ patří do schématu a ne do jména, a is_ přitom nekóduje nic jiného než typ. Opačný extrém ale neobstojí taky. Zkuste vymyslet adjektivum k food_supplement nebo spam — doména o nich mluví podstatnými jmény a nutit je do adjektiv znamená vymýšlet slova, která nikdo neřekne. Pravidlo tedy není tvar jména, ale postup:

1. Ověřte, že to vůbec má být boolean. Stav, u kterého vás zajímá i okamžik, patří do timestampu (read_at nese obojí zadarmo). Víc než dva stavy patří do výčtu. A hodnotu, kterou spočítáte z jiného sloupce, ukládat nemusíte vůbec: jestli je kategorie podkategorií, poznáte podle toho, že má vyplněné parent_id.
2. Zkuste přídavné jméno nebo příčestí: published, visible, pinned. Vždy pozitivní polarita, protože WHERE NOT disabled je hlavolam.
3. Když adjektivum nesedí, podívejte se na věc jinak: use_avatar → avatar_enabled, hide_price → price_visible, noindex → indexable.
4. Jinak is_. To není selhání, to je legitimní východisko. Oprávnění pak vždy can_.

Proč postup, a ne tvar? Protože hledání adjektiva je diagnostické. Prošel jsem si stovku booleanů napříč deseti databázemi a u každého se snažil najít jméno bez prefixu. Vypadlo z toho sedm sloupců, které vůbec neměly být boolean, dva odvoditelné (a tedy ke smazání) a hrst případů obrácené polarity. Mechanický prefix by tohle všechno zabetonoval.

Druhý argument je, že hlavní povrch není SQL, ale šablona:

{if $user->avatarsVisible}    čte se jako věta
{if $user->isShowAvatars}     ???

isXxx je napříč ekosystémem tvar metody, takže property s tímhle jménem je vizuální falešný přítel a zavírá dveře k rozdělení na property active a getter isActive().

Sloupec je pak vždycky tinyint(1) NOT NULL s defaultem, jehož hodnota se řídí sémantikou bezpečného stavu: enabled klidně DEFAULT 1. Jméno se kvůli estetice nuly neotáčí.

7. VARCHAR(255) je kargo-kult

To číslo pochází z historických limitů: jednobajtový délkový prefix fungoval do 255 bytů a starý limit klíče byl 767 B. V utf8mb4 neplatí ani jedno. 255 znaků je až 1020 bytů (a prefix je stejně dvoubajtový) a limit klíče je dnes 3072 B.

Dnes 255 neoptimalizuje vůbec nic. Je to jen číslo, které jsme viděli v cizí tabulce.

Délka je deklarace domény a strict mód ji vynucuje. Nejlepší jsou čísla, která obhájíte normou: email varchar(254) podle RFC, country char(2) podle ISO 3166, variable_symbol varchar(10), protože delší ho banka nevezme.

Jenže u spousty sloupců žádná norma neexistuje a musím se přiznat, že slug varchar(100) je taky kargo kult, jen o generaci mladší. Sto znaků nevychází z ničeho než z pocitu, že delší adresa je nesmysl. Rozdíl proti 255 je jediný, zato podstatný: u té stovky víte, proč jste ji zvolili. Je to strop, který jste si vědomě určili, ne číslo opsané z cizí tabulky. Účelem stropu je dát datům mez a odstřihnout odpad, ne trefit magickou konstantu.

Pozor na TEXT: je to 64 KB bytů, ne znaků. Na HTML obsah článků raději rovnou MEDIUMTEXT, 64 KB umí být překvapivě těsných.

A když je množina hodnot malá a pevná, ENUM chyba není. Zabírá jeden bajt v každém indexu, povolené hodnoty přečte admin z information_schema a postaví z nich dropdown, a přidání hodnoty na konec je ALGORITHM=INSTANT. Cenu má jinde: ORDER BY řadí podle interního pořadí, ne podle textu, a WHERE status = 1 porovnává s pozicí, ne s hodnotou. A přejmenování je „rozšiř, přesyp, ukliď“, jak jsem popisoval dřív.

8. Peníze jsou DECIMAL

FLOAT a DOUBLE jsou ban pro všechno, co se počítá nebo přesně porovnává. Binární aproximace, chyby se v SUM() sčítají, = 0.3 nefunguje. Float nechte na měření, kde je aproximace podstatou věci: senzor, souřadnice.

Zajímavější spor je DECIMAL versus celočíselné haléře. V PHP je držet částku jako int vynásobený stem naprosto rozumné, protože jazyk přesný desetinný typ nemá. Jenže co je dobré v aplikaci, není automaticky dobré ve sloupci: v databázi haléře vynášejí interpretaci ven ze schématu a jednou to někdo vypíše bez dělení stem. Ideální dělba práce je proto DECIMAL ve schématu a celá čísla nebo money objekt v PHP, s konverzí na jednom místě. Driver vám DECIMAL vrací jako string právě proto, že přesný typ v PHP neexistuje; nepřetypovávejte ho na float, tím zahodíte přesně to, co jste si sloupcem koupili.

Scale volte podle domény, ne z generické opatrnosti: DECIMAL(9,2) na částky, (9,4) na jednotkové ceny a sazby, (12,6) na kurzy. Nezápornost řešte přes CHECK, ne UNSIGNED, to je u DECIMAL od 8.0.17 deprecated.

A past, na kterou vás neupozorní ani strict mód: vložení hodnoty s vyšší přesností se tiše zaokrouhlí, jen s warningem. Zaokrouhlování je doménové rozhodnutí, dělejte ho vědomě. Mimochodem, ROUND v MySQL je half away from zero, bankovní zaokrouhlování tu neexistuje.

9. ON DELETE podle povahy vztahu

Cizí klíče explicitně a s oběma klauzulemi. ON UPDATE CASCADE jako výchozí. U umělých klíčů se prakticky nikdy neuplatní, protože id se nemění, takže vás nic nestojí. Zato ve chvíli, kdy na přečíslování opravdu dojde (ruční slučování dat, srovnání id mezi prostředími), provede ho atomicky, místo aby vás dohnalo k SET FOREIGN_KEY_CHECKS=0. To je teprve nebezpečí, protože s vypnutou kontrolou se odkazy neaktualizují a vzniknou sirotci.

ON DELETE má jednu jedinou rozhodovací otázku: je dítě součástí rodiče, nebo jen nezávislá věc, která na něj ukazuje?

vztah	pravidlo	příklad
kompozice	CASCADE	order_item → customer_order, pivoty
asociace	RESTRICT	post.author_id → user
odpojení se smyslem	SET NULL	log.user_id po anonymizaci

SET NULL je nejhorší možný default. Reference se vypaří, dítě zůstane viset a navíc vás to nutí do nullable sloupce. Použijte ho jen tam, kde osiřelé dítě dál dává smysl a to NULL něco znamená.

Dvě pasti vás pak dohoní jinde. Kaskádové mazání nespouští triggery, protože to InnoDB řeší pod SQL vrstvou. Invariant držený triggerem se tedy kaskádou tiše obejde. A kaskáda je jen pro hard delete: při soft delete žádný reálný DELETE nepřijde a celá vaše pečlivě promyšlená kaskáda je mrtvý kód.

10. CHECK na invarianty, ne na politiku

Začněme tím, že CHECK constrainty v MySQL existují a vy je chcete. Do verze 8.0.16 je server tiše parsoval a ignoroval, což je nejspíš důvod, proč je půlka lidí dodnes považuje za výsadu Postgresu a druhá půlka jim nevěří. Dnes se vynucují, takže jde o plnohodnotný nástroj, jak dostat doménové pravidlo z aplikace do schématu.

Otázka pak zní, co do nich patří. Osa je tahle: doménový invariant versus business politika. Invariant je nadčasová pravda (price >= 0, konec není před začátkem) a patří do schématu. Politika je měnitelné pravidlo („objednávka nad 10 000 jde na schválení“), to se za rok změní a CHECK by znamenal migraci.

Nejlepší využití je to, co jiný mechanismus zapsat neumí: vztahy mezi sloupci téhož řádku a podmíněná povinnost, což je implikace A -> B zapsaná jako NOT A OR B:

CONSTRAINT `chk_order_shipped_needs_date`
    CHECK (`status` <> 'shipped' OR `shipped_at` IS NOT NULL),

Všimněte si jména. Constrainty pojmenovávejte podle pravidla, ne podle sloupců, protože tohle jméno uvidí uživatel v chybové hlášce. Test: přečtete jméno v logu a víte, co se stalo, aniž byste otevřeli schéma.

Co CHECK naopak neumí: cokoli přes víc řádků (nemá poddotazy), cokoli nedeterministického (CURDATE() je zakázaný, takže ani birthdate <= CURDATE()), a nesmí odkazovat na AUTO_INCREMENT sloupec. A nepřidávejte redundantní CHECK tam, kde totéž zaručí levnější mechanismus. CHECK (age >= 0) nad TINYINT UNSIGNED je jen šum plus cena na každý zápis.

---

Titulek sliboval deset a tady měl článek správně skončit. Jenže tři pravidla, co zbývají, patří k těm, jejichž porušení bolí nejdéle, a vyhodit je kvůli kulatému číslu v nadpisu by bylo přesně to rozhodnutí, jaké tady celou dobu kritizuju: dát na vzhled místo na důvod. Takže jedenáct. A dvanáct. A když už jsme u toho, třináct.

11. Collation: 0900, jinak vám CHECK jen tak leží

utf8mb4 vždy, nikdy utf8 (to je tříbajtový alias, který rozbije emoji). To asi víte. Míň se ví, že collation patří do rodiny utf8mb4_0900_*, tedy UCA 9.0. Legacy utf8mb4_czech_ci, unicode_ci (UCA 4.0.0!), general_ci ani utf8mb4_bin nemají v novém schématu důvod existovat.

A teď to důležité, protože tohle se nedozvíte z dokumentace, ale z provozu. Porovnání i REGEXP v CHECKu respektují collation sloupce. Pod výchozím _ai_ci tenhle constraint klidně pustí 'CS' i 'cs_CZ':

CHECK (`lang` REGEXP '^[a-z]{2}$')

Ta část regulárního výrazu, která rozlišuje velikost písmen, tam nedělá vůbec nic. Formátový a členský CHECK má smysl jen nad sloupcem s _bin, ascii_bin nebo _as_cs collation.

Stejná past kousne v triggeru z pravidla 5. Bez toho COLLATE utf8mb4_0900_bin by pod výchozí collation nebyla oprava „reditel“ na „ředitel“ považovaná za změnu. Doplnění diakritiky, tedy naprosto typická česká editace, by datem poslední úpravy ani nehnulo.

12. Ascii sloupec vrátí 500 místo 404

Skoro v každém schématu se najde pár sloupců, ve kterých z definice leží jen ascii: slug článku, kód kupónu, přihlašovací jméno, API token. CHARACTER SET ascii se u nich nabízí jako chytrá volba, protože v klíči rezervuje jeden bajt na znak místo čtyř. Jenže charset se nevolí podle toho, co ve sloupci leží, ale podle toho, s čím se porovnává. A tyhle sloupce mají ještě jednu společnou vlastnost: jsou to přesně ty, podle kterých se vyhledává, takže hodnota v podmínce nepochází z vaší tabulky, ale zvenčí. A tam vám kdokoli pošle cokoli.

-- slug varchar(100) CHARACTER SET ascii
SELECT * FROM post WHERE slug = 'muj-clanek';  -- projde
SELECT * FROM post WHERE slug = 'můj-článek';  -- ERROR 1267 Illegal mix of collations

Spojení je utf8mb4, takže hodnota zvenčí dorazí jako utf8mb4. Dokud jsou v ní jen ascii znaky, MySQL ji tiše převede a porovná. Jakmile obsahuje jediný znak navíc, převod možný není a dotaz skončí chybou, ne prázdným výsledkem. V tom je ten rozdíl: hodnota, která v tabulce není, se normálně prostě nenajde, kdežto tenhle dotaz se vůbec nezeptá. Aplikace tak nedostane šanci vrátit 404 a návštěvník dostane pětistovku, protože do adresy zaklikal háček. Testy to nechytí, ty zkoušejí existující a neexistující hodnoty, ne hodnoty s diakritikou.

Ověřil jsem si to na MySQL 8.4 a nedělá to _bin, ale charset ascii: padá každý řetězcový operátor od = po CONCAT, a rozhoduje obsah toho řetězce, ne data v tabulce, takže spadne i prázdná tabulka. Obejít to jde přes CONVERT(? USING ascii), jenže musí být na každém dotazu a první zapomenuté místo past vrací.

Otázka proto nezní „jsou ta data ascii?“, ale „může se do porovnání dostat neascii hodnota?“. Ascii si nechte na sloupce, které plní výhradně aplikace a nikdo se podle nich zvenčí neptá (lang, country, ip_address). Všechno ostatní dostane utf8mb4_0900_bin, který umí totéž, jen zabere v indexu víc místa. Chybná ascii volba je výpadek, chybná utf8mb4 volba pár bajtů navíc.

13. Primární klíč se propaguje

INT UNSIGNED AUTO_INCREMENT jako výchozí primární klíč, BIGINT u tabulek, do kterých se jen sype.

Proč se zrovna u primárního klíče vyplatí šetřit byty způsobem, který by jinde byl předčasná optimalizace? Protože v InnoDB je PK fyzicky součástí každého sekundárního indexu a musí být typově identický v každém cizím klíči, který na něj míří. Jeho velikost neplatíte jednou, ale (1 + počet indexů + počet odkazů)krát.

Dvě věci se přitom běžně přehlédnou:

• AUTO_INCREMENT se řídí maximem, ne počtem řádků. Nikdy nerecykluje a díry po rollbacích nebo INSERT IGNORE už nikdo nezaplní. Fronta nebo log vyčerpá INT s pár miliony živých řádků. Dívejte se na to, kolik se za život tabulky vloží, ne kolik jich tam bude ležet.
• Riziko je asymetrické. Poddimenzování je produkční incident: INSERT padá a ALTER na BIGINT je dlouhá těžká operace v nejhorší možnou chvíli. Předimenzování stojí čtyři byty na řádek. Když váháte, berte BIGINT.

UNSIGNED má smysl na INT, kde zdvojnásobení na 4,29 miliardy často rozhodne, že BIGINT nepotřebujete. Na BIGINT už ne: signed rozsah je absurdní sám o sobě a horní polovina je z PHP stejně nedosažitelná.

Zbytek už je hygiena

Věci, které si vlastní pravidlo nezaslouží, ale sežerou vám odpoledne, když je neuděláte:

• Index a constraint vždycky pojmenujte sami. MySQL jinak vyrobí post_ibfk_1 a post_chk_1, tedy jména číslovaná podle pořadí vzniku. Stačí, aby někdo constraint zahodil a přidal znovu, nebo aby tabulka jinde vznikla z dumpu místo z migrací, a číslování se rozejde. DROP FOREIGN KEY post_ibfk_2 pak na jednom prostředí projde a na druhém spadne, nebo zahodí něco úplně jiného.
• Jména hybridně: indexy bez prefixu, constrainty s prefixem (fk_post_blog, chk_post_lang_format). Rozdíl má důvod: jméno constraintu je součást chybové hlášky, takže patří tam, kde ho čtenář potká, kdežto jméno indexu čte jen ten, kdo ladí dotazy.
• Index technický, nebo výkonnostní? Ten pod cizím klíčem a UNIQUE na přirozeném klíči existuje od začátku, to nejsou optimalizace, ale constrainty. Ostatní vznikají až podle reálného dotazu, z EXPLAIN a slow logu. Podle toho je i pojmenujte: technický podle sloupců, protože jeho obsah je jeho účel, výkonnostní podle dotazu, který obsluhuje (post_listing).
• Index, který žádný dotaz nepoužívá, je čistá cena. Zpomaluje každý zápis a žere buffer pool.
• Leftmost prefix: index (a, b, c) obslouží podmínky na a, a+b i a+b+c, ale ne na b samotné. Z toho recept: nejdřív sloupce porovnávané na rovnost, potom nejvýš jeden pro rozsah nebo řazení. Za rozsahovým sloupcem už index pro filtrování nepokračuje.
• PK je v InnoDB implicitně na konci každého sekundárního indexu. Takže ho tam neopakujte, covering index vám často vyjde zadarmo, a je to hlavní důvod pravidla 13.
• Sloupec s malou kardinalitou neindexujte samostatně, boolean vám nic nevyfiltruje. Jako složka kompozitu na správné pozici je ale úplně v pořádku.
• Index (a) vedle (a, b) je zbytečný, leftmost prefix ho pokrývá.
• FK sloupec musí typem přesně kopírovat cílový PK včetně UNSIGNED, jinak ten cizí klíč vůbec nevytvoříte.

Kdy tohle všechno porušit

Neporušitelné jsou jen nulté předpoklady (strict mód, utf8mb4, explicitní cizí klíče) a fakta o MySQL, protože co nejde vytvořit, to nejde. Všechno ostatní smíte porušit s důvodem, který by obstál jako zápis rozhodnutí: konkrétní, zapsaný, a přežil by otázku „proč to tady takhle je?“ za dva roky. „Nelíbí se mi to“ není důvod. „Tahle tabulka má 500 milionů řádků a rebuild nepřežijeme“ je.

Jen ať je ta odchylka vidět tam, kde ji čtenář potká, tedy v COMMENT sloupce nebo tabulky. Ne v commit message, ne v Confluence a rozhodně ne ve vaší hlavě. SHOW CREATE TABLE je jediná dokumentace, která cestuje spolu s daty.

Konvence bez únikové cesty se totiž buď obchází tajně, nebo blokuje práci 🙂

A proč vlastně tuhle energii sypat do schématu, když v aplikaci se to napíše rychleji? Protože aplikaci jednou přepíšete. Data ne.