【深度】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性能优化分析工具

explain
profiling
OPTIMIZER_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...);