用慘痛教訓換來的156條MySQL設計規約

在設計資料庫技術方案時，我們是有自己的設計理念或者原則，還是更多依據直覺去設計？是否曾經懊悔線上發生過的一次低級故障？是否思考過怎樣才能避免？設計規範的價值在於提供了一份工作檢查清單，我們不斷從錯誤中積累有效經驗，指導未來的工作。

以下規範在大型互聯網公司經過了充分的驗證，尤其適用於併發量大、數據量大的業務場景。安全無小事，很多公司都曾經因為數據泄露導致用戶損失慘痛，所以將安全規範放到了第一位。

1.【強制】禁止在資料庫中存儲明文密碼，需把密碼加密後存儲。

說明：對於加密操作建議由公司的中間件團隊基於如mybatis的擴展，提供統一的加密演算法及密鑰管理，避免每個業務線單獨開發一套，同時也與具體的業務進行瞭解耦。

2.【強制】禁止在資料庫中明文存儲用戶敏感信息，如手機號等。

說明：對於手機號建議公司搭建統一的手機號查詢服務，避免在每個業務線單獨存儲。

3.【強制】禁止開發直接給業務同學導出或者查詢涉及到用戶敏感信息的數據，如需要需上級領導審批。

4.【強制】涉及到導出數據功能的操作，如包含敏感欄位都需加密或脫敏。

5.【強制】跟資料庫交互涉及的敏感數據操作都需有審計日誌，必要時要做告警。

6.【強制】對連接資料庫的IP需設置白名單功能，杜絕非法IP接入。

7.【強制】對重要sql（如訂單信息的查詢）的訪問頻率或次數要做歷史趨勢監控，及時發現異常行為。

8.【推薦】線上連接資料庫的用戶名、密碼建議定期進行更換。

1.【推薦】儘量不在資料庫做運算，複雜運算需移到業務應用里完成。

2.【推薦】拒絕大sql語句、拒絕大事務、拒絕大批量，可轉化到業務端完成。

說明：大批量操作可能會造成嚴重的主從延遲，binlog日誌為row格式會產生大量的日誌。

3.【推薦】避免使用存儲過程、觸發器、函數等，容易造成業務邏輯與DB耦合。

說明：資料庫擅長存儲與索引、要解放資料庫CPU，將計算轉移到服務層、也具備更好的擴展性。

4.【強制】數據表、數據欄位必須加入中文註釋。

說明：後續維護的同學看到後才清楚表是乾什麼用的。

5.【強制】不在資料庫中存儲圖片、文件等大數據。

說明：大文件和圖片需要儲在文件系統。

6.【推薦】對於程式連接資料庫賬號，遵循許可權最小原則。

7.【推薦】資料庫設計時，需要問下自己是否對以後的擴展性進行了考慮。

8.【推薦】利用 pt-query-digest 定期分析slow query log併進行優化。

9.【推薦】使用內網功能變數名稱而不是ip連接資料庫。

10.【推薦】如果數據量或數據增長在前期規劃時就較大，那麼在設計評審時就應加入分表策略。

11.【推薦】要求所有研發SQL關鍵字全部是小寫，每個詞只允許有一個空格

1.【強制】庫名、表名、欄位名要小寫，下劃線風格，不超過32個字元，必須見名知意，建議使用名詞而不是動詞，詞義與業務、產品線等相關聯，禁止拼音英文混用。

2.【強制】普通索引命名格式：idx_表名_索引欄位名（如果以首個欄位名為索引有多個，可以加上第二個欄位名，太長可以考慮縮寫），唯一索引命名格式：uk_表名_索引欄位名（索引名必須全部小寫，長度太長可以利用縮寫），主鍵索引命名：pk_ 欄位名。

3.【強制】庫名、表名、欄位名禁止使用MySQL保留字。

4.【強制】臨時庫表名必須以tmp為首碼，並以日期為尾碼。

5.【強制】備份庫表必須以bak為首碼，並以日期為尾碼。

6.【推薦】用HASH進行散表，表名尾碼使用16進位數，下標從0開始。

7.【推薦】按日期時間分表需符合YYYY[MM][DD][HH]格式。

8.【推薦】散表如果使用md5（或者類似的hash演算法）進行散表，表名尾碼使用16進位，比如user_ff。

9.【推薦】使用CRC32求餘（或者類似的算術演算法）進行散表，表名尾碼使用數字，數字必須從0開始並等寬，比如散100張表，尾碼從00-99。

10.【推薦】使用時間散表，表名尾碼必須使用特定格式，比如按日散表user_20110209、按月散表user_201102。

11.【強制】表達是與否概念的欄位，使用 is _ xxx 的方式進行命名。

1.【推薦】資料庫使用InnoDB存儲引擎。

說明：支持事務、行級鎖、併發性能更好、CPU及記憶體緩存頁優化使得資源利用率更高。

2.【推薦】資料庫和表的字元集統一使用UTF8。

說明：utf8號稱萬國碼，其無需轉碼、無亂碼風險且節省空間。若是有欄位需要存儲emoji表情之類的，則將表或欄位設置成utf8mb4，utf8mb4向下相容utf8。

3.【推薦】不同業務，使用不同的資料庫，避免互相影響。

4.【強制】所有線上業務庫均必須搭建MHA高可用架構，避免單點問題。

1.【推薦】建表規範示例：

CREATE TABLE `student_info` (    `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵',    `stu_name` varchar(10) NOT NULL DEFAULT '' COMMENT '姓名',    `stu_score` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '總分',    `stu_num` int(11) NOT NULL COMMENT '學號',    `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',    `gmt_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',    `status` tinyint(4) DEFAULT '1' COMMENT '1代表記錄有效，0代表記錄無效',      PRIMARY KEY (`id`),      UNIQUE KEY `uk_student_info_stu_num` (`stu_num`) USING BTREE,    KEY `idx_student_info_stu_name` (`stu_name`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='學生信息表';

2.【強制】禁止使用外鍵，如果有外鍵完整性約束，需要應用程式控制。

3.【強制】每個Innodb 表必須有一個主鍵。

說明：Innodb 是一種索引組織表，其數據存儲的邏輯順序和索引的順序是相同的。每張表可以有多個索引，但表的存儲順序只能有一種，Innodb 是按照主鍵索引的順序來組織表的，因此不要使用更新頻繁的列如UUID、MD5、HASH和字元串列作為主鍵，這些列無法保證數據的順序增長，主鍵建議使用自增ID 值。

4.【推薦】單表列數目最好小於50。

5.【強制】禁止使用分區表。

說明：分區表在物理上表現為多個文件，在邏輯上表現為一個表，謹慎選擇分區鍵，跨分區查詢效率可能更低，建議採用物理分表的方式管理大數據。

6.【推薦】拆分大欄位和訪問頻率低的欄位，分離冷熱數據。

7.【推薦】採用合適的分庫分表策略，例如千庫十表、十庫百表等（建議表大小控制在2G）。

8.【推薦】單表不超過50個int欄位；不超過20個char欄位，不超過2個text欄位。

9.【推薦】表預設設置創建時間戳和更改時間戳欄位。

10.【推薦】日誌類型的表可以考慮按創建時間水平切割，定期歸檔歷史數據。

11.【強制】禁止使用order by rand()。

說明：order by rand()會為表增加一個偽列，然後用rand()函數為每一行數據計算出rand()值，基於該行排序，這通常都會生成磁碟上的臨時表，因此效率非常低。

12.【參考】可以結合使用hash、range、lookup table進行散表。

13.【推薦】每張表數據量建議控制在500w以下，超過500w可以使用歷史數據歸檔或分庫分表來實現（500萬行並不是MySQL資料庫的限制。過大對於修改表結構，備份，恢復都會有很大問題。MySQL沒有對存儲有限制，取決於存儲設置和文件系統）。

14.【強制】禁止在表中建立預留欄位。

說明：預留欄位的命名很難做到見名識義，預留欄位無法確認存儲的數據類型，所以無法選擇合適的類型；對預留欄位類型的修改，會對錶進行鎖定。

1.【強制】必須把欄位定義為NOT NULL並且提供預設值。

說明：NULL欄位很難查詢優化，NULL欄位的索引需要額外空間，NULL欄位的複合索引無效。

2.【強制】禁止使用ENUM，可使用TINYINT代替。

3.【強制】禁止使用TEXT、BLOB類型(如果表的記錄數在萬級以下可以考慮)。

4.【強制】必須使用varchar(20)存儲手機號。

5.【強制】禁止使用小數存儲國幣、使用“分”作為單位，這樣資料庫里就是整數了。

6.【強制】用DECIMAL代替FLOAT和DOUBLE存儲精確浮點數。

7.【推薦】使用UNSIGNED存儲非負整數。

說明：同樣的位元組數，存儲的數值範圍更大。

8.【推薦】建議使用INT UNSIGNED存儲IPV4。

說明：用UNSINGED INT存儲IP地址占用4位元組，CHAR(15)則占用15位元組。另外，電腦處理整數類型比字元串類型快。使用INT UNSIGNED而不是CHAR(15)來存儲IPV4地址，通過MySQL函數inet_ntoa和inet_aton來進行轉化。IPv6地址目前沒有轉化函數，需要使用DECIMAL或兩個BIGINT來存儲。例如:

SELECT INET_ATON('192.168.172.3'); 3232279555 SELECT INET_NTOA(3232279555); 192.168.172.3

9.【推薦】欄位長度儘量按實際需要進行分配，不要隨意分配一個很大的容量。

10.【推薦】核心表欄位數量儘可能地少，有大欄位要考慮拆分。

11.【推薦】適當考慮一些反範式的表設計，增加冗餘欄位，減少JOIN。

12.【推薦】資金欄位考慮統一*100處理成整型，避免使用decimal浮點類型存儲。

13.【推薦】使用VARBINARY存儲大小寫敏感的變長字元串或二進位內容。

說明：VARBINARY預設區分大小寫，沒有字元集概念，速度快。

14.【參考】INT類型固定占用4位元組存儲。

說明：INT(4)僅代表顯示字元寬度為4位，不代表存儲長度。數值類型括弧後面的數字只是表示寬度而跟存儲範圍沒有關係，比如INT(3)預設顯示3位，空格補齊，超出時正常顯示，Python、Java客戶端等不具備這個功能。

15.【參考】區分使用DATETIME和TIMESTAMP。

說明：存儲年使用YEAR類型、存儲日期使用DATE類型、存儲時間(精確到秒)建議使用TIMESTAMP類型。

DATETIME和TIMESTAMP都是精確到秒，優先選擇TIMESTAMP，因為TIMESTAMP只有4個位元組，而DATETIME8個位元組，同時TIMESTAMP具有自動賦值以及⾃自動更新的特性。

補充：如何使用TIMESTAMP的自動賦值屬性?

自動初始化，而且自動更新：column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP 只是自動初始化：column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP 自動更新，初始化的值為0：column1 TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP 初始化的值為0：column1 TIMESTAMP DEFAULT 0

16.【推薦】將大欄位、訪問頻率低的欄位拆分到單獨的表中存儲，分離冷熱數據。

說明：有利於有效利用緩存，防⽌讀入無用的冷數據，較少磁碟IO，同時保證熱數據常駐記憶體提⾼高緩存命中率。

17.【參考】VARCHAR(N)，N表示的是字元數不是位元組數，比如VARCHAR(255)，可以最大可存儲255個漢字，需要根據實際的寬度來選擇N。

18.【參考】VARCHAR(N)，N儘可能小，因為MySQL一個表中所有的VARCHAR欄位最大長度是65535個位元組，進行排序和創建臨時表一類的記憶體操作時，會使用N的長度申請記憶體。

19.【推薦】VARCHAR(N)，N>5000時，使用BLOB類型。

20.【推薦】使用短數據類型，比如取值範圍為0~80時，使用TINYINT UNSIGNED。

21.【強制】存儲狀態，性別等，用TINYINT。

22.【強制】所有存儲相同數據的列名和列類型必須一致（在多個表中的欄位如user_id，它們類型必須一致）。

23.【推薦】優先選擇符合存儲需要的最小數據類型。

24.【推薦】如果存儲的字元串長度幾乎相等，使用 char 定長字元串類型。

1.【推薦】單表索引建議控制在5個以內。

說明：索引可以增加查詢效率，但同樣也會降低插入和更新的效率，甚至有些情況下會降低查詢效率，所以不是越多越好。

2.【強制】禁止在更新十分頻繁，區分度不高的屬性上建立索引。

3.【強制】建立組合索引必須把區分度高的欄位放在前面。

4.【推薦】對字元串使用索引，如果字元串定義長度超過128的，可以考慮首碼索引。

5.【強制】表必須有主鍵，並且是auto_increment及not null的，根據表的實際情況定義無符號的tinyint,smallint,int,bigint。

6.【強制】禁止更新頻繁的列作為主鍵。

7.【強制】禁止字元串列作為主鍵。

8.【強制】禁止UUID MD5 HASH這些作為主鍵（數值太離散了）。

9.【推薦】預設使用非空的唯一鍵作為主鍵。

10.【推薦】主鍵建議選擇自增或發號器。

11.【推薦】核心SQL優先考慮覆蓋索引。

12.【參考】避免冗餘和重覆索引。

13.【參考】索引要綜合評估數據密度和分佈以及考慮查詢和更新比例。

14.【強制】不在索引列進行數學運算和函數運算。

15.【推薦】研發要經常使用explain，如果發現索引選擇性差，必須要學會使用hint。

16.【推薦】能使用唯一索引就要使用唯一索引，提高查詢效率。

17.【推薦】多條欄位重覆的語句，要修改語句條件欄位的順序，為其建立一條聯合索引，減少索引數量。

18.【強制】索引欄位要保證不為NULL，考慮default value進去。NULL也是占空間，而且NULL非常影響索引的查詢效率。

19.【強制】新建的唯一索引不能和主鍵重覆。

20.【推薦】儘量不使用外鍵、外鍵用來保護參照完整性，可在業務端實現。

說明：避免對父表和子表的操作會相互影響，降低可用性。

21.【強制】字元串不應做主鍵。

22.【強制】表必須有無符號int型自增主鍵，對應表中id欄位。

說明：必須得有主鍵的原因：採用RBR模式複製，無主鍵的表刪除，會導致備庫夯住 ；使用自增的原因：數據寫入可以提高插入性能，避免page分裂，減少表碎片。

23.【推薦】對長度過長的VARCHAR欄位建立索引時，添加crc32或者MD5 Hash欄位，對Hash欄位建立索引。

說明：下麵的表增加一列url_crc32，然後對url_crc32建立索引，減少索引欄位的長度，提高效率。

CREATE TABLE url(   ...   url VARCHAR(255) NOT NULL DEFAULT 0,   url_crc32 INT UNSIGNED NOT NULL DEFAULT 0,   ...   index idx_url(url_crc32) ）

24.【推薦】WHERE條件中的非等值條件（IN、BETWEEN、<、<=、>、>=）會導致後面的條件使用不了索引。

25.【推薦】索引欄位的順序需要考慮欄位值去重之後的個數，個數多的放在前面。

26.【推薦】ORDER BY，GROUP BY，DISTINCT的欄位需要添加在索引的後面。

27.【參考】合理創建聯合索引（避免冗餘），如(a,b,c) 相當於 (a) 、(a,b) 、(a,b,c)。

28.【推薦】複合索引中的欄位數建議不超過5個。

29.【強制】不在選擇性低的列上建立索引，例如"性別", "狀態"， "類型"。

30.【推薦】對於單獨條件如果走不了索引，可以使用force –index強制指定索引。

31.【強制】禁止給表中的每一列都建立單獨的索引。

32.【推薦】在varchar欄位上建立索引時，必須指定索引長度，沒必要對全欄位建立索引，根據實際文本區分度決定索引長度即可。

1.【強制】禁止使用SELECT *，只獲取必要的欄位，需要顯示說明列屬性。

說明：按需獲取可以減少網路帶寬消耗，能有效利用覆蓋索引，表結構變更對程式基本無影響。

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

3.【強制】WHERE條件中必須使用合適的類型，避免MySQL進行隱式類型轉化。

說明：因為MySQL進行隱式類型轉化之後，可能會將索引欄位類型轉化成=號右邊值的類型，導致使用不到索引，原因和避免在索引欄位中使用函數是類似的，例子 select uid from t_user where phone=15855550101（phone為 varchat 類型，此時查詢中使用數字查詢，會導致索引失效）。

4.【強制】禁止在WHERE條件的屬性上使用函數或者表達式。

5.【強制】禁止負向查詢，以及%開頭的模糊查詢。

6.【強制】應用程式必須捕獲SQL異常，並有相應處理。

7.【推薦】sql語句儘可能簡單、大的sql想辦法拆成小的sql語句。

說明：簡單的SQL容易使用到MySQL的querycache、減少鎖表時間特別是MyISAM、可以使用多核cpu。

8.【推薦】事務要簡單，整個事務的時間長度不要太長。

9.【強制】避免在資料庫中進行數學運算或者函數運算（MySQL不擅長數學運算和邏輯判斷，也容易將業務邏輯和DB耦合在一起）。

10.【推薦】sql中使用到OR的改寫為用IN() （or的效率沒有in的效率高）。

11.【參考】SQL語句中IN包含的值不應過多，裡面數字的個數建議控制在1000個以內。

12.【推薦】limit分頁註意效率。Limit越大，效率越低。可以改寫limit。

說明：改寫例子：

1）改寫方法一

延遲回表寫法 select xx,xx from t t1, (select id from t where ....  limit 10000,10) t2 where t1.id = t2.id

2）改寫方法二

select id from t limit 10000, 10; 應該改為 => select id from t where id > 10000 limit 10;

13.【推薦】儘量使用union all替代union。

14.【參考】避免使用大表JOIN。

15.【推薦】對數據的更新要打散後批量更新，不要一次更新太多數據。

16.【推薦】使用合理的SQL語句減少與資料庫的交互次數。

17.【參考】註意使用性能分析工具 Sql explain  / showprofile  /   mysqlsla。

18.【推薦】能不用NOT IN就不用NOT IN，坑太多了，會把空和NULL給查出來。

19.【推薦】關於分頁查詢，程式里建議合理使用分頁來提高效率，limit、offset較大要配合子查詢使用。

20.【強制】禁止在資料庫中跑大查詢。

21.【強制】禁止單條SQL語句同時更新多個表。

22.【推薦】統計表中記錄數時使用COUNT(*)，而不是COUNT(primary_key)和COUNT(1)。

說明：count( * ) 會統計值為 NULL 的行，而 count( 列名 ) 不會統計此列為 NULL 值的行。

23.【推薦】INSERT語句使用batch提交（INSERT INTO tableVALUES(),(),()……），values的個數不應過多。

24.【推薦】獲取大量數據時，建議分批次獲取數據，每次獲取數據少於2000條，結果集應小於1M。

25.【推薦】在做開發時建議使用資料庫框架(如mybatis)或prepared statement，可以提升性能並避免SQL註入。

26.【強制】禁止跨庫查詢（為數據遷移和分庫分表留出餘地，降低耦合度，降低風險）。

27.【推薦】儘量避免使用子查詢，可以把子查詢優化為join操作（子查詢的結果集無法使用索引，子查詢會產生臨時表操作，如果子查詢數據量大會影響效率，消耗過多的CPU及IO資源）。

28.【強制】超過三個表禁止 join（需要 join 的欄位，數據類型必須絕對一致；多表關聯查詢時，保證被關聯的欄位需要有索引。即使雙表 join 也要註意表索引、SQL 性能）。

29.【推薦】SQL 性能優化的目標：至少要達到 range 級別，要求是 ref 級別，如果可以是 consts最好。

30.【推薦】儘量不要使用物理刪除（即直接刪除，如果要刪除的話提前做好備份），而是使用邏輯刪除，使用欄位delete_flag做邏輯刪除，類型為tinyint，0表示未刪除，1表示已刪除。

31.【強制】在代碼中寫分頁查詢邏輯時，若 count 為 0 應直接返回，避免執行後面的分頁語句。

32.【強制】程式連接不同的資料庫要使用不同的賬號。

33.【推薦】使用 ISNULL()來判斷是否為 NULL 值。

1.【強制】禁止使用應用程式配置文件內的帳號手工訪問線上資料庫。

2.【強制】禁止非DBA對線上資料庫進行寫操作，修改線上數據需要提交工單，由DBA執行，提交的SQL語句必須經過測試。

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

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

5.【強制】禁止在主庫進行後臺統計操作，避免影響業務，可以在離線從庫上執行後臺統計。

1.【強制】所有的建表操作需要提前告知該表涉及的查詢sql。

2.【強制】所有的建表需要確定建立哪些索引後才可以建表上線。

3.【強制】所有的改表結構、加索引操作都需要將涉及到所改表的查詢sql發出來告知DBA等相關人員。

4.【強制】在建新表加欄位之前，要求至少要提前3天郵件出來，給dba們評估、優化和審核的時間。

5.【強制】批量導入、導出數據需要DBA進行審查，併在執行過程中觀察服務。

6.【強制】禁止有super許可權的應用程式賬號存在。

7.【強制】推廣活動或上線新功能必須提前通知DBA進行流量評估。

8.【強制】不在業務高峰期批量更新、查詢資料庫。

9.【強制】隔離線上線下環境（開發測試程式禁止訪問線上資料庫）。

10.【強制】在對大表做表結構變更時，如修改欄位屬性會造成鎖表，並會造成從庫延遲，從而影響線上業務，必須在凌晨後業務低峰期執行，另統一用工具pt-online-schema-change避免鎖表且降低延遲執行時間。

11.【強制】核心業務資料庫變更需在凌晨執行。

12.【推薦】彙總庫開啟Audit審計日誌功能，出現問題時方可追溯。

13.【強制】給業務方開許可權時，密碼要用MD5加密，至少16位。許可權如沒有特殊要求，均為select查詢許可權，並做庫表級限制。

14.【推薦】如果出現業務部門人為誤操作導致數據丟失，需要恢複數據，請在第一時間通知DBA，並提供準確時間，誤操作語句等重要線索。

15.【強制】批量更新數據，如update,delete 操作，需要DBA進行審查，併在執行過程中觀察服務。

16.【強制】業務部門程式出現bug等影響資料庫服務的問題，請及時通知DBA便於維護服務穩定。

17.【強制】線上資料庫的變更操作必須提供對應的回滾方案。

18.【強制】批量清洗數據，需要開發和DBA共同進行審查，應避開業務高峰期時段執行，併在執行過程中觀察服務狀態。

19.【強制】數據訂正如刪除和修改記錄時，要先 select ，確認無誤才能執行更新語句，避免出現誤刪除。