共計 4479 個字符，預(yù)計需要花費 12 分鐘才能閱讀完成。
這篇文章主要講解了“如何解決千萬級數(shù)據(jù)表選錯索引導(dǎo)致的線上慢查詢事故”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請大家跟著丸趣 TV 小編的思路慢慢深入，一起來研究和學(xué)習(xí)“如何解決千萬級數(shù)據(jù)表選錯索引導(dǎo)致的線上慢查詢事故”吧！
故障描述
在 7 月 24 日 11 點線上某數(shù)據(jù)庫突然收到大量告警，慢查詢數(shù)超標(biāo)，并且引發(fā)了連接數(shù)暴增，導(dǎo)致數(shù)據(jù)庫響應(yīng)緩慢，影響業(yè)務(wù)。看圖表慢查詢在高峰達(dá)到了每分鐘 14w 次，在平時正常情況下慢查詢數(shù)僅在兩位數(shù)以下，如下圖：
趕緊查看慢 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看起來語句很簡單，沒什么特別的。但是每個執(zhí)行的查詢時間達(dá)到了驚人的 44s。
簡直聳人聽聞，這已經(jīng)不是“慢”能形容的了 …
接下來查看表數(shù)據(jù)信息，如下圖：
可以看到表數(shù)據(jù)量較大，預(yù)估行數(shù)在 83683240，也就是 8000w 左右，「千萬數(shù)據(jù)量的表」。
大致情況就是這樣，下面進(jìn)入排查問題的環(huán)節(jié)。
問題原因排查
首先當(dāng)然要懷疑會不會該語句沒走索引，查看建表 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`)請忽略 idx_1 和 idx_city_id_type 兩個索引的重復(fù)，這都是歷史遺留問題了。
「可以看到是有 idx_city_id_type 和 idx_1 索引的」，我們的查詢條件是 city_id 和 type，這兩個索引都是能走到的。
但是，我們的查詢條件真的只要考慮 city_id 和 type 嗎?(機(jī)智的小伙伴應(yīng)該注意到問題所在了，先往下講，留給大家思考)
既然有索引，接下來就該看該語句實際有沒有走到索引了，MySQL 提供了 Explain 可以分析 SQL 語句。Explain 用來分析 SELECT 查詢語句。
Explain 比較重要的字段有：
select_type : 查詢類型，有簡單查詢、聯(lián)合查詢、子查詢等
key : 使用的索引
rows : 預(yù)計需要掃描的行數(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é)果：
可以看出，雖然 possiblekey 有我們的索引，但是最后走了主鍵索引。而表是千萬級別，「并且該查詢條件最后實際是返回的空數(shù)據(jù)」，也就是 MySQL 在主鍵索引上實際檢索時間很長，導(dǎo)致了慢查詢。
我們可以使用 force index(idx_city_id_type) 讓該語句選擇我們設(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í)行的飛快，分析語句：
實際執(zhí)行時間 0.00175714s，走了聯(lián)合索引后，不再是慢查詢了。
問題找到了，總結(jié)下來就是：「MySQL 優(yōu)化器認(rèn)為在 limit 1 的情況下，走主鍵索引能夠更快的找到那一條數(shù)據(jù)，并且如果走聯(lián)合索引需要掃描索引后進(jìn)行排序，而主鍵索引天生有序，所以優(yōu)化器綜合考慮，走了主鍵索引。實際上，MySQL 遍歷了 8000w 條數(shù)據(jù)也沒找到那個天選之人 (符合條件的數(shù)據(jù))，所以浪費了很多時間。」
MySQL 索引選擇原理
優(yōu)化器索引選擇的準(zhǔn)則
MySQL 一條語句的執(zhí)行流程大致如下圖，而「查詢優(yōu)化器」則是選擇索引的地方：
引用參考文獻(xiàn)一段解釋：
首先要知道，選擇索引是 MySQL 優(yōu)化器的工作。
而優(yōu)化器選擇索引的目的，是找到一個最優(yōu)的執(zhí)行方案，并用最小的代價去執(zhí)行語句。在數(shù)據(jù)庫里面，掃描行數(shù)是影響執(zhí)行代價的因素之一。掃描的行數(shù)越少，意味著訪問磁盤數(shù)據(jù)的次數(shù)越少，消耗的 CPU 資源越少。
「當(dāng)然，掃描行數(shù)并不是唯一的判斷標(biāo)準(zhǔn)，優(yōu)化器還會結(jié)合是否使用臨時表、是否排序等因素進(jìn)行綜合判斷。」
總結(jié)下來，優(yōu)化器選擇有許多考慮的因素：「掃描行數(shù)、是否使用臨時表、是否排序等等」
我們回頭看剛才的兩個 explain 截圖：
走了「主鍵索引」的查詢語句，rows 預(yù)估行數(shù) 1833，而強(qiáng)制走「聯(lián)合索引」行數(shù)是 45640，并且 Extra 信息中，顯示需要 Using filesort 進(jìn)行額外的排序。所以在不加強(qiáng)制索引的情況下，「優(yōu)化器選擇了主鍵索引，因為它覺得主鍵索引掃描行數(shù)少，而且不需要額外的排序操作，主鍵索引天生有序。」
rows 是怎么預(yù)估出來的
同學(xué)們就要問了，為什么 rows 只有 1833，明明實際掃描了整個主鍵索引啊, 行數(shù)遠(yuǎn)遠(yuǎn)不止幾千行。實際上 explain 的 rows 是 MySQL「預(yù)估」的行數(shù)，「是根據(jù)查詢條件、索引和 limit 綜合考慮出來的預(yù)估行數(shù)。」
MySQL 是怎樣得到索引的基數(shù)的呢? 這里，我給你簡單介紹一下 MySQL 采樣統(tǒng)計的方法。
為什么要采樣統(tǒng)計呢? 因為把整張表取出來一行行統(tǒng)計，雖然可以得到精確的結(jié)果，但是代價太高了，所以只能選擇“采樣統(tǒng)計”。
采樣統(tǒng)計的時候，InnoDB 默認(rèn)會選擇 N 個數(shù)據(jù)頁，統(tǒng)計這些頁面上的不同值，得到一個平均值，然后乘以這個索引的頁面數(shù)，就得到了這個索引的基數(shù)。
而數(shù)據(jù)表是會持續(xù)更新的，索引統(tǒng)計信息也不會固定不變。所以，當(dāng)變更的數(shù)據(jù)行數(shù)超過 1 / M 的時候，會自動觸發(fā)重新做一次索引統(tǒng)計。
在 MySQL 中，有兩種存儲索引統(tǒng)計的方式，可以通過設(shè)置參數(shù) innodb_stats_persistent 的值來選擇：
設(shè)置為 on 的時候，表示統(tǒng)計信息會持久化存儲。這時，默認(rèn)的 N 是 20，M 是 10。
設(shè)置為 off 的時候，表示統(tǒng)計信息只存儲在內(nèi)存中。這時，默認(rèn)的 N 是 8，M 是 16。
由于是采樣統(tǒng)計，所以不管 N 是 20 還是 8，這個基數(shù)都是很容易不準(zhǔn)的。
我們可以使用 analyze table t 命令，可以用來重新統(tǒng)計索引信息。但是這條命令生產(chǎn)環(huán)境需要聯(lián)系 DBA，所以我就不做實驗了, 大家可以自行實驗。
索引要考慮 order by 的字段
為什么這么說? 因為如果我這個表中的索引是 city_id,type 和 id 的聯(lián)合索引，那優(yōu)化器就會走這個聯(lián)合索引，因為索引已經(jīng)做好了排序。
更改 limit 大小能解決問題?
把 limit 數(shù)量調(diào)大會影響預(yù)估行數(shù) rows，進(jìn)而影響優(yōu)化器索引的選擇嗎?
答案是會。
我們執(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，增長了 10 倍。如果使用 limit 100，會發(fā)生什么?
優(yōu)化器選擇了聯(lián)合索引。初步估計是 rows 還會翻倍，所以優(yōu)化器放棄了主鍵索引。寧愿用聯(lián)合索引后排序，也不愿意用主鍵索引了。
為何突然出現(xiàn)異常慢查詢
問：這個查詢語句已經(jīng)在線上穩(wěn)定運行了非常長的時間，為何這次突然出現(xiàn)了慢查詢?
答：以前的語句查詢條件返回結(jié)果都不為空，limit1 很快就能找到那條數(shù)據(jù)，返回結(jié)果。而這次代碼中查詢條件實際結(jié)果為空，導(dǎo)致了掃描了全部的主鍵索引。
解決方案
知道了 MySQL 為何選擇這個索引的原因后，我們就可以根據(jù)上面的思路來列舉出解決辦法了。
主要有兩個大方向：
鴻蒙官方戰(zhàn)略合作共建——HarmonyOS 技術(shù)社區(qū)
強(qiáng)制指定索引
干涉優(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)點是見效快，問題馬上就能解決。
缺點也很明顯：
高耦合，這種語句寫在代碼里，會變得難以維護(hù)，如果索引名變化了，或者沒有這個索引了，代碼就要反復(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這樣就會走上聯(lián)合索引，然后排序，但是這樣強(qiáng)行增長 limit，其實總有種面向黑盒調(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)合索引后還要排序，干脆就不太想走這個聯(lián)合索引了。
我們可以新建 city_id,type 和 id 的聯(lián)合索引，來解決這個問題。
這樣也有一定的弊端，比如我這個表到了 8000w 數(shù)據(jù)，建立索引非常耗時，而且通常索引就有 3.4 個 g，如果無限制的用索引解決問題，可能會帶來新的問題。表中的索引不宜過多。
干涉優(yōu)化器選擇：寫成子查詢
還有什么辦法? 我們可以用子查詢，在子查詢里先走 city_id 和 type 的聯(lián)合索引，得到結(jié)果集后在 limit1 選出第一條。
但是子查詢使用有風(fēng)險，一版 DBA 也不建議使用子查詢，會建議大家在代碼邏輯中完成復(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)化是個很大的工程，我們還有非常多的辦法能夠解決這句慢查詢問題，這里就不一一展開了。留給大家做為思考題了。
感謝各位的閱讀，以上就是“如何解決千萬級數(shù)據(jù)表選錯索引導(dǎo)致的線上慢查詢事故”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對如何解決千萬級數(shù)據(jù)表選錯索引導(dǎo)致的線上慢查詢事故這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是丸趣 TV，丸趣 TV 小編將為大家推送更多相關(guān)知識點的文章，歡迎關(guān)注！
