image

前言

又和大家見面了！又兩周過去了，我的云筆記里又多了幾篇寫了一半的文章草稿岩灭。有的是因?yàn)橘|(zhì)量沒有達(dá)到預(yù)期還準(zhǔn)備再加點(diǎn)內(nèi)容，有的則完全是一個(gè)靈感而已赂鲤，內(nèi)容完全木有噪径。羨慕很多大佬們柱恤，一周能產(chǎn)出五六篇文章，給我兩個(gè)肝我都不夠找爱。好了梗顺，不多說廢話了...

最近在線上環(huán)境遇到了一次SQL慢查詢引發(fā)的數(shù)據(jù)庫故障，影響線上業(yè)務(wù)车摄。經(jīng)過排查后寺谤，確定原因是SQL在執(zhí)行時(shí)，MySQL優(yōu)化器選擇了錯(cuò)誤的索引（不應(yīng)該說是“錯(cuò)誤”练般，而是選擇了實(shí)際執(zhí)行耗時(shí)更長的索引）矗漾。在排查過程中，查閱了許多資料薄料，也學(xué)習(xí)了下MySQL優(yōu)化器選擇索引的基本準(zhǔn)則敞贡，在本文中進(jìn)行解決問題思路的分享。本人MySQL了解深度有限摄职，如果錯(cuò)誤歡迎理性討論和指正誊役。

在這次事故中也能充分看出深入了解MySQL運(yùn)行原理的重要性，這是遇到問題時(shí)能否獨(dú)立解決問題的關(guān)鍵谷市。 試想一個(gè)月黑風(fēng)高的夜晚蛔垢，公司線上突然掛了，而你的同事們都不在線迫悠，就你一個(gè)人有條件解決問題鹏漆，這時(shí)候如果被工程師的基本功把你卡住了，就問你尷不尷尬...

本文的主要內(nèi)容：

• 故障描述
• 問題原因排查
• MySQL索引選擇原理
• 解決方案
• 思考與總結(jié)

請(qǐng)大家多多支持我的原創(chuàng)技術(shù)公眾號(hào)：后端技術(shù)漫談

正文

故障描述

在7月24日11點(diǎn)線上某數(shù)據(jù)庫突然收到大量告警创泄，慢查詢數(shù)超標(biāo)艺玲，并且引發(fā)了連接數(shù)暴增，導(dǎo)致數(shù)據(jù)庫響應(yīng)緩慢鞠抑，影響業(yè)務(wù)饭聚。看圖表慢查詢?cè)诟叻暹_(dá)到了每分鐘14w次搁拙，在平時(shí)正常情況下慢查詢數(shù)僅在兩位數(shù)以下秒梳，如下圖：

image

趕緊查看慢SQL記錄，發(fā)現(xiàn)都是同一類語句導(dǎo)致的慢查詢(隱私數(shù)據(jù)例如表名箕速，我已經(jīng)隱去)：

select
  *
from
  sample_table
where
    1 = 1
    and (city_id = 565)
    and (type = 13)
order by
  id desc
limit
  0, 1

看起來語句很簡單酪碘，沒什么特別的。但是每個(gè)執(zhí)行的查詢時(shí)間達(dá)到了驚人的44s盐茎。

image

簡直聳人聽聞婆跑，這已經(jīng)不是“慢”能形容的了...

接下來查看表數(shù)據(jù)信息，如下圖：

image

可以看到表數(shù)據(jù)量較大庭呜，預(yù)估行數(shù)在83683240滑进，也就是8000w左右犀忱，千萬數(shù)據(jù)量的表。

大致情況就是這樣扶关，下面進(jìn)入排查問題的環(huán)節(jié)阴汇。

問題原因排查

首先當(dāng)然要懷疑會(huì)不會(huì)該語句沒走索引，查看建表DML中的索引：

KEY `idx_1` (`city_id`,`type`,`rank`),
KEY `idx_log_dt_city_id_rank` (`log_dt`,`city_id`,`rank`),
KEY `idx_city_id_type` (`city_id`,`type`)

請(qǐng)忽略idx_1和idx_city_id_type兩個(gè)索引的重復(fù)节槐，這都是歷史遺留問題了搀庶。

可以看到是有idx_city_id_type和idx_1索引的，我們的查詢條件是city_id和type铜异，這兩個(gè)索引都是能走到的哥倔。

但是，我們的查詢條件真的只要考慮city_id和type嗎揍庄？（機(jī)智的小伙伴應(yīng)該注意到問題所在了咆蒿，先往下講，留給大家思考）

既然有索引蚂子，接下來就該看該語句實(shí)際有沒有走到索引了沃测，MySQL提供了Explain可以分析SQL語句。Explain 用來分析 SELECT 查詢語句食茎。

Explain比較重要的字段有：

• select_type : 查詢類型蒂破，有簡單查詢、聯(lián)合查詢别渔、子查詢等
• key : 使用的索引
• rows : 預(yù)計(jì)需要掃描的行數(shù)

更多詳細(xì)Explain介紹可以參考：MySQL 性能優(yōu)化神器 Explain 使用分析

我們使用Explain分析該語句：

select * from sample_table where city_id = 565 and type = 13 order by id desc limit 0,1

得到結(jié)果：

image

可以看出附迷，雖然possiblekey有我們的索引，但是最后走了主鍵索引哎媚。而表是千萬級(jí)別喇伯，并且該查詢條件最后實(shí)際是返回的空數(shù)據(jù)，也就是MySQL在主鍵索引上實(shí)際檢索時(shí)間很長抄伍，導(dǎo)致了慢查詢。

我們可以使用force index(idx_city_id_type)讓該語句選擇我們?cè)O(shè)置的聯(lián)合索引：

select * from sample_table force index(idx_city_id_type)  where ( ( (1 = 1) and (city_id = 565) ) and (type = 13) ) order by id desc limit 0, 1

這次明顯執(zhí)行的飛快管宵，分析語句：

image

實(shí)際執(zhí)行時(shí)間0.00175714s截珍，走了聯(lián)合索引后，不再是慢查詢了箩朴。

問題找到了岗喉，總結(jié)下來就是：MySQL優(yōu)化器認(rèn)為在limit 1的情況下，走主鍵索引能夠更快的找到那一條數(shù)據(jù)炸庞，并且如果走聯(lián)合索引需要掃描索引后進(jìn)行排序钱床，而主鍵索引天生有序，所以優(yōu)化器綜合考慮埠居，走了主鍵索引查牌。實(shí)際上事期，MySQL遍歷了8000w條數(shù)據(jù)也沒找到那個(gè)天選之人（符合條件的數(shù)據(jù)），所以浪費(fèi)了很多時(shí)間纸颜。

MySQL索引選擇原理

優(yōu)化器索引選擇的準(zhǔn)則

MySQL一條語句的執(zhí)行流程大致如下圖兽泣，而查詢優(yōu)化器則是選擇索引的地方：

image

引用參考文獻(xiàn)一段解釋：

首先要知道，選擇索引是MySQL優(yōu)化器的工作胁孙。

而優(yōu)化器選擇索引的目的唠倦，是找到一個(gè)最優(yōu)的執(zhí)行方案，并用最小的代價(jià)去執(zhí)行語句涮较。在數(shù)據(jù)庫里面稠鼻，掃描行數(shù)是影響執(zhí)行代價(jià)的因素之一。掃描的行數(shù)越少狂票，意味著訪問磁盤數(shù)據(jù)的次數(shù)越少候齿，消耗的CPU資源越少。

當(dāng)然苫亦，掃描行數(shù)并不是唯一的判斷標(biāo)準(zhǔn)毛肋，優(yōu)化器還會(huì)結(jié)合是否使用臨時(shí)表、是否排序等因素進(jìn)行綜合判斷屋剑。

總結(jié)下來润匙，優(yōu)化器選擇有許多考慮的因素：掃描行數(shù)、是否使用臨時(shí)表唉匾、是否排序等等

我們回頭看剛才的兩個(gè)explain截圖：

image

image

走了主鍵索引的查詢語句孕讳，rows預(yù)估行數(shù)1833，而強(qiáng)制走聯(lián)合索引行數(shù)是45640巍膘，并且Extra信息中厂财，顯示需要Using filesort進(jìn)行額外的排序。所以在不加強(qiáng)制索引的情況下峡懈，優(yōu)化器選擇了主鍵索引璃饱，因?yàn)樗X得主鍵索引掃描行數(shù)少，而且不需要額外的排序操作肪康，主鍵索引天生有序荚恶。

rows是怎么預(yù)估出來的

同學(xué)們就要問了，為什么rows只有1833磷支，明明實(shí)際掃描了整個(gè)主鍵索引啊,行數(shù)遠(yuǎn)遠(yuǎn)不止幾千行谒撼。實(shí)際上explain的rows是MySQL預(yù)估的行數(shù)，是根據(jù)查詢條件雾狈、索引和limit綜合考慮出來的預(yù)估行數(shù)廓潜。

MySQL是怎樣得到索引的基數(shù)的呢？這里，我給你簡單介紹一下MySQL采樣統(tǒng)計(jì)的方法辩蛋。

為什么要采樣統(tǒng)計(jì)呢呻畸？因?yàn)榘颜麖埍砣〕鰜硪恍行薪y(tǒng)計(jì)，雖然可以得到精確的結(jié)果堪澎，但是代價(jià)太高了擂错，所以只能選擇“采樣統(tǒng)計(jì)”。

采樣統(tǒng)計(jì)的時(shí)候樱蛤，InnoDB默認(rèn)會(huì)選擇N個(gè)數(shù)據(jù)頁钮呀，統(tǒng)計(jì)這些頁面上的不同值，得到一個(gè)平均值昨凡，然后乘以這個(gè)索引的頁面數(shù)爽醋，就得到了這個(gè)索引的基數(shù)。

而數(shù)據(jù)表是會(huì)持續(xù)更新的便脊，索引統(tǒng)計(jì)信息也不會(huì)固定不變蚂四。所以，當(dāng)變更的數(shù)據(jù)行數(shù)超過1/M的時(shí)候哪痰，會(huì)自動(dòng)觸發(fā)重新做一次索引統(tǒng)計(jì)遂赠。

在MySQL中，有兩種存儲(chǔ)索引統(tǒng)計(jì)的方式晌杰，可以通過設(shè)置參數(shù)innodb_stats_persistent的值來選擇：

設(shè)置為on的時(shí)候跷睦，表示統(tǒng)計(jì)信息會(huì)持久化存儲(chǔ)。這時(shí)肋演，默認(rèn)的N是20抑诸，M是10。
設(shè)置為off的時(shí)候爹殊，表示統(tǒng)計(jì)信息只存儲(chǔ)在內(nèi)存中蜕乡。這時(shí)，默認(rèn)的N是8梗夸，M是16层玲。
由于是采樣統(tǒng)計(jì)，所以不管N是20還是8反症，這個(gè)基數(shù)都是很容易不準(zhǔn)的辛块。

我們可以使用analyze table t命令，可以用來重新統(tǒng)計(jì)索引信息惰帽。但是這條命令生產(chǎn)環(huán)境需要聯(lián)系DBA憨降，所以我就不做實(shí)驗(yàn)了,大家可以自行實(shí)驗(yàn)父虑。

索引要考慮 order by 的字段

為什么這么說该酗？因?yàn)槿绻疫@個(gè)表中的索引是city_id,type和id的聯(lián)合索引，那優(yōu)化器就會(huì)走這個(gè)聯(lián)合索引，因?yàn)樗饕呀?jīng)做好了排序呜魄。

更改limit大小能解決問題悔叽？

把limit數(shù)量調(diào)大會(huì)影響預(yù)估行數(shù)rows，進(jìn)而影響優(yōu)化器索引的選擇嗎爵嗅？

答案是會(huì)娇澎。

我們執(zhí)行l(wèi)imit 10

select * from sample_table where city_id = 565 and type = 13 order by id desc limit 0,10

image

圖中rows變?yōu)榱?8211，增長了10倍睹晒。如果使用limit 100趟庄，會(huì)發(fā)生什么？

image

優(yōu)化器選擇了聯(lián)合索引伪很。初步估計(jì)是rows還會(huì)翻倍戚啥，所以優(yōu)化器放棄了主鍵索引。寧愿用聯(lián)合索引后排序锉试，也不愿意用主鍵索引了猫十。

為何突然出現(xiàn)異常慢查詢

問：這個(gè)查詢語句已經(jīng)在線上穩(wěn)定運(yùn)行了非常長的時(shí)間，為何這次突然出現(xiàn)了慢查詢呆盖？

答：以前的語句查詢條件返回結(jié)果都不為空拖云，limit1很快就能找到那條數(shù)據(jù)，返回結(jié)果应又。而這次代碼中查詢條件實(shí)際結(jié)果為空宙项，導(dǎo)致了掃描了全部的主鍵索引。

解決方案

知道了MySQL為何選擇這個(gè)索引的原因后丁频，我們就可以根據(jù)上面的思路來列舉出解決辦法了杉允。

主要有兩個(gè)大方向：

1. 強(qiáng)制指定索引
2. 干涉優(yōu)化器選擇

強(qiáng)制選擇索引：force index

就像上面我最開始的操作那樣，我們直接使用force index席里，讓語句走我們想要走的索引叔磷。

select * from sample_table force index(idx_city_id_type)  where ( ( (1 = 1) and (city_id = 565) ) and (type = 13) ) order by id desc limit 0, 1

這樣做的優(yōu)點(diǎn)是見效快，問題馬上就能解決奖磁。

缺點(diǎn)也很明顯：

• 高耦合改基，這種語句寫在代碼里，會(huì)變得難以維護(hù)咖为，如果索引名變化了秕狰，或者沒有這個(gè)索引了，代碼就要反復(fù)修改躁染。屬于硬編碼鸣哀。
• 很多代碼用框架封裝了SQL，force index()并不容易加進(jìn)去吞彤。

我們換一種辦法我衬，我們?nèi)ヒ龑?dǎo)優(yōu)化器選擇聯(lián)合索引叹放。

干涉優(yōu)化器選擇：增大limit

通過增大limit，我們可以讓預(yù)估掃描行數(shù)快速增加挠羔，比如改成下面的limit 0, 1000

SELECT * FROM sample_table where city_id = 565 and type = 13 order by id desc LIMIT 0,1000

這樣就會(huì)走上聯(lián)合索引井仰，然后排序，但是這樣強(qiáng)行增長limit破加，其實(shí)總有種面向黑盒調(diào)參的感覺俱恶。我們還有更優(yōu)美的解決方案嗎？

干涉優(yōu)化器選擇：增加包含order by id字段的聯(lián)合索引

我們這句慢查詢使用的是order by id范舀，但是我們卻沒有在聯(lián)合索引中加入id字段合是，導(dǎo)致了優(yōu)化器認(rèn)為聯(lián)合索引后還要排序，干脆就不太想走這個(gè)聯(lián)合索引了锭环。

我們可以新建city_id,type和id的聯(lián)合索引端仰，來解決這個(gè)問題。

這樣也有一定的弊端田藐，比如我這個(gè)表到了8000w數(shù)據(jù)荔烧，建立索引非常耗時(shí)，而且通常索引就有3.4個(gè)g汽久，如果無限制的用索引解決問題鹤竭，可能會(huì)帶來新的問題。表中的索引不宜過多景醇。

干涉優(yōu)化器選擇：寫成子查詢

還有什么辦法臀稚？我們可以用子查詢，在子查詢里先走city_id和type的聯(lián)合索引三痰，得到結(jié)果集后在limit1選出第一條吧寺。

但是子查詢使用有風(fēng)險(xiǎn)，一版DBA也不建議使用子查詢散劫，會(huì)建議大家在代碼邏輯中完成復(fù)雜的查詢稚机。當(dāng)然我們這句并不復(fù)雜啦~

Select * From sample_table Where id in (Select id From `newhome_db`.`af_hot_price_region` where (city_id = 565 and type = 13)) limit 0, 1

還有很多解決辦法...

SQL優(yōu)化是個(gè)很大的工程，我們還有非常多的辦法能夠解決這句慢查詢問題获搏，這里就不一一展開了赖条。留給大家做為思考題了。

總結(jié)

本文帶大家回顧了一次MySQL優(yōu)化器選錯(cuò)索引導(dǎo)致的線上慢查詢事故常熙，可以看出MySQL優(yōu)化器對(duì)于索引的選擇并不單單依靠某一個(gè)標(biāo)準(zhǔn)纬乍，而是一個(gè)綜合選擇的結(jié)果。我自己也對(duì)這方面了解不深入裸卫，還需要多多學(xué)習(xí)仿贬，爭取能夠好好的做一個(gè)索引選擇的總結(jié)（挖坑）。不說了墓贿，拿起巨厚的《高性能MySQL》,開始...

壓住我的泡面...

最后做個(gè)文章總結(jié)：

• 該慢查詢語句中使用order by id導(dǎo)致優(yōu)化器在主鍵索引和city_id和type的聯(lián)合索引中有所取舍茧泪，最終導(dǎo)致選擇了更慢的索引退个。
• 可以通過強(qiáng)制指定索引，建立包含id的聯(lián)合索引调炬，增大limit等方式解決問題。
• 平時(shí)開發(fā)時(shí)舱馅，尤其是對(duì)于特大數(shù)據(jù)量的表缰泡，要注意SQL語句的規(guī)范和索引的建立，避免事故的發(fā)生代嗤。

參考

《高性能MySQL》

MySQL優(yōu)化器 limit影響的case：

https://www.cnblogs.com/xpchild/p/3878417.html

mysql中走與不走索引的情況匯集(待全量實(shí)驗(yàn))：

https://www.cnblogs.com/gxyandwmm/p/13363100.html

MySQL ORDER BY主鍵id加LIMIT限制走錯(cuò)索引：

http://www.reibang.com/p/caf5818eca81

【業(yè)務(wù)學(xué)習(xí)】關(guān)于MySQL order by limit 走錯(cuò)索引的探討：

https://segmentfault.com/a/1190000020399424

MySQL為什么有時(shí)候會(huì)選錯(cuò)索引棘钞？：

https://www.cnblogs.com/a-phper/p/10313888.html

關(guān)注我

我是一名后端開發(fā)工程師。主要關(guān)注后端開發(fā)干毅，數(shù)據(jù)安全宜猜，爬蟲，物聯(lián)網(wǎng)硝逢，邊緣計(jì)算等方向姨拥，歡迎交流。

各大平臺(tái)都可以找到我

• 微信公眾號(hào)：后端技術(shù)漫談
• Github：@qqxx6661
• CSDN：@蠻三刀把刀
• 知乎：@后端技術(shù)漫談
• 簡書：@蠻三刀把刀
• 掘金：@蠻三刀把刀
• 騰訊云+社區(qū)：@后端技術(shù)漫談

原創(chuàng)文章主要內(nèi)容

• 后端開發(fā)
• Java面試
• 設(shè)計(jì)模式/數(shù)據(jù)結(jié)構(gòu)/算法題解
• 爬蟲/邊緣計(jì)算/物聯(lián)網(wǎng)
• 讀書筆記/逸聞趣事/程序人生

個(gè)人公眾號(hào)：后端技術(shù)漫談

個(gè)人公眾號(hào)：后端技術(shù)漫談

如果文章對(duì)你有幫助渠鸽，不妨收藏叫乌，轉(zhuǎn)發(fā)，在看起來~