日志君導(dǎo)讀：
本文根據(jù) MySQL中文網(wǎng)創(chuàng)始人葉金榮在DevOps華南運(yùn)維圈@UCloud微信群的「運(yùn)維在線」欄目的嘉賓分享整理而成。分享主題為“MySQL通用優(yōu)化”，涵蓋硬件、系統(tǒng)優(yōu)化，MySQL優(yōu)化、配置優(yōu)化、SCHEMA優(yōu)化等內(nèi)容，值得學(xué)習(xí)。
本文轉(zhuǎn)自UCloud云計算公眾號，點(diǎn)擊閱讀原文 查看網(wǎng)頁版文章。
葉金榮
Oracle MySQL ACE，國內(nèi)最早的MySQL推廣者。2006年創(chuàng)辦國內(nèi)首個MySQL專業(yè)技術(shù)網(wǎng)站 MySQL 中文網(wǎng)。資深MySQL專家，10余年MySQL經(jīng)驗(yàn)，擅長MysQL性能優(yōu)化、架構(gòu)設(shè)計、故障排查。
MySQL的特點(diǎn)；
硬件、系統(tǒng)優(yōu)化；
MySQL 配置優(yōu)化；
SCHEMA設(shè)計優(yōu)化；
SQL 優(yōu)化；
其他優(yōu)化。
首先，需要明確的是。想要做好MySQL優(yōu)化，需要先了解MySQL都有哪些特點(diǎn)：
簡言之，MySQL一般用于互聯(lián)網(wǎng)業(yè)務(wù)的數(shù)據(jù)持久化存儲，并且用于保證數(shù)據(jù)的一致性、可靠性，而不是用于：
復(fù)雜查詢；
復(fù)雜運(yùn)算；
大二進(jìn)制存儲。
等奇葩用途。
看看MySQL不同版本對CPU多核的支持、利用情況：
建議：
采用最新MySQL版本，以提升其CPU利用率；
每個SQL足夠簡單，不要太過復(fù)雜；
每個連接足夠快速完成，不要“戀戰(zhàn)”。
內(nèi)存利用、管理方面有什么特點(diǎn)呢？
建議：
關(guān)閉query cache；
采用InnoDB；
采用Percona\MariaDB分支版本；
簡單KV數(shù)據(jù)用NOSQL存儲，不使用MySQL。
最后看下磁盤I/O方面的特點(diǎn)：
建議：
使用多盤提升整體I/O性能；
多使用高速I/O設(shè)備；
盡量加大內(nèi)存，緩解I/O負(fù)載。
了解完MySQL各方面的特點(diǎn)后，我們可以開始進(jìn)行優(yōu)化工作了。
在開始之前，我們需要先明確幾點(diǎn)：
為何而優(yōu)化？領(lǐng)導(dǎo)指派\用戶投訴\監(jiān)控預(yù)警\沒事找事？當(dāng)前跑得好好的話，就沒必要折騰神馬優(yōu)化沒事找抽，即便想練手，也要悠著點(diǎn)，防止誤操作；
優(yōu)化的目標(biāo)是什么，說白了，就是要解決什么瓶頸，切忌在過程中偏離初心；
計算投入產(chǎn)出比，比如為了讓性能提升1%而投入1人月，基本上是非常不劃算了，還不如去干點(diǎn)別的；
優(yōu)化前做好現(xiàn)場信息采集，優(yōu)化后再次采集做對比，確認(rèn)優(yōu)化成果（用來邀功啊，讓老板看到你的成績，年底加加薪什么的，最起碼也能鍛煉總結(jié)歸納文檔能力吧）。
通常，我們進(jìn)行MySQL優(yōu)化工作的套路是這樣的：
確認(rèn)需求，先明確當(dāng)前的運(yùn)行狀態(tài)，是否真的需要進(jìn)行優(yōu)化，別沒事找事；
常見瓶頸：
絕大多數(shù)瓶頸在于I/O子系統(tǒng)；
若CPU很高，90%以上是因?yàn)樗饕划?dāng)；
發(fā)生swap時，可能因?yàn)閮?nèi)存分配太小或過大；
iowait太高時，想辦法從索引角度入手優(yōu)化，以及提高I/O設(shè)備性能，增加內(nèi)存，減少排序，減少SELECT一次性讀取數(shù)據(jù)量。
常用優(yōu)化策略
瞬間并發(fā)很高，采用thread pool；
頻繁order by\group by，索引入手；
適當(dāng)調(diào)整內(nèi)存，不要太大或太小。一般ibp設(shè)置為50% ~ 70%為宜；
iowait高，加內(nèi)存，提高iops，減少數(shù)據(jù)讀寫。
制定方案時，重點(diǎn)解決發(fā)生頻率高的問題（量變更容易引起質(zhì)變）；回顧反饋，整理文檔，回顧總結(jié)，從零散資料中總結(jié)出規(guī)律，預(yù)防風(fēng)險再次出現(xiàn)。
一般采用下面幾個瓶頸分析工具：
絕大多數(shù)情況下，有經(jīng)驗(yàn)的工程師靠sysstat工具集中的就足夠了，很多問題一看現(xiàn)象大概就能知道瓶頸何在。
在MySQL層面，有哪些確認(rèn)瓶頸的手段呢？
我們繼續(xù)MySQL優(yōu)化之旅。先來看看從硬件以及OS層面，都有哪些可以優(yōu)化的。首先主要是BIOS中關(guān)于CPU和內(nèi)存的參數(shù)調(diào)整，其次是RAID方面的優(yōu)化。
再來看看幾個參考配置圖：
1、CPU選擇最大性能模式，避免節(jié)能模式導(dǎo)致性能不足。
2、關(guān)閉NUMA，降低swap概率。
3、采用RAID-10，并且選擇FORCE WB。
在OS層面，可以有幾個優(yōu)化手段：
調(diào)整IO Scheduler
使用XFS
調(diào)整其他內(nèi)核選項(xiàng)備
備注：
vm.swappiness，降低發(fā)生swap的幾率；
vm.dirty_background_ratio & vm.dirty_ratio，避免瞬間大量I/O請求導(dǎo)致系統(tǒng)卡死。
從這個壓測結(jié)果可以看到noop/deadline有明顯優(yōu)勢。
這個io scheduler還可以在線修改的哦，還等神馬？
echo deadline > /sys/block/sdc/queue/scheduler
在用PCIe SSD設(shè)備做測試時，XFS的IOPS能跑到ext4的4倍，表現(xiàn)非常好。
還有什么理由不用XFS呢？
xfs掛載參數(shù)：
/dev/sdc1 /data xfs defaults,noatime,nodiratime,nobarrier 0 0
格式化參數(shù)不用特別指定，默認(rèn)的即可。
前面講到，給MySQL分配的內(nèi)存不要太大或太小，那么多少合適呢。
首先，要搞清楚MySQL的內(nèi)存都由哪些部分組成：
global buffers和oracle的SGA一個意思，就是全局一次分配，多個線程間共享。
thread buffers和oracle的PGA一個意思，每個線程單獨(dú)分配，線程間不能相互共享，因此不要分配過大，避免內(nèi)存不夠使用，發(fā)生OOM。
原則： 對這些選項(xiàng)調(diào)整時，不要照貓畫虎隨便調(diào)整，要先做到心里有數(shù)，了解其具體作用才動手。
看看innodb_flush_log_at_trx_commit分別為0、1、2的性能對比如：
如果再啟用binlog后的對比：
最后，再加上sync_binlog選項(xiàng)不同設(shè)置的對比：
備注： 這3個測試結(jié)果圖均來自Percona。
結(jié)論&建議：
想要保證數(shù)據(jù)安全，就設(shè)置 trx_commit =1 & sync_binlog = 1
在slave上或非關(guān)鍵場景，可以都改成0
接下來看看MySQL的模式（SCHEMA）設(shè)計優(yōu)化要點(diǎn)：
要點(diǎn)：
默認(rèn)地，使用InnoDB引擎，別上MyISAM給自己找事；
InnoDB必須要有自增（或類似自增）屬性的主鍵；
不使用或少使用TEXT/BLOB列；
NOT NULL主要是為了優(yōu)化索引效率；
若無特殊需求，均可使用latin1字符集，否則用utf8\utf8mb4等大字符集保證通用性。
其他要點(diǎn)：
SQL優(yōu)化層面有幾個要點(diǎn)：
以及 COUNT(*)、大分頁 的優(yōu)化要點(diǎn)：
接下來，我們來看看EXPLAIN的結(jié)果中，有哪些關(guān)鍵信息要注意的。首先看下EXPLAIN結(jié)果的type列，都可以給我們什么信息：
再看看Extra列有哪些狀態(tài)要引起重視：
MySQL的慢日志可用下面的工具來進(jìn)行解析和管理：
pt-query-digest + Box Anemometer的案例，可以對slow log進(jìn)行便捷管理。
關(guān)于JOIN優(yōu)化有下面的幾個關(guān)鍵點(diǎn)：
接下來看看哪些情況下，無法有效使用索引的：
再看看幾個殺手級SQL的案例及其優(yōu)化建議：
在平時，我們登入MySQL服務(wù)器后，如果覺得有問題，可以重點(diǎn)關(guān)注下面的一些線程狀態(tài)：
關(guān)于DBA的利器，常用percona-toolkit工具簡介：
附：關(guān)于MariaDB及Percona分支版本的簡介
Q1: 多實(shí)例，進(jìn)程會不會搶占？每個事例都是單獨(dú)起的。
A：除了OS層面的資源會相互影響外，其他的不會。比如某個實(shí)例消耗特別多cpu資源的話，那么其他實(shí)例也會跟著受影響，這是必然的，除非用虛擬化等方式做隔離。
Q2: SSD建議單盤還是Raid？
A：如果不擔(dān)心丟數(shù)據(jù)，單盤唄。如果怕丟的話，那顯然不能單盤了。隨機(jī)io很高的話，Raid5就不合適了。不過除非采用SSD，用Raid5也不怕了。事實(shí)上，Raid卡反而會影響（降低）SSD性能的發(fā)揮，但為了數(shù)據(jù)可靠性，沒辦法，還好影響不算特別大。
Q3: 能介紹一下哪些業(yè)務(wù)場景適合哪種RAID嗎？
A：1、高隨機(jī)IO，用Raid10；2、需要大容量，用Raid5。基本就這兩種方案，事實(shí)上，因?yàn)镾SD的IOPS性能已經(jīng)很不錯了，很多企業(yè)會選擇直接用3塊盤構(gòu)建Raid5。毋庸置疑，上了PCIE SSD，可以避免很多問題，或者DBA可以少干很多活，至少可以緩解。
Q4: nnodb_buffer_pool_instances應(yīng)該如何設(shè)置？
A：ibp的instance一般不超過8為宜，超過8的話，可能有反作用，不過多個instance的前提是，平均到每個instance的ibp不能小于2G，否則也沒啥意義。
Q5: No text,or in compressed是指如果使用text的話，建議壓縮嗎？在壓縮數(shù)據(jù)方面，葉老師有什么經(jīng)驗(yàn)嗎？
A：對的，建議不要在InnoDB中存儲大量文本。需要的話，事先壓縮好再存進(jìn)去。不需要檢索的文本，可以統(tǒng)統(tǒng)壓縮后存進(jìn)去，不是用InnoDB的壓縮格式哦，是事先外部壓縮后存儲，文本內(nèi)容在存儲進(jìn)去前先壓縮好，不是用InnoDB的compressed這種row format，那會被坑慘的，性能損失9層，只有一半壓縮比，還不如用TokuDB算了。
Q6: MariaDB和MySQL的優(yōu)缺點(diǎn)，以及大神怎么看Maria有否取代MySQL的趨勢？
A：想要取代還早呢，沒那么容易，而且也沒必要取代，作為補(bǔ)充就ok。除非哪天MySQL官方版本閉源了，或者支持很差。
Q7: 新的業(yè)務(wù)系統(tǒng)，是建議繼續(xù)用MySQL5.5或以上，還是用mariaDB？
A：建議優(yōu)先Percona 5.6，其次是MySQL 5.6，最末才是MariaDB。
Q8: 你們的數(shù)據(jù)庫備份是用Percona的工具進(jìn)行嗎？每周一全備，每天一增量？用這些工具備份，會不會出現(xiàn)恢復(fù)不了的情況？這個有沒有辦法驗(yàn)證備份是否“正?！?？
A：工具則以xtrabackup為主，mysqldump為輔，數(shù)量不是巨大的話，每天一全備，大多有slave做熱備，所以就沒定期增備了。Mydumper也有些不太爽的，也比較小眾就是，備份文件一定要做恢復(fù)性測試，千萬別只備份不恢復(fù)測試，關(guān)鍵時刻會死人的。
Q9:恢復(fù)性測試怎么做 有流程方案指導(dǎo)一下嗎？
A：簡單的：數(shù)據(jù)恢復(fù)，簡單查詢驗(yàn)證數(shù)量，關(guān)鍵數(shù)據(jù)什么的；復(fù)雜的：搭測試環(huán)境唄。
Q10: 有沒有什么效率較高的驗(yàn)證備份有效性的工具或者方法？還是只好把庫恢復(fù)出來核對？
A：mysqldump或mydumper備份的文件，可以用grep簡單快速驗(yàn)證；xtrabackup的話，只能看文件大小，或者做全量恢復(fù)了。
