So optimieren Sie eine SQL-Abfrage mit über 2 Millionen Zeilen
P粉127901279
P粉127901279 2023-09-01 18:09:31
0
2
563
<p>Ich habe eine SQL-Datenbank mit über 2 Millionen Zeilen und sie wächst schnell. Es gibt nicht viele Spalten, nur <code>code, Preis, Datum und stationID</code>. </p> <p>Der Zweck besteht darin, den aktuellen Preis nach Code und Stations-ID zu erhalten. Die Abfrage funktioniert super, dauert aber über 10 Sekunden. </p> <p>Gibt es eine Möglichkeit, die Abfrage zu optimieren? </p> <pre class="brush:php;toolbar:false;">$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);</pre> <p>Bearbeiten: Die erste Spalte hat einen Index namens „id“. Ich weiß nicht, ob das hilft. </p> <p>Die Datenbank (InnoDB) sieht folgendermaßen aus: </p> <pre class="brush:php;toolbar:false;">id Primary - int stationID - int Code - int Preis-Dezimal(10,5) Datum – Datum/Uhrzeit</pre> <p>Bearbeiten 2:</p> <p>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.像这样:</p> <pre class="brush:php;toolbar:false;">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</pre> <p>Die JSON-Ausgabe sollte wie folgt aussehen:</p> <pre class="brush:php;toolbar:false;">{"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}],…</pre></p>
P粉127901279
P粉127901279

Antworte allen(2)
P粉141455512

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

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

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

P粉297434909

只要同一 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;
Beliebte Tutorials
Mehr>
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage