sql 軍規

一、基礎規範

• 表儲存引擎必須使用InnoDB

• 表字符集預設使用utf8，必要時候使用utf8mb4

解讀：

（1）通用，無亂碼風險，漢字3位元組，英文1位元組

（2）utf8mb4是utf8的超集，有儲存4位元組例如表情符號時，使用它

• 禁止使用儲存過程，檢視，觸發器，Event

解讀：

（1）對資料庫效能影響較大，網際網路業務，能讓站點層和服務層乾的事情，不要交到資料庫層

（2）除錯，排錯，遷移都比較困難，擴充套件性較差

• 禁止在資料庫中儲存大檔案，例如照片，可以將大檔案儲存在物件儲存系統，資料庫中儲存路徑

• 禁止線上上環境做資料庫壓力測試

• 測試，開發，線上資料庫環境必須隔離

二、命名規範

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

解讀：abc，Abc，ABC都是給自己埋坑

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

解讀：tmp，wushan誰TM知道這些庫是幹嘛的

• 庫備份必須以bak為字首，以日期為字尾

• 從庫必須以-s為字尾

• 備庫必須以-ss為字尾

三、表設計規範

• 單例項表個數必須控制在2000個以內

• 單表分表個數必須控制在1024個以內

• 表必須有主鍵，推薦使用UNSIGNED整數為主鍵

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

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

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

• 建議將大欄位，訪問頻度低的欄位拆分到單獨的表中儲存，分離冷熱資料

解讀：具體參加《如何實施資料庫垂直拆分》

四、列設計規範

• 根據業務區分使用tinyint/int/bigint，分別會佔用1/4/8位元組

• 根據業務區分使用char/varchar

解讀：

（1）欄位長度固定，或者長度近似的業務場景，適合使用char，能夠減少碎片，查詢效能高

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

• 根據業務區分使用datetime/timestamp

解讀：前者佔用5個位元組，後者佔用4個位元組，儲存年使用YEAR，儲存日期使用DATE，儲存時間使用datetime

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

解讀：

（1）NULL的列使用索引，索引統計，值都更加複雜，MySQL更難優化

（2）NULL需要更多的儲存空間

（3）NULL只能採用IS NULL或者IS NOT NULL，而在=/!=/in/not in時有大坑

• 使用INT UNSIGNED儲存IPv4，不要用char(15)

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

解讀：

（1）牽扯到國家代號，可能出現+/-/()等字元，例如+86

（2）手機號不會用來做數學運算

（3）varchar可以模糊查詢，例如like ‘138%’

• 使用TINYINT來代替ENUM

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

五、索引規範

• 唯一索引使用uniq_[欄位名]來命名

• 非唯一索引使用idx_[欄位名]來命名

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

解讀：

（1）網際網路高併發業務，太多索引會影響寫效能

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

（3）異常複雜的查詢需求，可以選擇ES等更為適合的方式儲存

• 組合索引欄位數不建議超過5個

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

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

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

解讀：踩過因為JOIN欄位型別不一致，而導致全表掃描的坑麼？

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

六、SQL規範

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

解讀：

*（1）select 會增加cpu/io/記憶體/頻寬的消耗

（2）指定欄位能有效利用索引覆蓋

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

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

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

• 隱式型別轉換會使索引失效，導致全表掃描

• 禁止在where條件列使用函式或者表示式

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

• 禁止負向查詢以及%開頭的模糊查詢

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

• 禁止大表JOIN和子查詢

• 同一個欄位上的OR必須改寫問IN，IN的值必須少於50個

• 應用程式必須捕獲SQL異常

解讀：方便定位線上問題

說明：本軍規適用於併發量大，資料量大的典型網際網路業務，可直接帶走參考，不謝。

軍規練習：為什麼下列SQL不能命中phone索引？

select uid from user where phone=13811223344

解讀：隱形型別轉換