MySQL選錯索引導致的線上慢查詢事故怎么辦

小編給大家分享一下MySQL選錯索引導致的線上慢查詢事故怎么辦，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

正文

故障描述

在7月24日11點線上某數(shù)據(jù)庫突然收到大量告警，慢查詢數(shù)超標，并且引發(fā)了連接數(shù)暴增，導致數(shù)據(jù)庫響應緩慢，影響業(yè)務。看圖表慢查詢在高峰達到了每分鐘14w次，在平時正常情況下慢查詢數(shù)僅在兩位數(shù)以下，如下圖：

趕緊查看慢SQL記錄，發(fā)現(xiàn)都是同一類語句導致的慢查詢(隱私數(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í)行的查詢時間達到了驚人的44s。

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

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

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

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

問題原因排查

首先當然要懷疑會不會該語句沒走索引，查看建表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兩個索引的重復，這都是歷史遺留問題了。

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

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

既然有索引，接下來就該看該語句實際有沒有走到索引了，MySQL提供了Explain可以分析SQL語句。Explain 用來分析 SELECT 查詢語句。

Explain比較重要的字段有：

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

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

我們使用Explain分析該語句：

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

得到結果：

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

我們可以使用force index(idx_city_id_type)讓該語句選擇我們設置的聯(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)合索引后，不再是慢查詢了。

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

MySQL索引選擇原理

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

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

引用參考文獻一段解釋：

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

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

當然，掃描行數(shù)并不是唯一的判斷標準，優(yōu)化器還會結合是否使用臨時表、是否排序等因素進行綜合判斷。

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

我們回頭看剛才的兩個explain截圖：

走了主鍵索引的查詢語句，rows預估行數(shù)1833，而強制走聯(lián)合索引行數(shù)是45640，并且Extra信息中，顯示需要Using filesort進行額外的排序。所以在不加強制索引的情況下，優(yōu)化器選擇了主鍵索引，因為它覺得主鍵索引掃描行數(shù)少，而且不需要額外的排序操作，主鍵索引天生有序。

rows是怎么預估出來的

同學們就要問了，為什么rows只有1833，明明實際掃描了整個主鍵索引啊,行數(shù)遠遠不止幾千行。實際上explain的rows是MySQL預估的行數(shù)，是根據(jù)查詢條件、索引和limit綜合考慮出來的預估行數(shù)。

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

為什么要采樣統(tǒng)計呢？因為把整張表取出來一行行統(tǒng)計，雖然可以得到精確的結果，但是代價太高了，所以只能選擇“采樣統(tǒng)計”。

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

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

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

設置為on的時候，表示統(tǒng)計信息會持久化存儲。這時，默認的N是20，M是10。
設置為off的時候，表示統(tǒng)計信息只存儲在內(nèi)存中。這時，默認的N是8，M是16。
由于是采樣統(tǒng)計，所以不管N是20還是8，這個基數(shù)都是很容易不準的。復制代碼

我們可以使用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)大會影響預估行數(shù)rows，進而影響優(yōu)化器索引的選擇嗎？

答案是會。

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

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

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

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

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

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

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

解決方案

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

主要有兩個大方向：

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

強制選擇索引：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)點是見效快，問題馬上就能解決。

缺點也很明顯：

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

我們換一種辦法，我們?nèi)ヒ龑?yōu)化器選擇聯(lián)合索引。

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

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

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

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

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

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

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

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

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

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

但是子查詢使用有風險，一版DBA也不建議使用子查詢，會建議大家在代碼邏輯中完成復雜的查詢。當然我們這句并不復雜啦~

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)化是個很大的工程，我們還有非常多的辦法能夠解決這句慢查詢問題，這里就不一一展開了。留給大家做為思考題了。

總結

本文帶大家回顧了一次MySQL優(yōu)化器選錯索引導致的線上慢查詢事故，可以看出MySQL優(yōu)化器對于索引的選擇并不單單依靠某一個標準，而是一個綜合選擇的結果。我自己也對這方面了解不深入，還需要多多學習，爭取能夠好好的做一個索引選擇的總結（挖坑）。不說了，拿起巨厚的《高性能MySQL》,開始...

壓住我的泡面...

最后做個文章總結：

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

以上是MySQL選錯索引導致的線上慢查詢事故怎么辦的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業(yè)資訊頻道！