记一次mysql性能优化记录

1.背景

有个项目需求需要从一个数据量较大的表中，取出一部分数据，这个表的记录数量大概在3000万-1亿左右，从中需要取出1000万+的部分数据，数据库是MySQL，表的数据结构为：

CREATE TABLE `task_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `task_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `dom_id` char(24) COLLATE utf8mb4_unicode_ci NOT NULL,
  `task_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `task_name` text COLLATE utf8mb4_unicode_ci,
  `task_result` char(24) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `task_state` char(24) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '状态',
  `execution_type` int(2) NOT NULL DEFAULT '0',
  `temporal_workflow_id` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `temporal_run_id` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `obj_id` char(24) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `obj_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `content` mediumtext COLLATE utf8mb4_unicode_ci,
  `task_param` mediumtext COLLATE utf8mb4_unicode_ci,
  `task_status_history` mediumtext COLLATE utf8mb4_unicode_ci,
  `submit_time` datetime DEFAULT NULL,
  `running_time` datetime DEFAULT NULL,
  `finished_time` datetime DEFAULT NULL,
  `utime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_del` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `task_id` (`task_id`),
  KEY `task_status_submit_time_index` (`submit_time`),
  KEY `dom_id` (`dom_id`),
  KEY `obj_id` (`obj_id`),
  KEY `obj_name` (`obj_name`),
  KEY `task_type` (`task_type`),
  KEY `task_result` (`task_result`),
  KEY `finished_time` (`finished_time`)
) ENGINE=InnoDB AUTO_INCREMENT=12429951 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

查询数据的SQL:
select * from task_status where utime between #{startTime} and #{endTime};
因为数据量较大，不可能一次查询出来，所以需要进行分页，所以SQL就变成了分页查询：
select * from task_status where utime between #{startTime} and #{endTime} order by id limit #{offset},#{pageSize};

通过这样的方式上线后，当表的数据量较大时（千万级），每次分页查询的速度挺慢的，因为需要多次查询，总的消耗的时间不低，我们需要对SQL进行优化

2.优化方案

先分析下SQL为什么执行效率不高：
主要原因：
1.utime没有索引，所以每次查询基本都是全表扫描，效率很差。
2.分页查询当offset越来越大时，查询效率会越来越低，因为分页查询的本质是先查询出来offset+pageSize后，再剔除掉前面offset个，所以效率很低

对utime增加索引???
如果对utime增加索引，则语句变成：
select * from task_status where utime between #{startTime} and #{endTime} order by utime limit #{offset},#{pageSize};
这种方案能解决上面的原因1，但是原因2还是无法解决，offset越来越大时查询速度会越来越慢。而且还需要变更表结构，线上客户环境是私有化部署，需要运维进入每个客户的环境进行表结构升级，成本较高，而且索引占用存储，所以这个方案也被否定了。

分页优化一般是通过id来实现，例如：
select * from task_status where id in(select id from task_status where utime between #{startTime} and #{endTime} order by utime limit #{offset},#{pageSize});
利用了覆盖索引和主键索引，不需要回表查询大量的数据，但是这种方式前提是能对utime增加索引，但是实际情况是这个字段增加索引很困难，所以这种方案也不是最好的方案。

经过一段时间的思考，想到了一种方案：
方案详情：
步骤一：我们是根据utime字段做查询，先可以查询出来总数和满足条件的最大id和最小id值
select count() as num,max(id) as maxId,min(id) as minId from task_status where utime between #{startTime} and #{endTime}
步骤二：我们将minId和maxId整个区间的数据划分为若干个段，根据第一步得到的 num,maxId和minId，如果我们每次分页的数量是5万，那么需要分段的数量为：num/50000 as pageSize（注意取整，向上+1），在每个段里id的递增数量是：(maxId-minId+1)/pageSize as idIncrement，所以查询的语句变成
select * from task_status id between minId and minId+idIncrement and utime between #{startTime} and #{endTime};
select * from task_status id between minId+idIncrement and minId+2idIncrement and utime between #{startTime} and #{endTime};
.....
不足之处：因为id分布式不均衡的，这样直接限制递增数量，可能会出现有的区间可能查询出来数据较多，有的较少，可能没有，所以对局部再做分页查询，sql就变成了：
select * from task_status id between minId and minId+idIncrement and utime between #{startTime} and #{endTime} order by id limit 50000;
如果返回的数据数量少于50000，就不需要再查询，直接到下一个id区间查询，否则需要继续进行查询，但是可以获取到这次查询的最后一个id值，下一个查询就变成了
select * from task_status id between #{上一次查询的最大值+1} and a+idIncrement and utime between #{startTime} and #{endTime} order by id limit 50000;

优化效果：
task_status 200w-300w数据之间

SELECT * FROM task_status
WHERE utime BETWEEN '2022-04-18 00:00:00' AND '2022-05-18 00:00:00' order by id limit 0,50000;
刚开始是1-2s，随着分页的进行

SELECT * FROM task_status_00014
WHERE utime BETWEEN '2022-04-18 00:00:00' AND '2022-05-18 00:00:00' order by id limit 500000,50000;
需要3s左右

SELECT * FROM task_status WHERE utime BETWEEN '2022-04-18 00:00:00' AND '2022-05-18 00:00:00' order by id limit 1500000,50000;
需要5s+

优化后：
SELECT * FROM task_status WHERE id between 1000000 and 1050000 and utime BETWEEN '2022-04-18 00:00:00' AND '2022-05-18 00:00:00' order by id limit 50000;
不管区间怎么变，时间维持在1-2s之间

数据量太小，用1000万数据进行测试

SELECT * FROM task_status where utime between '2018-05-21' and '2019-05-21' limit 3000000,10000;
10s+

SELECT * FROM task_status where utime between '2018-05-21' and '2019-05-21' and id between 500000 and 550000 order by id;
0.1s

性能还是得到了很大的提升，但是这种方案确实有点复杂，还需要分区间，每个区间里面去做分页，很绕人，所以后面又想到了更加简单更好的方案

最终方案：
不用分区间，先获取max(id) as maxId和min(id) as minId

如果每次分页查询50000条，则第一次查询语句
select * from task_status where id>=minId and utime between '2018-05-21' and '2019-05-21' order by id asc limit 50000

找到上一次查询最大的id as lastMaxId，作为下次的起始id，那么第二次查询语句变成
select * from task_status where id>=lastMaxId+1 and utime between '2018-05-21' and '2019-05-21' order by id asc limit 50000
......
以此类推，直到这次查询到的最大id值>=maxId值或者查询出来的数据数量为0结束。

实际数据测试结果：
SELECT * FROM task_status where utime between '2001-05-21' and '2023-05-21' and id > #{lastMaxId}+1 order by id asc limit 50000;
每次基本维持在0.5s，而且实现起来较为简单，也比较好理解，需要查询的数量比上个方案更少，所以这种方案目前来说是最优的。
注意：order by id必须要有，不然按默认顺序的话就会有问题，不过mysql好像默认也是按照id顺序，为了防止不可预料的问题，建议还是加这个