史上最全Mysql規範

1 整體規約

1.1 註釋

1）【強制】資料庫所有對象必須要有註釋，包括：表、欄位、索引等，並且要保持最新；

1.2 字元集

1）【強制】預設使用utf8字元集，無亂碼風險，除一些需要存儲特殊符號的欄位，可以採用utf8mb4，比如文章內容欄位，支持表情符號等；

2）【強制】排序規則預設使用utf8-general-ci；

1.3 存儲引擎

1）【強制】預設使用INNODB存儲引擎；

 說明：MyISAM引擎從MYSQL 5.5版本後查詢性能已經沒InnoDB高，另外InnoDB的以主鍵為條件的查詢性能是非常高的，且支持事務、行級鎖、高併發性能更好、對多核CPU、大記憶體、SSD等硬體資源支持更好，利用率更高；

如需要使用基他類型的存儲引擎，請在DBA的建議下使用；

1.4 資料庫特性

1）【推薦】降低對資料庫功能的依賴，如在業務上使用了MySQL特性，且這個特性是只有MySQL存在的，對以後的資料庫遷移會帶來麻煩；

1.5 平衡範式與冗餘

1）【推薦】並非一定要遵守範式理論，適度的冗餘設計，欄位長度短而且頻繁查詢的欄位可以冗餘到其他表，避免表連接查詢，可以極大提升查詢效率；

2 資料庫對象

2.1 表設計

2.1.1 單庫表數量

1）【強制】單庫表數量建議控制在500以內；

2.1.2 單表數據量

1）【強制】單表數據量建議控制在1000萬以內（參考值）；

說明：表的記錄數多少合適不能死搬硬套，需要根據伺服器的CPU、記憶體、磁碟IO能力綜合評估，比如伺服器總記憶體有168G，資料庫總數據文件大小100G，innodb緩存池設置為120G，這個時候即便大表有3000萬條，也可以全部載入到記憶體中，性能上完全不會有磁碟IO壓力。根據經驗值一般熱數據占數據總量的10%左右，熱數據都能緩存到記憶體中性能上就不會有磁碟IO壓力。

2.1.3 單表欄位數量

1）【強制】表列數量建議控制在30個以內；

說明：控制單表單欄位數量的目的是為了控制數據行的長度避免出現行遷移和行鏈接。如果計算行長度避免出現行鏈接或行遷移呢？MYSQL的數據行是存儲在數據頁中，數據頁的大小是16KB（預設16KB），file header、Page、Header、File Trailer 占用了102位元組，Page Directory記錄數據行在數據頁的位置也需要消耗數據頁空間，建議把總消耗空間按1KB算，也就是說數據頁可以空間還剩15KB。15KB除去行長度可以整除就可以避免行鏈接，儘量少使用可變長度的大欄位可以有效減少行遷移。

2.1.4 冷熱數據分離

1）【推薦】訪問頻率較低的大欄位拆分出數據表，以免造成IO資源、緩存資源的浪費。經常一起使用的列應該放到一個表中，允許適當冗餘，避免更多的關聯操作；

2.1.5 分庫分表策略

1）【推薦】如果按HASH散表，表名尾碼使用十進位，下標從1開始。考慮後續的擴容，建議使用二叉樹分庫分表策略。

2）【推薦】如果按日期時間散表，表名需要符合YYYY[MM][DD][HH][mm][sss]的格式。

說明：大表查詢效率很低，需要考慮水平拆分。根據業務特性有很多拆分方式。符合時間遞增的表，可以根據時間來分，也可以ID的HASH方式來拆分，也可以通過某些特定欄位的計算規則拆分。

2.1.6 彙總表

1）【推薦】多表關聯查詢會很慢，可以根據實際情況，考慮在業務上彙總計算，記錄到彙總表。

2.2 欄位設計

2.2.1 基本規範

1）【強制】存儲相同數據的列名和列類型必須一致，否則會導致隱式轉換，造成索引失效，降低查詢效率；

2）【強制】在最大限度的滿足可能的需要的前提下，欄位應該儘可能的設計得短一些，以提高查詢的效率，且降低索引對資源的消耗；

3）【強制】數據行的長度不要超過8020位元組，如果超過這個長度的話在物理頁中插入兩行數據就會出現行鏈接從而造成存儲碎片，降低查詢效率；

4）【強制】單表列數量建議控制在30個以內；

5）【強制】儘量使用整型欄位，代替IP、枚舉類型、字元類型、浮點數類型；

6）【強制】所有欄位都需要預設值，如有特殊情況，另作討論決定；

2.2.2 字元欄位

1）【強制】長度變化不大的欄位選擇CHAR類型，減少資源的浪費。

2）【強制】其他不確定長度的欄位，統一使用varchar相關的類型。

2.2.3 整型欄位

1）【強制】明確無符號的數值，使用的整型。

2）【強制】能夠用整型的欄位儘量整型，提高查詢和連接的性能，降低存儲開銷、CPU計算開銷。如enum、ip、小額貨幣等。

2.2.4 Enum欄位

1）【強制】禁止使用enum，可使用tinyint代替；

說明：因為修改ENUM需要使用ALTER語句，需要進行DDL操作， ENUM類型的ORDER BY操作效率低，需要額外操作。

2.2.5 預設值

1）【強制】所有欄位都需要預設值，不允許為null，避免無法使用索引或null值引發BUG，如有特殊情況，可以存儲空白字元代替null；

說明：null欄位難以進行查詢優化，索引需要額外的空間，複合索引無效，整體降低資料庫處理的性能，也容易導致應用層程式報空指針異常。

2.2.6 二進位數據

1）【強制】禁止在資料庫上存儲圖片、二進位文件等靜態資源，應該使用合適的文件系統，資料庫僅存儲URL對於二進位多媒體數據、超大文本數據也不要放在資料庫欄位中；

2.2.7 Text/Blob欄位

1）【強制】一般避免使用text、blob等類型欄位，會浪費更多的磁碟和記憶體空間，非必要的大量的大欄位查詢會淘汰掉熱數據，導致記憶體命中率急劇降低，影響資料庫性能。

2）【強制】考慮使用varchar來代替，如果一定要使用text/blob，要離到單獨的擴展表中，如果要用到索引，只能使用首碼索引。

2.2.8 日期時間欄位

1）【推薦】timestamp類型比較精簡，可以提高查詢效率，減少磁碟空間及IO，但範圍是1970年-2038年，考慮企業的歷史及將來，建議使用int類型（10）存儲日期時間戳；

2.2.9 金額欄位

1）【強制】禁止使用float、double來定義金額欄位，建議使用decimal類型或者bigint類型；

2）【強制】金額欄位使用decimal類型，並給予足夠的長度及精度。在性能要求比較苛刻的情況下，使用bigint類型，單位是分（如果是其他貨幣，需要定義其他單位）。

2.2.10 其他

電話欄位

1）【強制】考慮到區號或者國家代號可能會涉及到±()等符號，並且需要支持模糊查詢，所以應該使用字元類型，如varchar等；

坐標欄位

1）【強制】表示坐標（0,0），應該使用兩列表示，而不是將“0,0”放在1個列中。

預留欄位

1）【推薦】預留欄位的命名很難做到見名識義；預留欄位無法確認存儲的數據類型，所以無法選擇合適的類型；預留欄位是一種“過度設計”，我們應該做的就是“按需設計”，在經過詳細有效的分析之後，在數據表中只放置必要的欄位，而不要留出大量的備用欄位。

2.3 索引設計

索引可以提高查詢效率，但會降低更新效率，所以索引不是越多越好，原則是能不加就不加，要加的一定得加。

2.3.1 單表索引數量

1）【推薦】單表索引數量不超過5個。

2.3.2 單個索引的欄位數量

1）【強制】單個索引中的欄位數不超過5個。

2.3.3 欄位選擇

1）【強制】對於頻繁更新的欄位要評估讀寫比例和創建索引後的性能收益再決定是否創建索引。

比如一個欄位每秒更新20次，但每秒查詢達到100次，而且是直接通過該欄位來定位數據行的，如果該欄位沒有索引就會導致全表掃描，如果更新也是需要使用該欄位定位數據行也會導致更新出現全表掃描，這種情況就是一定要創建索引的。（相對應的一種情況是通過數據行的ID可以定位到數據行，不需要使用被更新欄位定位數據行，這種情況就不適合創建索引）。

2）【強制】如“性別”這種區分度不大的欄位，建立索引對查詢性能的提升有限，與全表掃描差別不大。

3）【強制】已經建立唯一索引的欄位，沒有必要再建立與該欄位有關的聯合索引。

4）【強制】不要建立查詢條件里根本不會出現的欄位的索引或者聯合索引。

2.3.4 聯合索引

1）【強制】聯合索引中各欄位的順序，要與查詢語句的欄位順序保持一致，否則可能無法應用索引。

2）【強制】區分度最高的放在聯合索引的最左側。

3）【強制】使用最頻繁的列放到聯合索引的左側。

4）【強制】儘量把欄位長度小的列放在聯合索引的最左側。

2.3.5 首碼索引

1）【推薦】建立長字元串欄位的首碼索引。

當要索引的列字元很多時，索引則會很大且變慢，這時候可以只索引列開始的部分字元串節約索引空間，降低重覆的索引值，保證快速有效過濾數據的同時，節省維護索引的開銷。

2.3.6 索引類型

1）【強制】唯一確定一條記錄的一個欄位或多個欄位要建立主鍵或者唯一索引，不能唯一確定一條記錄，為了提高查詢效率建立普通索引。

2.4 主鍵設計

1）【推薦】一般不使用聯合主鍵。

2）【強制】必須指定主鍵，建議使用記憶體型、數值型欄位做主建，以應對大數據高併發的業務場景。如果使用自增列，在一定程度上依賴了資料庫自身的特性，同時也要考慮分散式環境的全局唯一性。UUID是字元類型，增加索引磁碟空間及CPU開銷，且不具備自增特性。

2.5 其他規約

在大數據、高併發的互聯網業務，架構設計的思路是解放資料庫，讓應用層承擔更到的責任。一般禁止使用與資料庫自身特性相關的對象，如存儲過程、觸發器、視圖等，降低業務耦合度，讓資料庫做最擅長的事情。

2.5.1 觸發器

1）【推薦】禁止使用資料庫的觸發器特性，請在應用層尋求對應的解決方案。如有特殊需求，另行研究決定。

2.5.2 存儲過程

1）【推薦】禁止使用資料庫的存儲過程特性，請在應用層尋求對應的解決方案。如有特殊需求，另行研究決定。

2.5.3 函數

1）【推薦】禁止使用資料庫的函數特性，請在應用層尋求對應的解決方案。如有特殊需求，另行研究決定。

2.5.4 外鍵

1）【強制】禁止使用資料庫的外鍵特性，請在應用層尋求對應的解決方案。如有特殊需求，另行研究決定。

說明：外鍵會導致表與表之間耦合，update與delete操作都會涉及相關聯的表，影響sql 的性能，甚至會造成死鎖，大數據高併發業務場景下容易造成資料庫性能大幅下降。

2.5.5 約束設計

1）【強制】本規範禁止使用資料庫的約束特性，請在應用層尋求對應的解決方案。如有特殊需求，另行研究決定。

說明：主鍵自身會有唯一性約束，其他約束如check、外鍵等，建議在應用層實現。

2.5.6 表分區

1）【強制】本規範禁止使用資料庫的表分區特性，請在應用層尋求對應的解決方案。如有特殊需求，另行研究決定。

說明：分區表在物理上表現為多個文件，在邏輯上表現為一個表，實際性能不是非常好，且管理維護成本較高，建議採用物理分表的方式管理大數據，請參考分庫分表策略相關的文檔。

3 命名

3.1 基本規約

資料庫的所有表(Table)、視圖(View)、索引(Index)、觸發器(Trigger)、函數(Function)和存儲過程(Store  Procedure)均應遵循以下命名規範：

1）【強制】統一小寫格式。

2）【強制】統一使用英文字母，數字和下劃線來命名，禁止使用其他字元，如中橫線等。

3）【強制】不超過32個字元，須見名知意，易於辨識。

4）【強制】禁止使用拼音來命名，禁止拼音英文混用。

5）【強制】禁止使用關鍵字，可以加上首碼區別關鍵字，參見附錄一《關鍵字列表》

6）【推薦】臨時庫、臨時表名必須以tmp為首碼並以時間戳為尾碼。

7）【推薦】備份庫、備份表名必須以bak為首碼並以時間戳為尾碼。

8）【推薦】不同表中，存儲相同數據的列名要保持一致。

3.2 庫命名

1）【推薦】參考格式：<首碼>[_業務類型/產品類型/其他類型]_<庫名>

首碼：必選項，如baidu。

類型：非必選，但需要所有庫要統一選還是不選。參考類型：產品類型/業務類型/其他類型。

庫名：應儘可能和所服務的業務模塊名一致。

正例： 

3.3 表命名

3.3.1 常規表

1）【推薦】參考格式：<庫名/庫名縮寫>_<表名/表名縮寫>。

表名應儘可能和所服務的業務模塊名一致。

表名應儘量包含與所存放數據對應的單詞或者縮寫。

同一個模塊的表應儘量以模塊名(或縮寫)為首碼。

正例： 

3.3.2 關聯表

1）【推薦】參考格式：庫名/庫名縮寫>_<表名1>_<表名2>_rel。

正例： 

3.4 欄位命名

1）【推薦】參考格式：[首碼_]<欄位名>

一般不用首碼（當和關鍵詞衝突的可以考慮加首碼區別）。

欄位名稱也應儘量保持和實際數據相對應。

正例： 

3.5 索引命名

1）【推薦】普通索引：idx_<表名/表名縮寫>_<列名/列名縮寫[_列名/列名縮寫]>。

2）【推薦】唯一索引：uidx_<表名/表名縮寫>_<列名/列名縮寫[_列名/列名縮寫]>。

備註：

【idx】：表示索引，英文index。

【uidx】：表示唯一索引，英文unique index。

聯合索引名稱應儘量包含所有索引鍵欄位名或縮寫，且各欄位名在索引名中的順序應與索引鍵在索引中的索引順序一致。

正例： 

4 SQL

4.1 in/or

or的效率是n級別，in的效率是log(n)級別。

1）【強制】應儘量避免在子句中使用 or來連接條件，否則將導致引擎放棄使用索引而進行全表掃描。

2）【強制】in的個數建議控制在1000以內，避免使用在大集合中使用in。

4.2 select *

1）【強制】禁止使用SELECT *，應用層應指定所要的欄位，避免消耗不必要的CPU、硬碟IO及網路帶寬。

正例：SELECT `blog_id` FROM `blog`;

反例：SELECT * FROM `blog`;

4.3 union all

1）【推薦】使用union all替代union，union有去重開銷，儘量由應用層實現去重。

4.4 模糊查詢

1）【強制】禁止使用全模糊查詢，無法使用索引，導致全表掃描。

2）【強制】可以使用右模糊查詢，如like‘xxx%’，可以正常應用索引。

4.5 反向查詢

1）【強制】禁止使用反向查詢，如NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，會導致全表掃描。

4.6 隱式類型轉換

1）【強制】禁止使用隱式轉換，會導致索引失效。

說明：當操作符與不同類型的操作數一起使用時，會發生類型轉換以使操作數相容，則會發生轉換隱式。比如user_id資料庫欄位設計時是int類型，sql中你寫成了字元串類型，會導致索引失效。

4.7 join

1）【強制】大表連接欄位和其他過濾條件欄位沒有合適的索引，禁止大表使用JOIN查詢。

說明：大表join查詢如果全表掃描，會產生臨時表，消耗較多記憶體與CPU，極大影響資料庫性能。

2）【推薦】禁止3表及以上連表查詢，編寫sql查詢時，需要用explain分析sql執行效率（指標：掃描行數，是否用到索引，如果連表效率優於單表查詢的條件下，允許3表連表）。

4.8 SQL表達式

1）【推薦】避免在資料庫中使用數學運算、函數等，容易將業務邏輯和DB耦合在一起，且容易導致索引失效。

4.9 交互

1）【強制】減少與資料庫的交互次數，也就是禁止迴圈查詢資料庫。

4.10 大批量

1）【推薦】Insert語句中，根據測試，批量一次插入1000條時效率最高，多於1000條時，要拆分，多次進行同樣的插入，應該合併批量進行。

說明：大批量寫操作會產生大量日誌，日誌傳輸和恢復所需要的時間過長，造成主從環境數據同步嚴重延遲，當因為這種延遲造成數據不一致時，可以考慮直接強制查詢主庫。

4.11 大事務

1）【推薦】遵循事務相關性最小原則。

2）【推薦】事務儘量簡單，事務時間儘可能短。

說明：大批量修改數據，一定是在一個事務中進行的，這就會造成表中大批量數據進行鎖定，從而導致大量的阻塞，阻塞會對資料庫的性能產生非常大的影響。

4.12 索引欄位順序

1）【強制】查詢條件中的欄位，要把最有效的索引欄位寫在前面，同時要註意聯合索引中的欄位順序。

4.13 insert 

1）【強制】禁止使用INSERT INTO t_xxx VALUES(xxx)，必須顯示指定插入的列屬性。

正例：INSERT INTO blog (‘blog_id’,’title’,’user_id’) VALUES(1,’標題’,1)

反例：INSERT INTO blog VALUES(1,’標題’,’1’)

4.14 DDL操作

1）【強制】應用程式里的語句，禁止一切 DDL 操作。

說明：如有特殊需要，必需與協商同意方可使用。

4.15 排序

1）【推薦】使用時，預設會進行排序，當你不需要排序時，可以使用order by null。

4.16 聚合函數

1）【強制】使用count(1)和count(*)代替count(column_name)。

說明：count(1)≈count(*)>count(主鍵ID)>count(column)

count(*)其實可以理解為等於count(0)，mysql會將參數 * 轉化為參數 0 來進行處理，所以count(*)和count(1)的執行過程是基本一樣的，性能上沒有什麼差異。

count(1)、 count(*)、 count(主鍵欄位)在執行的時候，如果表裡存在二級索引，優化器就會選擇二級索引進行掃描。

不要使用欄位)  來統計記錄個數，因為它的效率是最差的，會採用全表掃描的方式來統計。如果你非要統計表中該欄位不為 NULL 的記錄個數，建議給這個欄位建立一個二級索引

count()函數不會返回 NULL，但 sum()函數可能返回 NULL。

5 資料庫功能變數名稱

1）【強制】禁止使用IP連接資料庫。

正例：

註意：

生產環境：英文取Production，縮寫prod。

開發環境：英文取Development，縮寫dev。

測試環境：英文取Test，縮寫test。

從庫：英文取Slave，縮寫slave。

主庫：英文取Master，縮寫master。

6 用戶行為

1）【強制】禁止分配super許可權的賬號給應用程式使用，super許可權只能留給DBA處理問題的賬號使用。

2）【強制】禁止在資料庫中存儲明文密碼。

3）【強制】禁止從開發環境、測試環境直連線上資料庫。

4）【強制】禁止線上上做資料庫壓力測試。

5）【強制】禁止使用IP連接資料庫，應該使用內網功能變數名稱。

6）【強制】禁止在生產環境創建test庫。

7）【強制】合理分配資料庫賬號所擁有的許可權，如應用程式賬號原則上不准有drop許可權。

8）【推薦】導入導出數據必須提前通知DBA，並讓DBA協助觀察。

9）【推薦】促銷活動或者上線新功能必須提前通知DBA進行流量評估。

10）【推薦】不在業務高峰期批量更新，查詢資料庫。

11）【推薦】進行DDL/DML操作時，需要DBA進行審查，併在執行過程中觀察服務負載等各種指標。

12）【推薦】對特別重要的庫表，提前與DBA溝通確定維護和備份優先順序。