最近，又遇到了慢 SQL，簡單的看了下，又是因為 MySQL 本身優(yōu)化器還有查詢計劃估計不準的問題。SQL 如下：

select * from t_pay_record
WHERE
((
  user_id = 'user_id1' 
  AND is_del = 0 
)) 
ORDER BY
  id DESC 
  LIMIT 20

這個 SQL 執(zhí)行了 20 分鐘才有結(jié)果。但是我們換一個 user_id，執(zhí)行就很快。從線上業(yè)務(wù)表現(xiàn)來看，大部分用戶的表現(xiàn)都正常。我們又用一個數(shù)據(jù)分布與這個用戶相似的用戶去查，還是比較快。

我們先來 EXPLAIN 下這個原始 SQL，結(jié)果是：

+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys                                                                           | key     | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+---------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t_pay_record | NULL       | index | idx_user_id,idx_user_status_pay,idx_user_id_trade_code_status_amount_create_time_is_del | PRIMARY | 8       | NULL | 22593 |     0.01 | Using where |
+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+---------+---------+------+-------+----------+-------------+

然后我們換一些分布差不多的用戶但是響應時間正常的用戶，EXPLAIN 結(jié)果有的是：

+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+---------------------------------------------------------+---------+------+-------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys                                                                           | key                                                     | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+---------------------------------------------------------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t_pay_record | NULL       | index | idx_user_id,idx_user_status_pay,idx_user_id_trade_code_status_amount_create_time_is_del | idx_user_id_trade_code_status_amount_create_time_is_del | 195     | NULL | 107561|     10.00| Using where |
+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+---------------------------------------------------------+---------+------+-------+----------+-------------+

有的是：

+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+-------------+---------+------+-------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys                                                                           | key         | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+-------------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t_pay_record | NULL       | index | idx_user_id,idx_user_status_pay,idx_user_id_trade_code_status_amount_create_time_is_del | idx_user_id | 195     | NULL |  87514|     10.00| Using where |
+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+-------------+---------+------+-------+----------+-------------+

其實根據(jù)這個表現(xiàn)就可以推斷出，是走錯索引了。為啥會用錯索引呢?這個是因為多方面的原因?qū)е碌?，本篇文章將針對這個 SQL 來分析下這個多方面的原因，并給出最后的解決辦法。

對于 MySQL 慢 SQL 的分析

在之前的文章，我提到過 SQL 調(diào)優(yōu)一般通過下面三個工具：

1. EXPLAIN：這個是比較淺顯的分析，并不會真正執(zhí)行 SQL，分析出來的可能不夠準確詳細。但是能發(fā)現(xiàn)一些關(guān)鍵問題。
2. PROFILING： 通過 set profiling = 1 開啟的 SQL 執(zhí)行采樣?？梢苑治?SQL 執(zhí)行分為哪些階段，并且每階段的耗時如何。需要執(zhí)行并且執(zhí)行成功 SQL，并且分析出來的階段不夠詳細，一般只能通過某些階段是否存在如何避免這些階段的出現(xiàn)進行優(yōu)化(例如避免內(nèi)存排序的出現(xiàn)等等)。
3. OPTIMIZER TRACE：詳細展示優(yōu)化器的每一步，需要執(zhí)行并且執(zhí)行成功 SQL。MySQL 的優(yōu)化器由于考慮的因素太多，迭代太多，配置相當復雜，默認的配置在大部分情況沒問題，但是在某些特殊情況會有問題，需要我們進行人為干預。

這里再說一下在不同的 MySQL 版本， EXPLAIN 和 OPTIMIZER TRACE 結(jié)果可能不同，這是 MySQL 本身設(shè)計上的不足導致的，EXPLAIN 更貼近最后的執(zhí)行結(jié)果，OPTIMIZER TRACE 相當于在每一步埋點采集，在 MySQL 不斷迭代開發(fā)的時候，難免會有疏漏

對于上面這個 SQL，我們其實 EXPLAIN 就能知道它的原因是走錯索引了。但是不能直觀的看出來為啥會走錯索引，需要通過 OPTIMIZER TRACE 進行進一步定位。但是在進一步定位之前，我想先說一下 MySQL 的 InnoDB 查詢優(yōu)化器數(shù)據(jù)配置。

MySQL InnoDB 查詢優(yōu)化器數(shù)據(jù)配置(MySQL InnoDB Optimizer Statistics)

官網(wǎng)文檔地址：

https://dev.mysql.com/doc/refman/8.0/en/innodb-persistent-stats.html

為了優(yōu)化用戶的 SQL 查詢，MySQL 會對所有 SQL 查詢進行 SQL 解析、改寫和查詢計劃優(yōu)化。針對 InnoDB 引擎，制定查詢計劃的時候要分析：

1. 全表掃描消耗是多大
2. 走索引可以走哪些索引?會考慮 where 條件，以及 order 條件，通過里面的條件找有這些條件的索引
3. 每個索引的查詢消耗是多大
4. 選出消耗最小的那個查詢計劃并執(zhí)行

每個索引查詢消耗，需要通過 InnoDB 查詢優(yōu)化器數(shù)據(jù)。這個數(shù)據(jù)是通過采集表以及索引數(shù)據(jù)得出的，并且并不是全量采集，而是抽樣采集。與以下配置相關(guān)：

1. innodb_stats_persistent 全局變量控制全局默認的數(shù)據(jù)是否持久化，默認為 ON 即持久化，我們一般不會能接受在內(nèi)存中保存，這樣萬一數(shù)據(jù)庫重啟，表就要重新分析，這樣減慢啟動時間?？刂茊蝹€表的配置是 STATS_PERSISTENT(在 CREATE TABLE 以及 ALTER TABLE 中使用)。
2. innodb_stats_auto_recalc 全局變量全局默認是否自動更新，默認為 ON 即在表中有 10% 以上的行更新后觸發(fā)后臺異步更新采集數(shù)據(jù)，。控制單個表的配置是 STATS_AUTO_RECALC(在 CREATE TABLE 以及 ALTER TABLE 中使用)。
3. innodb_stats_persistent_sample_pages 全局變量控制全局默認的采集頁的數(shù)量，默認為 20. 即每次更新，隨機采集表以及表中的每個索引的 20 頁數(shù)據(jù)，用于估算每個索引的查詢消耗是多大以及全表掃描消耗是多大，控制單個表的配置是 STATS_SAMPLE_PAGES(在 CREATE TABLE 以及 ALTER TABLE 中使用)。

執(zhí)行時間最慢的 SQL 原因定位

通過之前的 EXPLAIN 的結(jié)果，我們知道最后的查詢用的索引是 PRIMARY 主鍵索引，這樣的話整個 SQL 的執(zhí)行過程就是：通過主鍵倒序遍歷表中的每一條數(shù)據(jù)，直到篩選出 20 條。通過執(zhí)行耗時我們知道，這個遍歷了很多數(shù)據(jù)才湊滿 20 條，效率極其低下。為啥會這樣呢?

通過 SQL 語句我們知道，在前面提到的第二步中，考慮的索引包括 where 條件中的 user_id，is_del 相關(guān)的索引(通過 EXPLAIN 我們知道有這些索引：idx_user_id,idx_user_status_pay,

idx_user_id_trade_code_status_amount_create_time_is_del)，以及 order by 條件中的 id 索引，也就是主鍵索引。假設(shè)本次隨機采集中采集的頁數(shù)據(jù)是這個樣子的：

圖中藍色的代表抽樣到的頁，同一個表內(nèi)每個索引都會抽樣默認 20 頁。假設(shè)本次采集的結(jié)果就是圖中所示，其他索引采集的比較均衡，通過其他索引判斷用戶都要掃描幾萬行的結(jié)果。但是主鍵采集的最后一頁，正好末尾全是這個用戶的記錄。由于語句最后有 limit 20，如果末尾正好有 20 條記錄(并且都符合 where 條件)，那么就會認為按照主鍵倒著找 20 條記錄就可以了。這樣就會造成優(yōu)化器認為走主鍵掃描消耗最少。但是實際上并不是這樣，因為這是采樣的，沒準后面有很多很多不是這個用戶的記錄，對大表尤其如此。

如果我們把 limit 去掉，EXPLAIN 就會發(fā)現(xiàn)索引走對了，因為不限制 limit，主鍵索引就要全部掃描一遍，消耗怎么也不可能比 user_id 相關(guān)的索引低了。

執(zhí)行時間正常的 SQL 為啥 user_id 不同也會走分析出走不同索引的原因

同樣的，由于所有索引的優(yōu)化器數(shù)據(jù)是隨機采樣的，隨著表的不斷變大以及索引的不斷膨脹，還有就是可能加更復雜的索引，這樣會加劇使用不同參數(shù)分析索引消耗的差異性(這里就是使用不同的 user_id)。

這也引出了一個新的可能大家也會遇到的問題，我在原有索引的基礎(chǔ)上，加了一個復合索引(舉個例子就是原來只有 idx_user_id，后來加了 idx_user_status_pay)，那么原來的只按照 user_id 去查數(shù)據(jù)的 SQL，有的可能會使用

idx_user_id，有的可能會使用 idx_user_status_pay，使用 idx_user_status_pay 大概率比使用 idx_user_id， 慢。所以，添加新的復合索引，可能會導致原來的不是這個復合索引要優(yōu)化的 SQL 的其他業(yè)務(wù) SQL 變慢，所以需要慎重添加

這種設(shè)計，在數(shù)據(jù)量不斷增大表越變越復雜的時候，會帶來哪些問題

1. 由于統(tǒng)計數(shù)據(jù)不是實時更新，而是更新的行數(shù)超過一定比例才會開始更新。并且統(tǒng)計數(shù)據(jù)不是全量統(tǒng)計，是抽樣統(tǒng)計。所以在表的數(shù)據(jù)量很大的時候，這個統(tǒng)計數(shù)據(jù)很難非常準確。
2. 由于統(tǒng)計數(shù)據(jù)本來就不夠準確，表設(shè)計如果也比較復雜，存儲的數(shù)據(jù)類型比較多，字段也很多，并且最關(guān)鍵的是有各種復合索引，索引也越來越復雜，這樣更加加劇了這個統(tǒng)計數(shù)據(jù)的不準確性。
3. 順便說一下：MySQL 表數(shù)據(jù)量不能很大，需要做好水平拆分，同時字段不能太多，所以需要做好垂直拆分。并且索引不能隨便加，想加多少加多少，也有以上說的這兩個原因，這樣會加劇統(tǒng)計數(shù)據(jù)的不準確性，導致用錯索引。
4. 手動 Analyze Table，會在表上加讀鎖，會阻塞表上的更新以及事務(wù)。所以不能在這種在線業(yè)務(wù)關(guān)鍵表上面使用?？梢钥紤]在業(yè)務(wù)低峰的時候，定時 Analyze 業(yè)務(wù)關(guān)鍵 Table
5. 依靠表本身自動刷新數(shù)據(jù)機制，參數(shù)比較難以調(diào)整(主要是 STATS_SAMPLE_PAGES 這個參數(shù)，STATS_PERSISTENT 我們一般不會改，我們不會能接受在內(nèi)存中保存，這樣萬一數(shù)據(jù)庫重啟，表就要重新分析，這樣減慢啟動時間，STATS_AUTO_RECALC 我們也不會關(guān)閉，這樣會導致優(yōu)化器分析的越來越不準確)，很難預測出到底調(diào)整到什么數(shù)值最合適。并且業(yè)務(wù)的增長，用戶的行為導致的數(shù)據(jù)的傾斜，也是很難預測的。通過 Alter Table 修改某個表的 STATS_SAMPLE_PAGES 的時候，會導致和 Analyze 這個 Table 一樣的效果，會在表上加讀鎖，會阻塞表上的更新以及事務(wù)。所以不能在這種在線業(yè)務(wù)關(guān)鍵表上面使用。所以最好一開始就能估計出大表的量級，但是這個很難。

結(jié)論和建議

綜上所述，我建議線上對于數(shù)據(jù)量比較大的表，最好能提前通過分庫分表控制每個表的數(shù)據(jù)量，但是業(yè)務(wù)增長與產(chǎn)品需求都是不斷在迭代并且變復雜的。很難保證不會出現(xiàn)大并且索引比較復雜的表。這種情況下需要我們，在適當調(diào)高 STATS_SAMPLE_PAGES 的前提下，對于一些用戶觸發(fā)的關(guān)鍵查詢 SQL，使用 force index 引導它走正確的索引，這樣就不會出現(xiàn)本文中說的因為 MySQL 優(yōu)化器表采集數(shù)據(jù)的不準確導致的某些用戶 id 查詢走錯索引的情況。