分布式任務(wù)調(diào)度內(nèi)的 MySQL 分頁查詢優(yōu)化
一、背景介紹
最近在線上環(huán)境發(fā)現(xiàn)了一條執(zhí)行較慢的分頁查詢，高并發(fā)執(zhí)行，產(chǎn)生了大量的慢查詢?nèi)罩?，CPU使用率逐步升高。
通過觀察它的執(zhí)行時間，發(fā)現(xiàn)該SQL查詢時快時慢，執(zhí)行時間并不穩(wěn)定，以至于在高并發(fā)執(zhí)行場景時，數(shù)據(jù)庫來不及響應(yīng)，數(shù)據(jù)庫服務(wù)變慢。
二、分析定位
2.1 定位 SQL 執(zhí)行變慢的原因
通過數(shù)據(jù)庫管理平臺查看SQL執(zhí)行信息發(fā)現(xiàn)，SQL解析行數(shù)（掃描行數(shù)）和SQL執(zhí)行時間都很不穩(wěn)定，執(zhí)行時長和解析行數(shù)（掃描行數(shù)）是成正比的。
這個也能解釋的通為什么SQL執(zhí)行時長變了，因為掃描行數(shù)變多了，SQL執(zhí)行時間成比例增長。
-- 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 的業(yè)務(wù)背景
通過與研發(fā)溝通發(fā)現(xiàn)，該SQL原來是串行執(zhí)行，單個線程在跑，后來覺得比較慢，改為分布式任務(wù)并行執(zhí)行，通過id取模0-9，調(diào)度10個線程，每個線程處理1個分區(qū)，這樣就有10個并發(fā)相當(dāng)于把數(shù)據(jù)做了切片，并發(fā)查詢并發(fā)處理，由此帶來數(shù)據(jù)庫端的并發(fā)升高。從技術(shù)角度上看，提高數(shù)據(jù)處理速度，給數(shù)據(jù)做切片，改單線程為并發(fā)處理，并沒有任何問題，反而是一種比較好的優(yōu)化方案，但是高并發(fā)執(zhí)行的SQL都是要有一個前提，SQL執(zhí)行效率要特別高，否則會導(dǎo)致數(shù)據(jù)庫端物理機資源耗盡，數(shù)據(jù)庫服務(wù)來不及響應(yīng)。
2.3 定位 SQL 掃描行數(shù)變化的原因
2.3.1 慢 SQL 及表結(jié)構(gòu)信息
-- 為了方便理解和說明，新建一個test_user表，造了一些模擬數(shù)據(jù)，將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 '創(chuàng)建時間',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_uuid` (`uuid`),
KEY `idx_modifydate` (`modify_date`)
) ENGINE=InnoDB AUTO_INCREMENT=7986024 DEFAULT CHARSET=utf8mb42.3.2 查看 SQL 執(zhí)行計劃
通過查看SQL執(zhí)行計劃，發(fā)現(xiàn)執(zhí)行計劃走主鍵索引掃描，以下是SQL執(zhí)行計劃的關(guān)鍵信息解讀：
- type=range 范圍掃描
- key = primary 使用主鍵索引
- rows = 877w 預(yù)估的掃描行數(shù)
- filter = 1.00 百分比，滿足過濾條件返回的行數(shù) = 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 執(zhí)行過程
通過簡單的圖示，描述下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 計算數(shù)據(jù)分布
從SQL過濾條件看只有is_deleted、user_type、id這三個，能預(yù)估到is_deleted和user_type區(qū)分度不高，通過SQL查看下數(shù)據(jù)的分布。
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)
-- 從數(shù)據(jù)分布來看user_type等于2的數(shù)據(jù)較少，只有999條，其他相對比較均勻數(shù)據(jù)分布驗證測試
將上述4種結(jié)果（is_deleted和user_type）分別通過SQL查看最近1000條滿足條件的數(shù)據(jù)的id區(qū)間，驗證數(shù)據(jù)的分布。
- 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的數(shù)據(jù)記錄分布在id 6-3876,大約掃描3871條數(shù)據(jù),能返回500條滿足條件的值,數(shù)據(jù)分布均勻.
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的數(shù)據(jù)記錄分布在id 3-4019,大約掃描4016條數(shù)據(jù),能返回500條滿足條件的值,數(shù)據(jù)分布均勻.
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的數(shù)據(jù)記錄分布在id 5-4020,大約掃描4015條數(shù)據(jù),能返回500條滿足條件的值,數(shù)據(jù)分布均勻.
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的數(shù)據(jù)記錄分布在id 17890648-17891147，是比較緊湊的，但是由于id比較大，整體排在較后的位置。
-- 如果按照主鍵遍歷，需要遍歷完前面的1700w條不符合條件數(shù)據(jù)，才能遍歷到滿足條件的數(shù)據(jù)。
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 實際執(zhí)行測試
重要字段信息說明：
- Query_time：SQL執(zhí)行時間
- Rows_examined：SQL掃描行數(shù)
- Rows_sent：SQL返回行數(shù)
# 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;（這邊大家可能會有疑惑，為什么掃描行數(shù)要比預(yù)估的多一些，其實也正常，我們在做預(yù)估時并沒有把取模的過濾條件加上，所以必然會多掃描）
# 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 自此能得到結(jié)論
因為is_deleted和user_type數(shù)據(jù)分布不均勻并且數(shù)據(jù)區(qū)分度不高，執(zhí)行計劃走主鍵順序掃描， 在查詢is_deleted=0 and user_type=2 特定場景的時，因為走主鍵索引順序遍歷，滿足user_type=2 的id比較靠后，需要先掃描完成前面1700w條數(shù)據(jù)后，才能找到滿足user_type=2的數(shù)據(jù)，SQL掃描行數(shù)變多， SQL執(zhí)行時間變長。
三、優(yōu)化方案
3.1 優(yōu)化方案確定
當(dāng)前SQL執(zhí)行計劃以主鍵進行順序遍歷，是一個范圍掃描，有點像在一片很大的居民區(qū)按照序號挨家挨戶尋找一些特定的人一樣，比較簡單也比較低效。
既然查詢是以is_deleted和user_type為主要的過濾條件，查詢特定的人群信息，可以考慮直接在這兩列上添加索引，記錄特定人群信息的位置，根據(jù)位置直接去定向?qū)ふ摇?/p>
雖然is_deleted和user_type字段區(qū)分度很低，但是成為有序結(jié)構(gòu)，能避免這條SQL大量的讀取不符合條件的數(shù)據(jù)的行為，添加索引的收益遠大于索引帶來負(fù)面影響。
最終的添加的索引：
alter table test_user add index idx_isdeleted_usertype_id(is_deleted,user_type,id)；添加該索引的考慮：遵循ESR原則（等值在前，排序在中間，范圍在最后），既能高效掃描到對應(yīng)的數(shù)據(jù),還能避免id的排序，extra內(nèi)顯示使用了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 優(yōu)化效果對比
優(yōu)化前：
# 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;優(yōu)化后：
# 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;優(yōu)化提升：
掃描行數(shù)從1700w條降低為100條，查詢時間從6.98s 降低為 0.8ms。
3.3 圖示的優(yōu)化后的SQL執(zhí)行過程
- 通過idx_isdeleted_usertype_id索引的有序性，進行二分查找，快速定位到滿足is_deleted和user_type、id條件主鍵信息。
- 通過主鍵信息回表讀取完整的數(shù)據(jù)。
- 返回數(shù)據(jù)給客戶端服務(wù)。
3.4 ICP特性(Index Condition Pushdown)
補充下執(zhí)行計劃內(nèi)extra列體現(xiàn)Using index condition優(yōu)化。
- 索引條件下推 (ICP) 是針對 MySQL 使用索引從表中檢索行的情況的優(yōu)化。
- 如果沒有 ICP，存儲引擎會遍歷索引以定位基表中的行，并將它們返回給 MySQL server，由 MySQL server評估行的 WHERE 條件。
- 在啟用 ICP 的情況下，如果 WHERE 條件的一部分可以通過僅使用索引中的列來評估，MySQL server會將這部分 WHERE 條件下推到存儲引擎。
- 然后存儲引擎通過使用索引條目來評估推送的索引條件，并且只有在滿足這一條件時才從表中讀取行。
- ICP可以減少存儲引擎必須訪問基表的次數(shù)和MySQL server必須訪問存儲引擎的次數(shù)。
ICP優(yōu)化的使用和局限性路：
ICP優(yōu)化在數(shù)據(jù)庫優(yōu)化器內(nèi)默認(rèn)是開啟的，ICP優(yōu)化適用性取決于以下條件：
- icp 對于使用rang、ref、eq_ref 和ref_or_null訪問模式去檢索全表數(shù)據(jù)行時候。
- icp 只適用于innodb、myisam引擎的表，包括分區(qū)的InnoDB和MyISAM表。
- icp只會使用二級索引，減少完整行記錄的讀取和減少I/O操作 對于聚集索引，完整行記錄已經(jīng)被讀入innodb buffer中，using icp不能減少I/O操作。
- icp不支持使用創(chuàng)建在虛擬列上的二級索引，innodb引擎支持在虛擬列上創(chuàng)建二級索引。
- 引用子查詢的條件無法下推。
- 引用存儲函數(shù)的條件無法下推。存儲引擎無法調(diào)用存儲的函數(shù)。
- Triggered conditions cannot be pushed down。
-- 測試下相同的SQL執(zhí)行在開啟ICP優(yōu)化和關(guān)閉ICP優(yōu)化,執(zhí)行時間和掃描行數(shù)的對比.
-- 關(guān)閉ICP,SQL執(zhí)行掃描行數(shù)是5043行,執(zhí)行時間為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執(zhí)行掃描行數(shù)僅為500行,執(zhí)行時間為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;結(jié)論：本次測試,開啟ICP優(yōu)化,SQL執(zhí)行時掃描的行數(shù)僅為未開啟時的1/10,執(zhí)行時間提升約2-3倍。
四、總結(jié)
- 將SQL查詢從串行改為高并發(fā)執(zhí)行，需要評估下SQL查詢效率是否足夠高，評估的標(biāo)準(zhǔn)：SQL掃描行數(shù)/SQL返回行數(shù) 結(jié)果越大說明存在很多低效的數(shù)據(jù)掃描，執(zhí)行效率不高。
- 分頁查詢通過主鍵遍歷是順序遍歷，從最小id到最大id，當(dāng)存在其它過濾條件時，需要再次判斷數(shù)據(jù)是否滿足這些過濾條件，掃描的行數(shù)會隨著增長。
- 區(qū)分度較低的字段并非不適合創(chuàng)建索引，仔細(xì)評估查詢的場景，建立特定的組合索引，觸發(fā)MySQL icp優(yōu)化，對查詢性能會有很大提升。
參考文章
Index Condition Pushdown介紹：
