MariaDB递归查询无法充分利用复合索引的性能优化求助

这个问题我之前处理过类似场景，核心原因是你用的MariaDB 10.4.11版本对递归CTE（公共表表达式）的优化支持有限，当entity_id来自递归查询或临时表时，优化器没法把stamp过滤条件和复合索引ix_test_relation(entity_id, stamp)结合起来，只能先匹配entity_id再扫描过滤stamp，导致性能下降。下面几个方案可以帮你把递归查询的性能提升到接近常量IN查询的水平：

1. 强制物化递归CTE并改用JOIN关联 #

MariaDB 10.4+支持MATERIALIZED关键字强制将CTE结果物化到临时表，再通过JOIN替代IN子查询，这样优化器能更好地利用复合索引的两个字段：

WITH RECURSIVE recursive_child AS MATERIALIZED (
 SELECT id FROM test_entity WHERE id IN (2, 4)
 UNION ALL
 SELECT C.id FROM test_entity C INNER JOIN recursive_child P ON P.id = C.parent_id
)
SELECT TR.entry_id 
FROM test_relation TR
JOIN recursive_child RC ON TR.entity_id = RC.id
WHERE TR.stamp BETWEEN 6 AND 8;

原理： #

• MATERIALIZED让MariaDB提前计算递归结果并存储为临时表，避免重复计算
• JOIN比IN子查询更易被优化器识别，能直接将TR.entity_id = RC.id和TR.stamp BETWEEN ...组合，触发复合索引的全键扫描（先匹配entity_id，再用stamp过滤，完全利用索引顺序）

2. 给临时表添加主键/索引 #

如果必须用临时表存储递归结果，一定要给临时表的id字段加主键或索引，否则优化器会对临时表做全表扫描，无法高效关联test_relation：

CREATE OR REPLACE TEMPORARY TABLE tbl (id BIGINT PRIMARY KEY)
WITH RECURSIVE recursive_child AS (
 SELECT id FROM test_entity WHERE id IN (2, 4)
 UNION ALL
 SELECT C.id FROM test_entity C INNER JOIN recursive_child P ON P.id = C.parent_id
)
SELECT id FROM recursive_child;

SELECT TR.entry_id 
FROM test_relation TR
JOIN tbl ON TR.entity_id = tbl.id
WHERE TR.stamp BETWEEN 6 AND 8;

原理： #

临时表默认没有索引，添加主键后，关联时会走索引查找，同时优化器能将stamp条件下推到test_relation的复合索引中，避免无效扫描。

3. 升级MariaDB版本 #

你的10.4.11版本比较老旧，MariaDB 10.5及后续版本（尤其是10.6 LTS）对CTE的优化有很大提升，包括更好的索引下推支持、自动物化判断等。升级后，甚至不需要额外关键字，直接用你的原查询可能就能达到快查询的性能：

-- 升级到10.5+后尝试原查询写法，可能自动优化
SELECT entry_id FROM test_relation TR
WHERE TR.entity_id IN (
 WITH RECURSIVE recursive_child AS (
 SELECT id FROM test_entity WHERE id IN (2, 4)
 UNION ALL
 SELECT C.id FROM test_entity C INNER JOIN recursive_child P ON P.id = C.parent_id
 )
 SELECT id FROM recursive_child
) AND TR.stamp BETWEEN 6 AND 8

4. 强制使用复合索引（最后手段） #

如果以上方案都不生效，可以用FORCE INDEX提示强制优化器使用ix_test_relation，避免选错执行计划：

WITH RECURSIVE recursive_child AS MATERIALIZED (
 SELECT id FROM test_entity WHERE id IN (2, 4)
 UNION ALL
 SELECT C.id FROM test_entity C INNER JOIN recursive_child P ON P.id = C.parent_id
)
SELECT TR.entry_id 
FROM test_relation TR FORCE INDEX (ix_test_relation)
JOIN recursive_child RC ON TR.entity_id = RC.id
WHERE TR.stamp BETWEEN 6 AND 8;

注意： #

FORCE INDEX是硬提示，可能在数据分布变化后导致性能下降，优先用前面的方案让优化器自动选择。

你可以先测试方案1，应该能直接把耗时降到1-2ms左右，和快查询持平。

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