MySQL中Datetime渐进计算优化：解决20万条记录SQL查询超时问题

性能根因 #

• 相关子查询导致O(n²)复杂度：原SQL对每一行路由数据都会执行一次子查询查找关联数据，20万条数据会触发近20万次独立查询，是性能超时的核心原因
• 索引失效：WHERE条件中使用DATE(routes.receive_at)对索引列做函数运算，导致无法命中receive_at字段的索引，全表扫描开销极高
• 逻辑错误：子查询中routes.id < table12.id的条件写反，实际获取的是ID更大的下一条记录而非上一条，且子查询的日期过滤条件错误绑定外层表字段，返回结果不符合预期
• 聚合逻辑错误：未加GROUP BY的SUM函数会将多行结果聚合为单行，和SELECT后返回的单行路由字段逻辑冲突

优化方案 #

1. 索引优化

先新增两个覆盖索引，避免查询回表：

-- routes表覆盖索引：满足日期过滤、排序、字段读取的全部需求
CREATE INDEX idx_routes_query ON routes(receive_at, id, barcode, release_at);
-- documents表关联索引：覆盖关联和字段读取需求
CREATE INDEX idx_doc_code_title ON documents(document_code, document_title);

将日期过滤条件修改为不包裹函数的写法，触发索引命中：

-- 原写法（索引失效）
WHERE DATE(routes.receive_at) between "2021-10-01" AND "2021-10-18"
-- 优化后写法（可命中索引）
WHERE routes.receive_at >= '2021-10-01' AND routes.receive_at < '2021-10-19'

2. SQL逻辑优化（MySQL 8.0+ 版本，支持窗口函数）

使用LAG窗口函数替代相关子查询，直接取当前行的上一条记录的release_at，复杂度降为O(n)。如果业务要求同一条码的路由才计算相邻差值，可在LAG中加PARTITION BY barcode：

SELECT 
    r.barcode,
    d.document_title, 
    r.receive_at, 
    r.release_at,
    -- 计算和上一条记录的停留时长，无需嵌套子查询
    SEC_TO_TIME(TIME_TO_SEC(TIMEDIFF(r.receive_at, LAG(r.release_at) OVER(ORDER BY r.id)))) as time_office,
    SEC_TO_TIME(TIME_TO_SEC(TIMEDIFF(r.receive_at, r.release_at))) as time_travel 
FROM routes r
LEFT JOIN documents d ON r.barcode = d.document_code
WHERE r.receive_at >= '2021-10-01' AND r.receive_at < '2021-10-19'
ORDER BY r.id ASC;

如果需要按条码分组统计总时长，直接加上分组和聚合即可：

SELECT 
    r.barcode,
    d.document_title,
    SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(r.receive_at, LAG(r.release_at) OVER(PARTITION BY r.barcode ORDER BY r.id))))) as total_time_office,
    SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(r.receive_at, r.release_at)))) as total_time_travel 
FROM routes r
LEFT JOIN documents d ON r.barcode = d.document_code
WHERE r.receive_at >= '2021-10-01' AND r.receive_at < '2021-10-19'
GROUP BY r.barcode, d.document_title;

3. MySQL 5.x 兼容方案（不支持窗口函数）

使用用户变量模拟LAG功能，性能同样远高于相关子查询：

SELECT 
    barcode,
    document_title,
    receive_at,
    release_at,
    SEC_TO_TIME(TIME_TO_SEC(TIMEDIFF(receive_at, prev_release))) as time_office,
    SEC_TO_TIME(TIME_TO_SEC(TIMEDIFF(receive_at, release_at))) as time_travel
FROM (
    SELECT 
        r.*,
        d.document_title,
        @prev_release as prev_release,
        @prev_release := r.release_at
    FROM (
        SELECT * FROM routes 
        WHERE receive_at >= '2021-10-01' AND receive_at < '2021-10-19'
        ORDER BY id ASC
    ) r
    LEFT JOIN documents d ON r.barcode = d.document_code
    CROSS JOIN (SELECT @prev_release := NULL) t
) final;

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