业务场景

一般在项目开发中会有很多的统计数据需要进行上报分析，一般在分析过后会在后台展示出来给运营和产品进行分页查看，最常见的一种就是根据日期进行筛选。这种统计数据随着时间的推移数据量会慢慢的变大，达到百万、千万条数据只是时间问题。

瓶颈再现

创建了一张 user 表，给 create_time 字段添加了索引。并在该表中添加了 100w 条数据。

我们这里使用 limit 分页的方式查询下前 5 条数据和后 5 条数据在查询时间上有什么区别。

查询前 10 条基本上不消耗什么时间

我们从第 50w+开始取数据的时候，查询耗时 1 秒。

SQL_NO_CACHE 这个关键词是为了不让 SQL 查询走缓存。

同样的 SQL 语句，不同的分页条件，两者的性能差距如此之大，那么随着数据量的增长，往后页的查询所耗时间按理会越来越大。

问题分析

回表

我们一般对于查询频率比较高的字段会建立索引。索引会提高我们的查询效率。我们上面的语句使用了 SELECT * FROM user，但是我们并不是所有的字段都建立了索引。当从索引文件中查询到符合条件的数据后，还需要从数据文件中查询到没有建立索引的字段。那么这个过程称之为回表。

覆盖索引

如果查询的字段正好创建了索引了，比如 SELECT create_time FROM user，我们查询的字段是我们创建的索引，那么这个时候就不需要再去数据文件里面查询，也就不需要回表。这种情况我们称之为覆盖索引。

IO

回表操作通常是 IO 操作，因为需要根据索引查找到数据行后，再根据数据行的主键或唯一索引去聚簇索引中查找具体的数据行。聚簇索引一般是存储在磁盘上的数据文件，因此在执行回表操作时需要从磁盘读取数据，而磁盘 IO 是相对较慢的操作。

LIMTI 2000,10 ？

你有木有想过 LIMIT 2000,10 会不会扫描 1-2000 行，你之前有没有跟我一样，觉得数据是直接从 2000 行开始取的，前面的根本没扫描或者不回表。其实这样的写法，一个完整的流程是查询数据，如果不能覆盖索引，那么也是要回表查询数据的。

现在你知道为什么越到后面查询越慢了吧！

问题总结

我们现在知道了 LIMIT 遇到后面查询的性能越差，性能差的原因是因为要回表，既然已经找到了问题那么我们只需要减少回表的次数就可以提升查询性能了。

解决方案

既然覆盖索引可以防止数据回表，那么我们可以先查出来主键 id（主键索引），然后将查出来的数据作为临时表然后 JOIN 原表就可以了，这样只需要对查询出来的 5 条结果进行数据回表，大幅减少了 IO 操作。

优化前后性能对比

我们看下执行效果：

• 优化前：1.4s

• 优化后：0.2s

查询耗时性能大幅提升。这样如果分页数据很大的话，也不会像普通的 limit 查询那样慢。

三种优化方案，查询效率直接提升 10 倍

1. 准备数据

先创建一张用户表，只在 create_time 字段上加索引：

CREATE TABLE `user` (  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',  `name` varchar(255) DEFAULT NULL COMMENT '姓名',  `create_time` timestamp NULL DEFAULT NULL COMMENT '创建时间',  PRIMARY KEY (`id`),  KEY `idx_create_time` (`create_time`)) ENGINE=InnoDB COMMENT='用户表';

然后往用户表中插入 100 万条测试数据，这里可以使用存储过程：

drop PROCEDURE IF EXISTS insertData;DELIMITER $$create procedure insertData()begin declare i int default 1;   while i <= 100000 do         INSERT into user (name,create_time) VALUES (CONCAT("name",i), now());         set i = i + 1;    end while; end $$call insertData() $$

2. 验证深分页问题

每页 10 条，当我们查询第一页的时候，速度很快：

select * from user where create_time>'2022-07-03' limit 0,10;

在不到 0.01 秒内直接返回了，所以没显示出执行时间。

我们翻到第 10000 页的时候，查询效率急剧下降：

select * from user where create_time>'2022-07-03' limit 100000,10;

执行时间变成了 0.16 秒，性能至少下降了几十倍。

耗时主要花在哪里了？

1. 需要扫描前 10 条数据，数据量较大，比较耗时

2. create_time 是非聚簇索引，需要先查询出主键 ID，再回表查询，通过主键 ID 查询出所有字段

画一下回表查询流程：

1. 先通过 create_time 查询出主键 ID

2. 再通过主键 ID 查询出表中所有字段

别问为什么 B+树的结构是这样的？问就是规定。

可以看一下前两篇文章。

然后我们就针对这两个耗时原因进行优化。

3. 优化查询

3.1 使用子查询

先用子查询查出符合条件的主键，再用主键 ID 做条件查出所有字段。

select * from user where id in (  select id from user   where create_time>'2022-07-03'   limit 100000,10);

不过这样查询会报错，说是子查询中不支持使用 limit。

我们加一层子查询嵌套，就可以了：

select * from user where id in ( select id from (    select id from user     where create_time>'2022-07-03'     limit 100000,10 ) as t);

执行时间缩短到 0.05 秒，减少了 0.12 秒，相当于查询性能提升了 3 倍

为什么先用子查询查出符合条件的主键 ID，就能缩短查询时间呢？

我们用 explain 查看一下执行计划就明白了：

explain select * from user where id in ( select id from (    select id from user     where create_time>'2022-07-03'     limit 100000,10 ) as t);

可以看到 Extra 列显示子查询中用到 Using index，表示用到了覆盖索引，所以子查询无需回表查询，加快了查询效率。

3.2 使用 inner join 关联查询

把子查询的结果当成一张临时表，然后和原表进行关联查询。

select * from user inner join (   select id from user     where create_time>'2022-07-03'     limit 100000,10) as t on user.id=t.id;

询性能跟使用子查询一样。

3.3 使用分页游标（推荐）

实现方式就是：当我们查询第二页的时候，把第一页的查询结果放到第二页的查询条件中。

例如：首先查询第一页

select * from user where create_time>'2022-07-03' limit 10;

然后查询第二页，把第一页的查询结果放到第二页查询条件中：

select * from user where create_time>'2022-07-03' and id>10 limit 10;

这样相当于每次都是查询第一页，也就不存在深分页的问题了，推荐使用。

执行耗时是 0 秒，查询性能直接提升了几十倍。

这样的查询方式虽然好用，但是又带来一个问题，就是跳转到指定页数，只能一页页向下翻。

所以这种查询只适合特定场景，比如资讯类 APP 的首页。

互联网 APP 一般采用瀑布流的形式，比如百度首页、头条首页，都是一直向下滑动翻页，并没有跳转到制定页数的需求。

不信的话，可以看一下，这是头条的瀑布流：

传参中带了上一页的查询结果。

响应数据中，返回了下一页查询条件。

所以这种查询方式的应用场景还是挺广的，赶快用起来吧。

知识点总结：