如何优化200万+行数据的SQL查询（按code和stationID取最新价格）

问题背景 #

现有一张InnoDB表，包含200万+行数据且持续增长，表结构如下：

id INT PRIMARY KEY,
stationID INT,
code INT,
price DECIMAL(10,5),
date DATETIME

仅主键id有索引，当前使用CTE+ROW_NUMBER()的查询获取每个(code, stationID)分组的最新价格数据，耗时超10秒，需要优化。原查询语句：

$statement = $this->pdo->prepare(
    'WITH cte AS 
    (
        SELECT stationID AS ind, code, CAST(price AS DOUBLE ) AS price, date
        ,ROW_NUMBER() OVER(
             PARTITION BY code, stationID
             ORDER BY date DESC
         ) AS latest
        FROM price
    )
    SELECT *
    FROM cte
    WHERE latest  = 1
    '
);
$statement->execute();
$results = $statement->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_ASSOC);

核心优化方案 #

1. 创建覆盖复合索引 #

原查询慢的核心原因是全表扫描（仅主键索引无法覆盖分组、排序和字段获取），必须创建覆盖复合索引消除回表和全表扫描：

CREATE INDEX idx_code_station_date_price ON price (code, stationID, date DESC, price);

• 索引顺序逻辑：先匹配PARTITION BY的code、stationID字段，再按date DESC满足排序需求，最后包含price字段，让数据库直接从索引中获取所有所需数据，无需回表查询主键对应的行。
• 若业务更常按stationID维度查询，可调整索引顺序为(stationID, code, date DESC, price)，优化效果一致，可根据实际查询频率选择。

2. 优化查询语句 #

方案A：保留窗口函数并利用索引

创建索引后，原CTE查询会自动复用索引，可去掉不必要的类型转换（若业务无需DOUBLE类型的price）进一步降低开销：

WITH cte AS 
(
    SELECT stationID AS ind, code, price, date
    ,ROW_NUMBER() OVER(
         PARTITION BY code, stationID
         ORDER BY date DESC
     ) AS latest
    FROM price
)
SELECT ind, code, price, date
FROM cte
WHERE latest = 1

方案B：改用关联查询兼容低版本MySQL

若你的MySQL版本低于8.0（不支持窗口函数），可通过GROUP BY先获取每个分组的最新日期，再关联原表获取对应数据：

SELECT p.stationID AS ind, p.code, p.price, p.date
FROM price p
INNER JOIN (
    SELECT code, stationID, MAX(date) AS max_date
    FROM price
    GROUP BY code, stationID
) AS latest ON p.code = latest.code 
           AND p.stationID = latest.stationID 
           AND p.date = latest.max_date

该写法在有上述覆盖索引的前提下，性能同样优秀，且兼容性更强。

3. 额外优化建议 #

• 移除不必要的类型转换：若业务允许，直接使用DECIMAL类型的price，避免转DOUBLE带来的计算开销。
• 分批处理（可选）：若最终返回的分组数据量极大（十万级以上），可按stationID分段查询，避免一次性加载大量数据占用过多PHP内存。
• 归档历史数据：若业务无需保留全量历史价格，定期归档旧数据（比如仅保留最近3个月数据），减少表总数据量，从根源提升查询速度。

验证优化效果 #

执行EXPLAIN查看查询计划：

• 若type列显示range或ref，Extra列显示Using index，说明索引已被正确利用，查询将从全表扫描转为索引扫描，耗时会降至秒级甚至更低。

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