分布式任务调度内的 MySQL 分页查询优化等值在前，排序在中间，范围在最后

分布式任务调度内的 MySQL 分页查询优化 https://mp.weixin.qq.com/s/VhSzxYIRv83T3D3JD4cORg

三、优化方案

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;