MariaDB特定场景下查询首次执行缓慢问题排查求助

核心问题定位 #

1. 执行计划选择偏差 #

首次执行时，TMPPropertyValue/TMPTransaction这类临时表刚填充完数据，MariaDB优化器未获取到最新统计信息，导致选择了低效的关联顺序或索引。当TMPTransaction从1条增至100条时，优化器错误估算数据分布，触发全表扫描或笛卡尔积式关联，直接引发超时。杀死进程或重启后，执行计划缓存被清除、统计信息更新，优化器选择了正确执行路径，因此恢复快速。

2. OR条件与旧式JOIN语法的冲突 #

原SQL使用旧式逗号分隔表的语法，关联逻辑模糊，加上(TT.DatastoreId <> D.DatastoreId OR TT.TransactionSeqNr <> T.TransactionSeqNr)的OR条件，优化器无法有效利用索引——OR条件会打断索引连续性，关联Datastore表后进一步放大性能损耗，即使添加索引也无法发挥作用。

3. 临时表统计信息缺失 #

用户创建的真实临时表（非内存临时表）填充数据后，默认不会自动更新统计信息。优化器基于过时统计生成执行计划，这是首次执行慢、后续执行快的核心原因之一。

具体优化建议 #

1. 重构SQL语法，明确关联逻辑 #

将旧式逗号分隔表语法改为标准JOIN，避免关联逻辑混乱，让优化器更易解析：

UPDATE TMPPropertyValue
INNER JOIN TMPObject AS TMPO ON TMPPropertyValue.ObjectRowId = TMPO.RowId
INNER JOIN Object AS O ON TMPO.ObjectId = O.ObjectId
INNER JOIN PropertyValue AS PV ON PV.ObjectRowId = O.RowId 
    AND PV.PropertyRowId = TMPPropertyValue.PropertyRowId
INNER JOIN Transaction AS T ON T.RowId = PV.TransactionRowId
INNER JOIN Datastore AS D ON T.DatastoreRowId = D.RowId
INNER JOIN TMPTransaction AS TT ON TMPPropertyValue.TransactionRowId = TT.RowId
SET TMPPropertyValue.Active = CASE
    WHEN TT.TransactionDateTime > T.TransactionDateTime THEN 1
    WHEN TT.TransactionDateTime < T.TransactionDateTime THEN 0
    ELSE CASE WHEN TT.DatastoreID > D.DatastoreId THEN 1 ELSE 0 END
END 
WHERE TMPPropertyValue.Active IS NULL
  AND TMPPropertyValue.IsNew = 1
  AND (TT.DatastoreId <> D.DatastoreId OR TT.TransactionSeqNr <> T.TransactionSeqNr);

2. 拆分OR条件，改用UNION ALL #

OR条件是性能瓶颈关键，将其拆分为两个独立查询，用UNION ALL合并，让每个分支都能利用索引：

-- 先创建临时表存储需更新的记录ID
CREATE TEMPORARY TABLE tmp_update_ids (id INT PRIMARY KEY);

INSERT INTO tmp_update_ids
SELECT TMPPropertyValue.RowId
FROM TMPPropertyValue
INNER JOIN TMPObject AS TMPO ON TMPPropertyValue.ObjectRowId = TMPO.RowId
INNER JOIN Object AS O ON TMPO.ObjectId = O.ObjectId
INNER JOIN PropertyValue AS PV ON PV.ObjectRowId = O.RowId 
    AND PV.PropertyRowId = TMPPropertyValue.PropertyRowId
INNER JOIN Transaction AS T ON T.RowId = PV.TransactionRowId
INNER JOIN Datastore AS D ON T.DatastoreRowId = D.RowId
INNER JOIN TMPTransaction AS TT ON TMPPropertyValue.TransactionRowId = TT.RowId
WHERE TMPPropertyValue.Active IS NULL
  AND TMPPropertyValue.IsNew = 1
  AND TT.DatastoreId <> D.DatastoreId

UNION ALL

SELECT TMPPropertyValue.RowId
FROM TMPPropertyValue
INNER JOIN TMPObject AS TMPO ON TMPPropertyValue.ObjectRowId = TMPO.RowId
INNER JOIN Object AS O ON TMPO.ObjectId = O.ObjectId
INNER JOIN PropertyValue AS PV ON PV.ObjectRowId = O.RowId 
    AND PV.PropertyRowId = TMPPropertyValue.PropertyRowId
INNER JOIN Transaction AS T ON T.RowId = PV.TransactionRowId
INNER JOIN Datastore AS D ON T.DatastoreRowId = D.RowId
INNER JOIN TMPTransaction AS TT ON TMPPropertyValue.TransactionRowId = TT.RowId
WHERE TMPPropertyValue.Active IS NULL
  AND TMPPropertyValue.IsNew = 1
  AND TT.TransactionSeqNr <> T.TransactionSeqNr;

-- 基于临时表执行UPDATE
UPDATE TMPPropertyValue
INNER JOIN tmp_update_ids ON TMPPropertyValue.RowId = tmp_update_ids.id
INNER JOIN TMPTransaction AS TT ON TMPPropertyValue.TransactionRowId = TT.RowId
INNER JOIN PropertyValue AS PV ON PV.PropertyRowId = TMPPropertyValue.PropertyRowId
INNER JOIN Transaction AS T ON T.RowId = PV.TransactionRowId
INNER JOIN Datastore AS D ON T.DatastoreRowId = D.RowId
SET TMPPropertyValue.Active = CASE
    WHEN TT.TransactionDateTime > T.TransactionDateTime THEN 1
    WHEN TT.TransactionDateTime < T.TransactionDateTime THEN 0
    ELSE CASE WHEN TT.DatastoreID > D.DatastoreId THEN 1 ELSE 0 END
END;

3. 更新临时表统计信息 #

填充完临时表后，手动更新统计信息，让优化器生成正确执行计划：

ANALYZE TABLE TMPPropertyValue, TMPTransaction, TMPObject;

4. 添加针对性索引 #

为临时表和核心表添加覆盖查询、关联字段的组合索引：

• 针对TMPPropertyValue：

  CREATE INDEX IDX_TMPPropertyValue_NewActive_ObjectPropertyTrans ON TMPPropertyValue (IsNew, Active, ObjectRowId, PropertyRowId, TransactionRowId);
  

• 针对TMPTransaction：

  CREATE INDEX IDX_TMPTransaction_DatastoreSeqDateTime ON TMPTransaction (DatastoreId, TransactionSeqNr, TransactionDateTime);
  

• 针对Transaction表：

  CREATE INDEX IDX_Transaction_RowId_DatastoreSeqDateTime ON Transaction (RowId, DatastoreRowId, TransactionSeqNr, TransactionDateTime);
  

5. 减少不必要的关联层级 #

Datastore表仅10条数据，可提前将DatastoreId转换为RowId存储到TMPTransaction中，避免查询时关联Datastore表，直接减少关联层级。

6. 分批次处理数据 #

若TMPPropertyValue数据量持续增长，可分批次处理，避免一次性更新大量数据超时：

WHILE EXISTS (SELECT 1 FROM TMPPropertyValue WHERE Active IS NULL AND IsNew = 1) DO
    UPDATE TMPPropertyValue
    -- 关联逻辑同重构后的SQL
    SET TMPPropertyValue.Active = CASE
        WHEN TT.TransactionDateTime > T.TransactionDateTime THEN 1
        WHEN TT.TransactionDateTime < T.TransactionDateTime THEN 0
        ELSE CASE WHEN TT.DatastoreID > D.DatastoreId THEN 1 ELSE 0 END
    END
    WHERE TMPPropertyValue.Active IS NULL
      AND TMPPropertyValue.IsNew = 1
    LIMIT 1000;
END WHILE;

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