SQLite - LIMIT 子句

控制查询结果：LIMIT 子句

LIMIT 子句是一个关键的 SQL 特性，用于限制查询返回的行数。其最常见的应用是实现分页，即将大型结果集分解成更小、更易于管理的“数据页”。

语法

基本语法限制了返回的总行数。

SELECT column_list
FROM table_name
[WHERE conditions]
[ORDER BY column_name]
LIMIT row_count;

您还可以使用 OFFSET 子句指定起始点，该子句在开始返回结果之前跳过指定数量的行。

SELECT column_list
FROM table_name
[ORDER BY column_name]
LIMIT row_count OFFSET offset_count;

ORDER BY 的关键作用

专业提示： 总是将 ORDER BY 子句与 LIMIT 一起使用。如果没有它，数据库不保证返回行的顺序。这意味着两次运行相同的查询可能会产生不同的结果，这会破坏分页并导致不可预测的行为。ORDER BY 子句确保结果集是稳定且可预测的。

示例：实现分页

让我们使用一个按姓名排序的示例 employees 表。

ID          NAME            AGE         SALARY
----------  --------------  ----------  ----------
2           Allen Johnson   25          60000.0
7           James Miller    24          52000.0
4           Mark Davis      26          98000.0
1           Paul Smith      32          75000.0
3           Teddy Brown     23          55000.0

要获取每页大小为 3 的第一页结果，我们使用不带偏移量的 LIMIT。

-- 获取按姓名排序的前 3 名员工
SELECT * FROM employees
ORDER BY name ASC
LIMIT 3;

这将产生：

ID          NAME            AGE         SALARY
----------  --------------  ----------  ----------
2           Allen Johnson   25          60000.0
7           James Miller    24          52000.0
4           Mark Davis      26          98000.0

要获取第二页，我们需要跳过前 3 条记录。我们将 LIMIT 设置为页大小（3），并将 OFFSET 设置为已经看过的记录数（3）。

-- 跳过前 3 名员工，获取接下来的 3 名
SELECT * FROM employees
ORDER BY name ASC
LIMIT 3 OFFSET 3;

这将产生下一组记录：

ID          NAME            AGE         SALARY
----------  --------------  ----------  ----------
1           Paul Smith      32          75000.0
3           Teddy Brown     23          55000.0

性能陷阱与替代方案

OFFSET 的性能问题

尽管 LIMIT ... OFFSET 易于实现，但在大型表上可能会变得非常慢。当您使用一个很大的偏移量（例如 OFFSET 100000）时，数据库仍必须在内部获取并计数所有 100,000 行，然后才能丢弃它们。这种方式扩展性不佳。

替代方案：键集（或“游标”）分页

对于“无限滚动”式分页，一种性能更高的方法是键集（keyset）分页。您不使用偏移量，而是使用 WHERE 子句来获取在您看到的最后一个项目之后的行。这需要一个唯一且有序的列（例如时间戳或 ID）。

例如，要获取“Mark Davis”（他在第一页中是最后一个，id 为 4）之后的下一页，我们可以这样做：

-- 最后一个看到的姓名是“Mark Davis”。获取他之后的 3 个姓名。
-- 这种方法使用索引，避免了扫描整个表。
SELECT * FROM employees
WHERE name > 'Mark Davis'
ORDER BY name ASC
LIMIT 3;

这种方法效率更高，因为它可以使用 name 列上的索引直接跳转到正确的起始点，避免了大量偏移量带来的昂贵扫描。

最佳实践总结

• 始终将 ORDER BY 与 LIMIT 结合使用，以确保结果具有确定性。
• 对于性能不关键的中小型数据集，使用 LIMIT 和 OFFSET 进行简单分页。
• 对于大型数据集或无限滚动用户界面（UIs），为了更好的性能，优先选择键集/游标分页而非大偏移量。
• 确保 ORDER BY 子句中的列已创建索引，以加快排序速度。