1 前言

近期隨著數(shù)據(jù)量的增長(zhǎng)，數(shù)據(jù)庫(kù) CPU 使用率 100% 報(bào)警頻繁起來(lái)。第一個(gè)想到的就是慢 Sql，我們對(duì)未合理運(yùn)用索引的表加入索引后，問(wèn)題依然沒(méi)有得到解決，深入排查時(shí)，發(fā)現(xiàn)在 order by id asc limit n 時(shí)，即使 where 條件已經(jīng)包含了覆蓋索引，優(yōu)化器還是選擇了錯(cuò)誤的索引導(dǎo)致。通過(guò)查詢大量資料，問(wèn)題得到了解決。這里將解決問(wèn)題的思路以及排查過(guò)程分享出來(lái)，如果有錯(cuò)誤歡迎指正。

2 正文

2.1 環(huán)境介紹

2.2 發(fā)現(xiàn)問(wèn)題

22 日開(kāi)始，收到以下圖 1 報(bào)警變得頻繁起來(lái)，由于數(shù)據(jù)庫(kù)中會(huì)有大數(shù)據(jù)推數(shù)動(dòng)作，數(shù)據(jù)庫(kù) CPU 偶爾報(bào)警并沒(méi)有引起對(duì)該問(wèn)題的重視，直到通過(guò)圖 2 對(duì)整日監(jiān)控?cái)?shù)據(jù)分析時(shí)，才發(fā)現(xiàn)問(wèn)題的嚴(yán)重性，從 0 點(diǎn)開(kāi)始，數(shù)據(jù)庫(kù) CPU 頻繁被打滿。



圖 1：報(bào)警圖



圖 2：整日 CPU 監(jiān)控圖

2.3 排查問(wèn)題

發(fā)現(xiàn)問(wèn)題后，開(kāi)始排查慢 Sql，發(fā)現(xiàn)很多查詢未添加合適的索引，經(jīng)過(guò)一輪修復(fù)后，問(wèn)題依然沒(méi)有得到解決，在深入排查時(shí)發(fā)現(xiàn)了一個(gè)奇怪現(xiàn)象，SQL 代碼如下（表名已經(jīng)替換），比較簡(jiǎn)單的一個(gè)單表查詢語(yǔ)句。

看似比較簡(jiǎn)單的查詢，但執(zhí)行時(shí)長(zhǎng)平均在 90s 以上，并且調(diào)用頻次較高。如圖 3 所示。


圖 3：慢 Sql 平均執(zhí)行時(shí)長(zhǎng) 開(kāi)始檢查表信息，可以看到表數(shù)據(jù)量在 2100w 左右。



圖 4：數(shù)據(jù)表情況 排查索引情況，主鍵為 id，并且有 business_day 與 full_ps_code 的聯(lián)合索引。

通過(guò) Explain 查看執(zhí)行計(jì)劃時(shí)發(fā)現(xiàn)，possible_keys 中包含上面的聯(lián)合索引，而 Key 卻選擇了 Primary 主鍵索引，掃描行數(shù) Rows 為 1700w，幾乎等于全表掃描。 圖 5：執(zhí)行計(jì)劃情況

2.4 解決問(wèn)題

第一次，我們分析是，由于 Where 條件中包含了 ID，查詢分析器認(rèn)為主鍵索引掃描行數(shù)會(huì)少，同時(shí)根據(jù)主鍵排序，使用主鍵索引會(huì)更加合理，我們?cè)囍砑右韵滤饕胍尣樵兎治銎髅形覀冃录拥乃饕?/p>

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

圖 6：執(zhí)行計(jì)劃情況 第二次，我們通過(guò)強(qiáng)制指定索引方式 force index (idx_test) 方式，再次分析執(zhí)行情況，得到圖 7 的結(jié)果，同樣的查詢條件同樣的結(jié)果，查詢時(shí)長(zhǎng)由 90s->0.49s 左右。問(wèn)題得到解決



圖 7：強(qiáng)制指定索引后執(zhí)行計(jì)劃情況



第三次，我們懷疑是 where 條件中有 ID 導(dǎo)致直接走的主鍵索引，where 條件中去掉 id，Sql 調(diào)整如下，然后進(jìn)行分析。依然沒(méi)有命中索引，掃描 rows 變成 111342，查詢時(shí)間 96s

第四次，我們把 order by 去掉，SQL 調(diào)整如下，然后進(jìn)行分析。命中了 idx_business_day_full_ps_code 之前建立的聯(lián)合索引。掃描行數(shù)變成 154900，查詢時(shí)長(zhǎng)變?yōu)?0.062s，但是發(fā)現(xiàn)結(jié)果與預(yù)想的不一致，發(fā)生了亂序

第五次，經(jīng)過(guò)前幾次的分析可以確定，order by 導(dǎo)致查詢分析器選擇了主鍵索引，我們?cè)?Order by 中增加排序字段，將 Sql 調(diào)整如下，同樣可以命中我們之前的聯(lián)合索引，查詢時(shí)長(zhǎng)為 0.034s，由于先按照主鍵排序，結(jié)果是一致的。相比第四種方法多了一份 filesort，問(wèn)題得解決。

第六次，我們考慮是不是 Limit 導(dǎo)致的問(wèn)題，我們將 Limit 500 調(diào)整到 1000，Sql 調(diào)整如下，奇跡發(fā)生了，命中了聯(lián)合索引，查詢時(shí)長(zhǎng)為 0.316s，結(jié)果一致，只不過(guò)多返回來(lái) 500 條數(shù)據(jù)。問(wèn)題得到了解決。經(jīng)過(guò)多次實(shí)驗(yàn) Limit 大于 695 時(shí)就會(huì)命中聯(lián)合索引，查詢條件下的數(shù)據(jù)量是 79963，696/79963 大概占比是 0.0087，猜測(cè)當(dāng)獲取數(shù)據(jù)比超過(guò) 0.0087 時(shí)，會(huì)選擇聯(lián)合索引，未找到源代碼驗(yàn)證此結(jié)論。

經(jīng)過(guò)我們的驗(yàn)證，其中第 2、5、6 三種方法都可以解決性能問(wèn)題。為了不影響線上，我們立即修改代碼，并選擇了 force index 的方式，上線觀察一段時(shí)間后，數(shù)據(jù)庫(kù) CPU 恢復(fù)正常，問(wèn)題得到了解決。

3 事后分析

上線后問(wèn)題得到了解決，同時(shí)也留給我了很多疑問(wèn)。

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

為什么在 order by 中增加了一個(gè)索引其他字段，就可以命中聯(lián)合索引了呢？

為什么我僅僅是將 limit 限制條件由原來(lái)的 500 調(diào)大后，也能命中聯(lián)合索引呢？

這一切的答案都來(lái)自 MySQL 的查詢優(yōu)化器。

3.1 查詢優(yōu)化器

查詢優(yōu)化器是專門負(fù)責(zé)優(yōu)化查詢語(yǔ)句的優(yōu)化器模塊，通過(guò)計(jì)算分析收集的各種系統(tǒng)統(tǒng)計(jì)信息，為查詢給出最優(yōu)的執(zhí)行計(jì)劃 —— 最優(yōu)的數(shù)據(jù)檢索方式。 優(yōu)化器決定如何執(zhí)行查詢的方式是基于一種稱為基于代價(jià)的優(yōu)化的方法。5.7 在代價(jià)類型上分為 IO、CPU、Memory。內(nèi)存的代價(jià)收集了，但是并沒(méi)有參與最終的代價(jià)計(jì)算。Mysql 中引入了兩個(gè)系統(tǒng)表，mysql.server_cost 和 mysql.engine_cost，server_cost 對(duì)應(yīng) CPU 的代價(jià)，engine_cost 代表 IO 的代價(jià)。 server_cost（CPU 代價(jià)）

row_evaluate_cost (default 0.2) 計(jì)算符合條件的行的代價(jià)，行數(shù)越多，此項(xiàng)代價(jià)越大

memory_temptable_create_cost (default 2.0) 內(nèi)存臨時(shí)表的創(chuàng)建代價(jià)

memory_temptable_row_cost (default 0.2) 內(nèi)存臨時(shí)表的行代價(jià)

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

disk_temptable_create_cost (default 40.0) 內(nèi)部 myisam 或 innodb 臨時(shí)表的創(chuàng)建代價(jià)

disk_temptable_row_cost (default 1.0) 內(nèi)部 myisam 或 innodb 臨時(shí)表的行代價(jià)

由上可以看出創(chuàng)建臨時(shí)表的代價(jià)是很高的，尤其是內(nèi)部的 myisam 或 innodb 臨時(shí)表。 engine_cost（IO 代價(jià)）

io_block_read_cost (default 1.0) 從磁盤讀數(shù)據(jù)的代價(jià)，對(duì) innodb 來(lái)說(shuō)，表示從磁盤讀一個(gè) page 的代價(jià)

memory_block_read_cost (default 1.0) 從內(nèi)存讀數(shù)據(jù)的代價(jià)，對(duì) innodb 來(lái)說(shuō)，表示從 buffer pool 讀一個(gè) page 的代價(jià)

這些信息都可以在數(shù)據(jù)庫(kù)中配置，當(dāng)數(shù)據(jù)庫(kù)中未配置時(shí)，從 MySql 源代碼（5.7）中可以看到以上默認(rèn)值情況

3.2 代價(jià)配置

3.3 代價(jià)計(jì)算

代價(jià)是如何算出來(lái)的呢，通過(guò)讀 MySql 的源代碼，可以找到最終的答案 3.3.1 全表掃描（table_scan_cost） 以下代碼摘自 MySql Server（5.7 分支），全表掃描時(shí)，IO 與 CPU 的代價(jià)計(jì)算方式。

根據(jù)源代碼分析，當(dāng)表中包含 100 行數(shù)據(jù)時(shí)，全表掃描的成本為 23.1，計(jì)算邏輯如下

驗(yàn)證結(jié)果如下圖



3.3.2 索引掃描（index_scan_cost） 以下代碼摘自 MySql Server（5.7 分支），當(dāng)出現(xiàn)索引掃描時(shí)，是如何進(jìn)行計(jì)算的，核心代碼如下

io 代價(jià)計(jì)算核心代碼

//核心代碼
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個(gè)數(shù)

//page_read_cost_index(index, 1.0)
//根據(jù)buffer pool大小和索引大小來(lái)估算page in memory和in disk的比例，計(jì)算讀一個(gè)page的代價(jià)

cpu 代價(jià)計(jì)算核心代碼

3.3.3 其他方式 計(jì)算代價(jià)的方式有很多，其他方式請(qǐng)參考 MySql 原代碼。https://github.com/mysql/mysql-server.git

3.4 深度解析

通過(guò)查看 optimizer_trace，可以了解查詢優(yōu)化器是如何選擇的索引。

通過(guò)分析 rows_estimation 節(jié)點(diǎn)，可以看到通過(guò)全表掃描（table_scan）的話的代價(jià)是 8.29e6，同時(shí)也可以看到該查詢可以選擇到主鍵索引與聯(lián)合索引，如下圖。



上圖中全表掃描的代價(jià)是 8.29e6，我們轉(zhuǎn)換成普通計(jì)數(shù)法為 8290000，如果使用主鍵索引成本是 3530000，聯(lián)合索引 185881，最小的應(yīng)該是 185881 聯(lián)合索引，也可以看到第一步通過(guò)成本分析確實(shí)選擇了我們的聯(lián)合索引。







但是為什么還是選擇了主鍵索引呢？ 通過(guò)往下看，在 reconsidering_access_paths_for_index_ordering 節(jié)點(diǎn)下， 發(fā)現(xiàn)由于 Order by 導(dǎo)致重新選擇了索引，在下圖中可以看到主鍵索引可用（usable=true），我們的聯(lián)合索引為 not_applicable （不適用），意味著排序只能使用主鍵索引。



接下來(lái)通過(guò) index_order_summary 可以看出，執(zhí)行計(jì)劃最終被調(diào)整，由原來(lái)的聯(lián)合索引改成了主鍵索引，就是說(shuō)這個(gè)選擇無(wú)視了之前的基于索引成本的選擇。



為什么會(huì)有這樣的一個(gè)選項(xiàng)呢，主要原因如下：

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 的排序?qū)懛ǎ瑑?yōu)化器認(rèn)為排序是個(gè)昂貴的操作，所以為了避免排序，并且它認(rèn)為 limit n 的 n 如果很小的話即使使用全表掃描也能很快執(zhí)行完，所以它選擇了全表掃描，也就避免了 id 的排序。

5 總結(jié)

查詢優(yōu)化器會(huì)基于代價(jià)來(lái)選擇最優(yōu)的執(zhí)行計(jì)劃，但由于 order by id limit n 的存在，MySql 可能會(huì)重新選擇一個(gè)錯(cuò)誤的索引，忽略原有的基于代價(jià)選擇出來(lái)的索引，轉(zhuǎn)而選擇全表掃描的主鍵索引。這個(gè)問(wèn)題在國(guó)內(nèi)外有大量的用戶反饋，BUG 地址https://bugs.mysql.com/bug.php?id=97001。官方稱在 5.7.33 以后版本可以關(guān)閉 prefer_ordering_index 來(lái)解決。如下圖所示。



另外在我們?nèi)粘Ｂ?Sql 調(diào)優(yōu)時(shí)，可以通過(guò)以下兩種方式，了解更多查詢優(yōu)化器選擇過(guò)程。

當(dāng)你也出現(xiàn)了本篇文章碰到的問(wèn)題時(shí)，可以采用以下的方法來(lái)解決

使用 force index，強(qiáng)制指定索引。

order by 中增加一個(gè)聯(lián)合索引的 key。

擴(kuò)大 limit 返回的范圍（不推薦，隨著數(shù)據(jù)量的增大，可能還會(huì)走回主鍵索引）

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

MySQL 5.7.33 版本以上，可以關(guān)閉 prefer_ordering_index 解決。

審核編輯：劉清