接手過幾個(gè)維護(hù)期項(xiàng)目之后你會(huì)慢慢發(fā)現(xiàn)一個(gè)規(guī)律線上數(shù)據(jù)變臟往往不是業(yè)務(wù)代碼的bug而是約束沒設(shè)計(jì)好。重復(fù)的手機(jī)號(hào)能注冊(cè)、子訂單引用了不存在的父訂單、員工表里age字段存了-3……這些問題的根子大多在建模階段就沒想清楚約束該怎么加。約束這東西建表的時(shí)候順手寫上幾行似乎沒什么技術(shù)含量可等到數(shù)據(jù)出問題那天你才會(huì)意識(shí)到它才是數(shù)據(jù)庫最便宜、最可靠的那道防線。這篇內(nèi)容沒什么高深理論就是把MySQL里五類約束——非空、唯一、主鍵、默認(rèn)值、外鍵——掰開揉碎講清楚。每個(gè)約束解決什么業(yè)務(wù)問題、建表時(shí)怎么寫、線上怎么改、有哪些坑全部覆蓋。適合正在學(xué)數(shù)據(jù)庫的初學(xué)者也適合準(zhǔn)備重構(gòu)老表結(jié)構(gòu)、想補(bǔ)上約束缺口的中級(jí)開發(fā)者。1. 約束的本質(zhì)數(shù)據(jù)質(zhì)量的聲明式防線先說一個(gè)容易忽略的事實(shí)約束其實(shí)是數(shù)據(jù)庫對(duì)業(yè)務(wù)規(guī)則的一種聲明式表達(dá)。你在建表語句里寫下一行約束相當(dāng)于告訴數(shù)據(jù)庫引擎凡是違反這條規(guī)則的數(shù)據(jù)一律不許進(jìn)來。 這比在應(yīng)用層寫一百個(gè)if判斷都可靠——因?yàn)椴还軘?shù)據(jù)是從哪個(gè)接口進(jìn)來的、是哪個(gè)同事用腳本灌的、還是DBA手工刷的只要走到這張表就必須過約束這道閘。約束的哲學(xué)在于防而不是糾。應(yīng)用層校驗(yàn)是軟性的總有漏網(wǎng)之魚而且漏掉的時(shí)候你往往不知道約束是硬性的違反就報(bào)錯(cuò)從源頭保證你查出來的數(shù)據(jù)永遠(yuǎn)是你期望的形狀。我見過太多團(tuán)隊(duì)對(duì)約束不上心覺得反正Java里都校驗(yàn)過了結(jié)果換了個(gè)臨時(shí)腳本批量刷數(shù)據(jù)直接把臟數(shù)據(jù)灌進(jìn)生產(chǎn)庫。這種教訓(xùn)一次就足夠讓人長記性。具體來說MySQL的約束體系分五條主線NOT NULL非空約束聲明這一列必須有值。UNIQUE唯一約束聲明這一列或列組合不可重復(fù)。PRIMARY KEY主鍵約束非空唯一的合體同時(shí)承擔(dān)定位一行數(shù)據(jù)的職責(zé)。DEFAULT默認(rèn)值約束不填時(shí)給一個(gè)兜底值。FOREIGN KEY外鍵約束聲明這一列的值必須存在于另一張表的某列中。這五類約束不是孤立存在的它們經(jīng)常組合出現(xiàn)。舉個(gè)例子用戶表的手機(jī)號(hào)字段CREATE TABLE user ( id BIGINT PRIMARY KEY AUTO_INCREMENT, phone VARCHAR(20) NOT NULL UNIQUE, nickname VARCHAR(50) NOT NULL, status TINYINT NOT NULL DEFAULT 1 );這一條建表語句里NOT NULL UNIQUE就是兩個(gè)約束的組合手機(jī)號(hào)既不能為空也不能重復(fù)。DEFAULT 1保證了只要業(yè)務(wù)方不顯示傳status進(jìn)來就是正常狀態(tài)。這就是約束組合拳的日常用法。有些約束是列級(jí)約束寫在字段定義后面比如NOT NULL和DEFAULT有些是表級(jí)約束寫在字段列表之后比如把多列組合成一個(gè)唯一鍵。搞清楚這個(gè)區(qū)別等會(huì)兒寫復(fù)雜約束的時(shí)候就不會(huì)暈。提示MySQL 8.0及以上版本建表時(shí)若定義了約束但沒起名字系統(tǒng)會(huì)自動(dòng)生成約束名。唯一約束默認(rèn)叫字段名_2外鍵默認(rèn)叫表名_ibfk_1這種格式。線上環(huán)境建議顯式命名不然排錯(cuò)的時(shí)候光找約束名都?jí)蚰愫纫粔亍?. 五種約束逐個(gè)拆解語法、適用場(chǎng)景與暗坑這節(jié)我們從業(yè)務(wù)視角過一遍每種約束。直接給結(jié)論再解釋為什么這么用。2.1 非空約束比你想的更底層非空約束在MySQL里不是一個(gè)獨(dú)立的約束對(duì)象它就是列定義的一部分。語法極簡CREATE TABLE t_example ( name VARCHAR(50) NOT NULL, email VARCHAR(100) NULL );NULL和NOT NULL的區(qū)別用一句話概括NULL表示不知道或者沒有它參與任何計(jì)算都會(huì)把結(jié)果染成NULL。比如SELECT score 10 FROM student如果score是NULL結(jié)果是NULL而不是10。這就是為什么很多字段必須聲明NOT NULL——不是為了好看是為了避免業(yè)務(wù)代碼里到處躲NULL指針。暗坑之一空字符串和NULL是兩回事。是一個(gè)具體值NULL是沒有值。很多初學(xué)者以為WHERE name 張三能把name為NULL的行也查出來其實(shí)查不出來。NULL不參與任何比較運(yùn)算只能用IS NULL去查。所以如果你的業(yè)務(wù)里沒有填寫和填了空串語義不同一定要想清楚是否加非空約束。2.2 唯一約束不是你想的只是不重復(fù)唯一約束的邏輯聲明是這一列或這幾列的組合在整個(gè)表中不允許重復(fù)值。有意思的是MySQL實(shí)現(xiàn)唯一約束的方式是自動(dòng)創(chuàng)建一個(gè)唯一索引。你建一個(gè)唯一約束等于同時(shí)建了一個(gè)索引。所以唯一約束不僅能防重復(fù)還能加速查詢。CREATE TABLE user ( id INT PRIMARY KEY AUTO_INCREMENT, phone VARCHAR(20), UNIQUE KEY uk_phone (phone) );這里我給了約束一個(gè)顯式名字uk_phone后面刪除或修改約束時(shí)能直接找到它。需要注意兩個(gè)細(xì)節(jié)。第一唯一約束允許NULL重復(fù)。在MySQL里NULL被視為未知未知和未知不相等所以你可以插很多條phone NULL的記錄而不觸發(fā)唯一約束。如果你的業(yè)務(wù)要求手機(jī)號(hào)要么不填填了就絕不能重復(fù)或者有手機(jī)號(hào)的行最多一條那光靠UNIQUE是不夠的還得配合其他邏輯。這是個(gè)經(jīng)常被忽略的邊界。第二刪除唯一約束的語法不是DROP CONSTRAINT而是DROP INDEX因?yàn)樗谋举|(zhì)是索引。到了MySQL 8.0雖然支持了DROP CONSTRAINT寫法但底層邏輯依然是索引操作。多列組合唯一也很常見典型場(chǎng)景是同一個(gè)用戶對(duì)同一個(gè)商品只能有一條評(píng)價(jià)記錄CREATE TABLE product_comment ( user_id INT NOT NULL, product_id INT NOT NULL, content TEXT, UNIQUE KEY uk_user_product (user_id, product_id) );注意多列唯一判斷的是組合值重復(fù)而不是單列。用戶1評(píng)論商品A、用戶1評(píng)論商品B兩條都能插入用戶1再評(píng)商品A才觸發(fā)沖突。2.3 主鍵約束非空唯一的合體但更重要主鍵和唯一約束最大的區(qū)別主鍵是行的身份標(biāo)識(shí)它直接和存儲(chǔ)引擎的物理組織方式掛鉤。InnoDB的表就是一棵按主鍵排序的B樹數(shù)據(jù)行直接掛在主鍵上。所以選主鍵是個(gè)策略性問題。日常建表常見寫法CREATE TABLE t_order ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, order_no VARCHAR(64) NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no) );這里有個(gè)非常容易踩的坑業(yè)務(wù)唯一鍵order_no和主鍵id是兩套東西。我見過不少項(xiàng)目設(shè)計(jì)表的人圖省事直接用訂單號(hào)當(dāng)主鍵。當(dāng)時(shí)看沒什么問題等到業(yè)務(wù)發(fā)展、訂單表數(shù)據(jù)量上千萬后訂單號(hào)的隨機(jī)字符串導(dǎo)致B樹索引頻繁頁分裂插入性能肉眼可見地下降。主鍵盡量用自增整數(shù)或有序雪花ID保證插入順序近似遞增。另外主鍵約束的索引名永遠(yuǎn)是PRIMARY所以它和唯一約束在刪除語法上不一樣——主鍵本身不存在刪除約束名這種操作而是直接DROP PRIMARY KEY。InnoDB還有一個(gè)隱藏規(guī)則如果你建表時(shí)不主動(dòng)定義主鍵InnoDB會(huì)優(yōu)先找一個(gè)非空的唯一索引當(dāng)隱藏主鍵都找不到就生成一個(gè)6字節(jié)的隱藏列。這會(huì)導(dǎo)致額外的存儲(chǔ)開銷和索引效率下降。所以任何InnoDB表都應(yīng)該顯式定義主鍵這不只是規(guī)范問題。2.4 默認(rèn)值約束減少業(yè)務(wù)職責(zé)的兜底機(jī)制默認(rèn)值約束比較容易理解插入時(shí)不傳這一列就自動(dòng)填默認(rèn)值。但用起來有三個(gè)細(xì)節(jié)值得展開。第一默認(rèn)值是在INSERT語句沒有顯式指定該列時(shí)生效。如果你顯式寫明傳NULL那就是NULL除非列定義了NOT NULL或DEFAULT有特殊處理邏輯。比如CREATE TABLE t_user ( status TINYINT NOT NULL DEFAULT 1 ); -- 不傳status取1 INSERT INTO t_user (status) VALUES (DEFAULT); -- 顯式傳2取2 INSERT INTO t_user (status) VALUES (2);第二MySQL 8.0對(duì)DEFAULT表達(dá)式做了一次語法調(diào)整。8.0.13之前函數(shù)的默認(rèn)值必須加括號(hào)比如DEFAULT (NOW())8.0.13之后建議直接寫DEFAULT CURRENT_TIMESTAMP也支持表達(dá)式默認(rèn)值了。如果你還停留在5.7的習(xí)慣升級(jí)到8.0后要留意這個(gè)變化。第三默認(rèn)值約束不改變列的可空性。一個(gè)列同時(shí)寫DEFAULT 1但沒寫NOT NULL那插入時(shí)傳NULL照樣能插進(jìn)去。默認(rèn)值只在你完全不提這個(gè)字段時(shí)生效。想讓字段不填就默認(rèn)值、填了NULL就報(bào)錯(cuò)請(qǐng)組合使用NOT NULL DEFAULT 值。這兩者的語義差異業(yè)務(wù)人員討論需求時(shí)經(jīng)常混淆。2.5 外鍵約束跨表完整性但很多人不敢用外鍵約束是把數(shù)據(jù)完整性從單表提升到跨表從表的外鍵列值要么是NULL要么必須存在于主表被引用列中。經(jīng)典場(chǎng)景是訂單明細(xì)表引用訂單主表CREATE TABLE t_order ( id BIGINT PRIMARY KEY, order_no VARCHAR(64) NOT NULL ); CREATE TABLE t_order_item ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_id BIGINT NOT NULL, product_name VARCHAR(100) NOT NULL, CONSTRAINT fk_item_order FOREIGN KEY (order_id) REFERENCES t_order(id) ON DELETE CASCADE ON UPDATE RESTRICT );這里有幾個(gè)必須搞清楚的底層限制被引用的列必須有索引。通常引用主鍵就沒問題如果引用非主鍵列那張表該列必須有唯一約束或普通索引否則MySQL會(huì)報(bào)錯(cuò)——外鍵列上如果沒有索引MySQL會(huì)自動(dòng)創(chuàng)建一個(gè)但在從表上創(chuàng)建是要付出DDL代價(jià)的。外鍵只支持InnoDB。MyISAM雖然允許寫外鍵語句但引擎會(huì)直接忽略它。這是個(gè)極其隱蔽的坑你滿懷信心地寫了FOREIGN KEY以為數(shù)據(jù)完整性有保障了結(jié)果插了臟數(shù)據(jù)也沒報(bào)錯(cuò)因?yàn)镸yISAM根本不理會(huì)你。類型必須嚴(yán)格匹配。主表BIGINT從表INT即使數(shù)值范圍能放得下MySQL也不允許建立外鍵。字符集的排序規(guī)則也得一致。那為什么很多團(tuán)隊(duì)在生產(chǎn)環(huán)境里禁用外鍵主要原因是性能和高并發(fā)下的鎖問題。外鍵約束每次寫入都要去檢查主表多了一次查詢而且在某些場(chǎng)景下會(huì)擴(kuò)大鎖范圍影響并發(fā)。這不是外鍵本身錯(cuò)而是用錯(cuò)了地方。我的建議是核心交易鏈路、并發(fā)量高的表盡量不用外鍵靠應(yīng)用層保證后臺(tái)管理系統(tǒng)、配置表、數(shù)據(jù)一致性要求極強(qiáng)但寫入量低的場(chǎng)景外鍵值得用。嚴(yán)格分級(jí)看待而不是一刀切。3. 約束設(shè)計(jì)的實(shí)戰(zhàn)思路從建表開始就減少未來的坑約束不是寫完就完事的它的設(shè)計(jì)直接關(guān)系到你未來兩年維護(hù)這個(gè)表的幸福感。這節(jié)講講我在實(shí)際項(xiàng)目中是怎么取舍的。3.1 列屬性設(shè)計(jì)的順序先定類型再定約束很多新手建表先糾結(jié)這列該不該有默認(rèn)值其實(shí)順序反了。正確順序是先確定這列存什么類型的數(shù)據(jù)選對(duì)類型整數(shù)、字符串、日期、布爾。再問語義這列能不能為空如果業(yè)務(wù)上沒有和填了空串含義不同就按需加NOT NULL。再問規(guī)則這列允不允許重復(fù)需要的話加UNIQUE。最后問兜底如果不填給什么默認(rèn)值舉個(gè)例子一個(gè)后臺(tái)員工表的last_login_time字段。業(yè)務(wù)上從未登錄是一個(gè)合法的中間狀態(tài)所以這一列允許為NULL比較合理但如果你用DATETIME DEFAULT CURRENT_TIMESTAMP那就意味著創(chuàng)建時(shí)默認(rèn)等于創(chuàng)建時(shí)間這不等于從未登錄語義直接沖突了。所以我傾向于這樣設(shè)計(jì)last_login_time DATETIME NULL DEFAULT NULL這樣查詢時(shí)用IS NULL來判斷從未登錄邏輯既不會(huì)誤傷也不會(huì)產(chǎn)生歧義。3.2 組合約束的幾個(gè)經(jīng)典案例案例A邏輯刪除與唯一約束的沖突業(yè)務(wù)上最常見的需求是同一用戶不能有兩個(gè)未刪除的手機(jī)號(hào)記錄但你做的是邏輯刪除is_deleted物理數(shù)據(jù)還在表里。如果直接在phone上建唯一約束刪掉一條再插同號(hào)會(huì)被卡住。解決方案通常有兩種把刪除標(biāo)記并入唯一鍵UNIQUE KEY uk_phone_deleted (phone, is_deleted)。但問題是同一個(gè)手機(jī)號(hào)被刪兩次(phone, 1)又插不進(jìn)去了。更通用的方案是引入刪除批次號(hào)deleted_batch VARCHAR(32) NOT NULL DEFAULT 然后唯一鍵設(shè)為(phone, deleted_batch)。刪除時(shí)給該行生成一個(gè)UUID批次號(hào)物理上沒有重復(fù)的(phone, uuid)了。這種設(shè)計(jì)常見于需要大量邏輯刪除場(chǎng)景的業(yè)務(wù)表。案例B聯(lián)合唯一是分頁數(shù)據(jù)的天然保證比如一個(gè)角色權(quán)限關(guān)聯(lián)表(role_id, permission_id)聯(lián)合唯一既防重復(fù)又能充當(dāng)查詢索引。一張中間表如果連聯(lián)合唯一都沒有遲早會(huì)插出大量重復(fù)授權(quán)記錄接口返回來滿屏相同數(shù)據(jù)排查起來相當(dāng)痛苦。案例C默認(rèn)值與業(yè)務(wù)狀態(tài)機(jī)配合狀態(tài)字段用TINYINT NOT NULL DEFAULT 0配合一張枚舉表說明每個(gè)數(shù)字的含義。不要在狀態(tài)列上直接設(shè)NULL否則業(yè)務(wù)里空狀態(tài)和無狀態(tài)混在一起后統(tǒng)計(jì)口徑會(huì)崩。3.3 到底要不要所有字段都加NOT NULL網(wǎng)上有種說法是禁止任何字段為NULL這個(gè)觀點(diǎn)過于絕對(duì)。有些字段確實(shí)該允許NULL比如備注擴(kuò)展信息最后登錄時(shí)間。這些字段的NULL值可以被當(dāng)成一種攜帶信息的狀態(tài)。真正的區(qū)別在于NULL是否有業(yè)務(wù)語義。有語義就保留NULL沒語義就一律NOT NULL DEFAULT。我見過一個(gè)比較穩(wěn)妥的分法所有參與運(yùn)算、統(tǒng)計(jì)、比較的數(shù)值字段一律NOT NULL DEFAULT 0所有狀態(tài)位、標(biāo)志位一律NOT NULL DEFAULT 0或NOT NULL DEFAULT 1所有時(shí)間字段按需選擇但創(chuàng)建時(shí)間通常NOT NULL DEFAULT CURRENT_TIMESTAMP自由文本、備注、擴(kuò)展字段允許NULL但查詢時(shí)心里要清楚NULL的語義4. 約束的后期管理改約束比建約束容易踩坑建表時(shí)想清楚約束是最好的但現(xiàn)實(shí)往往是老表已經(jīng)跑了好幾年數(shù)據(jù)里存了一堆臟值這時(shí)候想補(bǔ)約束麻煩就來了。這節(jié)我講幾個(gè)改約束的場(chǎng)景和操作細(xì)節(jié)。4.1 給已有表添加約束給已有列添加非空約束時(shí)MySQL要求該列現(xiàn)有數(shù)據(jù)不能有NULL。所以老表加NOT NULL前通常先做一次數(shù)據(jù)清洗-- 步驟1把NULL值替換為兜底值 UPDATE t_user SET nickname 未填寫 WHERE nickname IS NULL; -- 步驟2修改列屬性 ALTER TABLE t_user MODIFY COLUMN nickname VARCHAR(50) NOT NULL DEFAULT 未填寫;同樣的道理給已有列加唯一約束前得先確認(rèn)沒有重復(fù)數(shù)據(jù)-- 查詢有重復(fù)的組 SELECT phone, COUNT(*) FROM t_user GROUP BY phone HAVING COUNT(*) 1; -- 去重完成后再添加唯一約束 ALTER TABLE t_user ADD UNIQUE KEY uk_phone (phone);這一步如果數(shù)據(jù)量巨大比如千萬級(jí)必須先評(píng)估耗時(shí)。ALTER TABLE在MySQL 5.7及以下版本是鎖表的8.0雖然引入了ALGORITHMINPLACE優(yōu)化但一些場(chǎng)景依然會(huì)鎖。大表加約束務(wù)必在低峰期執(zhí)行或者借助在線變更工具。4.2 刪除與修改約束刪除主鍵ALTER TABLE t_user DROP PRIMARY KEY;刪除唯一約束本質(zhì)刪索引ALTER TABLE t_user DROP INDEX uk_phone;刪除外鍵約束注意要用約束名不是列名ALTER TABLE t_order_item DROP FOREIGN KEY fk_item_order;修改約束通常沒有一條語句直接改的路徑一般是先刪再加。比如你想把某個(gè)唯一約束從單列換成多列組合就要先刪舊約束再ADD新約束。這中間有個(gè)空窗期操作要在事務(wù)或低峰期處理防止期間插入重復(fù)數(shù)據(jù)。4.3 SQL_MODE對(duì)約束的隱性影響約束的行為受sql_mode影響這點(diǎn)容易被忽略。在寬松模式下比如sql_mode不含STRICT_TRANS_TABLES向NOT NULL列插入NULLMySQL不報(bào)錯(cuò)而是把列改成隱式默認(rèn)值只產(chǎn)生一條warning。很多研發(fā)在本地開發(fā)環(huán)境沒開嚴(yán)格模式測(cè)試怎么插都成功上線后在生產(chǎn)環(huán)境被報(bào)錯(cuò)搞得一頭霧水。開發(fā)、測(cè)試、生產(chǎn)三個(gè)環(huán)境的sql_mode保持一致是約束正常工作的前提。MySQL 8.0默認(rèn)sql_mode是帶嚴(yán)格模式的5.7也默認(rèn)開啟。但某些云數(shù)據(jù)庫默認(rèn)配置可能被調(diào)過。建議執(zhí)行下面語句確認(rèn)一下SELECT sql_mode;比較理想的配置至少包含STRICT_TRANS_TABLES和NO_ENGINE_SUBSTITUTION前者保證約束不被悄悄繞過后者防止建表時(shí)引擎被靜默替換。4.4 線上改約束的經(jīng)典事故場(chǎng)景我參與過一次事故排查印象極深某后臺(tái)系統(tǒng)的用戶表mobile列歷史上有大量NULL和重復(fù)值。團(tuán)隊(duì)給mobile加了唯一約束之后當(dāng)天晚上發(fā)現(xiàn)有一個(gè)定時(shí)任務(wù)開始報(bào)錯(cuò)——因?yàn)檫@個(gè)任務(wù)執(zhí)行時(shí)如果手機(jī)號(hào)為空會(huì)往表里插入一條mobile NULL的記錄。字段本身沒有NOT NULL而唯一約束又允許NULL重復(fù)所以當(dāng)時(shí)插入沒失敗問題出在另一個(gè)地方這個(gè)任務(wù)還會(huì)把空手機(jī)號(hào)更新成同一個(gè)兜底字符串空串是具體值唯一約束攔住了第二次更新。結(jié)果就是任務(wù)永遠(yuǎn)卡在第一次更新之后形成了一個(gè)極其隱蔽的循環(huán)失敗。后來怎么解的把mobile的語義徹底分清要么為空NULL要么為用戶真實(shí)手機(jī)號(hào)。加了NOT NULL DEFAULT 后再配合正則校驗(yàn)才把這塊徹底穩(wěn)住。這件事讓我養(yǎng)成了一個(gè)習(xí)慣加約束前把現(xiàn)有數(shù)據(jù)里有哪些非法值完整列一遍再?zèng)Q定兜底策略。別讓約束成為定時(shí)炸彈。5. 約束與索引的協(xié)作關(guān)系一張表的設(shè)計(jì)權(quán)衡約束和索引在MySQL里是糾纏在一起的。唯一約束創(chuàng)建唯一索引主鍵創(chuàng)建主鍵索引外鍵創(chuàng)建普通索引。理解這層關(guān)系你才能在設(shè)計(jì)表時(shí)做出合理的權(quán)衡。約束類型自動(dòng)創(chuàng)建的索引索引類型刪除約束的動(dòng)作PRIMARY KEYPRIMARY聚集索引DROP PRIMARY KEYUNIQUE約束名對(duì)應(yīng)的索引二級(jí)索引唯一DROP INDEX 約束名FOREIGN KEY自動(dòng)生成普通索引若列上已有索引則復(fù)用二級(jí)索引DROP FOREIGN KEY 約束名NOT NULL / DEFAULT不創(chuàng)建索引無改列屬性這張表意味著你每加一個(gè)唯一約束就同時(shí)獲得了一個(gè)查詢加速能力。比如用戶表的phone列加了唯一約束那么按手機(jī)號(hào)登錄時(shí)用的索引覆蓋就有了。同理外鍵列自動(dòng)生成的索引也能加快從表按主表ID反查的速度。但反過來索引不是免費(fèi)午餐。每個(gè)索引都會(huì)增加寫入的開銷因?yàn)椴迦搿⒏?、刪除時(shí)都要同步維護(hù)索引樹。所以約束加得太多太濫也會(huì)讓寫入變慢。設(shè)計(jì)原則是該有的約束必須有不必要的索引一個(gè)也別留。比如一張只有幾十行數(shù)據(jù)的配置表加不加索引無所謂一張日增百萬的流水表每個(gè)多余的索引都是實(shí)打?qū)嵉膶懭霌p耗。還有個(gè)關(guān)聯(lián)點(diǎn)唯一約束和普通索引在去重場(chǎng)景下的選擇。如果你想防重復(fù)必須用唯一約束如果你只想加速查找用普通索引就夠了。后者允許重復(fù)值但寫入開銷更小。搞清楚業(yè)務(wù)自己要的是什么不要為了順手就亂加。在分庫分表的大數(shù)據(jù)架構(gòu)下唯一約束的效果會(huì)打折——唯一性只能在單個(gè)分片內(nèi)保證跨分片就無能為力了。這種場(chǎng)景下唯一ID通常由全局發(fā)號(hào)器生成數(shù)據(jù)庫約束已經(jīng)退居二線。如果是單庫單表的項(xiàng)目約束依然是性價(jià)比最高的防線先把數(shù)據(jù)庫約束吃透再談中間件層的高級(jí)方案。6. 一張表描述全部約束設(shè)計(jì)的完整示例最后用一個(gè)相對(duì)完整的示例把上面所有內(nèi)容串起來。假設(shè)我們?cè)O(shè)計(jì)一張簡單的訂單表業(yè)務(wù)規(guī)則是訂單號(hào)必填且唯一、下單用戶必填、訂單金額默認(rèn)0、狀態(tài)默認(rèn)待支付、備注可空、關(guān)聯(lián)的用戶必須存在。CREATE TABLE t_order ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主鍵, order_no VARCHAR(32) NOT NULL COMMENT 訂單號(hào), user_id BIGINT UNSIGNED NOT NULL COMMENT 下單用戶ID, amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 訂單金額, status TINYINT NOT NULL DEFAULT 0 COMMENT 0待支付 1已支付 2已取消, remark VARCHAR(255) NULL COMMENT 備注, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 創(chuàng)建時(shí)間, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新時(shí)間, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_id (user_id), CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES t_user(id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;這個(gè)設(shè)計(jì)的邏輯是這樣的id自增主鍵保證行物理順序插入有序避免隨機(jī)主鍵帶來的頁分裂。order_no是業(yè)務(wù)單號(hào)單獨(dú)做唯一約束天然防重復(fù)下單。user_id建外鍵關(guān)聯(lián)用戶表保證訂單不會(huì)落到不存在的用戶頭上。如果并發(fā)量極高這一條可以拆掉放到應(yīng)用層校驗(yàn)但一般的后臺(tái)業(yè)務(wù)外鍵完全扛得住。amount用DECIMAL(10,2)而不是FLOAT避免浮點(diǎn)誤差默認(rèn)0保證統(tǒng)計(jì)時(shí)不出現(xiàn)NULL陷阱。status用TINYINT NOT NULL DEFAULT 0并注釋清楚狀態(tài)含義。created_at和updated_at都是數(shù)據(jù)庫層維護(hù)應(yīng)用層不用管。這里面其實(shí)還隱含了一個(gè)設(shè)計(jì)細(xì)節(jié)KEY idx_user_id (user_id)。雖然外鍵會(huì)自動(dòng)建索引但如果你決定不用外鍵這個(gè)索引得手動(dòng)建否則按用戶查訂單的SQL會(huì)全表掃。約束可以拆掉索引不要忘記留。7. 實(shí)際維護(hù)中的幾個(gè)個(gè)人體會(huì)約束這東西建表時(shí)多花十分鐘琢磨能替你在未來省下幾個(gè)通宵。說幾個(gè)我自己的堅(jiān)持第一任何表都必須有主鍵尤其是InnoDB表。就算業(yè)務(wù)上找不到天然的唯一鍵建一個(gè)自增id成本最低、收益最高。沒有主鍵的InnoDB表在復(fù)制、備份、數(shù)據(jù)修正時(shí)都會(huì)出各種幺蛾子。第二唯一約束的語義要落到字段組合而不是直覺。好好理清哪些列組合起來才是業(yè)務(wù)上的唯一比到處亂加約束重要得多。一個(gè)約束加錯(cuò)后面業(yè)務(wù)方會(huì)天天來提工單。第三改約束前先備份、先查重復(fù)、先看執(zhí)行計(jì)劃。一套組合拳下來線上事故率會(huì)低很多。尤其是大表加唯一約束一定要先在從庫或測(cè)試環(huán)境完整跑一遍算好時(shí)間窗口再動(dòng)生產(chǎn)。第四把約束的刪除腳本留在項(xiàng)目的數(shù)據(jù)庫變更記錄里。哪天約束定義有變化你能清楚地從歷史記錄里看到這個(gè)字段以前是什么約束、為什么改。這比靠記憶靠譜得多。最后分享一個(gè)小技巧用SHOW CREATE TABLE查看別人建的表時(shí)先掃一遍約束定義再掃字段定義。約束規(guī)劃往往比字段類型更能反映設(shè)計(jì)者對(duì)業(yè)務(wù)的思考深度。你讀懂了約束就讀懂了半張表。