MySQL超3000万行表分组查询最新记录的性能优化咨询

我了解这类问题已有相关提问，但尝试过连接、子查询等方案后仍存在性能问题。

我拥有一张存储股票价格的prices表，包含约20000只股票，总记录数超3000万条，建表语句如下：

CREATE TABLE `prices` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `StockID` int(11) NOT NULL,
  `Price` decimal(18,4) DEFAULT NULL,
  `Date` datetime NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UX_StockID_Date` (`StockID`,`Date`),
  KEY `IX_StockID` (`StockID`),
  KEY `IX_Date` (`Date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

我需要获取任意指定日期所有股票的价格，但因低交易量、节假日或周末，部分股票当日无交易记录，需取该日期前最近交易日的价格。

我尝试过连接方式的查询，代码如下：

select P.*
from (
    SELECT StockID, Max(Date) As MaxDate
    FROM prices
    WHERE Date <= '2020-12-31'
    GROUP BY StockID
) as P1
join prices as P on P.StockID = P1.StockID and P.Date = P1.MaxDate;

该查询的EXPLAIN结果如下：

该查询结果准确但速度极慢（耗时1-2分钟），后续因InnoDB缓存性能会提升，但我不想依赖缓存。请问是否有优化该查询的方法，或可创建额外视图/表以高效获取此类数据？

优化方案 #

1. 用窗口函数改写查询，避免临时表全扫

原查询的子查询生成的临时表P1没有索引，导致主查询对其做全表扫描。改用窗口函数可以直接利用现有索引快速定位每个股票的最近交易日数据：

SELECT StockID, Price, Date
FROM (
    SELECT 
        StockID, 
        Price, 
        Date,
        ROW_NUMBER() OVER (PARTITION BY StockID ORDER BY Date DESC) AS rn
    FROM prices
    WHERE Date <= '2020-12-31'
) t
WHERE rn = 1;

这个写法会按StockID分组，每组内按日期倒序排序，取第一条就是目标数据，InnoDB能通过UX_StockID_Date索引快速过滤，避免临时表全量扫描。

2. 创建覆盖索引，消除回表开销

如果窗口函数的效果仍不满足，可创建覆盖索引让查询完全走索引，无需访问主表：

CREATE INDEX IX_StockID_Date_Price ON prices(StockID, Date DESC, Price);

该索引包含了查询所需的所有字段，查询时直接从索引读取数据，大幅减少IO开销。

3. 预计算快照表/物化视图

如果需要频繁查询这类数据，建议建一张预计算的快照表，定期更新：

CREATE TABLE stock_daily_prices (
    StockID int(11) NOT NULL,
    Date datetime NOT NULL,
    Price decimal(18,4) NOT NULL,
    PRIMARY KEY(StockID, Date),
    PARTITION BY RANGE (TO_DAYS(Date)) (
        PARTITION p2020 VALUES LESS THAN (TO_DAYS('2021-01-01')),
        PARTITION p2021 VALUES LESS THAN (TO_DAYS('2022-01-01'))
        -- 按需添加后续年份分区
    )
) ENGINE=InnoDB;

用定时任务（比如每日收盘后）执行脚本，预计算当日所有股票的价格（含补取最近交易日数据）：

REPLACE INTO stock_daily_prices(StockID, Date, Price)
SELECT StockID, '2020-12-31' AS Date, Price
FROM (
    SELECT 
        StockID, 
        Price, 
        Date,
        ROW_NUMBER() OVER (PARTITION BY StockID ORDER BY Date DESC) AS rn
    FROM prices
    WHERE Date <= '2020-12-31'
) t
WHERE rn = 1;

后续查询指定日期时，直接从stock_daily_prices表中读取，速度接近实时。

4. 强制索引优化原查询

如果不想改写查询逻辑，可强制主查询使用唯一索引，同时尽量减少临时表的影响：

select P.*
from (
    SELECT StockID, Max(Date) As MaxDate
    FROM prices
    WHERE Date <= '2020-12-31'
    GROUP BY StockID
) as P1
join prices as P FORCE INDEX (UX_StockID_Date) 
on P.StockID = P1.StockID and P.Date = P1.MaxDate;

这个方案效果弱于前三种，但能在不改动核心逻辑的前提下小幅提升性能。

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