MySQL分组查询每组最新的一条数据（提供三种实现方法，附带三种方法查询性能比较和分析查询原理）

kerwin_code

已于 2023-10-17 16:47:56 修改

阅读量2w

点赞数 11

分类专栏：数据库文章标签： mysql 数据库

于 2023-10-17 16:44:44 首次发布

本文链接：https://blog.csdn.net/weixin_44606481/article/details/133870601

版权

数据库专栏收录该内容

20 篇文章 2 订阅

订阅专栏

一、前言

在写报表功能时遇到一个需要根据用户id分组查询最新一条钱包明细数据的需求，在写sql测试时遇到一个有趣的问题，开始使用子查询根据时间倒序+group by customer_id发现查询出来的数据一直都是最旧的一条，而不是我需要的最新一条数据我明明已经倒序排了，后来总结出了两种比较完善的解决方案如下。

二、注意事项

数据库版本 Mysql5.7+
执行 GROUP BY 语句的时候出现 sql_mode=only_full_group_by 解决方法（这里是Mysql8的解决方案，Mysql5.7也差不多，具体实现可以查看解决MySQL-this is incompatible with sql_mode=only_full_group_by 问题）
- 1、执行 select @@sql_mode; 查看sql模式
```
select @@sql_mode;
```
- 2、将sql_mode中的only_full_group_by模式剔除重新设置sql_mode值，如果是使用JDBC连接需要重启项目才能生效。
```
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';
```

三、准备SQL

这里模拟一个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 '发生金额 带-号的代表扣款',
  `balance_amount` varchar(15) NULL DEFAULT '0' COMMENT '可用余额',
  `create_time` bigint(20) NULL DEFAULT NULL COMMENT '发生时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB COMMENT = '用户钱包明细';

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);

在这里插入图片描述

四、错误查询分析

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

在这里插入图片描述

这里可以发现并没有根据时间倒序，查询出最新的一条数据，下面来分析一下原因。

4.1、错误原因

原因：在mysql5.7以及之后的版本，如果GROUP BY的子查询中包含ORDER BY，但是 GROUP BY 不与 LIMIT 或 DISTINCT 等特殊查询配合使用，ORDER BY会被忽略掉，所以子查询在 GROUP BY 时排序不会生效，可能是因为子查询大多数是作为一个结果给主查询使用，所以子查询不需要排序，在MySQL内置语句优化器中会将将这条查询语句优化，可以查看执行计划。
```
EXPLAIN
SELECT
	* 
FROM
	( SELECT * FROM customer_wallet_detail ORDER BY create_time DESC) t1 
GROUP BY
	t1.customer_id;
SHOW WARNINGS; # 查看优化后的sql语句，必须和EXPLAIN一起执行
```
在结果2中可以查看被优化后的语句，我精简了一下优化后的SQL语句会变成这样select * from customer_wallet_detail group by customer_id;，我们可以看到两条语句被合并成了一条，排序没了。
解决思路：如果想要在分组前排序只要打破MySQL语句优化就行，可以通过LIMIT、DISTINCT、MAX() 等操作实现，下面会给出三种实现方法。

五、实现方法

5.1、实现方法一（使用 LIMIT 查询）

鉴于以上的原因我们可以添加上 LIMIT 条件来实现功能，子查询使用分页查询后，MySQL语句优化器就不会再去将两条语句合并了，逻辑不同，所以这里子查询排序会生效。
PS：这个LIMIT的数量可以先自行 COUNT 出你要遍历的数据条数（这个数据条数是所有满足查询条件的数据合，我这里共9条数据）

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

在这里插入图片描述

执行计划

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

在这里插入图片描述

5.2、实现方法二（使用 DISTINCT 查询）

使用 DISTINCT 查询进行去重的主要原理是通过先对要进行去重的数据进行分组操作，然后从分组后的每组数据中去一条返回给客户端，MySQL语句优化器会认为子查询中进行的其它处理无法合并，查看执行计划和优化后的语句还是和原语句一致，会先执行子查询然后再执行分组查询。

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

在这里插入图片描述

执行计划

EXPLAIN
SELECT
	* 
FROM
	( SELECT DISTINCT * FROM `customer_wallet_detail` ORDER BY create_time DESC ) AS t1 
GROUP BY
	t1.customer_id;
SHOW WARNINGS;

在这里插入图片描述

5.3、实现方法三（使用 MAX(id) 查询，只适用于自增ID和创建时间排序一致，查询性能最优）

使用MAX(id)+分组查询可以查询出每组中最大的id，然后通过每组最大数据id集合关联查出数据即可，因为我这里的业务数据是有序插入的，使用主键自增id和create_time结果是一样的而且使用id查询效率更高，如果没有唯一且有序的id可以替代create_time那么就用上面两个方法。
PS：这里使用内连接查询而不使用 IN 查询是因为我测试时发现连接查询会比 IN 查询性能要高50%以上，这个和底层查询机制有关有兴趣可以查看 IN 查询和连接查询的执行计划。

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

在这里插入图片描述

六、性能比对和优化

6.1、准备100w测试数据

# 创建表
DROP TABLE IF EXISTS `customer_wallet_detail_test`;
CREATE TABLE `customer_wallet_detail_test`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `customer_id` bigint(20) NULL DEFAULT NULL COMMENT '用户ID',
  `happen_amount` varchar(15)  NULL DEFAULT '0' COMMENT '发生金额 带-号的代表扣款',
  `balance_amount` varchar(15) NULL DEFAULT '0' COMMENT '可用余额',
  `create_time` bigint(20) NULL DEFAULT NULL COMMENT '发生时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB COMMENT = '用户钱包明细';

## 创建一个插入数据的存储过程
DROP PROCEDURE IF EXISTS insert_procedure;
delimiter;;
CREATE PROCEDURE insert_procedure () 
BEGIN
  # 定义循环值
  DECLARE i INT DEFAULT 1;
  #定义一个错误的变量,类型是整形，默认是0
  DECLARE t_error INTEGER DEFAULT 0;
  #捕获到sql的错误，就设置t_error为1
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
  # 开启事务
  START TRANSACTION;
  # 开始循环插入
  WHILE ( i <= 1000000 ) DO
		INSERT INTO `customer_wallet_detail_test`(`customer_id`, `happen_amount`, `balance_amount`, `create_time`) VALUES (CEIL(RAND() * 1000), CEIL(RAND() * 1000), CEIL(RAND() * 1000), UNIX_TIMESTAMP() * 1000);
    SET i = i + 1;
  END WHILE;

  #如果捕获到错误
  IF t_error=1 THEN
    #回滚
    ROLLBACK;
  ELSE
    #提交
    COMMIT;
  END IF;
END;;
delimiter;

# 调用存储过程插入数据
CALL insert_procedure ();

6.2、方法一查询耗时测试和优化

SELECT
	* 
FROM
	( SELECT * FROM customer_wallet_detail_test ORDER BY create_time DESC LIMIT 1000000 ) t1 
GROUP BY
	t1.customer_id;

6.2.1、优化前（耗时1.126s）

在这里插入图片描述

6.2.2、添加索引优化

这里可以添加一个create_time字段索引优化查询，但是如果查询结果集非常大索引是会失效的，比如我们这里会查询出全表，如果走索引还要回表开销会更大，所以不会走索引，如果加上时间区间则可能会走索引，也要看这个区间数据是否很大，如果区间数据量太大比全表扫描性能开销更大MySQL也是不会走索引的。

ALTER TABLE `customer_wallet_detail_test` ADD INDEX `idx_createTime`(`create_time`);

6.3、方法二查询耗时测试和优化

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

6.3.1、优化前（耗时1.221s）

在这里插入图片描述

6.3.2、添加索引优化

方法二和方法一类似，都会扫描全表，而且方法二会有一个根据全字段去重操作，主要还是针对查询条件创建索引，这里也可以添加一个create_time字段索引优化查询，查询的时候给定一个时间区间。

ALTER TABLE `customer_wallet_detail_test` ADD INDEX `idx_createTime`(`create_time`);

6.4、方法三查询耗时测试和优化

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

6.4.1、优化前（耗时0.373s）

在这里插入图片描述

6.3.2、添加索引优化（耗时0.035s）

方法三可以创建一个customer_id字段的索引，在子查询中有一个分组操作是可以使用到customer_id字段的索引的，添加索引后查询性能提升了10倍。

ALTER TABLE `customer_wallet_detail` ADD INDEX `idx_customerId`(`customer_id`);

七、总结

结合我的业务经过测试，目前看来方法三是最合适的，性能较高，方案一和方案二性能较差，最终选择那个方案主要看业务而定，而且这里查询都是没有where条件，在添加上where条件并且附加上辅助查询的索引后，查询耗时会有很大变化，结合业务选择一种方法即可。

kerwin_code

关注

11
点赞
踩
40

收藏

觉得还不错? 一键收藏
2
评论
MySQL分组查询每组最新的一条数据（提供三种实现方法，附带三种方法查询性能比较和分析查询原理）

MySQL分组查询每组最新的一条数据（提供三种实现方法，附带三种方法查询性能比较和分析查询原理），在写报表功能时遇到一个需要根据用户id分组查询最新一条钱包明细数据的需求，在写sql测试时遇到一个有趣的问题，开始使用子查询根据时间倒序+group by customer_id发现查询出来的数据一直都是最旧的一条，而不是我需要的最新一条数据我明明已经倒序排了，后来总结出了两种比较完善的解决方案如下。
复制链接

扫一扫

专栏目录