MariaDB 10.7.6多子查询SQL语句性能优化求助

环境 #

• 系统：搭载M1 Max芯片的macOS 12.6
• 数据库：通过Homebrew安装的MariaDB 10.7

问题 #

一条包含多子查询的SQL语句执行耗时远超预期，原预计2-10分钟，实际超过1小时；数据库共5700万条记录，该查询扫描了5500万条。

当前分析 #

怀疑是执行计划中的Using temporary; Using filesort导致性能问题，已为多数关联字段添加索引。

需求 #

寻求更多性能优化思路；若当前已是最优状态也可告知；暂未准备迁移至全文搜索。

ANALYZE 执行计划结果 #

查询语句 #

WITH Common as (
  SELECT '% strings %' AS Value
  UNION
  SELECT '% tofind %'
),
MyRPost (Id, STitle, SBody, Queued, Frozen)
AS
(
SELECT
  RPost.Id,
  (
    SELECT count(*) FROM Common
    WHERE RPost.Title Like Common.Value
  ) AS STitle,
  (
    SELECT count(*) FROM Common
    WHERE RPost.Body Like Common.Value
  ) AS SBody,
  (SELECT count(History.TypeId) FROM History
          WHERE History.PostId = RPost.Id
            AND History.TypeId BETWEEN 1 AND 9
  )%2 AS Queued,
  (SELECT count(History.TypeId) FROM History
          WHERE History.PostId = RPost.Id
            AND History.TypeId BETWEEN 10 AND 19
  )%2 AS Frozen
  FROM RPost
  WHERE RPost.CompletedDate IS NULL
    AND RPost.CancelDate is NULL
    AND RPost.PausedDate IS NULL
)
SELECT STitle, SBody, Id, Queued, Frozen,
  (SELECT RPost.ParentId FROM RPost WHERE MyRPost.Id=RPost.Id) as OriginId,
  (SELECT RPost.PostTypeId FROM RPost WHERE MyRPost.Id=RPost.Id) as Type,
  (SELECT RPost.Title FROM RPost WHERE MyRPost.Id=RPost.Id) as Title
FROM MyRPost
WHERE (Queued+Frozen) = 0
  AND (STitle+SBody) > 1
ORDER BY (STitle+SBody), Id

表结构 #

CREATE TABLE History (
    Id INT NOT NULL PRIMARY KEY,
    TypeId SMALLINT NOT NULL,
    PostId INT NOT NULL
);
CREATE TABLE RPost (
    Id INT NOT NULL PRIMARY KEY,
    Title varchar(256),
    Body text NULL,
    PostTypeId TINYINT NOT NULL ,
    ParentId INT,
    CompletedDate DATETIME,
    CancelDate DATETIME,
    PausedDate DATETIME
);

数据量统计 #

性能优化思路 #

1. 重构子查询，减少重复访问 #

当前查询对RPost的OriginId、Type、Title字段做了三次独立子查询，每条记录重复访问主键三次，可直接在CTE中一次性获取：

-- 修改MyRPost CTE，直接包含所需字段
MyRPost (Id, STitle, SBody, Queued, Frozen, OriginId, Type, Title)
AS
(
SELECT
  RPost.Id,
  (SELECT count(*) FROM Common WHERE RPost.Title Like Common.Value) AS STitle,
  (SELECT count(*) FROM Common WHERE RPost.Body Like Common.Value) AS SBody,
  (SELECT count(History.TypeId) FROM History WHERE History.PostId = RPost.Id AND History.TypeId BETWEEN 1 AND 9)%2 AS Queued,
  (SELECT count(History.TypeId) FROM History WHERE History.PostId = RPost.Id AND History.TypeId BETWEEN 10 AND 19)%2 AS Frozen,
  RPost.ParentId AS OriginId,
  RPost.PostTypeId AS Type,
  RPost.Title AS Title
FROM RPost
WHERE RPost.CompletedDate IS NULL
  AND RPost.CancelDate is NULL
  AND RPost.PausedDate IS NULL
)
-- 后续SELECT直接从MyRPost取字段即可
SELECT STitle, SBody, Id, Queued, Frozen, OriginId, Type, Title
FROM MyRPost
WHERE (Queued+Frozen) = 0
  AND (STitle+SBody) > 1
ORDER BY (STitle+SBody), Id

2. 预过滤数据，缩小处理范围 #

根据数据统计，最终仅8505条记录符合条件，可先过滤掉(Queued+Frozen)≠0的记录，再进行Like匹配，避免对5500万条记录全量处理：

WITH ValidPosts AS (
  -- 先筛选出(Queued+Frozen)=0的PostId
  SELECT 
    PostId,
    COUNT(CASE WHEN TypeId BETWEEN 1 AND 9 THEN 1 END)%2 AS Queued,
    COUNT(CASE WHEN TypeId BETWEEN 10 AND 19 THEN 1 END)%2 AS Frozen
  FROM History
  GROUP BY PostId
  HAVING (Queued + Frozen) = 0
  -- 关联RPost过滤状态条件
  JOIN RPost ON RPost.Id = History.PostId
  WHERE RPost.CompletedDate IS NULL
    AND RPost.CancelDate IS NULL
    AND RPost.PausedDate IS NULL
),
Common as (
  SELECT '% strings %' AS Value
  UNION
  SELECT '% tofind %'
)
SELECT
  (SELECT count(*) FROM Common WHERE RPost.Title Like Common.Value) AS STitle,
  (SELECT count(*) FROM Common WHERE RPost.Body Like Common.Value) AS SBody,
  RPost.Id,
  VP.Queued,
  VP.Frozen,
  RPost.ParentId AS OriginId,
  RPost.PostTypeId AS Type,
  RPost.Title AS Title
FROM ValidPosts VP
JOIN RPost ON RPost.Id = VP.PostId
WHERE (STitle + SBody) > 1
ORDER BY (STitle+SBody), RPost.Id

3. 优化索引，避免index_merge #

当前执行计划用index_merge合并三个索引匹配空值条件，可创建联合索引替代：

-- 创建覆盖三个状态字段的联合索引，附带Id用于后续关联
CREATE INDEX rpost_status_idx ON RPost(CompletedDate, CancelDate, PausedDate, Id);

该索引可直接定位符合状态条件的记录，无需合并多个索引，减少IO开销。

4. 优化History表的统计查询 #

对History表创建联合索引，加速按PostId和TypeId范围的统计：

CREATE INDEX history_post_type_idx ON History(PostId, TypeId);

数据库可通过该索引快速按PostId分组，统计指定TypeId范围的记录数，避免全表扫描。

5. 调整Common CTE的使用方式 #

当前每条记录执行两次关联Common的子查询，可改为JOIN方式减少重复计算：

-- 替换STitle和SBody的子查询为JOIN统计
SELECT
  RPost.Id,
  SUM(CASE WHEN RPost.Title LIKE Common.Value THEN 1 ELSE 0 END) AS STitle,
  SUM(CASE WHEN RPost.Body LIKE Common.Value THEN 1 ELSE 0 END) AS SBody,
  VP.Queued,
  VP.Frozen,
  RPost.ParentId AS OriginId,
  RPost.PostTypeId AS Type,
  RPost.Title AS Title
FROM RPost
CROSS JOIN Common
JOIN ValidPosts VP ON RPost.Id = VP.PostId
WHERE RPost.CompletedDate IS NULL
  AND RPost.CancelDate IS NULL
  AND RPost.PausedDate IS NULL
GROUP BY RPost.Id, VP.Queued, VP.Frozen, RPost.ParentId, RPost.PostTypeId, RPost.Title
HAVING (STitle + SBody) > 1
ORDER BY (STitle+SBody), RPost.Id

6. 缓解Using temporary和Using filesort问题 #

排序字段(STitle+SBody)是计算字段，无法直接用索引优化，可通过提前过滤数据减少排序数据集（从5500万降到15万再到8505），降低临时表和文件排序的开销。

总结 #

当前查询的核心问题是先对5500万条记录全量执行子查询和Like匹配，再过滤少量结果。通过提前过滤数据、减少重复查询、优化索引三个方向调整，可大幅降低执行时间。若调整后仍有瓶颈，可考虑分批处理数据，或启用MariaDB查询缓存（若业务场景适用）。

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