MariaDB 优化器深度（独有特性）

Hash Join、Condition Pushdown、Derived Table Merge、Optimizer Trace——MariaDB 比 MySQL 强的地方

MariaDB 优化器与 MySQL 8 在很多场景已经走出不同的路。这一篇讲清 MariaDB 独有 / 更激进的优化，以及怎么诊断查询计划。

第一招：看清查询计划

-- 标准 EXPLAIN
EXPLAIN SELECT ...;

-- 详细
EXPLAIN FORMAT=JSON SELECT ...;

-- 实际跑一遍并给真实行数（MariaDB 独有）
ANALYZE FORMAT=JSON SELECT ...;

ANALYZE 比 EXPLAIN 多了 r_rows（real rows）、r_total_time_ms，调优必备。

一个完整例子

ANALYZE FORMAT=JSON
SELECT u.name, COUNT(o.id)
FROM users u LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > NOW() - INTERVAL 30 DAY
GROUP BY u.id;

输出 JSON 里关注：

r_loops：实际执行次数
r_rows：实际返回行数（vs rows 的估计）
r_total_time_ms：单步耗时
attached_condition：实际下推到这步的过滤条件

估计 vs 实际差距过大 → 统计信息过期，跑 ANALYZE TABLE。

Hash Join（MariaDB 10.6+ 默认开启）

传统 Nested Loop Join：

for each row in A (outer):
  for each row in B (inner):
    if A.x = B.y: emit

没索引时 O(N×M)。

Hash Join：

build hash table on B
for each row in A:
  probe hash table

O(N+M)，没索引时快得多。

-- 强制 hash join
SELECT /*+ HASH_JOIN(o) */ ... FROM users u JOIN orders o ON o.user_id = u.id;
-- 或全局开（默认已开）
SET optimizer_switch='hash_join_cardinality=on';

EXPLAIN Extra 列里看到 "Using join buffer (hash join)" 就是命中。

与 MySQL 8 对比：MySQL 8.0.18+ 也支持 hash join，但 MariaDB 优化器更激进地选用，且对子查询和派生表也能用。

Condition Pushdown

把 WHERE 条件推到存储引擎层，引擎扫描时直接过滤，不用把整行送回 server 层。

-- 自动启用
SELECT * FROM big_table WHERE updated_at > '2026-01-01' AND status = 'paid';

-- 看是否生效：EXPLAIN 的 attached_condition

对 InnoDB 和 ColumnStore 都有效，对 ColumnStore 提速尤其明显（少传一大堆列）。

Derived Table Merge

把派生表（FROM 子查询、CTE）自动合并到外层查询，避免临时表物化：

SELECT * FROM (
  SELECT user_id, MAX(created_at) AS last FROM orders GROUP BY user_id
) t WHERE t.last > NOW() - INTERVAL 7 DAY;

-- 优化器会改写成：
SELECT user_id, MAX(created_at) AS last FROM orders
GROUP BY user_id
HAVING last > NOW() - INTERVAL 7 DAY;

EXPLAIN 里不再看到 "DERIVED" 行就是 merge 成功了。复杂查询里这能省一大堆临时表。

Subquery Optimization

MariaDB 优化器对子查询有多种改写策略：

策略	何时	EXPLAIN 标识
Semi-Join	`IN (subquery)`	`Start temporary` / `LooseScan`
Materialization	`IN` 子查询结果不大	`MATERIALIZED`
Exists-to-In	`EXISTS` 改写	看不到，内部做
Subquery Cache	相关子查询	内存 cache

经验法则：写 SQL 时别强行优化子查询——优化器通常做得对。但长得不像优化器能识别的模式（如条件嵌套很深），可以手动改成 JOIN。

In-Order Index Merge

MariaDB 11.x 优化器可以同时用多个索引：

INDEX idx_user (user_id), INDEX idx_status (status);

-- 查询
SELECT * FROM orders WHERE user_id = 42 OR status = 'cancelled';

-- EXPLAIN type: index_merge, Extra: Using union(idx_user, idx_status)

但复合索引 (user_id, status) 几乎总是更好——index merge 是"没复合索引时的安慰奖"。

Optimizer Trace

看优化器为什么这么选：

SET optimizer_trace='enabled=on';
SELECT ... ;  -- 你的查询
SELECT * FROM information_schema.OPTIMIZER_TRACE\G
SET optimizer_trace='enabled=off';

输出是一大坨 JSON，记录了优化器考虑过的所有方案和选择理由。调优疑难杂症必备。

Optimizer Switch

SHOW VARIABLES LIKE 'optimizer_switch';

每个 flag 控制一个优化。常调的：

hash_join_cardinality=on — hash join 是否考虑（默认开）
condition_pushdown_for_derived=on — derived 内下推
condition_pushdown_for_subquery=on — 子查询下推
derived_merge=on — 派生表合并
engine_condition_pushdown=on — 存储引擎下推

临时关掉某个优化做 A/B 测试：

SET SESSION optimizer_switch='derived_merge=off';

索引提示

-- 强制用某索引
SELECT * FROM orders USE INDEX (idx_user_created) WHERE user_id = 42;

-- 忽略某索引
SELECT * FROM orders IGNORE INDEX (idx_status) WHERE ...;

-- 强制用某索引（更强）
SELECT * FROM orders FORCE INDEX (idx_user) WHERE ...;

用之前先 EXPLAIN 看自动选错没——优化器在 99% 的情况下选得对。

Histogram-based Cardinality

MariaDB 10.4+ 支持柱状图统计：

SET histogram_size=255;
ANALYZE TABLE orders PERSISTENT FOR ALL;

对高基数 + 分布不均的列效果显著（如 status 99% 是 paid，1% 是其他）。

经验排查清单

查询慢？按顺序问：

EXPLAIN 显示 type=ALL 吗？ → 缺索引
rows 估计远大于实际？ → 统计过期，跑 ANALYZE TABLE
JOIN 顺序怪？ → STRAIGHT_JOIN 试试
子查询慢？ → 看是否 materialize；改 EXISTS 试试
GROUP BY 慢？ → 看是否走索引（Using filesort 是警报）
LIMIT N 拉很多行？ → 看 r_rows，可能优化器读了一大堆才挑 N
ORDER BY 慢？ → 有没有适配的索引 (ORDER BY 列 DESC)
JOIN 没用 hash？ → MariaDB 10.6+ 默认开，老版本手动启

真实案例：用 ANALYZE 找到瓶颈

-- 慢查询：
SELECT u.name, COUNT(o.id) AS cnt
FROM users u JOIN orders o ON o.user_id = u.id
WHERE u.country = 'CN' GROUP BY u.id;

ANALYZE FORMAT=JSON 上述查询;

输出片段：

{
  "table": "u",
  "rows": 100000,
  "r_rows": 100000,
  "filtered": 30,
  "r_filtered": 12,
  "attached_condition": "u.country = 'CN'"
}

filtered=30 意味着优化器估计 30% 行符合 country='CN'，但 r_filtered=12 实际只 12%。估计偏差大 → 跑 ANALYZE TABLE users PERSISTENT FOR COLUMNS (country) INDEXES ALL。

与 MySQL 8 优化器对比

特性	MySQL 8	MariaDB 11
Hash Join	8.0.18+	10.6+ 更激进
Subquery Materialization	✅	✅
Derived Merge	✅	✅ 更彻底
Optimizer Trace	✅	✅
Histogram	✅	✅
Condition Pushdown to engine	✅	✅ 更全
`ANALYZE` 真实行数	❌	✅
Optimizer hints `/+ ... /`	✅ 多种	⚠️ 部分

结论：MariaDB 在没索引的大表场景上往往更快；MySQL 8 的 hint 体系更成熟。