分布式任務調度內的 MySQL 分頁查詢優化
作者：Qiu Xinbo
本文主要通過圖示介紹了用主鍵進行分片查詢的過程，介紹了主鍵分頁查詢存在SQL性能問題，如何去創建高效的索引去優化主鍵分頁查詢的SQL性能問題。對于數據分布不均如何發現，提供了一些SQL查詢案例來進行參考，對MySQL Index Condition Pushdown優化算法做了一些簡單介紹。
一、背景介紹
最近在線上環境發現了一條執行較慢的分頁查詢，高并發執行，產生了大量的慢查詢日志，CPU使用率逐步升高。
通過觀察它的執行時間，發現該SQL查詢時快時慢，執行時間并不穩定，以至于在高并發執行場景時，數據庫來不及響應，數據庫服務變慢。
二、分析定位
2.1 定位 SQL 執行變慢的原因
通過數據庫管理平臺查看SQL執行信息發現，SQL解析行數（掃描行數）和SQL執行時間都很不穩定，執行時長和解析行數（掃描行數）是成正比的。
這個也能解釋的通為什么SQL執行時長變了，因為掃描行數變多了，SQL執行時間成比例增長。
-- SQL全文
select
id,
uuid,
name,
user_type,
is_deleted,
modify_date
from
test_user
where
is_deleted=0
and user_type=0
and id > 10000
and id % 10 = 9
order by
id limit 500;
2.2 了解 SQL 的業務背景
通過與研發溝通發現，該SQL原來是串行執行，單個線程在跑，后來覺得比較慢，改為分布式任務并行執行，通過id取模0-9，調度10個線程，每個線程處理1個分區，這樣就有10個并發相當于把數據做了切片，并發查詢并發處理，由此帶來數據庫端的并發升高。從技術角度上看，提高數據處理速度，給數據做切片，改單線程為并發處理，并沒有任何問題，反而是一種比較好的優化方案，但是高并發執行的SQL都是要有一個前提，SQL執行效率要特別高，否則會導致數據庫端物理機資源耗盡，數據庫服務來不及響應。
2.3 定位 SQL 掃描行數變化的原因
2.3.1 慢 SQL 及表結構信息
-- 為了方便理解和說明，新建一個test_user表，造了一些模擬數據，將SQL做了一些簡化，不影響整體的分析效果
-- SQL全文
select
id,
uuid,
name,
user_type,
is_deleted,
modify_date
from
test_user
where
is_deleted=0
and user_type=0
and id > 10000
and id % 10 = 9
order by
id limit 500;
-- 表信息
CREATE TABLE `test_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
`uuid` varchar(64) NOT NULL COMMENT '用戶ID',
`name` varchar(20) DEFAULT '' COMMENT '用戶名',
`user_type` tinyint(4) NOT NULL DEFAULT '0',
`is_deleted` tinyint(4) NOT NULL DEFAULT '0',
`modify_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時間',
`create_date` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_uuid` (`uuid`),
KEY `idx_modifydate` (`modify_date`)
) ENGINE=InnoDB AUTO_INCREMENT=7986024 DEFAULT CHARSET=utf8mb42.3.2 查看 SQL 執行計劃
通過查看SQL執行計劃，發現執行計劃走主鍵索引掃描，以下是SQL執行計劃的關鍵信息解讀：
- type=range 范圍掃描
- key = primary 使用主鍵索引
- rows = 877w 預估的掃描行數
- filter = 1.00 百分比，滿足過濾條件返回的行數 = rows * filter
mysql> explain select
-> id,
-> uuid,
-> name,
-> user_type,
-> is_deleted,
-> modify_date
-> from
-> test_user
-> where
-> is_deleted=0
-> and user_type=9
-> and id > 10000
-> and id % 10 = 9
-> order by
-> id limit 500;
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | test_user | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 8775507 | 1.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)2.3.3 圖示 SQL 執行過程
通過簡單的圖示，描述下SQL掃描過程，由于是通過主鍵索引遍歷，避免了額外的排序行為，從最小id開始取到最大id。
mysql> select min(id),max(id) from test_user;
+---------+----------+
| min(id) | max(id) |
+---------+----------+
| 3 | 17889149 |
+---------+----------+
1 row in set (0.00 sec)
2.3.4 計算數據分布
從SQL過濾條件看只有is_deleted、user_type、id這三個，能預估到is_deleted和user_type區分度不高，通過SQL查看下數據的分布。
mysql> select is_deleted,user_type,count(*) from test_user group by is_deleted,user_type order by count(*) desc limit 1,10;
+------------+-----------+----------+
| is_deleted | user_type | count(*) |
+------------+-----------+----------+
| 1 | 1 | 4473019 |
| 1 | 0 | 4471648 |
| 0 | 0 | 4470140 |
| 0 | 2 | 999 |
+------------+-----------+----------+
4 rows in set (4.81 sec)
-- 從數據分布來看user_type等于2的數據較少，只有999條，其他相對比較均勻數據分布驗證測試
將上述4種結果（is_deleted和user_type）分別通過SQL查看最近1000條滿足條件的數據的id區間，驗證數據的分布。
- is_deleted=1、user_type=1
- is_deleted=1、user_type=0
- is_deleted=0、user_type=0
-- 最近1000條is_deleted=1、user_type=1的數據記錄分布在id 6-3876,大約掃描3871條數據,能返回500條滿足條件的值,數據分布均勻.
mysql> select max(id),min(id) from( select id from test_user where is_deleted=1 and user_type=1 order by id limit 1000) a;
+---------+---------+
| max(id) | min(id) |
+---------+---------+
| 3876 | 6 |
+---------+---------+
1 row in set (0.00 sec)
-- 最近1000條is_deleted=1、user_type=0的數據記錄分布在id 3-4019,大約掃描4016條數據,能返回500條滿足條件的值,數據分布均勻.
mysql> select max(id),min(id) from( select id from test_user where is_deleted=1 and user_type=0 order by id limit 1000) a;
+---------+---------+
| max(id) | min(id) |
+---------+---------+
| 4019 | 3 |
+---------+---------+
1 row in set (0.00 sec)
-- 最近1000條is_deleted=0、user_type=0的數據記錄分布在id 5-4020,大約掃描4015條數據,能返回500條滿足條件的值,數據分布均勻.
mysql> select max(id),min(id) from( select id from test_user where is_deleted=0 and user_type=0 order by id limit 1000) a;
+---------+---------+
| max(id) | min(id) |
+---------+---------+
| 4025 | 5 |
+---------+---------+
1 row in set (0.00 sec)
is_deleted=0、user_type=2
-- 最近1000條is_deleted=0、user_type=2的數據記錄分布在id 17890648-17891147，是比較緊湊的，但是由于id比較大，整體排在較后的位置。
-- 如果按照主鍵遍歷，需要遍歷完前面的1700w條不符合條件數據，才能遍歷到滿足條件的數據。
mysql> select max(id),min(id) from( select id from test_user where is_deleted=0 and user_type=2 order by id limit 1000) a;
+----------+----------+
| max(id) | min(id) |
+----------+----------+
| 17891147 | 17890149 |
+----------+----------+
1 row in set (0.00 sec)
2.3.5 實際執行測試
重要字段信息說明：
- Query_time：SQL執行時間
- Rows_examined：SQL掃描行數
- Rows_sent：SQL返回行數
# Query_time: 0.009549 Lock_time: 0.000074 Rows_sent: 500 Rows_examined: 20537
SET timestamp=1695711745;
select id,uuid,name,user_type,is_deleted,modify_date from test_user where is_deleted=1 and user_type=0 and id > 0 and id % 10 = 9 order by id limit 500;# Query_time: 0.009835 Lock_time: 0.000081 Rows_sent: 500 Rows_examined: 21037
SET timestamp=1695711779;
select id,uuid,name,user_type,is_deleted,modify_date from test_user where is_deleted=0 and user_type=0 and id > 0 and id % 10 = 9 order by id limit 500;（這邊大家可能會有疑惑，為什么掃描行數要比預估的多一些，其實也正常，我們在做預估時并沒有把取模的過濾條件加上，所以必然會多掃描）
# Query_time: 6.981938 Lock_time: 0.000076 Rows_sent: 100 Rows_examined: 17890145
SET timestamp=1695711818;
select id,uuid,name,user_type,is_deleted,modify_date from test_user where is_deleted=0 and user_type=2 and id > 0 and id % 10 = 9 order by id limit 500;2.3.6 自此能得到結論
因為is_deleted和user_type數據分布不均勻并且數據區分度不高，執行計劃走主鍵順序掃描， 在查詢is_deleted=0 and user_type=2 特定場景的時，因為走主鍵索引順序遍歷，滿足user_type=2 的id比較靠后，需要先掃描完成前面1700w條數據后，才能找到滿足user_type=2的數據，SQL掃描行數變多， SQL執行時間變長。
三、優化方案
3.1 優化方案確定
當前SQL執行計劃以主鍵進行順序遍歷，是一個范圍掃描，有點像在一片很大的居民區按照序號挨家挨戶尋找一些特定的人一樣，比較簡單也比較低效。
既然查詢是以is_deleted和user_type為主要的過濾條件，查詢特定的人群信息，可以考慮直接在這兩列上添加索引，記錄特定人群信息的位置，根據位置直接去定向尋找。
雖然is_deleted和user_type字段區分度很低，但是成為有序結構，能避免這條SQL大量的讀取不符合條件的數據的行為，添加索引的收益遠大于索引帶來負面影響。
最終的添加的索引：
alter table test_user add index idx_isdeleted_usertype_id(is_deleted,user_type,id)；添加該索引的考慮：遵循ESR原則（等值在前，排序在中間，范圍在最后），既能高效掃描到對應的數據,還能避免id的排序，extra內顯示使用了Using index condition。
mysql> explain select id,uuid,name,user_type,is_deleted,modify_date from test_user where is_deleted=0 and user_type=2 and id > 0 and id % 10 = 9 order by id limit 500;
+----+-------------+-----------+------------+-------+-----------------------------------+---------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+-----------------------------------+---------------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | test_user | NULL | range | PRIMARY,idx_isdeleted_usertype_id | idx_isdeleted_usertype_id | 10 | NULL | 999 | 100.00 | Using index condition |
+----+-------------+-----------+------------+-------+-----------------------------------+---------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)3.2 優化效果對比
優化前：
# Query_time: 6.981938 Lock_time: 0.000076 Rows_sent: 100 Rows_examined: 17890145
SET timestamp=1695711818;
select id,uuid,name,user_type,is_deleted,modify_date from test_user where is_deleted=0 and user_type=2 and id > 0 and id % 10 = 9 order by id limit 500;優化后：
# Query_time: 0.000884 Lock_time: 0.000091 Rows_sent: 100 Rows_examined: 100
SET timestamp=1695714485;
select id,uuid,name,user_type,is_deleted,modify_date from test_user where is_deleted=0 and user_type=2 and id > 0 and id % 10 = 9 order by id limit 500;優化提升：
掃描行數從1700w條降低為100條，查詢時間從6.98s 降低為 0.8ms。
3.3 圖示的優化后的SQL執行過程
- 通過idx_isdeleted_usertype_id索引的有序性，進行二分查找，快速定位到滿足is_deleted和user_type、id條件主鍵信息。
- 通過主鍵信息回表讀取完整的數據。
- 返回數據給客戶端服務。
3.4 ICP特性(Index Condition Pushdown)
補充下執行計劃內extra列體現Using index condition優化。
- 索引條件下推 (ICP) 是針對 MySQL 使用索引從表中檢索行的情況的優化。
- 如果沒有 ICP，存儲引擎會遍歷索引以定位基表中的行，并將它們返回給 MySQL server，由 MySQL server評估行的 WHERE 條件。
- 在啟用 ICP 的情況下，如果 WHERE 條件的一部分可以通過僅使用索引中的列來評估，MySQL server會將這部分 WHERE 條件下推到存儲引擎。
- 然后存儲引擎通過使用索引條目來評估推送的索引條件，并且只有在滿足這一條件時才從表中讀取行。
- ICP可以減少存儲引擎必須訪問基表的次數和MySQL server必須訪問存儲引擎的次數。
ICP優化的使用和局限性路：
ICP優化在數據庫優化器內默認是開啟的，ICP優化適用性取決于以下條件：
- icp 對于使用rang、ref、eq_ref 和ref_or_null訪問模式去檢索全表數據行時候。
- icp 只適用于innodb、myisam引擎的表，包括分區的InnoDB和MyISAM表。
- icp只會使用二級索引，減少完整行記錄的讀取和減少I/O操作 對于聚集索引，完整行記錄已經被讀入innodb buffer中，using icp不能減少I/O操作。
- icp不支持使用創建在虛擬列上的二級索引，innodb引擎支持在虛擬列上創建二級索引。
- 引用子查詢的條件無法下推。
- 引用存儲函數的條件無法下推。存儲引擎無法調用存儲的函數。
- Triggered conditions cannot be pushed down。
-- 測試下相同的SQL執行在開啟ICP優化和關閉ICP優化,執行時間和掃描行數的對比.
-- 關閉ICP,SQL執行掃描行數是5043行,執行時間為8.03ms.
SET optimizer_switch='index_condition_pushdown=off';
# Query_time: 0.008031 Lock_time: 0.000085 Rows_sent: 500 Rows_examined: 5043
select id,uuid,name,user_type,is_deleted,modify_date from test_user where is_deleted=0 and user_type=0 and id > 10000 and id % 10 = 9 order by id limit 500;
-- 開啟ICP,SQL執行掃描行數僅為500行,執行時間為2.72ms.
SET optimizer_switch='index_condition_pushdown=on';
# Query_time: 0.002724 Lock_time: 0.000082 Rows_sent: 500 Rows_examined: 500
select id,uuid, name, user_type,is_deleted,modify_date from test_user where is_deleted=0 and user_type=0 and id > 10000 and id % 10 = 9 order by id limit 500;結論：本次測試,開啟ICP優化,SQL執行時掃描的行數僅為未開啟時的1/10,執行時間提升約2-3倍。
四、總結
- 將SQL查詢從串行改為高并發執行，需要評估下SQL查詢效率是否足夠高，評估的標準：SQL掃描行數/SQL返回行數 結果越大說明存在很多低效的數據掃描，執行效率不高。
- 分頁查詢通過主鍵遍歷是順序遍歷，從最小id到最大id，當存在其它過濾條件時，需要再次判斷數據是否滿足這些過濾條件，掃描的行數會隨著增長。
- 區分度較低的字段并非不適合創建索引，仔細評估查詢的場景，建立特定的組合索引，觸發MySQL icp優化，對查詢性能會有很大提升。
參考文章
Index Condition Pushdown介紹：
責任編輯：龐桂玉
來源：
vivo互聯網技術
