我击败了 MariaDB 优化器：从 94 秒到 55 毫秒

背景：从 MySQL 8 迁移到 MariaDB 11.4

一个从 MySQL 8 迁移到 MariaDB 11.4 的项目总体进展顺利。功能测试通过了，性能测试也通过了 — 除了一个查询。一个在 MySQL 8 上运行 2 秒的查询，在 MariaDB 11.4 上现在需要 94 秒。

这是一个典型的迁移回退：MySQL 和 MariaDB 的优化器自分叉以来已经显著分化。在一个引擎上运行良好的执行计划，在另一个上可能是灾难性的。

问题查询

原始查询使用经典的 LEFT JOIN 模式来查找另一张表中不存在的记录：

SELECT p.product_id, p.product_name, p.category_id
FROM products p
LEFT JOIN discontinued_products dp
    ON p.product_id = dp.product_id
    AND p.category_id = dp.category_id
WHERE dp.product_id IS NULL
  AND p.status = 'active'
  AND p.created_at >= '2023-01-01';

意图很明确：查找所有未出现在已停产产品表中的活跃产品。这是通过 LEFT JOIN + IS NULL 实现的"反连接"模式。

为什么需要 94 秒？

分析 MariaDB 11.4 上的执行计划揭示了问题。优化器选择了：

1. 使用 status 上的索引扫描 products 表（200,000 行）
2. 对于每一行，扫描 discontinued_products 表以验证是否不存在匹配

在 discontinued_products 表有 17,500 行的情况下，这意味着大约 35 亿次比较。MySQL 8 优化器选择了不同的计划，使用 hash join，对于这种模式要高效得多。

根本问题不在于 MariaDB 优化器 — 而在于查询本身。LEFT JOIN + IS NULL 来实现反连接是一个历史悠久的反模式，源自 SQL 没有更好替代方案的时代。

解决方案：CTE + EXCEPT

MariaDB 从 10.2 版本开始支持公共表表达式（CTE），从 10.3 版本开始支持 EXCEPT 运算符。这两个特性允许以更明确的方式重写逻辑：

WITH active_products AS (
    SELECT product_id, category_id
    FROM products
    WHERE status = 'active'
      AND created_at >= '2023-01-01'
),
still_active AS (
    SELECT product_id, category_id FROM active_products
    EXCEPT
    SELECT product_id, category_id FROM discontinued_products
)
SELECT p.product_id, p.product_name, p.category_id
FROM products p
JOIN still_active sa
    ON p.product_id = sa.product_id
    AND p.category_id = sa.category_id;

为什么更快

1. active_products CTE 将 200,000 个活跃产品物化到内存中。只需一次 products 表扫描。

2. EXCEPT 运算符 执行集合操作：它取活跃产品集合并减去出现在 discontinued_products 中的那些。这是一个基于哈希的集合差运算，而非逐行比较。

3. 与 still_active CTE 的最终 JOIN 是在一个已过滤集合上的简单查找。

结果：55 毫秒

从 94 秒到 55 毫秒。1,700 倍的提升。不是通过改变配置参数。不是通过添加索引。而是通过重新思考查询逻辑。

LEFT JOIN 反模式：为什么它持续存在

LEFT JOIN + IS NULL 的反连接模式无处不在。你可以在教程、在线课程、Stack Overflow 回答中找到它。它持续存在的原因有几个：

历史原因：在 SQL:1999 之前，没有 EXCEPT，没有优化的 NOT EXISTS，没有 CTE。LEFT JOIN + IS NULL 是唯一可移植的选项。

习惯：开发者学会一个有效的模式就会反复使用。"能用"是"最优"的敌人。

兼容性：LEFT JOIN 在所有数据库的所有版本上都能工作。EXCEPT 只在较新版本中支持。

需要了解的替代方案

对于反连接，有三种替代方案：

NOT EXISTS（通常是最佳选择）

SELECT p.product_id, p.product_name, p.category_id
FROM products p
WHERE p.status = 'active'
  AND p.created_at >= '2023-01-01'
  AND NOT EXISTS (
    SELECT 1 FROM discontinued_products dp
    WHERE dp.product_id = p.product_id
      AND dp.category_id = p.category_id
  );

NOT EXISTS 通常被 MariaDB 和 MySQL 引擎都能很好地优化。优化器可以使用反向半连接，比 LEFT JOIN 高效得多。

NOT IN（注意 NULL 值）

SELECT product_id, product_name, category_id
FROM products
WHERE status = 'active'
  AND created_at >= '2023-01-01'
  AND (product_id, category_id) NOT IN (
    SELECT product_id, category_id
    FROM discontinued_products
  );

注意：NOT IN 在遇到 NULL 值时行为诡异。如果 discontinued_products.product_id 可以为 NULL，NOT IN 语义会返回空结果。如果可能存在 NULL，请始终使用 NOT EXISTS。

EXCEPT（最具可读性）

SELECT product_id, category_id FROM products
WHERE status = 'active' AND created_at >= '2023-01-01'
EXCEPT
SELECT product_id, category_id FROM discontinued_products;

EXCEPT 是集合操作"A 减 B"最纯粹的表达。但它只返回操作涉及的列，不返回额外的列 — 因此需要使用 CTE 通过 JOIN 重新引入缺失的列。

教训

优化器是一个工具，不是万能的。当查询很慢时，第一个问题不应该是"我可以添加什么 hint？"或"我应该修改什么参数？"第一个问题应该是：我的查询是否正确表达了我的意图？

LEFT JOIN + IS NULL 表达的是"先连接再过滤不匹配的"。EXCEPT 直接表达"从那个集合中减去这个集合"。第二种表述更接近业务意图，给优化器找到高效计划的机会也大得多。

用数学家的思维而非程序员的思维来击败优化器。

本文最初发表于 Medium。