老葉觀點：MySQL開發規範之我見（更新版）

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

1、預設使用InnoDB引擎

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

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

當然了，也不是說MyISAM就一無是處，比如老葉之前就把MyISAM用于臨時導資料資料（把資料導入MyISAM，一番處理後再入到InnoDB表）、或者一些特殊的資料統計類場景用MyISAM（大資料量下MyISAM全表順序讀取比InnoDB有明顯優勢）可能比較合适。前提是，你得非常清楚MyISAM引擎的優勢在哪裡。

【參考】：

[MySQL FAQ]系列 — 為什麼InnoDB表要建議用自增列做主鍵

。

2、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列的存儲效率

3、是否使用表分區（partition）

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

比如老葉就在zabbix的資料庫采用TokuDB引擎的前提下，又根據時間次元使用了分區。這樣的好處是保證zabbix日常應用不受到影響前提下，友善管理者例行删除過去資料，隻需要删除相應分區即可，不需再執行一個非常慢的DELETE而影響整體性能。

當然了，使用表分區可能不利于實體擴充，比如大資料量下想要做哈希水準拆分，這個就見仁見智了，如果你的業務場景下使用表分區更有好處，就放心大膽的用吧。該進行拆分就用拆分方案，不要繼續抱着表分區方案不放。

參考：

遷移Zabbix資料庫到TokuDB

4、是否使用存儲過程、觸發器

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

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

有同行認為存儲過程和觸發器的應用可能會導緻發生鎖等待、死鎖時排查問題上的困惑。嗯，這個是有這個可能性，不過如果真發生時，根據當時現場記錄的SQL反查對應的存儲過程或觸發器，應該也不是難事，隻不過要求DBA對線上業務環境更要了然于胸了。

總的來說，不要把MySQL的存儲過程和觸發器視為洪水猛獸，用好的話，沒有問題的，真遇到問題了再優化也不遲。另外，MySQL因為對視圖的處理也不太理想也沒有物化視圖，是以視圖能不用就盡量少用吧。

5、選擇合适的類型

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

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

5.2、枚舉類型可以使用ENUM，ENUM的内部存儲機制是采用TINYINT或SMALLINT（并非CHAR/VARCHAR），性能一點都不差，記住千萬别用CHAR/VARCHAR 來存儲枚舉資料。

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

5.4、所有字段定義中，預設都加上NOT NULL限制，除非必須為NULL（但我也想不出來什麼場景下必須要在資料庫中存儲NULL值，可以用0來表示）。在對該字段進行COUNT()統計時，統計結果更準确（值為NULL的不會被COUNT統計進去），或者執行 WHERE column IS NULL 檢索時，也可以快速傳回結果。

5.5、杜絕直接 SELECT * 讀取全部字段，當表中存在 TEXT/BLOB 大列的時候就會是災難了。可能本來不需要讀取這些列，但因為偷懶寫成 SELECT * 導緻記憶體buffer pool被這些“垃圾”資料把真正需要緩沖起來的熱點資料給洗出去了。

相應地，在寫INSERT時，也要寫上相對應的字段清單。

要求在SQL中寫清楚每個字段的重要意義還在于，當業務需要表DDL發生更新後，如果不寫清楚字段，可能會導緻舊業務代碼不可用，這個就折騰大發了。

6、關于索引

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

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

6.2、定期用 pt-duplicate-key-checker 工具檢查并删除備援的索引。比如 index idx1(a, b) 索引已經涵蓋了 index idx2(a)，就可以删除 idx2 索引了。

6.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則隻能用到部分索引，或者可利用到ICP特性：

• SELECT ... WHERE b = ? AND a = ?; -- 隻能用到 (a, b) 部分
• SELECT ... WHERE a IN (?, ?) AND b = ?; -- EXPLAIN顯示隻用到 (a, b) 部分索引，同時有ICP
• SELECT ... WHERE (a BETWEEN ? AND ?) AND b = ?; -- EXPLAIN顯示隻用到 (a, b) 部分索引，同時有ICP
• SELECT ... WHERE a = ? AND b IN (?, ?); -- EXPLAIN顯示隻用到 (a, b) 部分索引，同時有ICP
• SELECT ... WHERE a = ? AND (b BETWEEN ? AND ?) AND c = ?; -- EXPLAIN顯示用到 (a, b, c) 整個索引，同時有ICP
• SELECT ... WHERE a = ? AND c = ?; -- EXPLAIN顯示隻用到 (a) 部分索引，同時有ICP
• SELECT ... WHERE a = ? AND c >= ?; -- EXPLAIN顯示隻用到 (a) 部分索引，同時有ICP

ICP（index condition pushdown）是MySQL 5.6的新特性，其機制會讓索引的其他部分也參與過濾，減少引擎層和server層之間的資料傳輸和回表請求，通常情況下可大幅提升查詢效率。

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

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

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

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

7、其他

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

7.2、多表JOIN時，要把過濾性最大（不一定是資料量最小哦，而是隻加了WHERE條件後過濾性最大的那個）的表選為驅動表。此外，如果JOIN之後有排序，排序字段一定要屬于驅動表，才能利用驅動表上的索引完成排序。

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

7.4、利用 pt-query-digest 定期分析slow query log，并結合 Box Anemometer 建構slow query log分析及優化系統。

[MySQL FAQ]系列 — EXPLAIN結果中哪些資訊要引起關注

備注：若無特别說明，以上規範建議适用于MySQL 5.6及之前的版本（并且主要是5.6版本，尤其是ICP特性、DATETIME變化這兩個地方）。5.7及之後的版本可能會有些變化，個别規範建議需要相應調整。

特别感謝周彥偉、淘寶丁奇，對本文中的一些觀點做了深入詳細的探讨和完善。