MariaDB慢查询优化及EXPLAIN中cost指标解读求助

1. 关联查询优化方案 #

原查询执行超时的核心原因是a.al LIKE concat(al.iid, '%')无法利用现有索引，导致执行计划采用**Block Nested Loop (BNL)**连接：遍历alb的174万行数据，每行再匹配al的185万行，计算量达到百亿级别，这是性能瓶颈所在。

优化思路与改写方案 #

根据样本数据的匹配规则（al.iid是alb.al的前缀或完全匹配），提供以下优化方案：

方案一：反转匹配逻辑+前缀索引

将匹配逻辑反转，让al.iid匹配alb.al的前缀，同时给alb.al创建单独的前缀索引：

-- 先创建前缀索引（根据al.iid的平均长度调整前缀长度，示例用20）
ALTER TABLE alb ADD INDEX idx_al_prefix (al(20));

-- 改写后的查询
SELECT a.iid, al.iid
FROM al
INNER JOIN alb a ON al.iid LIKE concat(a.al, '%');

反转后，条件可以利用alb的idx_al_prefix索引，避免全表扫描，大幅降低计算量。

方案二：预处理匹配键（适合数据更新不频繁场景）

如果al表数据更新不频繁，可通过生成列将模糊匹配转为等值匹配：

-- 给al表添加生成列，存储iid的前缀（适配样本中al.iid比alb.al多后缀的规则）
ALTER TABLE al ADD COLUMN al_iid_prefix VARCHAR(169) AS (LEFT(iid, LENGTH(iid)-2)) STORED;
ALTER TABLE al ADD INDEX idx_al_iid_prefix (al_iid_prefix);

-- 改写后的查询
SELECT a.iid, al.iid
FROM alb a
INNER JOIN al ON a.al = al.al_iid_prefix;

这种方式完全利用索引，性能最优，适合数据相对稳定的场景。

方案三：全文索引（适合复杂前缀匹配）

如果前缀规则不固定，可给alb.al创建全文索引：

ALTER TABLE alb ADD FULLTEXT INDEX idx_al_fulltext (al);

-- 改写后的查询（需注意分词规则，必要时调整数据库全文索引配置）
SELECT a.iid, al.iid
FROM alb a
INNER JOIN al ON MATCH(a.al) AGAINST(al.iid IN BOOLEAN MODE);

原执行计划问题分析 #

从EXPLAIN结果可见：

• alb表使用联合索引iid_2，但仅用到iid字段，al字段无法支持模糊匹配的索引扫描
• al表采用block-nl-join，意味着每行alb数据都要遍历整个al索引，这是导致查询超时的直接原因

2. EXPLAIN中cost指标的相对参考方法 #

MariaDB的cost是优化器基于统计信息估算的相对逻辑执行量，无实际单位，但可通过以下方式转化为参考依据：

相对比较法 #

• 同一实例内对比不同查询的cost：比如查询A的cost是1000，查询B是10000，说明B的预估执行量约为A的10倍
• 同一查询的不同执行计划对比：比如改写前cost为3亿，改写后为10万，说明优化效果显著

结合行数估算 #

• cost与预估扫描行数正相关：比如alb表预估行数174万，cost为627；al表预估行数185万，单循环cost约为301476095.9/1744202≈172，即每行alb匹配al的预估逻辑执行量为172
• 小范围测试验证：取1000行alb数据执行查询，记录实际耗时，再按总行数比例估算整体耗时

注意事项 #

• cost依赖表统计信息，若统计过时，需先执行ANALYZE TABLE alb, al;更新
• 硬件资源（CPU、内存、磁盘IO）会影响实际耗时，cost仅反映逻辑执行量，无法直接对应绝对时间

内容的提问来源于stack exchange，提问作者Slawowid