mysql查询时offset过大影响性能的原因和优化

优化的三种方案

1 问题现状

起初我们采用最为简单的offset+limit分页查询方式，伪代码具体如下：

List<Student> students = new ArrayList<>();
int limit = n;
int offset = 0;
do {
  students = SELECT * FROM [table] WHERE status = [status] AND gradeId = [id] LIMIT [limit] OFFSET [offset];
  push(pos);
  update [table] set updateTime = NOW() where id in students[0].getId, students[1].getId...;
  offset += limit;
} while (CollectionUtils.isNotEmptry(students));

通过压测我们发现这种分页查询会存在性能问题，举例：假设表大小有500w行，随着offset的增加，查询的时长会越来越长：

SELECT * FROM student WHERE status = 1 AND gradeId = 4 LIMIT 100 OFFSET 10000; //7ms
SELECT * FROM student WHERE status = 1 AND gradeId = 4 LIMIT 100 OFFSET 100000; //54ms
SELECT * FROM student WHERE status = 1 AND gradeId = 4 LIMIT 100 OFFSET 1000000; //503ms
SELECT * FROM student WHERE status = 1 AND gradeId = 4 LIMIT 100 OFFSET 2000000; //1.013s
SELECT * FROM student WHERE status = 1 AND gradeId = 4 LIMIT 100 OFFSET 3000000; //1.509s
SELECT * FROM student WHERE status = 1 AND gradeId = 4 LIMIT 100 OFFSET 4000000; //2.035 s

当offset达到百万级别的时候查询时长长达1s多，这是线上业务所不能容忍的。要理解造成这个现象的原因，我们需要先了解以下几个概念：

2 问题原因分析

1. 聚簇&非聚簇索引

   InnoDB的索引基于B+树实现，每张InnoDB的表都有一个特殊的索引，叫做聚簇索引(Clustered Index)，聚簇索引每一个叶子节点都存储了表中的一行真实数据。表数据存储是全局唯一的，因此聚簇索引也是唯一的，往往都是利用主键生成。而其他非主键列生成的索引就是非聚簇索引，也叫二级索引，他们的叶子节点除了存储了被索引列的值，只存储主键的键值，通过主键关联到真实数据。总的来说，聚集索引与非聚集索引的区别是：叶节点是否存放一整行记录。

2. InnoDB引擎的OFFSET实现

         InnoDB引擎涉及OFFSET的查询执行过程中，对于offset前的每一行也做查询，具体来说，查询的是从第一行到第OFFSET+LIMIT行的数据块，再舍弃掉offset前的数据返回。

     3.学生表结构如下     

CREATE TABLE `student` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `gradeId` int(11) NOT NULL DEFAULT '0' COMMENT '年级id',
  `name` varchar(255) NOT NULL DEFAULT '' COMMENT '学生姓名',
  `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '学生状态，1：在校、2：退学',
  `creator` varchar(255) NOT NULL DEFAULT '' COMMENT '创建人',
  `createTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updater` varchar(255) NOT NULL DEFAULT '' COMMENT '更新人',
  `updateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `IX_GradeId` (`gradeId`),
  KEY `IX_Status` (`status`),
  KEY `IX_CreateTime` (`createTime`),
  KEY `IX_UpdateTime` (`updateTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

基于以上两个概念并结合学生表的结构，我们不难分析出耗时增加的原因，即：因为status、gradeId属于二级索引，因此只通过它们是无法查询出整行数据，还需要一次回表操作，也就是使用非聚簇索引进行查找数据时，需要根据主键值去聚簇索引中再查找一遍完整的记录，这是有IO耗时的。而随着offset的增加，对于offset前的每一行查询都需要回表操作，造成了查询时间变长。

3 问题解决方案

既然回表会造成额外的IO耗时，同时offset会引来不必要的查询行数，一个自然的优化方向是：直接基于ID进行分页查询。基于id进行分页查询带来的副作用是会涉及表内所有数据，但因为分表和冷热数据隔离的优化（参见3.1、数据规模优化），子表内只包含了一个在线推送任务的订阅数据，因此表内不回存在多余的无效数据。具体的实现参见以下伪代码：

List<Student> students = new ArrayList<>();
List<Student> pushedStudents = new ArrayList<>();
int limit = n;
int endId = SELECT id FROM [table] ORDER BY createTime DESC limit 1;
int startId = SELECT id FROM [table] ORDER BY createTime ASC limit 1;
do {
	students = SELECT * FROM [table] WHERE id > [endId - limit] and id <= [endId];
  for(Student student : students) {
    if(status == 1 and gradeId == xxx]){
    	push(student);
      pushedStudents.add(student);
    }
  }
  update [table] set updateTime = NOW() where id in pushedStudents[0].getId, pushedStudents[1].getId...;
  endId -= limit;
} while (endId > startId);

分页查询方式	索引	是否回表	涉及行数
LIMIT+OFFSET	非聚簇索引：status、gradeId	是	OFFSET+LIMIT
基于ID	聚簇索引：id	否	LIMIT

基于ID分页查询方式直接使用聚簇索引无需回表，也无需查询分页偏移量前的数据，从而减短了查询时间。