分页查询我们都会用，Mysql提供的limit可以帮助我们实现分页查询，但随着数据越来越多，limit指令还能高效执行嘛？

做个实验

数据生成

先生成1000w条数据

SELECT COUNT(1) from user;

开始查询

分别执行以下sql

SELECT id,username,age,address,email from user limit 0,10000;

SELECT id,username,age,address,email from user limit 10000,10000;

SELECT id,username,age,address,email from user limit 100000,10000;

SELECT id,username,age,address,email from user limit 1000000,10000;

SELECT id,username,age,address,email from user limit 9000000,10000;

使用Navicat执行，结果如下：

分析

声明：数据库所在的服务器配置非常低，因此查询需要更多时间，好处是这样可以放大差距

观察查询结果得知，即使PageSize相同，如果OFFSET不同，查询的时间也会有所差距，对于最后一条语句而言，更是花费了3.517s

为什么会发生这种情况，难道mysql不是跳过OFFSET条数据再选取PageSize条数据嘛？

还真不是。

Mysql执行limit语句的流程

1. 解析查询：MySQL首先解析整个查询语句，包括SELECT列表、FROM子句、WHERE子句、JOIN子句等。

2. 执行计划：根据解析的结果，MySQL生成一个执行计划，这个计划详细描述了如何高效地检索数据。

3. 应用WHERE子句：如果存在WHERE子句，MySQL会根据条件过滤数据，只留下符合条件的行。

4. 排序：如果存在ORDER BY子句，MySQL会根据指定的列对结果集进行排序。

5. 应用LIMIT子句：

   • 计算OFFSET：MySQL会跳过前OFFSET条记录。这意味着，如果OFFSET是10，MySQL会跳过前10条记录，从第11条记录开始返回结果。

   • 计算COUNT：在跳过OFFSET条记录之后，MySQL会返回接下来的COUNT条记录。如果COUNT是5，MySQL会返回接下来的5条记录。

6. 返回结果：MySQL将最终的结果集返回给客户端。

但是，关键点在这：MySQL实际上会扫描并处理所有被OFFSET跳过的行。

这在未来会达到性能瓶颈，因此，如果我们想要查询海量数据，不应该直接limit

优化

先来explain一下原始sql语句

explain SELECT id,username,age,address,email from user limit 9000000,10000;

可以发现这条sql语句全表扫描，完全不走索引。

先查询OFFSET处的id，再LIMIT

我们可以先利用索引，获取到OFFSET处的id，然后根据id来获取数据

SELECT * from user WHERE id >= (SELECT id from user LIMIT 9000000,1) LIMIT 10000;

结果如下：

使用explain分析

可以发现，不管是子查询还是主查询，都没有全表查询，值得注意的是：这种方法只适合自增ID

先查询需要获取的id，然后使用内连接查询

SELECT u1.id,username,age,address,email from user u1 INNER JOIN (SELECT id from user LIMIT 9000000,10000) u2 on u1.id = u2.id

结果如下：

使用explain分析

可以发现，在针对user表的查询中，使用了主键索引，之后通过内连接对派生表进行全表扫描

综上所述，在大数据量的情况下，我们应当尽量使用索引，避免全表扫描以提高查询速度