本文共 2424 字，大约阅读时间需要 8 分钟。

       

1. 分页查询优化

在平时开发中，免不了使用分页，很多时候我们业务系统实现分页功能可能会用如下sql实现：

mysql> select * from employees limit 10000,10;

       这种查询方式表示从表 employees 中取出从 10001 行开始的 10 行记录。看似只查询了 10 条记录，实际这条 SQL 是先读取 10010 条记录，然后抛弃前 10000 条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据，执行效率是非常低的。所以在应对大数据量分页查询时，需要进行分页查询优化！

分页查询优化方案一般有两种

       

方法一：当主键是自增并且连续时，先根据主键过滤一部分数据，然后在剩下的数据中，再取一页数据（5条）

首先来看一个根据自增且连续主键排序的分页查询的例子：

mysql> select * from employees limit 90000,5;

该 SQL 表示查询从第 90001开始的5行数据，这种方式就如上面所说，大数据时存在效率问题，可以做以下改进

mysql> select * from employees where id > 90000 limit 5;

因为主键是自增并且连续的，所以可以改写成按照主键去从索引树上直接定位id，查询第 90001开始的5行数据，这样不仅用到了索引，还减少了扫描次数！

       

两者查询结果是一致的！再对比一下执行计划：

select * from employees limit 90000,5; 的执行计划如下：

select * from employees where id > 90000 limit 5;的执行计划如下： 显然改写后的 SQL 走了索引，而且扫描的行数大大减少，执行效率更高。

       

但需要注意的是：

       这种分页优化场景在开发中并不常用，因为这种的必须保证主键是自增并且连续的，然而在实际开发中，已插入的数据是有可能被删除的。如果数据被删除，则id就会变得不连续！那么limit 90000,5和id > 90000 limit 5取到的结果就可能不一致！

测试：

如果删除0 - 90000之间任意一条数据，则：

select * from employees limit 90000,5; 的执行结果如下：

select * from employees where id > 90000 limit 5;的执行结果如下：

可以看到两者的查询结果不一致

• limit 90000,5：以行数为基准，是从第 90001行开始查5行数据，因为前边删除了一行，所以第 90001行的id是90002
• id > 90000 limit 5：以id为基准，只查id > 90000行的5行数据，即使前边删除了一行，也依然从 id = 90001行开始收集！

另外，如果 原 SQL 是 order by 非主键的字段（比如 order by name），按照上面说的方法改写也会导致两条 SQL 的结果不一致。因为当order by name 时，是以name的Ascall码当做基准来排序的，id是乱的！

• limit 90000,5：表示的是按照name排序后的结果中，是从第 90001行开始取5行数据
• id > 90000 limit 5：表示按照name排序后的结果中，找到id>90000 的数据，取5条，因为按name排序，id是乱的，所以结果与上面的不一致！

所以这种改写得满足以下两个条件：

• 主键自增且连续
• 结果是按照主键排序的
         

方法二：使用 inner join 优化分页查询

当使用主键排序的分页查询时：

select * from employees  a    INNER JOIN 	(select * from employees where id > 90000 LIMIT 5) b    on  a.id = b.id

上面的sql可以看作是方法一的另一个版本，两个版本都是按照主键排序的，都需要主键自增且连续，只不过这里使用的是 inner join 的方式去查询

       

当使用非主键字段排序的分页查询时:

select * from employees ORDER BY name limit 90000,5;

执行计划如下：可以看到使用了全表扫描

这种分页查询才是开发中经常使用的：数据不一定连续，且不一定是按照主键排序！这种我们就无法使用方法一以id做排序查询！

       对于这种情况，可以对name字段建立索引，或者建立覆盖索引以name开头，当然就算为name建立了二级索引，但有的时候mysql执行器会觉得走二级索引再回表的效率，还不如直接扫一下全表，所以优化器放弃使用索引。这样也不会走name索引，如上执行计划所示！（建立的是（name、age、position）复合索引）

       可以使用inner join优化，优化的关键是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键（select id from employees order by name limit 90000,5，这个查询的数据都在覆盖索引上，肯定会走索引），然后根据查到的主键id再 inner join 全表查到对应的记录，SQL改写如下

SQL改写如下:

mysql> select * from employees einner join 	(select id from employees order by name limit 90000,5) ed on e.id = ed.id;

执行计划如下：

       可以看到，需要的结果与原 SQL 结果一致，执行时间减少了一半以上，原 SQL 使用的是 filesort 排序，而优化后的 SQL 使用的是索引排序，优化后的sql基本都用到了索引！

转载地址：http://gzhzi.baihongyu.com/