最近，又遇到了慢 SQL，簡單的看了下，又是因為 MySQL 本身優化器還有查詢計劃估計不準的問題。SQL 如下：
select * from t_pay_record
WHERE
((
user_id = 'user_id1'
AND is_del = 0
))
ORDER BY
id DESC
LIMIT 20
這個 SQL 執行了 20 分鐘才有結果。但是我們換一個 user_id，執行就很快。從線上業務表現來看，大部分使用者的表現都正常。我們又用一個資料分佈與這個使用者相似的使用者去查，還是比較快。
我們先來 EXPLAIN 下這個原始 SQL，結果是：
+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+---------+---------+------+-------+----------+-------------+
| 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 結果有的是：
+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+---------------------------------------------------------+---------+------+-------+----------+-------------+
| 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 |
+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+-------------+---------+------+-------+----------+-------------+
其實根據這個表現就可以推斷出，是走錯索引了。為啥會用錯索引呢？這個是因為多方面的原因導致的，本篇文章將針對這個 SQL 來分析下這個多方面的原因，並給出最後的解決辦法。
對於 MySQL 慢 SQL 的分析
在之前的文章，我提到過 SQL 調優一般通過下面三個工具：
- EXPLAIN：這個是比較淺顯的分析，並不會真正執行 SQL，分析出來的可能不夠準確詳細。但是能發現一些關鍵問題。
- PROFILING： 通過
set profiling = 1開啟的 SQL 執行取樣。可以分析 SQL 執行分為哪些階段，並且每階段的耗時如何。需要執行並且執行成功 SQL，並且分析出來的階段不夠詳細，一般只能通過某些階段是否存在如何避免這些階段的出現進行優化（例如避免記憶體排序的出現等等）。 - OPTIMIZER TRACE：詳細展示優化器的每一步，需要執行並且執行成功 SQL。MySQL 的優化器由於考慮的因素太多，迭代太多，配置相當複雜，預設的配置在大部分情況沒問題，但是在某些特殊情況會有問題，需要我們進行人為干預。
這裡再說一下在不同的 MySQL 版本， EXPLAIN 和 OPTIMIZER TRACE 結果可能不同，這是 MySQL 本身設計上的不足導致的，EXPLAIN 更貼近最後的執行結果，OPTIMIZER TRACE 相當於在每一步埋點採集，在 MySQL 不斷迭代開發的時候，難免會有疏漏
對於上面這個 SQL，我們其實 EXPLAIN 就能知道它的原因是走錯索引了。但是不能直觀的看出來為啥會走錯索引，需要通過 OPTIMIZER TRACE 進行進一步定位。但是在進一步定位之前，我想先說一下 MySQL 的 InnoDB 查詢優化器資料配置。
MySQL InnoDB 查詢優化器資料配置（MySQL InnoDB Optimizer Statistics）
官網文件地址：https://dev.mysql.com/doc/refman/8.0/en/innodb-persistent-stats.html
為了優化使用者的 SQL 查詢，MySQL 會對所有 SQL 查詢進行 SQL 解析、改寫和查詢計劃優化。針對 InnoDB 引擎，制定查詢計劃的時候要分析：
- 全表掃描消耗是多大
- 走索引可以走哪些索引？會考慮 where 條件，以及 order 條件，通過裡面的條件找有這些條件的索引
- 每個索引的查詢消耗是多大
- 選出消耗最小的那個查詢計劃並執行
每個索引查詢消耗，需要通過 InnoDB 查詢優化器資料。這個資料是通過採集表以及索引資料得出的，並且並不是全量採集，而是抽樣採集。與以下配置相關：
innodb_stats_persistent全域性變數控制全域性預設的資料是否持久化，預設為 ON 即持久化，我們一般不會能接受在記憶體中儲存，這樣萬一資料庫重啟，表就要重新分析，這樣減慢啟動時間。控制單個表的配置是STATS_PERSISTENT（在CREATE TABLE以及ALTER TABLE中使用）。innodb_stats_auto_recalc全域性變數全域性預設是否自動更新，預設為 ON 即在表中有 10% 以上的行更新後觸發後臺非同步更新採集資料，。控制單個表的配置是STATS_AUTO_RECALC（在CREATE TABLE以及ALTER TABLE中使用）。innodb_stats_persistent_sample_pages全域性變數控制全域性預設的採集頁的數量，預設為 20. 即每次更新，隨機採集表以及表中的每個索引的 20 頁資料，用於估算每個索引的查詢消耗是多大以及全表掃描消耗是多大，控制單個表的配置是STATS_SAMPLE_PAGES（在CREATE TABLE以及ALTER TABLE中使用）。
執行時間最慢的 SQL 原因定位
通過之前的 EXPLAIN 的結果，我們知道最後的查詢用的索引是 PRIMARY 主鍵索引，這樣的話整個 SQL 的執行過程就是：通過主鍵倒序遍歷表中的每一條資料，直到篩選出 20 條。通過執行耗時我們知道，這個遍歷了很多資料才湊滿 20 條，效率極其低下。為啥會這樣呢？
通過 SQL 語句我們知道，在前面提到的第二步中，考慮的索引包括 where 條件中的 user_id，is_del 相關的索引（通過 EXPLAIN 我們知道有這些索引：idx_user_id,idx_user_status_pay,idx_user_id_trade_code_status_amount_create_time_is_del），以及 order by 條件中的 id 索引，也就是主鍵索引。假設本次隨機採集中採集的頁資料是這個樣子的：
圖中藍色的代表抽樣到的頁，同一個表內每個索引都會抽樣預設 20 頁。假設本次採集的結果就是圖中所示，其他索引採集的比較均衡，通過其他索引判斷使用者都要掃描幾萬行的結果。但是主鍵採集的最後一頁，正好末尾全是這個使用者的記錄。由於語句最後有 limit 20，如果末尾正好有 20 條記錄（並且都符合 where 條件），那麼就會認為按照主鍵倒著找 20 條記錄就可以了。這樣就會造成優化器認為走主鍵掃描消耗最少。但是實際上並不是這樣，因為這是取樣的，沒準後面有很多很多不是這個使用者的記錄，對大表尤其如此。
如果我們把 limit 去掉，EXPLAIN 就會發現索引走對了，因為不限制 limit，主鍵索引就要全部掃描一遍，消耗怎麼也不可能比 user_id 相關的索引低了。
執行時間正常的 SQL 為啥 user_id 不同也會走分析出走不同索引的原因
同樣的，由於所有索引的優化器資料是隨機取樣的，隨著表的不斷變大以及索引的不斷膨脹，還有就是可能加更復雜的索引，這樣會加劇使用不同引數分析索引消耗的差異性（這裡就是使用不同的 user_id）。
這也引出了一個新的可能大家也會遇到的問題，我在原有索引的基礎上，加了一個複合索引（舉個例子就是原來只有 idx_user_id，後來加了 idx_user_status_pay），那麼原來的只按照 user_id 去查資料的 SQL，有的可能會使用
idx_user_id，有的可能會使用 idx_user_status_pay，使用 idx_user_status_pay 大概率比使用 idx_user_id， 慢。所以，新增新的複合索引，可能會導致原來的不是這個複合索引要優化的 SQL 的其他業務 SQL 變慢，所以需要慎重新增
這種設計，在資料量不斷增大表越變越複雜的時候，會帶來哪些問題
- 由於統計資料不是實時更新，而是更新的行數超過一定比例才會開始更新。並且統計資料不是全量統計，是抽樣統計。所以在表的資料量很大的時候，這個統計資料很難非常準確。
- 由於統計資料本來就不夠準確，表設計如果也比較複雜，儲存的資料型別比較多，欄位也很多，並且最關鍵的是有各種複合索引，索引也越來越複雜，這樣更加加劇了這個統計資料的不準確性。
- 順便說一下：MySQL 表資料量不能很大，需要做好水平拆分，同時欄位不能太多，所以需要做好垂直拆分。並且索引不能隨便加，想加多少加多少，也有以上說的這兩個原因，這樣會加劇統計資料的不準確性，導致用錯索引。
- 手動 Analyze Table，會在表上加讀鎖，會阻塞表上的更新以及事務。所以不能在這種線上業務關鍵表上面使用。可以考慮在業務低峰的時候，定時 Analyze 業務關鍵 Table
- 依靠表本身自動重新整理資料機制，引數比較難以調整（主要是
STATS_SAMPLE_PAGES這個引數，STATS_PERSISTENT我們一般不會改，我們不會能接受在記憶體中儲存，這樣萬一資料庫重啟，表就要重新分析，這樣減慢啟動時間，STATS_AUTO_RECALC我們也不會關閉，這樣會導致優化器分析的越來越不準確），很難預測出到底調整到什麼數值最合適。並且業務的增長，使用者的行為導致的資料的傾斜，也是很難預測的。通過 Alter Table 修改某個表的STATS_SAMPLE_PAGES的時候，會導致和 Analyze 這個 Table 一樣的效果，會在表上加讀鎖，會阻塞表上的更新以及事務。所以不能在這種線上業務關鍵表上面使用。所以最好一開始就能估計出大表的量級，但是這個很難。
結論和建議
綜上所述，我建議線上對於資料量比較大的表，最好能提前通過分庫分表控制每個表的資料量，但是業務增長與產品需求都是不斷在迭代並且變複雜的。很難保證不會出現大並且索引比較複雜的表。這種情況下需要我們，在適當調高 STATS_SAMPLE_PAGES 的前提下，對於一些使用者觸發的關鍵查詢 SQL，使用 force index 引導它走正確的索引，這樣就不會出現本文中說的因為 MySQL 優化器表採集資料的不準確導致的某些使用者 id 查詢走錯索引的情況。
微信搜尋“我的程式設計喵”關注公眾號，加作者微信，每日一刷，輕鬆提升技術，斬獲各種offer：
我會經常發一些很好的各種框架的官方社群的新聞視訊資料並加上個人翻譯字幕到如下地址（也包括上面的公眾號），歡迎關注：
