众所周知，MySQL为分页查询提供了一个非常好的方式：LIMIT，然而，在实际开发中，当数据量达到一定的量级（例如千万量级）的时候，单纯使用LIMIT语句查询，查询后面的数据的时候，耗时会增加，这个时候就需要对SQL进行一定的优化，或者说，在开发的过程中，应当避免直接使用LIMIT语句。

做个小实验

在开始前，我们先做一个小实验，首先准备一张千万量级的数据表，我这里简单准备了一张表，同时添加了一千多万的数据

表结构：

mysql> desc product;
+-----------+---------------+------+-----+---------+----------------+
| Field     | Type          | Null | Key | Default | Extra          |
+-----------+---------------+------+-----+---------+----------------+
| id        | int           | NO   | PRI | NULL    | auto_increment |
| name      | varchar(32)   | YES  |     | NULL    |                |
| content   | varchar(256)  | YES  |     | NULL    |                |
| price     | int           | YES  |     | NULL    |                |
| image     | varchar(256)  | YES  |     | NULL    |                |
| count     | int           | YES  |     | NULL    |                |
| type      | int           | YES  |     | NULL    |                |
| extra     | varchar(1024) | YES  |     | NULL    |                |
| create_at | date          | YES  |     | NULL    |                |
| update_at | date          | YES  |     | NULL    |                |
+-----------+---------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

先查询一下数据量：

mysql> select count(*) from product;
+----------+
| count(*) |
+----------+
| 10079244 |
+----------+
1 row in set (1 min 24.35 sec)

使用LIMIT查询第10、1000、100000、10000000页数据，并分析其耗时：

mysql> show profiles;
+----------+-------------+----------------------------------------+
| Query_ID | Duration    | Query                                  |
+----------+-------------+----------------------------------------+
|        1 |  0.00272500 | select * from product limit 10,5       |
|        2 |  0.01160000 | select * from product limit 1000,5     |
|        3 |  0.22394800 | select * from product limit 100000,5   |
|        4 | 15.21520500 | select * from product limit 10000000,5 |
+----------+-------------+----------------------------------------+
4 rows in set, 1 warning (0.00 sec)

可以看到，随着页数的增加，SQL的耗时依次增大

原因分析

到这里，我们就可以分析一下原因了。

对于LIMIT m,n，他的执行过程是先读取前面m+n条记录，然后抛弃前m条，读后面n条想要的，所以m越大，偏移量越大，性能就越差。

我们知道，如果MySQL只读取索引列的话，并没有回表操作，读取速度很快，那么，我们可以利用这一特性，对SQL进行一些改造

LIMIT的优化

对于主键有序

如果主键有序，我们可以使用子查询+id >= 的形式进行

对于上述SQL，我们将其优化为：

select * from product where id >= (select id from user limit 10000000,1) limit 5;

对于主键无序

如果主键无序，可以通过limit查询满足条件的id，然后通过inner join进行查询

对于上述SQL，我们将其优化为：

select * from product t inner join (select id from user limit 10000000,5) tmp on t.id = tmp.id;

验证效果

mysql> show profiles;
+----------+-------------+------------------------------------------------------------------------------------------------+
| Query_ID | Duration    | Query                                                                                          |
+----------+-------------+------------------------------------------------------------------------------------------------+
|        1 | 15.65217800 | select * from product limit 10000000,5                                                         |
|        2 |  1.47032300 | select * from product where id > (select id from user limit 10000000,1) limit 5                |
|        3 |  1.38755000 | select * from product t inner join (select id from user limit 10000000,5) tmp on t.id = tmp.id |
+----------+-------------+------------------------------------------------------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

可以看到，效果显著