序

背景

由于在项目中，APP端有一个关于异常的分页查询，在响应上一直比较慢，由于对MySQL认知不够，一直觉得分页没法优化了，直到从一篇技术博客上看到了相关的文章，便引起了我的好奇，并通过自己的电脑进行了测试，于是本篇博客记录一下。

前言

在阅读本篇内容前，最好有一定的MySQL索引相关的底层知识，包括索引结构B+树、索引覆盖、回表、聚簇与非聚簇索引等知识。本篇也会对这些内容做一些简单的介绍。本文的存储引擎基于InnoDB。

简介

本篇将介绍MySQL中分页查询慢的原因、以及实际测试，并给出一个优化的解决方案、通过测试进行对比，最后会给出分析。

优化前的分页查询

翻开很多的博客，都说分页效率低下，那我们需要实际测试一下才能知道真假。

准备数据

环境：MySQL5.7
引擎：InnoDB
脚本：建表、存储过程及调用：

-- 创建表：logs1
CREATE TABLE `logs1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `logtype` varchar(255) DEFAULT NULL,
  `logurl` varchar(255) DEFAULT NULL,
  `logip` varchar(255) DEFAULT NULL,
  `logdz` varchar(255) DEFAULT NULL,
  `ladduser` varchar(255) DEFAULT NULL,
  `lfadduser` varchar(255) DEFAULT NULL,
  `laddtime` datetime DEFAULT NULL,
  `htmlname` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='1000W';


-- 创建存储过程, 暂时修改命令以$$为结束符
DELIMITER $$
CREATE PROCEDURE my_insert()
BEGIN
	SET @i=1;
	WHILE @i<=10000000 DO
		INSERT INTO `logs1`(`logtype`,`logurl`,`logip`,`logdz`,`ladduser` ,`lfadduser`,`laddtime`,`htmlname`) VALUES ( 2, '/index', '0:0:0:0:0:0:0:1', null, null, 'null', '2018-05-03 14:02:42', '首页');
		SET @i=@i+1;
	END WHILE;
END $$
DELIMITER;

-- 调用存储过程
CALL my_insert();
复制代码

以上步骤完成了表、数据的填充，一共插入了一千万条数据。

开启监控

为了方便对执行时间进行更好的监控，先开启profiles监控。开启之后，每一条SQL的执行时间都可以查看到：

set profiling=1;
复制代码

开始测试

这里分别测试其实条目和偏移量对性能的影响，每组分别测试三次，并记录其时间：

测试不同查询数量对速度的影响：每条SQL分别执行三次

select * from logs1 where logtype=2 limit 100000,1;
select * from logs1 where logtype=2 limit 100000,10;
select * from logs1 where logtype=2 limit 100000,100;
select * from logs1 where logtype=2 limit 100000,1000;
select * from logs1 where logtype=2 limit 100000,10000;
复制代码

时间记录如下：

通过上面这一组数据，可以得出发现：单次查询的数量越大，速度越慢，但性能下降的不是太大。

测试不同的偏移量对查询速度的影响：每条SQL同样分别执行三次

select * from logs1 where logtype=2 limit 100,100;
select * from logs1 where logtype=2 limit 1000,100;
select * from logs1 where logtype=2 limit 10000,100;
select * from logs1 where logtype=2 limit 100000,100;
select * from logs1 where logtype=2 limit 1000000,100;
复制代码

时间记录如下：

通过这组结果，可以发现，偏移量越大，速度越慢，而且性能急剧下降。

为什么分页会很慢

当分页时候，例如我们需要获取第100万条数据的时候，MySQL并没有办法跳过前面的100万行数据，因此会从头进行扫描，当扫描到需要的第100万行数据的时候，会丢弃前面扫描出来的100万条数据。所以上面测试的第二种，即针对偏移量的测试，会发现偏移量越大，性能下降就会越厉害。而且如果需要回表，则前100万次的回表操作，效率上的影响会是巨大的。

总结

偏移量越大，查询数量越大，性能就会越差。 其中偏移量对性能的影响，超过查询数量的影响。

优化后的分页查询

如何优化

分页查询在每个系统里，或多或少，往往很难避免，那有什么办法可以优化呢？
答案是有的，这里给出一种方案：基于子查询。来看写法：查询从第100000行记录开始的100条记录

select id  
from logs1 
where logtype=2 and id >=
        (select id from logs1 where logtype=2 limit 100000,1) 
limit 100;
复制代码

基于优化的测试

在普通的分页查询中，偏移量对性能的影响较大，所以这里仅仅对偏移量这种情况进行测试，基于查询数量的测试就不做了。

基于偏移量的分忧查询优化测试：每条SQL同样分别执行三次

select id  from logs1 where logtype=2 and id >= (select id from logs1 where logtype=2 limit 100,1) limit 100;
select id  from logs1 where logtype=2 and id >= (select id from logs1 where logtype=2 limit 1000,1) limit 100;
select id  from logs1 where logtype=2 and id >= (select id from logs1 where logtype=2 limit 10000,1) limit 100;
select id  from logs1 where logtype=2 and id >= (select id from logs1 where logtype=2 limit 100000,1) limit 100;
select id  from logs1 where logtype=2 and id >= (select id from logs1 where logtype=2 limit 1000000,1) limit 100;
复制代码

优化后的测试结果如下：

我们再把上面优化前的、同样针对不同偏移量的测试结果拿过来，方便对比：

通过对比，可以发下，偏移量越大的情况下，优化后的效果越明显，至少提速了两到三倍
左右的性能。

优化的原理

通过前面的测试对比，可以看到通过子查询确实提高了几倍的效率，那我们不禁思考，原理是什么？

我们在把优化的子查询语句放过来：

select id  
from logs1 
where logtype=2 and id >=
        (select id from logs1 where logtype=2 limit 100000,1) 
limit 100;
复制代码

前面总结过：偏移量越大，查询数量约大，性能越大。所以优化的角度就可以从这两处入手，减少偏移量和数量。同时考虑到B+树结构的特点：查询会比较快。
所以来分析这条优化后的SQL：

子查询部分： 依旧从100000行开始，但是数量只取了1条，所以此处做了第一次优化；
where条件： id > 子查询的结果，记录的是上次最大的id值。由于B+树，id又是主键索引，所以，此处速度也很快；
主查询的limit： 由于上面的子查询和where已经定位到了开始扫描的位置，且由于limit 100 这条语句，只会扫描100行，而不同于普通分页中的limit，不会去扫描前面的N行，所以不会拜拜扫描前面的多行再丢弃。因此效率上有了很大的提升。