MariaDB 教程 / 读取 EXPLAIN 并比较索引前后的执行计划

EXPLAIN 显示优化器选择的执行计划。比较索引前后时,应保持查询和实际返回结果相同,结合访问方式、所选键与估算行数阅读计划,不能把估算值直接当成实测耗时。

结合上下文读取计划字段

先看访问了哪些表、哪些键能缩小输入,再解释后面的估计。ALL 不一定错误,小表全扫描可能合理;Using filesort 也不能单独证明发生了磁盘文件排序。应结合规模、筛选比例与实际延迟,而不是看到某个词就断言计划好坏。

字段 怎样理解 不要直接假定
table 当前步骤访问的表或别名 一条查询只会有一个步骤
type 访问记录的方式 任何全表扫描都应消除
possible_keys / key 候选键与实际选中的键 索引存在就必须被选择
rows 访问步骤的估算行数 最终返回的精确行数
Extra 额外筛选、排序与覆盖信息 某一句短语能决定整体质量

候选键设计可参照 单列与复合索引教程,字段定义可查阅官方 EXPLAIN 文档。先确认计划表达的内容,再考虑如何验证实际执行。

生成可复现的一千行样本

使用学习账号连接 MariaDB 客户端,先核对服务器版本与数据库,并检查 digits_40_lab、explain_40_lab 不存在。已有同名对象时,先查看定义和记录,不要覆盖用途未知的数据。下面只生成独立样本,不读取或修改真实业务订单。

准备代码把三个 0~9 的数字集合交叉组合,生成 0~999,再加 1 得到订单编号。客户编号在 1~100 之间循环,每位客户恰好十个订单。可直接执行,也可按 SQL 文件与 SOURCE 教程保存为 UTF-8 文件后加载实际路径。

USE shopcourse;
CREATE TABLE digits_40_lab(n TINYINT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO digits_40_lab VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE explain_40_lab(
 order_id INT PRIMARY KEY,customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,amount DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB;
INSERT INTO explain_40_lab
SELECT 1+a.n+10*b.n+100*d.n,
 MOD(a.n+10*b.n+100*d.n,100)+1,
 DATE_ADD('2026-01-01',INTERVAL (a.n+10*b.n+100*d.n) MINUTE),1000
FROM digits_40_lab a CROSS JOIN digits_40_lab b CROSS JOIN digits_40_lab d;

运行 SELECT COUNT(*) FROM explain_40_lab; 应得到 1000。金额固定为 1000,时间从 2026-01-01 起每行增加一分钟。这种均匀分布便于独立核对,不代表生产环境的数据倾斜、规模或并发负载。

索引前后比较完全相同的 EXPLAIN

EXPLAIN SELECT order_id,ordered_at FROM explain_40_lab
WHERE customer_id=47 AND ordered_at>='2026-01-01'
ORDER BY ordered_at,order_id;
CREATE INDEX idx_e40_customer_date
ON explain_40_lab(customer_id,ordered_at,order_id);
EXPLAIN SELECT order_id,ordered_at FROM explain_40_lab
WHERE customer_id=47 AND ordered_at>='2026-01-01'
ORDER BY ordered_at,order_id;

第一份计划没有客户—时间的二级键,第二份多了新的候选索引。检查实际 key、type、rows 和 Extra,而不是只确认 CREATE INDEX 成功。版本、统计信息与分布都可能改变计划细节,不应把某次数字背成固定答案。

在本节 MariaDB 12.3.3 的独立一千行样本中,执行得到如下计划。它是特定环境的观察值,其他环境应读取自己的实际输出:

阶段 type key rows Extra
建索引前 ALL NULL 1000 Using where; Using filesort
建索引后 range idx_e40_customer_date 10 Using where; Using index

这里从全扫描变成客户与时间范围访问,估算行数从 1000 降到 10。这不等于实测速度提升一百倍;还要独立核对十个返回订单,并区分读取范围、循环次数与总耗时。

独立核对实际输出,不把估计当答案

SELECT order_id,ordered_at FROM explain_40_lab
WHERE customer_id=47 AND ordered_at>='2026-01-01'
ORDER BY ordered_at,order_id;

实际编号应为 47、147、247、347、447、547、647、747、847、947。对应时间由初始化规则决定,例如编号 47 是起点后第 46 分钟。索引创建前后,同一 SELECT 应返回相同记录和相同顺序,而不只是相同数量。

EXPLAIN 的 rows 描述访问步骤的估计工作量,不是最终结果的精确计数。在更复杂连接中,还可能需要结合每步执行次数与筛选情况解释,不能把单个数字直接当作整条查询读了多少行。

计划也不保证延迟。若要比较耗时,应控制查询、数据、并发和缓存条件,做重复观察并记录方法。不能把一次小型合成测试的微小时间差,发布成整站或真实业务的固定改善百分比。

区分执行型 ANALYZE 与统计信息维护

MariaDB 的 ANALYZE 查询分析会真正执行目标语句,再返回执行信息。不要把它当成另一个无副作用的 EXPLAIN,随意放在 UPDATE 或 DELETE 前。需要运行时分析时,先确认语句范围、权限与环境,学习阶段只对下面这个 SELECT 执行。

ANALYZE TABLE 是另一项维护表统计信息的操作。统计陈旧可能影响估计,但刷新统计也有版本相关的成本和锁影响;不能因为计划不符合期待,就直接在生产反复维护统计或强制 FORCE INDEX。

ANALYZE FORMAT=JSON
SELECT order_id,ordered_at FROM explain_40_lab
WHERE customer_id=47 AND ordered_at>='2026-01-01'
ORDER BY ordered_at,order_id;

JSON 中 rows 是估计字段,r_rows 是实际读取的平均行数,r_loops 是相关节点执行次数。应把它们放在对应节点中一起读,不能只搜索一个 r_rows 就当成所有步骤的总量。字段解释见官方 ANALYZE FORMAT=JSON 文档。

本节索引 SELECT 的目标表节点可核对到一次循环、实际平均读取十行,说明实际访问与这个样本的目标集合一致。具体耗时依赖环境,不是应背诵的预期答案;这里也没有通过修改型 ANALYZE 写入任何业务数据。

保留可复现证据,再定向清理

记录服务器版本、表定义与索引、数据生成方法、比较查询、前后计划和实际输出。如果另一个环境得到不同 key 或 rows,先检查这些条件,而不是先强制它复制这里的截图数字。

在独立样本中删除新增索引再重建,可以验证结果仍保持一致,并比较访问路径如何变化。这类受控对照应一次只改一项,不要同时改查询范围、数据与缓存条件后把差异全归给索引。

确认两个练习对象不含需要保留的数据后,执行下面的定向清理。它们之间没有外键关系,也不涉及已有业务订单表。DROP TABLE 会删除定义与数据,普通 ROLLBACK 无法恢复,应与数据回滚实验分开。

DROP TABLE shopcourse.explain_40_lab;
DROP TABLE shopcourse.digits_40_lab;