本文前提，比较复杂的查询语句，不是单单理论上的覆盖索引实现延迟关联可以解决的场景。

本文主要讨论：

  1. order by 对分页的影响：
    1.1 order by 索引字段；
    1.2 order by 非索引字段（Using filesort）；
  2. order by 非索引字段下：浅分页和深分页的区别
字段关系
id自增主键
user_id二级索引
type普通字段
create_time普通字段

1. 排序对浅分页的影响

1.1 存在排序条件（借助索引有序性）

无影响，性能好

创建索引：user_id、create_time

select * from table where user_id=10001 and type=1 order by create_time limit 100;

  • 当user_id相同时，create_time是有序的，借助create_time的有序性，只需要读取100条记录即可。

1.2 存在排序条件（不走索引）

极大影响，性能极差。此时explain中出现Using filesort

select * from table where user_id=10001 and type=1 order by create_time desc limit 100;

  • 分页出现性能瓶颈的点有两个（此时深分页的性能等同浅分页）：
    • 将所有数据读取到内存时，若内存空间不足，会使用临时磁盘；
    • 将内存中所有字段排序时，若内存空间不足，会使用临时磁盘；

2. 浅分页一定性能好吗？

2.1 当存在Using filesort时

此时浅分页性能也不好

浅分页【性能差】：select * from table where user_id=10001 and type=1 order by create_time desc limit 100;

注意：user_id=10001 and type=1条件后得到的记录依旧非常多。

深分页【性能差】：select * from table where user_id=10001 and type=1 order by create_time desc limit 10000,100;

当分页中存在Using filesort时，那么一定会将所有记录都读取到内存中，进行统一排序，然后选择出limit的记录。

2.2 当不存在Using filesort时

此时浅分页性能较好。

浅分页【性能好】：select * from table where user_id=10001 and type=1 limit 100;

  • 深分页【性能查差】：select * from table where user_id=10001 and type=1 limit 10000,100;

**瓶颈：**深分页要多读取数据到内存中，故性能差。

3 如何优化复杂的深分页

3.1 方式一：优化sql

select * from table where user_id=10001 and type=1 order by create_time desc limit 10000,100;

  1. 借助索引有序性进行排序，优化【性能瓶颈3】，也防止浅分页下的【性能瓶颈1】会拉取所有记录；
  2. 使用嵌套子查询的方式优化【性能瓶颈2】，即在子查询中只查询id；在此我向大家推荐一个架构学习交流圈。交流学习指导伪鑫：1253431195（里面有大量的面试题及答案）里面会分享一些资深架构师录制的视频录像：有Spring，MyBatis，Netty源码分析，高并发、高性能、分布式、微服务架构的原理，JVM性能优化、分布式架构等这些成为架构师必备的知识体系。还能领取免费的学习资源，目前受益良多

优化后的语句：

select * FROM table INNER JOIN (SELECT id FROM table  b WHERE user_id=1001 and type=1 order by id DESC LIMIT 10000,100) h on a.id=h.id

  • 注意id是自增主键，所以order by create_time等效于order by id，而记录就是默认以id的顺序来存储的。就可以借助索引有序性来完成排序；
  • 子查询中查到的是id，这样可以防止深分页查询到记录太多，导致的临时文件存储记录的场景；

注意：依旧存在【性能瓶颈1】，在深分页场景下性能依旧偏忧。

3.2 方式二：新建create_time索引

select * from table where user_id=10001 and type=1 order by create_time desc limit 100;

此时存在两个索引：user_id和create_time，此时mysql将使用create_time来完成查询。

在浅分页中性能不错。

注意：依旧存在【性能瓶颈1】，在深分页场景下性能依旧偏忧。

3.3 业务上的优化

  1. 产品维度对分页游标进行约束，例如Google

  2. 修改接口，每次返回scroll（即当前滚动id）。但不支持页码的跳转：select * from table where id>滚动id limit 200

相关阅读

本文前提：查询条件比较复杂，不能单纯的在子查询中使用覆盖索引（即不能实现延迟关联）。延迟关联详见：[MySql性能（5）—覆盖索引与延迟关联（优化深分页查询）]