MySQL資料庫規範 (設計規範+開發規範+操作規範)
I 文檔定義
1.1 編寫目的
為了在軟體生命周期內規範資料庫相關的需求分析、設計、開發、測試、運維工作，便於不同團隊之間的溝通協調，以及在相關規範上達成共識，提升相關環節的工作效率和系統的可維護性。同時好的規範，在執行的時候可以培養出好的習慣，好的習慣是軟體品質的保證。
1.2 適用範圍
本文檔適用於開發、測試、QA及運維團隊成員。
II . 命名設計規範
2.1 總則
（1）所有命名採用26個英文小寫字母和0－9這十個自然數，加上下劃線_組成。不能出現其他字元（注釋除外）。
（2）對象名盡量短，長度不超過30個字元。
（3）對象名字盡量描述實體的內容，由英文單詞、單片語合或單詞縮寫組成，不以數字和_開頭。
（4）命名中禁止使用SQL保留字。
2.2 庫名
庫名與應用名稱盡量一致，統一小寫，以下劃線分割。
2.3 表名
表名必須使用小寫字母或數字，以下劃線分割，禁止出現數字開頭，禁止兩個下劃線中間只出現數字。如果表名僅有一個單詞，那麼建議不使用縮寫，而是用完整的單詞。同一模組的表儘可能使用相同的前綴，表名稱儘可能表達含義。
數據表 <模組標識>_<表標識> 例如： order_header , order_detail
編碼表 base_<模組標識>_<表標識>
日誌表 log_<模組標識>_<表標識>
2.4 欄位名
（1） 能表達欄位功能的英文單詞或單詞縮寫，一般不超過三個英文單詞，以下劃線分割。布爾類型的欄位以「is_」作為前綴。
（2） 各表之間意義相同的欄位應同名。
（3） 系統中所有屬於內碼的欄位（僅用於表示唯一性和程式內部用到的標識性欄位），名稱取為：<表標識>_id。
（4） 系統中屬於是業務範圍內的編號的欄位，其代表一定的業務資訊，這樣的欄位建議命名為<業務標識>_code，其數據類型為VARCHAR，該欄位需加唯一索引。
（5） 欄位名不要與表名重複。
（6） 不要在列的名稱中包含數據類型。
（7） 每個欄位添加欄位說明。
（8） 資料庫欄位名的修改代價很大，所以欄位名稱需要慎重考慮。
（9） 統一命名欄位：create_by、create_time、modify_by、modify_time、disabled
2.5 索引名
A. 非唯一索引必須按照「idx_<構成索引的欄位名>」進行命名
例如：在age上添加索引idx_age
B. 唯一索引必須按照「uidx_<構成索引的欄位名>」進行命名
例如：uidx_cardid
C. 組合索引建議包含所有欄位名，過長的欄位名可以采⽤縮寫形式
例如：idx_age_name
2.6 視圖命名
v_<模組標識>_<視圖標識>
2.7 存儲過程命名
usp_<模組標識>_<存儲過程標識>
2.8 函數命名
ufn_<模組標識>_<函數標識>
III 資料庫設計規範
3.1 表設計原則
（1） 表的存儲引擎建議是InnoDB存儲引擎,InnoDB 支援事務，支援行級鎖，更好的恢復性，高並發下性能更好
（2）同一個DB中的表，其存儲引擎、字符集應保持統一
（2） 數據表創建、變更具備說明文檔
數據表創建、變更時必須提供數據表設計文檔: 包含表及欄位詳細說明
（3） 規範化與反規範化
規範化的優點是減少了數據冗餘，節約了存儲空間，相應邏輯和物理的I/O次數減少，同時加快了增、刪、改的速度。但是一個完全規範化的設計並不總能生成最優的性能，因為對資料庫查詢通常需要更多的連接操作，從而影響到查詢的速度，而且範式越高性能就會越差。出於性能和方便管理的考慮，原則上表設計應滿足第三範式。有時為了提高某些查詢或應用的性能而可以破壞規範規則，即反規範化。數據應當按兩種類別進行組織：頻繁訪問的數據和頻繁修改的數據。對於頻繁訪問但是不頻繁修改的數據，內部設計應當物理不規範化。對於頻繁修改但並不頻繁訪問的數據，內部設計應當物理規範化。比較複雜的方法是將規範化的表作為邏輯資料庫設計的基礎，然後再根據整個應用系統的需要，物理地非規範化數據。
（4）臨時庫表必須以 _tmp_ 為前綴並以日期為後綴，備份表必須以 _bak_ 為前綴並以日期 為後綴。
（5）盡量控制單表數據量的大小，建議控制在 600 萬以內
大表在查詢性能和結構修改、備份、恢復等運維方面存在很多弊端。可以用歷史數據歸檔，分庫分表、選擇其它類型資料庫等手段來控制數據量大小。
（6）數據表分類說明
根據應用的實際需要和特點，可以將數據表進行如下分類：
A. 基本數據表：描述業務實體的基本資訊。例如：人員基本資訊、單位基本資訊等。
B. 標準編碼表：描述屬性的列表值。例如：職稱、民族、狀態等。
C. 業務數據表：記錄業務發生的過程和結果。例如：人員調動登記、變更通知單等。
D. 系統資訊表：存放與系統操作、業務控制有關的參數。例如：用戶資訊、許可權、用戶配置資訊等。
E. 統計數據表：存放業務數據統計值。例如：通知單統計、人員類別統計等。
F. 臨時處理表：存放業務處理過程中的中間結果。
G. 其他類型表：存放應用層的日誌、消息記錄等。
3.2 欄位設計原則
（1）完善的欄位說明
涉及數據欄位新增、變更，必須提供欄位說明，需要及時更新欄位注釋。
（2）選擇符合存儲需要的最小的數據類型
一般來說，應該使用能正確存儲和表示數據的最小類型。如果不確定需要什麼數據類型，則選擇不會超出範圍的最小類型。選擇更簡單的數據類型。例如，整數類型的比較其代價小於字元類型的比較，因為字符集和排序規則使字元比較更複雜。
（3）合理的欄位默認值
欄位儘可能有默認值，字元型的默認值為一個空字元串，數字型的默認為數值0。 儘可能把欄位定義為NOT NULL。對於欄位能否NULL，應該在SQL建表腳本中明確指明，不應使用預設。
（4）所有布爾類型欄位數據類型是unsigned tinyint，數值0表示為假；數值1表示為真（根據表的欄位意義：比如Disabled = 1表示 Disabled 值為真，可以表示數據被邏輯刪除）
（5）避免使用 ENUM 類型
ENUM 類型的 ORDER BY 操作效率低，需要額外操作。
（6）MySQL最大行大小不能超過64KB（65535位元組），所以一個表中的欄位不要太多，理論上建議不要超過30個。
（7）如果存儲的字元串長度幾乎相等，推薦使用CHAR定長字元串類型。
（8）VARCHAR是可變長字元串，不預先分配存儲空間，長度不要超過2000，如果存儲長度大於此值，定義欄位類型為text或blob，獨立出來一張表，用主鍵來對應，避免影響其他欄位索引效率。TEXT 和 BLOB 的主要差別是 BLOB 能夠保存二進位數據；而 TEXT 只能保存字元數據。在程式設計時，儘可能不使用TEXT、BLOB類型。
（9）區分使用DATETIME和TIMESTAMP，兩者都可用來表示YYYY-MM-DD HH:MM:SS類型的日期。兩種都保存日期和時間資訊，毫秒部分最高精確度都是6位數。建議使用TIMESTAMP(3)。
A. TIMESTAMP佔用4位元組，DATETIME佔用8位元組，當保存毫秒部分時兩者都使用額外的空間 （1-3 位元組）。
B. TIMESTAMP的取值範圍比DATETIME小得多，不適合存放比較久遠的日期。TIMESTAMP只能存儲從 '1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999' 之間的時間。而DATETIME允許存儲從 '1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999' 之間的時間。
C. TIMESTAMP的插入和查詢受時區的影響。如果記錄的日期需要讓不同時區的人使用，最好使用 TIMESTAMP。
（10）根據實際需要選擇能夠滿足應用的最小存儲的日期類型。如果應用只需記錄「年份」，那麼用1個位元組的YEAR類型完全可以滿足，而不需要用4個位元組來存儲的DATE類型。這樣不僅可以節約存儲，還可以提高表的操作效率。
（11）小數類型為decimal，禁止使用float和double。因為float和double在存儲的時候，存在精度損失問題，這是浮點數特有的問題。因此在精度要求比較高的應用中（比如貨幣）要使用定點數而不是浮點數來保存數據。浮點數指的就是含有小數的值，浮點數插入到指定列中超過指定精度後，浮點數會四捨五入，MySQL 中的浮點數指的就是 float 和 double，定點數指的是 decimal，定點數能夠更加精確的保存和顯示數據。
（12）欄位允許適當冗餘，以提高性能，但是必須考慮數據完整性。冗餘欄位應遵循：
A. 不是頻繁修改的欄位。
B. 不是varchar超長欄位，更不能是text欄位。
C. 需要維護冗餘欄位的數據完整性。
3.3 主鍵設計原則
（1）一定要有顯式的主鍵。
（2）針對InnoDB，在無特殊需求的情況下，建議使用與業務無關的自增ID作為主鍵。
（3）自增欄位做主鍵時，欄位類型必須是bigint 。
（4）不推薦使用聯合主鍵。由於InnoDB索引的數據結構都是B+tree，對包含聯合主鍵的表做大量寫入，會導致InnoDB為了維持B+tree而移動大量數據，降低性能。
（5）禁止外鍵。對性能損耗特別大，一般的做法是，在業務層設計專門的邏輯或解決方案來保證數據的一致性，以最終一致的時差來換取即使訪問的性能問題。
3.4 索引設計原則
（1）不允許存在和主鍵重複的索引。主鍵其實就是一個非空的唯一索引，所以再在該欄位上添加一個索引完全是多此一舉。
（2）業務上具有唯一特性的欄位，即使是組合欄位，也必須建成唯一索引。唯一索引的值是唯一的，可以更快速地通過該索引確定某條記錄。另外，即使在應用層做了非常完善的校驗控制，只要沒有唯一索引，必然有臟數據產生。
（3）考慮索引列值的分布。評估某一欄位是否值得建索引，是根據選擇性(符合條件筆數/總筆數)*100%來判斷，選擇性越低代表越值得，慣用的百分比界線是20%。如果某個數據列用於記錄性別（只有"M"和"F"兩種值），並且值出現的幾率幾乎相等，那麼無論搜索哪個值都可能得到一半的數據行，在這種情況下索引的用處就不大。因為查詢優化器發現某個值出現在表的數據行中的百分比很高的時候，它一般會忽略索引，進行全表掃描。
（4）為經常需要排序、分組和關聯的欄位建立索引。
（5）為常作為查詢條件的欄位建立索引。
（6）使用短索引，不要索引大欄位。如果對varchar欄位進行索引，必須指定一個前綴長度，盡量使用前綴索引，沒必要對全欄位建立索引，根據實際文本區分度決定索引長度即可。 使用前綴索引，對列的某幾個字元進行索引，可以提高檢索效率。
（7）合理創建聯合索引，(a，b，c) 相當於 (a) 、(a，b) 、(a，b，c)，區分度最高的列在最左邊。
（8）合理使用覆蓋索引減少IO，避免排序。
（9）不要過度使用索引，單個表上的索引數量建議不要超過5個 。
（10）刪除不再使用或者很少使用的索引。
3.5 資料庫里不建議存放業務日誌
業務日誌的寫入量比較大，影響mysql的性能，建議存放到非關係型資料庫中。
IV SQL設計規範
4.1 避免數據類型的隱式轉換
例如：SQL中的字元串類型數據應該統一使用單引號。特別對純數字的字元串，必須用單引號，否則會導致隱式轉換而引起性能問題或索引失效問題。
4.2 避免複雜SQL
對於非常複雜的SQL，特別是有多層嵌套，帶子句或相關子查詢的，應該先考慮是否設計不當引起的。對於一些複雜SQL可以考慮使用程式實現。
4.3 批量插入
使用INSERT語句一定要給出插入值的欄位列表，這樣即使表加了欄位也不會影響現有系統的運行。對於小批量插入，可以將多條記錄合併為同一個SQL，使用INSERT INTO tablename (col1,col2,...) VALUES (value1, value2,...),(value1, value2,...),...; 插入多條數據只有一次提交，效率明顯提高。對於大批量插入和文件的導入導出，避免使用insert .... select和create table…select的形式，可能會阻止對源表的並發更新，如果查詢比較複雜，會造成嚴重的性能問題。推薦使用select...into outfile和load data infile的組合來實現，採用這種方式MySQL不會給source_tab 加鎖，還可以大大縮短數據的導出導入時間。但是，由於這種方式存在一定的安全隱患，所以如果需要使用這種方式，必須提交DBA審批，審批通過以後才可執行。
4.4 數據更新
推薦使用主鍵更新，其它維度條件的更新操作會造成頁鎖。對多個表進行關聯update操作風險較大，尤其是當執行計划出現錯誤時，可導致多個表同時被鎖住，應該盡量避免。不帶條件的update會導致全表操作，耗時較長，如有此需求，請聯繫DBA評估、操作。
4.5 避免使用TRUNCATE TABLE
TRUNCATE TABLE 比 DELETE速度快，且使用的系統和事務日誌資源較少，也可以直接釋放磁碟空間，但TRUNCATE無事務且不觸發trigger，有可能造成事故，故不建議在程式碼中使用此語句。
TRUNCATE TABLE在功能上與不帶where子句的delete語句相同。
4.6 避免使用SELECT *
如果不必要取出所有數據，不要用 * 來代替，應給出欄位列表。
4.7 使用索引做條件查詢count(*)
innodb引擎在統計方面和myisam是不同的，Myisam內置了一個計數器，所以在使用 select count(*) from table 的時候，直接可以從計數器中取出數據。而innodb必須全表掃描一次方能得到總的數量。每執行一次掃描一次，代價非常高。需要進行count(*)統計表記錄總數時，加上secondary index掃描條件，可以加快掃描速度。例如：SELECT COUNT(*) FROM sbtest1 WHERE id>=0;
4.8 避免IN子句
使用 IN 或 NOT IN 子句時，特別是當子句中有多個值且表數據較多時，速度會明顯下降。可以採用連接查詢或外連接查詢來提高性能。
4.9 避免不必要的排序
不必要的數據排序大大的降低系統性能。
比如：在使用group by col的時候，mysql會默認order by col ,在只需要分組不需要排序的情況下，可以使用GROUP BY col ORDER BY NULL提升執行效率，僅僅對col列分組，而不排序。
4.10 合理利用最左索引
組合索引的生效原則是：從前往後依次使用生效，如果中間某個索引沒有使用，那麼斷點前面的索引部分起作用，斷點後面的索引沒有起作用。對於組合索引，注意索引的使用順序，where子句中將最左索引放在第一列。
比如：（a,b,c） 三個列上加了聯合索引（是聯合索引，不是在每個列上單獨加索引）where a=3 and b=45 and c=5 .... 這種三個索引順序使用中間沒有斷點，全部發揮作用 where a=3 and c=5... 這種情況下b就是斷點，a發揮了效果，c沒有效果where b=3 and c=4... 這種情況下a就是斷點，在a後面的索引都沒有發揮作用，這種寫法聯合索引沒有發揮任何效果where b=45 and a=3 and c=5 .... 這個跟第一個一樣，全部發揮作用，abc只要用上了就行，跟寫的順序無關 。
4.11 多表連接
做多表操作時，應該給每個表取一個別名，每個表欄位都應該標明其所屬哪個表。
為關聯操作的欄位建立索引，並使用統一數據類型，不同數據類型做關聯時，MySQL會進行隱式轉換，導致無法用到索引，開銷較大。
多表連接個數建議不超過3個。
4.12 避免在where後的索引欄位上使用函數
在where後的索引欄位上使用函數會導致索引失效，嚴重情況下會拖慢整個資料庫實例的速度。
例如：
SELECT orderid
FROM order_detail
WHERE from_unixtime(create_time)>'2017-12-04 12:00:00';
這樣使用函數會導致查詢條件不使用索引，使查詢性能下降。應改為：
SELECT orderid
FROM order_detail
WHERE create_time>unix_timestamp('2017-12-04 12:00:00');
4.13 盡量不要做』%』前綴模糊查詢
col like 「abc%」 能用上索引，而col like 「%abc」不能用上索引
4.14 使用UNION ALL代替UNION
UNION合併兩個或多個SELECT語句的結果集，並消去表中任何重複行。而UNION ALL不會消除重複行。從效率上說，UNION ALL要比UNION快很多，所以如果可以確認合併的多個結果集中不包含重複數據時，建議使用UNION ALL。
4.15 盡量避免OR操作
通常情況下，如果條件中有or，即使其中有條件帶索引也不會使用，所以除非每個列都建立了索引，否則不建議使用OR。在多列OR中，建議用UNION ALL替換。
比如：
select f_crm_id from d_dbname1.t_tbname1 where f_xxx_id = 926067
and (f_mobile ='1234567891' or f_phone ='1234567891' )；
應改為：
select f_crm_id from d_dbname1.t_tbname1 where f_xxx_id = 926067
and f_mobile ='1234567891'
UNION ALL
select f_crm_id from d_dbname1.t_tbname1 where f_xxx_id = 926067
and f_phone ='1234567891'
相同欄位or可改成 in，如 f_id=1 or f_id=100 --> f_id in (1,100)。
4.16 MySQL 在否定條件中不能使用索引
例如，where 條件裡面有<>、not in 、not exists的時候，即便是在這些判斷欄位上加有索引，也不會起作用。
4.7 MySQL 在JOIN中連接欄位類型如果不一致，則不能使用索引
但是例外就是char和varchar如果在定義表的時候，長度一致，就可以利用索引JOIN，反正不行。例如，char(20)和varchar(20)可以利用索引，char(20)和varchar(25)則不行，不管varchar裡面實際存儲的值是多長。
4.18 如果兩個欄位列的字符集不同，不推薦JOIN
字符集不同的列，索引失效，容易引起慢查詢故障。
V 完整性設計規範
採用資料庫系統實現數據的完整性，這不但包括通過標準化實現的完整性而且還包括數據的功能性。
5.1 主鍵約束
每個表要求有主健，主健欄位或組合欄位必須滿足非空屬性和唯一性要求。
5.2 NULL值
（1）由於NULL值在參加任何運算時，結果均為NULL，所以儘可能把欄位定義為NOT NULL。對於所有聲明為NOT NULL的欄位，必須顯式指定默認值。
（2）不要使用count(列名)或者count(常量)來替代 count(*)，count(*)是SQL92定義的標準統計行數的語法，跟資料庫無關，跟null和非null無關。
說明：count(*)會統計值為null的行，而count(列名)不會統計此列為null的行。
（3）count(distinct col)計算該列除null之外不重複的行數
注意：count(distinct col1, col2)，如果其中一列全為null，那麼即使另一列有不同的值，也返回0。
（4）當某一列的值全為null，count(col)的返回結果為0，但sum(col)的返回結果為null，因此使用sum()時需要注意NPE問題。
例如，可以使用ISNULL()來判斷是否為NULL值，來避免sum的NPE問題：
SELECT IF(ISNULL(SUM(g)), 0, SUM(g)) FROM table;
（5）NULL與任何值的直接比較都為null。
NULL<>NULL的返回結果是NULL，而不是false。
NULL=NULL的返回結果是NULL，而不是true。
NULL<>1的返回結果是NULL，而不是true。
5.3 視圖使用原則
為了在應用程式和資料庫之間提供一層抽象，可以為應用程式建立視圖而不必直接訪問表。使用試圖可以簡化操作，不用關注表結構的定義，可以把經常使用的數據集合定義成視圖；屏蔽了表結構變化對用戶的影響， 表增加列對視圖沒有影響，具有一定的獨立性。此外，用戶對視圖不可以隨意的更改和刪除，可以保證數據的安全性。視圖是虛擬的資料庫表，在使用時要遵循以下原則：
A. 儘可能減少使用視圖。
B. 視圖中如果嵌套使用視圖，級數不要超過3級。
C. 由於視圖中只能固定條件或沒有條件，所以對於數據量較大或隨時間的推移逐漸增多的表，不宜使用視圖。
D. 除特殊需要，避免類似SELECT * FROM [Table Name] 而沒有檢索條件的視圖
E. 視圖中盡量避免出現數據排序的SQL語句。
VI 安全性設計規範
6.1 資料庫帳號使用規範
嚴格管理程式的專用帳號，禁止用戶使用此帳號進行數據操作。 請使用開發人員專用只讀帳號進行數據查詢。
6.2 用戶與許可權
為不同用戶設定允許的許可權，管理和使用許可權分離。確定每個用戶對資料庫表的操作許可權，如查詢、新增、更新等。每個用戶擁有剛好能夠完成任務的許可權。
嚴格把控好管理許可權，只將管理許可權賦予管理員。禁止有super許可權的應用程式帳號存在。禁止有DDL、DCL許可權的應用程式帳號存在。
6.3 用戶密碼管理
用戶帳號的密碼必須進行加密處理，確保在任何地方查詢都不會出現密碼的明文。
VII 開發行為規範
7.1 總則
（1） 業務部門推廣活動或上線新功能，必須提前通知DBA，並留出必要時間以便DBA完成壓力評估和擴容 ；
（2） 單表多次alter操作必須合併一次操作；
例如：
要給表t增加一個欄位aa,同時給已有的欄位bb建立索引，通常的做法分為兩步:
alter table t add column aa varchar(10);
然後增加索引:
alter table t add index idx_bb(bb);
正確的做法是:
alter table t add column aa varchar(10),add index idx_bb(bb);
（3） 懷疑有性能瓶頸的SQL及早提交DBA調優，避免上線出現性能問題；
（4） 批量更新數據，必須通知DBA進行審核，並在執行過程中觀察服務及主從延遲；
（5） 重要業務庫的變更，須告知DBA重要等級、是否數據備份和執行時間要求；
（6） 避免在業務高峰期批量更新、查詢資料庫；
（7） 提交線上建表改表需求，必須詳細註明涉及到的所有SQL語句，便於DBA進行審核和優化；
（8） 所有DDL和DML語句必須要在運維平台上提交申請，禁止口頭或通過聊天工具傳送需求；
（9） 不要在MySQL資料庫中存放業務邏輯，如果把業務邏輯放到資料庫中,將會影響橫向發展和上線測試。建議把業務邏輯提前，放到前端或中間邏輯層，資料庫僅作為存儲層，實現邏輯與存儲的分離；
（10） 出現業務部門人為誤操作導致數據丟失，需要恢複數據的，必須第一時間通知DBA，並提供準確時間地點、誤操作語句等重要線索；
（11） 業務部門程式出現BUG等影響資料庫服務的問題，必須及時通知DBA，便於維護服務穩定；
（12） 重要項目的資料庫方案選型和設計必須提前通知DBA參與。
7.2 避免使用觸發器
MySQL中觸發器是行觸發的，每次增加、修改或者刪除記錄都會觸發進行處理，編寫過於複雜的觸發器或者增加過多的觸發器對記錄的插入、更新、刪除操作會有比較嚴重的影響，因此不要將應用的處理邏輯過多地依賴於觸發器來處理。觸發器的功能通常可以用其他方式實現，確實需要採用觸發器，請聯繫DBA進行確認。
7.3 避免使用存儲過程和函數
在資料庫伺服器上進行大量的複雜運算會佔用伺服器的CPU，造成資料庫伺服器的壓力，影響資料庫的正常使用，所以應盡量將這些運算操作分攤到應用伺服器上執行。此外，存儲過程難以調試和擴展，資料庫擴展能力遠遠不如應用。
7.4 避免使用視圖
視圖可能導致執行計劃錯亂，影響SQL運行效率。對視圖的修改，資料庫必須把它轉化為對基本表的資訊修改，不便於維護。
VIII 其他規範
8.1 編製文檔
對所有的命名規範、限制、數據字典、存儲過程、函數都要編製文檔。資料庫文檔化會大大減少犯錯的機會，對開發、支援和跟蹤修改非常有用。
8.2 維護計劃規範
（1） 數據歸檔設計
根據業務功能，做最小限度保留，將數據備份至歸檔庫，系統功能兼容訪問歷史資料庫。
（2） 數據歸檔刪除
需要物理刪除不需要歸檔的數據，直接由DBA排作業自動物理刪除。
