如何最佳化超過 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)我來回復