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及之後的版本可能會有些變化，個別規範建議需要相應調整。