MySQL分页查询的限制有性能问题，你知道吗？

MySQL分页查询通常是通过limit来实现的。

MySQL limit基本使用非常简单。 limit 接收 1 或 2 个整数参数。如果有2个参数，第一个指定返回的第一条记录行的偏移量，第二个是返回的最大记录行数。记录起始行的偏移量为0。

为了兼容PostgreSQL，limit还支持limit # offset #。

问题：

对于小偏移量，直接用limit查询是没有问题的，但是随着数据量的增大，分页进一步往后，limit的语句会更大并且速度明显变慢。

优化思路：

避免数据量较大时扫描过多记录

解决方案：

查询或子查询下的分页方式

JOIN分页与分页效率子查询基本处于同一级别，消耗的时间也基本相同。

这是一个例子。一般来说，MySQL的主键是自增数字类型。对于这种情况，可以采用以下方法进行优化。

以实际生产环境中80万条数据的表为例，对比一下优化前后的查询时间：

-- 传统limit，文件扫描
[SQL]SELECT * FROM tableName ORDER BY id LIMIT 500000,2;
受影响的行: 0
时间: 5.371s

-- 子查询方式，索引扫描
[SQL]
SELECT * FROM tableName
WHERE id >= (SELECT id FROM tableName ORDER BY id LIMIT 500000 , 1)
LIMIT 2;
受影响的行: 0
时间: 0.274s

-- JOIN分页方式
[SQL]
SELECT *
FROM tableName AS t1
JOIN (SELECT id FROM tableName ORDER BY id desc LIMIT 500000, 1) AS t2
WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT 2;
受影响的行: 0
时间: 0.278s
复制代码

可以看到优化后性能提升了近20倍。

优化原理：

子查询是在索引上完成的，而正则查询是在数据文件上完成的。一般来说，索引文件比数据文件小很多，因此操作也会更加高效。因为要读取数组的全部内容，第一种方法要跨越大量的数据块来读取，而第二种方法基本上是直接根据索引字段定位再读取对应的内容，所以效率自然大大提高改善了。因此，

优化limit并不是直接使用limit，而是先获取offset id，然后直接使用limit数据。

在实际项目使用中，可以使用类似策略模式的方法来处理分页。比如每页有100条数据，如果在100页范围内，就使用最基本的分页方式。如果大于 100，则使用子查询。分页模式。

作者：RebeccaZhong
链接：https://juejin.im/post/5cd2d57951882540d928c2be
来源：掘金
。商业转载请联系作者获得许可。非商业转载请注明来源。