mysql分页查询limit_MySQL分页查询优化

MySQL是通过select * from order limit offset, N 的方式实现分页查询的。但并不是跳过offset行，而是取offset+N行，然后放弃前offset行，返回N行，当offset很小的时候，是没有问题的，当offset很大的时候，效率就非常的地下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL优化。

一、首先应该开启慢查询跟踪慢sql

mysql> show variables like 'slow_query%';+---------------------------+----------------------------------+| Variable_name | Value |+---------------------------+----------------------------------+| slow_query_log | OFF || slow_query_log_file | /mysql/data/localhost-slow.log |+---------------------------+----------------------------------+mysql> show variables like 'long_query_time';+-----------------+-----------+| Variable_name | Value |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+

二、SQL性能优化分析工具

explainprofilingOPTIMIZER_TRACE

三、分页查询优化方案

1 确定起始ID，适合ID自增情况

这种情况下可以前端传递给后端最大的id(xxx)，然后根据该id进行过滤并取得一页数据

 select * from student where id>=xxx ORDER BY id desc limit 0,20

2 使用子查询

原理和(1)一样，即取得起始的id，然后进行查询

select * from student where id>=(select id from student limit 800000,1) limit 20

这两种方案可以继续优化，速度更快：

 SELECT * FROM table WHERE id BETWEEN 1000000 AND 1000010;

3 ID不自增，需要先找到IDs，再利用聚集索引取数据，不需要回表查

 SELECT * FROM table WHERE id IN(10001, 100002, 1000003...);