SQL 等价改写优化案例集

范围说明:本文档只收录不新增索引、不改表结构、仅靠改写SQL本身就能拿到显著性能收益的案例。每个案例包含:问题场景、根因分析、改写方案、生效的前提条件、以及如何用 EXPLAIN 验证效果。默认以 MySQL 8.0 / InnoDB 为背景,个别案例会注明与 PostgreSQL 的差异。

---

案例一:深分页 —— 延迟关联(Deferred Join)

场景

订单表按状态筛选后翻到很靠后的页码,是后台管理系统里最常见的慢查询之一。

CREATE TABLE orders (
    id          BIGINT      PRIMARY KEY,
    customer_id BIGINT      NOT NULL,
    status      VARCHAR(20) NOT NULL,
    create_time DATETIME    NOT NULL,
    buyer_name  VARCHAR(50),
    address     VARCHAR(200),
    remark      VARCHAR(500)
    -- 其余字段省略,实际共30+列
);
CREATE INDEX idx_status_time ON orders(status, create_time);

SELECT *
FROM orders
WHERE status = 'shipped'
ORDER BY create_time
LIMIT 1000000, 20;

根因

idx_status_time 是二级索引,叶子节点只存 (status, create_time, id),不含其余字段。执行过程是: 1. 在二级索引上定位 status='shipped' 的记录,按 create_time 排序; 2. 每扫到一行,都要拿 id 回聚簇索引取完整行(回表); 3. 前 1,000,000 行取出来后直接丢弃,只保留最后 20 行。

问题的本质不是"排序慢",而是做了 1,000,020 次没必要的回表 I/O。

改写

SELECT o.*
FROM orders o
JOIN (
    SELECT id
    FROM orders
    WHERE status = 'shipped'
    ORDER BY create_time
    LIMIT 1000000, 20
) t ON o.id = t.id;

子查询的 SELECT 列表只有 id,而 id 已经包含在 idx_status_time 的叶子节点里,所以这一步是索引覆盖查询,全程不回表。丢弃前 1,000,000 行时丢的只是索引里的 (status, create_time, id) 三元组,而不是几十个字段的整行数据。最后只对剩下的 20 个 id 做回表,回表次数从百万级降到 20 次。

生效前提(必须同时满足,否则是无效甚至负优化)

• 排序/过滤字段命中的是二级索引,而不是主键本身(如果本来就是 ORDER BY id 无 WHERE,数据本就在聚簇索引里,延迟关联没有意义,详见后面的"反例");
• SELECT 的字段超出了索引覆盖范围,否则本来就不需要回表;
• 深分页页码确实很靠后(page 数很大)。如果只是翻第 2、3 页,收益不明显。

更彻底的方案:游标分页

如果业务允许"不能跳页,只能翻下一页"(多数信息流、导出场景符合),用游标分页直接替代 OFFSET,复杂度从 O(offset) 降到 O(1):

-- 记录上一页最后一条的 create_time 和 id 作为游标
SELECT *
FROM orders
WHERE status = 'shipped'
  AND (create_time, id) > ('2025-06-01 10:00:00', 123456)
ORDER BY create_time, id
LIMIT 20;

验证方法

对比 EXPLAIN 中的 rows(预估扫描行数)和实际执行时间;也可以用 EXPLAIN ANALYZE(MySQL 8.0.18+)直接看真实扫描行数和耗时。

---

案例二:对索引列做函数/表达式运算,导致索引失效(SARGable 改写)

场景

CREATE TABLE orders (
    id          BIGINT      PRIMARY KEY,
    customer_id BIGINT      NOT NULL,
    amount      DECIMAL(10,2),
    create_time DATETIME    NOT NULL
    -- 其余字段省略
);
CREATE INDEX idx_create_time ON orders(create_time);

SELECT * FROM orders
WHERE DATE(create_time) = '2025-06-01';

根因

DATE(create_time) 对索引列做了函数运算。InnoDB 的 B+树索引是按原始列值排序的,一旦对列施加了函数,优化器无法再利用索引的有序性做范围定位,只能逐行计算 DATE(create_time) 后再比较,等价于全表扫描(type: ALL)。这类问题极其隐蔽,因为语义完全正确,很多人写的时候根本意识不到自己"关闭"了索引。

同类写法还包括:

WHERE YEAR(create_time) = 2025
WHERE create_time + INTERVAL 1 DAY > NOW()
WHERE phone LIKE CONCAT('%', '1234')   -- 左模糊,同样无法走索引范围扫描

改写:把计算移到常量一侧,保持索引列"干净"

SELECT * FROM orders
WHERE create_time >= '2025-06-01 00:00:00'
  AND create_time <  '2025-06-02 00:00:00';

改写后索引列本身没有任何运算,优化器可以直接在 idx_create_time 上做范围扫描(type: range),只扫描目标区间内的行。这种改写通常能把一次全表扫描变成一次区间扫描,数据量越大、目标区间越窄,收益越夸张(百万级表上从秒级降到毫秒级是常态)。

验证方法

EXPLAIN 里 type 从 ALL 变为 range,key 列从 NULL 变成 idx_create_time,rows 大幅下降。

---

案例三:隐式类型转换导致索引失效

场景

CREATE TABLE users (
    id     BIGINT      PRIMARY KEY,
    phone  VARCHAR(20) NOT NULL,
    name   VARCHAR(50)
    -- 其余字段省略
);
CREATE INDEX idx_phone ON users(phone);

SELECT * FROM users WHERE phone = 13800001111;   -- 注意:没加引号,传的是整数

根因

phone 字段是字符串类型,但条件给的是数字字面量。根据 MySQL 的比较规则,字符串和数字比较时会把字符串转成数字再比较,也就是等价于:

WHERE CAST(phone AS SIGNED) = 13800001111

和案例二一样,索引列被套上了函数,索引失效,变成全表扫描。这个坑比案例二更隐蔽,因为 SQL 表面上"看起来完全正常",很多人不会想到自己没加引号导致了类型转换。

改写

SELECT * FROM users WHERE phone = '13800001111';

只是加了一对引号,但 EXPLAIN 的结果可能从 type: ALL 直接变成 type: ref。这是本文档里"改动最小、收益可能最大"的案例。

验证方法

EXPLAIN 观察 key 是否命中 idx_phone;也可以直接用 EXPLAIN FORMAT=JSON 看 attached_condition 里是否出现了隐式的 cast(...)。

---

案例四:相关子查询(Correlated Subquery)改写为 JOIN

场景:查询每个客户最新的一笔订单

CREATE TABLE customers (
    id   BIGINT PRIMARY KEY,
    name VARCHAR(50)
    -- 其余字段省略
);

CREATE TABLE orders (
    id          BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    amount      DECIMAL(10,2),
    create_time DATETIME NOT NULL
    -- 其余字段省略
);
CREATE INDEX idx_customer_time ON orders(customer_id, create_time);

SELECT c.id, c.name,
       (SELECT o.amount
        FROM orders o
        WHERE o.customer_id = c.id
        ORDER BY o.create_time DESC
        LIMIT 1) AS latest_amount
FROM customers c;

根因

子查询里引用了外层的 c.id,这是一个相关子查询:对 customers 表的每一行,都要重新执行一次子查询(哪怕子查询本身走了索引,也是"索引查找 × 客户数"级别的开销,而不是一次集合运算)。客户表几十万行时,这类写法经常直接拖成几十秒甚至超时。

改写方案一:窗口函数(推荐,MySQL 8.0+ / PostgreSQL)

SELECT id, name, latest_amount
FROM (
    SELECT c.id, c.name, o.amount,
           ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.create_time DESC) AS rn
    FROM customers c
    JOIN orders o ON o.customer_id = c.id
) t
WHERE rn = 1;

优化器只需要对 orders 按 customer_id 分组做一次排序(如果有 idx_customer_time(customer_id, create_time) 索引,甚至不需要额外排序),整个过程是一次集合运算,而不是逐行触发子查询。

改写方案二:LEFT JOIN + 反连接排除(不支持窗口函数时的老写法)

SELECT c.id, c.name, o1.amount AS latest_amount
FROM customers c
JOIN orders o1 ON o1.customer_id = c.id
LEFT JOIN orders o2
       ON o2.customer_id = c.id
      AND o2.create_time > o1.create_time
WHERE o2.id IS NULL;

思路是:o1 是候选行,如果找不到比它更新的 o2,说明 o1 就是最新的一条。这种写法在没有窗口函数的老版本 MySQL(5.7 及以下)上仍然常见,但在数据量大、每个客户订单数多时性能会明显劣于窗口函数方案,能升级版本的话优先用方案一。

验证方法

观察 EXPLAIN 里子查询是否显示为 DEPENDENT SUBQUERY(相关子查询的标志);改写后应该看不到这个标记,且 rows 总量从"外层行数 × 内层扫描行数"降为"一次 JOIN 的行数"。

---

案例五:跨列 OR 条件改写为 UNION ALL

场景

CREATE TABLE orders (
    id          BIGINT      PRIMARY KEY,
    customer_id BIGINT      NOT NULL,
    order_no    VARCHAR(32) NOT NULL,
    amount      DECIMAL(10,2),
    create_time DATETIME
    -- 其余字段省略
);
CREATE INDEX idx_customer ON orders(customer_id);
CREATE UNIQUE INDEX idx_order_no ON orders(order_no);

SELECT * FROM orders
WHERE customer_id = 10086 OR order_no = 'ORD20250601001';

根因

customer_id 和 order_no 分别有独立索引,但条件是跨列的 OR。MySQL 的优化器理论上支持 index_merge(把两个索引的结果做 union 再合并),但实际执行计划是否选择 index_merge 取决于统计信息、字段选择性等因素,很多时候优化器会保守地选择全表扫描,尤其是在两个字段选择性差异较大,或者表比较宽的时候。

改写

SELECT * FROM orders WHERE customer_id = 10086
UNION
SELECT * FROM orders WHERE order_no = 'ORD20250601001';

拆成两条独立查询后,每条都能确定性地分别命中 idx_customer 和 idx_order_no,不再依赖优化器是否愿意做 index_merge。如果能确定两个条件的结果集不会重参(比如像本例这种一个是客户ID一个是唯一订单号),用 UNION ALL 代替 UNION 可以省去去重的排序开销。

验证方法

改写前 EXPLAIN 观察 type 是否为 ALL 或 index_merge;改写后两条子查询应分别显示为 ref/const 级别。

---

案例六:NOT IN 子查询含 NULL 导致结果错误进而被迫全表扫描兜底

场景

CREATE TABLE customers (
    id   BIGINT PRIMARY KEY,
    name VARCHAR(50)
    -- 其余字段省略
);

CREATE TABLE orders (
    id          BIGINT PRIMARY KEY,
    customer_id BIGINT NULL,   -- 允许为空,部分历史数据存在脏数据
    amount      DECIMAL(10,2),
    create_time DATETIME
    -- 其余字段省略
);
CREATE INDEX idx_customer ON orders(customer_id);

SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

根因

这条 SQL 有两个问题叠加:

1. 正确性陷阱:只要子查询结果集里存在一个 NULL,整个 NOT IN 就会返回空集(三值逻辑:id <> NULL 结果是 UNKNOWN,而不是 TRUE),很多人排查了半天"为什么查不出数据",根源就在这里。
2. 性能陷阱:即便数据没有 NULL,NOT IN 子查询在很多版本的优化器下也难以有效利用索引,容易退化为全表扫描 + 逐行比对。

改写:用 NOT EXISTS

SELECT * FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

NOT EXISTS 不受 NULL 三值逻辑的影响(它判断的是"存在性"而不是"值相等"),语义上先天规避了正确性陷阱;同时 EXISTS/NOT EXISTS 更容易被优化器转换为半连接(semi join)/反连接(anti join),从而使用 orders.customer_id 上的索引做高效查找,而不是把整个子查询结果物化后再逐行比对。

验证方法

先验证结果集是否一致(这是本案例最重要的一步,因为原 SQL 可能本身就是错的);再对比 EXPLAIN,NOT EXISTS 版本通常会显示为反连接相关的执行方式,扫描行数显著下降。

---

案例七:自连接统计改写为窗口函数

场景:计算每个商品当天销量相对前一天的环比

CREATE TABLE daily_sales (
    product_id BIGINT  NOT NULL,
    sale_date  DATE    NOT NULL,
    amount     DECIMAL(12,2) NOT NULL,
    PRIMARY KEY (product_id, sale_date)
);

SELECT t1.product_id, t1.sale_date, t1.amount,
       t1.amount - t2.amount AS diff
FROM daily_sales t1
JOIN daily_sales t2
  ON t1.product_id = t2.product_id
 AND t2.sale_date = t1.sale_date - INTERVAL 1 DAY;

根因

自连接本质上是把一张表当成两张表做笛卡尔积再过滤,当 daily_sales 表很大(比如几千个商品 × 几年的每日数据)时,即使连接条件命中了索引,这种"表自己乘自己"的写法在执行计划复杂度和内存开销上都明显高于窗口函数,尤其当业务需求从"环比 1 天"扩展到"移动平均 7 天"时,自连接的写法会线性膨胀成 7 张表连接,而窗口函数几乎不用改动逻辑复杂度。

改写

SELECT product_id, sale_date, amount,
       amount - LAG(amount) OVER (
           PARTITION BY product_id ORDER BY sale_date
       ) AS diff
FROM daily_sales;

LAG() 窗口函数在一次有序扫描内就能拿到"上一行"的值,不需要再对表做二次自连接,执行计划从"两次表访问 + JOIN"简化为"一次排序 + 一次窗口计算"。表越大、需要对比的"前N天"跨度越长,相对自连接的收益越明显。

验证方法

对比两种写法在同一份数据量下的 EXPLAIN(自连接版本会出现两次对 daily_sales 的访问)和实际执行耗时。

---

案例八:循环单条 INSERT 合并为批量 INSERT

场景

应用层对同一张表循环执行单行插入,这是数据导入、批量任务里最常见的写法。

CREATE TABLE import_log (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    batch_id    BIGINT      NOT NULL,
    item_no     VARCHAR(32) NOT NULL,
    status      VARCHAR(20) NOT NULL,
    create_time DATETIME    NOT NULL
);

-- 应用代码里循环执行(伪代码示意):
-- for row in rows(共1000条):
--     INSERT INTO import_log (batch_id, item_no, status, create_time)
--     VALUES (?, ?, ?, NOW());

根因

每条 INSERT 都是一次独立的网络往返(round trip);如果连接是 autocommit 模式,每条语句还会各自触发一次事务提交,对应 InnoDB 的一次 redo log 刷盘(fsync)。1000 条单行插入意味着 1000 次网络延迟叠加 1000 次磁盘同步,哪怕单条 SQL 本身执行只要 1ms,这些"额外开销"累积起来也会远超语句本身的执行时间。这是典型的"SQL 本身没问题,但调用方式很浪费"的场景。

改写

INSERT INTO import_log (batch_id, item_no, status, create_time) VALUES
    (1001, 'A001', 'pending', NOW()),
    (1001, 'A002', 'pending', NOW()),
    (1001, 'A003', 'pending', NOW()),
    -- ... 一次性拼接多行
    (1001, 'A999', 'pending', NOW());

合并成一条多值 INSERT 后,网络往返从 1000 次降到 1 次,事务提交(redo log 刷盘)也从最多 1000 次降到 1 次。实践中,1000 行数据从"循环单条插入耗时数十秒"降到"批量插入几十到几百毫秒"是常见量级,是这份文档里投入产出比最高的改写之一。

生效前提

• 单条批量语句要控制大小(经验值每批 500~2000 行),不要无限拼接到几万行——一方面可能撞上 max_allowed_packet 限制,另一方面单个事务过大本身也有副作用(见前文"大事务"相关问题);
• 如果业务需要"某一行插入失败、其它行仍然成功"的细粒度错误处理,批量写入会改变失败的处理粒度,需要配合 INSERT IGNORE 或 ON DUPLICATE KEY UPDATE 等策略,不能简单地把循环体原样拼接。

验证方法

对比应用层记录的总耗时;数据库侧可以观察 SHOW STATUS LIKE 'Com_insert' 或慢查询日志里语句次数和事务提交次数的变化。

---

案例九:UPDATE 里的相关子查询改写为 JOIN UPDATE

场景:按促销表把命中商品的价格批量更新为促销价

CREATE TABLE products (
    id       BIGINT PRIMARY KEY,
    price    DECIMAL(10,2) NOT NULL,
    promo_id BIGINT NULL
);

CREATE TABLE promotions (
    id          BIGINT PRIMARY KEY,
    promo_price DECIMAL(10,2) NOT NULL
);

UPDATE products p
SET p.price = (
    SELECT pr.promo_price
    FROM promotions pr
    WHERE pr.id = p.promo_id
)
WHERE p.promo_id IS NOT NULL;

根因

SET 子句里的子查询同样是相关子查询:对 products 表里所有满足 promo_id IS NOT NULL 的行,数据库要逐行重新执行一次针对 promotions 的查询。即便 promotions.id 是主键、单次查找是 O(1),几十万商品行叠加起来仍然是几十万次独立的索引查找与执行器调度,而不是一次集合运算。

改写(MySQL 语法)

UPDATE products p
JOIN promotions pr ON pr.id = p.promo_id
SET p.price = pr.promo_price;

PostgreSQL 语法略有不同,用 FROM:

UPDATE products p
SET price = pr.promo_price
FROM promotions pr
WHERE pr.id = p.promo_id;

改写后数据库执行的是一次 products 与 promotions 之间的 JOIN,而不是"外层行数"次独立子查询。

生效前提

promotions.id 在连接键上必须对每个 promo_id 唯一匹配一行。如果右表在连接键上可能匹配多行,JOIN UPDATE 会导致同一行被多次更新,最终取值依赖数据库具体的执行顺序(未定义行为),这种情况必须先去重或加限定条件,不能直接套用这个改写。

验证方法

把 UPDATE 换成等价的 SELECT(SELECT p.id, pr.promo_price FROM products p JOIN promotions pr ON ...)先跑一遍 EXPLAIN,确认连接方式和扫描行数符合预期,再执行 UPDATE。

---

案例十:多个独立 COUNT 子查询合并为一次条件聚合

场景:首页统计卡片,展示订单表里不同状态的数量

CREATE TABLE orders (
    id          BIGINT PRIMARY KEY,
    status      VARCHAR(20) NOT NULL,
    create_time DATETIME NOT NULL
);

SELECT
    (SELECT COUNT(*) FROM orders WHERE status = 'pending')   AS pending_cnt,
    (SELECT COUNT(*) FROM orders WHERE status = 'shipped')   AS shipped_cnt,
    (SELECT COUNT(*) FROM orders WHERE status = 'completed') AS completed_cnt,
    (SELECT COUNT(*) FROM orders WHERE status = 'cancelled') AS cancelled_cnt;

根因

四条完全独立的子查询,数据库要对同一张 orders 表做四次独立的扫描/统计。如果 status 上没有索引(取值少、区分度低,团队通常也不会为它单独建索引),这四条子查询就是四次全表扫描,同一张表被反复扫了四遍。

改写:条件聚合(conditional aggregation),一次扫描算出所有统计值

-- MySQL:布尔表达式返回 1/0,SUM 求和即等价于计数
SELECT
    SUM(status = 'pending')   AS pending_cnt,
    SUM(status = 'shipped')   AS shipped_cnt,
    SUM(status = 'completed') AS completed_cnt,
    SUM(status = 'cancelled') AS cancelled_cnt
FROM orders;

-- PostgreSQL:用标准的 FILTER 子句,语义等价
SELECT
    COUNT(*) FILTER (WHERE status = 'pending')   AS pending_cnt,
    COUNT(*) FILTER (WHERE status = 'shipped')   AS shipped_cnt,
    COUNT(*) FILTER (WHERE status = 'completed') AS completed_cnt,
    COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled_cnt
FROM orders;

四次全表扫描合并成一次,I/O 直接降到大约四分之一(如果原本就有索引能高效执行单个 COUNT,合并的收益主要体现在减少重复的表访问和优化器调度开销,不会有全表扫描场景那么夸张,但依然是净收益)。这种写法可以很自然地扩展到十几个统计维度,而不需要写十几条子查询。

验证方法

EXPLAIN 里改写前会看到多个独立的执行分支,改写后应该只有一次对 orders 的访问;也可以直接对比两种写法的总耗时。

---

案例十一:巨型 IN 列表改写为 JOIN 派生表/临时表

场景:批量任务根据几万个订单 ID 查详情,应用层直接拼接成一条巨大的 IN 列表

CREATE TABLE orders (
    id     BIGINT PRIMARY KEY,
    amount DECIMAL(10,2),
    status VARCHAR(20)
);

-- id 列表是从上游系统批量拉取的,实际场景中可能有几万个
SELECT * FROM orders
WHERE id IN (10001, 10002, 10003 /* ... 省略,总共约5万个值 ... */, 60000);

根因

这类写法有两个隐患:第一,SQL 文本本身随 ID 数量线性膨胀,解析这条 SQL(词法分析、语法树构建)的开销会显著增加,如果这类查询在应用侧被频繁执行,SQL 解析本身就会成为不可忽视的开销;第二,超大 IN 列表容易撞上报文大小限制(如 MySQL 的 max_allowed_packet),或者让优化器在处理巨大常量列表时选择不理想的执行计划。

改写:把 ID 列表写入临时表,再做 JOIN

CREATE TEMPORARY TABLE tmp_ids (id BIGINT PRIMARY KEY);

-- 批量写入(可以配合案例八的多值 INSERT 一起使用)
INSERT INTO tmp_ids VALUES (10001), (10002), (10003) /* , ... 批量插入 */;

SELECT o.*
FROM orders o
JOIN tmp_ids t ON o.id = t.id;

这样一来,主查询的 SQL 文本保持短小、不再随 ID 数量线性膨胀,数据库也可以对 tmp_ids 做正常的统计和优化。

生效前提

这个改写主要在 IN 列表达到几千到几万量级时收益明显。如果只是几十上百个 ID,直接写 IN 列表更简单直接,额外建临时表反而增加了开销,得不偿失。

验证方法

对比应用层记录的 SQL 解析与网络传输耗时,以及是否还会触发报文大小相关的错误。

---

案例十二:全模糊 LIKE 按业务语义收窄为前缀匹配

场景:订单号搜索框,后端不假思索地套用了 %keyword%

CREATE TABLE orders (
    id       BIGINT PRIMARY KEY,
    order_no VARCHAR(32) NOT NULL
);
CREATE INDEX idx_order_no ON orders(order_no);

SELECT * FROM orders WHERE order_no LIKE '%20250601%';

根因

LIKE 的通配符出现在开头,索引的有序性完全用不上(B+树索引按字符串前缀排序,前缀不确定就无法定位区间),idx_order_no 形同虚设,退化为全表扫描。

如果去跟业务确认后发现:订单号本身有固定格式(比如"年月日+流水号"),这个搜索框实际只会被用来搜索"以某个日期开头"的订单,那问题的根源其实不是 SQL 写法有 bug,而是这条查询从一开始就不该用全模糊匹配。

改写

SELECT * FROM orders WHERE order_no LIKE '20250601%';

去掉开头的通配符后,LIKE 'xxx%' 可以走索引范围扫描(这是 SARGable 写法),优化器能直接定位到以 20250601 开头的区间,不再需要逐行扫描整张表。

生效前提(务必先确认,而不是直接改代码)

这个改写会改变查询语义——"包含"变成了"以某字符串开头"。如果业务确实需要支持"关键字可以出现在任意位置"的模糊搜索(比如商品名称搜索),就不能这么改,得考虑全文索引(FULLTEXT)或专门的搜索引擎(如 Elasticsearch)。这是本文档里唯一一个"表面是技术改写、实质是先纠正需求理解"的案例——遇到全模糊 LIKE 的性能问题,第一步永远是先确认业务真实诉求,而不是直接动手改 SQL。

验证方法

和业务确认语义不变后,EXPLAIN 观察 type 从 ALL 变为 range。

---

关于"反例":哪些场景改写没有意义甚至有害

严谨起见,列几个容易被误用的场景,提醒自己在应用上述技巧前先检查前提条件:

技巧	无效/有害的场景
延迟关联(案例一)	直接 ORDER BY 主键 且无 WHERE 条件——数据本就在聚簇索引里,子查询和外层扫的是同一棵树,多一次 JOIN 反而更慢
UNION 拆分 OR(案例五)	两个字段选择性都很差(比如状态字段只有3个值),拆分后每个分支扫描行数依然很大,收益有限
窗口函数替代自连接(案例七)	数据库版本不支持窗口函数(如 MySQL 5.7 及更早),需要先确认目标环境版本
NOT EXISTS 替代 NOT IN(案例六)	如果能确定子查询结果集一定不含 NULL(比如该列有 NOT NULL 约束),NOT IN 本身没有正确性问题,只是性能上仍建议优先 NOT EXISTS
批量 INSERT 合并(案例八)	单批拼接行数过多(比如几万行)会撞上 max_allowed_packet,也会让单个事务过大,需控制批次大小
相关子查询改 JOIN UPDATE(案例九)	右表在连接键上不唯一时,同一行可能被多次更新,结果依赖执行顺序,是未定义行为,必须先保证唯一性
巨型 IN 列表改临时表 JOIN(案例十一)	ID 数量只有几十上百个时,建临时表的固定开销比直接写 IN 列表还大,不划算
全模糊 LIKE 收窄为前缀匹配(案例十二)	业务确实需要"关键字可以出现在任意位置"的模糊搜索语义时不能这么改,需要全文索引或搜索引擎方案

---

通用验证方法论

1. 先用 EXPLAIN 看执行计划,重点关注:
   • type:优先级从好到差大致是 system > const > eq_ref > ref > range > index > ALL;
   • key:是否命中了预期的索引;
   • rows:优化器预估的扫描行数,改写前后对比最直观;
   • Extra:出现 Using filesort、Using temporary 通常意味着有额外的排序/临时表开销;Using index 表示命中了覆盖索引。
2. 有条件时用 EXPLAIN ANALYZE(MySQL 8.0.18+ / PostgreSQL 早已支持)看真实执行的行数和耗时,而不只是优化器的估算值——统计信息过期时,EXPLAIN 的估算可能与实际情况相差很大。
3. 在生产等量或近似量级的数据上测试,很多问题在小数据量下根本复现不出来(优化器在小表上可能直接选全表扫描,因为确实更快)。
4. 改写后必须做结果集比对,尤其是涉及 OR→UNION、NOT IN→NOT EXISTS 这类改动——语法上等价不代表语义上一定等价(如案例六的 NULL 陷阱),务必先验证正确性,再谈性能。