MySQL实践——分页查询优化

问题现象

一个客户业务系统带有分页查询功能，但是随着查询页数的增加，越往后查询性能越差，有时一个查询可能需要1分钟左右的时间。分页查询的写法类似于：

select * from employees limit 250000,5000;

这是最传统的一种分页查询写法，但问题也是最多的。随着limit M,N值的增大，往往在越往后翻页的过程中速度越慢，原因是MySQL会读取表中的前M+N条数据，M越大，性能就越差。

这里多说几句，在服务的很多客户中，还是有很多客户使用这种传统的分页查询写法的，主要有两点原因：
①系统早期建设时数据量不大，性能问题没有暴露出来；
②很多开发商把这种写法固化到了产品框架中，导致后期开发人员根本不关心这类问题。

优化方案

1．普通优化写法

针对分页查询，我们可以使用最简单的一种优化写法：

select * from(select emp_no from employees limit 250000,5000) b, employees a where a.emp_no=b.emp_no;

优化后的分页查询写法，会先查询翻页中需要的N条数据的主键值（emp_no），然后根据主键值回表查询所需要的N条数据，在此过程中查询N条数据的主键id在索引中完成，所以效率会高一些。

2．业务优化写法

上面的写法虽然可以达到一定程度的优化，但还是存在性能问题。最佳的方式是在业务上进行配合修改为以下语句：

select * from employees where emp_no > #last_emp_no# order by emp_no limit 20;

采用这种写法，在页面上只能通过点击More来获得更多数据，而不是纯粹的翻页。因此，每次查询只需要使用上次查询出的数据中的id来获取接下来的数据即可，但这种写法需要业务配合。

3．性能对比

传统的分页查询写法：

mysql>select *from employees limit 250000,5000;
5000 rows in set(1.31 sec)

优化写法：

mysql>select * from(select emp_no from employees limit 250000,5000)b,employees awhere a.emp_no =b.emp_no;
5000 rows in set(0.94 sec)

从执行计划中可以看出，首先执行子查询中的employees表，根据主键做索引全表扫描，然后与a表通过emp_no做主键关联查询，相比传统写法中的全表扫描效率会高一些。从两种写法上能看出性能有一定的差距，虽然并不明显，但是随着数据量的增大，两者执行的效率便会体现出来。