MySQL分组查询每组最新的一条数据：三种实现方法，性能比较与原理分析

一、前言

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

二、注意事项

1. 查看当前的SQL模式：

```sql

select @@sql_mode;

```

2. 设置全局和会话级别的SQL模式：

```sql

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查询语句：

```sql

SELECT customer_id, wallet_detail

FROM (

SELECT customer_id, wallet_detail, MAX(time) AS max_time

FROM wallet_details

GROUP BY customer_id

) AS temp

JOIN wallet_details ON temp.customer_id = wallet_details.customer_id AND temp.max_time = wallet_details.time;

```

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

```

四、错误查询分析

原始查询语句如下：

```sql

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

```

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

4.1、错误原因

使用`EXPLAIN`查看优化后的SQL语句，发现问题在于MySQL语句优化器没有将子查询排序生效。

五、实现方法

5.1、实现方法一(使用LIMIT查询)

鉴于以上的原因，我们可以添加上`LIMIT`条件来实现功能。子查询使用分页查询后，MySQL语句优化器就不会再去将两条语句合并了，逻辑不同，所以这里子查询排序会生效。

示例代码：

```sql

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

```

执行计划：

```sql

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语句优化器会认为子查询中进行的其它处理无法合并，查看执行计划和优化后的语句还是和原语句一致，会先执行子查询然后再执行分组查询。

重构后的查询语句如下：

```sql

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;

```

执行计划和警告信息：

```sql

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

SHOW WARNINGS;

```

性能比对和优化部分，准备了100万条测试数据。

## 创建表

### 删除已存在的表

```sql

DROP TABLE IF EXISTS `customer_wallet_detail_test`;

```

### 创建新表

```sql

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 = '用户钱包明细';

```

## 创建一个插入数据的存储过程

### 删除已存在的存储过程

```sql

DROP PROCEDURE IF EXISTS insert_procedure;

```

### 创建新的存储过程

```sql

DELIMITER //

CREATE PROCEDURE insert_procedure()

BEGIN

DECLARE i INT DEFAULT 1; --定义循环值

DECLARE t_error INTEGER DEFAULT 0; --定义一个错误的变量，类型是整形，默认是0

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1; --捕获到sql的错误，就设置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; --递增循环变量i

END WHILE; --结束循环插入语句块

IF t_error=1 THEN --如果捕获到错误，则回滚事务

ROLLBACK;

ELSE --否则提交事务

COMMIT;

END IF; --结束if语句段

END //

DELIMITER ; // 将分隔符改回分号，因为这是存储过程的结束标志。

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

查询语句：

```sql

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

```

优化前(耗时1.126s)

6.2.2、添加索引优化

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

```sql

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

```

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

查询语句：

```sql

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

```

优化前(耗时1.221s)

6.3.2、添加索引优化

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

```sql

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

```

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

查询语句：

```sql

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;

```

优化前(耗时0.373s)

.3.2、添加索引优化(耗时0.035s)

在进行子查询优化的过程中，我们尝试了三种方法，分别是方法一、方法二和方法三。经过测试，方法三可以创建一个customer_id字段的索引，使得在子查询中有一个分组操作可以使用到customer_id字段的索引，从而提高了查询性能，提升了10倍。具体的代码实现如下：

```sql

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

```

综合考虑业务需求和性能表现，我们认为方法三是最合适的方案。当然，在实际应用中，最终选择哪个方案主要取决于具体的业务场景。需要注意的是，这里的查询都是没有where条件，如果在添加上where条件并附加上辅助查询的索引后，查询耗时会有很大变化。因此，在实际应用中，结合业务需求选择一种合适的方法是非常重要的。