各位观众老爷们，晚上好！我是你们的老朋友，今儿个咱们聊点儿MySQL里让人头疼的玩意儿：LIMIT OFFSET 大分页的性能问题以及优化方案。
咱们都知道，LIMIT OFFSET 这玩意儿是用来做分页的，但是用多了，特别是数据量大的时候，那叫一个慢啊！慢到怀疑人生！今天，咱就来扒一扒它慢在哪儿，又该怎么治它。
一、LIMIT OFFSET 为啥这么慢？
先来个简单的例子，假设咱们有个 users 表，里面有100万条数据，字段包括 id (主键，自增), name, age, email。
CREATE TABLE `users` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` int unsigned DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;现在，我们要查第999991到1000000条数据，也就是最后一页的数据：
SELECT * FROM users LIMIT 999990, 10;看起来很简单是吧？但这条SQL执行的时候，MySQL可不是简单地跳到第999991条数据开始读。它是这样的：
- 扫描前999990条数据。 这就是问题所在！MySQL需要把前999990条数据都扫描一遍，虽然最终丢弃了，但扫描的动作是实实在在发生的。
- 读取需要的10条数据。
- 返回结果。
这就像你去图书馆借书，你想借第1000本书，图书馆管理员不是直接拿给你，而是从第一本书开始，一本一本翻，翻到第999本扔掉，然后才找到你要的第1000本！这效率能高吗？
可以用 EXPLAIN 命令来看一下这条SQL的执行计划：
EXPLAIN SELECT * FROM users LIMIT 999990, 10;你会发现 type 字段可能是 ALL (全表扫描)，rows 字段显示扫描了接近100万行数据！ 这就说明MySQL确实是扫描了大量无用的数据。
二、优化方案：让MySQL少干点活！
既然知道了LIMIT OFFSET慢的原因是扫描了太多无用数据，那么优化的思路就是：让MySQL尽可能少地扫描无用数据。
下面介绍几种常见的优化方案：
1. 利用索引覆盖优化
如果我们只需要查询 id 字段，可以利用索引覆盖，避免回表查询。 假设我们只需要查询 id 字段，那么可以建立一个覆盖索引：
CREATE INDEX idx_id ON users (id);然后执行查询：
SELECT id FROM users LIMIT 999990, 10;这时候，EXPLAIN 的结果会显示 Using index，说明使用了索引覆盖，避免了回表查询。虽然还是扫描了大量数据，但是由于只需要读取索引，速度会比全表扫描快一些。
2. 子查询优化
这种方法的核心思想是：先查出需要的 id，然后再根据 id 去查其他字段。
SELECT * FROM users WHERE id IN (SELECT id FROM users LIMIT 999990, 10);这种方法在某些情况下有效，但在MySQL优化器不给力的情况下，可能会更慢！ 因为MySQL优化器可能会先执行外层查询，再对每个结果执行内层查询，导致性能更差。
3. 利用书签法/延迟关联
这种方法也需要先查出需要的 id，然后再根据 id 去查其他字段。但是，它使用了 JOIN 操作，通常比子查询更高效。
SELECT u.*
FROM users u
JOIN (SELECT id FROM users LIMIT 999990, 10) AS t
ON u.id = t.id;这种方法通常比子查询更快，因为MySQL优化器可以更好地优化 JOIN 操作。 EXPLAIN 输出应该显示使用了索引。
4. 范围查询优化
如果 id 是连续的，我们可以利用 id 的范围来优化查询。
首先，查询出最后一页的起始 id：
SELECT id FROM users ORDER BY id LIMIT 999990, 1;假设查出的起始 id 是 999991，那么我们可以使用范围查询：
SELECT * FROM users WHERE id >= 999991 LIMIT 10;这种方法避免了扫描大量无用数据，效率通常很高。 但这种方法要求 id 是连续的，如果 id 不连续，就需要使用其他方法。
5. 游标优化
游标是一种编程技术，可以在服务器端缓存结果集，然后逐条返回给客户端。 这种方法适用于需要频繁访问同一结果集的情况。
但是，游标会占用服务器资源，需要谨慎使用。
6. 禁止使用OFFSET，使用上一页最后一条数据的ID
这种方法核心思路是，将OFFSET换成ID范围查询，避免MySQL扫描无用数据。
例如，你要查询第N页，每页大小是pageSize，那么你需要保存第N-1页最后一条数据的ID。
假设最后一页pageSize是10， 上一页最后一条数据的id是999990，那么查询语句如下：
SELECT * FROM users WHERE id > 999990 LIMIT 10;这种方法效率非常高，但是需要客户端保存上一页最后一条数据的ID。 适用于APP下拉刷新，滚动加载等场景。
7. 使用搜索引擎
如果数据量非常大，并且需要进行复杂的查询，可以考虑使用搜索引擎，例如 Elasticsearch 或 Solr。 搜索引擎可以对数据进行索引，并提供快速的查询和分页功能。
三、各种优化方案的比较
| 优化方案 | 优点 | 缺点 | 适用场景 |
|---|---|---|---|
| 索引覆盖 | 避免回表查询，提高查询速度 | 仍然需要扫描大量数据 | 只需要查询索引字段的情况 |
| 子查询 | 简单易懂 | 可能会被MySQL优化器优化成更慢的执行计划 | 某些情况下有效，需要测试 |
| 书签法/延迟关联 | 通常比子查询更快 | 需要 JOIN 操作 |
通常比子查询更快，需要测试 |
| 范围查询 | 避免扫描大量无用数据，效率高 | 要求 id 是连续的 |
id 是连续的情况 |
| 游标 | 适用于需要频繁访问同一结果集的情况 | 占用服务器资源，需要谨慎使用 | 需要频繁访问同一结果集的情况 |
| 基于ID范围查询 | 效率极高，避免扫描无用数据 | 需要客户端保存上一页最后一条数据的ID |
适用于APP下拉刷新，滚动加载等场景 |
| 使用搜索引擎 | 数据量大，查询复杂的情况下，提供快速的查询和分页功能 | 需要引入新的技术栈，增加系统复杂度 | 数据量非常大，并且需要进行复杂的查询 |
四、实战演练：优化一个慢查询
假设我们有一个 orders 表，里面有1000万条数据，字段包括 id (主键，自增), user_id, order_time, amount。
CREATE TABLE `orders` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`user_id` int unsigned DEFAULT NULL,
`order_time` datetime DEFAULT NULL,
`amount` decimal(10,2) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;现在，我们要查询第9999991到10000000条订单数据：
SELECT * FROM orders LIMIT 9999990, 10;这条SQL执行非常慢，EXPLAIN 结果显示全表扫描。
我们可以尝试使用书签法/延迟关联优化：
SELECT o.*
FROM orders o
JOIN (SELECT id FROM orders LIMIT 9999990, 10) AS t
ON o.id = t.id;如果 id 是连续的，我们还可以使用范围查询优化：
SELECT id FROM orders ORDER BY id LIMIT 9999990, 1; -- 查出起始 ID
SELECT * FROM orders WHERE id >= 起始ID LIMIT 10;或者使用基于ID范围查询
SELECT * FROM orders WHERE id > 上一页最后一个ID LIMIT 10;具体选择哪种优化方案，需要根据实际情况进行测试。
五、总结：没有银弹，只有合适的解决方案
LIMIT OFFSET 大分页的性能问题是一个常见的难题，但是没有一劳永逸的解决方案。我们需要根据实际情况，选择合适的优化方案。
- 数据量小的时候，
LIMIT OFFSET足够应付。 - 数据量大的时候，需要考虑优化方案，例如索引覆盖、子查询、书签法、范围查询、游标、搜索引擎。
- 优化的时候，要多做测试，找到最适合自己的方案。
记住，优化是一个持续的过程，需要不断地学习和实践。
好了，今天就先聊到这里，希望对大家有所帮助！ 下次有机会再跟大家分享其他MySQL的技巧。 各位晚安！