如何最佳化超過 200 萬行的 SQL 查詢

Question

<p>我有一個包含超過 200 萬行的 Sql 資料庫，而且成長速度很快。欄位不多，只有<code>代碼、價格、日期和stationID</code>。 </p> <p>目的是透過程式碼和stationID來取得最新價格。 查詢效果很好，但需要 10 多秒。 </p> <p>有沒有辦法優化查詢？ </p> <pre class="brush:php;toolbar:false;">$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);</pre> <p>編輯： 第一列有一個名為「id」的索引。我不知道這是否有幫助。 </p> <p>資料庫（InnoDB）如下所示：</p> <pre class="brush:php;toolbar:false;">id primary - int stationID - int code - int price - decimal(10,5) date - datetime</pre> <p>編輯2：</p> <p>結果需要按stationID分組，每個stationID需要顯示多行。每個帶有最新日期的代碼一行。像這樣：</p> <pre class="brush:php;toolbar:false;">22456: 代碼：1 價格：3 日期：2023-06-21 代碼：2 價格：2 日期：2023-06-21 代碼：3 價格：5 日期：2023-06-21 22457： 代碼：1 價格：10 日期：2023-06-21 代碼：2 價格：1 日期：2023-06-21 代碼：3 價格：33 日期：2023-06-21</pre> <p>json 輸出應該是這樣的：</p>

{"1000001":[{"代碼":1,"價格":1.661,"日期":"2023-06- 06 12:46:32" ,"最新":1},{"代碼":2,"價格":1.867,"日期":"2023-06-06 12:46:32", "最新":1},{"代碼": 3,"價格":1.05,"日期":"2023-06-06 12:46:32","最新":1}, {"code":5,"price":1.818,"date":" 2023-06-06 12:46:32","latest":1},{"code":6, “價格”:1.879,“日期”:“2023-06-06 12:46:32”,“最新”:1}],“1000002”:[{“代碼”:1,” ;價格”:1.65,“日期”:“2023-06-03 08:53:26”,“最新”:1}, {“代碼”:2,“價格”:1.868,”日期”:“2023-06-03 08:53:26”,“最新”:1},{“代碼”:6,“價格”:1.889, 「日期」:「2023-06 -03 08:53:27","最新」:1}],…</pre></p>            

Answer 1

我想您需要以下索引才能使查詢良好執行（作為資料庫設計的一部分，您只需執行一次）。

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

前兩列可以任意順序排列。

Answer 2

只要同一 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;