[[379919]]

圖片來(lái)自 Pexels 

經(jīng)過(guò)排查后，確定原因是：SQL 在執(zhí)行時(shí)，MySQL 優(yōu)化器選擇了錯(cuò)誤的索引(不應(yīng)該說(shuō)是“錯(cuò)誤”，而是選擇了實(shí)際執(zhí)行耗時(shí)更長(zhǎng)的索引)。

排查過(guò)程中，查閱了許多資料，也學(xué)習(xí)了下 MySQL 優(yōu)化器選擇索引的基本準(zhǔn)則，在本文中進(jìn)行解決問(wèn)題思路的分享。

PS：本人 MySQL 了解深度有限，如有錯(cuò)誤歡迎在評(píng)論區(qū)理性討論和指正。

在這次事故中也能充分看出深入了解 MySQL 運(yùn)行原理的重要性，這是遇到問(wèn)題時(shí)能否獨(dú)立解決問(wèn)題的關(guān)鍵。

試想一個(gè)月黑風(fēng)高的夜晚，公司線上突然掛了，而你的同事們都不在線，就你一個(gè)人有條件解決問(wèn)題，這時(shí)候如果被工程師的基本功把你卡住了，就問(wèn)你尷不尷尬...

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

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

故障描述

在去年 7 月 24 日 11 點(diǎn)線上某數(shù)據(jù)庫(kù)突然收到大量告警，慢查詢數(shù)超標(biāo)，并且引發(fā)了連接數(shù)暴增，導(dǎo)致數(shù)據(jù)庫(kù)響應(yīng)緩慢，影響業(yè)務(wù)。

看圖表慢查詢?cè)诟叻暹_(dá)到了每分鐘 14w 次，在平時(shí)正常情況下慢查詢數(shù)僅在兩位數(shù)以下，如下圖：

 

趕緊查看慢 SQL 記錄，發(fā)現(xiàn)都是同一類語(yǔ)句導(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 

看起來(lái)語(yǔ)句很簡(jiǎn)單，沒(méi)什么特別的，但是每個(gè)執(zhí)行的查詢時(shí)間達(dá)到了驚人的 44s。

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

 

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

 

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

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

問(wèn)題原因排查

首先當(dāng)然要懷疑會(huì)不會(huì)該語(yǔ)句沒(méi)走索引，查看建表 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ù)，這都是歷史遺留問(wèn)題了。

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

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

既然有索引，接下來(lái)就該看該語(yǔ)句實(shí)際有沒(méi)有走到索引了，MySQL 提供了 Explain 可以分析 SQL 語(yǔ)句。Explain 用來(lái)分析 SELECT 查詢語(yǔ)句。

Explain 比較重要的字段有：

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

我們使用 Explain 分析該語(yǔ)句：

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

得到結(jié)果：

 

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

我們可以使用 force index(idx_city_id_type) 讓該語(yǔ)句選擇我們?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í)行得飛快，分析語(yǔ)句：

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

 

問(wèn)題找到了，總結(jié)下來(lái)就是：MySQL 優(yōu)化器認(rèn)為在 limit 1 的情況下，走主鍵索引能夠更快的找到那一條數(shù)據(jù)，并且如果走聯(lián)合索引需要掃描索引后進(jìn)行排序，而主鍵索引天生有序，所以優(yōu)化器綜合考慮，走了主鍵索引。

實(shí)際上，MySQL 遍歷了 8000w 條數(shù)據(jù)也沒(méi)找到那個(gè)天選之人(符合條件的數(shù)據(jù))，所以浪費(fèi)了很多時(shí)間。

MySQL 索引選擇原理

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

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

引用參考文獻(xiàn)一段解釋：首先要知道，選擇索引是 MySQL 優(yōu)化器的工作。 

而優(yōu)化器選擇索引的目的，是找到一個(gè)最優(yōu)的執(zhí)行方案，并用最小的代價(jià)去執(zhí)行語(yǔ)句。

在數(shù)據(jù)庫(kù)里面，掃描行數(shù)是影響執(zhí)行代價(jià)的因素之一。掃描的行數(shù)越少，意味著訪問(wèn)磁盤數(shù)據(jù)的次數(shù)越少，消耗的 CPU 資源越少。

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

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

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

 

走了主鍵索引的查詢語(yǔ)句，rows 預(yù)估行數(shù) 1833，而強(qiáng)制走聯(lián)合索引行數(shù)是 45640，并且 Extra 信息中，顯示需要 Using filesort 進(jìn)行額外的排序。

所以在不加強(qiáng)制索引的情況下，優(yōu)化器選擇了主鍵索引，因?yàn)樗X(jué)得主鍵索引掃描行數(shù)少，而且不需要額外的排序操作，主鍵索引天生有序。

②rows 是怎么預(yù)估出來(lái)的

同學(xué)們就要問(wèn)了，為什么 rows 只有 1833，明明實(shí)際掃描了整個(gè)主鍵索引啊,行數(shù)遠(yuǎn)遠(yuǎn)不止幾千行。

實(shí)際上 explain 的 rows 是 MySQL 預(yù)估的行數(shù)，是根據(jù)查詢條件、索引和 limit 綜合考慮出來(lái)的預(yù)估行數(shù)。

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

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

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

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

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

設(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 命令，可以用來(lái)重新統(tǒng)計(jì)索引信息。但是這條命令生產(chǎn)環(huán)境需要聯(lián)系 DBA，所以我就不做實(shí)驗(yàn)了，大家可以自行實(shí)驗(yàn)。

③索引要考慮 order by 的字段

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

④更改 limit 大小能解決問(wèn)題?

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

答案是會(huì)。

我們執(zhí)行 limit 10：

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

 

 

圖中 rows 變?yōu)榱?18211，增長(zhǎng)了 10 倍。如果使用 limit 100，會(huì)發(fā)生什么?

 

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

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

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

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

解決方案

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

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

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

①?gòu)?qiáng)制選擇索引：force index

就像上面我最開(kāi)始的操作那樣，我們直接使用 force index，讓語(yǔ)句走我們想要走的索引。

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)是見(jiàn)效快，問(wèn)題馬上就能解決。

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

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

我們換一種辦法，去引導(dǎo)優(yōu)化器選擇聯(lián)合索引。

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

通過(guò)增大 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)行增長(zhǎng) limit，其實(shí)總有種面向黑盒調(diào)參的感覺(jué)。我們還有更優(yōu)美的解決方案嗎?

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

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

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

這樣也有一定的弊端，比如我這個(gè)表到了 8000w 數(shù)據(jù)，建立索引非常耗時(shí)，而且通常索引就有 3.4 個(gè) G，如果無(wú)限制的用索引解決問(wèn)題，可能會(huì)帶來(lái)新的問(wèn)題。表中的索引不宜過(guò)多。

④干涉優(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è)很大的工程，我們還有非常多的辦法能夠解決這句慢查詢問(wèn)題，這里就不一一展開(kāi)了。

總結(jié)

本文帶大家回顧了一次 MySQL 優(yōu)化器選錯(cuò)索引導(dǎo)致的線上慢查詢事故，可以看出 MySQL 優(yōu)化器對(duì)于索引的選擇并不單單依靠某一個(gè)標(biāo)準(zhǔn)，而是一個(gè)綜合選擇的結(jié)果。

我自己也對(duì)這方面了解不深入，還需要多多學(xué)習(xí)，爭(zhēng)取能夠好好的做一個(gè)索引選擇的總結(jié)(挖坑)。不說(shuō)了，拿起巨厚的《高性能MySQL》，開(kāi)始?jí)鹤∥业呐菝?..

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

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

作者：蠻三刀把刀

編輯：陶家龍

出處：轉(zhuǎn)載自公眾號(hào)后端技術(shù)漫談(ID：Rude3Knife)