一次sql优化案例

业务需求是找到水动力结果表里面某一个预报方案的所有河道的断面，最大水位，以及对应最大水位对应的预警时间，由于该表记录有1500多万条，查询速度较慢

一开始是这么写的

SELECT
    HydroID,
    (SELECT TOP 1 HTime FROM Simulation_HWaterFLow SH2 WHERE SH2.HydroID = SH1.HydroID and ProgramID = 2009  ORDER BY HWater DESC) AS HTime,
    MAX(HWater) AS HWater
FROM
    Simulation_HWaterFLow SH1
WHERE
    ProgramID = 2009
GROUP BY
    HydroID
ORDER BY
    HydroID

结果耗时65s左右

优化后是这么写的

	WITH max_HWater AS (
    SELECT
        HydroID,
        MAX(HWater) AS HWater
    FROM
        Simulation_HWaterFLow
    WHERE
        ProgramID = 2009
    GROUP BY
        HydroID
), max_HTime AS (
    SELECT
        SH1.HydroID,
        MAX(SH1.HTime) AS HTime
    FROM
        Simulation_HWaterFLow SH1
        INNER JOIN max_HWater ON SH1.HydroID = max_HWater.HydroID AND SH1.HWater = max_HWater.HWater
    WHERE
        ProgramID = 2009
    GROUP BY
        SH1.HydroID
)
SELECT
    max_HTime.HydroID,
    max_HTime.HTime,
    max_HWater.HWater
FROM
    max_HTime
    INNER JOIN max_HWater ON max_HTime.HydroID = max_HWater.HydroID
ORDER BY
    max_HTime.HydroID
		
		SELECT count(*) from Simulation_HWaterFLow

平均耗时0.074ms

优化思路：

1. 添加索引：在涉及到大量数据筛选的列上添加索引可以加快查询速度。例如，在本查询中，我们可以为 ProgramID 和 HydroID 列分别添加索引。（加之前优化的sql查询时间1s左右，快了13倍；优化前的sql查询时间5s左右快了13倍）

2. 优化子查询：子查询的性能往往比较差，因此可以尝试将子查询转换为 JOIN 操作，或者使用临时表优化查询。例如，在本查询中，可以尝试使用 LEFT JOIN 将两个查询结果合并起来。

优化后的sql理解：

这个查询使用了两个公共表达式 max_HWater 和 max_HTime，分别用于查找每个 HydroID 对应的最大 HWater 和最新 HTime。

在 max_HWater 的查询中，我们直接使用了简单的 GROUP BY 和 MAX 函数来计算最大的 HWater 值，不需要使用子查询。在 max_HTime 的查询中，我们使用 INNER JOIN 将 max_HWater 的结果与 Simulation_HWaterFLow 的原始数据表连接起来，只保留 HWater 值最大的记录；然后再使用 GROUP BY 和 MAX 函数获取对应的最新 HTime。这样，相较于子查询的方式，JOIN 更容易优化。

最终查询的选择列表中只包含了 max_HWater.HWater、max_HTime.HTime 和 max_HTime.HydroID 三列，因此SQL 可以直接使用这两张表符合的数据来构造结果而不必访问原始数据表。