最重要的MySQL開發規範，全都在這了！

今天，騰訊雲學院來分享一篇技術文章——有關MySQL的開發規範。文章開始之前，先給大家派送一個超級福利！

騰訊雲學院邀請到本文作者——「知數堂」培訓聯合創始人、知名MySQL專家葉金榮老師和吳炳錫老師，為大家現場直播分享！大家可以在線與兩位老師答疑互動哦~

直播主題

MySQL Schema設計及SQL開發規範實踐

直播時間

9月17日 20:00

預約方式

直接掃碼預約直播

那麼，接下來我們進入正題：MySQL 開發規範有哪些呢，我們兩位大咖講師結合自身在資料庫領域的多年實戰經驗，為大家總結了以下7點：

1、默認使用InnoDB引擎

事實上，InnoDB適用於幾乎99%的MySQL應用場景，從MySQL 8.0開始所有系統表都改成InnoDB表了，是時候全面轉向InnoDB引擎了。

2、字符集默認選擇utf8mb4

若為了節省磁碟空間，則建議選擇latin1。但仍強烈建議選擇utf8mb4字符集，因為它更通用，能兼容現有的其他字符集，尤其是在移動互聯時代的emoji表情符，可以有效避免「亂碼」問題。

注1：不同表JOIN時，若關聯欄位為字元串類型，且表（或關聯欄位）的字符集不同時，可能會造成類型隱式轉換，從而無法使用索引，變成全表掃描。

注2：無論什麼方式連接到MySQL，都記得總是先執行命令「SET NAMES UTF8MB4」，確保各層都使用UTF8MB4字符集。

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

InnoDB的data page size默認是16KB，當一條記錄物理長度超過約8KB（innodb data page size的一半，約8010位元組，非精確值）時，InnoDB會對其採用「overflow page」方式存儲，類似ORACLE中的「行遷移」。

因此，當必須使用大對象欄位（尤其是TEXT/BLOB類型）且讀寫頻繁的話，則最好把這些列拆分到子表中，不要和主表放在一起存儲。

【參考】：[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、枚舉類型可以使用TINYINT存儲，記住千萬別用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 檢索時，也可以快速返回結果。此外，NULL值過多也可能會造成索引樹傾斜，索引統計信息不準確。

6.5、不要直接 SELECT * 讀取全部欄位，尤其是表中存在 TEXT/BLOB 大對象欄位時代價更大，上面第2條已經提過。

7、關於索引的使用

除了常見的建議外，還有幾個要點：

7.1、一般而言，單表建議不超過5個索引，每個索引中由不超過5個欄位組成。

7.2、當發現mysqld進程長時間消耗CPU接近或超過100%時，99.99%是因為索引使用不當導致。

7.1、超過20個位元組長度的字元串欄位，最好創建前綴索引而非整列索引（例如：ALTER TABLE t1 ADD INDEX(user(20))），可以有效提高索引利用率。

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

7.3、哪怕是基於索引的條件過濾，如果優化器意識到總共需要掃描的數據量超過約30%時（大概是20% ~ 30%區間，非精確值），就可能直接改變執行計劃為全表掃描，不再使用索引。

7.4、多表JOIN時，如果JOIN之後有排序，排序欄位一定要屬於驅動表，才能利用驅動表上的索引完成排序。

8、其他

8.1、利用 pt-query-digest 定期分析slow query log並進行優化。

8.2、永遠不要在業務高峰期執行DDL、備份或需要長時間才能執行完的其他SQL命令。

8.3、強烈建議啟用 sql_safe_updates 選項以避免誤操作導致全表被更新或刪除。

8.4、重要業務上線前，一定要仔細確人重要的SQL都被優化過了。

8.5、一定要對用戶輸入值進行類型強制轉換，避免潛在的SQL注入風險。

最後，記得關注由葉金榮、吳炳錫、祝百萬、張松坡、邵宗文五位隱世高人強強聯手，由知數堂和騰訊雲學院聯合推出的《MySQL 資料庫架構及優化》課程！五位高人本門武功絕學特別面向以下武林人士開放：

技術總監

技術主管

架構師

開發工程師

DBA工程師

運維工程師

「神功」共分三大式、十六大招，若能耐心研習，必得五位高人真傳，獨步武林的夢想指日可待。

「神功」第一大式：MySQL應用開發規範

MySQL有趣的歷史，溫故知新

MySQL應用開發規範，避雷專用

開發者必備的備份及恢復技能，誤操作時的「無效救心丸」

帶你見識常見MySQL架構，尤其是如何快速進行讀寫分離架構設計

雲資料庫與傳統資料庫優劣勢對比

不同規格的雲資料庫配置性能測試及分析

雲資料庫日常管理要點

「神功」第二大式：深入理解MySQL優化

深入理解MySQL索引的方方面面，必學

理解MySQL事務，快人一步更充分理解MySQL獨特的事務機制

InnoDB的行鎖和其他資料庫區別在哪？如何減少死鎖的概率，看這裡就對

欲成「神功」，InnoDB引擎必然是繞不過的，學完定會有恍然大悟的感覺

「神功」得有「神技」相伴，SQL開發的優化及技巧就是「神技」

怎麼才能隨心所欲的「御劍」，就看智能DBA平台

「神功」第三大式：MySQL應用架構設計

如何從低段位升級到高段位，看MySQL升級注意事項

數據遷移與同步，升級過程中可別走火入魔了

作為高手面臨的挑戰之一，如何設計多IDC的DB架構

高手挑戰之二，如何設計高並發的DB架構

集采百家之長，看看遊戲、電商、金融這三大行業的經典案例

好了，五位世外高人的《MySQL 資料庫架構及優化》獨門心法吹水先打住。

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！