史上最全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溝通確定維護和備份優先級，

標籤：MySQL

上一篇：MYSQL（進階篇）——一篇文章帶你深入掌握MYSQL

下一篇：干貨，分布式資料庫在金融核心場景的落地實踐｜騰訊云資料庫