大家好，欢迎来到今天的“SQL 性能奇妙夜”！🌙 我是你们的老朋友，今天我们将一起深入探讨一个让无数开发者头疼的问题：LIMIT 子句，以及它那令人又爱又恨的“大偏移量”问题。准备好了吗？让我们一起揭开它的神秘面纱，找到驯服它的方法！
开场白：LIMIT 的诱惑与陷阱
LIMIT 子句，就像SQL中的“魔镜”，它能让你从浩瀚的数据海洋中精确地捞取你想要的几条信息。想象一下，你是一位考古学家，手握 LIMIT，就能精准地从历史的尘埃中挖掘出你心仪的文物，而不是被一堆没用的瓦砾淹没。
但是，这面“魔镜”也并非完美无瑕。当你在使用 LIMIT 的同时，还搭配了一个看似人畜无害的 OFFSET，尤其是当 OFFSET 变得巨大无比时，你可能会发现，你的查询就像蜗牛一样，慢得让你怀疑人生。🐌
那么，这究竟是怎么回事呢？让我们先来认识一下 LIMIT 和 OFFSET。
LIMIT 和 OFFSET：SQL 世界的“好基友”
LIMIT 用于限制查询结果返回的行数，而 OFFSET 则用于跳过指定数量的行。它们常常联袂演出，实现分页功能。
例如：
SELECT * FROM products LIMIT 10 OFFSET 20;这条SQL语句的意思是：从 products 表中，跳过前 20 行，然后选取接下来的 10 行。这就像你在书架上找书，先跳过前面两排，然后从第三排开始拿 10 本。
看起来很简单，对吧？但是，魔鬼就藏在细节里。
大偏移量：性能的“隐形杀手”
问题就出在 OFFSET 上。当 OFFSET 变得非常大时，数据库需要做大量无用功。它需要扫描并跳过所有 OFFSET 指定的行，即使这些行最终不会被返回。这就像你翻阅一本 1000 页的书，只为了看最后一页的某个段落，你还是得先把前面的 999 页都翻一遍！😤
这种“全部扫描，再丢弃”的行为，会极大地消耗 CPU、IO 和内存资源，导致查询性能急剧下降。
为什么会这样？数据库的内心独白
要理解这个问题，我们需要了解一下数据库的查询过程。
- 全表扫描或索引扫描： 数据库首先需要找到符合
WHERE条件的记录（如果没有WHERE条件，则需要扫描整个表）。 - 排序（如果需要）： 如果查询包含
ORDER BY子句，数据库需要对结果集进行排序。 - 跳过
OFFSET行： 数据库需要扫描并跳过OFFSET指定数量的行。 - 返回
LIMIT行： 数据库返回LIMIT指定数量的行。
问题就在第三步。即使你只需要 10 行数据，但如果 OFFSET 是 1000000，数据库也需要扫描并跳过前 1000000 行。这简直是 “搬起石头砸自己的脚”！🦶
实战演示：看看你的查询有多慢
为了更直观地展示这个问题，我们来做一个小实验。假设我们有一个名为 users 的表，包含 100 万条用户数据。
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255),
email VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 插入 100 万条数据 (这里省略了插入数据的代码)现在，我们执行以下查询：
SELECT * FROM users LIMIT 10 OFFSET 999990;这条查询的目的是获取最后 10 个用户。在没有优化的情况下，你可能会惊讶地发现，这条查询竟然需要几秒甚至几十秒才能完成！😱
优化方案：拯救你的查询速度！
既然我们找到了问题的根源，那么接下来就是解决问题的时候了。这里，我将向大家介绍几种常用的优化方案，助你摆脱大偏移量的困扰。
1. 利用索引：化繁为简，事半功倍
索引就像书的目录，可以帮助数据库快速定位到目标数据。如果你的查询包含 WHERE 或 ORDER BY 子句，并且这些字段上有索引，那么数据库可以利用索引来减少扫描的行数。
例如，如果我们在 created_at 字段上创建一个索引：
CREATE INDEX idx_created_at ON users (created_at);然后，我们将查询改写为：
SELECT * FROM users ORDER BY created_at LIMIT 10 OFFSET 999990;虽然仍然需要扫描大量数据，但由于使用了索引，查询速度会有所提升。
2. 子查询优化：曲线救国，另辟蹊径
这种方法的核心思想是：先通过子查询找到目标数据的 ID，然后根据 ID 获取完整的数据。
SELECT * FROM users WHERE id IN (SELECT id FROM users ORDER BY created_at LIMIT 999990, 10);或者，更高效的写法：
SELECT u.*
FROM users u
JOIN (SELECT id FROM users ORDER BY created_at LIMIT 999990, 10) sub
ON u.id = sub.id;这种方法可以避免全表扫描，因为它只需要扫描 LIMIT + OFFSET 行。
3. 书签记录法：记住上次的位置，下次再来
这种方法适用于需要频繁分页的场景。它的核心思想是：记录上次查询的最后一条数据的 ID 或其他唯一标识，下次查询时，直接从该位置开始。
例如：
第一次查询：
SELECT * FROM users ORDER BY created_at LIMIT 10;记录最后一条数据的 created_at 值，假设为 2023-10-27 10:00:00。
第二次查询：
SELECT * FROM users WHERE created_at > '2023-10-27 10:00:00' ORDER BY created_at LIMIT 10;这种方法避免了 OFFSET 的使用，大大提高了查询效率。
4. 倒序分页：反其道而行之，柳暗花明
如果你的排序字段是唯一的，或者可以接受倒序分页，那么可以考虑使用倒序分页。
例如：
SELECT * FROM users ORDER BY id DESC LIMIT 10 OFFSET 0; -- 第一页
SELECT * FROM users ORDER BY id DESC LIMIT 10 OFFSET 10; -- 第二页当需要访问后面的页码时，可以使用以下SQL获取指定页的数据：
SELECT * FROM (SELECT * FROM users ORDER BY id DESC LIMIT 1000000, 10) AS tmp ORDER BY id ASC;这种方法虽然看起来有点复杂，但可以避免扫描大量数据。
5. 游标分页：专业工具，高效稳定
游标分页是一种更高级的分页方式，它通过在服务器端维护一个游标，来跟踪查询的位置。游标分页可以避免 OFFSET 的使用，并且可以处理更复杂的分页场景。
但是，游标分页的实现比较复杂，需要数据库和应用程序的共同支持。
总结：选择最适合你的方案
以上介绍了几种常用的优化方案，每种方案都有其优缺点和适用场景。选择哪种方案，需要根据你的实际情况进行权衡。
| 优化方案 | 优点 | 缺点 | 适用场景 |
|---|---|---|---|
| 利用索引 | 提高查询速度，简单易用 | 效果有限，需要创建索引 | 查询包含 WHERE 或 ORDER BY 子句，并且这些字段上有索引 |
| 子查询优化 | 避免全表扫描，提高查询速度 | 略微复杂 | 适用于大多数场景 |
| 书签记录法 | 避免 OFFSET，提高查询速度 |
需要记录上次查询的位置，略微复杂 | 适用于需要频繁分页的场景 |
| 倒序分页 | 避免扫描大量数据 | 需要接受倒序分页，略微复杂 | 适用于排序字段是唯一的，或者可以接受倒序分页的场景 |
| 游标分页 | 高效稳定，可以处理复杂的分页场景 | 实现复杂，需要数据库和应用程序的共同支持 | 适用于需要处理大量数据，并且对性能要求较高的场景 |
额外提示：一些小技巧
- *避免使用 `SELECT `：** 只选择你需要的字段，可以减少 IO 负担。
- *使用 `COUNT()
预估数据量：** 在执行LIMIT和OFFSET之前，可以使用COUNT(*)预估数据量，避免OFFSET` 过大。 - 数据库调优： 调整数据库的配置参数，例如
innodb_buffer_pool_size，可以提高查询性能。
案例分析：一个真实的故事
曾经，我遇到过一个用户，他的电商网站的商品列表页面非常慢。经过分析，发现是因为使用了 LIMIT 和 OFFSET 进行分页，并且 OFFSET 非常大。
我建议他使用书签记录法进行优化。具体来说，我们在商品表中添加了一个 created_at 字段，用于记录商品的创建时间。在用户第一次访问商品列表页面时，我们查询前 20 个商品，并记录最后一个商品的 created_at 值。在用户点击下一页时，我们使用以下查询：
SELECT * FROM products WHERE created_at > 'last_created_at' ORDER BY created_at LIMIT 20;通过这种方式，我们避免了 OFFSET 的使用，大大提高了商品列表页面的加载速度。用户非常满意，我也感到非常自豪！😊
结尾：性能优化永无止境
LIMIT 和 OFFSET 的性能问题只是冰山一角。在实际开发中，我们还会遇到各种各样的性能问题。性能优化是一个永无止境的过程，需要我们不断学习、实践和总结。
希望今天的分享对你有所帮助。记住，优秀的程序员不仅要写出能运行的代码，还要写出高效的代码。让我们一起努力，成为更优秀的程序员！💪
谢谢大家！下次“SQL 性能奇妙夜”再见！👋