如何更規範化使用MySQL

• 2019 年 10 月 3 日
• 筆記

如何更規範化使用MySQL

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

一、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 '年齡'; 

使用資料庫連接工具添加註釋：

圖1. 資料庫連接工具添加註釋

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

      盡量控制單表數據量的大小，建議控制在500萬以內；500萬並不是MySQL資料庫的極限，但數據量太多不利於對錶結構進行修改、備份和恢複數據，適當採用分庫分表等手段來控制單表數據量的大小。

5、使用MySQL分區表需謹慎

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

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

      MySQL限制每個表最多存儲4096列，並且每一行數據的大小不超過65535位元組，為了減少磁碟IO執行緒的開銷，就要適當控制表的寬度，因為表越寬，把表裝載進記憶體緩衝池時所佔用的記憶體也就越大，就會消耗更多的IO執行緒；除此之外，為了保證熱數據的記憶體快取命中率，更有效的利用快取，避免讀入無用的冷數據，盡量把經常使用到的列放到同一個表中，避免不必要的關聯操作。

7、建立預留欄位需謹慎

      部分友人在設計資料庫表時，不僅設計了當前所需要的欄位，而且還在其中留出幾個欄位作為備用。比方說，我設計了一個人員表（Person），其中已經添加了各種必要的欄位，包括姓名（Name）、性別（Sex）、出生年月日（birthday）等等；為了以防萬一，比如之後可能Person 表會涉及到畢業院校、工作單位、是否婚配和相片等資訊，於是就加入5個varchar2 型的欄位，分別叫做Text1、Text2……Text5；這一手操作看似防範於未然，其實也並不見得，因為大量預留欄位會浪費空間、預留欄位不能做到見名知意、預留欄位無法確認存儲的數據類型且修改其欄位類型還可能會造成鎖表等問題。

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

1. 如果數量很少，而且資訊的性質與原表密切相關，那麼就可以直接在原表上增加欄位，並將相關的數據更新進去；
2. 如果數量較大，或者並非是原表對象至關重要的屬性，那麼就可以新增一個表，然後通過鍵值連接起來；

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關聯查詢進行優化；

子查詢性能差的原因：

1）子查詢的結果集無法使用索引，通常子查詢的結果集會被存儲到臨時表中，不論是記憶體臨時表還是磁碟臨時表都不會存在索引，所以查詢性能會受到一定的影響；

2）由於子查詢會產生大量的臨時表也沒有索引，所以會消耗過多的CPU和IO資源，產生大量的慢查詢。

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

1）在Mysql中，對於同一個SQL關聯（join）多個表，每個join 就會多分配一個關聯快取，如果在一個SQL中關聯的表越多，所佔用的記憶體也就越大；

2）如果程式中大量的使用了多表關聯的操作，同時join_buffer_size(MySQL允許關聯快取的個數)設置的也不合理的情況下，就容易造成伺服器記憶體溢出的情況，就會影響伺服器資料庫性能的穩定性；

3）此外，對於關聯操作來說，會產生臨時表影響查詢效率，而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

1）UNION 會把兩個結果集的所有數據放到臨時表中後再進行去重操作；

2）UNION ALL 不會再對結果集進行去重操作；

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

1）大SQL在邏輯上比較複雜，是需要佔用大量CPU 進行計算一條SQL語句；

2）在MySQL中，一條SQL 語句只能使用一個CPU 進行計算；

3）SQL拆分後可以通過並行執行來提高處理效率。

六、MySQL資料庫行為規範

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

大批量操作可能回造成嚴重的主從延遲；

binlog日誌為row格式時會產生大量的日誌；

避免產生大事物操作。

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

1）避免大表修改產生的主從延遲、避免在對錶欄位進行修改時進行鎖表；

2）pt-online-schema-change 它首先會建立一個與原表結構相同的新表，並且在新表上進行表結構的修改，然後再把原表中的數據複製到新表中，並在原表中增加一些觸發器；然後，把原表中新增的數據也複製到新表中，在行所有數據複製完成之後，把新表命名成原表，並把原來的表刪除掉，其是把原來一個DDL操作，分解成多個小的批次執行。

3、禁止給程式使用的帳號授予super 許可權

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

4、對於程式連接資料庫帳號，遵循許可權最小原則

      程式使用資料庫帳號只能在一個資料庫下使用，且程式使用的帳號原則上不授予drop 許可權。