数据库基础体系 · 第 88/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。

MySQL SQL 实战：分页、批量、Upsert、JSON、窗口函数和锁定读

在后端系统中，SQL 很少只是“查询一行数据”。更常见的任务包括：

• 展示第 N 页数据；
• 持续拉取下一批数据；
• 一次写入或更新多行；
• 将半结构化属性存储在 JSON 中；
• 查询每个用户、每个分组的排名或最新记录；
• 多个 worker 并发领取任务而不重复处理。

这些需求都能写出“能运行”的 SQL，但不同写法在并发、索引、事务和故障恢复下的行为可能完全不同。

本文以 MySQL 8.4、InnoDB 为主要边界。除非特别说明，示例默认：

CREATE DATABASE IF NOT EXISTS demo
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

USE demo;

---

一、先建立共同基础：顺序、唯一性、事务和索引

后续的分页、批量写入、Upsert、窗口函数和锁定读，实际上都依赖几个基础概念。

1. 没有 ORDER BY，就没有稳定顺序

SQL 表是无序关系。下面的查询：

SELECT id, name
FROM users
LIMIT 10;

只表示“返回某 10 行”，不表示返回哪 10 行，也不保证下一次执行仍然是同 10 行。

即使当前执行计划每次都通过主键扫描，执行计划、统计信息、并发写入和数据页布局变化都可能改变返回顺序。

因此，只要业务语义涉及：

• 分页；
• 导出；
• 批处理；
• 增量同步；
• “最新”“最早”“排名前几”；

就应显式写出排序条件。

如果排序键可能相同，还需要加入唯一的次排序键。例如：

ORDER BY created_at DESC, id DESC

这里：

• created_at 决定主要业务顺序；
• id 保证同一时间的行也能获得确定顺序。

如果 created_at 和 id 的组合是唯一的，那么这个排序可以作为稳定游标。

---

2. 事务边界决定“批量”到底是什么

单条 SQL 和多条 SQL 的批量处理不是同一个概念。

在 InnoDB 中，关闭自动提交后，可以显式控制事务：

START TRANSACTION;

UPDATE account
SET balance = balance - 100
WHERE id = 1;

UPDATE account
SET balance = balance + 100
WHERE id = 2;

COMMIT;

如果第二条失败，应用可以：

ROLLBACK;

从而让两条更新一起回滚。

但如果连接处于默认的 autocommit=1，每一条独立的 INSERT 或 UPDATE 通常都会形成自己的事务。此时“循环执行 1000 条 SQL”不等于“1000 条 SQL 原子提交”。

需要区分三种批量：

1. 批量传输：减少客户端与服务器之间的往返；
2. 批量语句：一条 SQL 处理多行；
3. 批量事务：多条 SQL 在一个提交点完成。

它们可以同时存在，也可以单独存在。

---

3. 索引决定分页和批处理是否能按预期扩展

示例表：

CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    customer_id BIGINT NOT NULL,
    status ENUM('pending', 'paid', 'cancelled') NOT NULL,
    amount DECIMAL(12, 2) NOT NULL,
    created_at DATETIME(6) NOT NULL,
    updated_at DATETIME(6) NOT NULL,
    UNIQUE KEY uk_customer_created_id (customer_id, created_at, id),
    KEY idx_status_created_id (status, created_at, id)
) ENGINE = InnoDB;

对于：

WHERE status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 50

idx_status_created_id 可以同时帮助过滤和排序。

索引并不只是“让查询更快”。它还影响：

• MySQL 需要扫描多少记录；
• 锁定读会锁住哪些索引记录或间隙；
• LIMIT 能否尽早停止；
• 批处理是否会反复扫描已经处理过的数据。

可以使用：

EXPLAIN
SELECT id, customer_id, amount, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 50;

重点观察：

• key 是否选择了预期索引；
• rows 估算扫描行数；
• Extra 是否出现 Using filesort；
• 是否出现大量回表或过滤。

EXPLAIN 的估算不是实际执行结果。需要进一步确认时，可以使用：

EXPLAIN ANALYZE
SELECT id, customer_id, amount, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 50;

---

二、分页：Offset、Keyset 和一致性边界

2.1 Offset 分页：简单，但页码越深越昂贵

典型写法：

SELECT id, customer_id, amount, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 1000;

这表示跳过前 1000 行，再返回 50 行。

它的关键机制不是“直接跳到第 1001 行”。数据库通常仍需要按照排序顺序找到并检查前面的记录，然后丢弃它们。因此，随着 OFFSET 增大，扫描和排序成本通常也会增加。

插入导致的重复和遗漏

假设第一页返回：

created_at              id
2025-01-10 12:00:00     101
2025-01-10 11:59:00     100

客户端准备请求第二页时，另一事务插入了一条时间更晚的记录：

2025-01-10 12:01:00     200

第二页仍然使用：

LIMIT 2 OFFSET 2

新的排序结果变成：

200
101
100
...

第二页跳过前两行后，可能返回 100，导致原本第一页的 100 被重复看到；如果删除发生在前页，也可能导致某条记录被跳过。

因此，Offset 分页适合：

• 页数较浅；
• 数据变化不频繁；
• 用户确实需要跳到任意页；
• 对分页期间的严格一致性没有要求。

它不适合高频滚动列表、海量导出和持续批处理。

---

2.2 Keyset 分页：用上一页末尾作为游标

Keyset 分页不再告诉数据库“跳过多少行”，而是告诉数据库“从上一页末尾之后继续”。

对于降序排序：

ORDER BY created_at DESC, id DESC

第一页：

SELECT id, customer_id, amount, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 50;

假设第一页最后一行是：

created_at = '2025-01-10 10:00:00'
id         = 80

下一页使用：

SELECT id, customer_id, amount, created_at
FROM orders
WHERE status = 'paid'
  AND (
        created_at < '2025-01-10 10:00:00'
        OR (
            created_at = '2025-01-10 10:00:00'
            AND id < 80
        )
      )
ORDER BY created_at DESC, id DESC
LIMIT 50;

为什么这个条件正确

排序键是二元组：

(created_at, id)

降序顺序下，位于游标 (T, I) 之后的行必须满足：

created_at < T

或者：

created_at = T 且 id < I

因此条件是：

$$created\_at < T \lor (created\_at = T \land id < I)$$

这不是经验写法，而是对复合排序顺序的直接展开。

对于升序：

ORDER BY created_at ASC, id ASC

则使用：

WHERE created_at > :last_created_at
   OR (
        created_at = :last_created_at
        AND id > :last_id
      )

不能只用时间字段

错误写法：

WHERE created_at < :last_created_at
ORDER BY created_at DESC

如果多条记录拥有相同的 created_at，游标无法表达“同一时间点中已经读到哪一个 id”。结果可能漏行。

错误示例：

时间                    id
2025-01-10 10:00:00     80
2025-01-10 10:00:00     79
2025-01-10 09:59:00     78

如果第一页结束在 id=80，只使用 created_at < '2025-01-10 10:00:00'，会把 id=79 一起跳过。

---

2.3 Keyset 分页的完整示例

CREATE INDEX idx_orders_status_created_id
ON orders (status, created_at DESC, id DESC);

第一页：

SELECT id, customer_id, amount, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 3;

假定结果为：

id   created_at
101  2025-01-10 12:00:00
100  2025-01-10 11:59:00
80   2025-01-10 10:00:00

应用保存：

last_created_at = 2025-01-10 10:00:00
last_id         = 80

下一页：

SELECT id, customer_id, amount, created_at
FROM orders
WHERE status = 'paid'
  AND (
        created_at < ?
        OR (created_at = ? AND id < ?)
      )
ORDER BY created_at DESC, id DESC
LIMIT 3;

参数依次为：

2025-01-10 10:00:00
2025-01-10 10:00:00
80

分页游标应由服务端生成或签名，避免客户端随意修改游标造成越权读取。游标本身不提供事务一致性：如果分页期间数据更新，结果仍可能发生业务层面的变化。

---

2.4 需要“同一时刻视图”时怎么办

Keyset 解决的是定位效率和跨页重复/遗漏的一部分问题，但不保证所有页都来自同一个数据库快照。

如果需要导出任务看到固定数据边界，可以在任务开始时记录一个边界，例如：

SELECT MAX(id) AS max_id
FROM orders;

之后每一页都加：

AND id <= :max_id

这只在 id 单调递增并且业务允许按 id 作为边界时成立。更一般地，应记录符合排序规则的复合边界。

如果在一个长事务中反复查询，InnoDB 的普通非锁定读在一致性读语义下通常基于事务读视图，但长事务会带来：

• undo 保留时间变长；
• purge 延迟；
• 锁和资源持续时间变长；
• 事务失败时回滚成本增加。

因此，“长事务快照导出”和“短事务游标导出”是不同取舍，不能简单互换。

---

三、批量写入：减少往返不等于减少风险

3.1 多值 INSERT

表结构：

CREATE TABLE inventory (
    sku VARCHAR(64) PRIMARY KEY,
    quantity INT NOT NULL,
    updated_at DATETIME(6) NOT NULL
) ENGINE = InnoDB;

一次插入多行：

INSERT INTO inventory (sku, quantity, updated_at)
VALUES
    ('A-001', 10, CURRENT_TIMESTAMP(6)),
    ('A-002', 20, CURRENT_TIMESTAMP(6)),
    ('A-003', 30, CURRENT_TIMESTAMP(6));

这通常比发送三条独立 INSERT 减少客户端与服务器之间的往返次数。

但它仍然受到以下限制：

• SQL 文本或绑定参数不能无限增长；
• 连接器和服务器有包大小限制；
• 单条语句失败时，通常不能只提交其中部分行；
• 大事务会增加 undo、锁和恢复成本。

批量大小不应只按“行数”决定，还应考虑每行大小、索引数量、触发器和事务持续时间。

---

3.2 分块事务

应用层常见模型：

读取一批输入
开始事务
写入这一批
提交
处理下一批

对应 SQL 可能是：

START TRANSACTION;

INSERT INTO inventory (sku, quantity, updated_at)
VALUES
    ('A-001', 10, CURRENT_TIMESTAMP(6)),
    ('A-002', 20, CURRENT_TIMESTAMP(6)),
    ('A-003', 30, CURRENT_TIMESTAMP(6));

COMMIT;

如果执行失败：

ROLLBACK;

每个批次独立提交意味着：

• 已提交的批次不会因为后续批次失败而回滚；
• 失败后可以从最后一个确认成功的位置恢复；
• 但恢复逻辑必须保证重复执行安全。

这也是 Upsert 经常与批处理一起使用的原因。

---

3.3 批量读取与断点恢复

不要使用下面这种方式持续删除或处理：

SELECT id
FROM orders
ORDER BY id
LIMIT 100 OFFSET 100000;

更适合批处理的是主键游标：

SELECT id, customer_id, amount
FROM orders
WHERE id > :last_id
ORDER BY id ASC
LIMIT 100;

处理成功后，将本批最大 id 记录为新的断点。

但需要注意：id > last_id 只保证按 id 向前推进。它不保证：

• 处理期间新插入的更小 id 会被读取；
• 被修改后不再满足过滤条件的行仍会被处理；
• 业务顺序与 id 顺序一致。

如果要求固定处理范围，可以在开始时记录上界：

SELECT MAX(id) AS upper_id
FROM orders;

后续查询：

SELECT id, customer_id, amount
FROM orders
WHERE id > :last_id
  AND id <= :upper_id
ORDER BY id
LIMIT 100;

---

四、Upsert：插入与冲突更新的原子语义

4.1 Upsert 的前提是唯一约束

“如果不存在就插入，存在就更新”必须由数据库中的唯一键定义“存在”。

CREATE TABLE user_preferences (
    user_id BIGINT PRIMARY KEY,
    theme VARCHAR(32) NOT NULL,
    locale VARCHAR(16) NOT NULL,
    updated_at DATETIME(6) NOT NULL
) ENGINE = InnoDB;

这里 user_id 是唯一键，因此：

INSERT INTO user_preferences
    (user_id, theme, locale, updated_at)
VALUES
    (1001, 'dark', 'zh-CN', CURRENT_TIMESTAMP(6))
AS new
ON DUPLICATE KEY UPDATE
    theme = new.theme,
    locale = new.locale,
    updated_at = new.updated_at;

语义是：

1. 尝试插入 (1001, ...)；
2. 如果不违反唯一约束，插入成功；
3. 如果发生重复键，则更新冲突的已有行；
4. 整个语句在 InnoDB 中作为一个语句执行，并受事务边界控制。

这里使用了行别名 new 引用本次待插入的值。新版本语法应优先使用这种形式，而不是依赖旧的 VALUES(column) 写法。

---

4.2 Upsert 不是无条件覆盖

库存同步通常不是：

quantity = new.quantity

而是需要表达明确的业务规则。

例如，库存事件带有版本号：

CREATE TABLE product_stock (
    sku VARCHAR(64) PRIMARY KEY,
    quantity INT NOT NULL,
    source_version BIGINT NOT NULL,
    updated_at DATETIME(6) NOT NULL
) ENGINE = InnoDB;

只接受更高版本：

INSERT INTO product_stock
    (sku, quantity, source_version, updated_at)
VALUES
    ('A-001', 80, 12, CURRENT_TIMESTAMP(6))
AS new
ON DUPLICATE KEY UPDATE
    quantity = IF(new.source_version > source_version,
                  new.quantity,
                  quantity),
    source_version = GREATEST(source_version, new.source_version),
    updated_at = IF(new.source_version > source_version,
                    new.updated_at,
                    updated_at);

需要注意表达式中的 source_version 指已有行的列，new.source_version 指本次输入值。实际业务中应通过集成测试确认连接器、SQL 模式和受影响行数处理符合预期。

---

4.3 Upsert 的边界

多个唯一键可能带来复杂冲突

如果表上有多个唯一索引，一条待插入记录可能同时违反多个唯一约束。ON DUPLICATE KEY UPDATE 并不等于“按业务上最想要的唯一键自由选择一行”。设计时应尽量让冲突键唯一且明确，否则可能更新非预期记录，甚至在更新后再次触发其他唯一约束错误。

Upsert 不是幂等的同义词

下面的语句：

INSERT INTO counters (id, value)
VALUES (1, 5)
AS new
ON DUPLICATE KEY UPDATE
    value = value + new.value;

重复执行会累加两次，因此不是幂等操作。

而下面这种按版本覆盖的写法，在相同版本重复提交时更接近幂等：

value = IF(new.version > version, new.value, value)

幂等性取决于更新表达式和业务键，不是由 “Upsert” 这个语法自动提供的。

事务仍然重要

一条 Upsert 语句可以原子地处理自身的插入或更新，但如果业务还要写入审计表、消息表或其他表，仍应放入同一事务：

START TRANSACTION;

INSERT INTO product_stock (...)
VALUES (...)
AS new
ON DUPLICATE KEY UPDATE ...;

INSERT INTO stock_change_log (...);

COMMIT;

---

五、JSON：存储结构化值，但不能代替数据建模

5.1 JSON 列的基本语义

CREATE TABLE customer_profiles (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    customer_id BIGINT NOT NULL,
    attributes JSON NOT NULL,
    updated_at DATETIME(6) NOT NULL,
    UNIQUE KEY uk_customer (customer_id)
) ENGINE = InnoDB;

插入 JSON：

INSERT INTO customer_profiles
    (customer_id, attributes, updated_at)
VALUES
    (
        1001,
        JSON_OBJECT(
            'theme', 'dark',
            'language', 'zh-CN',
            'marketing', JSON_OBJECT('email', true)
        ),
        CURRENT_TIMESTAMP(6)
    );

查询字段：

SELECT
    customer_id,
    attributes->>'$.theme' AS theme,
    attributes->>'$.language' AS language
FROM customer_profiles
WHERE customer_id = 1001;

->> 返回去除 JSON 字符串引号后的结果。也可以使用：

JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.theme'))

SQL NULL、JSON null 和缺失字段不是一回事

例如：

SELECT
    JSON_EXTRACT('{"a": null}', '$.a') AS json_null,
    JSON_EXTRACT('{}', '$.a') AS missing;

前者表示 JSON 文档中存在 a，其值是 JSON null；后者表示路径不存在。应用层映射时不能简单把所有情况都当作同一种空值。

---

5.2 更新 JSON 中的局部路径

UPDATE customer_profiles
SET attributes = JSON_SET(
        attributes,
        '$.theme', 'light',
        '$.marketing.email', false
    ),
    updated_at = CURRENT_TIMESTAMP(6)
WHERE customer_id = 1001;

JSON_SET 会设置已有路径或创建路径。删除路径：

UPDATE customer_profiles
SET attributes = JSON_REMOVE(attributes, '$.marketing.email')
WHERE customer_id = 1001;

这些语句仍然会更新整行的事务版本，并可能触发行级锁、二级索引维护和日志写入。“只更新 JSON 的一个字段”不意味着数据库只写入一个字节，也不应据此推断固定的物理写放大。

---

5.3 JSON 查询与索引

直接写：

SELECT *
FROM customer_profiles
WHERE attributes->>'$.language' = 'zh-CN';

并不会自动拥有普通列一样的高效索引访问路径。

一种明确方式是生成列：

ALTER TABLE customer_profiles
ADD COLUMN language VARCHAR(16)
    GENERATED ALWAYS AS (
        JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.language'))
    ) STORED,
ADD INDEX idx_language (language);

之后：

SELECT customer_id, attributes
FROM customer_profiles
WHERE language = 'zh-CN';

生成列的索引值必须与表达式结果和类型设计一致。还要考虑：

• JSON 中路径缺失时生成什么值；
• 字符集和排序规则；
• 数字不要误定义成字符串；
• 路径数据是否真的稳定。

如果 JSON 是数组并需要判断数组元素，MySQL 还提供多值索引等能力，但语法、适用表达式和查询写法有明确限制，不能把普通 B-Tree 索引的经验直接套用到任意 JSON 路径。

---

5.4 JSON_TABLE：把 JSON 展开成关系行

假设订单属性如下：

{
  "items": [
    {"sku": "A-001", "qty": 2},
    {"sku": "A-002", "qty": 1}
  ]
}

可以使用：

SELECT
    p.customer_id,
    jt.sku,
    jt.qty
FROM customer_profiles AS p
JOIN JSON_TABLE(
    p.attributes,
    '$.items[*]'
    COLUMNS (
        sku VARCHAR(64) PATH '$.sku',
        qty INT PATH '$.qty'
    )
) AS jt
WHERE p.customer_id = 1001;

结果类似：

customer_id  sku     qty
1001         A-001   2
1001         A-002   1

这里发生了明确的数据流转换：

1. p.attributes 提供一个 JSON 文档；
2. $.items[*] 为数组中的每个元素产生一行；
3. COLUMNS 从每个元素提取关系列；
4. 外层查询再对这些行进行过滤、连接或聚合。

如果这些数组元素需要频繁连接、约束、统计或独立更新，通常应考虑拆成子表，而不是长期依赖每次查询时展开 JSON。

---

六、窗口函数：保留明细行的分组计算

窗口函数对一组相关行进行计算，但不会像 GROUP BY 那样把多行压缩为一行。

基本结构：

函数(...) OVER (
    PARTITION BY 分组列
    ORDER BY 排序列
    frame 子句
)

• PARTITION BY 划分窗口；
• ORDER BY 定义窗口内顺序；
• frame 定义当前行计算时包含哪些行。

---

6.1 每组最新一行

表：

CREATE TABLE user_events (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    event_type VARCHAR(32) NOT NULL,
    payload JSON NOT NULL,
    created_at DATETIME(6) NOT NULL,
    KEY idx_user_created_id (user_id, created_at DESC, id DESC)
) ENGINE = InnoDB;

需求：每个用户只取最新一条事件。

WITH ranked AS (
    SELECT
        id,
        user_id,
        event_type,
        payload,
        created_at,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY created_at DESC, id DESC
        ) AS rn
    FROM user_events
)
SELECT
    id,
    user_id,
    event_type,
    payload,
    created_at
FROM ranked
WHERE rn = 1;

为什么不能直接写：

SELECT ...
FROM user_events
WHERE ROW_NUMBER() OVER (...) = 1;

窗口函数是在查询逻辑处理中较晚的阶段计算的，不能直接作为同一层 WHERE 的普通行过滤条件。需要使用 CTE 或派生表先生成 rn，再由外层过滤。

加入 id DESC 很重要。若同一用户有两条事件时间相同，只有时间排序并不能确定哪一条是“最新”。

---

6.2 ROW_NUMBER、RANK 和 DENSE_RANK

假设分数如下：

user_id  score
1        100
2        90
3        90
4        80

SELECT
    user_id,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_number_rank,
    RANK()       OVER (ORDER BY score DESC) AS rank_value,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_value
FROM scores;

结果的排名区别：

score   ROW_NUMBER   RANK   DENSE_RANK
100     1            1      1
90      2            2      2
90      3            2      2
80      4            4      3

• ROW_NUMBER 给每行唯一序号；
• RANK 并列后会跳号；
• DENSE_RANK 并列后不跳号。

如果要求“每组严格取一行”，通常使用 ROW_NUMBER 并加入确定性的次排序键。

---

6.3 窗口累计值与 frame

SELECT
    user_id,
    created_at,
    amount,
    SUM(amount) OVER (
        PARTITION BY user_id
        ORDER BY created_at, id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM payments;

对于某个用户的金额：

10, 20, 5

结果为：

10, 30, 35

这里：

• UNBOUNDED PRECEDING 表示从该分组第一行开始；
• CURRENT ROW 表示计算到当前行；
• ROWS 按物理行计数。

如果排序值存在重复，RANGE 与 ROWS 的行为可能不同。需要逐行累计时，应明确写出 ROWS，并使用足够确定的排序条件。

---

6.4 分组 Top-N

需求：每个用户取金额最高的两笔支付。

WITH ranked AS (
    SELECT
        id,
        user_id,
        amount,
        created_at,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY amount DESC, id DESC
        ) AS rn
    FROM payments
)
SELECT id, user_id, amount, created_at
FROM ranked
WHERE rn <= 2;

这与全局：

ORDER BY amount DESC
LIMIT 2

完全不同。后者只返回全表前两行，不会为每个用户分别取两行。

窗口函数解决的是“先在每个分区内编号，再过滤”的问题，但排序和分区仍可能需要扫描、排序和临时资源。应通过 EXPLAIN 和 EXPLAIN ANALYZE 验证真实成本。

---

七、锁定读：把“读取并准备修改”变成并发协调

7.1 普通读取与锁定读取

InnoDB 中，普通 SELECT 通常是非锁定一致性读：

SELECT quantity
FROM inventory
WHERE sku = 'A-001';

它读取某个事务读视图，不会因为普通读取而锁住目标行。

锁定读：

SELECT quantity
FROM inventory
WHERE sku = 'A-001'
FOR UPDATE;

它要求在事务中读取并锁定符合条件的记录，使其他事务不能同时取得相冲突的锁并修改这些记录。

共享锁定读：

SELECT quantity
FROM inventory
WHERE sku = 'A-001'
FOR SHARE;

FOR SHARE 用于读取并持有共享锁。它与 FOR UPDATE 的兼容关系不同：多个共享锁可以共存，但排他修改需要等待。

锁定读必须放在显式事务中才有通常的业务意义：

START TRANSACTION;

SELECT quantity
FROM inventory
WHERE sku = 'A-001'
FOR UPDATE;

UPDATE inventory
SET quantity = quantity - 1,
    updated_at = CURRENT_TIMESTAMP(6)
WHERE sku = 'A-001'
  AND quantity >= 1;

COMMIT;

如果连接使用自动提交，锁定读所在语句结束后事务可能立即提交，锁的保护范围就无法覆盖后续的 UPDATE。

---

7.2 锁定读的正确用途：检查后修改

错误的并发模型：

事务 A：普通 SELECT quantity，得到 1
事务 B：普通 SELECT quantity，得到 1
事务 A：UPDATE quantity = 0
事务 B：UPDATE quantity = 0

结果可能是两次扣减只生效一次，形成丢失更新。

改为：

START TRANSACTION;

SELECT quantity
FROM inventory
WHERE sku = 'A-001'
FOR UPDATE;

事务 A 获得行锁后，事务 B 对同一行执行 FOR UPDATE 会等待，直到事务 A 提交或回滚。事务 B 随后读取到可用于锁定操作的最新版本，再决定是否继续更新。

更稳妥的方式是把条件放入更新语句：

START TRANSACTION;

UPDATE inventory
SET quantity = quantity - 1,
    updated_at = CURRENT_TIMESTAMP(6)
WHERE sku = 'A-001'
  AND quantity >= 1;

-- 由应用检查受影响行数：
-- 1 表示扣减成功，0 表示库存不足或记录不存在

COMMIT;

这避免了不必要的先读，但仍需要正确处理受影响行数和事务失败。

---

7.3 隔离级别与锁范围

InnoDB 的锁定行为与访问路径、隔离级别和索引条件有关。

在默认的 REPEATABLE READ 下，范围扫描可能涉及：

• 已存在的索引记录锁；
• 记录之间的间隙锁；
• 记录与间隙组合形成的 next-key lock。

例如：

START TRANSACTION;

SELECT id
FROM orders
WHERE status = 'pending'
  AND id BETWEEN 100 AND 200
FOR UPDATE;

这不是简单地“锁住返回结果”。数据库实际锁定范围与所选索引和扫描路径有关，可能影响范围内并发插入。

在 READ COMMITTED 下，InnoDB 通常减少部分间隙锁行为，但唯一性检查和外键检查等场景仍可能需要相关锁。不能仅凭隔离级别名称推断所有锁范围。

查看当前事务隔离级别：

SELECT @@transaction_isolation;

查看锁等待和事务信息可结合：

SHOW ENGINE INNODB STATUS;

以及 Performance Schema 中的锁和事务监控表。诊断死锁时，应记录：

• 事务执行的 SQL；
• 访问顺序；
• 使用的索引；
• 持锁时间；
• 死锁日志中的等待关系。

---

7.4 SKIP LOCKED：并发领取任务

建立任务表：

CREATE TABLE jobs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    status ENUM('ready', 'running', 'done') NOT NULL,
    available_at DATETIME(6) NOT NULL,
    worker_id VARCHAR(64) NULL,
    started_at DATETIME(6) NULL,
    KEY idx_jobs_ready (status, available_at, id)
) ENGINE = InnoDB;

多个 worker 可以使用：

START TRANSACTION;

SELECT id
FROM jobs
WHERE status = 'ready'
  AND available_at <= CURRENT_TIMESTAMP(6)
ORDER BY available_at, id
LIMIT 10
FOR UPDATE SKIP LOCKED;

该语句的含义是：

1. 按可执行时间和 id 找任务；
2. 尝试锁定前 10 个符合条件的任务；
3. 已被其他事务锁定的行直接跳过，而不是等待；
4. 当前事务得到一组锁定的任务 id。

然后标记任务：

UPDATE jobs
SET status = 'running',
    worker_id = :worker_id,
    started_at = CURRENT_TIMESTAMP(6)
WHERE id IN (:ids);

COMMIT;

之后在事务外执行实际工作，避免长时间持有数据库锁。

SKIP LOCKED 的代价

它不保证严格公平，也不保证每次返回固定的前 10 行：

• 被慢 worker 持有的任务会暂时被跳过；
• 某些任务可能长期得不到处理；
• 事务提交前，其他 worker 看不到这些任务已被领取；
• worker 在提交后崩溃，需要超时回收 running 状态。

回收逻辑示例：

UPDATE jobs
SET status = 'ready',
    worker_id = NULL,
    started_at = NULL
WHERE status = 'running'
  AND started_at < CURRENT_TIMESTAMP(6) - INTERVAL 10 MINUTE;

超时阈值必须大于正常任务处理时间，否则会出现两个 worker 同时处理同一任务。更可靠的设计还应包含租约版本、幂等业务键或处理结果去重。

如果不希望等待而希望立即报错，可以使用：

SELECT id
FROM jobs
WHERE status = 'ready'
ORDER BY available_at, id
LIMIT 10
FOR UPDATE NOWAIT;

具体错误应由应用捕获并按业务决定重试或返回。

---

7.5 锁定读不是“给查询结果加锁”

以下理解是不准确的：

SELECT ... FOR UPDATE 返回了 10 行，所以只锁了这 10 行。

真实锁范围取决于：

• 使用的索引；
• 查询条件是否唯一；
• 是否为范围扫描；
• 隔离级别；
• 优化器选择的访问路径；
• 外键和唯一性检查。

例如：

SELECT id
FROM orders
WHERE status = 'pending'
ORDER BY created_at
LIMIT 10
FOR UPDATE;

如果 status, created_at 没有合适索引，数据库可能扫描大量记录，并对扫描过程中符合锁定规则的索引记录施加锁，导致等待范围扩大。查询条件、排序和索引应一起设计，而不能只看返回行数。

---

八、把这些能力组合起来：一个可恢复的批处理流程

下面构造一个“扫描待处理订单并写入汇总表”的流程。

汇总表：

CREATE TABLE customer_daily_total (
    customer_id BIGINT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(18, 2) NOT NULL,
    order_count INT NOT NULL,
    updated_at DATETIME(6) NOT NULL,
    PRIMARY KEY (customer_id, order_date)
) ENGINE = InnoDB;

8.1 用 Keyset 读取一批数据

SELECT
    id,
    customer_id,
    DATE(created_at) AS order_date,
    amount
FROM orders
WHERE status = 'paid'
  AND id > :last_id
ORDER BY id ASC
LIMIT 500;

这里 id 是游标，last_id 是上一个成功批次的最大 id。

应用把返回的 500 行按 (customer_id, order_date) 聚合后，形成：

customer_id  order_date   total_amount  order_count
1001         2025-01-10   120.00         3
1002         2025-01-10   80.00          2

8.2 用 Upsert 写入汇总

START TRANSACTION;

INSERT INTO customer_daily_total
    (customer_id, order_date, total_amount, order_count, updated_at)
VALUES
    (1001, '2025-01-10', 120.00, 3, CURRENT_TIMESTAMP(6)),
    (1002, '2025-01-10', 80.00, 2, CURRENT_TIMESTAMP(6))
AS new
ON DUPLICATE KEY UPDATE
    total_amount = total_amount + new.total_amount,
    order_count = order_count + new.order_count,
    updated_at = new.updated_at;

COMMIT;

这里主键 (customer_id, order_date) 定义了冲突对象。

但该流程有一个重要边界：如果批处理在“写入汇总成功、记录断点失败”之间崩溃，重启后重新执行这一批会再次累加，结果错误。

因此，增量聚合不能仅凭“批量 + Upsert”获得幂等性。常见解决方向包括：

1. 为每个输入订单建立处理记录，并对 order_id 加唯一约束；
2. 汇总表保存已处理的输入范围，并在同一事务中更新；
3. 使用可重放但不重复累加的事件表；
4. 让汇总采用“按源数据重算”，而不是直接累加。

例如处理记录：

CREATE TABLE processed_orders (
    order_id BIGINT PRIMARY KEY,
    processed_at DATETIME(6) NOT NULL
) ENGINE = InnoDB;

只有成功插入 processed_orders 的订单才允许进入汇总。具体实现需要把“判断未处理、写处理记录、更新汇总”放在同一事务里，并正确处理并发冲突。

---

九、常见失败表现与诊断路径

9.1 深分页变慢

表现：

第一页很快，翻到几千页后明显变慢

检查：

EXPLAIN ANALYZE
SELECT ...
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 200000;

如果扫描行数远大于返回行数，说明 Offset 正在付出跳过成本。可以改为 Keyset，并建立匹配过滤和排序的索引。

---

9.2 分页重复或漏数据

优先检查：

• 是否缺少 ORDER BY；
• 排序键是否不唯一；
• 是否使用了稳定的复合游标；
• 查询期间是否发生插入、删除或排序字段更新；
• 是否需要固定快照或上界。

仅把 OFFSET 保存到客户端，不能解决动态数据集中的一致性问题。

---

9.3 批量写入超时或回滚时间长

检查：

SHOW VARIABLES LIKE 'max_allowed_packet';

同时检查：

• 单批 SQL 的字节大小；
• 单批行数；
• 事务持续时间；
• 二级索引数量；
• 是否有触发器；
• 是否发生锁等待。

减小批次只能降低部分资源压力，不能替代索引和事务设计。

---

9.4 Upsert 更新了错误的行

检查：

• 冲突的唯一键是哪一个；
• 是否存在多个唯一索引；
• 输入业务键是否真的唯一；
• 更新表达式是否区分了“新值”和“旧值”；
• 是否把受影响行数错误地当作插入/更新类型。

如果业务需要精确区分插入、更新、无变化，应结合应用测试和连接器文档验证受影响行数语义，不要只依赖某一个数字。

---

9.5 JSON 查询越来越慢

检查：

EXPLAIN
SELECT customer_id
FROM customer_profiles
WHERE language = 'zh-CN';

如果仍然扫描大量行：

• 确认生成列是否存在；
• 确认索引是否建立；
• 确认查询表达式是否与索引表达式匹配；
• 确认 JSON 路径和返回类型一致；
• 判断该字段是否已经稳定到应迁移为普通列。

JSON 适合扩展属性，不适合隐藏所有关系模型。需要唯一约束、频繁连接、范围查询和聚合的字段，通常应显式建模。

---

9.6 锁等待和死锁

死锁不是“数据库坏了”，而是多个事务形成了循环等待。例如：

事务 A：先锁 id=1，再锁 id=2
事务 B：先锁 id=2，再锁 id=1

诊断：

SHOW ENGINE INNODB STATUS;

处理方式包括：

• 让所有事务按相同顺序访问资源；
• 缩短事务；
• 为过滤和排序建立合适索引；
• 避免在持锁事务中执行远程调用；
• 应用捕获死锁错误并重试整个事务，而不是只重试其中一条 SQL。

死锁检测和等待超时都可能导致当前事务失败。重试必须重新开始事务，因为原事务的状态不能假定仍然有效。

---

十、选择哪种写法

可以用下面的判断来选择机制：

• 需要任意跳页：优先 Offset，但控制最大页深度；
• 需要长列表、滚动加载或导出：使用 Keyset；
• 需要稳定游标：排序键必须构成唯一顺序；
• 需要减少网络往返：多值 SQL 或批量参数；
• 需要原子性：显式事务；
• 需要重复执行不出错：设计真正的幂等键和更新表达式；
• 需要插入或冲突更新：先定义唯一约束，再使用 Upsert；
• 属性结构变化频繁：可以考虑 JSON；
• 属性需要高频过滤、连接或约束：考虑普通列或生成列索引；
• 需要每组排名、累计和、Top-N：使用窗口函数；
• 需要“读后修改”：使用事务中的锁定读，或直接使用带条件的原子更新；
• 多 worker 领取任务：使用 FOR UPDATE SKIP LOCKED，同时设计租约、超时回收和幂等处理。

这些语法解决的是不同层次的问题：分页解决数据定位，批量解决传输和提交组织，Upsert 解决唯一冲突，JSON 解决部分结构灵活性，窗口函数解决行级分析，锁定读解决并发协调。只有把它们与唯一约束、索引、事务和故障恢复放在一起设计，SQL 才能从“能够执行”变成“在真实并发环境中仍然正确”。

---

系列导航与关联阅读

• 系列入口：数据库完整学习路线：从关系模型、事务索引到分布式与向量检索
• 上一篇：MySQL 分区表：Range、List、Hash、裁剪、维护和适用边界
• 下一篇：MySQL 备份恢复：逻辑备份、物理备份、Binlog 与 PITR 演练
• 延伸：MySQL 查询优化：EXPLAIN、统计信息、Join、排序和慢日志
• 延伸：SQL 批处理与分页：游标、Keyset、批量写、限速和断点恢复

官方资料

• MySQL 8.4 Reference Manual
• MySQL InnoDB Storage Engine

本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。