mariadb-query-optimization

MariaDB 查询优化

最后更新：2026-06-05

要求： 使用 ANALYZE 和直方图需要 MariaDB 10.1+；以下内容以截至 11.8 LTS（2025 年 5 月 GA）的优化器改进为基准。

默认上下文： 除非用户指定其他版本，否则假设使用 MariaDB 11.8 LTS。相关时可以建议使用标记为 12.x 的功能（包括作为升级选项），但务必注明最低版本——不要将其描述为 11.8 中可用的功能。

LLM 容易出错之处

模式	应采取的做法
SELECT * FROM table LIMIT 10 OFFSET 50000	使用基于游标的分页——OFFSET 会扫描所有被跳过的行
一概而论地说“对索引列使用函数会导致索引失效”	对 MariaDB 11.1+/11.3+ 中的许多情况来说，这种说法已经过时。对于不区分大小写的列，YEAR(col) = const 和 UPPER(col) = const 现在可以使用索引——请参阅下文的索引列上的函数
在低基数列（布尔值、只有 2-3 个值的状态列）上添加索引	优化器会跳过选择性低的索引，仍然执行全表扫描
批量插入后未运行 ANALYZE TABLE	直方图统计信息会过时；优化器会做出较差的执行计划选择
在 WHERE b = 1 AND c = 2 中使用复合索引 (a, b, c)	最左前缀规则：这里跳过了 a，因此不会使用该索引
在包含 JOIN 的查询中使用 SELECT *	只列出所需的列——避免意外阻止使用覆盖索引
使用 ALTER TABLE t ALTER INDEX idx INVISIBLE 禁用索引	这是 MySQL 语法。MariaDB 使用 IGNORED——请参阅下文的忽略索引（而非不可见索引）
在缓慢的服务器上直接使用 EXPLAIN 或创建索引	首先在启动时启用 Performance Schema——它默认关闭，且无法在运行时启用
使用 SET GLOBAL performance_schema = ON 启用监控	Performance Schema 需要在 my.cnf 中设置 performance_schema=ON，并重启服务器

Performance Schema（先启用）

在使用 EXPLAIN、索引或重写查询之前，先确认服务器能够观测查询的运行情况。MariaDB 的内置监控层 Performance Schema（10.5+：performance_schema 数据库中约有 80 个表）。

检查状态：

SHOW VARIABLES LIKE 'performance_schema';

重要： Performance Schema 默认禁用，且无法在运行时启用。如果状态为 OFF，请将以下内容添加到 my.cnf 并重启：

[mysqld]
performance_schema=ON

重启后，启用所需的消费者和监控项。在生产环境中，不要不加区分地全部启用，而应使用 WHERE NAME LIKE '...' 限定范围——请参阅概览文档：

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES';
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES';

使用 performance_schema（等待、阶段、语句及相关汇总表）查看时间花在哪里；然后使用下文的 EXPLAIN 和 ANALYZE 了解优化器为何选择该执行计划。在 10.7.1+ 中，列注释有助于理解这些表：

SELECT column_name, column_comment
  FROM information_schema.columns
 WHERE table_schema = 'performance_schema' AND table_name = 'events_statements_summary_by_digest';

阅读 EXPLAIN

诊断生产环境中的性能问题时，如果可以使用 Performance Schema，请运行 EXPLAIN 检查优化器的执行计划：

EXPLAIN SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 10;

输出中的危险信号：

字段	危险信号	含义
type	ALL	全表扫描——缺少索引或未使用索引
key	NULL	尽管存在索引，却未使用索引——检查列上是否有函数或类型是否不匹配
rows	数值非常大	优化器预计需要扫描许多行
Extra	Using filesort	索引未覆盖的高开销排序
Extra	Using temporary	创建了临时表——通常由 GROUP BY 或 DISTINCT 导致
Extra	Using index	✅ 良好——使用覆盖索引，无需访问表行

ANALYZE 语句（MariaDB 10.1+）会实际执行查询，并显示实际行数与估算行数的对比——比单独使用 EXPLAIN 更可靠。注意：MariaDB 使用 ANALYZE，而不是 EXPLAIN ANALYZE：

ANALYZE SELECT * FROM orders WHERE customer_id = 42;

Optimizer Trace 会显示优化器的完整决策过程。从 MariaDB 12.1 开始，跟踪信息可以包含完整的表和视图定义（optimizer_record_context 系统变量）。从 13.0 开始，还会包含用于基数估算的具体统计信息（直方图、索引统计信息）——结合使用，能有效诊断令人意外的 rows 估算值：

SET optimizer_trace = 'enabled=on';
SELECT * FROM orders WHERE customer_id = 42;
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
SET optimizer_trace = 'enabled=off';

索引规则

最左前缀规则

对于复合索引 (a, b, c)，MariaDB 可以使用：

• WHERE a = 1 ✅
• WHERE a = 1 AND b = 2 ✅
• WHERE a = 1 AND b = 2 AND c = 3 ✅
• WHERE b = 2 ✗——跳过了 a，未使用索引
• WHERE a = 1 AND c = 3——仅使用 a 部分

将选择性最高的等值条件放在前面，然后将范围条件放在最后：

-- Query: WHERE status = 'active' AND created_at > '2025-01-01' ORDER BY created_at
INDEX (status, created_at)  -- ✅ equality first, range last
INDEX (created_at, status)  -- ✗ range first breaks the prefix for status

覆盖索引

覆盖索引包含查询所需的所有列——无需访问表行（EXPLAIN 中显示 Using index）：

-- Query fetches id, status, created_at for a customer
-- Covering index includes all three:
CREATE INDEX idx_customer_cover ON orders (customer_id, status, created_at);
-- Now EXPLAIN shows: Extra = Using index

不应添加索引的情况

• 基数较低的列：status 列的值为 active/inactive，会影响 50% 的行——优化器更倾向于全表扫描。仅当与其他高选择性列组合时，索引才有用。
• 小表（少于几千行）：对于很小的表，全表扫描比索引查找更快。
• 写入频繁的列：每个索引都会拖慢 INSERT、UPDATE、DELETE——不要为很少查询的列创建索引。

被忽略的索引（而非 INVISIBLE）

若要让优化器跳过某个索引而不删除它——这在移除索引前测试该索引是否确有必要时很有用——MariaDB 使用 IGNORED，而不是 MySQL 的 INVISIBLE：

-- ✅ MariaDB syntax (10.6+):
ALTER TABLE demo ALTER INDEX index_name IGNORED;
ALTER TABLE demo ALTER INDEX index_name NOT IGNORED;  -- re-enable

-- ✗ MySQL syntax — fails on MariaDB:
ALTER TABLE demo ALTER INDEX index_name INVISIBLE;

写入时仍会维护该索引；它只是对优化器隐藏。主键不能被忽略。请参阅被忽略的索引。

索引列上的函数

经典规则“在索引列上使用任何函数都会禁用索引”对于 MariaDB 11.1+ 和 11.4 LTS 来说已经过时。现在，优化器可以为一些常见的函数模式使用索引：

模式	是否使用索引？	起始版本
WHERE YEAR(col) = 2025	✅ — 可搜索，能选择正确的范围	11.1+ (MDEV-8320)
WHERE DATE(col) <= '2025-12-31'	✅ — 可搜索	11.1+ (MDEV-8320)
在不区分大小写的排序规则（例如 utf8mb4_uca1400_ai_ci）上使用 WHERE UPPER(varchar_col) = '...'	✅ — 默认启用 sargable_casefold=ON	11.3+ (MDEV-31496)
WHERE SUBSTR(col, 1, n) = 'abc'	✅ — 已优化前缀 SUBSTR	11.8+ (MDEV-34911)
WHERE LOWER(case_sensitive_col) = '...'	✗ — 不使用索引（排序规则不区分大小写）	—
WHERE CAST(col AS UNSIGNED) = 1 或其他会改变类型的转换	✗ — 不使用索引	—

对于优化器仍无法转换为可搜索条件的情况，改写为范围查询的模式仍然有效：

WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'

请使用 EXPLAIN 验证，而不要想当然：在 11.4+ 中，许多“不会使用索引”的改写现在都没有必要。如果对于可搜索模式，EXPLAIN 仍显示 type=ALL，请检查 @@optimizer_switch 中的 sargable_casefold，并确认列的排序规则是 _ci。

函数索引：使用生成列

MySQL 8.0.13+ 支持函数键部分，可使用双括号语法直接为表达式创建索引。MariaDB 不支持此功能：

-- ✗ MySQL syntax — fails on MariaDB:
CREATE INDEX idx_upper ON users ((UPPER(name)));
ALTER TABLE orders ADD INDEX ((total * quantity));

在 MariaDB 中，应改为为生成列（计算列）创建索引。VIRTUAL 列不存储数据，而是在读取时计算；对其创建的索引会持久化表达式的值，并据此进行搜索：

-- ✅ MariaDB equivalent:
ALTER TABLE users
  ADD COLUMN name_upper VARCHAR(255) AS (UPPER(name)) VIRTUAL,
  ADD INDEX idx_name_upper (name_upper);

-- Query the column the optimizer can resolve directly,
-- or rely on virtual-column optimizer support (11.8+) for WHERE UPPER(name) = ...
SELECT * FROM users WHERE name_upper = 'ALICE';

请注意，对于许多简单情况（YEAR(col)、在 _ci 排序规则上使用 UPPER(col)），MariaDB 11.1+ 已不再需要对表达式创建索引——参见上表。当表达式本身无法转换为可搜索条件时，再考虑使用生成列。

分页：使用基于游标的方式，而不是 OFFSET

OFFSET 是一个隐蔽的性能陷阱。LIMIT 10 OFFSET 50000 会在每次加载页面时扫描并丢弃 50,000 行。

-- ✗ Slow — scans 50,000 rows to skip them:
SELECT id, title FROM posts ORDER BY id DESC LIMIT 10 OFFSET 50000;

-- ✅ Fast — index seek directly to the cursor position:
-- First page:
SELECT id, title FROM posts ORDER BY id DESC LIMIT 10;

-- Next page (pass last id from previous result as $last_id):
SELECT id, title FROM posts WHERE id < $last_id ORDER BY id DESC LIMIT 10;

对于带筛选条件的查询，应将筛选列与 id 一起纳入索引：

-- Query: WHERE category = 'news' ORDER BY id DESC
CREATE INDEX idx_cat_id ON posts (category, id);
-- Cursor query:
SELECT id, title FROM posts WHERE category = 'news' AND id < $last_id ORDER BY id DESC LIMIT 10;

要判断是否还有下一页，可使用 LIMIT 11，并检查是否出现第 11 行。

直方图统计信息

直方图能让优化器了解非索引列上的数据分布，这对复杂查询的执行计划质量至关重要。没有直方图时，优化器会假设数据均匀分布，因此可能选择错误的联接顺序。

-- Collect histograms for a table (requires a full scan — run during low traffic):
ANALYZE TABLE orders;

-- Verify histograms were collected:
SELECT * FROM mysql.column_stats WHERE table_name = 'orders';

应运行 ANALYZE TABLE 的情况：

• 批量插入或大量更改数据后
• EXPLAIN 显示的 rows 估算值异常偏高时
• 初次创建表并加载数据后

对于数据分布高度倾斜的表，应调整直方图粒度：

SET histogram_size = 100;  -- default is 0 (disabled) in older versions, 254 in 10.4.3+
ANALYZE TABLE orders;

使用 ANALYZE TABLE 且 histogram_size > 0 时，会自动按列收集直方图。直方图存储在 mysql.column_stats 中，并会在 optimizer_use_condition_selectivity >= 4 时使用（10.4.1+ 中的默认值）。

MariaDB 优化器开关

MariaDB 优化器的可调标志比 MySQL 更多。以下是对开发者最有用的标志：

-- See current settings:
SELECT @@optimizer_switch\G

-- Disable a specific optimization for a session (useful for debugging):
SET optimizer_switch = 'derived_merge=off';

-- Re-enable:
SET optimizer_switch = 'derived_merge=on';

影响最大的标志：

标志	默认值	作用
derived_merge	on	将派生表合并到外层查询中，通常能提升速度
semijoin	on	优化 IN/EXISTS 子查询；禁用它可排查异常的执行计划
subquery_cache	on	缓存相关子查询的结果；对重复执行的子查询提升显著
rowid_filter	on	在获取行之前预筛选 rowid；有助于范围查询
mrr	off	多范围读取；在旋转磁盘上执行大型范围扫描时可启用

每次关闭一个标志，以确定是哪个优化导致执行计划不佳；然后，如果默认设置产生的执行计划比替代方案更差，请通过 JIRA 报告。

10.7–10.11 LTS 阶段的优化器改进

10.11 LTS 系列整合了 10.7–10.10 短期版本中推出的功能：

• JSON 格式的直方图（10.8+、MDEV-21130、MDEV-26519）——直方图统计信息以 JSON 格式存储，比旧的二进制格式更精确。在 10.8+ 上只需运行 ANALYZE TABLE，即可让优化器获得更好的基数估算。
• 降序索引（10.8+、MDEV-13756）——支持 CREATE INDEX idx ON t (a ASC, b DESC)；适用于复合 ORDER BY a, b DESC 模式，以及对降序索引执行 MIN()/MAX()。
• SHOW ANALYZE [FORMAT=JSON]（10.9+、MDEV-27021）——无需干扰，即可获取在另一个连接中运行的查询的优化器计划和运行时统计信息。还支持 EXPLAIN FOR CONNECTION 语法（MDEV-10000）。
• 改进了对含有许多 eq_ref 表的连接的优化（10.10+、MDEV-28852、MDEV-26278）——大型星型模式连接的查询计划有了显著改善。
• ANALYZE FORMAT=JSON 报告优化器本身耗费的时间（11.0+、MDEV-28926）——将计划生成时间与执行时间区分开来。

11.4 LTS 中的优化器改进

11.4 LTS 系列继续推进优化器改造：

• 新的基于成本的成本模型（11.0+）——以经过调优、能感知 SSD 和各存储引擎特性的模型，取代旧的基于规则的启发式方法。同一查询在 10.6 与 11.4 中的 EXPLAIN 结果和连接顺序可能有明显差异。如果你在 10.x 中手动设置了 optimizer_adjust_secondary_key_costs，请删除这些设置——它们在 11.4+ 中不起作用。
• 针对单表 UPDATE/DELETE 的半连接优化（11.1+、MDEV-7487）——UPDATE/DELETE 中的子查询现在可以使用与 SELECT 相同的子查询改写方式（物化、半连接等）。通常能大幅提速，无需改写查询。
• 针对常量的可搜索 DATE/YEAR 比较（11.1+、MDEV-8320）——请参见上文的索引列上的函数。
• 可搜索的大小写折叠（11.3+、MDEV-31496，默认启用 sargable_casefold）——对使用不区分大小写排序规则的列应用 UCASE/LCASE/UPPER/LOWER 时，可以使用索引。

11.5–11.8 LTS 中的优化器改进

以下功能属于当前 LTS 基线，可帮助你了解优化器目前的能力：

• 对分区表执行索引条件下推（11.5+、MDEV-12404）——此前分区表无法使用 ICP；现在可以了，在分区架构上通常能大幅提速
• ANALYZE 显示下推索引条件的选择性（11.5+、MDEV-18478）——有助于诊断 ICP 是否发挥作用
• 默认启用字符集收窄优化（11.8+、MDEV-34380）——消除 WHERE 子句中不必要的字符集转换
• SUBSTR(col, 1, n) = const_str 优化（11.8+、MDEV-34911）——即使条件使用 SUBSTR 提取前缀，优化器现在也可以使用列索引
• 优化器支持虚拟列（11.8+、MDEV-35616）——请参见优化器对虚拟列的支持；此前优化器基本无法识别虚拟列
• 针对单表 UPDATE/DELETE 的基于成本的子查询策略（11.8+、MDEV-25008）——优化器现在会根据成本选择子查询策略

MariaDB 12.x 中的优化器改进

在 12.x 滚动发布版本中，进一步消除了若干限制：

• 反向排序扫描中的 Rowid 过滤（12.0+）——以前，ORDER BY ... DESC 查询无法利用 Rowid 过滤；现在可以了
• 反向排序扫描中的索引条件下推（12.0+）——ICP 也修复了同样的问题
• 松散索引扫描（“使用索引进行分组”）支持 DESC 键部分（12.0+）——以前要求使用 ASC 索引
• GROUP BY / ORDER BY 可使用虚拟列上的索引（12.1+）
• 可重排的 LEFT JOIN 优化（12.3+）——优化器现在可以重排更多 LEFT JOIN 组合
• 推断 DISTINCT GROUP BY 列（12.2+）——系统会识别带有 GROUP BY 的派生表具有不同的分组键，从而支持下游进行更多优化

如果你的目标版本是 11.8 LTS，并且发现反向排序或虚拟列查询的执行计划显得不必要地缓慢，可能是上述某项限制所致——请在 12.x 版本上运行相同的查询进行验证。

优化器提示

MariaDB 12.0 引入了一套全面的 MySQL 8 风格优化器提示框架（MDEV-35504），并在 12.1 和 12.2 中新增了其他提示。提示写在紧跟 SELECT 后的 /*+ ... */ 注释中，可覆盖单条查询的优化器设置，而不会更改会话设置：

SELECT /*+ JOIN_ORDER(o, c) */ *
FROM orders o JOIN customers c ON c.id = o.customer_id;

可用提示：

提示	引入版本	用途
QB_NAME(name)	12.0	为查询块命名，以便其他提示可以从外部对其进行指定
JOIN_FIXED_ORDER / JOIN_ORDER(t1, t2, ...)	12.0	强制指定连接顺序（JOIN_FIXED_ORDER 与 STRAIGHT_JOIN 类似）
JOIN_PREFIX(t1, ...) / JOIN_SUFFIX(t1, ...)	12.0	强制指定哪些表排在连接顺序的开头或末尾
MAX_EXECUTION_TIME(ms)	12.0	如果查询运行时间超过超时时间，则中止查询
[NO_]MRR / [NO_]BKA / [NO_]BNL	12.0	启用或禁用多范围读取、批量键访问、块嵌套循环
[NO_]ICP	12.0	启用或禁用索引条件下推
[NO_]RANGE_OPTIMIZATION	12.0	启用或禁用范围优化器
SEMIJOIN(strategy, ...) / SUBQUERY(strategy)	12.0	选择子查询重写策略
[NO_]INDEX(t idx, ...) / [NO_]JOIN_INDEX / [NO_]GROUP_INDEX / [NO_]ORDER_INDEX	12.1	按用途强制或禁止使用特定索引
[NO_]SPLIT_MATERIALIZED / [NO_]DERIVED_CONDITION_PUSHDOWN / [NO_]MERGE	12.1	控制子查询 / 派生表优化
[NO_]ROWID_FILTER / [NO_]INDEX_MERGE	12.2	启用或禁用 Rowid 过滤和索引合并

QB_NAME() 示例——为子查询命名，以便外部提示可以对其进行指定：

SELECT /*+ NO_MERGE(@sub) */ *
FROM (
    SELECT /*+ QB_NAME(sub) */ customer_id, COUNT(*) AS n
    FROM orders
    GROUP BY customer_id
) t
WHERE n > 10;

与 SET optimizer_switch 相比，提示的作用范围更明确，因为它们只应用于所在的查询，而非整个会话。

限制开销大的查询：LIMIT ROWS EXAMINED

LIMIT ROWS EXAMINED 是 MariaDB 专有扩展（自 5.5.21 起提供），MySQL 中没有对应功能。它会限制 SELECT 可以检查的行数，达到上限后便提前终止执行——可作为无界查询或临时查询中防止扫描失控的安全机制：

-- Up to 10 result rows, but stop after examining 10,000 rows:
SELECT * FROM t1, t2 LIMIT 10 ROWS EXAMINED 10000;

-- The cap can be used on its own:
SELECT * FROM big_table WHERE status = 'x' LIMIT ROWS EXAMINED 50000;

达到上限时，查询会返回部分结果集并附带警告——因此它是防护措施，而不是获取正确且更快结果的方法。它仅适用于 SELECT；用于 UPDATE / DELETE 时会产生语法错误。如需按时间限制查询，则可使用上文的 MAX_EXECUTION_TIME(ms) 优化器提示（12.0+）。请参阅 LIMIT ROWS EXAMINED。

快速优化清单

在添加索引或重写查询之前，先检查以下事项：

1. 确认启动时已启用 performance_schema=ON（如果未启用，则需要重启）——Performance Schema 概览
2. 对慢查询执行 EXPLAIN——确认耗时实际发生在哪里
3. 执行 ANALYZE TABLE——统计信息过时会导致执行计划不佳
4. 检查 WHERE 中索引列上的函数——请注意，许多情况下在 11.4+ 版本中已支持索引查找（YEAR()、DATE()、_ci 排序规则下的 UPPER()）
5. 检查分页查询中是否使用了 OFFSET
6. 确认复合索引的列顺序与查询条件匹配（最左前缀）
7. 检查 EXPLAIN 的 Extra 列中是否有 Using filesort 或 Using temporary——这通常表明缺少索引或索引顺序不当

资料来源

• 查询优化 — MariaDB 文档
• Performance Schema 概览 — MariaDB 文档
• EXPLAIN — MariaDB 文档
• optimizer_switch — MariaDB 文档
• 索引入门 — MariaDB 文档
• 为给定的 SELECT 构建最佳索引 — MariaDB 文档
• 基于直方图的统计信息 — MariaDB 文档
• 分页优化 — MariaDB 文档

此处未涵盖的主题，请参阅 MariaDB 官方文档：mariadb.com/docs。