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(

        'DENGAN cte AS
        (

        PILIH stationID SEBAGAI ind, kod, CAST(harga SEBAGAI GANDA ) SEBAGAI harga, tarikh

        ,ROW_NUMBER() LEBIH(
             PARTITION MENGIKUT kod, stationID
             PESANAN MENGIKUT tarikh DESC
         ) SEBAGAI terkini

        DARI harga
        )
        
        PILIH *
        DARI cte
        DI MANA terkini = 1
        '
    );

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

Edit: Lajur pertama mempunyai indeks yang dipanggil "id". Saya tidak tahu sama ada ini membantu.

Pangkalan data (InnoDB) kelihatan seperti ini:

id primary - int
stationID - int
kod - int
perpuluhan harga(10,5)
tarikh - datetime

Edit 2:

Hasilnya perlu dikumpulkan mengikut stationID dan berbilang baris perlu dipaparkan untuk setiap stationID. Satu baris untuk setiap kod dengan tarikh terkini.像这样：

22456:
      kod: 1
      harga: 3
      tarikh: 2023-06-21

      kod: 2
      harga: 2
      tarikh: 2023-06-21

      kod: 3
      harga: 5
      tarikh: 2023-06-21

22457:
      kod: 1
      harga: 10
      tarikh: 2023-06-21

      kod: 2
      harga: 1
      tarikh: 2023-06-21

      kod: 3
      harga: 33
      tarikh: 2023-06-21</pra>
<p>Output json hendaklah 像这样：</p>
<pre class="brush:php;toolbar:false;">{"1000001":[{"kod":1,"harga":1.661,"tarikh":"2023-06- 06 12:46:32","terkini":1},{"kod":2,"harga":1.867,"tarikh":"2023-06-06 12:46:32", "terkini":1},{"kod":3,"harga":1.05,"tarikh":"2023-06-06 12:46:32","terkini":1}, {"kod":5,"harga":1.818,"tarikh":"2023-06-06 12:46:32","terkini":1},{"kod":6, "harga":1.879,"tarikh":"2023-06-06 12:46:32","terkini":1}],"1000002":[{"kod":1," ;harga":1.65,"tarikh":"2023-06-03 08:53:26","terkini":1},{"kod":2,"harga":1.868," tarikh":"2023-06-03 08:53:26","terkini":1},{"kod":6,"harga":1.889,"tarikh":"2023-06 -03 08:53:27","terkini":1}],…

Answer 1

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.

Answer 2

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;