P粉9738995672023-08-21 11:26:01
UPD: 2017-03-31,MySQL的版本5.7.5預設啟用了ONLY_FULL_GROUP_BY開關(因此,非確定性的GROUP BY查詢被停用)。此外,他們更新了GROUP BY的實現方式,即使禁用了開關,解決方案可能不再按預期工作。需要進行檢查。
Bill Karwin的解決方案在群組內項目數量較小時效果良好,但是當群組較大時,查詢的效能變差,因為解決方案需要進行大約n*n/2 n/2
次IS NULL
比較。
我在一個包含18684446
行和1182
個群組的InnoDB表上進行了測試。此表包含功能測試的測試結果,且(test_id, request_id)
是主鍵。因此,test_id
是一個群組,我正在尋找每個test_id
的最後一個request_id
。
Bill的解決方案已經在我的戴爾e4310上運行了幾個小時,我不知道它何時會完成,儘管它在覆蓋索引上操作(因此在EXPLAIN中顯示using index
)。
我還有幾個基於相同思路的解決方案:
group_id
中的最大(group_id, item_value)
對就是每個group_id
的最後一個值,如果我們按降序遍歷索引,則是每個group_id
的第一個值;3 ways MySQL uses indexes是一篇很好的文章,可以了解一些細節。
解決方案1
#這個解決方案非常快,對於我1800萬 行的數據,大約需要0.8秒:
SELECT test_id, MAX(request_id) AS request_id FROM testresults GROUP BY test_id DESC;
如果要改變順序為升序,將其放入子查詢中,只傳回ID,並將其作為子查詢與其他列連接:
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;
對於我的數據,這個解決方案大約需要1.2秒。
解決方案2
#這是另一個解決方案,對於我的表,大約需要19秒:
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
它也按降序傳回測試結果。它的速度較慢,因為它進行了完整的索引掃描,但是它可以給你一個關於如何為每個組輸出N個最大行的想法。
該查詢的缺點是它的結果無法被查詢快取。
P粉2677913262023-08-21 09:55:33
MySQL 8.0現在支援視窗函數,幾乎所有流行的SQL實作都支援。使用這種標準語法,我們可以寫最大-n-per-group查詢:
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;
MySQL手冊中展示了此方法及其他尋找分組最大行的方法。
以下是我在2009年為這個問題寫的原始答案:
我這樣寫解決方案:
SELECT m1.* FROM messages m1 LEFT JOIN messages m2 ON (m1.name = m2.name AND m1.id < m2.id) WHERE m2.id IS NULL;
關於效能,根據資料的性質,其中一個解決方案可能會更好。因此,您應該測試兩個查詢,並根據資料庫的效能選擇更好的查詢。
例如,我有一個StackOverflow八月資料轉儲的副本。我將用它進行基準測試。在Posts
表中有1,114,357行資料。這是在我的Macbook Pro 2.40GHz上運行的MySQL 5.0.75。
我將編寫一個查詢來尋找給定用戶ID(我的)的最新貼文。
首先使用了Eric在子查詢中使用GROUP BY
的技術:
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行结果(1分17.89秒)
即使EXPLAIN
分析也需要超過16秒:
+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+ | 1 | PRIMARY | <derived2> | 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行结果(16.09秒)
現在使用LEFT JOIN
使用我的技術產生相同的查詢結果:
SELECT p1.postid FROM Posts p1 LEFT JOIN posts p2 ON (p1.owneruserid = p2.owneruserid AND p1.postid < p2.postid) WHERE p2.postid IS NULL AND p1.owneruserid = 20860; 1行结果(0.28秒)
EXPLAIN
分析顯示兩個資料表都能使用它們的索引:
+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+ | 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行结果(0.00秒)
這是我的Posts
表格的DDL:
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;
評論者註意:如果您想要使用不同版本的MySQL、不同的資料集或不同的表設計進行另一個基準測試,請隨意自行進行。我已經展示了上述技術。 Stack Overflow的目的是要向您展示如何進行軟體開發工作,而不是為您完成所有工作。