分布式任务调度内的 MySQL 分页查询优化

一、背景介绍

最近在线上环境发现了一条执行较慢的分页查询，高并发执行，产生了大量的慢查询日志，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=utf8mb4

2.3.2 查看 SQL 执行计划

通过查看SQL执行计划，发现执行计划走主键索引扫描，以下是SQL执行计划的关键信息解