如何优化超过 200 万行的 SQL 查询

Question

我有一个包含超过200万行的Sql数据库，并且增长速度很快。列不多，只有代码、价格、日期和stationID。目的是通过代码和stationID获取最新价格。查询效果很好，但需要10多秒。有没有办法优化查询？$statement=$this->pdo->prepare('WITHcteAS(SELECTstationIDASind,code,CAST(priceASDOUBLE)ASp

P粉141455512 · Answer

我想您需要以下索引才能使查询良好执行（作为数据库设计的一部分，您只需执行一次）。

CREATE INDEX IX ON price
  (code, stationID, date DESC, price)

前两列可以按任意顺序排列。

P粉297434909 · Answer

只要同一 code, stationID 对不能有两行具有相同的日期时间，使用窗口函数就有点像使用大锤敲开坚果。

select p.stationID, p.code, p.price, p.date
from (
    select code, stationID, max(date) as max_date
    from price
    group by code, stationID
) max
join price p
    on max.code = p.code
   and max.stationID = p.stationID
   and max.max_date = p.date;

它需要以下索引：

alter table price add index (code, stationID, date desc);

此查询应该花费不到 1 毫秒的时间，因为可以从索引构建派生表，然后它只从表中读取所需的行。

或者，如果您知道每个 code, stationID 对都会在特定时间段（1 小时、1 天、1 周）内收到更新的价格，那么您可以显着减少工作量窗口函数需要添加一个 where 子句：

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
    where date >= now() - interval 1 week
)
select * from cte where latest  = 1;

如何优化超过 200 万行的 SQL 查询

全部回复(2)我来回复