MySQL性能優(yōu)化教程一
編者注：這是一篇MySQL性能優(yōu)化的教程，來(lái)著某公司的DBA，原是為了培訓(xùn)公司員工用，現(xiàn)在轉(zhuǎn)載出來(lái)供大家一起學(xué)習(xí)提高。
背景及目標(biāo)
● 用于員工培訓(xùn)和分享。
● 針對(duì)用戶(hù)群為已經(jīng)使用過(guò)mysql環(huán)境，并有一定開(kāi)發(fā)經(jīng)驗(yàn)的工程師
● 針對(duì)高并發(fā)，海量數(shù)據(jù)的互聯(lián)網(wǎng)環(huán)境。
● 本文語(yǔ)言為口語(yǔ)，非學(xué)術(shù)標(biāo)準(zhǔn)用語(yǔ)。
● 以實(shí)戰(zhàn)和解決具體問(wèn)題為主要目標(biāo)，非應(yīng)試，非常規(guī)教育。友情提醒，在校生學(xué)習(xí)本教程可能對(duì)成績(jī)提高有害無(wú)益。
● 非技術(shù)挑戰(zhàn)，非高端架構(gòu)師培訓(xùn)，請(qǐng)高手自動(dòng)忽略。
Mysql 執(zhí)行優(yōu)化
認(rèn)識(shí)數(shù)據(jù)索引
1.為什么使用數(shù)據(jù)索引能提高效率
■ 數(shù)據(jù)索引的存儲(chǔ)是有序的
■ 在有序的情況下，通過(guò)索引查詢(xún)一個(gè)數(shù)據(jù)是無(wú)需遍歷索引記錄的
■ 極端情況下，數(shù)據(jù)索引的查詢(xún)效率為二分法查詢(xún)效率，趨近于 log2(N)
2.如何理解數(shù)據(jù)索引的結(jié)構(gòu)
■ 數(shù)據(jù)索引通常默認(rèn)采用btree索引，（內(nèi)存表也使用了hash索引）。
■ 單一有序排序序列是查找效率最高的（二分查找，或者說(shuō)折半查找），使用樹(shù)形索引的目的是為了達(dá)到快速的更新和增刪操作。
■ 在極端情況下（比如數(shù)據(jù)查詢(xún)需求量非常大，而數(shù)據(jù)更新需求極少，實(shí)時(shí)性要求不高，數(shù)據(jù)規(guī)模有限），直接使用單一排序序列，折半查找速度最快。
◆實(shí)戰(zhàn)范例 ： ip地址反查
資源：
Ip地址對(duì)應(yīng)表，源數(shù)據(jù)格式為 startip, endip, area
源數(shù)據(jù)條數(shù)為 10萬(wàn)條左右，呈很大的分散性
目標(biāo)：
需要通過(guò)任意ip查詢(xún)?cè)搃p所屬地區(qū)
性能要求達(dá)到每秒1000次以上的查詢(xún)效率
挑戰(zhàn)：
如使用 between … and 數(shù)據(jù)庫(kù)操作，無(wú)法有效使用索引。
如果每次查詢(xún)請(qǐng)求需要遍歷10萬(wàn)條記錄，根本不行。
方法：
一次性排序（只在數(shù)據(jù)準(zhǔn)備中進(jìn)行，數(shù)據(jù)可存儲(chǔ)在內(nèi)存序列）
折半查找（每次請(qǐng)求以折半查找方式進(jìn)行）
■ 在進(jìn)行索引分析和SQL優(yōu)化時(shí)，可以將數(shù)據(jù)索引字段想象為單一有序序列，并以此作為分析的基礎(chǔ)。
◆實(shí)戰(zhàn)范例：復(fù)合索引查詢(xún)優(yōu)化實(shí)戰(zhàn)，同城異性列表
資源： 用戶(hù)表user，字段 sex性別；area 地區(qū)；lastlogin 最后登錄時(shí)間；其他略
目標(biāo)：
查找同一地區(qū)的異性，按照最后登錄時(shí)間逆序
高訪(fǎng)問(wèn)量社區(qū)的高頻查詢(xún)，如何優(yōu)化。
查詢(xún)SQL: select * from user where area=’$area’ and sex=’$sex’ order by lastlogin desc limit 0,30;
挑戰(zhàn)：
建立復(fù)合索引并不難， area+sex+lastlogin 三個(gè)字段的復(fù)合索引,如何理解？
首先，忘掉btree，將索引字段理解為一個(gè)排序序列。
如果只使用area會(huì)怎樣？搜索會(huì)把符合area的結(jié)果全部找出來(lái)，然后在這里面遍歷，選擇命中sex的并排序。 遍歷所有 area=’$area’數(shù)據(jù)！
如果使用了area+sex，略好，仍然要遍歷所有area=’$area’ and sex=’$sex’數(shù)據(jù)，然后在這個(gè)基礎(chǔ)上排序??！
Area+sex+lastlogin復(fù)合索引時(shí)（切記lastlogin在最后），該索引基于area+sex+lastlogin 三個(gè)字段合并的結(jié)果排序，該列表可以想象如下。
廣州女$時(shí)間1
廣州女$時(shí)間2
廣州女$時(shí)間3
…
廣州男
….
深圳女
….
數(shù)據(jù)庫(kù)很容易命中到 area+sex的邊界，并且基于下邊界向上追溯30條記錄，搞定！在索引中迅速命中所有結(jié)果，無(wú)需二次遍歷！
3.如何理解影響結(jié)果集
■ 影響結(jié)果集是數(shù)據(jù)查詢(xún)優(yōu)化的一個(gè)重要中間數(shù)據(jù)
◆ 查詢(xún)條件與索引的關(guān)系決定影響結(jié)果集
如上例所示，即便查詢(xún)用到了索引，但是如果查詢(xún)和排序目標(biāo)不能直接在索引中命中，其可能帶來(lái)較多的影響結(jié)果。而這會(huì)直接影響到查詢(xún)效率
◆ 微秒級(jí)優(yōu)化
● 優(yōu)化查詢(xún)不能只看慢查詢(xún)?nèi)罩荆Ｒ?guī)來(lái)說(shuō)，0.01秒以上的查詢(xún)，都是不夠優(yōu)化的。
● 實(shí)戰(zhàn)范例
和上案例類(lèi)似，某游戲社區(qū)要顯示用戶(hù)動(dòng)態(tài)，select * from userfeed where uid=$uid order by lastlogin desc limit 0,30; 初期默認(rèn)以u(píng)id為索引字段， 查詢(xún)?yōu)槊兴衭id=$uid的結(jié)果按照l(shuí)astlogin排序。 當(dāng)用戶(hù)行為非常頻繁時(shí)，該SQL索引命中影響結(jié)果集有數(shù)百乃至數(shù)千條記錄。查詢(xún)效率超過(guò)0.01秒，并發(fā)較大時(shí)數(shù)據(jù)庫(kù)壓力較大。
解決方案：將索引改為 uid+lastlogin 復(fù)合索引，索引直接命中影響結(jié)果集30條，查詢(xún)效率提高了10倍，平均在0.001秒，數(shù)據(jù)庫(kù)壓力驟降。
■ 影響結(jié)果集的常見(jiàn)誤區(qū)
◆ 影響結(jié)果集并不是說(shuō)數(shù)據(jù)查詢(xún)出來(lái)的結(jié)果數(shù)或操作影響的結(jié)果數(shù)，而是查詢(xún)條件的索引所命中的結(jié)果數(shù)。
◆ 實(shí)戰(zhàn)范例
● 某游戲數(shù)據(jù)庫(kù)使用了innodb，innodb是行級(jí)鎖，理論上很少存在鎖表情況。出現(xiàn)了一個(gè)SQL語(yǔ)句(delete from tabname where xid=…)，這個(gè)SQL非常用SQL，僅在特定情況下出現(xiàn)，每天出現(xiàn)頻繁度不高（一天僅10次左右），數(shù)據(jù)表容量百萬(wàn)級(jí)，但是這個(gè)xid未建立索引，于是悲慘的事情發(fā)生了，當(dāng)執(zhí)行這條delete 的時(shí)候，真正刪除的記錄非常少，也許一到兩條，也許一條都沒(méi)有；但是！由于這個(gè)xid未建立索引，delete操作時(shí)遍歷全表記錄，全表被delete操作鎖定，select操作全部被locked，由于百萬(wàn)條記錄遍歷時(shí)間較長(zhǎng)，期間大量select被阻塞，數(shù)據(jù)庫(kù)連接過(guò)多崩潰。
這種非高發(fā)請(qǐng)求，操作目標(biāo)很少的SQL，因未使用索引，連帶導(dǎo)致整個(gè)數(shù)據(jù)庫(kù)的查詢(xún)阻塞，需要極大提高警覺(jué)。
■ 總結(jié)：
◆ 影響結(jié)果集是搜索條件索引命中的結(jié)果集，而非輸出和操作的結(jié)果集。
◆ 影響結(jié)果集越趨近于實(shí)際輸出或操作的目標(biāo)結(jié)果集，索引效率越高。
◆ 請(qǐng)注意，我這里永遠(yuǎn)不會(huì)講關(guān)于外鍵和join的優(yōu)化，因?yàn)樵谖覀兊捏w系里，這是根本不允許的！ 架構(gòu)優(yōu)化部分會(huì)解釋為什么。
#p#
理解執(zhí)行狀態(tài)
1.常見(jiàn)分析手段
● 慢查詢(xún)?nèi)罩荆P(guān)注重點(diǎn)如下
■ 是否鎖定，及鎖定時(shí)間
◆ 如存在鎖定，則該慢查詢(xún)通常是因鎖定因素導(dǎo)致，本身無(wú)需優(yōu)化，需解決鎖定問(wèn)題。
■ 影響結(jié)果集
◆ 如影響結(jié)果集較大，顯然是索引項(xiàng)命中存在問(wèn)題，需要認(rèn)真對(duì)待。
● Explain 操作
■ 索引項(xiàng)使用
◆ 不建議用using index做強(qiáng)制索引，如未如預(yù)期使用索引，建議重新斟酌表結(jié)構(gòu)和索引設(shè)置。
■ 影響結(jié)果集
◆ 這里顯示的數(shù)字不一定準(zhǔn)確，結(jié)合之前提到對(duì)數(shù)據(jù)索引的理解來(lái)看，還記得嘛？就把索引當(dāng)作有序序列來(lái)理解，反思SQL。
● Set profiling , show profiles for query操作
■ 執(zhí)行開(kāi)銷(xiāo)
◆ 注意，有問(wèn)題的SQL如果重復(fù)執(zhí)行，可能在緩存里，這時(shí)要注意避免緩存影響。通過(guò)這里可以看到。
◆ 執(zhí)行時(shí)間超過(guò)0.005秒的頻繁操作SQL建議都分析一下。
◆ 深入理解數(shù)據(jù)庫(kù)執(zhí)行的過(guò)程和開(kāi)銷(xiāo)的分布
● Show processlist
■ 狀態(tài)清單
◆ Sleep 狀態(tài)， 通常代表資源未釋放，如果是通過(guò)連接池，sleep狀態(tài)應(yīng)該恒定在一定數(shù)量范圍內(nèi)
♦ 實(shí)戰(zhàn)范例： 因前端數(shù)據(jù)輸出時(shí)（特別是輸出到用戶(hù)終端）未及時(shí)關(guān)閉數(shù)據(jù)庫(kù)連接，導(dǎo)致因網(wǎng)絡(luò)連接速度產(chǎn)生大量sleep連接，在網(wǎng)速出現(xiàn)異常時(shí)，數(shù)據(jù)庫(kù) too many connections 掛死。
♦ 簡(jiǎn)單解讀，數(shù)據(jù)查詢(xún)和執(zhí)行通常只需要不到0.01秒，而網(wǎng)絡(luò)輸出通常需要1秒左右甚至更長(zhǎng)，原本數(shù)據(jù)連接在0.01秒即可釋放，但是因?yàn)榍岸顺绦蛭磮?zhí)行close操作，直接輸出結(jié)果，那么在結(jié)果未展現(xiàn)在用戶(hù)桌面前，該數(shù)據(jù)庫(kù)連接一直維持在sleep狀態(tài)！
◆ Waiting for net, reading from net, writing to net
♦ 偶爾出現(xiàn)無(wú)妨
♦ 如大量出現(xiàn)，迅速檢查數(shù)據(jù)庫(kù)到前端的網(wǎng)絡(luò)連接狀態(tài)和流量
♦ 案例: 因外掛程序，內(nèi)網(wǎng)數(shù)據(jù)庫(kù)大量讀取，內(nèi)網(wǎng)使用的百兆交換迅速爆滿(mǎn)，導(dǎo)致大量連接阻塞在waiting for net，數(shù)據(jù)庫(kù)連接過(guò)多崩潰
◆ Locked狀態(tài)
♦ 有更新操作鎖定
♦ 通常使用innodb可以很好的減少locked狀態(tài)的產(chǎn)生，但是切記，更新操作要正確使用索引，即便是低頻次更新操作也不能疏忽。如上影響結(jié)果集范例所示。
♦ 在myisam的時(shí)代，locked是很多高并發(fā)應(yīng)用的噩夢(mèng)。所以mysql官方也開(kāi)始傾向于推薦innodb。
◆ Copy to tmp table
♦ 索引及現(xiàn)有結(jié)構(gòu)無(wú)法涵蓋查詢(xún)條件，才會(huì)建立一個(gè)臨時(shí)表來(lái)滿(mǎn)足查詢(xún)要求，產(chǎn)生巨大的恐怖的i/o壓力。
♦ 很可怕的搜索語(yǔ)句會(huì)導(dǎo)致這樣的情況，如果是數(shù)據(jù)分析，或者半夜的周期數(shù)據(jù)清理任務(wù)，偶爾出現(xiàn)，可以允許。頻繁出現(xiàn)務(wù)必優(yōu)化之。
♦ Copy to tmp table 通常與連表查詢(xún)有關(guān)，建議逐漸習(xí)慣不使用連表查詢(xún)。
♦ 實(shí)戰(zhàn)范例：
某社區(qū)數(shù)據(jù)庫(kù)阻塞，求救，經(jīng)查，其服務(wù)器存在多個(gè)數(shù)據(jù)庫(kù)應(yīng)用和網(wǎng)站，其中一個(gè)不常用的小網(wǎng)站數(shù)據(jù)庫(kù)產(chǎn)生了一個(gè)恐怖的copy to tmp table 操作，導(dǎo)致整個(gè)硬盤(pán)i/o和cpu壓力超載。Kill掉該操作一切恢復(fù)。
◆ Sending data
♦ Sending data 并不是發(fā)送數(shù)據(jù)，別被這個(gè)名字所欺騙，這是從物理磁盤(pán)獲取數(shù)據(jù)的進(jìn)程，如果你的影響結(jié)果集較多，那么就需要從不同的磁盤(pán)碎片去抽取數(shù)據(jù)，
♦ 偶爾出現(xiàn)該狀態(tài)連接無(wú)礙。
♦ 回到上面影響結(jié)果集的問(wèn)題，一般而言，如果sending data連接過(guò)多，通常是某查詢(xún)的影響結(jié)果集過(guò)大，也就是查詢(xún)的索引項(xiàng)不夠優(yōu)化。
♦ 如果出現(xiàn)大量相似的SQL語(yǔ)句出現(xiàn)在show proesslist列表中，并且都處于sending data狀態(tài)，優(yōu)化查詢(xún)索引，記住用影響結(jié)果集的思路去思考。
◆ Freeing items
♦ 理論上這玩意不會(huì)出現(xiàn)很多。偶爾出現(xiàn)無(wú)礙
♦ 如果大量出現(xiàn)，內(nèi)存，硬盤(pán)可能已經(jīng)出現(xiàn)問(wèn)題。比如硬盤(pán)滿(mǎn)或損壞。
◆ Sorting for …
♦ 和Sending data類(lèi)似，結(jié)果集過(guò)大，排序條件沒(méi)有索引化，需要在內(nèi)存里排序，甚至需要?jiǎng)?chuàng)建臨時(shí)結(jié)構(gòu)排序。
◆ 其他
♦ 還有很多狀態(tài)，遇到了，去查查資料?；旧衔覀冇龅狡渌麪顟B(tài)的阻塞較少，所以不關(guān)心。
2.分析流程
● 基本流程
■ 詳細(xì)了解問(wèn)題狀況
◆ Too many connections 是常見(jiàn)表象，有很多種原因。
◆ 索引損壞的情況在innodb情況下很少出現(xiàn)。
◆ 如出現(xiàn)其他情況應(yīng)追溯日志和錯(cuò)誤信息。
■ 了解基本負(fù)載狀況和運(yùn)營(yíng)狀況
◆ 基本運(yùn)營(yíng)狀況
♦ 當(dāng)前每秒讀請(qǐng)求
♦ 當(dāng)前每秒寫(xiě)請(qǐng)求
♦ 當(dāng)前在線(xiàn)用戶(hù)
♦ 當(dāng)前數(shù)據(jù)容量
◆ 基本負(fù)載情況
♦ 學(xué)會(huì)使用這些指令
Top
Vmstat
uptime
iostat
df
♦ Cpu負(fù)載構(gòu)成
特別關(guān)注i/o壓力( wa%)
多核負(fù)載分配
♦ 內(nèi)存占用
Swap分區(qū)是否被侵占
如Swap分區(qū)被侵占，物理內(nèi)存是否較多空閑
♦ 磁盤(pán)狀態(tài)
硬盤(pán)滿(mǎn)和inode節(jié)點(diǎn)滿(mǎn)的情況要迅速定位和迅速處理
■ 了解具體連接狀況
◆ 當(dāng)前連接數(shù)
♦ Netstat –an|grep 3306|wc –l
♦ Show processlist
◆ 當(dāng)前連接分布 show processlist
♦ 前端應(yīng)用請(qǐng)求數(shù)據(jù)庫(kù)不要使用root帳號(hào)！
Root帳號(hào)比其他普通帳號(hào)多一個(gè)連接數(shù)許可。
前端使用普通帳號(hào)，在too many connections的時(shí)候root帳號(hào)仍可以登錄數(shù)據(jù)庫(kù)查詢(xún) show processlist!
記住，前端應(yīng)用程序不要設(shè)置一個(gè)不叫root的root帳號(hào)來(lái)糊弄！非root賬戶(hù)是骨子里的，而不是名義上的。
♦ 狀態(tài)分布
不同狀態(tài)代表不同的問(wèn)題，有不同的優(yōu)化目標(biāo)。
參見(jiàn)如上范例。
雷同SQL的分布
是否較多雷同SQL出現(xiàn)在同一狀態(tài)
◆ 當(dāng)前是否有較多慢查詢(xún)?nèi)罩?/p>
♦ 是否鎖定
♦ 影響結(jié)果集
■ 頻繁度分析
◆ 寫(xiě)頻繁度
♦ 如果i/o壓力高，優(yōu)先分析寫(xiě)入頻繁度
♦ Mysqlbinlog 輸出最新binlog文件，編寫(xiě)腳本拆分
♦ 最多寫(xiě)入的數(shù)據(jù)表是哪個(gè)
♦ 最多寫(xiě)入的數(shù)據(jù)SQL是什么
♦ 是否存在基于同一主鍵的數(shù)據(jù)內(nèi)容高頻重復(fù)寫(xiě)入？
涉及架構(gòu)優(yōu)化部分，參見(jiàn)架構(gòu)優(yōu)化-緩存異步更新
◆ 讀取頻繁度
♦ 如果cpu資源較高，而i/o壓力不高，優(yōu)先分析讀取頻繁度
♦ 程序中在封裝的db類(lèi)增加抽樣日志即可，抽樣比例酌情考慮，以不顯著影響系統(tǒng)負(fù)載壓力為底線(xiàn)。
♦ 最多讀取的數(shù)據(jù)表是哪個(gè)
♦ 最多讀取的數(shù)據(jù)SQL是什么
該SQL進(jìn)行explain 和set profiling判定
注意判定時(shí)需要避免query cache影響
比如，在這個(gè)SQL末尾增加一個(gè)條件子句 and 1=1 就可以避免從query cache中獲取數(shù)據(jù)，而得到真實(shí)的執(zhí)行狀態(tài)分析。
♦ 是否存在同一個(gè)查詢(xún)短期內(nèi)頻繁出現(xiàn)的情況
涉及前端緩存優(yōu)化
■ 抓大放小，解決顯著問(wèn)題
◆ 不苛求解決所有優(yōu)化問(wèn)題，但是應(yīng)以保證線(xiàn)上服務(wù)穩(wěn)定可靠為目標(biāo)。
◆ 解決與評(píng)估要同時(shí)進(jìn)行，新的策略或解決方案務(wù)必經(jīng)過(guò)評(píng)估后上線(xiàn)。
3.總結(jié)
● 要學(xué)會(huì)怎樣分析問(wèn)題，而不是單純拍腦袋優(yōu)化
■ 慢查詢(xún)只是最基礎(chǔ)的東西，要學(xué)會(huì)優(yōu)化0.01秒的查詢(xún)請(qǐng)求。
● 當(dāng)發(fā)生連接阻塞時(shí)，不同狀態(tài)的阻塞有不同的原因，要找到原因，如果不對(duì)癥下藥，就會(huì)南轅北轍
■ 范例：如果本身系統(tǒng)內(nèi)存已經(jīng)超載，已經(jīng)使用到了swap，而還在考慮加大緩存來(lái)優(yōu)化查詢(xún)，那就是自尋死路了。
● 監(jiān)測(cè)與跟蹤要經(jīng)常做，而不是出問(wèn)題才做
■ 讀取頻繁度抽樣監(jiān)測(cè)
◆ 全監(jiān)測(cè)不要搞，i/o嚇?biāo)廊恕?/p>
◆ 按照一個(gè)抽樣比例抽樣即可。
◆ 針對(duì)抽樣中發(fā)現(xiàn)的問(wèn)題，可以按照特定SQL在特定時(shí)間內(nèi)監(jiān)測(cè)一段全查詢(xún)記錄，但仍要考慮i/o影響。
■ 寫(xiě)入頻繁度監(jiān)測(cè)
◆ 基于binlog解開(kāi)即可，可定時(shí)或不定時(shí)分析。
■ 微慢查詢(xún)抽樣監(jiān)測(cè)
◆ 高并發(fā)情況下，查詢(xún)請(qǐng)求時(shí)間超過(guò)0.01秒甚至0.005秒的，建議酌情抽樣記錄。
■ 連接數(shù)預(yù)警監(jiān)測(cè)
◆ 連接數(shù)超過(guò)特定閾值的情況下，雖然數(shù)據(jù)庫(kù)沒(méi)有崩潰，建議記錄相關(guān)連接狀態(tài)。
● 學(xué)會(huì)通過(guò)數(shù)據(jù)和監(jiān)控發(fā)現(xiàn)問(wèn)題，分析問(wèn)題，而后解決問(wèn)題順理成章。特別是要學(xué)會(huì)在日常監(jiān)控中發(fā)現(xiàn)隱患，而不是問(wèn)題爆發(fā)了才去處理和解決。
【編輯推薦】
