日志君導(dǎo)讀：

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

本文轉(zhuǎn)自UCloud云計(jì)算公眾號(hào)，點(diǎn)擊閱讀原文 查看網(wǎng)頁(yè)版文章。

嘉賓介紹

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

內(nèi)容提綱

1. MySQL的特點(diǎn)；

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

3. MySQL 配置優(yōu)化；

4. SCHEMA設(shè)計(jì)優(yōu)化；

5. SQL 優(yōu)化；

6. 其他優(yōu)化。

MySQL 的特點(diǎn)

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

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

• 復(fù)雜查詢(xún)；

• 復(fù)雜運(yùn)算；

• 大二進(jìn)制存儲(chǔ)。

等奇葩用途。

CPU的利用特點(diǎn)

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

建議：

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

• 每個(gè)SQL足夠簡(jiǎn)單，不要太過(guò)復(fù)雜；

• 每個(gè)連接足夠快速完成，不要“戀戰(zhàn)”。

內(nèi)存利用特點(diǎn)

內(nèi)存利用、管理方面有什么特點(diǎn)呢？

建議：

• 關(guān)閉query cache；

• 采用InnoDB；

• 采用Percona\MariaDB分支版本；

• 簡(jiǎn)單KV數(shù)據(jù)用NOSQL存儲(chǔ)，不使用MySQL。

磁盤(pán)的利用特點(diǎn)

最后看下磁盤(pán)I/O方面的特點(diǎn)：

建議：

• 使用多盤(pán)提升整體I/O性能；

• 多使用高速I(mǎi)/O設(shè)備；

• 盡量加大內(nèi)存，緩解I/O負(fù)載。

MySQL 優(yōu)化

了解完MySQL各方面的特點(diǎn)后，我們可以開(kāi)始進(jìn)行優(yōu)化工作了。

在開(kāi)始之前，我們需要先明確幾點(diǎn)：

1. 為何而優(yōu)化？領(lǐng)導(dǎo)指派\用戶(hù)投訴\監(jiān)控預(yù)警\沒(méi)事找事？當(dāng)前跑得好好的話(huà)，就沒(méi)必要折騰神馬優(yōu)化沒(méi)事找抽，即便想練手，也要悠著點(diǎn)，防止誤操作；

2. 優(yōu)化的目標(biāo)是什么，說(shuō)白了，就是要解決什么瓶頸，切忌在過(guò)程中偏離初心；

3. 計(jì)算投入產(chǎn)出比，比如為了讓性能提升1%而投入1人月，基本上是非常不劃算了，還不如去干點(diǎn)別的；

4. 優(yōu)化前做好現(xiàn)場(chǎng)信息采集，優(yōu)化后再次采集做對(duì)比，確認(rèn)優(yōu)化成果（用來(lái)邀功啊，讓老板看到你的成績(jī)，年底加加薪什么的，最起碼也能鍛煉總結(jié)歸納文檔能力吧）。

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

確認(rèn)需求，先明確當(dāng)前的運(yùn)行狀態(tài)，是否真的需要進(jìn)行優(yōu)化，別沒(méi)事找事；

常見(jiàn)瓶頸：

• 絕大多數(shù)瓶頸在于I/O子系統(tǒng)；

• 若CPU很高，90%以上是因?yàn)樗饕划?dāng)；

• 發(fā)生swap時(shí)，可能因?yàn)閮?nèi)存分配太小或過(guò)大；

• iowait太高時(shí)，想辦法從索引角度入手優(yōu)化，以及提高I/O設(shè)備性能，增加內(nèi)存，減少排序，減少SELECT一次性讀取數(shù)據(jù)量。

常用優(yōu)化策略

1. 瞬間并發(fā)很高，采用thread pool；

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

3. 適當(dāng)調(diào)整內(nèi)存，不要太大或太小。一般ibp設(shè)置為50% ~ 70%為宜；

4. iowait高，加內(nèi)存，提高iops，減少數(shù)據(jù)讀寫(xiě)。

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

一般采用下面幾個(gè)瓶頸分析工具：

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

在MySQL層面，有哪些確認(rèn)瓶頸的手段呢？

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

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

再來(lái)看看幾個(gè)參考配置圖：

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

2、關(guān)閉NUMA，降低swap概率。

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

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

• 調(diào)整IO Scheduler

• 使用XFS

• 調(diào)整其他內(nèi)核選項(xiàng)備

備注：

1. vm.swappiness，降低發(fā)生swap的幾率；

2. vm.dirty_background_ratio & vm.dirty_ratio，避免瞬間大量I/O請(qǐng)求導(dǎo)致系統(tǒng)卡死。

從這個(gè)壓測(cè)結(jié)果可以看到noop/deadline有明顯優(yōu)勢(shì)。

這個(gè)io scheduler還可以在線(xiàn)修改的哦，還等神馬？

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

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

還有什么理由不用XFS呢？

xfs掛載參數(shù)：

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

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

MySQL配置優(yōu)化

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

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

1. global buffers和oracle的SGA一個(gè)意思，就是全局一次分配，多個(gè)線(xiàn)程間共享。

2. thread buffers和oracle的PGA一個(gè)意思，每個(gè)線(xiàn)程單獨(dú)分配，線(xiàn)程間不能相互共享，因此不要分配過(guò)大，避免內(nèi)存不夠使用，發(fā)生OOM。

原則： 對(duì)這些選項(xiàng)調(diào)整時(shí)，不要照貓畫(huà)虎隨便調(diào)整，要先做到心里有數(shù)，了解其具體作用才動(dòng)手。

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

如果再啟用binlog后的對(duì)比：

最后，再加上sync_binlog選項(xiàng)不同設(shè)置的對(duì)比：

備注： 這3個(gè)測(cè)試結(jié)果圖均來(lái)自Percona。

結(jié)論&建議：

1. 想要保證數(shù)據(jù)安全，就設(shè)置 trx_commit =1 & sync_binlog = 1

2. 在slave上或非關(guān)鍵場(chǎng)景，可以都改成0

SCHEMA設(shè)計(jì)優(yōu)化

接下來(lái)看看MySQL的模式（SCHEMA）設(shè)計(jì)優(yōu)化要點(diǎn)：

要點(diǎn)：

1. 默認(rèn)地，使用InnoDB引擎，別上MyISAM給自己找事；

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

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

4. NOT NULL主要是為了優(yōu)化索引效率；

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

其他要點(diǎn)：

SQL優(yōu)化

SQL優(yōu)化層面有幾個(gè)要點(diǎn)：

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

接下來(lái)，我們來(lái)看看EXPLAIN的結(jié)果中，有哪些關(guān)鍵信息要注意的。首先看下EXPLAIN結(jié)果的type列，都可以給我們什么信息：

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

MySQL的慢日志可用下面的工具來(lái)進(jìn)行解析和管理：

pt-query-digest + Box Anemometer的案例，可以對(duì)slow log進(jìn)行便捷管理。

關(guān)于JOIN優(yōu)化有下面的幾個(gè)關(guān)鍵點(diǎn)：

接下來(lái)看看哪些情況下，無(wú)法有效使用索引的：

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

在平時(shí)，我們登入MySQL服務(wù)器后，如果覺(jué)得有問(wèn)題，可以重點(diǎn)關(guān)注下面的一些線(xiàn)程狀態(tài)：

其他優(yōu)化

關(guān)于DBA的利器，常用percona-toolkit工具簡(jiǎn)介：

附：關(guān)于MariaDB及Percona分支版本的簡(jiǎn)介

Q&A

Q1: 多實(shí)例，進(jìn)程會(huì)不會(huì)搶占？每個(gè)事例都是單獨(dú)起的。

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

Q2: SSD建議單盤(pán)還是Raid？

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

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

A：1、高隨機(jī)IO，用Raid10；2、需要大容量，用Raid5?；揪瓦@兩種方案，事實(shí)上，因?yàn)镾SD的IOPS性能已經(jīng)很不錯(cuò)了，很多企業(yè)會(huì)選擇直接用3塊盤(pán)構(gòu)建Raid5。毋庸置疑，上了PCIE SSD，可以避免很多問(wèn)題，或者DBA可以少干很多活，至少可以緩解。

Q4: nnodb_buffer_pool_instances應(yīng)該如何設(shè)置？

A：ibp的instance一般不超過(guò)8為宜，超過(guò)8的話(huà)，可能有反作用，不過(guò)多個(gè)instance的前提是，平均到每個(gè)instance的ibp不能小于2G，否則也沒(méi)啥意義。

Q5: No text,or in compressed是指如果使用text的話(huà)，建議壓縮嗎？在壓縮數(shù)據(jù)方面，葉老師有什么經(jīng)驗(yàn)嗎？

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

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

A：想要取代還早呢，沒(méi)那么容易，而且也沒(méi)必要取代，作為補(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ù)庫(kù)備份是用Percona的工具進(jìn)行嗎？每周一全備，每天一增量？用這些工具備份，會(huì)不會(huì)出現(xiàn)恢復(fù)不了的情況？這個(gè)有沒(méi)有辦法驗(yàn)證備份是否“正?！?？

A：工具則以xtrabackup為主，mysqldump為輔，數(shù)量不是巨大的話(huà)，每天一全備，大多有slave做熱備，所以就沒(méi)定期增備了。Mydumper也有些不太爽的，也比較小眾就是，備份文件一定要做恢復(fù)性測(cè)試，千萬(wàn)別只備份不恢復(fù)測(cè)試，關(guān)鍵時(shí)刻會(huì)死人的。

Q9:恢復(fù)性測(cè)試怎么做 有流程方案指導(dǎo)一下嗎？

A：簡(jiǎn)單的：數(shù)據(jù)恢復(fù)，簡(jiǎn)單查詢(xún)驗(yàn)證數(shù)量，關(guān)鍵數(shù)據(jù)什么的；復(fù)雜的：搭測(cè)試環(huán)境唄。

Q10: 有沒(méi)有什么效率較高的驗(yàn)證備份有效性的工具或者方法？還是只好把庫(kù)恢復(fù)出來(lái)核對(duì)？

A：mysqldump或mydumper備份的文件，可以用grep簡(jiǎn)單快速驗(yàn)證；xtrabackup的話(huà)，只能看文件大小，或者做全量恢復(fù)了。