阿里巴巴MySQL規(guī)范，五千字版，這次全了（建議收藏）

程序員的成長之路

互聯(lián)網(wǎng)/程序員/技術(shù)/資料共享 

閱讀本文大概需要 10 分鐘。

來自：架構(gòu)師之路

推薦一個程序員編程資料站：

http://cxyroad.com

tips：

2024年IDEA最新激活方法教程，后臺回復(fù)：激活碼

CSDN免登錄復(fù)制代碼插件下載：CSDN復(fù)制插件

以下是正文。

---

之前分享了阿里巴巴MySQL【強(qiáng)制】規(guī)范，大伙反饋不過癮，希望補(bǔ)充【推薦】規(guī)范與【參考】規(guī)范。好滴，滿足大家的心愿。

畫外音：補(bǔ)充了很多信息，文章較長，建議收藏。

規(guī)范內(nèi)容取自“阿里巴巴MySQL規(guī)范”（黃山版），發(fā)布時間為2022.2.3，開源。

第一部分：建表規(guī)范

【強(qiáng)制】規(guī)范一：是否字段

1. 表達(dá)是否概念的字段，必須使用is_xxx的形式命名；

2. 數(shù)據(jù)類型必須是 unsigned tinyint；

3. 1表示是，0表示否；

舉例：是否刪除

正確：使用is_deleted，1表示刪除，0表示未刪除

錯誤：deleted, if_deleted, delete_or_not

【強(qiáng)制】規(guī)范二：字母與數(shù)字

1. 表名，字段名禁止出現(xiàn)大寫；

畫外音：MySQL在Windows下不區(qū)分大小寫，Linux下雖然區(qū)分，但為了避免節(jié)外生枝，統(tǒng)一禁止大寫。

2. 禁止數(shù)字開頭，禁止兩個下劃線中間只有數(shù)字；

舉例：

正確：aliyun_admin，level3_name

錯誤：AliyunAdmin，level_3_name

【強(qiáng)制】規(guī)范三：表名禁止使用復(fù)數(shù)

表名表示實(shí)體內(nèi)容，不是實(shí)體數(shù)量，禁止使用復(fù)數(shù)。

【強(qiáng)制】規(guī)范四：禁止使用保留字

常見的例如：desc，range，match，delayed...

【強(qiáng)制】規(guī)范五：主鍵，唯一索引，普通索引命名規(guī)范

1. 主鍵索引名：pk_xxx

畫外音：primary key

2. 唯一索引名：uk_xxx

畫外音：unique key

3. 普通索引名：idx_xxx

畫外音：index

【強(qiáng)制】規(guī)范六：小數(shù)類型規(guī)范

1. 小數(shù)類型使用decimal；

2. 禁止使用float和double；

畫外音：float和double存在精度損失，比較的時候，可能得到意想不到的結(jié)果。

3. 如果范圍超過decimal，可以拆成整數(shù)與小數(shù)分開存儲；

【強(qiáng)制】規(guī)范七：字符串長度非常相近，必須使用定長char

畫外音：預(yù)先分配存儲空間，不會觸發(fā)重新分配。

【強(qiáng)制】規(guī)范八：可變字符串規(guī)范

1. 如果字符串長度較長，且內(nèi)容長度差異較大，使用varchar；

畫外音：不預(yù)先分配存儲空間，比較節(jié)省空間。

2. 如果字符串長度大部分超過5000，使用text，獨(dú)立出一張表單獨(dú)存儲；

畫外音：避免影響主表其他字段索引效率。

【強(qiáng)制】規(guī)范九：強(qiáng)制字段

1. 必須具備id字段：類型為bigint unsigned，單表時自增，步長為1，不具備業(yè)務(wù)含義；

2. 必須具備create_time字段：類型為datetime（除非記錄時區(qū)信息，使用timestamp）；

3. 必須具備update_time字段：同上；

【強(qiáng)制】規(guī)范十：禁止進(jìn)行物理刪除操作

畫外音：邏輯操作保留了數(shù)據(jù)資產(chǎn)的同時，能夠追溯操作行為。

【推薦】規(guī)范十一：表名建議

1. 表名建議遵循“業(yè)務(wù)名稱_表的作用”；

舉例：alipay_task

【推薦】規(guī)范十二：庫名建議

1. 庫名建議與應(yīng)用名稱一致；

【推薦】規(guī)范十三：字段修改

1. 如果修改字段含義，或者追加字段狀態(tài)，建議同步更新注釋；

【推薦】規(guī)范十四：數(shù)據(jù)冗余

允許通過數(shù)據(jù)冗余來提高查詢性能，但要考慮數(shù)據(jù)一致性，冗余的字段建議遵循：

1. 非頻繁修改；

2. 非唯一索引；

3. 非vahcar超長字段；

4. 非text字段；

【推薦】規(guī)范十五：分庫分表

以下情況建議分庫分表：

1. 單表數(shù)據(jù)超過500W行；

2. 單表容量超過2GB；

畫外音：預(yù)計3年內(nèi)達(dá)不到1或2，不建議分庫分表。

【參考】規(guī)范十六：使用恰當(dāng)?shù)臄?shù)據(jù)類型

1. 無負(fù)數(shù)可使用無符號類型，還能擴(kuò)大表示范圍；

2. 以下是一些典型業(yè)務(wù)場景的類型參考：

畫外音：選擇合適的類型，能節(jié)約表空間，節(jié)約索引空間，提升檢索速度。

第二部分：索引規(guī)范

【強(qiáng)制】規(guī)范一：唯一索引規(guī)范

1. 業(yè)務(wù)上具備唯一特性的字段，即使是組合字段，也必須建立成唯一索引。

畫外音：

1. 唯一索引雖然影響插入速度，但針對于互聯(lián)網(wǎng)大數(shù)據(jù)量高并發(fā)量的數(shù)據(jù)存儲場景來說，插入的影響可以忽略不計，查詢效率的提升是主要矛盾；

2. 應(yīng)用層的唯一檢查是不夠的；

【強(qiáng)制】規(guī)范二：join規(guī)范

1. 超過三個表時，禁止join；

2. 需要join的字段，數(shù)據(jù)類型必須絕對一致；

3. 被關(guān)聯(lián)的字段必須要有索引；

畫外音：

1. 針對于互聯(lián)網(wǎng)大數(shù)據(jù)量高并發(fā)量的數(shù)據(jù)存儲場景來說，join對性能的潛在影響較大；

2. 數(shù)據(jù)類型不對，沒有索引，對性能的潛在影響較大；

【強(qiáng)制】規(guī)范三：varchar規(guī)范

1. 沒有必要對過長的varchar全字段建立索引；

2. varchar字段上的索引必須指定索引長度；

3. 索引長度可參考文本區(qū)分度，索引長度N可用count(distinct left(column, N))/count(*)來測試；

畫外音：

1. 基于性能考慮；

【強(qiáng)制】規(guī)范四：模糊搜索規(guī)范

1. 禁止左模糊或者全模糊查詢；

2. 如果有相關(guān)業(yè)務(wù)需求，必須走搜索引擎方案解決；

畫外音：

1. 基于性能考慮；

【推薦】規(guī)范五：order by規(guī)范

1. order by場景要注意組合索引的順序，order by的字段應(yīng)該放在組合索引的最后；

舉例：

where a=? and b=? order by c

可以使用a_b_c索引

但是要注意：

where a>? order by b

無法使用a_b索引

【推薦】規(guī)范六：利用索引覆蓋來進(jìn)行查詢，可以避免回表

說明：

索引分為主鍵索引、唯一索引、普通索引三種，覆蓋索引只是一種查詢效果，explain時，extra會出現(xiàn)using index。

畫外音：

索引覆蓋詳見《被查詢的列，為啥要放到索引里？》explain詳見《MySQL性能調(diào)優(yōu)，必須掌握這一個工具！》《MySQL性能調(diào)優(yōu)，Explain-extra》

【推薦】規(guī)范七：利用延遲關(guān)聯(lián)或者子查詢，可以優(yōu)化分頁場景

舉例，先快速定位id，再關(guān)聯(lián)：

select t1.* from biao1 as t1,

 (select id from biao1 where XXX limit 100000, 20) as t2 

  where t1.id = t2.id

畫外音：MySQL并不跳過offset行，而是先取offset+N行，然后放棄前面offset行，再返回N行。如果offset特別大，效率就非常低。常見的優(yōu)化手段，是通過id對SQL進(jìn)行改寫。

【推薦】規(guī)范八：大表的性能需要優(yōu)化

SQL優(yōu)化目標(biāo)為：至少達(dá)到range級別，要求達(dá)到ref級別，如果是const級別那最好。

畫外音補(bǔ)充說明：

explain結(jié)果中的type字段代表什么意思？

MySQL的官網(wǎng)解釋非常簡潔，只用了3個單詞：連接類型(the join type)。它描述了找到所需數(shù)據(jù)使用的掃描方式。

 

最為常見的掃描方式有：

（1）system：系統(tǒng)表，少量數(shù)據(jù)，往往不需要進(jìn)行磁盤IO；

（2）const：常量連接；

（3）eq_ref：主鍵索引(primary key)或者非空唯一索引(unique not null)等值掃描；

（4）ref：非主鍵非唯一索引等值掃描；

（5）range：范圍掃描；

（6）index：索引樹掃描；

（7）ALL：全表掃描(full table scan)；

這些是最常見的，大家去explain自己工作中的SQL語句，95%都是上面這些類型。explain詳見《MySQL性能調(diào)優(yōu)，必須掌握這一個工具！》。

【推薦】規(guī)范九：組合索引

1. 建立組合索引時，區(qū)分度高的列放在左邊；

2. 混合條件時，等號條件的列放在左邊；

舉例：

where a=? and b=?

a列區(qū)分度高，建立a_b索引

如果a接近唯一，可以只建立a索引

舉例：

whare a>? and b=?

應(yīng)該建立b_a組合索引

【推薦】規(guī)范十：避免字段類型不同產(chǎn)生隱式轉(zhuǎn)換，導(dǎo)致索引失效

【參考】規(guī)范十一：避免極端

1. 認(rèn)為一個查詢就需要一個索引；

2. 認(rèn)為索引占Buffer Pool就不建立索引；

3. 認(rèn)為可以通過應(yīng)用層“先查詢再插入”的方式變相實(shí)現(xiàn)唯一索引；

第三部分：SQL規(guī)范

【強(qiáng)制】規(guī)范一：count規(guī)范

1. 不要使用count(column)或者count(1)，請使用count(*)

畫外音：

1. count(*)是SQL92標(biāo)準(zhǔn)定義的統(tǒng)計行的語法，與數(shù)據(jù)庫無關(guān)，與值無關(guān)；

2. count(*)會統(tǒng)計值為NULL的行，count(column)不會；

【強(qiáng)制】規(guī)范二：count規(guī)范

1. 如果要計算排除NULL值的不重復(fù)行計數(shù)，請使用count(distinct column)；

畫外音，請避坑：

使用count(distinct column1, column2)時，如果一列全為NULL，另一列即使有不同值，也會返回0；

【強(qiáng)制】規(guī)范三：NULL規(guī)范

1. 如果一列全是NULL，sum(column)返回的是NULL，因此在使用sum時，應(yīng)用程序務(wù)必考慮NPE問題；

畫外音：NPE，NullPointerException

【強(qiáng)制】規(guī)范四：NULL規(guī)范

1. 使用ISNULL(column)判斷列是否為空，不要使用column is null 或者column is not null；

畫外音：

1. NULL與任何值比較都是NULL；

2. column is (not) null可能導(dǎo)致?lián)Q行，影響可讀性，而ISNULL(column)是一個整體；

3. ISNULL(column)的執(zhí)行效率更高；

【強(qiáng)制】規(guī)范五：分頁規(guī)范

1. 應(yīng)用層分頁查詢邏輯，必須加上count為0時直接返回的判斷；

畫外音：

1. 避免執(zhí)行分頁語句提高性能；

【強(qiáng)制】規(guī)范六：外鍵規(guī)范

1. 禁止使用外鍵；

2. 外鍵約束問題必須在應(yīng)用層解決；

3. 禁止使用級聯(lián)查詢；

畫外音：

1. 針對于互聯(lián)網(wǎng)大數(shù)據(jù)量高并發(fā)量的數(shù)據(jù)存儲場景來說，外鍵與級聯(lián)查詢對性能的潛在影響較大；

2. 外鍵與級聯(lián)查詢存在更新風(fēng)暴的風(fēng)險；

級聯(lián)查詢是指，一個查詢的結(jié)果依賴于另一個查詢的結(jié)果，通常是通過子查詢或者嵌套查詢實(shí)現(xiàn)的。

更新風(fēng)暴問題是指，由于某些約束，例如外鍵約束或者觸發(fā)器約束，當(dāng)一條記錄被更新時，相關(guān)約束的記錄也會被更新，引發(fā)一系列連鎖反應(yīng)，導(dǎo)致短時間大量更新操作引發(fā)數(shù)據(jù)庫性能下降甚至死鎖的問題。因此，分布式場景一般禁止使用外鍵約束，或者觸發(fā)器約束。

【強(qiáng)制】規(guī)范七：存儲過程規(guī)范

1. 禁止使用存儲過程；

畫外音：

1. 但針對于互聯(lián)網(wǎng)大數(shù)據(jù)量高并發(fā)量的數(shù)據(jù)存儲場景來說，存儲過程對性能的潛在影響較大；

2. 難以調(diào)試；

3. 無可移植性；

【強(qiáng)制】規(guī)范八：別名規(guī)范

1. SQL中對于列的查詢與修改，如果涉及多個表，必須使用表名（或者別名）對列進(jìn)行限定；

畫外音：

1. 如果不進(jìn)行限定，未來對表DDL時，不同表可能出現(xiàn)同名列，使得原本正常的程序在DDL后突然異常；

【強(qiáng)制】規(guī)范九：線上操作規(guī)范

1. 線上數(shù)據(jù)庫進(jìn)行update/delete操作時，必須先同查詢條件select執(zhí)行，確認(rèn)結(jié)果后再update/delete；

畫外音：

1. 懂的都懂；

【推薦】規(guī)范十：表別名

1. 表的別名前加as，并以t1, t2, t3, ...依次命名；

【推薦】規(guī)范十一：in規(guī)范

1. 盡量避免in，實(shí)在避免不了，也建議將集合元素個數(shù)控制在1000個以內(nèi)；

【參考】規(guī)范十二：字符編碼

1. 因國際化需要，字符編碼建議采用utf8mb4字符集；

【參考】規(guī)范十三：刪除規(guī)范

truncate在功能上與不帶where的delete相同，但速度更快，使用的系統(tǒng)資源與日志資源更少，但避免在代碼中使用此語句。

畫外音：truncate不觸發(fā)觸發(fā)器，MySQL刪除數(shù)據(jù)時需要注意：

<END>

推薦閱讀：

為什么 idea 建議去掉 StringBuilder，使用“+”拼接字符串

SpringBoot統(tǒng)一屬性配置，以數(shù)據(jù)庫作為配置中心

    

     程序員在線工具站：cxytools.com
    
推薦一個我自己寫的工具站：http://cxytools.com，專為程序員設(shè)計，包括時間日期、JSON處理、SQL格式化、隨機(jī)字符串生成、UUID生成、隨機(jī)數(shù)生成、文本Hash...等功能，提升開發(fā)效率。
?戳閱讀原文直達(dá)！                                  朕已閱