前言

在寫(xiě)報(bào)表功能時(shí)遇到一個(gè)需要根據(jù)用戶id分組查詢最新一條錢(qián)包明細(xì)數(shù)據(jù)的需求，在寫(xiě)sql測(cè)試時(shí)遇到一個(gè)有趣的問(wèn)題，開(kāi)始使用子查詢根據(jù)時(shí)間倒序+group by customer_id發(fā)現(xiàn)查詢出來(lái)的數(shù)據(jù)一直都是最舊的一條，而不是我需要的最新一條數(shù)據(jù)我明明已經(jīng)倒序排了，后來(lái)總結(jié)出了五種解決方案如下。

注意事項(xiàng)

數(shù)據(jù)庫(kù)版本 Mysql5.7+

執(zhí)行 GROUP BY 語(yǔ)句的時(shí)候出現(xiàn) sql_mode=only_full_group_by 解決方法（這里是Mysql8的解決方案，Mysql5.7也差不多，具體實(shí)現(xiàn)可以查看 解決MySQL-this is incompatible with sql_mode=only_full_group_by 問(wèn)題）

1、執(zhí)行 select @@sql_mode; 查看sql模式

select @@sql_mode;

2、將sql_mode中的only_full_group_by模式剔除 重新設(shè)置sql_mode值，如果是使用JDBC連接需要重啟項(xiàng)目才能生效。

set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

準(zhǔn)備SQL

這里模擬一個(gè)sql

DROP TABLE IF EXISTS `customer_wallet_detail`;
CREATE TABLE `customer_wallet_detail`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `customer_id` bigint(20) NULL DEFAULT NULL COMMENT '用戶ID',
  `happen_amount` varchar(15)  NULL DEFAULT '0' COMMENT '發(fā)生金額 帶-號(hào)的代表扣款',
  `balance_amount` varchar(15) NULL DEFAULT '0' COMMENT '可用余額',
  `create_time` bigint(20) NULL DEFAULT NULL COMMENT '發(fā)生時(shí)間',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB COMMENT = '用戶錢(qián)包明細(xì)';

INSERT INTO `customer_wallet_detail`(`id`, `customer_id`, `happen_amount`, `balance_amount`, `create_time`) VALUES (1, 1, '100', '100', 1670300656630);
INSERT INTO `customer_wallet_detail`(`id`, `customer_id`, `happen_amount`, `balance_amount`, `create_time`) VALUES (2, 1, '-10', '90', 1670300656640);
INSERT INTO `customer_wallet_detail`(`id`, `customer_id`, `happen_amount`, `balance_amount`, `create_time`) VALUES (3, 1, '5', '95', 1670300656650);
INSERT INTO `customer_wallet_detail`(`id`, `customer_id`, `happen_amount`, `balance_amount`, `create_time`) VALUES (4, 3, '998', '998', 1670300656660);
INSERT INTO `customer_wallet_detail`(`id`, `customer_id`, `happen_amount`, `balance_amount`, `create_time`) VALUES (5, 3, '-100', '898', 1670300656670);
INSERT INTO `customer_wallet_detail`(`id`, `customer_id`, `happen_amount`, `balance_amount`, `create_time`) VALUES (6, 3, '-98', '800', 1670300656680);
INSERT INTO `customer_wallet_detail`(`id`, `customer_id`, `happen_amount`, `balance_amount`, `create_time`) VALUES (7, 2, '666', '666', 1670300656690);
INSERT INTO `customer_wallet_detail`(`id`, `customer_id`, `happen_amount`, `balance_amount`, `create_time`) VALUES (8, 2, '-66', '600', 1670300656695);
INSERT INTO `customer_wallet_detail`(`id`, `customer_id`, `happen_amount`, `balance_amount`, `create_time`) VALUES (9, 2, '-600', '0', 1670300656699);

錯(cuò)誤查詢

SELECT
	* 
FROM
	( SELECT * FROM customer_wallet_detail ORDER BY create_time DESC ) t1 
GROUP BY
	t1.customer_id;

錯(cuò)誤原因

在mysql5.7以及之后的版本，如果GROUP BY的子查詢中包含ORDER BY，但是 GROUP BY 不與 LIMIT 配合使用，ORDER BY會(huì)被忽略掉，所以子查詢?cè)?nbsp;GROUP BY 時(shí)排序不會(huì)生效，可能是因?yàn)樽硬樵兇蠖鄶?shù)是作為一個(gè)結(jié)果給主查詢使用，所以子查詢不需要排序。

方法一

鑒于以上的原因我們可以添加上 LIMIT 條件來(lái)實(shí)現(xiàn)功能。
PS：這個(gè)LIMIT的數(shù)量可以先自行 COUNT 出你要遍歷的數(shù)據(jù)條數(shù)（這個(gè)數(shù)據(jù)條數(shù)是所有滿足查詢條件的數(shù)據(jù)合，我這里共9條數(shù)據(jù)）

SELECT
	* 
FROM
	( SELECT * FROM customer_wallet_detail ORDER BY create_time DESC LIMIT 9 ) t1 
GROUP BY
	t1.customer_id;

方法二（適用于自增ID和創(chuàng)建時(shí)間排序一致）

方法一需要先 COUNT 查詢?nèi)缓髮⒉樵兘Y(jié)果設(shè)置到 LIMIT 條件中比較麻煩，這里還可以使用 MAX() 函數(shù)來(lái)實(shí)現(xiàn)該功能。
PS：因?yàn)槲疫@里的業(yè)務(wù)數(shù)據(jù)是有序插入的，使用主鍵自增id和create_time結(jié)果是一樣的而且使用id查詢效率更高，如果沒(méi)有唯一且有序的id可以替代create_time那么就用方案一，不能直接使用 SELECT id,MAX(create_time) 這種操作來(lái)獲取最新一條數(shù)據(jù)id，原因在總結(jié)中有詳細(xì)描述。

SELECT
	*
FROM
	customer_wallet_detail 
WHERE
	id IN ( SELECT MAX( id ) FROM customer_wallet_detail GROUP BY customer_id ) 
ORDER BY
	customer_id;

方法三（適用于自增ID和創(chuàng)建時(shí)間排序一致，查詢性能最優(yōu)）

方法三和方法二實(shí)現(xiàn)邏輯基本一致只是將IN查詢替換成了連接查詢，本地20w條數(shù)據(jù)測(cè)試 方法三比方法二性能提升50%，有興趣的可以增大數(shù)據(jù)集測(cè)試后續(xù)性能變化。

SELECT
	t1.* 
FROM
	customer_wallet_detail t1
	INNER JOIN ( SELECT MAX( id ) AS id FROM customer_wallet_detail GROUP BY customer_id ) t2 ON t1.id = t2.id

方法四（通過(guò)DISTINCT關(guān)鍵字打破MySQL語(yǔ)句優(yōu)化使排序生效）

方法四實(shí)現(xiàn)起來(lái)比較簡(jiǎn)單，數(shù)據(jù)量小的時(shí)候查詢性能也挺不錯(cuò)的，數(shù)據(jù)量大了之后查詢性能也還可以，我本地測(cè)試了100w數(shù)據(jù)的查詢，這個(gè)方法耗時(shí)0.9s左右，減少DISTINCT的字段能降到0.4s左右，不給customer_id字段加索引的情況下通過(guò)方法三查詢耗時(shí)0.35s，加了索引耗時(shí)0.035s，有興趣可以分析一下方法三和方法四的執(zhí)行計(jì)劃。

SELECT
	* 
FROM
	( SELECT DISTINCT * FROM `customer_wallet_detail` ORDER BY id DESC ) AS t1 
GROUP BY
	t1.customer_id;

方法五（以創(chuàng)建時(shí)間為基準(zhǔn)獲取每個(gè)用戶最新的一條數(shù)據(jù)，必須要添加對(duì)應(yīng)字段的索引 最好是覆蓋索引）

有朋友在評(píng)論區(qū)提供了第四種方法，這種方法在表數(shù)據(jù)量少的時(shí)候是可行的，我的測(cè)試表還是20w數(shù)據(jù)，并且customer_id字段加了索引，全部查詢出來(lái)耗時(shí)在180s左右，我本地MySQL性能會(huì)差一點(diǎn)，這種查詢方式是將 b1 中的每一條數(shù)據(jù) 都和 b2 中的每一條數(shù)據(jù)進(jìn)行比對(duì)取出滿足條件的數(shù)據(jù)，b1 有20w條數(shù)據(jù) b2 也有20w條數(shù)據(jù)，如果沒(méi)有索引不計(jì)算io開(kāi)銷(xiāo)，只算cpu開(kāi)銷(xiāo)，這條sql需要進(jìn)行 20w * 20w = 400億次數(shù)據(jù)比對(duì)，在有索引的情況下數(shù)據(jù)比對(duì)次數(shù)會(huì)少一些但是也千萬(wàn)級(jí)的，如果考慮其它開(kāi)銷(xiāo)并且沒(méi)索引的情況下那查詢耗時(shí)可想而知。

使用限制

• 1、這種方式其實(shí)除了性能問(wèn)題以外還有一個(gè)更加嚴(yán)重的問(wèn)題，在一些業(yè)務(wù)里給用戶余額明細(xì)添加數(shù)據(jù)時(shí)可能同一時(shí)間戳添加多條，這樣count結(jié)果就大于1了，這個(gè)用戶數(shù)據(jù)就查不出來(lái)了，還有一種情況如果開(kāi)發(fā)人員事務(wù)沒(méi)有控制好，我們?cè)谌霂?kù)時(shí)一般會(huì)提前將create_time填充，但是我們用的是自增ID，入庫(kù)時(shí)create_time 小的，數(shù)據(jù)ID可能還會(huì)大一些，選擇那種方法還是需要看業(yè)務(wù)上怎么設(shè)計(jì)的

SELECT
	b1.* 
FROM
	customer_wallet_detail t1 
WHERE
	( SELECT COUNT( 1 ) FROM customer_wallet_detail t1 WHERE t2.customer_id = t1.customer_id AND t1.create_time <= t2.create_time ) <= 1;

PS：優(yōu)化方案

• 1、針對(duì)這條語(yǔ)句的查詢特性，我們減少數(shù)據(jù)的查詢條數(shù)，比如給 t1和t2 添加上篩選時(shí)間區(qū)間，減少遍歷數(shù)組總數(shù)。
• 2、使用覆蓋索引，我自己在測(cè)試的時(shí)候發(fā)現(xiàn)如果使用組合索引包含兩個(gè)字段 (customer_id,create_time) 性能會(huì)提升很多，20w數(shù)據(jù)查詢出結(jié)果只用了40s，如果只使用customer_id字段索引會(huì)進(jìn)行回表，使用覆蓋索引沒(méi)有額外的回表操作所以會(huì)快很多。

總結(jié)

結(jié)合我的業(yè)務(wù)經(jīng)過(guò)測(cè)試，目前看來(lái)方案三是最合適的，sql簡(jiǎn)單性能適中，方案一比方案二性能更差而且實(shí)現(xiàn)麻煩，最終選擇那個(gè)方案主要看業(yè)務(wù)而定。

MAX()函數(shù)和MIN()這一類(lèi)函數(shù)和GROUP BY配合使用存在問(wèn)題

MAX()函數(shù)和MIN()這一類(lèi)函數(shù)和GROUP BY配合使用，GROUP BY拿到的數(shù)據(jù)永遠(yuǎn)都是這個(gè)分組排序最上面的一條，而MAX()函數(shù)和MIN()這一類(lèi)函數(shù)會(huì)將這個(gè)分組中最大或最小的值取出來(lái)，這樣會(huì)導(dǎo)致查詢出來(lái)的數(shù)據(jù)對(duì)應(yīng)不上。

正確查詢：

錯(cuò)誤查詢：這里的確拿到每個(gè)分組最新創(chuàng)建時(shí)間了但是拿的數(shù)據(jù)id還是排序的第一條

到此這篇關(guān)于Mysql分組查詢每組最新的一條數(shù)據(jù)的五種實(shí)現(xiàn)方法的文章就介紹到這了,更多相關(guān)Mysql分組查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！