LIMIT 子句的性能问题与大偏移量优化方案

好的，各位观众老爷们，欢迎来到今天的“数据库冷知识大放送”节目！ 🥳 今天我们要聊的，是一个让无数程序员抓耳挠腮、捶胸顿足，又不得不面对的“老朋友”—— LIMIT 子句，以及它那让人头疼的 大偏移量性能问题，当然，我们还会拿出一些“屠龙之技”，来优化优化它！

准备好了吗？让我们开始吧！

第一幕：LIMIT 子句，你是我的温柔刀？

大家对 LIMIT 子句肯定不陌生，它就像一位绅士，温文尔雅地控制着数据库返回的结果数量。当你只想看前10条数据，或者想做个分页功能的时候，它简直是你的救星。

比如，你想从 users 表里取出前 10 个用户：

SELECT * FROM users LIMIT 10;

简单明了，就像清晨的第一缕阳光，让人心情舒畅。🌞

但这位“绅士”也有着阴暗面，尤其当它的 offset (偏移量) 变得很大时，它就会变成一把锋利的刀，狠狠地刺向你的数据库性能！

第二幕：大偏移量，性能的噩梦

想象一下，你要翻到微信朋友圈的第1000页，是不是要疯狂地往下滑动，滑到手抽筋？数据库也是一样！

SELECT * FROM users LIMIT 1000000, 10;

这条 SQL 语句的意思是：跳过前 100 万条数据，然后取出后面的 10 条。

问题来了：

数据库必须先找到这 100 万条数据！ 即使它最终不会返回给你，它也得辛辛苦苦地扫描、排序，然后默默地丢弃。这就像你辛辛苦苦地搬了 100 万块砖头，然后只用了最后 10 块，前面的 999990 块全扔了！简直是血亏啊！ 😭
IO 负担沉重！ 大量的数据读取，意味着大量的磁盘 IO 操作，这会严重拖慢数据库的响应速度。

这种现象，我们称之为 大偏移量性能问题。

第三幕：罪魁祸首是谁？

要解决问题，先要找到问题的根源。那么，为什么大偏移量会导致性能问题呢？

根本原因在于 MySQL 的 查询执行方式。当你使用 LIMIT offset, row_count 时，MySQL 通常会：

扫描整个表 (或者使用索引，但即使使用索引，也需要扫描大量的索引条目)。
跳过 offset 行。
返回 row_count 行。

这意味着，无论 offset 有多大，数据库都必须进行大量的扫描操作，而这些扫描操作大部分都是无用的！这就像你明明只想找一把钥匙🔑，却要把整个房间翻个底朝天！

第四幕：屠龙之技，优化方案大放送

既然找到了问题的根源，我们就可以对症下药，祭出我们的“屠龙之技”了！

方案一：书签法 (记住上次的位置)

这种方法的核心思想是：避免使用 offset，而是记住上次查询的位置。

假设你已经查询了第 10 页的数据，每页 10 条，那么下次查询第 11 页时，你就可以这样：

记录上次查询的最后一条数据的 ID 或其他唯一标识符，比如 last_id。
使用 WHERE 子句来过滤掉 last_id 之前的数据。

-- 查询第一页 (ID 从小到大排序)
SELECT * FROM users WHERE id > 0 ORDER BY id ASC LIMIT 10;

-- 记录第一页最后一条数据的 ID: 100
-- 查询第二页
SELECT * FROM users WHERE id > 100 ORDER BY id ASC LIMIT 10;

-- 记录第二页最后一条数据的 ID: 110
-- 查询第三页
SELECT * FROM users WHERE id > 110 ORDER BY id ASC LIMIT 10;

优点：

避免了大偏移量扫描，性能提升显著。
实现简单，容易理解。

缺点：

要求表中有一个唯一且有序的字段 (通常是自增 ID)。
如果数据被删除或修改，可能会导致数据跳跃或重复。
不适用于复杂的排序场景。

适用场景：

简单的分页场景，数据量大，且ID字段有序。

方案二：延迟关联 (先查 ID，再关联)

这种方法的核心思想是：先通过索引找到需要的 ID，然后再通过 ID 去查询完整的记录。

SELECT u.*
FROM users u
INNER JOIN (
    SELECT id FROM users ORDER BY id LIMIT 1000000, 10
) AS t ON u.id = t.id;

步骤分解：

子查询： SELECT id FROM users ORDER BY id LIMIT 1000000, 10 这个子查询只查询 ID，利用索引，减少了扫描的数据量。虽然仍然存在大偏移量的问题，但由于只查询 ID，IO 负担大大降低。
关联查询： INNER JOIN users u ON u.id = t.id 将子查询的结果 (ID 列表) 与原表进行关联，只查询需要的 10 条完整记录。

优点：

减少了 IO 负担，提高了查询效率。
适用于复杂的排序场景。

缺点：

需要进行两次查询，增加了查询的复杂性。
对于非常大的偏移量，子查询仍然会消耗大量资源。

适用场景：

需要复杂的排序，且无法使用书签法。

方案三：覆盖索引 (Index-Only Query)

这种方法的核心思想是：如果查询只需要索引中的字段，那么数据库就可以直接从索引中获取数据，而不需要回表查询。

假设你需要查询用户的姓名和邮箱，并且你已经创建了一个包含 name 和 email 字段的组合索引：

CREATE INDEX idx_name_email ON users (name, email);

那么，你可以这样查询：

SELECT name, email FROM users ORDER BY id LIMIT 1000000, 10;

由于 name 和 email 都在索引中，数据库可以直接从索引中获取数据，避免了回表查询，大大提高了查询效率。

优点：

避免了回表查询，性能提升显著。
实现简单，只需要创建合适的索引。

缺点：

只适用于查询索引包含的字段。
需要维护索引，增加了存储空间。

适用场景：

查询的字段都在索引中，且需要分页。

方案四：SQL优化器提示 (Force Index)

有时候，MySQL 的查询优化器可能会“犯傻”，选择了错误的索引，导致性能下降。我们可以使用 FORCE INDEX 提示来强制 MySQL 使用指定的索引。

SELECT * FROM users FORCE INDEX (idx_name) WHERE name LIKE '张%' LIMIT 1000000, 10;

这条 SQL 语句强制 MySQL 使用 idx_name 索引。

优点：

可以强制 MySQL 使用指定的索引，避免优化器“犯傻”。
灵活可控，可以针对特定的查询进行优化。

缺点：

需要对 MySQL 的查询优化器有一定的了解。
过度使用 FORCE INDEX 可能会导致性能下降。

适用场景：

MySQL 优化器选择了错误的索引，导致性能下降。

方案五：数据归档 (Archive Data)

如果你的表中包含大量的历史数据，而你只需要查询最新的数据，那么你可以考虑将历史数据归档到另一个表中。

这样可以大大减少原表的数据量，从而提高查询效率。

优点：

大大减少了原表的数据量，提高了查询效率。
可以将历史数据保存起来，以备将来使用。

缺点：

需要维护两个表，增加了维护成本。
需要定期进行数据归档。

适用场景：

表中包含大量的历史数据，且只需要查询最新的数据。

第五幕：总结与思考

好了，各位观众老爷们，今天的“数据库冷知识大放送”就到这里了。我们一起探讨了 LIMIT 子句的大偏移量性能问题，以及一些优化方案。

优化方案	核心思想	优点	缺点	适用场景
书签法	记住上次查询的位置	避免大偏移量扫描，性能提升显著，实现简单	要求表中有一个唯一且有序的字段，数据被删除或修改可能导致数据跳跃或重复，不适用于复杂的排序场景	简单的分页场景，数据量大，且ID字段有序
延迟关联	先查ID，再关联	减少 IO 负担，提高查询效率，适用于复杂的排序场景	需要进行两次查询，增加了查询的复杂性，对于非常大的偏移量，子查询仍然会消耗大量资源	需要复杂的排序，且无法使用书签法
覆盖索引	直接从索引中获取数据	避免回表查询，性能提升显著，实现简单	只适用于查询索引包含的字段，需要维护索引，增加了存储空间	查询的字段都在索引中，且需要分页
SQL优化器提示	强制MySQL使用指定的索引	可以强制 MySQL 使用指定的索引，避免优化器“犯傻”，灵活可控，可以针对特定的查询进行优化	需要对 MySQL 的查询优化器有一定的了解，过度使用 `FORCE INDEX` 可能会导致性能下降	MySQL 优化器选择了错误的索引，导致性能下降
数据归档	将历史数据归档到另一个表中	大大减少了原表的数据量，提高了查询效率，可以将历史数据保存起来，以备将来使用	需要维护两个表，增加了维护成本，需要定期进行数据归档	表中包含大量的历史数据，且只需要查询最新的数据

记住，没有万能的解决方案，只有最适合你的方案！ 你需要根据你的实际情况，选择合适的优化方案。甚至可以将多种方案结合起来使用，以达到最佳的性能效果。

最后，希望今天的节目能对你有所帮助。如果你觉得还不错，记得点赞、评论、转发哦！我们下期再见！ 👋

发表回复 取消回复

发表回复取消回复