Saya mempunyai pangkalan data Sql dengan lebih 2 juta baris dan ia berkembang pesat. Tiada banyak lajur, cuma
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-21Output 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}],…
Saya rasa anda memerlukan indeks berikut untuk pertanyaan dapat dilaksanakan dengan baik (anda hanya perlu melakukannya sekali sebagai sebahagian daripada reka bentuk pangkalan data).
Dua lajur pertama boleh disusun mengikut sebarang susunan.
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.Ia memerlukan indeks berikut:
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 :