limit 影响性能的原因和优化方案

一、问题

当使用limit实现分页查询时，当limit的偏移量越大时，sql语句的耗时也越大。

select * from table_name limit 10000,10

select * from table_name limit 0,10

这两条查询语句都是取10条数据，但性能就相差甚远。

二、原因

　　原因：Limit 会导致 Mysql 扫描过多的数据记录或索引记录，而且大部分扫描到的记录都是无用的。

　　客户端程序发送sql语句查询请求给服务层，服务层会解析、优化sql语句，之后交给存储引擎，也就是说，存储引擎是真正完成查询的(增加、删除、修改也是由存储引擎负责的)。

SELECT * FROM testing  limit 1200000,100 

这条SQL 的执行逻辑是：
1）从数据表中读取第N条数据添加到数据集中
2）重复第一步直到 N = 1200000 + 100
3）根据 offset 抛弃前面 1200000 条数
4）返回剩余的 100 条数据

显然，导致这句 SQL 速度慢的问题出现在第2步。这前面的 1200000 条数据完全对本次查询没有意义，但是却占据了绝大部分的查询时间。

当存储引擎查询数据库文件后返回的不是一页的数据(100行)， 而是从第1行到第 (1200000 + 100)行的数据一起返回给服务层。服务层收到数据后会抛弃前面的1200000行，只留下最后的100行返回给客户端。

数据库表中行数据、索引都是以文件的形式存储到磁盘(硬盘)上的，而硬盘的速度相对来说要慢很多，存储引擎运行sql语句时，需要访问硬盘查询文件，然后返回数据给服务层。当返回的数据越多时，访问磁盘的次数就越多，就会越耗时。

三、优化

1）可减少返回的字段

2）尽可能使用索引覆盖扫描，避免了回表

select id,val from test where val=4 limit 300000,5;

Mysql 只需要扫描索引页，而不需要访问数据页，提高了查询效率。

3）使用子查询

如果不能使用索引覆盖扫描，或者查询字段较多，可以尝试使用子查询，也就是先用一个子查询找出需要的记录的 id 值，然后再用一个主查询根据 id 值获取其他字段。

比如：

select * from test where id in (select id from test where val=4 limit 300000,5);

Mysql 先执行子查询，在 val 索引上进行范围扫描，并返回 5 个 id 值。然后，Mysql 再执行主查询，在 id 索引上进行点查找，并返回所有字段。这样，Mysql 只需要扫描 5 个数据页，而不是 300005 个数据页，提高了查询效率。

4）使用id限定优化，省去了在数据集中查询初始位置的过程

当表的主键是有序或者是自增，可以使用id限定查询，查询过程是：

当已经查询了某页的数据后，记录下该页最后一行记录的主键id值(本例中是id为主键)，查询下一页时就可以使用如下sql： 

 select  *  from testing where 主键列名 > 当前页最后一行的主键值 limit 0, 100 

比如：

当前页最后一行的主键值是1563544，查询下一页就可以使用：

SELECT id,app_type,os_version FROM testing where id>1563566  limit 0,100

第一页怎么查询，可以选择一个比所有主键值都小的值，比如0或者负数 ：

SELECT id,app_type,os_version FROM testing where id>0  limit 0,100

5）基于索引再排序

这种方法适用于数据量多的情况(元组数上万)，最好ORDER BY后的列对象是主键或唯一索引，使得ORDER BY操作能利用索引被消除但结果集是稳定的。比如下面两个语句：

mysql> show profiles;
+----------+------------+--------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                        |
+----------+------------+--------------------------------------------------------------------------------------------------------------+
|        8 | 3.30585150 | select * from sbtest1 limit 1000000,10                                                                       |
|        9 | 1.03224725 | select * from sbtest1 order by id limit 1000000,10                                                           |
+----------+------------+--------------------------------------------------------------------------------------------------------------+

对索引字段id使用order by语句后，性能有了明显的提升。