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';

這樣使用函數會導緻查詢條件不使用索引，使查詢性能下降。應改為：

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' )；

應改為：

and f_mobile ='1234567891'

UNION ALL

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排作業自動實體删除。