Cara mengoptimumkan pertanyaan SQL dengan lebih 2 juta baris
P粉127901279
P粉127901279 2023-09-01 18:09:31
0
2
455

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 

Output json hendaklah 像这样:

{"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}],…

P粉127901279
P粉127901279

membalas semua (2)
P粉141455512

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

    Selagi pasangancode, stationIDyang 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 pasangancode, stationIDakan 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;
      Muat turun terkini
      Lagi>
      kesan web
      Kod sumber laman web
      Bahan laman web
      Templat hujung hadapan
      Tentang kita Penafian Sitemap
      Laman web PHP Cina:Latihan PHP dalam talian kebajikan awam,Bantu pelajar PHP berkembang dengan cepat!