PostgreSQL `LIMIT` 子句完全指南

LIMIT 是分页与性能控制的利器，配合 OFFSET、ORDER BY 可实现 高效分页、Top-N 查询、采样、性能优化。
本文涵盖 语法、性能陷阱、Keyset 分页、索引优化、动态 LIMIT、常见误区 等全部内容。

一、`LIMIT` 基本语法

SELECT column1, column2, ...
FROM table_name
[WHERE ...]
[ORDER BY ...]
LIMIT row_count [OFFSET row_skip];

部分	说明
`LIMIT n`	最多返回 n 行
`OFFSET m`	跳过前 m 行
`LIMIT n OFFSET m`	第 m+1 到 m+n 行

二、核心示例表

CREATE TABLE sales (
    id BIGSERIAL PRIMARY KEY,
    product TEXT,
    amount NUMERIC(12,2),
    sale_date DATE,
    region TEXT
);

-- 插入 1000 条测试数据
INSERT INTO sales (product, amount, sale_date, region)
SELECT 
    'Product_' || (random() * 100)::INT,
    (random() * 10000)::NUMERIC(12,2),
    '2025-01-01'::DATE + (random() * 365)::INT,
    CASE WHEN random() > 0.5 THEN 'North' ELSE 'South' END
FROM generate_series(1, 1000);

三、基础 `LIMIT` 用法

需求	SQL
前 10 条	`SELECT * FROM sales LIMIT 10;`
跳过前 5 条，取 10 条	`SELECT * FROM sales LIMIT 10 OFFSET 5;`
等价写法	`SELECT * FROM sales LIMIT 10, 5;`（不推荐，易混）
只用 OFFSET	`OFFSET 100`（无 `LIMIT` → 跳过 100 行后返回全部）

-- 获取最新 5 笔销售（必须 ORDER BY）
SELECT * FROM sales 
ORDER BY sale_date DESC, id DESC
LIMIT 5;

警告：无 ORDER BY + LIMIT = 结果随机！

四、`LIMIT` + `ORDER BY`：Top-N 查询

-- 销售额最高的前 3 笔
SELECT product, amount FROM sales
ORDER BY amount DESC
LIMIT 3;

-- 每个地区最新 2 笔销售
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY region ORDER BY sale_date DESC) AS rn
    FROM sales
) t WHERE rn <= 2;

五、传统分页：`LIMIT` + `OFFSET`

-- 第 3 页，每页 10 条
SELECT * FROM sales
ORDER BY id
LIMIT 10 OFFSET 20;  -- (3-1)*10 = 20

性能陷阱（大数据量时）

页码	问题
第 1 页	快
第 100 页	慢！ PostgreSQL 仍需扫描前 990 行
第 10000 页	极慢！扫描 99990 行

EXPLAIN ANALYZE SELECT * FROM sales ORDER BY id LIMIT 10 OFFSET 99000;
-- 扫描 99010 行 → 性能灾难

六、推荐方案：Keyset 分页（Seek Method）

核心思想：用 WHERE 过滤 + ORDER BY 列值，而非 OFFSET

示例：按 ID 分页

-- 第一页（初始）
SELECT * FROM sales
WHERE id > 0
ORDER BY id
LIMIT 10;

-- 第二页（记住上一页最后一个 id = 10）
SELECT * FROM sales
WHERE id > 10
ORDER BY id
LIMIT 10;

示例：按日期 + ID 分页（复合键）

-- 第一页
SELECT * FROM sales
ORDER BY sale_date DESC, id DESC
LIMIT 10;

-- 第二页（记住最后一行：sale_date='2025-06-01', id=500）
SELECT * FROM sales
WHERE (sale_date < '2025-06-01')
   OR (sale_date = '2025-06-01' AND id < 500)
ORDER BY sale_date DESC, id DESC
LIMIT 10;

优势：

始终走索引

性能稳定（与页码无关）

支持“下一页”，不支持“上一页”（可用双向游标）

七、动态 `LIMIT`（应用层传入）

-- Python 示例
cur.execute(
    "SELECT * FROM sales ORDER BY id LIMIT %s OFFSET %s",
    (page_size, (page - 1) * page_size)
)

防注入：用参数化查询

八、性能优化：索引是关键

1. 为 `ORDER BY` 列建索引

-- 加速 ID 分页
CREATE INDEX idx_sales_id ON sales(id);

-- 加速复合排序
CREATE INDEX idx_sales_date_id ON sales(sale_date DESC, id DESC);

2. 覆盖索引（包含 SELECT 列）

-- 加速只选几列
CREATE INDEX idx_sales_cover ON sales(id, sale_date, amount, product);

九、`LIMIT` 在子查询 / CTE 中的应用

-- 每个地区销售额最高的一笔
WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
    FROM sales
)
SELECT region, product, amount
FROM ranked WHERE rn = 1;

十、采样与调试：`LIMIT` 是神器

-- 快速查看表结构
SELECT * FROM sales LIMIT 5;

-- 调试复杂查询
SELECT ... FROM ... WHERE ... LIMIT 100;

十一、常见误区与解决方案

误区	说明	解决方案
`LIMIT` 无 `ORDER BY`	结果随机	必须加 `ORDER BY`
`OFFSET` 大页码	性能崩塌	用 Keyset 分页
`LIMIT n, m` 语法	易混淆	推荐 `LIMIT m OFFSET n`
`LIMIT 0`	返回 0 行	用于测试语法
`LIMIT ALL`	等价于无 `LIMIT`	明确写 `LIMIT 100`

十二、最佳实践 Checklist

项目	建议
永远加 `ORDER BY`	保证顺序
小数据用 `OFFSET`	< 100 页
大数据用 Keyset	稳定性能
建排序索引	`ORDER BY` 列
覆盖索引	包含 `SELECT` 列
动态 LIMIT	参数化防注入
避免深分页	提示用户

十三、一键分页函数（Keyset）

-- 获取下一页
CREATE OR REPLACE FUNCTION get_sales_page(
    p_limit INT,
    p_last_date DATE DEFAULT NULL,
    p_last_id BIGINT DEFAULT NULL
)
RETURNS SETOF sales AS $$
BEGIN
    RETURN QUERY
    SELECT *
    FROM sales
    WHERE (p_last_date IS NULL)
       OR (sale_date < p_last_date)
       OR (sale_date = p_last_date AND id < p_last_id)
    ORDER BY sale_date DESC, id DESC
    LIMIT p_limit;
END;
$$ LANGUAGE plpgsql;

-- 使用
SELECT * FROM get_sales_page(10);  -- 第一页
SELECT * FROM get_sales_page(10, '2025-06-01', 500);  -- 第二页

十四、快速实战：5 分钟掌握 `LIMIT`

-- 1. Top 5 销售额
SELECT product, amount FROM sales
ORDER BY amount DESC LIMIT 5;

-- 2. 传统分页（第 3 页）
SELECT * FROM sales ORDER BY id LIMIT 10 OFFSET 20;

-- 3. Keyset 分页（第二页）
SELECT * FROM sales
WHERE id > 20
ORDER BY id LIMIT 10;

-- 4. 建索引
CREATE INDEX idx_sales_date_id ON sales(sale_date DESC, id DESC);

现在就动手：
在 sales 表中：

查询 最近 10 笔 销售（按 sale_date DESC）
实现 第 5 页（每页 10 条，传统方式）
改写为 Keyset 分页（记住最后一行的 sale_date 和 id）
建复合索引并用 EXPLAIN 验证性能提升

十五、性能对比（EXPLAIN ANALYZE）

-- 慢：深分页
EXPLAIN ANALYZE SELECT * FROM sales ORDER BY id LIMIT 10 OFFSET 90000;

-- 快：Keyset
EXPLAIN ANALYZE SELECT * FROM sales WHERE id > 90000 ORDER BY id LIMIT 10;

需要我生成：

10万条测试数据 + 分页压力测试？回复 测试数据
前后端 Keyset 分页完整代码？回复 Keyset 代码
无限滚动加载优化？回复 无限滚动
EXPLAIN 可视化分析？回复 EXPLAIN 分析

随时告诉我！

PostgreSQL LIMIT 子句

PostgreSQL `LIMIT` 子句完全指南

一、`LIMIT` 基本语法

二、核心示例表

三、基础 `LIMIT` 用法

四、`LIMIT` + `ORDER BY`：Top-N 查询

五、传统分页：`LIMIT` + `OFFSET`

性能陷阱（大数据量时）

六、推荐方案：Keyset 分页（Seek Method）

示例：按 ID 分页

示例：按日期 + ID 分页（复合键）

七、动态 `LIMIT`（应用层传入）

八、性能优化：索引是关键

1. 为 `ORDER BY` 列建索引

2. 覆盖索引（包含 SELECT 列）

九、`LIMIT` 在子查询 / CTE 中的应用

十、采样与调试：`LIMIT` 是神器

十一、常见误区与解决方案

十二、最佳实践 Checklist

十三、一键分页函数（Keyset）

十四、快速实战：5 分钟掌握 `LIMIT`

十五、性能对比（EXPLAIN ANALYZE）

PostgreSQL ORDER BY 语句

PostgreSQL NULL 值

PostgreSQL LOCK（锁）

PostgreSQL 索引

PostgreSQL 运算符

PostgreSQL 时间/日期函数和操作符

发表回复取消回复

PostgreSQL LIMIT 子句完全指南

一、LIMIT 基本语法

二、核心示例表

三、基础 LIMIT 用法

四、LIMIT + ORDER BY：Top-N 查询

五、传统分页：LIMIT + OFFSET

性能陷阱（大数据量时）

六、推荐方案：Keyset 分页（Seek Method）

示例：按 ID 分页

示例：按日期 + ID 分页（复合键）

七、动态 LIMIT（应用层传入）

八、性能优化：索引是关键

1. 为 ORDER BY 列建索引

2. 覆盖索引（包含 SELECT 列）

九、LIMIT 在子查询 / CTE 中的应用

十、采样与调试：LIMIT 是神器

十一、常见误区与解决方案

十二、最佳实践 Checklist

十三、一键分页函数（Keyset）

十四、快速实战：5 分钟掌握 LIMIT

十五、性能对比（EXPLAIN ANALYZE）

类似文章

发表回复 取消回复

PostgreSQL `LIMIT` 子句完全指南

一、`LIMIT` 基本语法

三、基础 `LIMIT` 用法

四、`LIMIT` + `ORDER BY`：Top-N 查询

五、传统分页：`LIMIT` + `OFFSET`

七、动态 `LIMIT`（应用层传入）

1. 为 `ORDER BY` 列建索引

九、`LIMIT` 在子查询 / CTE 中的应用

十、采样与调试：`LIMIT` 是神器

十四、快速实战：5 分钟掌握 `LIMIT`

发表回复取消回复