背景

一個平臺或系統隨著時間的推移和用戶量的增多，數據庫操作往往會變慢;而在Java應用開發中數據庫更是尤為重要，絕大多數情況下數據庫的性能決定了程序的性能，如若前期埋下的坑越多到后期數據庫就會成為整個系統的瓶頸;因此，更規范化的使用MySQL在開發中是不可或缺的。

[[325931]]

一、MySQL數據庫命名規范

1. 數據庫所有表前綴均使用項目名稱首字母縮寫;
2. 數據庫所有對象名稱均使用小寫字母，并且單詞之間通過下劃線分開;
3. 數據庫所有對象名稱禁止使用MySQL保留字及關鍵字，涉及到關鍵字的SQL查詢需要將關鍵字用單引號括起來;
4. 數據庫所有對象名稱不超過32個字符，并且命名要遵循見名知意原則;
5. 數據庫臨時表必須以 pro_tmp_ 為前綴并且以日期 20190917 為后綴，備份表必須以 pro_bac 為前綴并以時間戳為后綴;(pro為項目名稱首字母縮寫)
6. 數據庫所有存儲相同數據的列名和列類型必須保持一致。

二、MySQL數據庫基本設計規范

1、若無特殊說明，建表時一律采用Innodb存儲引擎。

選擇合適的引擎可以提高數據庫性能，如InnoDB和MyISAM，InnoDB和MyISAM是許多人在使用MySQL時最常用的兩個表類型，這兩個表類型各有優劣，視具體應用而定;

基本的差別為：MyISAM類型不支持事務處理等高級處理，而InnoDB類型支持;MyISAM類型的表強調的是性能，其執行速度比InnoDB類型更快，但是不提供事務支持，而InnoDB提供事務支持以及外部鍵等高級數據庫功能;

因此，其支持事務處理、支持外鍵、支持崩潰修復能力和并發控制是我們建表時首選的存儲引擎。

2、數據庫和表的字符集統一使用UTF8

數據庫和表的字符集統一使用utf8，若是有字段需要存儲emoji表情之類的，則將表或字段設置成utf8mb4;因為，utf8號稱萬國碼，其無需轉碼、無亂碼風險且節省空間，而utf8mb4又向下兼容utf8。

3、設計數據庫時所有表和字段必須添加注釋

使用Comment從句添加表和列的備注，或直接在數據庫連接工具的注釋欄添加注釋，從項目開始就進行數據字典的維護。

使用Comment從句添加注釋如：

-- 1、創建表：

CREATE TABLE t1(id varchar2(32) primary key,name VARCHAR2(8) NOT NULL,age number); 

-- 2、添加表注釋：

Comment on table t1 is '個人信息'; 

-- 3、添加字段注釋：

comment on column t1.id is 'id';  
comment on column t1.nameis '姓名';  
comment on column t1.age is '年齡'; 

使用數據庫連接工具添加注釋：

4、單個表的數據量大小控制在500萬以內

盡量控制單表數據量的大小，建議控制在500萬以內;500萬并不是MySQL數據庫的極限，但數據量太多不利于對表結構進行修改、備份和恢復數據，適當采用分庫分表等手段來控制單表數據量的大小。

5、使用MySQL分區表需謹慎

分區是將一個表的數據按照某種方式，比如按照時間上的月份，分成多個較小的，更容易管理的部分，但是邏輯上仍是一個表;分區表在物理上表現為多個文件，在邏輯上仍表現為同一個表，需要謹慎選擇分區鍵;跨分區查詢效率可能會更低，建議使用物理分區表等方式管理大數據。

6、盡量滿足冷熱數據分離，減小表等寬度

MySQL限制每個表最多存儲4096列，并且每一行數據的大小不超過65535字節，為了減少磁盤IO線程的開銷，就要適當控制表的寬度，因為表越寬，把表裝載進內存緩沖池時所占用的內存也就越大，就會消耗更多的IO線程;除此之外，為了保證熱數據的內存緩存命中率，更有效的利用緩存，避免讀入無用的冷數據，盡量把經常使用到的列放到同一個表中，避免不必要的關聯操作。

7、建立預留字段需謹慎

部分友人在設計數據庫表時，不僅設計了當前所需要的字段，而且還在其中留出幾個字段作為備用。比方說，我設計了一個人員表(Person)，其中已經添加了各種必要的字段，包括姓名(Name)、性別(Sex)、出生年月日(birthday)等等;

為了以防萬一，比如之后可能Person 表會涉及到畢業院校、工作單位、是否婚配和相片等信息，于是就加入5個varchar2 型的字段，分別叫做Text1、Text2……Text5;這一手操作看似防范于未然，其實也并不見得，因為大量預留字段會浪費空間、預留字段不能做到見名知意、預留字段無法確認存儲的數據類型且修改其字段類型還可能會造成鎖表等問題。

針對此等情況可以參考以下兩點解決方案：

• 如果數量很少，而且信息的性質與原表密切相關，那么就可以直接在原表上增加字段，并將相關的數據更新進去;
• 如果數量較大，或者并非是原表對象至關重要的屬性，那么就可以新增一個表，然后通過鍵值連接起來;

8、數據庫中禁止存儲圖片、文件等大的二進制數據

若往數據庫表中存儲文件，而文件通常很大，當數據庫進行讀取操作時，會進行大量的隨機IO操作，大文件使得IO操作很耗時耗性能，造成短時間內數據量快速增長;所以，通常將圖片、文件存儲在文件服務器中，數據庫只用于存儲文件地址信息。

三、MySQL數據庫字段設計規范

1、優先選擇符合存儲需要的最小的數據類型。

主要是考慮索引的性能，因為列的字段越大，建立索引時所需要的空間也越大，這樣一頁中能存儲的索引節點的數量也就越少，在遍歷時需要的IO次數也就越多，索引的性能也就越差。

2、避免使用TEXT、BLOB數據類型

避免使用TEXT和BLOB數據類型，其中最常見的TEXT類型可以存儲64K數據，MySQL內存臨時表不支持TEXT、BLOB這樣的大數據類型，若查詢中包含這樣的數據，在執行排序等操作時就不能使用內存臨時表，必須使用磁盤臨時表執行操作;

TEXT和BLOB類型只能使用前綴索引(當索引是很長的字符序列時，這個索引將會很占內存，而且會很慢，這時候就會用到前綴索引了;所謂的前綴索引就是去索引的前面幾個字母作為索引，但是要降低索引的重復率，所以我們還必須要判斷前綴索引的重復率;)，因為MySQL對索引字段長度是有限的，所以TEXT類型只能使用前綴索引，并且TEXT列上是不能有默認值的;

若需要使用，建議把BLOB或TEXT列分離到單獨的的擴展表中，且查詢時一定不要使用select *，只需取出必要的列即可。

3、避免使用ENUM枚舉類型

• 修改ENUM 值需要使用ALTER 語句;
• ENUM 類型的ORDER BY 操作效率低;
• 禁止使用數值作為ENUM 的枚舉值。

4、所有列的默認值定義為NOT NULL

數據庫所有為NULL 的列需要額外的空間來存儲，因此會占用更多的空間;

數據庫在進行比較和計算時需要對NULL 值做特別處理。

5、使用TIMESTAMP(4字節)或DATETIME(8字節)類型存儲時間

TIMESTAMP 存儲的時間范圍為：1970-01-01 00:00:01 ~ 2038-01-19-03:14:07;

TIMESTAMP 占用4字節和INT相同，但可讀性比INT 類型的高，若是超出TIMESTAMP 取值范圍的則使用DATETIME 類型存儲;

用字符串類型存儲時間的缺點：無法使用日期函數進行比較計算、字符串存儲占有更多的空間。

6、財務相關的金額類數據必須使用decimal 類型

• 精準浮點：decimal
• 非精準浮點：float、double

Decimal類型為精準浮點數，在計算時不會丟失精度;占有空間大小由定義的寬度決定，每4個字節可以存儲9位數字，且小數點也要占有一個字節;另外，Decimal類型可用于存儲比bigint更大的數據類型。

四、MySQL索引設計規范

1、每張表的索引數量不超過5個

索引可以增加查詢效率，但同樣也會降低插入和更新的效率，甚至有些情況下還會降低查詢效率，因此并不是越多越好，要控制其數量。

2、每個Innodb 表必須有一個主鍵

Innodb 是一種索引組織表，其數據存儲的邏輯順序和索引的順序是相同的;

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

3、盡量避免使用外鍵約束

不建議使用外鍵約束(foreign key)，但一定要在表與表之間的關聯鍵上建立索引;

外鍵雖然可以保證數據的參照完整性，但外鍵也會影響父表和子表的寫操作從而降低性能，還會使得表更耦合，建議在業務端實現。

五、MySQL數據庫SQL開發規范

1、建議使用預編譯語句進行數據庫操作

預編譯語句可以重復使用，相同的SQL語句可以一次解析，多次使用，減少SQL編譯所需要的時間，提高處理效率;此外，還可以有效解決動態SQL帶來的SQL注入問題。

2、避免數據類型的隱式轉換

隱式轉換如：SELECT 1 + "1";數值型 + 字符型 的隱式轉換有可能會導致索引失效，以及一些意想不到的結果等。

3、充分利用表中存在的索引

• 1)避免使用雙%號的查詢條件

如 WHERE first_name like '%James%'，若無前置%，只有后置%，則執行SQL語句時會用到列上的索引，雙%號則不會使用列上的索引。

• 2)一條SQL語句只能使用復合索引中的一列進行范圍查詢

例如有weight、age、sex三列的聯合索引，在查詢條件中有weight列的范圍查詢，則在age和sex列上的索引將不會被使用;因此，在定義聯合索引時，若某列需要用到范圍查詢，則將該列放到聯合索引的右側。

• 3)使用not exists 代替not in

因為not in 在SQL語句中執行時會導致索引失效。

4、杜絕使用SELECT * ，必須使用SELECT <字段列表> 查詢

因為使用SELECT * 查詢會消耗更多的CPU、IO和網絡寬帶資源，并且查詢時無法使用覆蓋索引。

5、禁止使用不含字段列表的INSERT 語句

如：INSERT into table_name values ('1','2','3'); 改為帶字段列表的INSERT 語句：INSERT into table_name('c1','c2','c3') values ('1','2','3');

6、避免使用子查詢，可以把子查詢優化為join 關聯操作

但是，通常子查詢在in 子句中，且子查詢中為簡單SQL(即不包含union、group by、order by、limit從句)時，才可以把子查詢轉化為join關聯查詢進行優化;

子查詢性能差的原因：

• 子查詢的結果集無法使用索引，通常子查詢的結果集會被存儲到臨時表中，不論是內存臨時表還是磁盤臨時表都不會存在索引，所以查詢性能會受到一定的影響;
• 由于子查詢會產生大量的臨時表也沒有索引，所以會消耗過多的CPU和IO資源，產生大量的慢查詢。

7、避免使用JOIN 關聯太多表

• 在Mysql中，對于同一個SQL關聯(join)多個表，每個join 就會多分配一個關聯緩存，如果在一個SQL中關聯的表越多，所占用的內存也就越大;
• 如果程序中大量的使用了多表關聯的操作，同時join_buffer_size(MySQL允許關聯緩存的個數)設置的也不合理的情況下，就容易造成服務器內存溢出的情況，就會影響服務器數據庫性能的穩定性;
• 此外，對于關聯操作來說，會產生臨時表影響查詢效率，而Mysql最多允許關聯61個表，建議不超過5個;

8、對同一列對象進行or 判斷時，使用in 替代or

in 的值只要涉及不超過500個，則in 操作可以更有效的利用索引，or 大多數情況下很少能利用到索引。

9、禁止使用order by rand() 進行隨機排序

10、禁止在WHERE 從句中對列進行函數轉換和計算

因為在WHERE 從句中對列進行函數轉換或計算時會導致索引無法使用。

No推薦：

where date(end_time)='20190101' 

推薦：

where end_time >= '20190101' and end_time < '20190102' 

11、在明顯不會有重復值時使用UNION ALL 而不是UNION

• UNION 會把兩個結果集的所有數據放到臨時表中后再進行去重操作;
• UNION ALL 不會再對結果集進行去重操作;

12、把復雜、較長的SQL 拆分為為多個小SQL 執行

• 大SQL在邏輯上比較復雜，是需要占用大量CPU 進行計算一條SQL語句;
• 在MySQL中，一條SQL 語句只能使用一個CPU 進行計算;
• SQL拆分后可以通過并行執行來提高處理效率。

六、MySQL數據庫行為規范

1、超過100萬行數據的批量操作(update delete insert)，分多次進行

• 大批量操作可能會造成嚴重的主從延遲;
• binlog日志為row格式時會產生大量的日志;
• 避免產生大事物操作。

2、對于大表使用pt-online-schema-change 修改表結構

• 避免大表修改產生的主從延遲、避免在對表字段進行修改時進行鎖表;
• pt-online-schema-change 它首先會建立一個與原表結構相同的新表，并且在新表上進行表結構的修改，然后再把原表中的數據復制到新表中，并在原表中增加一些觸發器;然后，把原表中新增的數據也復制到新表中，在行所有數據復制完成之后，把新表命名成原表，并把原來的表刪除掉，其是把原來一個DDL操作，分解成多個小的批次執行。

3、禁止給程序使用的賬號授予super 權限

當達到最大連接數限制時，還運行1個有super權限的用戶連接super權限只能留給DBA處理問題的賬號使用。

4、對于程序連接數據庫賬號，遵循權限最小原則

程序使用數據庫賬號只能在一個數據庫下使用，且程序使用的賬號原則上不授予drop 權限。