MySQL: Dapatkan entri terkini dalam setiap kumpulan-Soal Jawab Rangkaian PHP Cina

Artikel Topik pembelajaran Muat turun Soal Jawab Kamus Pengaturcaraan Permainan kemas kini terkini

简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)

MySQL: Dapatkan entri terkini dalam setiap kumpulan

P粉569205478 2023-10-09 12:38:29

504

Terdapat jadualmessagesyang mengandungi data seperti ini:

Id Name Other_Columns ------------------------- 1 A A_data_1 2 A A_data_2 3 A A_data_3 4 B B_data_1 5 B B_data_2 6 C C_data_1

Jika saya menjalankan pertanyaanselect * from messages group by name, hasil yang saya akan dapat ialah:

1 A A_data_1 4 B B_data_1 6 C C_data_1

Apakah pertanyaan yang akan mengembalikan hasil berikut?

3 A A_data_3 5 B B_data_2 6 C C_data_1

Maksudnya, rekod terakhir dalam setiap kumpulan hendaklah dikembalikan.

Pada masa ini, ini adalah pertanyaan yang saya gunakan:

SELECT * FROM (SELECT * FROM messages ORDER BY id DESC) AS x GROUP BY name

Tetapi ini nampaknya sangat tidak cekap. Adakah terdapat cara lain untuk mencapai hasil yang sama?

P粉569205478

membalas semua (2)

P粉1565327062023-10-10 11:51:46 Tingkat 2

UPD: 31-03-2017, versi5.7.5MySQL mendayakan suis ONLY_FULL_GROUP_BY secara lalai (jadi pertanyaan GROUP BY bukan deterministik dilumpuhkan). Selain itu, mereka mengemas kini pelaksanaan GROUP BY dan penyelesaiannya mungkin tidak berfungsi seperti yang diharapkan walaupun dengan suis dilumpuhkan. Perlu menyemaknya.

Penyelesaian Bill Karwin di atas berfungsi dengan baik apabila bilangan item dalam kumpulan agak kecil, tetapi apabila kumpulan agak besar prestasi pertanyaan menjadi lemah kerana penyelesaian memerlukan lebih kurang n*n/2 + n/2 Hanya bandingkanIS NULLIS NULL.

Saya menguji pada jadual InnoDB yang mengandungi 18684446 18684446行和 1182组的 InnoDB 表上进行了测试。该表包含功能测试的测试结果，并以 (test_id, request_id)作为主键。因此， test_id是一个组，我正在为每个 test_id搜索最后一个 request_idbaris dan 1182

kumpulan. Jadual ini mengandungi keputusan ujian untuk ujian berfungsi dan mempunyai

(test_id, request_id)sebagai kunci utama. Jadi,test_id
ialah kumpulan dan saya sedang mencarirequest_idterakhir untuk setiaptest_id.
            
             Penyelesaian Bill telah berjalan pada Dell e4310 saya selama beberapa jam sekarang, dan walaupun ia berjalan pada indeks penutup (oleh itu menggunakan indeks dalam EXPLAIN), saya tidak tahu bila ia akan lengkap.(group_id, item_value)对是每个group_id中的最后一个值，即如果我们按降序遍历索引，则为每个group_idSaya mempunyai beberapa penyelesaian lain berdasarkan idea yang sama:
             
             Jika indeks pendasar ialah indeks BTREE (yang selalunya berlaku), maka pasangan
              
               (group_id, item_value)
              terbesar ialah nilai terakhir dalam setiap
             group_id, iaitu jika kita melintasi indeks dalam menurun pesanan, Maka ia adalah yang pertama daripada setiap
               
                id_kumpulan
               
;
            
Jika kita membaca nilai yang diliputi oleh indeks, nilai dibaca dalam susunan indeksSetiap indeks secara tersirat mengandungi lajur kunci utama yang dilampirkan pada indeks tersebut (iaitu kunci utama berada dalam indeks penutup). Dalam penyelesaian di bawah saya beroperasi secara langsung pada kunci utama, dalam kes anda, anda hanya perlu menambah lajur kunci utama kepada hasilnya.
Dalam banyak kes, adalah lebih murah untuk mengumpul ID baris yang diperlukan dalam susunan yang dikehendaki dalam subkueri dan menggabungkan hasil subkueri kepada ID. Oleh kerana untuk setiap baris dalam hasil subquery, MySQL perlu melakukan pengambilan berdasarkan kunci utama, subquery akan dimasukkan ke dalam join dahulu, dan baris akan dikeluarkan mengikut susunan id dalam subquery (jika kita tinggalkan ORDER BY yang jelas untuk menyertai )
3 Cara MySQL Menggunakan Indeks
ialah artikel yang bagus untuk membantu anda memahami beberapa butiran.
Penyelesaian 1
Ini sangat pantas, mengambil masa kira-kira 0.8 saat pada baris 18J+ saya:
SELECT test_id, MAX(request_id) AS request_id FROM testresults GROUP BY test_id DESC;
Jika anda ingin menukar susunan kepada ASC, masukkannya dalam subkueri yang hanya mengembalikan id dan gunakannya sebagai subkueri untuk menyertai lajur yang lain:
SELECT test_id, request_id FROM ( SELECT test_id, MAX(request_id) AS request_id FROM testresults GROUP BY test_id DESC) as ids ORDER BY test_id;
Ini mengambil masa kira-kira 1.2 saat untuk data saya. Penyelesaian 2 Ini satu lagi penyelesaian yang mengambil masa kira-kira 19 saat untuk jam tangan saya:SELECT test_id, request_id FROM testresults, (SELECT @group:=NULL) as init WHERE IF(IFNULL(@group, -1)=@group:=test_id, 0, 1) ORDER BY test_id DESC, request_id DESC
Ia juga mengembalikan ujian dalam susunan menurun. Ia jauh lebih perlahan kerana ia melakukan imbasan indeks penuh, tetapi ia memberi anda idea tentang cara mengeluarkan N baris maksimum untuk setiap kumpulan.
Kelemahan pertanyaan ini ialah cache pertanyaan tidak boleh menyimpan hasil cariannya.

Suka+0

Tambah Balasan

P粉4632912482023-10-10 00:12:34 Tingkat 1

MySQL 8.0 kini menyokongfungsi tetingkap, seperti hampir semua pelaksanaan SQL yang popular. Menggunakan sintaks standard ini, kita boleh menulis sehingga n pertanyaan bagi setiap kumpulan:

WITH ranked_messages AS ( SELECT m.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) AS rn FROM messages AS m ) SELECT * FROM ranked_messages WHERE rn = 1;

Ini dan kaedah mencari yang lainbilangan maksimum baris yang dikumpulkan diterangkan dalam manual MySQL.

Berikut ialah jawapan asal yang saya tulis untuk soalan ini pada tahun 2009:

Saya menulis penyelesaian seperti ini:

SELECT m1.* FROM messages m1 LEFT JOIN messages m2 ON (m1.name = m2.name AND m1.idBerkenaan prestasi, satu penyelesaian mungkin lebih baik bergantung pada sifat data. Oleh itu, anda harus menguji kedua-dua pertanyaan dan menggunakan pertanyaan yang mempunyai prestasi yang lebih baik berdasarkan pangkalan data anda.
Sebagai contoh, saya mempunyai salinanStackOverflow Ogos pembuangan data. Saya akan menggunakannya untuk tujuan penanda aras.PostsTerdapat 1,114,357 baris dalam jadual. Ia berjalan padaMySQL5.0.75 pada Macbook Pro 2.40GHz saya.
Saya akan menulis pertanyaan untuk mencari siaran terkini untuk ID pengguna yang diberikan (saya).
Mula-mula gunakan teknikditunjukkan oleh @Eric menggunakanGROUP BYdalam subkueri:
SELECT p1.postid FROM Posts p1 INNER JOIN (SELECT pi.owneruserid, MAX(pi.postid) AS maxpostid FROM Posts pi GROUP BY pi.owneruserid) p2 ON (p1.postid = p2.maxpostid) WHERE p1.owneruserid = 20860; 1 row in set (1 min 17.89 sec)
MalahEXPLAINanalisis mengambil masa lebih 16 saat:
+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+ | 1 | PRIMARY |
            
             | ALL | NULL | NULL | NULL | NULL | 76756 | | | 1 | PRIMARY | p1 | eq_ref | PRIMARY,PostId,OwnerUserId | PRIMARY | 8 | p2.maxpostid | 1 | Using where | | 2 | DERIVED | pi | index | NULL | OwnerUserId | 8 | NULL | 1151268 | Using index | +----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+ 3 rows in set (16.09 sec)
            
Sekarang gunakanPetua saya untuk menggunakanLEFT JOIN:
SELECT p1.postid FROM Posts p1 LEFT JOIN posts p2 ON (p1.owneruserid = p2.owneruserid AND p1.postidEXPLAINAnalisis menunjukkan bahawa kedua-dua jadual boleh menggunakan indeksnya:
+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+ | 1 | SIMPLE | p1 | ref | OwnerUserId | OwnerUserId | 8 | const | 1384 | Using index | | 1 | SIMPLE | p2 | ref | PRIMARY,PostId,OwnerUserId | OwnerUserId | 8 | const | 1384 | Using where; Using index; Not exists | +----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+ 2 rows in set (0.00 sec)
            
Ini adalah DDL jadualPostssaya:
CREATE TABLE `posts` ( `PostId` bigint(20) unsigned NOT NULL auto_increment, `PostTypeId` bigint(20) unsigned NOT NULL, `AcceptedAnswerId` bigint(20) unsigned default NULL, `ParentId` bigint(20) unsigned default NULL, `CreationDate` datetime NOT NULL, `Score` int(11) NOT NULL default '0', `ViewCount` int(11) NOT NULL default '0', `Body` text NOT NULL, `OwnerUserId` bigint(20) unsigned NOT NULL, `OwnerDisplayName` varchar(40) default NULL, `LastEditorUserId` bigint(20) unsigned default NULL, `LastEditDate` datetime default NULL, `LastActivityDate` datetime default NULL, `Title` varchar(250) NOT NULL default '', `Tags` varchar(150) NOT NULL default '', `AnswerCount` int(11) NOT NULL default '0', `CommentCount` int(11) NOT NULL default '0', `FavoriteCount` int(11) NOT NULL default '0', `ClosedDate` datetime default NULL, PRIMARY KEY (`PostId`), UNIQUE KEY `PostId` (`PostId`), KEY `PostTypeId` (`PostTypeId`), KEY `AcceptedAnswerId` (`AcceptedAnswerId`), KEY `OwnerUserId` (`OwnerUserId`), KEY `LastEditorUserId` (`LastEditorUserId`), KEY `ParentId` (`ParentId`), CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`PostTypeId`) REFERENCES `posttypes` (`PostTypeId`) ) ENGINE=InnoDB;
            
Nota kepada pengulas: Jika anda ingin menjalankan penanda aras lain menggunakan versi MySQL yang berbeza, set data yang berbeza atau reka bentuk jadual yang berbeza, sila lakukan sendiri. Saya telah menunjukkan teknik di atas. Stack Overflow ada di sini untuk menunjukkan kepada anda cara melakukan kerja pembangunan perisian, bukan untuk melakukan semua kerja untuk anda.