你要的都有｜MySQL通用優(yōu)化手冊

芥納須彌 2015-09-18

展開全文

日志君導讀：

本文根據(jù) MySQL中文網(wǎng)創(chuàng)始人葉金榮在DevOps華南運維圈@UCloud微信群的「運維在線」欄目的嘉賓分享整理而成。分享主題為“MySQL通用優(yōu)化”，涵蓋硬件、系統(tǒng)優(yōu)化，MySQL優(yōu)化、配置優(yōu)化、SCHEMA優(yōu)化等內容，值得學習。

本文轉自UCloud云計算公眾號，點擊閱讀原文查看網(wǎng)頁版文章。

嘉賓介紹

葉金榮
Oracle MySQL ACE，國內最早的MySQL推廣者。2006年創(chuàng)辦國內首個MySQL專業(yè)技術網(wǎng)站 MySQL 中文網(wǎng)。資深MySQL專家，10余年MySQL經驗，擅長MysQL性能優(yōu)化、架構設計、故障排查。

內容提綱

MySQL的特點；
硬件、系統(tǒng)優(yōu)化；
MySQL 配置優(yōu)化；
SCHEMA設計優(yōu)化；
SQL 優(yōu)化；
其他優(yōu)化。

MySQL 的特點

首先，需要明確的是。想要做好MySQL優(yōu)化，需要先了解MySQL都有哪些特點：

簡言之，MySQL一般用于互聯(lián)網(wǎng)業(yè)務的數(shù)據(jù)持久化存儲，并且用于保證數(shù)據(jù)的一致性、可靠性，而不是用于：

復雜查詢；
復雜運算；
大二進制存儲。

等奇葩用途。

CPU的利用特點

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

建議：

采用最新MySQL版本，以提升其CPU利用率；
每個SQL足夠簡單，不要太過復雜；
每個連接足夠快速完成，不要“戀戰(zhàn)”。

內存利用特點

內存利用、管理方面有什么特點呢？

建議：

關閉query cache；
采用InnoDB；
采用Percona\MariaDB分支版本；
簡單KV數(shù)據(jù)用NOSQL存儲，不使用MySQL。

磁盤的利用特點

最后看下磁盤I/O方面的特點：

建議：

使用多盤提升整體I/O性能；
多使用高速I/O設備；
盡量加大內存，緩解I/O負載。

MySQL 優(yōu)化

了解完MySQL各方面的特點后，我們可以開始進行優(yōu)化工作了。

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

為何而優(yōu)化？領導指派\用戶投訴\監(jiān)控預警\沒事找事？當前跑得好好的話，就沒必要折騰神馬優(yōu)化沒事找抽，即便想練手，也要悠著點，防止誤操作；
優(yōu)化的目標是什么，說白了，就是要解決什么瓶頸，切忌在過程中偏離初心；
計算投入產出比，比如為了讓性能提升1%而投入1人月，基本上是非常不劃算了，還不如去干點別的；
優(yōu)化前做好現(xiàn)場信息采集，優(yōu)化后再次采集做對比，確認優(yōu)化成果（用來邀功啊，讓老板看到你的成績，年底加加薪什么的，最起碼也能鍛煉總結歸納文檔能力吧）。

通常，我們進行MySQL優(yōu)化工作的套路是這樣的：

確認需求，先明確當前的運行狀態(tài)，是否真的需要進行優(yōu)化，別沒事找事；

常見瓶頸：

絕大多數(shù)瓶頸在于I/O子系統(tǒng)；
若CPU很高，90%以上是因為索引不當；
發(fā)生swap時，可能因為內存分配太小或過大；
iowait太高時，想辦法從索引角度入手優(yōu)化，以及提高I/O設備性能，增加內存，減少排序，減少SELECT一次性讀取數(shù)據(jù)量。

常用優(yōu)化策略

瞬間并發(fā)很高，采用thread pool；
頻繁order by\group by，索引入手；
適當調整內存，不要太大或太小。一般ibp設置為50% ~ 70%為宜；
iowait高，加內存，提高iops，減少數(shù)據(jù)讀寫。

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

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

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

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

硬件、系統(tǒng)優(yōu)化

我們繼續(xù)MySQL優(yōu)化之旅。先來看看從硬件以及OS層面，都有哪些可以優(yōu)化的。首先主要是BIOS中關于CPU和內存的參數(shù)調整，其次是RAID方面的優(yōu)化。

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

1、CPU選擇最大性能模式，避免節(jié)能模式導致性能不足。

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

3、采用RAID-10，并且選擇FORCE WB。

在OS層面，可以有幾個優(yōu)化手段：

調整IO Scheduler
使用XFS
調整其他內核選項備

備注：

vm.swappiness，降低發(fā)生swap的幾率；
vm.dirty_background_ratio & vm.dirty_ratio，避免瞬間大量I/O請求導致系統(tǒng)卡死。

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

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

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

在用PCIe SSD設備做測試時，XFS的IOPS能跑到ext4的4倍，表現(xiàn)非常好。

還有什么理由不用XFS呢？

xfs掛載參數(shù)：

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

格式化參數(shù)不用特別指定，默認的即可。

MySQL配置優(yōu)化

前面講到，給MySQL分配的內存不要太大或太小，那么多少合適呢。

首先，要搞清楚MySQL的內存都由哪些部分組成：

global buffers和oracle的SGA一個意思，就是全局一次分配，多個線程間共享。
thread buffers和oracle的PGA一個意思，每個線程單獨分配，線程間不能相互共享，因此不要分配過大，避免內存不夠使用，發(fā)生OOM。

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

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

如果再啟用binlog后的對比：

最后，再加上sync_binlog選項不同設置的對比：

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

結論&建議：

想要保證數(shù)據(jù)安全，就設置 trx_commit =1 & sync_binlog = 1
在slave上或非關鍵場景，可以都改成0

SCHEMA設計優(yōu)化

接下來看看MySQL的模式（SCHEMA）設計優(yōu)化要點：

要點：

默認地，使用InnoDB引擎，別上MyISAM給自己找事；
InnoDB必須要有自增（或類似自增）屬性的主鍵；
不使用或少使用TEXT/BLOB列；
NOT NULL主要是為了優(yōu)化索引效率；
若無特殊需求，均可使用latin1字符集，否則用utf8\utf8mb4等大字符集保證通用性。

其他要點：

SQL優(yōu)化

SQL優(yōu)化層面有幾個要點：

以及 COUNT(*)、大分頁的優(yōu)化要點：

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

再看看Extra列有哪些狀態(tài)要引起重視：

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

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

關于JOIN優(yōu)化有下面的幾個關鍵點：

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

再看看幾個殺手級SQL的案例及其優(yōu)化建議：

在平時，我們登入MySQL服務器后，如果覺得有問題，可以重點關注下面的一些線程狀態(tài)：

其他優(yōu)化

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

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

Q&A

Q1: 多實例，進程會不會搶占？每個事例都是單獨起的。

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

Q2: SSD建議單盤還是Raid？

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

Q3: 能介紹一下哪些業(yè)務場景適合哪種RAID嗎？

A：1、高隨機IO，用Raid10；2、需要大容量，用Raid5?；揪瓦@兩種方案，事實上，因為SSD的IOPS性能已經很不錯了，很多企業(yè)會選擇直接用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的話，建議壓縮嗎？在壓縮數(shù)據(jù)方面，葉老師有什么經驗嗎？

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

Q6: MariaDB和MySQL的優(yōu)缺點，以及大神怎么看Maria有否取代MySQL的趨勢？