MySQL開發規範之我見

大多數MySQL規範在網路上也都能找到相關的分享，在這裡要分享的是老葉個人認為比較重要的，或者容易被忽略的，以及容易被混淆的一些地方。

　　1、預設使用InnoDB引擎

　　【老葉觀點】已多次呼籲過了，InnoDB適用於幾乎99%的MySQL應用場景，而且在MySQL 5.7的系統表都改成InnoDB了，還有什麼理由再死守MyISAM呢。

　　此外，頻繁讀寫的InnoDB表，一定要使用具有自增/順序特徵的整數作為顯式主鍵。

　　【參考】：[MySQL FAQ]系列 — 為什麼InnoDB表要建議用自增列做主鍵。

　　2、字元集選擇utf-8

　　【老葉觀點】若為了節省磁碟空間，則建議選擇latin1。建議選擇utf-8通常是為了所謂的“通用性”，但事實上用戶提交的utf-8資料也一樣可以以latin1字元集儲存。

　　用latin1儲存utf-8資料可能遇到的麻煩是，如果有基於中文的檢索時，可能無法100%準確(老葉親自簡單測試常規的中文完檢索全不是問題，也就是一般的中文對比是沒問題的)。

　　用latin1字元集儲存utf-8資料的做法是：在web端(用戶端)的字元集是utf-8，後端程式也採用utf-8來處理，但character_set_client、character_set_connection、 character_set_results、character_set_database、character_set_server 這幾個都是latin1，且資料表、欄位的字元集也是latin1。或者說資料表採用latin1，每次連線後執行 SET NAMES LATIN1 即可。

　　【參考】：小談MySQL字元集。

　　3、InnoDB表格行記錄物理長度不超過8KB

##　　【老葉觀點】InnoDB的data page預設是16KB，基於B+Tree的特點，一個data page中需要至少儲存2筆記錄。因此，當實際儲存長度超過8KB(尤其是TEXT/BLOB列)的大列(large column)時會引起“page-overflow儲存”，類似ORACLE中的“行遷移”。

　　因此，如果必須使用大列(尤其是TEXT/BLOB類型)且讀寫頻繁的話，則最好把這些列拆分到子表中，不要和主表放在一起存儲。如果不太頻繁，可以考慮繼續保留在主表中。

　　當然了，如果將 innodb_page_size 選項修改成 8KB，那麼行記錄物理長度建議不要超過4KB。

　　【

參考】：[MySQL最佳化案例]系列 — 最佳化InnoDB表BLOB列的儲存效率。

　　

4、是否使用分區表

　　【老葉觀點】在一些使用分區表後明顯可以提升性能或者運維便利性的場景下，還是建議使用分區表。

　　例如老葉就在zabbix的資料庫採用TokuDB引擎的前提下，又根據時間維度使用了分區表。這樣的好處是保證zabbix日常應用不受到影響前提下，方便管理員例行刪除過去數據，只需要刪除相應分區即可，不需再執行一個非常慢的DELETE而影響整體性能。

　　【

參考】：遷移Zabbix資料庫到TokuDB。

　　

5、是否使用預存程序、觸發器

　　【老葉觀點】在一些合適的場景下，用預存程序、觸發器也完全沒問題。

　　我們以前就是利用儲存完成遊戲業務邏輯處理，效能上不是問題，而且一旦需求有變更，只需修改預存過程，變更代價很低。我們也利用觸發器來維護一個頻繁更新的表，對這個表的所有變更都將部分字段同步更新到另一個表中(類似物化視圖的變相實現)，也不存在性能問題。

　　不要把MySQL的預存程序和觸發器視為洪水猛獸，用好的話，沒有問題的，真遇到問題了再優化也不遲。另外，MySQL因為沒有物化視圖，所以視圖能不用就盡量少用吧。

　　

6、選擇合適的類型

　　【老葉觀點】除了常見的建議外，還有其他幾個要點：

#　　6.1、用INT UNSIGNED儲存IPV4位址，用INET_ATON()、INET_NTOA()進行轉換，基本上沒必要使用CHAR(15)來儲存。

　　6.2、枚舉型別可以使用ENUM，ENUM的內部儲存機制是採用TINYINT或SMALLINT(並非CHAR/VARCHAR)，效能一點都不差，記住千萬別用CHAR/VARCHAR 來儲存欄位舉數據。

　　6.3、還個早前一直在傳播的“常識性誤導”，建議用TIMESTAMP取代DATETIME。其實從5.6開始，建議優先選擇DATETIME儲存日期時間，因為它的可用範圍比TIMESTAMP更大，實體儲存上僅比TIMESTAMP多1個位元組，整體效能上的損失並不大。

　　6.4、所有欄位定義中，預設都加上NOT NULL約束，除非必須為NULL(但我也想不出來什麼場景下必須要在資料庫中儲存NULL值，可以用0來表示)。在對此欄位進行COUNT()統計時，統計結果較準確(值為NULL的不會被COUNT統計進去)，或執行 WHERE column IS NULL 擷取時，也可以快速傳回結果。

　　6.5、盡可能不要直接 SELECT * 讀取全部字段，尤其是表中存在 TEXT/BLOB 大列的時候。可能本來不需要讀取這些列，但因為偷懶寫成 SELECT * 導致記憶體buffer pool被這些「垃圾」資料把真正需要緩衝起來的熱點資料給洗出去了。

　　8、關於索引

　　【老葉觀點】除了常見的建議外，還有幾個要點：

　　8.1、超過20個長度的字串列，最好建立前綴索引而非整列索引(例如：ALTER TABLE t1 ADD INDEX(user(20)))，可以有效提高索引利用率，不過它的缺點是對這個列排序時用不到前綴索引。前綴索引的長度可以基於對該欄位的統計得出，一般略大於平均長度一點就可以了。

　　8.2、定期用 pt-duplicate-key-checker 工具檢查並刪除重複的索引。例如 index idx1(a, b) 索引已經涵蓋了 index idx2(a)，就可以刪除 idx2 索引了。

　　8.3、有多欄位聯合索引時，WHERE中篩選條件的欄位順序無需和索引一致，但如果有排序、分組就必須一致了。

　　例如有聯合索引idx1(a, b, c)，那麼下面的SQL都可以完整用到索引：

SELECT ... WHERE b = ? AND c = ? AND a = ?;  --注意到，WHERE中字段顺序并没有和索引字段顺序一致
SELECT ... WHERE b = ? AND a = ? AND c = ?;
SELECT ... WHERE a = ? AND b IN (?, ?) AND c = ?;
SELECT ... WHERE a = ? AND b = ? ORDER BY c;
SELECT ... WHERE a = ? AND b IN (?, ?) ORDER BY c;
SELECT ... WHERE a = ? ORDER BY b, c;
SELECT ... ORDER BY a, b, c;  -- 可利用联合索引完成排序

　　而下面幾個SQL則只能用到部分索引：

SELECT ... WHERE b = ? AND a = ?;   -- 只能用到 (a, b) 部分
SELECT ... WHERE a IN (?, ?) AND b = ?;   -- 只能用到 (a, b) 部分
SELECT ... WHERE a = ? AND c = ?;   -- 只能用到 (a) 部分
SELECT ... WHERE a = ? AND b IN (?, ?);    -- 只能用到 (a, b) 部分
SELECT ... WHERE (a BETWEEN ? AND ?) AND b = ?;   -- 只能用到 (a) 部分，注意BETWEEN和IN的区别
SELECT ... WHERE a = ? AND (b BETWEEN ? AND ?) AND c = ?;    -- 只能用到 (a, b) 部分

　　下面的幾個SQL完全用不到該索引：

SELECT ... WHERE b = ?;
SELECT ... WHERE b = ? AND c = ?;
SELECT ... WHERE b = ? AND c = ?;
SELECT ... ORDER BY b;
SELECT ... ORDER BY b, a;

　　從上面的幾個例子就能看出來的出來，以往強調的WHERE條件字段順序要和索引順序一致才能使用索引的“常識性誤導” 無需嚴格遵守。

　　此外，有些時候查詢優化器指定的索引或執行計劃可能並不是最優的，可以手動指定最優索引，或者修改session級的optimizer_switch 選項，關閉某些導致效果反而更差的特性(例如index merge通常是好事，但也遇到過用上index merge後反而更差的，這時候要么強制指定其中一個索引，要么可以臨時關閉index merge 特性)。

　　9、其他

　　9.1、哪怕是基於索引的條件過濾，如果優化器意識到總共需要掃描的資料量超過30%時(ORACLE裡貌似是20%，MySQL目前是30%，沒準以後會調整)，就會直接改變執行計畫為全表掃描，不再使用索引。

　　9.2、多表JOIN時，要把過濾性最大(不一定是資料量最小哦，而是只加了WHERE條件後過濾性最大的那個)的表選為驅動表。此外，如果JOIN之後有排序，排序欄位一定要屬於驅動表，才能利用驅動表上的索引完成排序。

　　9.3、絕大多數情況下，排序的大家通常要來的更高，因此如果看到執行計劃中有 Using filesort，優先創建排序索引吧。

　　9.4、利用 pt-query-digest 定期分析slow query log，並結合 Box Anemometer 建構slow query log分析及最佳化系統。

　　【參考】：[MySQL FAQ]系列 — EXPLAIN結果中哪些資訊要引起關注。

　　備註：若無特別說明，以上規範建議適用於MySQL 5.6及之前的版本。 5.7及之後的版本可能會有一些變化，個別規範建議需要相應調整。

以上就是MySQL開發規範之我見的內容，更多相關內容請關注PHP中文網（www.php.cn）！