MySQL深分页性能优化

前言

最近在做的一个需求需要写分页查询，需要根据前端的请求返回具体页码的数据。这种分页查询看上去非常简单，通常是用select count(*)…和select … limit A,B这两条sql语句组合而成，前者获得总数，后者获得每页之中的数据。后端获得这些数据后，再组装成返回结果返回给前端。

通常这么写没有问题，但是当用户数据量极速增多，公开出去的应用非常多，导致表里数据非常大时，这时再使用select … limit A,B这种sql进行深分页就会耗时很长，从而导致以下几点问题：

1. 请求耗时长，影响用户体验
2. 消耗过多MySQL机器的CPU资源，影响其他查询操作
3. 服务器堆积大量请求，容易耗尽线程，影响其他操作

问题

先看一下select … limit A,B这种sql语句的问题在哪。先随便准备一张表，包含主键id和create_time以及其他一些字段，再往里面插入100万+的数据。

执行以下SQL语句

select * from t2 order by create_time desc limit 10, 20;

观察它的耗时时间仅为23ms，没什么问题

这时我们模拟用户的深分页请求，将offset参数调到1000000，执行以下SQL语句

select * from t2 order by create_time desc limit 1000000, 20;

这时它的耗时到了923ms，是之前的40多倍。如果MySQL机器用的是机械硬盘的话这个时间会更长。

原因

出现上个问题的原因是MySQL在执行select * from t2 order by create_time desc limit 1000000, 20这种语句时，不会直接定位到第1000000行数据，返回后面的20条，而是会从头开始扫描得到1000020行数据，返回的时候丢弃前1000000行数据只返回后20行。

使用explain看它的执行情况，扫描行数到了1038187行

在有索引的情况下，我们看一下select * from t2 order by create_time desc limit A, B这种语句的执行过程

1. 先到create_time的索引树上找到降序排序的第一条数据，在叶子结点中找到对应的主键值（二级索引树上的叶子结点保存的是主键值而非行数据）
2. 再用这个主键值到主键索引树上找到对应的行数据，这个操作称之为回表
3. 重复以上两个操作大概A+B次，丢弃前面A行数据，返回后B行数据

观察上面的执行过程，很容易发现前面A行数据的回表操作是完全没有必要的，因为最后返回的时候还会把这些数据丢弃。

还有一点需要注意的是，即使排序字段加了索引但是由于扫描和回表行数太多，MySQL的优化器会选择全表扫描，再将得到的结果进行一个外部排序。比如select * from t2 order by create_time desc limit 1000000, 20这种offeset很大的深分页语句。

使用覆盖索引优化

针对这种深分页的sql语句最典型的解决方法就是使用覆盖索引减少回表操作进行优化。

我们执行下面这行语句:

select * from t2, (select id from t2 order by create_time desc limit 1000000, 20) temp where temp.id = t2.id;

这行语句跟select * from t2 order by create_time desc limit 1000000, 20的执行效果是一模一样的，但是它的执行时间只有161ms，远远小于之前执行的900多毫秒

这条sql之所以快的原因就在于其中的一条子查询语句

select id from t2 order by create_time desc limit 1000000, 20

这条语句会在create_time的索引树上扫描找到符合条件的id，虽然也会扫描1000020行，但是由于二级索引树的叶子结点本身就保存了主键id值，无需回表，所以这个子查询执行的速度非常快。这种在索引树上就能获得值而无需去回表查询的方式叫做覆盖索引。

覆盖索引：索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。

再回头看上面这行语句

select * from t2, (select id from t2 order by create_time desc limit 1000000, 20) temp where temp.id = t2.id;

这条语句其中的子查询已经找到了符合条件的id值，只需要对这20行id值做回表操作即可拿到想要的数据，省去了之前1000000行数据的回表操作，从而提升查询速度。这种优化方法也叫做延迟关联。

总结

select * from t order by ... where ... limit A, B

一般来讲，上面这种普通的分页查询都可以通过延迟关联优化成下面这种sql

select * from t, (select id form t order by ... where ... limit A, B) temp where t.id = temp.id