So optimieren Sie eine SQL-Abfrage mit über 2 Millionen Zeilen
P粉127901279
2023-09-01 18:09:31
<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>
我想您需要以下索引才能使查询良好执行(作为数据库设计的一部分,您只需执行一次)。
前两列可以按任意顺序排列。
只要同一
code, stationID
对不能有两行具有相同的日期时间,使用窗口函数就有点像使用大锤敲开坚果。它需要以下索引:
此查询应该花费不到 1 毫秒的时间,因为可以从索引构建派生表,然后它只从表中读取所需的行。
或者,如果您知道每个
code, stationID
对都会在特定时间段(1 小时、1 天、1 周)内收到更新的价格,那么您可以显着减少工作量窗口函数需要添加一个 where 子句: