MySQL日常使用遵循的規範建議

一 . 基礎規範

1.必須使用InnoDB存儲引擎

解讀：支持事務；支持行級鎖；支持MVCC多版本控制；支持外鍵；死鎖自動檢測；併發性能更好、CPU及記憶體緩存頁優化使得資源利用率更高。

2.  表字元集推薦使用utf8mb4

解讀：utf8 是 Mysql 中的一種字元集，只支持最長三個位元組的 UTF-8字元，也就是 Unicode 中的基本多文本平面(BMP)。任何不在基本多文本平面的 Unicode字元，都無法使用 Mysql 的 utf8 字元集存儲。包括 Emoji 表情(Emoji 是一種特殊的 Unicode 編碼，常見於 ios 和 android 手機上)，和很多不常用的漢字，以及任何新增的 Unicode 字元等等。要在 Mysql 中保存 4 位元組長度的 UTF-8 字元，需要使用 utf8mb4 字元集，utf8mb4是utf8的超集，有存儲4位元組例如表情符號時，使用它。

3.  數據表、數據欄位必須加入中文註釋

解讀：方便大家對錶的理解，方便DBA和開發的溝通，方便歷史傳承。

4.  禁止使用存儲過程、視圖、觸發器、Event

解讀：A.對資料庫性能影響較大，互聯網業務，能讓站點層和服務層乾的事情，不要交到資料庫層，資料庫擅長存儲和索引。

           B.調試，排錯，遷移都比較困難，擴展性較差。

5.  禁止存儲大文件或者大照片

解讀：建議存儲到專門的類型資料庫中。

二 . 命名規範

1.  庫名，表名，列名必須用小寫，採用下劃線分隔

解讀：A.資料庫伺服器端開了大小寫敏感，提高資料庫伺服器性能

           B.只有一套標準，避免Abc,ABC混淆(SQL)

2.  庫名，表名，列名必須見名知義，長度不要超過32字元

解讀：見名知義，便於理解；長度不要過長，便於書寫。

三 . 表設計規範

1.  表必須有主鍵，推薦使用bigint,表數據量少也可使用unsigned int為主鍵

解讀：刪除無主鍵的表，如果是row模式的主從架構，從庫會掛住

2.  主從表可使用自增bigint做主鍵，冗餘無業務含義的欄位做關聯欄位，並做非聚集索引

解讀：A.如order表自增主鍵id,冗餘order_no欄位，order_detail表自增主鍵id,冗餘order_no欄位；order\order_detail和通過order_no關聯，並對order_no做非聚集索引

           B.可有效減少數據文件和索引文件提示查詢性能

3.  禁止使用外鍵，如果要保證完整性，應由應用程式實現

解讀：外鍵使得表之間相互耦合，影響update/delete等SQL性能，有可能造成死鎖，高併發情況下容易成為資料庫瓶頸點

4.  建議將大欄位，訪問頻度低的欄位拆分到單獨的表中存儲，分離冷熱數據

解讀：會顯著提升該表的查詢性能

5.  表必須有欄位create_by、create_time、modify_by、modify_time、disabled

解讀：便於表的維護。

四 . 列設計規範

1. 根據業務區分使用tinyint/int/bigint，分別會占用1/4/8字

解讀：數據量大時，表欄位的類型會顯著影響性能

2. 根據業務區分使用char/varchar

解讀：A.欄位長度固定，或者長度近似的業務場景，適合使用char，能夠減少碎片，查詢性能高

          B.欄位長度相差較大，或者更新較少的業務場景，適合使用varchar，能夠減少空間

3. 根據業務區分使用datetime(3)/timestamp(3)

解讀：A.前者占用8個位元組，後者占用4個位元組，存儲年使用YEAR，存儲日期使用DATE，存儲時間使用datetime(3)

          B.如果需要根據時區顯示對應時區的時間，使用timestamp

          C.Datetime(3)/timestamp(3)保存到毫秒級別，程式端select now(3)

4. 必須把欄位定義為NOT NULL並設預設值

解讀：A.NULL的列使用索引，索引統計，值都更加複雜，MySQL更難優化

          B.NULL需要更多的存儲空間

          C.NULL只能採用IS NULL或者IS NOT NULL，不可使用=/!=/in/not in

5.     使用varchar(20)存儲手機號，不要使用整數

解讀：A.牽扯到國家代號，可能出現+/-/()等字元，例如+86

          B.手機號不會用來做數學運算

          C.varchar可以模糊查詢，例如like ‘138%’

6.  使用TINYINT來代替ENUM

解讀：ENUM增加新值要進行DDL操作

五 . 索引規範

1. 非聚集索引使用idx_[欄位名]來命名

2. 單張表索引數量建議控制在5個以內

解讀：A.互聯網高併發業務，太多索引會影響寫性能

           B.生成執行計劃時，如果索引太多，會降低性能，並可能導致MySQL選擇不到最優索引

           C.異常複雜的查詢需求，可以選擇其他等更為適合的方式存儲

3. 儘量避免\禁止使用唯一索引

解讀：只是比非聚集索引快一個CPU查找的時間，但在delete，update時都要重新排序

4. 組合索引欄位數不建議超過3個

解讀：如果3個欄位還不能極大縮小row範圍，很可能是設計有問題

5. 不建議在頻繁更新的欄位上建立索引

6. 非必要不要進行JOIN查詢，如果要進行JOIN查詢，被JOIN的欄位必須類型相同，並建立索引

解讀： JOIN欄位類型不一致，會導致全表掃描。

7. 理解組合索引最左首碼原則，避免重覆建設索引，如果建立了(a,b,c)，相當於建立了(a), (a,b), (a,b,c) 

六 . SQL語句規範

1.  禁止使用select *，只獲取必要欄位

解讀：A.select *會增加cpu/io/記憶體/帶寬的消耗

           B.指定欄位能有效利用索引覆蓋

           C.指定欄位查詢，在表結構變更時，能保證對應用程式無影響

2.  insert必須指定欄位，禁止使用insert into T values()

解讀：指定欄位插入，在表結構變更時，能保證對應用程式無影響

3.  隱式類型轉換會使索引失效，導致全表掃描

解讀：一定要確保聲明的類型和索引欄位類型一致

4.  禁止在where條件列使用函數或者表達式

解讀：導致不能命中索引，全表掃描

5. 禁止%開頭的模糊查詢

解讀：導致不能命中索引，全表掃描

6.  儘可能不使用大表JOIN和子查詢

解讀：會占用大量的記憶體、IO、CPU，如果使用到要先縮小結果集在做join（超過1000萬的表都應該考慮縮小結果集在做join）

7.  區分INNER\LEFT\RIGHT\FULL JOIN，合理使用，JOIN儘量不超過3個表

解讀：MySQL查詢優化器很爛，可在測試環境構造不同的寫法，查看對應的執行計劃，得出最佳的方案，可使用測試環境最佳實踐

8. 同一個欄位上的OR必須改寫為IN，IN的值必須少於50個

9. 應用程式必須捕獲SQL異常

解讀：方便定位線上問題