精选文章｜MySQL深分页优化 - 文章 - 开发者社区

MySQL深分页优化

picture.image

前言

分页是一个很普通的功能，只要是个后端开发就要写分页，那为什么要分页？

从业务上来讲，即使系统返回所有数据，用户绝大多数情况下是不会看后面的数据的。
技术上，因为要考虑取数据的成本，目标服务器磁盘、内存、网络带宽，以及请求发起方自身是否能承受大批量数据。

MySQL分页语法


              
                  

                select * from table limit 0, 20

思考: 使用分页，上面提到的第2点，这些成本真的能降低吗?

建表，造数据


                
CREATE TABLE t1 (
                
  id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
                
  m_id BIGINT NOT NULL COMMENT '其他id',
                
  `name` VARCHAR ( 255 ) COMMENT '用户名称',
                
  identity_no VARCHAR ( 30 ) COMMENT '身份证号',
                
  address VARCHAR ( 255 ) COMMENT '地址',
                
  create_time TIMESTAMP NOT NULL COMMENT '添加时间',
                
  modify_time TIMESTAMP NOT NULL COMMENT '修改时间',
                
  PRIMARY KEY `id` ( `id` ) 
                
) ENGINE INNODB DEFAULT CHARSET = 'utf8' COMMENT '深分页测试表';
                

                
-- 先初始化一条数据
                
INSERT INTO t1
                
VALUES
                
  ( 1, 1, '这里是随机中英文的名字—1', '100000000000000000', '这里是随机中英文的地址—1', '2010-01-01 00:00:00', '2010-01-01 00:00:00' );
                

                
-- 执行个十几次
                
set @i=1;
                
insert into t1(m_id, name, identity_no, address, create_time, modify_time) 
                
select @i:=@i+1 as m_id,   #随机生成1~10的整数
                
        concat('这里是随机中英文的名字—',@i),   #按序列生成不同的name
                
        100000000000000000+@i,
                
        concat('这里是随机中英文的地址—',@i),
                
        date_add(create_time,interval +@i*cast(rand()*100 as signed) SECOND), 
                
        date_add(date_add(create_time,interval +@i*cast(rand()*100 as signed) SECOND), interval + cast(rand()*1000000 as signed) SECOND) 
                
from t1;
                
#注: 该方法来自网络, 方法来源: https://blog.csdn.net/mysqltop/article/details/105230327
                
select count(1) from t1;

数据总量400W+：

picture.image

1：没有查询条件，没有排序

picture.image


                
select id,m_id, name, identity_no, address, create_time, modify_time
                
from t1 limit 1000000, 20;

100w之后的数据，耗时：

0.613s elapsed

加主键排序


                
select id,m_id, name, identity_no, address, create_time, modify_time
                
from t1 order by id limit 1000000, 20;

耗时：有所降低

0.417s elapsed

执行计划对比：

picture.image

可以看到带主键排序使用了主键索引，且只读取了需要的前n条数据，所以快。

因此, 结论1：即使业务上看起来没有任何条件还不需要排序，也加上order by主键。

这里其实有另一个问题：如果不带排序条件，MySQL默认是什么排序？

通常认为是主键，但通过查资料发现并不一定，这里有个物理顺序和逻辑顺序的区别，如：删除原有数据后再插入复用旧id的数据，可能会由于存放在不同页上造成物理顺序与逻辑顺序不一致，此时可以通过优化表改善：optimize table table_name。

2： 带排序 - 排序字段没有索引

picture.image


                
select id,m_id, name, identity_no, address, create_time, modify_time
                
from t1
                
order by create_time desc
                
limit 10000, 20;

执行时间：

2.015s elapsed

排序字段有索引：（注：t2表数据是直接从t1复制得到，再添加create_time索引）相同的sql查询t2。


                
select id,m_id, name, identity_no, address, create_time, modify_time
                
from t2
                
order by create_time desc
                
limit 10000, 20;

执行时间：

0.937s elapsed

执行计划对比：

picture.image

可以看到有索引的表，直接走索引取前n条数据，不需要全表扫描，也不会用到filesor。

结论2：给常用字段加索引，包括排序字段。

新的问题：

以上2个场景看似已可以解决大部分分页问题，但：

排序字段有索引就一定快吗？1w的时候速度较快，换成查询100w之后的数据呢？
如果当前表已经有多个索引，不适合再添加索引了呢？

3： 排序字段有索引，但分页再深一点： 从100w开始取20条

picture.image


                
select id,m_id, name, identity_no, address, create_time, modify_time
                
from t2
                
order by create_time desc
                
limit 1000000, 20;

耗时：非常慢

18.350s elapsed

执行计划：

picture.image

通过执行计划发现，并没有走索引，为什么没有走索引？

因为mysql优化器发现这条sql查询行数超过一定比例（据说是30%，但测试下来并不完全是）就会自动转换为全表扫描，能不能强制走索引呢？

可以的，加force index(idx)。

4：强制索引

picture.image


                
select id,m_id, name, identity_no, address, create_time, modify_time
                
from t2
                
force index(idx_create_time)
                
order by create_time desc
                
limit 1000000, 20;

强制索引后的执行计划：

picture.image

看下执行时间：

15.197s elapsed

有效果，但效果不明显，即使强制索引，mysql要取出100w+条完整数据也是非常消耗资源的，需要读取大量的索引页，频繁回表等随机IO。

结论3：即使有索引，再深一点的分页也会有问题，要避免。

以上尝试结果都没有很好的解决深分页性能问题，那是否有更好的解决方案？

有！

5： 带last_*条件查询

picture.image


                
select id,m_id, name, identity_no, address, create_time, modify_time
                
from t2
                
where id > #{last_id},create_time > #{last_create_time}
                
order by create_time desc
                
limit 0, 20;

性能与正常浅分页没差别，但前提是last_*字段有索引。

同时，该方案受使用场景限制，如跳页、多排序字段等，last_*都将无法使用。

推荐使用场景：没有页码的应用，如：滑动加载下一页、只有上下页按钮等。

6：联表子查询

picture.image

将场景4中强制索的sql改为子查询，先测试排序字段有索引的t2表。


                
select id,m_id, name, identity_no, address, create_time, modify_time
                
from t2
                
force index(idx_create_time)
                
order by create_time desc
                
limit 1000000, 20;
                

                
-- 改为：
                
SELECT 
                
  id, m_id, NAME, identity_no, address, create_time, modify_time 
                
FROM t2
                
JOIN ( SELECT id FROM t2 ORDER BY create_time desc LIMIT 1000000, 20 ) x USING ( id );

执行时间：0.742 s elapsed

效果明显。（原sql执行时长：15s+）

再测试create_time没有索引的t1，看下效果如何？


                
-- 在t1执行：
                
SELECT 
                
  id, m_id, NAME, identity_no, address, create_time, modify_time 
                
FROM t1
                
JOIN ( SELECT id FROM t1 ORDER BY create_time desc LIMIT 1000000, 20 ) x USING ( id );