MYSQL深分页性能优化
MySQL深分页性能优化
前言
最近在做的一个需求需要写分页查询,需要根据前端的请求返回具体页码的数据。这种分页查询看上去非常简单,通常是用select count(*)…和select … limit A,B这两条sql语句组合而成,前者获得总数,后者获得每页之中的数据。后端获得这些数据后,再组装成返回结果返回给前端。
通常这么写没有问题,但是当用户数据量极速增多,公开出去的应用非常多,导致表里数据非常大时,这时再使用select … limit A,B这种sql进行深分页就会耗时很长,从而导致以下几点问题:
- 请求耗时长,影响用户体验
- 消耗过多MySQL机器的CPU资源,影响其他查询操作
- 服务器堆积大量请求,容易耗尽线程,影响其他操作
问题
先看一下select … limit A,B这种sql语句的问题在哪。先随便准备一张表,包含主键id和create_time以及其他一些字段,再往里面插入100万+的数据。
执行以下SQL语句
1 |
|
观察它的耗时时间仅为23ms,没什么问题
这时我们模拟用户的深分页请求,将offset参数调到1000000,执行以下SQL语句
1 |
|
这时它的耗时到了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这种语句的执行过程
- 先到create_time的索引树上找到降序排序的第一条数据,在叶子结点中找到对应的主键值(二级索引树上的叶子结点保存的是主键值而非行数据)
- 再用这个主键值到主键索引树上找到对应的行数据,这个操作称之为回表
- 重复以上两个操作大概A+B次,丢弃前面A行数据,返回后B行数据
观察上面的执行过程,很容易发现前面A行数据的回表操作是完全没有必要的,因为最后返回的时候还会把这些数据丢弃。
还有一点需要注意的是,即使排序字段加了索引但是由于扫描和回表行数太多,MySQL的优化器会选择全表扫描,再将得到的结果进行一个外部排序。比如select * from t2 order by create_time desc limit 1000000, 20这种offeset很大的深分页语句。
使用覆盖索引优化
针对这种深分页的sql语句最典型的解决方法就是使用覆盖索引减少回表操作进行优化。
我们执行下面这行语句:
1 |
|
这行语句跟select * from t2 order by create_time desc limit 1000000, 20的执行效果是一模一样的,但是它的执行时间只有161ms,远远小于之前执行的900多毫秒
这条sql之所以快的原因就在于其中的一条子查询语句
1 |
|
这条语句会在create_time的索引树上扫描找到符合条件的id,虽然也会扫描1000020行,但是由于二级索引树的叶子结点本身就保存了主键id值,无需回表,所以这个子查询执行的速度非常快。这种在索引树上就能获得值而无需去回表查询的方式叫做覆盖索引。
覆盖索引:索引是高效找到行的一个方法,当能通过检索索引就可以读取想要的数据,那就不需要再到数据表中读取行了。如果一个索引包含了(或覆盖了)满足查询语句中字段与条件的数据就叫做覆盖索引。
再回头看上面这行语句
1 |
|
这条语句其中的子查询已经找到了符合条件的id值,只需要对这20行id值做回表操作即可拿到想要的数据,省去了之前1000000行数据的回表操作,从而提升查询速度。这种优化方法也叫做延迟关联。
总结
1 |
|
一般来讲,上面这种普通的分页查询都可以通过延迟关联优化成下面这种sql
1 |
|