Mysql數據庫開發規范

前言 本規范是在項目開發中整理的一些開發規范和技巧，期望能更充分利用MySQL的特性，得到更好的性能。主要提供給需要基于MySQL做應用開發的人員作為參考，方便完成更有效率的開發。 數據庫設計 數據庫設計的目標三個：功能實現，可伸縮性，可用性。設計時需要平衡業務技術各個方面，做好取舍。數據庫的架構設計時最重要的，80%的性能優勢來自架構設計的優勢。

2.1 版本選擇

官方版本建議使用MySQL5.7.14，分支版本建議Mariadb10.1.14。

2.2 架構設計

Mysql數據庫架構設計主要考慮讀寫分離、分庫分表、熱點數據、雪崩效應與過載保護、讀寫優化等方面。

讀寫分離用的是主從庫的設計，至少要兩臺服務器，兩邊數據是同步的，主庫負責寫數據，從庫負責讀數據。 分庫：盡可能將訪問頻繁的不同業務數據分在不同的數據庫來存放，這樣能提高并發訪問效率。 分表：盡可能將大數據量的業務表采用某種分類標識來分成不同的表。可以考慮將歷史數據和現實數據分開存放。 熱點數據：對于經常要重復使用的數據，必須要放在內存中緩存起來，不能每次都從磁盤讀取。可以采用全局內存變量、Memory Cache等。 雪崩效應是指當并發量大時，對一些表的訪問會導致大量的鎖出現，這樣后來的數據庫訪問就要建立更多的數據庫連接，使數據庫性能下降甚至宕機。解決方法主要是優化數據庫、從業務上優化設計、及時釋放鎖和系統資源、使用連接池等。 讀寫優化：讀優化和熱點數據類似，主要通過在內存中緩存數據實現盡量少的讀取硬盤，盡量多的讀取內存。寫優化主要通過主鍵、索引實現。

2.3 schema設計

2.3.1控制庫和表的個數

MySQL是單進程多線程架構的數據庫，這點與SQL Server比較類似，但與Oracle多進程的架構有所不同（Oracle的Windows版本也是單進程多線程的架構）。這也就是說，MySQL數據庫實例在系統上的表現就是一個進程。

從性能上考慮，單個MySQL庫不能太大，總空間容量一般不超過100G，單庫不超過500個表，因為MySQL表的表結構文件、數據文件、索引文件在操作系統上存放在schema的同一個目錄下，當一個schema的表個數超過100個，即同一個目錄下面的文件超過300時，操作系統管理文件的成本會大幅增加，影響服務器性能。

2.3.2控制單表數據量

表設計主要考慮因素有：IO高效、全表遍歷、表修復快、提高并發；alter table快。 單表數據量建議控制在純INT不超1000W，含CHAR不超500W，因為Mysql在處理大表（char的表>500W行，或int表>1000W）時，性能就開始明顯降低，所以要采用不同的方式控制單表容量： A、根據數據冷熱，對數據分級存儲，歷史歸檔。

B、采用分庫/分表/分區表，橫向拆分控制單表容量。

C、對于OLTP系統，控制單事務的資源消耗，遇到大事務可以拆解，采用化整為零模式，避免特例影響大眾。

D、單庫不要超過500個表。

E、單表字段數不要太多，最多不要大于50個。

2.3.3數據冗余設計

數據庫冗余設計的目的： A、無外鍵時，減少多表join查詢。

B、便于分布式設計，允許適度冗余，為了容量擴展允許適度開銷。

C、基于業務自由優化，基于i/o 或查詢設計，無須遵循范式結構設計。

冗余設計的應用場景： A、原有展現程序涉及多個表的查詢，希望簡化查詢。

B、數據表拆分往往基于主鍵，而原有數據表往往存在非基于主鍵的關鍵查詢，無法在分表結構中完成。

C、存在較多數據統計需求（count, sum等），效率低下。

冗余的設計思路舉例： A、基于展現的冗余設計，如：

消息表message，存在字段 from userid,to userid,msg,send time四個字段，而展示程序需要顯示發送者姓名和性別。 通常在message表中增加冗余字段from username和from user sex即可。 B、基于查詢的冗余設計，如：

用戶分表，將用戶庫分成若干數據表。基于用戶名的查詢和基于userid的查詢都是高并發請求。用戶分表可以基于userid分成多個表，同時基于用戶名做對應冗余表。 C、基于統計的冗余設計，如：

count(*)操作，如不需要精準結果，可以直接show table status like …獲得，需要精準結果，可以在緩存層增加key-value對，實時更新該key-value。同時異步更新到數據庫中冗余字段，或冗余表中。

2.3.4控制事物大小

限制大SQL (BIG SQL)、大事務 (BIG Transaction)、大批量 (BIG Batch)。遇到大sql時，可以考慮根據業務分拆成幾個小sql，盡量不在數據庫做運算、復雜運算移到程序端CPU、盡可能簡單應用MySQL。 如：md5() 或Order by Rand()或計算字段等操作不在數據庫表上進行。

2.3.5存儲引擎選擇

默認使用InnoDB引擎。InnoDB適用于幾乎99%的MySQL應用場景，而且在MySQL 5.7的系統表都改成InnoDB了，還有什么理由再死守MyISAM呢。

2.3.6字符集

使用優先級 utf8mb4 > utf8 > latin1

2.3.7表主鍵

顯示指定自增int/bigint unsigned not null 作為主鍵，盡量不要使用uuid/HASH/MD5類型作為主鍵。

2.4 編碼規范

2.4.1命名規范

Mysql對象名稱最長是64個字符，為了閱讀方便，我們要求對象名控制在32個字符以內。且數據庫名、表名、字段名、索引名等強烈建議只用小寫字符、數字、下劃線組合，不使用 desc,select ,show ,update 等mysql關鍵字，臨時表加上tmp 后綴，統計表加上statistic后綴，日志表加上log后綴等。

對象 規范 表 t 應用名 模塊名 功能名 表的字段 英文單詞或縮寫，避開關鍵字 視圖 v 表名 存儲過程 p 表名 函數 f 功能說明 包 pkg 功能說明 觸發器 tri 表名 主鍵 primary 索引 idx 字段1 字段2 唯一索引 uk 字段1 字段2

表的命名應盡量反映存儲的數據內容。

2.4.2表字段的設計

字段的命名以單詞或者單詞縮寫為主，避開數據庫關鍵字如all、type等。 Mysql字段類型 ：

列類型  表達的范圍  存儲需求 1   TINYINT[(M)] [UNSIGNED] [ZEROFILL]  -128 到 127 或 0 到 255  1 個字節

2  SMALLINT[(M)] [UNSIGNED] [ZEROFILL]   -32768 到 32767 或 0 到 65535  2 個字節  3  INT[(M)] [UNSIGNED] [ZEROFILL]   -2147483648 到 2147483647 或 0 到 4294967295  4 個字節 4   BIGINT[(M)] [UNSIGNED] [ZEROFILL]   -9223372036854775808 到 9223372036854775807  或 0 到 18446744073709551615   8 個字節

5  DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]  整數最大位數（ M ）為 65 ，小數位數最大（ D ）為 30  變長  6  DATE   YYYY-MM-DD  3 個字節  7  DATETIME  YYYY-MM-DD HH:MM:SS(1001年到 9999 年的范圍 )   8 個字節  8  TIMESTAMP  YYYY-MM-DD HH:MM:SS （1970年到2037年的范圍）  4 個字節  9  CHAR(M)    0

2. 固定長度的字符串使用 CHAR 類型，所有單個字符的全部使用 CHAR 類型，而不應該使用VARCHAR 類型；

3. 僅僅當字符數量可能超過 20000 個的時候，可以使用 TEXT 類型來存放字符類數據。所有使用 TEXT 類型的字段必須和原表進行分拆，與原表主鍵單獨組成另外一個表進行存放；

4. 需要精確到時間（年月日時分秒）的字段可以使用DATETIME 或TIMESTAMP，但請注意各自能表達的范圍，以及是否需要用到TIMESTAMP的特性；精確到微秒建議時間類型轉換為整形BIGINT存儲（建議優先使用BIGINT類型存儲日期）；

5. 所有只需要精確到天的字段全部使用 DATE 類型，而不應該使用 TIMESTAMP或者DATETIME 類型；

6. 自增序列類型的字段只能使用 INT 或者 BIGINT，且明確標識出為無符號型(UNSIGNED)，除非確實會出現負數，僅當該字段數字取值會超過42億，才使用 BIGINT 類型；能有tinyint的就不要用smallint ，能用smallint的就不要用int，能用int的就不要用bigint

7. 字段字段使用not null：MySQL NULL類型和Oracle的NULL有差異，會進入索引中，如果是一個組合索引，那么這個NULL類型的字段會極大影響整個索引的效率。此外，NULL 在索引中的處理也是特殊的，也會占用額外的存放空間。

8. 有小數點的字段或者精度要求高的字段用decimal，禁用float.

2.4.3索引的設計

索引按照“idx_字段名”進行命名，索引名稱使用小寫。 索引中的字段數不超過5個。 唯一鍵由3個以下字段組成，并且字段都是整形時，使用唯一鍵作為主鍵。 沒有唯一鍵或者唯一鍵不符合5中的條件時，使用自增（或者通過發號器獲取）id作為主鍵。 唯一鍵不和主鍵重復。 索引字段的順序需要考慮字段值去重之后的個數，個數多的放在前面。 ORDER BY，GROUP BY，DISTINCT的字段需要添加在索引的后面。

單張表的索引數量控制在5個以內，若單張表多個字段在查詢需求上都要單獨用到索引，需要經過DBA評估。查詢性能問題無法解決的，應從產品設計上進行重構。 使用EXPLAIN判斷SQL語句是否合理使用索引，盡量避免extra列出現：Using File Sort，Using Temporary。 UPDATE、DELETE語句需要根據WHERE條件添加索引。

對超過50個長度的字符串列，最好創建前綴索引而非整列索引（例如：ALTER TABLE t1 ADD INDEX(user(50))），可以有效提高索引利用率，不過它的缺點是對這個列排序時用不到前綴索引。前綴索引的長度可以基于對該字段的統計得出，一般略大于平均長度一點就可以了。 合理創建聯合索引（避免冗余），(a,b,c) 相當于 (a) 、(a,b) 、(a,b,c)，注意不包括(b,c)、(b)、(c)。

2.4.4表注釋、字段注釋

Mysql中字段加注釋比較麻煩，需要用alter table語句，所以盡量在新建表的時候就加上表注釋和字段注釋。類型字段的注釋（注釋中必須包括字段初始值的含義）。

 

來自：http://tech.dianwoda.com/2016/11/08/mysqlshu-ju-ku-kai-fa-gui-fan/