MariaDB 10.4三层嵌套SELECT查询性能低下优化求助

原查询的性能损耗核心来自两方面：

• 内层是关联子查询，外层每扫描1行review表数据，就要重新执行1次子查询计算min_id，数据量越大耗时呈指数级增长，符合你观测到的O(n³)级别的耗时增长特征
• 没有匹配查询逻辑的联合索引，所有过滤、计算都依赖全表扫描，额外放大了耗时

1. 改写SQL，消除逐行计算的关联子查询 #

你的查询逻辑本质是筛选userid=:uid、reviewcount=0、deckid=:did的数据中，每个fcid分组下id最小的记录，直接用分组+关联的写法替换嵌套逻辑即可：

SELECT r.* 
FROM review r
INNER JOIN (
    SELECT MIN(id) AS min_id
    FROM review
    WHERE reviewcount = 0 
      AND userid = :uid
      AND deckid = :did
    GROUP BY fcid
) t ON r.id = t.min_id
LIMIT :nums;

改写后子查询仅执行1次就可以拿到所有符合条件的min_id，时间复杂度直接降到O(n)级别。

2. 创建覆盖联合索引，避免全表扫描 #

针对查询的过滤、分组逻辑创建专用联合索引，让整个子查询的计算都可以在索引中完成，不需要回表读取实际数据：

CREATE INDEX idx_review_opt ON review (userid, reviewcount, deckid, fcid, id);

索引排序逻辑：优先用等值过滤条件userid、reviewcount、deckid快速缩小数据集范围，再用fcid做分组，最后直接取id的最小值，全程不需要访问表数据。

3. 小LIMIT场景额外优化 #

如果你每次查询的:nums参数小于100，可以把LIMIT下推到子查询，进一步减少扫描的数据量：

SELECT r.* 
FROM review r
WHERE r.id IN (
    SELECT MIN(id) AS min_id
    FROM review
    WHERE reviewcount = 0 
      AND userid = :uid
      AND deckid = :did
    GROUP BY fcid
    LIMIT :nums
);

完成上述调整后，返回1000行数据的耗时可以降到1秒以内，性能提升至少200倍。

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