含DateTime的Join查询优化：千万级员工表慢查询求助

问题背景 #

现有两张数据库表：

Employee表 #

EmpID(int, PK, AI, NOT NULL),
Name(varchar(256), NOT NULL),
ContractExpire(datetime, NOT NULL)

Transaction表 #

ID(int, PK, AI, NOT NULL),
EmpID(int, NOT NULL),
Action(int, NOT NULL),
Status(int, NOT NULL)

• Employee表约1000万条记录，每位员工对应Transaction表中2-3条记录
• 需要统计**最新交易状态为20（活跃）且合同到期时间晚于'2024-04-10'**的员工数量
• 原查询因关联子查询逐行计算MAX(ID)，执行效率极低：

SELECT COUNT(emps.EmpID) 
FROM Employee emps 
INNER JOIN Transactions Trxs ON emps.EmpID = Trxs.EmpID 
WHERE Trxs.ID = (SELECT MAX(ID) from Transactions WHERE EmpID = emps.EmpID) 
    AND Trxs.Status = 20 
    AND emps.ContractExpire > '2024-04-10'

优化方案 #

方案1：使用窗口函数筛选最新交易记录 #

利用ROW_NUMBER()窗口函数，一次性为每个员工的交易记录按ID降序排序，标记出最新的一条记录，再关联Employee表过滤条件。该方案兼容MS SQL Server、MySQL 8.0+、SQLite 3.25+。

WITH LatestTrxs AS (
    SELECT 
        EmpID,
        Status,
        ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY ID DESC) AS RowNum
    FROM Transactions
)
SELECT COUNT(DISTINCT emps.EmpID)
FROM Employee emps
INNER JOIN LatestTrxs lt ON emps.EmpID = lt.EmpID
WHERE lt.RowNum = 1 
    AND lt.Status = 20
    AND emps.ContractExpire > '2024-04-10'

方案2：预计算每个员工的最新交易ID #

先通过GROUP BY获取每个员工的最大交易ID，再关联Transaction表获取对应状态，最后关联Employee表过滤合同时间。该方案兼容所有目标数据库（包括低版本MySQL/SQLite）。

SELECT COUNT(emps.EmpID)
FROM Employee emps
INNER JOIN (
    SELECT EmpID, MAX(ID) AS LatestID
    FROM Transactions
    GROUP BY EmpID
) lt ON emps.EmpID = lt.EmpID
INNER JOIN Transactions trxs ON lt.LatestID = trxs.ID
WHERE trxs.Status = 20
    AND emps.ContractExpire > '2024-04-10'

索引优化（关键补充） #

现有索引基础上，新增以下联合索引可进一步提升查询效率：

• Transaction表：创建(EmpID, ID DESC)联合索引，用于快速定位每个员工的最新交易ID，避免全表扫描
• Employee表：创建(ContractExpire, EmpID)联合索引，过滤合同到期时间时直接返回EmpID，无需回表查询其他字段

优化原理 #

1. 原查询中的关联子查询会对每个Employee记录执行一次MAX(ID)查询，导致重复遍历Transaction表，时间复杂度为O(N*M)（N为Employee数量，M为单员工交易数）
2. 方案1和方案2均通过一次性计算所有员工的最新交易记录，将时间复杂度降至O(N+M)，大幅减少IO操作
3. 联合索引可让数据库直接通过索引获取所需数据，避免全表扫描或回表查询

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