MySQL通用最佳化技巧

標籤：

內容提綱

1. MySQL的特點；

2. 硬體、系統最佳化；

3. MySQL 配置最佳化；

4. SCHEMA設計最佳化；

5. SQL 最佳化；

6. 其他最佳化。

MySQL 的特點

首先，需要明確的是。想要做好MySQL最佳化，需要先瞭解MySQL都有哪些特點：

簡言之，MySQL一般用於互連網業務的資料持久化儲存，並且用於保證資料的一致性、可靠性，而不是用於：

• 複雜查詢；

• 複雜運算；

• 大二進位儲存。

等奇葩用途。

CPU的利用特點

看看MySQL不同版本對CPU多核的支援、利用情況：

建議：

• 採用最新MySQL版本，以提升其CPU利用率；

• 每個SQL足夠簡單，不要太過複雜；

• 每個串連足夠快速完成，不要“戀戰”。

記憶體利用特點

記憶體利用、管理方面有什麼特點呢？

建議：

• 關閉query cache；

• 採用InnoDB；

• 採用Percona\MariaDB分支版本；

• 簡單KV資料用NOSQL儲存，不使用MySQL。

磁碟的利用特點

最後看下磁碟I/O方面的特點：

建議：

• 使用多盤提升整體I/O效能；

• 多使用高速I/O裝置；

• 盡量加大記憶體，緩解I/O負載。

MySQL 最佳化

瞭解完MySQL各方面的特點後，我們可以開始進行最佳化工作了。

在開始之前，我們需要先明確幾點：

1. 為何而最佳化？領導指派\使用者投訴\監控預警\沒事找事？當前跑得好好的話，就沒必要折騰神馬最佳化沒事找抽，即便想練手，也要悠著點，防止誤操作；

2. 最佳化的目標是什麼，說白了，就是要解決什麼瓶頸，切忌在過程中偏離初心；

3. 計算投入產出比，比如為了讓效能提升1%而投入1人月，基本上是非常不划算了，還不如去幹點別的；

4. 最佳化前做好現場資訊採集，最佳化後再次採集做對比，確認最佳化成果（用來邀功啊，讓老闆看到你的成績，年底加加薪什麼的，最起碼也能鍛煉總結歸納文檔能力吧）。

通常，我們進行MySQL最佳化工作的套路是這樣的：

確認需求，先明確當前的運行狀態，是否真的需要進行最佳化，別沒事找事；

常見瓶頸：

• 絕大多數瓶頸在於I/O子系統；

• 若CPU很高，90%以上是因為索引不當；

• 發生swap時，可能因為記憶體配置太小或過大；

• iowait太高時，想辦法從索引角度入手最佳化，以及提高I/O裝置效能，增加記憶體，減少排序，減少SELECT一次性讀取資料量。

常用最佳化策略

1. 瞬間並發很高，採用thread pool；

2. 頻繁order by\group by，索引入手；

3. 適當調整記憶體，不要太大或太小。一般ibp設定為50% ~ 70%為宜；

4. iowait高，加記憶體，提高iops，減少資料讀寫。

制定方案時，重點解決發生頻率高的問題（量變更容易引起質變）；回顧反饋，整理文檔，回顧總結，從零散資料中總結出規律，預防風險再次出現。

一般採用下面幾個瓶頸分析工具：

絕大多數情況下，有經驗的工程師靠sysstat工具集中的就足夠了，很多問題一看現象大概就能知道瓶頸何在。

在MySQL層面，有哪些確認瓶頸的手段呢？

硬體、系統最佳化

我們繼續MySQL最佳化之旅。先來看看從硬體以及OS層面，都有哪些可以最佳化的。首先主要是BIOS中關於CPU和記憶體的參數調整，其次是RAID方面的最佳化。

再來看看幾個參考配置圖：

1、CPU選擇最大效能模式，避免節能模式導致效能不足。

2、關閉NUMA，降低swap機率。

3、採用RAID-10，並且選擇FORCE WB。

在OS層面，可以有幾個最佳化手段：

• 調整IO Scheduler

• 使用XFS

• 調整其他核心選項備

備忘：

1. vm.swappiness，降低發生swap的幾率；

2. vm.dirty_background_ratio & vm.dirty_ratio，避免瞬間大量I/O請求導致系統卡死。

從這個壓測結果可以看到noop/deadline有明顯優勢。

這個io scheduler還可以線上修改的哦，還等神馬？

echo deadline > /sys/block/sdc/queue/scheduler

在用PCIe SSD裝置做測試時，XFS的IOPS能跑到ext4的4倍，表現非常好。

還有什麼理由不用XFS呢？

xfs掛載參數：

/dev/sdc1 /data xfs defaults,noatime,nodiratime,nobarrier 0 0

格式化參數不用特別指定，預設的即可。

MySQL配置最佳化

前面講到，給MySQL分配的記憶體不要太大或太小，那麼多少合適呢。

首先，要搞清楚MySQL的記憶體都由哪些部分組成：

1. global buffers和oracle的SGA一個意思，就是全域一次分配，多個線程間共用。

2. thread buffers和oracle的PGA一個意思，每個線程單獨分配，線程間不能相互共用，因此不要分配過大，避免記憶體不夠使用，發生OOM。

原則： 對這些選項調整時，不要照貓畫虎隨便調整，要先做到心裡有數，瞭解其具體作用才動手。

看看innodb_flush_log_at_trx_commit分別為0、1、2的效能對比如：

如果再啟用binlog後的對比：

最後，再加上sync_binlog選項不同設定的對比：

備忘： 這3個測試結果圖均來自Percona。

結論&建議：

1. 想要保證資料安全，就設定 trx_commit =1 & sync_binlog = 1

2. 在slave上或非關鍵情境，可以都改成0

SCHEMA設計最佳化

接下來看看MySQL的模式（SCHEMA）設計最佳化要點：

要點：

1. 預設地，使用InnoDB引擎，別上MyISAM給自己找事；

2. InnoDB必須要有自增（或類似自增）屬性的主鍵；

3. 不使用或少使用TEXT/BLOB列；

4. NOT NULL主要是為了最佳化索引效率；

5. 若無特殊需求，均可使用latin1字元集，否則用utf8\utf8mb4等大字元集保證通用性。

其他要點：

SQL最佳化

SQL最佳化層面有幾個要點：

以及 COUNT(*)、大分頁 的最佳化要點：

接下來，我們來看看EXPLAIN的結果中，有哪些關鍵資訊要注意的。首先看下EXPLAIN結果的type列，都可以給我們什麼資訊：

再看看Extra列有哪些狀態要引起重視：

MySQL的慢日誌可用下面的工具來進行解析和管理：

pt-query-digest + Box Anemometer的案例，可以對slow log進行便捷管理。

關於JOIN最佳化有下面的幾個關鍵點：

接下來看看哪些情況下，無法有效使用索引的：

再看看幾個殺手級SQL的案例及其最佳化建議：

在平時，我們登入MySQL伺服器後，如果覺得有問題，可以重點關注下面的一些線程狀態：

其他最佳化

關於DBA的利器，常用percona-toolkit工具簡介：

附：關於MariaDB及Percona分支版本的簡介

Q&A

Q1: 多執行個體，進程會不會搶佔？每個案例都是單獨起的。

A：除了OS層面的資源會相互影響外，其他的不會。比如某個執行個體消耗特別多cpu資源的話，那麼其他執行個體也會跟著受影響，這是必然的，除非用虛擬化等方式做隔離。

Q2: SSD建議單盤還是Raid？

A：如果不擔心丟資料，單盤唄。如果怕丟的話，那顯然不能單盤了。隨機io很高的話，Raid5就不合適了。不過除非採用SSD，用Raid5也不怕了。事實上，Raid卡反而會影響（降低）SSD效能的發揮，但為了資料可靠性，沒辦法，還好影響不算特別大。

Q3: 能介紹一下哪些業務情境適合哪種RAID嗎？

A：1、高隨機IO，用Raid10；2、需要大容量，用Raid5。基本就這兩種方案，事實上，因為SSD的IOPS效能已經很不錯了，很多企業會選擇直接用3塊盤構建Raid5。毋庸置疑，上了PCIE SSD，可以避免很多問題，或者DBA可以少幹很多活，至少可以緩解。

Q4: nnodb_buffer_pool_instances應該如何設定？

A：ibp的instance一般不超過8為宜，超過8的話，可能有反作用，不過多個instance的前提是，平均到每個instance的ibp不能小於2G，否則也沒啥意義。

Q5: No text,or in compressed是指如果使用text的話，建議壓縮嗎？在壓縮資料方面，葉老師有什麼經驗嗎？

A：對的，建議不要在InnoDB中儲存大量文本。需要的話，事先壓縮好再存進去。不需要檢索的文本，可以統統壓縮後存進去，不是用InnoDB的壓縮格式哦，是事先外部壓縮後儲存，常值內容在儲存進去前先壓縮好，不是用InnoDB的compressed這種row format，那會被坑慘的，效能損失9層，只有一半壓縮比，還不如用TokuDB算了。

Q6: MariaDB和MySQL的優缺點，以及大神怎麼看Maria有否取代MySQL的趨勢？

A：想要取代還早呢，沒那麼容易，而且也沒必要取代，作為補充就ok。除非哪天MySQL官方版本閉源了，或者支援很差。

Q7: 新的業務系統，是建議繼續用MySQL5.5或以上，還是用mariaDB？

A：建議優先Percona 5.6，其次是MySQL 5.6，最末才是MariaDB。

Q8: 你們的Database Backup是用Percona的工具進行嗎？每周一全備，每天一增量？用這些工具備份，會不會出現恢複不了的情況？這個有沒有辦法驗證備份是否“正常” ？

A：工具則以xtrabackup為主，mysqldump為輔，數量不是巨大的話，每天一全備，大多有slave做熱備，所以就沒定期增備了。Mydumper也有些不太爽的，也比較小眾就是，備份檔案一定要做恢複性測試，千萬別只備份不恢複測試，關鍵時刻會死人的。

Q9:恢複性測試怎麼做 有流程方案指導一下嗎？

A：簡單的：資料恢複，簡單查詢驗證數量，關鍵資料什麼的；複雜的：搭測試環境唄。

Q10: 有沒有什麼效率較高的驗證備份有效性的工具或者方法？還是只好把庫恢複出來核對？

A：mysqldump或mydumper備份的檔案，可以用grep簡單快速驗證；xtrabackup的話，只能看檔案大小，或者做全量恢複了。

MySQL通用最佳化技巧