MySQL 性能优化：LIMIT语句

MySQL 性能优化：LIMIT语句

分页查询是SQL语句中比较常见的方式，当数据量过大的时候就会很慢。

select * from table_name limit 10000,10

LIMIT当偏移量过大的时候就会效率低。

这句SQL的执行的逻辑是：

1 . 从数据表中读取第N条数据添加到数据集中
2 .重复第一步直到 N = 10000 + 10
3 .根据 offset 抛弃前面 10000 条数
4 .返回剩余的 10 条数据

这样的话，这句SQL语句有个很大的问题，就是第二步，这一步骤对我们想要获得的数据并没有什么意义，但是却占用了很大的时间。

优化方式一：

select * from table_name where (id >= 10000) limit 10

这句SQL的作用就是从全表搜索转化为范围搜索，缩小了搜索范围，从而提高效率。相当于直接告诉数据库，你可以直接从10001地方去拿数据了。

但是如果加上附加的查询条件，比如我只要某个用户的数据，这个SQL就不行了。

可以见到这种思路是有局限性的，首先必须要有自增索引列，而且数据在逻辑上必须是连续的，其次，你还必须知道特征值。

如此苛刻的要求，在实际应用中是不可能满足的。

优化方式二：

说起数据库优化，肯定是先想到索引。所以：先查找出需要数据的索引列（假设为 id），再通过索引列查找出需要的数据。

Select * From table_name Where id in (Select id From table_name where ( user = *** )) limit 10000, 10;

select * from table_name where( user = xxx ) limit 10000,10

相比较结果是（500w条数据）：第一条花费平均耗时约为第二条的 1/3 左右。

同样是较大的 offset（偏移量），第一条的查询更为复杂，为什么性能反而更好。

这是因为MYSQL的主索引的数据结构是B+Tree 基本原理就是：

• 子查询只用到了索引列，没有取实际的数据，所以不涉及到磁盘IO，所以即使是比较大的 offset 查询速度也不会太差。
• 利用子查询的方式，把原来的基于 user 的搜索转化为基于主键（id）的搜索，主查询因为已经获得了准确的索引值，所以查询过程也相对较快。

在数据量比较大的时候in就会显得有点慢了。****

优化方式三：

select * from table_name inner join ( select id from table_name where (user = xxx) limit 10000,10) b using (id)

本文关于SQL中LIMIT优化就到此为止。