So optimieren Sie eine SQL-Abfrage mit über 2 Millionen Zeilen

Question

Ich habe eine SQL-Datenbank mit über 2 Millionen Zeilen und sie wächst schnell. Es gibt nicht viele Spalten, nur code, Preis, Datum und stationID.

Der Zweck besteht darin, den aktuellen Preis nach Code und Stations-ID zu erhalten. Die Abfrage funktioniert super, dauert aber über 10 Sekunden.

Gibt es eine Möglichkeit, die Abfrage zu optimieren?

$statement = $this->pdo->prepare(

        'MIT cte AS
        (

        SELECT stationID AS ind, code, CAST(price AS DOUBLE ) AS price, date

        ,ROW_NUMBER() OVER(
             PARTITION BY-Code, Stations-ID
             ORDER BY Datum BESCHREIBUNG
         ) AS spätestens

        AB Preis
        )
        
        WÄHLEN *
        VON cte
        WO spätestens = 1
        '
    );

    $statement->execute();
    $results = $statement->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_ASSOC);

Bearbeiten: Die erste Spalte hat einen Index namens „id“. Ich weiß nicht, ob das hilft.

Die Datenbank (InnoDB) sieht folgendermaßen aus:

id Primary - int
stationID - int
Code - int
Preis-Dezimal(10,5)
Datum – Datum/Uhrzeit

Bearbeiten 2:

Die Ergebnisse müssen nach Stations-ID gruppiert werden und für jede Stations-ID müssen mehrere Zeilen angezeigt werden. Eine Zeile für jeden Code mit dem neuesten Datum.像这样:

22456:
      Code: 1
      Preis: 3
      Datum: 21.06.2023

      Code: 2
      Preis: 2
      Datum: 21.06.2023

      Code: 3
      Preis: 5
      Datum: 21.06.2023

22457:
      Code: 1
      Preis: 10
      Datum: 21.06.2023

      Code: 2
      Preis: 1
      Datum: 21.06.2023

      Code: 3
      Preis: 33
      Datum: 21.06.2023

Die JSON-Ausgabe sollte wie folgt aussehen:

{"1000001":[{"code":1,"preis":1.661,"datum":"2023-06- 06 12:46:32","neueste":1},{"Code":2,"Preis":1.867, "Datum":"2023-06-06 12:46:32", "latest":1},{"code":3,"price":1.05,"date":"2023-06-06 12:46:32","latest":1}, {"Code":5,"Preis":1.818,"Datum":"2023-06-06 12:46:32","neueste":1},{"Code":6, "Preis":1.879,"Datum":"2023-06-06 12:46:32","neueste":1}],"1000002":[{"Code":1," ;Preis":1,65,"Datum":"2023-06-03 08:53:26","neueste":1},{"Code":2,"Preis":1,868," date":"2023-06-03 08:53:26","latest":1},{"code":6,"price":1.889,"date":"2023-06 -03 08:53:27","latest":1}],…

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;