SQLite分组取Top N查询优化：200万行表查涨跌TOP10耗时31秒如何优化

原查询性能瓶颈分析 #

• 两次全表扫描200万行的DimCompanyPrice表，重复计算窗口函数，计算开销直接翻倍
• 没有匹配查询逻辑的索引，窗口函数的分区、排序操作需要临时构建B树，占用大量CPU和IO资源
• 存在冗余操作：两个CTE内部的ORDER BY CompanyID ASC完全没有作用，反而增加了额外的排序开销

优化方案 #

1. 新增覆盖索引（优先级最高） #

创建联合覆盖索引，直接匹配窗口函数的分区、排序逻辑，同时覆盖需要查询的字段，避免回表和临时排序：

CREATE INDEX idx_company_date_price ON DimCompanyPrice (CompanyID, Date DESC, CloseAdjusted);

加索引后窗口函数可以直接利用索引的有序性，不需要全表扫描和临时B树排序，性能可提升10倍以上。

2. 改写SQL，仅单次扫描表 #

原查询两个CTE各扫一次表，改写后仅需一次扫描就拿到每个公司最近两天的价格，开销直接减半：

WITH latest_prices AS (
    SELECT 
        CompanyID,
        CloseAdjusted,
        row_number() OVER (PARTITION BY CompanyID ORDER BY Date DESC) AS rn
    FROM DimCompanyPrice
    -- 可选：如果日期格式符合SQLite标准，可加最近7天过滤，进一步缩小扫描范围
    -- WHERE Date >= date('now', '-7 day')
)
SELECT 
    t1.CompanyID,
    100.0 * (t1.CloseAdjusted - t2.CloseAdjusted) / t2.CloseAdjusted AS gain
FROM latest_prices t1
INNER JOIN latest_prices t2 
    ON t1.CompanyID = t2.CompanyID 
    AND t1.rn = 1 -- 最新交易日数据
    AND t2.rn = 2 -- 前一交易日数据
ORDER BY gain DESC
LIMIT 10;

如果需要同时获取跌幅前10的股票，追加UNION ALL逻辑即可：

UNION ALL
SELECT 
    t1.CompanyID,
    100.0 * (t1.CloseAdjusted - t2.CloseAdjusted) / t2.CloseAdjusted AS gain
FROM latest_prices t1
INNER JOIN latest_prices t2 
    ON t1.CompanyID = t2.CompanyID 
    AND t1.rn = 1 
    AND t2.rn = 2
ORDER BY gain ASC
LIMIT 10;

3. 可选缓存方案（适配Web高并发场景） #

因为你每日仅新增少量价格数据，数据为日级更新，完全可以采用预计算缓存方案：

• 每日收盘新增价格数据后，定时跑一次计算任务，把所有股票的涨跌幅计算好存入专门的结果表StockGainRank
• Web端查询时直接从结果表取前10涨幅、前10跌幅数据，查询耗时可以控制在1ms以内，完全不需要每次动态计算
• 实现成本极低，稳定性远高于动态查询，非常适合面向用户的Web展示场景

优化效果 #

按前两个方案优化后，原31秒的查询可以降到1秒以内，搭配缓存方案可达到毫秒级响应，完全满足Web场景的性能要求。

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