百万数据分页查询优化方案

分页问题

分页列表查询是项目中的热点需求，这种需求的特点是：字段多、数据量大、访问频繁、使用率高的特点，这个功能是给用户最直观的展示系统的信息，针对于多、大、频、热这几个特点，会引申出一个问题：列表展示的数据可能是来自于不同的数据维度、需要关联N张表查询得到，那么，如何让用户更快、更准的获取到需要的数据，便成了程序员在编码时需要考虑到并且需要解决的问题，因为随时间推移，线上系统不乏几百万数据的表。

准备

CREATE TABLE `test_temp` (
  `test_id` int NOT NULL AUTO_INCREMENT,
  `field_1` varchar(20) DEFAULT NULL,
  `field_2` varchar(20) DEFAULT NULL,
  `field_3` bigint DEFAULT NULL,
  `create_date` date DEFAULT NULL,
  PRIMARY KEY (`test_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

写一个存储过程生成200万测试数据:

drop procedure if exists test_insert;
create procedure test_insert(n int)
begin
    declare v int default 0;
    SET AUTOCOMMIT = 0;
    while v < n
        do
            insert into test_temp(field_1, field_2, field_3, create_date)
            values (concat('testing',v),
                    substring(md5(rand()), 1, 10),
                    floor(rand() * 1000000),
                    adddate('1970-01-01', rand(v) * 10000));
            set v = v + 1;
        end while;
    SET AUTOCOMMIT = 1;
end;

插入数据:

call test_insert(2000000);

测试数据方案来自于:

https://blog.csdn.net/weixin_38924697/article/details/119978916

现象

带分页的语句，我们一般使用Limit实现，那么基于以上数据我们写一个SQL:

SELECT * from test_temp LIMIT 1,10

执行时间:0.004秒

此时，我们模拟分页数据特别往后的情况，分页数据越往后越慢。

SELECT SQL_NO_CACHE * from test_temp LIMIT 19999900,10

执行时间：1.348秒，速度慢了二十余倍。

实际的业务场景下，可能会关联N张表，而且线上服务器的压力会比单机开发环境更重，因此实际接口响应时间会更长。

问题原因

1. 回表:查询频率高的字段会建立索引，但是并不是所有的查询字段都会在索引上，无法命中索引的字段则需要回表，回表是IO操作，因为需要根据索引查找到数据行后，再根据数据行的主键或唯一索引去聚簇索引中查找具体的数据行。因此在执行回表操作时需要从磁盘读取数据，而磁盘IO是相对较慢的操作。
2. 查询规则：limit 19999900，10并不是从第19999900行开始扫描，使用explain查看执行计划：

解决方案

当查询的字段都被索引覆盖时，可无需回表，那么我们可以先查询出主键id，再根据主键id拼接id条件或者作为临时表JOIN原表就可以了。因为主键id是最快的索引：聚簇索引，通过id就能快速找到指定行。

查询方案一：

先查询出id，再根据id直接查询数据。

查询出id

SELECT test_id from test_temp LIMIT 1999995,5

执行计划:

再根据这些id为条件查询数据

SELECT * from test_temp WHERE test_id in (2952993,2952995,2952996,2952997)；

优化后的查询时间为:0.002秒。

执行计划

查询方案二:

使用子查询作为临时表Inner join主表查询：

SELECT * from (SELECT test_id from test_temp LIMIT 1999995,5) as temp INNER JOIN test_temp tt on tt.test_id = temp.test_id

整体查询时间:0.245秒

执行计划:

性能对比

在不考虑MySQL执行时校验权限、建立连接的情况下，两种方案整体查询时间在0.25秒左右，相比于整体查询的1.34秒具有较大优势。方案一相较于方案二的SQL语句更加简洁易懂，而方案二只需与MySL建立一次查询即可。

其他优化项

请参考:

百万查询注意点