Cara mengoptimumkan pertanyaan SQL dengan lebih 2 juta baris

Question

Saya mempunyai pangkalan data Sql dengan lebih 2 juta baris dan ia berkembang pesat. Tiada banyak lajur, cuma kod, harga, tarikh dan stationID. Tujuannya adalah untuk mendapatkan harga terkini mengikut kod dan stationID. Pertanyaan berfungsi hebat tetapi mengambil masa lebih 10 saat. Adakah terdapat cara untuk mengoptimumkan pertanyaan? $statement=$this->pdo->prepare('WITHcteAS(SELECTstationIDASind,code,CAST(priceASDOUBLE)ASp

P粉141455512 · Answer

Saya rasa anda memerlukan indeks berikut untuk pertanyaan dapat dilaksanakan dengan baik (anda hanya perlu melakukannya sekali sebagai sebahagian daripada reka bentuk pangkalan data).

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

Dua lajur pertama boleh disusun mengikut sebarang susunan.

P粉297434909 · Answer

Selagi pasangan code, stationID yang sama tidak boleh mempunyai dua baris dengan tarikh masa yang sama, menggunakan fungsi tetingkap adalah sedikit seperti menggunakan tukul besi untuk memecahkan kacang.

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;

Ia memerlukan indeks berikut:

alter table price add index (code, stationID, date desc);

Pertanyaan ini sepatutnya mengambil masa kurang daripada 1ms kerana jadual terbitan boleh dibina daripada indeks dan kemudian ia hanya membaca baris yang diperlukan daripada jadual.

Sebagai alternatif, jika anda tahu bahawa setiap pasangan code, stationID akan menerima harga yang dikemas kini dalam tempoh masa tertentu (1 jam, 1 hari, 1 minggu), anda boleh mengurangkan dengan ketara jumlah kerja yang perlu dilakukan oleh fungsi tetingkap dengan menambahkan klausa 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;