在MariaDB 10.7.6上使用包含许多子选择的查询时性能降低

在M1 Max macOS 12.6上运行，通过自制程序安装了MariaDB 10.7。
我很难找出它比我预期的慢的原因。我预期的是2到10分钟，但我看到的超过了一个小时。有5700万条记录，它似乎正在扫描5500万条。在底部，我列出了查询和每个查询的计数。我怀疑使用临时;使用filesort。我已经在大多数引用的列上添加了索引。
我正在寻找更多的想法来尝试解决这个性能问题。如果这是我能做的最好的，那也是一个有效的结果。我也在考虑迁移到全文搜索，但我还没有准备好。
表格格式的分析结果：
| 标识符|选择类型（_T）|工作台|类型|可能的密钥|钥匙|密钥长度|参考文献|列|r_行|过滤过的|r_已过滤|额外的|
| - -|- -|- -|- -|- -|- -|- -|- -|- -|- -|- -|- -|- -|
| 一个|主要|R邮政|索引合并|报告后索引x_6、报告后索引x_7、报告后索引x_8|报告后索引x_6、报告后索引x_7、报告后索引x_8|六、六、六|空值|小行星6176| 55104309.00元整|100元|0.02分|使用交集（rpost_idx_6、rpost_idx_7、rpost_idx_8）;使用where;使用临时;使用文件排序|
| 十一|相关子查询|R邮政|eq_参考|主要|主要|四个| test.RPost.Id |一个|一点整|100元|100元||
| 10个|相关子查询|R邮政|eq_参考|主要|主要|四个| test.RPost.Id |一个|一点整|100元|100元||
| 九个|相关子查询|R邮政|eq_参考|主要|主要|四个| test.RPost.Id |一个|一点整|100元|100元||
| 八个|相关子查询|历史记录|参考文献|后历史记录_idx_1、历史记录_idx_2、历史记录_idx_4|历史记录_idx_1|四个| test.RPost.Id |一个|二点六一分|二点九八|零点七八|使用where|
| 七个|相关子查询|历史记录|参考文献|后历史记录_idx_1、历史记录_idx_2、历史记录_idx_4|历史记录_idx_1|四个| test.RPost.Id |一个|二点六一分|五十个|九十六点三四|使用where|
| 六个|相关子查询||全部|空值|空值|空值|空值|2个|二点整|100元|0.59分|使用where|
| 十二个|衍生|空值|空值|空值|空值|空值|空值|空值|空值|空值|空值|未使用表|
| 十三个|管接头|空值|空值|空值|空值|空值|空值|空值|空值|空值|空值|未使用表|
| 空值|接头结果|〈工会12、13〉|全部|空值|空值|空值|空值|空值|二点整|空值|空值||
| 五个|相关子查询||全部|空值|空值|空值|空值|2个|二点整|100元|零点一二|使用where|
| 2个|衍生|空值|空值|空值|空值|空值|空值|空值|空值|空值|空值|未使用表|
| 三个|管接头|空值|空值|空值|空值|空值|空值|空值|空值|空值|空值|未使用表|
| 空值|接头结果|〈并集2，3〉|全部|空值|空值|空值|空值|空值|二点整|空值|空值||
查询：

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
);

计数：
| 查询|计数|
| - -|- -|
| 从rpost中选择 *| 五千六百万|
| 删除非空完成日期/取消日期/暂停日期|五千五百万|
| 现在已删除（排队+冻结）= 0|一千五百万|
| 最后处理喜欢|小行星8505|

请考虑重构查询，以进行不含巢状子选取的条件式汇总：

WITH Common AS (
  SELECT '% strings %' AS Value
  UNION ALL
  SELECT '% tofind %'
),
MyRPost (Id, STitle, SBody, Queued, Frozen) AS (
  SELECT
     r.Id,
     COUNT(ct.Value) AS STitle,
     COUNT(cb.Value) AS SBody,
     SUM(h.TypeId BETWEEN 1 AND 9) % 2 AS Queued,
     SUM(h.TypeId BETWEEN 10 AND 19) % 2 AS Frozen
  FROM RPost r
  LEFT JOIN Common ct ON r.Title LIKE ct.Value
  LEFT JOIN Common cb ON r.Body LIKE cb.Value
  LEFT JOIN History h ON h.PostId = r.Id
  WHERE r.CompletedDate IS NULL
    AND r.CancelDate is NULL
    AND r.PausedDate IS NULL
  GROUP BY r.Id
  HAVING (Queued + Frozen) = 0
     AND (STitle + SBody) > 1
)

SELECT 
   m.STitle, m.SBody, m.Id, m.Queued, m.Frozen,
   r.ParentId AS OriginId, r.PostTypeId AS Type, r.Title
FROM MyRPost m
LEFT JOIN RPost r ON m.Id = r.Id
ORDER BY (m.STitle + m.SBody), m.Id

在MariaDB 10.7.6上使用包含许多子选择的查询时性能降低

1条答案

相关问题

热门标签

最新问答