大多數(shù)MySQL規(guī)范在網(wǎng)上也都能找得到相關(guān)的分享，在這里要分享的是老葉個(gè)人認(rèn)為比較重要的，或者容易被忽視的，以及容易被混淆的一些地方。

1、默認(rèn)使用InnoDB引擎

【老葉觀點(diǎn)】已多次呼吁過(guò)了，InnoDB適用于幾乎99%的MySQL應(yīng)用場(chǎng)景，而且在MySQL 5.7的系統(tǒng)表都改成InnoDB了，還有什么理由再死守MyISAM呢。

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

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

2、字符集選擇utf-8

【老葉觀點(diǎn)】若為了節(jié)省磁盤(pán)空間，則建議選擇latin1。建議選擇utf-8通常是為了所謂的“通用性”，但事實(shí)上用戶(hù)提交的utf-8數(shù)據(jù)也一樣可以以latin1字符集存儲(chǔ)。

用latin1存儲(chǔ)utf-8數(shù)據(jù)可能遇到的麻煩是，如果有基于中文的檢索時(shí)，可能無(wú)法100%準(zhǔn)確(老葉親自簡(jiǎn)單測(cè)試常規(guī)的中文完檢索全不是問(wèn)題，也就是一般的中文對(duì)比是沒(méi)問(wèn)題的)。

用latin1字符集存儲(chǔ)utf-8數(shù)據(jù)的做法是：在web端(用戶(hù)端)的字符集是utf-8，后端程序也采用utf-8來(lái)處理，但 character_set_client、character_set_connection、character_set_results、character_set_database、character_set_server 這幾個(gè)都是 latin1，且數(shù)據(jù)表、字段的字符集也是latin1。或者說(shuō)數(shù)據(jù)表采用latin1，每次連接后執(zhí)行 SET NAMES LATIN1 即可。

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

3、InnoDB表行記錄物理長(zhǎng)度不超過(guò)8KB

【老葉觀點(diǎn)】InnoDB的data page默認(rèn)是16KB，基于B+Tree的特點(diǎn)，一個(gè)data page中需要至少存儲(chǔ)2條記錄。因此，當(dāng)實(shí)際存儲(chǔ)長(zhǎng)度超過(guò)8KB(尤其是TEXT/BLOB列)的大列(large column)時(shí)會(huì)引起“page-overflow存儲(chǔ)”，類(lèi)似ORACLE中的“行遷移”。

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

當(dāng)然了，如果將 innodb_page_size 選項(xiàng)修改成 8KB，那么行記錄物理長(zhǎng)度建議不超過(guò)4KB。

【參考】：[MySQL優(yōu)化案例]系列 — 優(yōu)化InnoDB表BLOB列的存儲(chǔ)效率。

4、是否使用分區(qū)表

【老葉觀點(diǎn)】在一些使用分區(qū)表后明顯可以提升性能或者運(yùn)維便利性的場(chǎng)景下，還是建議使用分區(qū)表。

比如老葉就在zabbix的數(shù)據(jù)庫(kù)采用TokuDB引擎的前提下，又根據(jù)時(shí)間維度使用了分區(qū)表。這樣的好處是保證zabbix日常應(yīng)用不受到影響前提下，方便管理員例行刪除過(guò)去數(shù)據(jù)，只需要?jiǎng)h除相應(yīng)分區(qū)即可，不需再執(zhí)行一個(gè)非常慢的DELETE而影響整體性能。

【參考】：遷移Zabbix數(shù)據(jù)庫(kù)到TokuDB。

5、是否使用存儲(chǔ)過(guò)程、觸發(fā)器

【老葉觀點(diǎn)】在一些合適的場(chǎng)景下，用存儲(chǔ)過(guò)程、觸發(fā)器也完全沒(méi)問(wèn)題。

我們以前就是利用存儲(chǔ)完成游戲業(yè)務(wù)邏輯處理，性能上不是問(wèn)題，而且一旦需求有變更，只需修改存儲(chǔ)過(guò)程，變更代價(jià)很低。我們還利用觸發(fā)器維護(hù)一個(gè)頻繁更新的表，對(duì)這個(gè)表的所有變更都將部分字段同步更新到另一個(gè)表中(類(lèi)似物化視圖的變相實(shí)現(xiàn))，也不存在性能問(wèn)題。

不要把MySQL的存儲(chǔ)過(guò)程和觸發(fā)器視為洪水猛獸，用好的話(huà)，沒(méi)有問(wèn)題的，真遇到問(wèn)題了再優(yōu)化也不遲。另外，MySQL因?yàn)闆](méi)有物化視圖，因此視圖能不用就盡量少用吧。

6、選擇合適的類(lèi)型

【老葉觀點(diǎn)】除了常見(jiàn)的建議外，還有其他幾個(gè)要點(diǎn)：

6.1、用INT UNSIGNED存儲(chǔ)IPV4地址，用INET_ATON()、INET_NTOA()進(jìn)行轉(zhuǎn)換，基本上沒(méi)必要使用CHAR(15)來(lái)存儲(chǔ)。

6.2、枚舉類(lèi)型可以使用ENUM，ENUM的內(nèi)部存儲(chǔ)機(jī)制是采用TINYINT或SMALLINT(并非CHAR/VARCHAR)，性能一點(diǎn)都不差，記住千萬(wàn)別用CHAR/VARCHAR 來(lái)存儲(chǔ)枚舉數(shù)據(jù)。

6.3、還個(gè)早前一直在傳播的“常識(shí)性誤導(dǎo)”，建議用TIMESTAMP取代DATETIME。其實(shí)從5.6開(kāi)始，建議優(yōu)先選擇DATETIME存儲(chǔ)日期時(shí)間，因?yàn)樗目捎梅秶萒IMESTAMP更大，物理存儲(chǔ)上僅比TIMESTAMP多1個(gè)字節(jié)，整體性能上的損失并不大。

6.4、所有字段定義中，默認(rèn)都加上NOT NULL約束，除非必須為NULL(但我也想不出來(lái)什么場(chǎng)景下必須要在數(shù)據(jù)庫(kù)中存儲(chǔ)NULL值，可以用0來(lái)表示)。在對(duì)該字段進(jìn)行COUNT()統(tǒng)計(jì)時(shí)，統(tǒng)計(jì)結(jié)果更準(zhǔn)確(值為NULL的不會(huì)被COUNT統(tǒng)計(jì)進(jìn)去)，或者執(zhí)行 WHERE column IS NULL 檢索時(shí)，也可以快速返回結(jié)果。

6.5、盡可能不要直接 SELECT * 讀取全部字段，尤其是表中存在 TEXT/BLOB 大列的時(shí)候。可能本來(lái)不需要讀取這些列，但因?yàn)橥祽袑?xiě)成 SELECT * 導(dǎo)致內(nèi)存buffer pool被這些“垃圾”數(shù)據(jù)把真正需要緩沖起來(lái)的熱點(diǎn)數(shù)據(jù)給洗出去了。

8、關(guān)于索引

【老葉觀點(diǎn)】除了常見(jiàn)的建議外，還有幾個(gè)要點(diǎn)：

8.1、超過(guò)20個(gè)長(zhǎng)度的字符串列，最好創(chuàng)建前綴索引而非整列索引(例如：ALTER TABLE t1 ADD INDEX(user(20)))，可以有效提高索引利用率，不過(guò)它的缺點(diǎn)是對(duì)這個(gè)列排序時(shí)用不到前綴索引。前綴索引的長(zhǎng)度可以基于對(duì)該字段的統(tǒng)計(jì)得出，一般略大于平均長(zhǎng)度一點(diǎn)就可以了。

8.2、定期用 pt-duplicate-key-checker 工具檢查并刪除重復(fù)的索引。比如 index idx1(a, b) 索引已經(jīng)涵蓋了 index idx2(a)，就可以刪除 idx2 索引了。

8.3、有多字段聯(lián)合索引時(shí)，WHERE中過(guò)濾條件的字段順序無(wú)需和索引一致，但如果有排序、分組則就必須一致了。

比如有聯(lián)合索引 idx1(a, b, c)，那么下面的SQL都可以完整用到索引：

SELECT ... WHERE b = ? AND c = ? AND a = ?; --注意到，WHERE中字段順序并沒(méi)有和索引字段順序一致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; -- 可利用聯(lián)合索引完成排序

而下面幾個(gè)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的區(qū)別SELECT ... WHERE a = ? AND (b BETWEEN ? AND ?) AND c = ?; -- 只能用到 (a, b) 部分

下面的幾個(gè)SQL完全用不到該索引：

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

從上面的幾個(gè)例子就能看的出來(lái)，以往強(qiáng)調(diào)的WHERE條件字段順序要和索引順序一致才能使用索引的 “常識(shí)性誤導(dǎo)” 無(wú)需嚴(yán)格遵守。

此外，有些時(shí)候查詢(xún)優(yōu)化器指定的索引或執(zhí)行計(jì)劃可能并不是最優(yōu)的，可以手工指定最優(yōu)索引，或者修改session級(jí)的 optimizer_switch 選項(xiàng)，關(guān)閉某些導(dǎo)致效果反而更差的特性(比如index merge通常是好事，但也遇到過(guò)用上index merge后反而更差的，這時(shí)候要么強(qiáng)制指定其中一個(gè)索引，要么可以臨時(shí)關(guān)閉 index merge 特性)。

9、其他

9.1、哪怕是基于索引的條件過(guò)濾，如果優(yōu)化器意識(shí)到總共需要掃描的數(shù)據(jù)量超過(guò)30%時(shí)(ORACLE里貌似是20%，MySQL目前是30%，沒(méi)準(zhǔn)以后會(huì)調(diào)整)，就會(huì)直接改變執(zhí)行計(jì)劃為全表掃描，不再使用索引。

9.2、多表JOIN時(shí)，要把過(guò)濾性最大(不一定是數(shù)據(jù)量最小哦，而是只加了WHERE條件后過(guò)濾性最大的那個(gè))的表選為驅(qū)動(dòng)表。此外，如果JOIN之后有排序，排序字段一定要屬于驅(qū)動(dòng)表，才能利用驅(qū)動(dòng)表上的索引完成排序。

9.3、絕大多數(shù)情況下，排序的大家通常要來(lái)的更高，因此如果看到執(zhí)行計(jì)劃中有 Using filesort，優(yōu)先創(chuàng)建排序索引吧。

9.4、利用 pt-query-digest 定期分析slow query log，并結(jié)合 Box Anemometer 構(gòu)建slow query log分析及優(yōu)化系統(tǒng)。

【參考】：[MySQL FAQ]系列 — EXPLAIN結(jié)果中哪些信息要引起關(guān)注。

備注：若無(wú)特別說(shuō)明，以上規(guī)范建議適用于MySQL 5.6及之前的版本。5.7及之后的版本可能會(huì)有些變化，個(gè)別規(guī)范建議需要相應(yīng)調(diào)整。