MySQL LEFT JOIN关联日志表获取最新记录查询过慢，求优化

表结构说明 #

• PartNumber表：主键为Part Number，包含Description、WorkflowStatus（关联WorkflowStatus表ID）等字段，示例数据：

'Part Number' 'Description' 'WorkflowStatus'
ZU0000        RES 1K 1%     4               
ZU0001        RES 2K 1%     2               
ZU0002        CAP 10nF 10V  3               
...

• WorkflowStatus表：主键ID，对应Workflows Status状态文本，示例数据：

'ID'   'Workflows Status'
1      New
2      Verified
3      Approved
4      Modified
...

• LogWorkflow表：记录操作日志，Type为PN时对应PartNumber的Part Number（存于Reference），需取每个PN的最新日志（示例中标注#的记录），示例数据：

'ID'   'Reference'  'Type'     'Recipient' 'Date'     'PrevStatus'  'NextStatus'
1      'ZU0000'      PN         Robert      2023.12.01   2           4 # 
2      'ZU0001'      PN         John        2023.10.02   NULL        1
3      'ZU0001'      PN         Librarian1  2023.10.04   1           2 #
4      'CR06031K00'  MPN        Mitch       2023.11.04   2           3
5      'ZU0002'      PN         Robert      2023.12.01   NULL        1
6      'QFN100-200'  Footprint  Mitch       2024.01.08   NULL        1
7      'ZU0002'      PN         Librarian1  2024.01.10   1           2
8      'ZU0002'      PN         Librarian2  2024.01.10   2           3 #
9      'QFN100-200'  Footprint  Mitch       2024.01.11   NULL        1

问题描述 #

需要关联PartNumber、WorkflowStatus表，同时关联每个PN对应的最新LogWorkflow记录，现有SQL能实现功能但查询速度极慢——子查询单独执行快，但与PartNumber表LEFT JOIN后整体性能骤降。原SQL如下：

SELECT 
 `PartNumber`.`Part Number`, `PartNumber`.`Description`,
 `WorkflowStatus`.`Workflow Status`,
 LW.`N°`, LW.`Recipient`
FROM `PartNumber`
LEFT JOIN `WorkflowStatus` ON `PartNumber`.`WorkflowStatus`=`WorkflowStatus`.`ID`
LEFT JOIN (
 SELECT LW1.`Type`, LW1.`Reference`, LW1.`N°`, LW1.`Recipient` FROM `LogWorkflow` LW1
 INNER JOIN (SELECT MAX(`N°`) AS`MAXNUM` FROM`LogWorkflow` WHERE `Type`='PN' GROUP BY `Reference`)
 AS LW2 ON LW1.`N°` = LW2.`MAXNUM`)
LW ON `PartNumber`.`Part Number` =  LW.`Reference`

优化方案 #

1. 添加针对性索引 #

慢查询核心原因是JOIN阶段无法有效利用索引，导致全表扫描。需创建以下索引：

• LogWorkflow表：创建复合索引，覆盖过滤、分组、排序场景

  CREATE INDEX idx_logworkflow_pn_ref_num ON LogWorkflow (`Type`, `Reference`, `N°`);
  

• 确认PartNumber表的Part Number为主键（若未设置，执行以下语句）：

  ALTER TABLE PartNumber ADD PRIMARY KEY (`Part Number`);
  

• 确认WorkflowStatus表的ID为主键，确保主键索引生效。

2. 改用窗口函数简化逻辑（适用于MySQL 8.0+/PostgreSQL等支持窗口函数的数据库） #

窗口函数可直接筛选每个Reference的最新记录，避免嵌套JOIN的性能损耗：

SELECT 
  pn.`Part Number`,
  pn.`Description`,
  ws.`Workflows Status`,
  lw.`N°`,
  lw.`Recipient`
FROM PartNumber pn
LEFT JOIN WorkflowStatus ws ON pn.WorkflowStatus = ws.ID
LEFT JOIN (
  SELECT 
    `Reference`,
    `N°`,
    `Recipient`,
    ROW_NUMBER() OVER (PARTITION BY `Reference` ORDER BY `N°` DESC) AS rn
  FROM LogWorkflow
  WHERE `Type` = 'PN'
) lw ON pn.`Part Number` = lw.`Reference` AND lw.rn = 1;

ROW_NUMBER()按Reference分组，按N°倒序排序，取每组第一条（rn=1）即为最新日志，逻辑清晰且执行效率更高。

3. 调整JOIN逻辑，减少全局分组开销 #

将子查询移至JOIN条件中，针对每个PartNumber直接查找对应PN的最大N°日志，避免全局分组后再JOIN的冗余操作：

SELECT 
  pn.`Part Number`,
  pn.`Description`,
  ws.`Workflows Status`,
  lw.`N°`,
  lw.`Recipient`
FROM PartNumber pn
LEFT JOIN WorkflowStatus ws ON pn.WorkflowStatus = ws.ID
LEFT JOIN LogWorkflow lw 
  ON pn.`Part Number` = lw.`Reference` 
  AND lw.`Type` = 'PN'
  AND lw.`N°` = (SELECT MAX(`N°`) FROM LogWorkflow WHERE `Reference` = lw.`Reference` AND `Type`='PN');

4. 检查执行计划验证索引生效 #

执行EXPLAIN查看查询执行计划，确认是否用到创建的索引：

EXPLAIN
-- 替换为你的查询语句

若执行计划中出现ALL（全表扫描），需检查索引字段与JOIN字段的类型、长度是否一致，避免隐式类型转换导致索引失效。

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