[[379919]]

圖片來自 Pexels 

經過排查后，確定原因是：SQL 在執行時，MySQL 優化器選擇了錯誤的索引(不應該說是“錯誤”，而是選擇了實際執行耗時更長的索引)。

排查過程中，查閱了許多資料，也學習了下 MySQL 優化器選擇索引的基本準則，在本文中進行解決問題思路的分享。

PS：本人 MySQL 了解深度有限，如有錯誤歡迎在評論區理性討論和指正。

在這次事故中也能充分看出深入了解 MySQL 運行原理的重要性，這是遇到問題時能否獨立解決問題的關鍵。

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

本文的主要內容：

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

故障描述

在去年 7 月 24 日 11 點線上某數據庫突然收到大量告警，慢查詢數超標，并且引發了連接數暴增，導致數據庫響應緩慢，影響業務。

看圖表慢查詢在高峰達到了每分鐘 14w 次，在平時正常情況下慢查詢數僅在兩位數以下，如下圖：

 

趕緊查看慢 SQL 記錄，發現都是同一類語句導致的慢查詢(隱私數據例如表名，我已經隱去)：

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

看起來語句很簡單，沒什么特別的，但是每個執行的查詢時間達到了驚人的 44s。

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

 

接下來查看表數據信息，如下圖：

 

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

大致情況就是這樣，下面進入排查問題的環節。

問題原因排查

首先當然要懷疑會不會該語句沒走索引，查看建表 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：查詢類型，有簡單查詢、聯合查詢、子查詢等。
• key：使用的索引。
• rows：預計需要掃描的行數。

我們使用 Explain 分析該語句：

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

得到結果：

 

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

我們可以使用 force index(idx_city_id_type) 讓該語句選擇我們設置的聯合索引：

 

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 

這次明顯執行得飛快，分析語句：

實際執行時間 0.00175714s，走了聯合索引后，不再是慢查詢了。

 

問題找到了，總結下來就是：MySQL 優化器認為在 limit 1 的情況下，走主鍵索引能夠更快的找到那一條數據，并且如果走聯合索引需要掃描索引后進行排序，而主鍵索引天生有序，所以優化器綜合考慮，走了主鍵索引。

實際上，MySQL 遍歷了 8000w 條數據也沒找到那個天選之人(符合條件的數據)，所以浪費了很多時間。

MySQL 索引選擇原理

①優化器索引選擇的準則

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

引用參考文獻一段解釋：首先要知道，選擇索引是 MySQL 優化器的工作。 

而優化器選擇索引的目的，是找到一個最優的執行方案，并用最小的代價去執行語句。

在數據庫里面，掃描行數是影響執行代價的因素之一。掃描的行數越少，意味著訪問磁盤數據的次數越少，消耗的 CPU 資源越少。

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

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

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

 

走了主鍵索引的查詢語句，rows 預估行數 1833，而強制走聯合索引行數是 45640，并且 Extra 信息中，顯示需要 Using filesort 進行額外的排序。

所以在不加強制索引的情況下，優化器選擇了主鍵索引，因為它覺得主鍵索引掃描行數少，而且不需要額外的排序操作，主鍵索引天生有序。

②rows 是怎么預估出來的

同學們就要問了，為什么 rows 只有 1833，明明實際掃描了整個主鍵索引啊,行數遠遠不止幾千行。

實際上 explain 的 rows 是 MySQL 預估的行數，是根據查詢條件、索引和 limit 綜合考慮出來的預估行數。

MySQL 是怎樣得到索引的基數的呢?這里，我給你簡單介紹一下 MySQL 采樣統計的方法。

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

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

而數據表是會持續更新的，索引統計信息也不會固定不變。所以，當變更的數據行數超過 1/M 的時候，會自動觸發重新做一次索引統計。

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

設置為 on 的時候，表示統計信息會持久化存儲。這時，默認的 N 是 20，M 是 10。

設置為 off 的時候，表示統計信息只存儲在內存中。這時，默認的 N 是 8，M 是 16。

由于是采樣統計，所以不管 N 是 20 還是 8，這個基數都是很容易不準的。

我們可以使用 analyze table t 命令，可以用來重新統計索引信息。但是這條命令生產環境需要聯系 DBA，所以我就不做實驗了，大家可以自行實驗。

③索引要考慮 order by 的字段

為什么這么說?因為如果我這個表中的索引是 city_id，type 和 id 的聯合索引，那優化器就會走這個聯合索引，因為索引已經做好了排序。

④更改 limit 大小能解決問題?

把 limit 數量調大會影響預估行數 rows，進而影響優化器索引的選擇嗎?

答案是會。

我們執行 limit 10：

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

 

 

圖中 rows 變為了 18211，增長了 10 倍。如果使用 limit 100，會發生什么?

 

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

⑤為何突然出現異常慢查詢

Q：這個查詢語句已經在線上穩定運行了非常長的時間，為何這次突然出現了慢查詢?

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

解決方案

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

主要有兩個大方向：

• 強制指定索引
• 干涉優化器選擇

①強制選擇索引：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 

這樣做的優點是見效快，問題馬上就能解決。

缺點也很明顯：

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

我們換一種辦法，去引導優化器選擇聯合索引。

②干涉優化器選擇：增大 limit

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

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

這樣就會走上聯合索引，然后排序，但是這樣強行增長 limit，其實總有種面向黑盒調參的感覺。我們還有更優美的解決方案嗎?

③干涉優化器選擇：增加包含 order by id 字段的聯合索引

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

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

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

④干涉優化器選擇：寫成子查詢

還有什么辦法?我們可以用子查詢，在子查詢里先走 city_id 和 type 的聯合索引，得到結果集后在 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 優化是個很大的工程，我們還有非常多的辦法能夠解決這句慢查詢問題，這里就不一一展開了。

總結

本文帶大家回顧了一次 MySQL 優化器選錯索引導致的線上慢查詢事故，可以看出 MySQL 優化器對于索引的選擇并不單單依靠某一個標準，而是一個綜合選擇的結果。

我自己也對這方面了解不深入，還需要多多學習，爭取能夠好好的做一個索引選擇的總結(挖坑)。不說了，拿起巨厚的《高性能MySQL》，開始壓住我的泡面...

最后做個文章總結：

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

作者：蠻三刀把刀

編輯：陶家龍

出處：轉載自公眾號后端技術漫談(ID：Rude3Knife)