1 前言

近期隨著數據量的增長，數據庫 CPU 使用率 100% 報警頻繁起來。第一個想到的就是慢 Sql，我們對未合理運用索引的表加入索引后，問題依然沒有得到解決，深入排查時，發現在 order by id asc limit n 時，即使 where 條件已經包含了覆蓋索引，優化器還是選擇了錯誤的索引導致。通過查詢大量資料，問題得到了解決。這里將解決問題的思路以及排查過程分享出來，如果有錯誤歡迎指正。

2 正文

2.1 環境介紹

2.2 發現問題

22 日開始，收到以下圖 1 報警變得頻繁起來，由于數據庫中會有大數據推數動作，數據庫 CPU 偶爾報警并沒有引起對該問題的重視，直到通過圖 2 對整日監控數據分析時，才發現問題的嚴重性，從 0 點開始，數據庫 CPU 頻繁被打滿。



圖 1：報警圖



圖 2：整日 CPU 監控圖

2.3 排查問題

發現問題后，開始排查慢 Sql，發現很多查詢未添加合適的索引，經過一輪修復后，問題依然沒有得到解決，在深入排查時發現了一個奇怪現象，SQL 代碼如下（表名已經替換），比較簡單的一個單表查詢語句。

看似比較簡單的查詢，但執行時長平均在 90s 以上，并且調用頻次較高。如圖 3 所示。


圖 3：慢 Sql 平均執行時長 開始檢查表信息，可以看到表數據量在 2100w 左右。



圖 4：數據表情況 排查索引情況，主鍵為 id，并且有 business_day 與 full_ps_code 的聯合索引。

通過 Explain 查看執行計劃時發現，possible_keys 中包含上面的聯合索引，而 Key 卻選擇了 Primary 主鍵索引，掃描行數 Rows 為 1700w，幾乎等于全表掃描。 圖 5：執行計劃情況

2.4 解決問題

第一次，我們分析是，由于 Where 條件中包含了 ID，查詢分析器認為主鍵索引掃描行數會少，同時根據主鍵排序，使用主鍵索引會更加合理，我們試著添加以下索引，想要讓查詢分析器命中我們新加的索引。

ADD INDEX `idx_test`(`business_day`, `full_ps_code`, `id`) USING BTREE;

再次通過 Explain 語句進行分析，發現執行計劃完全沒變，還是走的主鍵索引。

圖 6：執行計劃情況 第二次，我們通過強制指定索引方式 force index (idx_test) 方式，再次分析執行情況，得到圖 7 的結果，同樣的查詢條件同樣的結果，查詢時長由 90s->0.49s 左右。問題得到解決



圖 7：強制指定索引后執行計劃情況



第三次，我們懷疑是 where 條件中有 ID 導致直接走的主鍵索引，where 條件中去掉 id，Sql 調整如下，然后進行分析。依然沒有命中索引，掃描 rows 變成 111342，查詢時間 96s

第四次，我們把 order by 去掉，SQL 調整如下，然后進行分析。命中了 idx_business_day_full_ps_code 之前建立的聯合索引。掃描行數變成 154900，查詢時長變為 0.062s，但是發現結果與預想的不一致，發生了亂序

第五次，經過前幾次的分析可以確定，order by 導致查詢分析器選擇了主鍵索引，我們在 Order by 中增加排序字段，將 Sql 調整如下，同樣可以命中我們之前的聯合索引，查詢時長為 0.034s，由于先按照主鍵排序，結果是一致的。相比第四種方法多了一份 filesort，問題得解決。

第六次，我們考慮是不是 Limit 導致的問題，我們將 Limit 500 調整到 1000，Sql 調整如下，奇跡發生了，命中了聯合索引，查詢時長為 0.316s，結果一致，只不過多返回來 500 條數據。問題得到了解決。經過多次實驗 Limit 大于 695 時就會命中聯合索引，查詢條件下的數據量是 79963，696/79963 大概占比是 0.0087，猜測當獲取數據比超過 0.0087 時，會選擇聯合索引，未找到源代碼驗證此結論。

經過我們的驗證，其中第 2、5、6 三種方法都可以解決性能問題。為了不影響線上，我們立即修改代碼，并選擇了 force index 的方式，上線觀察一段時間后，數據庫 CPU 恢復正常，問題得到了解決。

3 事后分析

上線后問題得到了解決，同時也留給我了很多疑問。

為什么明明 where 條件中包含了聯合索引，卻未能命中，反而選擇了性能較慢的主鍵索引？

為什么在 order by 中增加了一個索引其他字段，就可以命中聯合索引了呢？

為什么我僅僅是將 limit 限制條件由原來的 500 調大后，也能命中聯合索引呢？

這一切的答案都來自 MySQL 的查詢優化器。

3.1 查詢優化器

查詢優化器是專門負責優化查詢語句的優化器模塊，通過計算分析收集的各種系統統計信息，為查詢給出最優的執行計劃 —— 最優的數據檢索方式。 優化器決定如何執行查詢的方式是基于一種稱為基于代價的優化的方法。5.7 在代價類型上分為 IO、CPU、Memory。內存的代價收集了，但是并沒有參與最終的代價計算。Mysql 中引入了兩個系統表，mysql.server_cost 和 mysql.engine_cost，server_cost 對應 CPU 的代價，engine_cost 代表 IO 的代價。 server_cost（CPU 代價）

row_evaluate_cost (default 0.2) 計算符合條件的行的代價，行數越多，此項代價越大

memory_temptable_create_cost (default 2.0) 內存臨時表的創建代價

memory_temptable_row_cost (default 0.2) 內存臨時表的行代價

key_compare_cost (default 0.1) 鍵比較的代價，例如排序

disk_temptable_create_cost (default 40.0) 內部 myisam 或 innodb 臨時表的創建代價

disk_temptable_row_cost (default 1.0) 內部 myisam 或 innodb 臨時表的行代價

由上可以看出創建臨時表的代價是很高的，尤其是內部的 myisam 或 innodb 臨時表。 engine_cost（IO 代價）

io_block_read_cost (default 1.0) 從磁盤讀數據的代價，對 innodb 來說，表示從磁盤讀一個 page 的代價

memory_block_read_cost (default 1.0) 從內存讀數據的代價，對 innodb 來說，表示從 buffer pool 讀一個 page 的代價

這些信息都可以在數據庫中配置，當數據庫中未配置時，從 MySql 源代碼（5.7）中可以看到以上默認值情況

3.2 代價配置

3.3 代價計算

代價是如何算出來的呢，通過讀 MySql 的源代碼，可以找到最終的答案 3.3.1 全表掃描（table_scan_cost） 以下代碼摘自 MySql Server（5.7 分支），全表掃描時，IO 與 CPU 的代價計算方式。

根據源代碼分析，當表中包含 100 行數據時，全表掃描的成本為 23.1，計算邏輯如下

驗證結果如下圖



3.3.2 索引掃描（index_scan_cost） 以下代碼摘自 MySql Server（5.7 分支），當出現索引掃描時，是如何進行計算的，核心代碼如下

io 代價計算核心代碼

//核心代碼
const double io_cost= index_only_read_time(index, rows) *
table->cost_model()->page_read_cost_index(index, 1.0);

// index_only_read_time(index, rows)
// 估算index占page個數

//page_read_cost_index(index, 1.0)
//根據buffer pool大小和索引大小來估算page in memory和in disk的比例，計算讀一個page的代價

cpu 代價計算核心代碼

3.3.3 其他方式 計算代價的方式有很多，其他方式請參考 MySql 原代碼。https://github.com/mysql/mysql-server.git

3.4 深度解析

通過查看 optimizer_trace，可以了解查詢優化器是如何選擇的索引。

通過分析 rows_estimation 節點，可以看到通過全表掃描（table_scan）的話的代價是 8.29e6，同時也可以看到該查詢可以選擇到主鍵索引與聯合索引，如下圖。



上圖中全表掃描的代價是 8.29e6，我們轉換成普通計數法為 8290000，如果使用主鍵索引成本是 3530000，聯合索引 185881，最小的應該是 185881 聯合索引，也可以看到第一步通過成本分析確實選擇了我們的聯合索引。







但是為什么還是選擇了主鍵索引呢？ 通過往下看，在 reconsidering_access_paths_for_index_ordering 節點下， 發現由于 Order by 導致重新選擇了索引，在下圖中可以看到主鍵索引可用（usable=true），我們的聯合索引為 not_applicable （不適用），意味著排序只能使用主鍵索引。



接下來通過 index_order_summary 可以看出，執行計劃最終被調整，由原來的聯合索引改成了主鍵索引，就是說這個選擇無視了之前的基于索引成本的選擇。



為什么會有這樣的一個選項呢，主要原因如下：

The short explanation is that the optimizer thinks — or should I say hopes — that scanning the whole table (which is already sorted by the id field) will find the limited rows quick enough, and that this will avoid a sort operation. So by trying to avoid a sort, the optimizer ends-up losing time scanning the table.

從這段解釋可以看出主要原因是由于我們使用了 order by id asc 這種基于 id 的排序寫法，優化器認為排序是個昂貴的操作，所以為了避免排序，并且它認為 limit n 的 n 如果很小的話即使使用全表掃描也能很快執行完，所以它選擇了全表掃描，也就避免了 id 的排序。

5 總結

查詢優化器會基于代價來選擇最優的執行計劃，但由于 order by id limit n 的存在，MySql 可能會重新選擇一個錯誤的索引，忽略原有的基于代價選擇出來的索引，轉而選擇全表掃描的主鍵索引。這個問題在國內外有大量的用戶反饋，BUG 地址https://bugs.mysql.com/bug.php?id=97001。官方稱在 5.7.33 以后版本可以關閉 prefer_ordering_index 來解決。如下圖所示。



另外在我們日常慢 Sql 調優時，可以通過以下兩種方式，了解更多查詢優化器選擇過程。

當你也出現了本篇文章碰到的問題時，可以采用以下的方法來解決

使用 force index，強制指定索引。

order by 中增加一個聯合索引的 key。

擴大 limit 返回的范圍（不推薦，隨著數據量的增大，可能還會走回主鍵索引）

order by (id+0) asc 欺騙查詢優化器，讓其選擇聯合索引。

MySQL 5.7.33 版本以上，可以關閉 prefer_ordering_index 解決。

審核編輯：劉清