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排作業自動物理洗掉，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/175433.html
標籤：MySQL