MySQL group by 底层原理详解。group by 执行慢原因深度分析。（图解+秒懂+史上最全）-CSDN博客

本文的原文地址

原始的内容，请参考本文的原文地址

本文的原文地址

尼恩说在前面

在40岁老架构师尼恩的读者交流群(50+)中，最近有小伙伴拿到了一线互联网企业如得物、阿里、滴滴、极兔、有赞、希音、百度、网易、美团、蚂蚁、得物的面试资格，遇到很多很重要的相关面试题：

MySQL group by 执行很慢、特别慢，原因是什么？

MySQL 使用 group by 语句时发现执行很慢，可能是什么原因？

最近有小伙伴在面腾讯，问到了相关的面试题，可以说是逢面必问。

小伙伴没有系统的去梳理和总结，所以支支吾吾的说了几句，面试官不满意，面试挂了。

所以，尼恩给大家做一下系统化、体系化的梳理，使得大家内力猛增，可以充分展示一下大家雄厚的 “技术肌肉”，让面试官爱到 “不能自已、口水直流”，然后实现”offer直提”。

当然，这道面试题，以及参考答案，也会收入咱们的《尼恩Java面试宝典PDF》V175版本，供后面的小伙伴参考，提升大家的 3高架构、设计、开发水平。

《尼恩架构笔记》《尼恩高并发三部曲》《尼恩Java面试宝典》的PDF，请到文末公号【技术自由圈】获取

本文作者：

第一作者老架构师肖恩（肖恩是尼恩团队高级架构师，负责写此文的第一稿，初稿）
第二作者老架构师尼恩（45岁老架构师，负责提升此文的技术高度，让大家有一种俯视技术、俯瞰技术、技术自由的感觉）

MySQL GROUP BY 语句底层原理与性能优化分析

GROUP BY 底层执行原理

分组过程 ：

MySQL 执行 GROUP BY 语句时，会先扫描表中的数据，按照指定的分组列将数据划分成不同的组。每个组内部的数据具有相同的分组列值。

排序与哈希 ：

为了实现分组，MySQL 有两种主要的策略，即排序（Sort）和哈希（Hash）。

排序（Sort）策略会将数据按照分组列进行排序，然后对排序后的数据进行分组。
哈希（Hash）策略则是通过构建哈希表，将数据按照分组列的哈希值进行分组。当数据量较大且内存足够时，哈希策略可能会更高效。

默认情况下，MySQL 使用排序策略来执行 GROUP BY 操作。

聚合计算 ：

在分组完成后，MySQL 在每个组上执行聚合函数（如 SUM、AVG、COUNT 等），计算出每个组的聚合结果。

图中展示了 MySQL 执行 GROUP BY 的主要步骤。
其中关键点是分组策略的选择（排序 vs 哈希）和最后的聚合计算。

GROUP BY 的两个分组策略

MySQL 执行GROUP BY的核心逻辑是将结果集按照指定字段分组，并对每组数据执行聚合函数（如SUM、COUNT）。

其底层实现主要有两种分组算法：

1、 哈希算法分组

当没有合适的索引时，MySQL 会使用哈希表实现分组。

步骤：扫描全量数据 → 在内存中构建哈希表（键为分组字段，值为聚合结果） → 若内存不足则溢出到磁盘 → 最终输出分组结果。

2、 排序分组

若分组字段存在索引，MySQL 会利用索引的有序性直接分组，避免显式排序。

步骤：通过索引扫描有序数据 → 按顺序直接分组 → 无需额外排序。

GROUP BY 的两个关键步骤

当执行 GROUP BY 时，MySQL 会经历以下关键步骤：

第一阶段：数据扫描阶段：

从表或索引中读取满足 WHERE 条件的所有行
将需要分组的字段和聚合字段加载到内存

第二阶段：分组核心处理：

有两种方式来处理分组：

1、 使用临时表（默认方式）
创建一个临时表，把 GROUP BY 的字段作为 key
遍历每条数据：

如果 key 已存在 → 更新对应的聚合值（比如 SUM、COUNT）
如果 key 不存在 → 插入一条新记录

2、使用排序（优化方式）

先按 GROUP BY 字段排序（如果能用索引就更快）

按顺序读取数据：

相同分组的数据会挨着出现
直接累加统计值，不需要查临时表

1. 哈希分组策略 GROUP BY 的执行流程

触发条件：无合适索引可用时

当数据库找不到合适的索引时，就会使用这种分组聚合的处理方式。

核心流程：

创建内存临时表（结构示例）：

分组键(hash)	聚合值1	聚合值2
key1	val1	val2

逐行扫描数据：

算出每一行的“分组键”的哈希值
根据哈希值，在临时表中找到对应的“桶”（也就是对应的位置）

聚合操作：

如果找到了对应的“桶”，就更新里面的聚合值
如果没找到，就新建一个“桶”，并初始化聚合值

内存管理：

内存占用 < tmp_table_size：全内存操作
内存占用 > tmp_table_size：转磁盘临时表

性能陷阱：

磁盘临时表 I/O 性能下降10-100倍
哈希冲突处理额外开销

2. 排序分组策略 GROUP BY 的执行流程 (优化方式)

触发条件：

GROUP BY 字段有索引
或开启 optimizer_switch='prefer_ordering_index=on'

核心流程：

数据排序（可选）：

要么用索引直接拿到排好序的数据
要么通过文件排序整理数据顺序

顺序处理：

按顺序一条条读取数据
发现分组字段变化时，就知道进入新分组了

聚合计算：

同一组的数据是连续的
直接一个个加起来就行，不用反复查找

优势：

无临时表开销
内存使用可预测
大数据量下更稳定

GROUP BY 的内存/磁盘使用原则：

当处理数据时，流程如下：

如果数据量小：

使用内存临时表
处理速度快，能快速完成

如果数据量大：

内存不够用，会提示内存不足
系统自动将临时表转为磁盘临时表
因为要读写硬盘，性能明显下降

GROUP BY 排序与分组阶段深度解析

排序阶段：数据整理的关键步骤

排序阶段的三种处理方式

(1)索引排序（最优）

触发条件：GROUP BY 字段有合适索引

工作方式：

直接按索引顺序读取数据
无需额外排序操作

性能：最佳，时间复杂度 O(N)

(2)内存排序（快速）

触发条件：数据量 < sort_buffer_size（默认 256KB）

工作方式：

在内存中进行快速排序
排序后输出有序数据流

性能：较好，时间复杂度 O(N log N)

(3)磁盘排序（高成本）

触发条件：数据量 > sort_buffer_size

工作方式：

将数据分割成多个块
每块在内存排序后写入临时文件
使用归并算法合并临时文件
最终输出有序数据

性能陷阱：

磁盘 I/O 操作（性能下降10-100倍）
额外 CPU 计算开销

分组阶段：聚合计算的核心过程

分组阶段的详细步骤

(1) 初始状态

读取有序数据流的第一行

初始化第一分组：

存储分组键
初始化聚合值（SUM=0, COUNT=0）

(2) 分组匹配

读取下一行数据

比较当前行与当前分组键：

相同：更新聚合值
不同：完成当前分组并输出

       SUM = SUM + amount
       COUNT = COUNT + 1

(3) 分组切换

遇到新分组键时：

存储当前分组结果
初始化新分组状态
基于新行数据初始化聚合值

(4) 结果输出

最后一组数据处理完毕
输出所有分组结果

排序与分组的协同工作

排序与分组协作案例：城市销售统计

数据准备：

id	city	amount
1	北京	100
2	上海	200
3	北京	150
4	广州	300
5	上海	250

执行流程：

(1) 排序阶段输出：

   (北京, 100)
   (北京, 150)
   (上海, 200)
   (上海, 250)
   (广州, 300)

(2) 分组阶段处理：

   [组1] 北京 
    处理行1：北京 100 → SUM=100, COUNT=1
    处理行2：北京 150 → SUM=250, COUNT=2
    分组变化：输出 (北京, 250, 2)
   
   [组2] 上海
    处理行3：上海 200 → SUM=200, COUNT=1
    处理行4：上海 250 → SUM=450, COUNT=2
    分组变化：输出 (上海, 450, 2)
   
   [组3] 广州
    处理行5：广州 300 → SUM=300, COUNT=1
    结束：输出 (广州, 300, 1)

哈希分组 vs 排序分组的关键区别

特性	哈希分组策略	排序分组策略
是否需要排序	❌ 完全不需排序	✅ 必须显式或隐式排序
执行阶段	单阶段完成	两阶段（排序+分组）
内存管理	依赖哈希表内存管理	依赖排序缓冲区管理
磁盘溢出处理	哈希表转磁盘	排序文件写磁盘
最佳适用场景	分组键重复率高	分组键有序/有索引
大数据量表现	内存溢出后性能骤降	相对稳定但排序成本高

哈希分组策略的核心特点

哈希分组策略的执行全流程

1. 初始化阶段（无排序）

创建空哈希表（内存中）
初始化聚合函数状态（SUM=0, COUNT=0等）

2. 数据处理阶段（核心）

伪代码演示哈希分组过程


hash_table = {}

for row in input_stream:  # 无需排序，乱序处理
    -- 计算分组键（如city字段）
    group_key = row['city']
    
     -- 计算哈希值（MySQL内部优化）
    hash_value = hash_function(group_key)
    
    -- 查找哈希桶
    if hash_value in hash_table:
         -- 更新聚合值
        hash_table[hash_value]['sum'] += row['amount']
        hash_table[hash_value]['count'] += 1
    else:
        -- 创建新分组
        hash_table[hash_value] = {
            'city': group_key,
            'sum': row['amount'],
            'count': 1
        }

3. 内存管理阶段

内存充足时：全内存操作（O(1)查找）

内存不足时：

将部分哈希桶写入磁盘临时文件
继续处理新数据
最终合并磁盘和内存结果

4. 结果输出阶段

遍历哈希表所有桶
输出分组键及聚合结果
注意：输出顺序不确定（与数据输入顺序无关）

GROUP BY 执行慢的可能原因

（1）缺少合适的索引（70%慢查询根源）

GROUP BY 分组字段没索引 → 全表扫描，效率低
WHERE查询条件字段没索引 → 扫描数据多，速度慢

如果用来分组的字段没有索引，MySQL 就得把整张表读一遍，并在内存里做分组，这样会很慢，占用资源也多。

例子：对 orders 表按 customer_id 分组，如果这个字段没索引，就得一行一行地看 100 万条数据，还要在内存里整理这些数据。

说明

无索引时：MySQL 需要一条条读取数据，再在内存里整理分组，效率低。
有索引时：可以快速找到需要的数据，分组更快更省资源。

（2）内存临时表溢出（20%慢查询原因）

分组数据太大，超过内存限制 → 性能下降

分组数量超过 tmp_table_size（默认 16MB）
转用磁盘临时表 → I/O 性能下降 10-100 倍

MySQL 默认用内存临时表来处理分组操作，默认大小是 16MB, 如果分组结果超过这个大小，就会转成磁盘临时表, 磁盘操作比内存慢很多（大约 10~100 倍），性能明显变差

（3）文件排序效率低

如果排序字段没有加索引，MySQL 就会用文件排序, 数据量大时，这种排序方式很吃 CPU 和磁盘 I/O

排序字段无索引 → 使用磁盘文件排序
大数据量排序消耗 CPU 和 I/O

当结果集超出tmp_table_size或max_heap_table_size时，临时表会从内存溢出到磁盘，导致性能骤降。

案例：分组结果集超过 512MB（默认临时表大小），触发磁盘 I/O。

（4）数据倾斜

某些分组的记录数远超其他组，导致哈希表分布不均，部分线程处理大量数据。

案例：customer_id=100的订单有 50 万条，其他客户仅几百条，处理该分组时 CPU 使用率飙升。

有些分组里的数据特别多，其他分组很少，导致哈希表分配不均匀，某些线程要处理太多数据。

（5）返回结果集过大

分组数量过多（如百万级分组）
结果集传输耗时剧增, 网络传输成为瓶颈

（6）聚合函数计算复杂

使用 COUNT(DISTINCT col) 等高开销函数
大数据集导致计算时间倍增

高成本函数：

COUNT(DISTINCT col)
AVG()/SUM() 处理浮点数
GROUP_CONCAT() 大字符串

若GROUP BY与WHERE、JOIN、复杂表达式（如函数计算）组合，会增加数据处理量。

案例：GROUP BY前需过滤大量数据，但过滤条件字段无索引。

避免频繁使用耗性能的函数
数据量越大，越要注意优化查询逻辑
给常用过滤条件字段加上索引，能大幅提升效率

（7）服务器配置不合理

sort_buffer_size过小导致多次磁盘排序，或innodb_buffer_pool_size不足影响数据缓存。
案例：sort_buffer_size仅 256KB，无法容纳排序数据，频繁触发磁盘交换。

`sort_buffer_size`

它是 MySQL 中用于控制排序操作所使用的内存缓冲区大小的参数。

作用：在执行 ORDER BY、GROUP BY 等需要排序的查询时，MySQL 会为每个连接分配一块内存来存储需要排序的数据子集，并在内存中完成排序操作，这个内存区域的大小就由 sort_buffer_size 决定。若数据量超出缓冲区大小，会将部分数据暂存到磁盘上的临时文件中，再进行磁盘排序和合并，这会增加磁盘 I/O，降低排序效率。

默认值 ：通常为 256KB 或 1MB，具体因 MySQL 版本和操作系统而异。

影响：如果设置过小，频繁的磁盘 I/O 会导致排序操作变慢；但设置过大可能占用过多内存资源，影响系统整体性能和其他操作。

`innodb_buffer_pool_size`

它是 MySQL InnoDB 存储引擎中一个关键的内存参数，用于控制 InnoDB 缓冲池的大小。

作用：InnoDB 缓冲池用于缓存表数据和索引数据，使数据读写尽可能在内存中完成，减少磁盘 I/O。当查询数据时，InnoDB 会先在缓冲池中查找，若找到（缓存命中），直接返回结果；若未找到，再从磁盘读取数据并加载到缓冲池中。

默认值 ：在 MySQL 8.0 及以后版本中，默认值通常为 128MB。

影响：设置过小会导致大量数据读写依赖磁盘 I/O，数据库性能下降；设置过大则可能占用过多系统内存，引发内存不足问题，一般建议设置为服务器物理内存的 50% - 80% 左右。

（8）子查询优化不当

有时候我们在写 SQL 的时候，会把 GROUP BY 和子查询一起用。如果没用对，就可能出现数据重复计算的问题。

比如：你在主查询里用了 GROUP BY，但子查询本身已经做了分组统计，这时候再分组，数据库可能就会多扫几遍数据，效率变低，结果也可能不对。

当 GROUP BY 和子查询搭配不当，数据库为了得出正确结果，可能会多次读取同一张表的数据。这种“重复扫描”会浪费资源，让查询变慢。

写 SQL 时要注意 GROUP BY 的位置。
子查询已经分组统计了，主查询就不需要再随便加 GROUP BY。
避免不必要的重复扫描，可以提升查询速度和准确性。

（9）统计信息不准确

表没有及时做统计分析（ANALYZE TABLE）

MySQL 需要通过统计信息来决定怎么执行查询。如果表数据变了但没运行 ANALYZE TABLE，优化器可能做出错误判断。

优化器选错了执行计划

因为统计信息不准，MySQL 可能选择了慢的执行方式，比如不该用索引却用了，或者该用的时候没用。

实战案例：电商订单分析

场景描述

我们有两个表：

orders（订单表）：记录了5000万条订单信息，包括用户ID、商品ID、订单金额和下单时间。
users（用户表）：记录了100万用户的注册信息，包括用户ID和注册时间。

表结构：

-- 订单表（5000万记录）
CREATE TABLE orders (
  order_id BIGINT PRIMARY KEY,
  user_id INT NOT NULL,
  product_id INT NOT NULL,
  amount DECIMAL(10,2),
  order_time DATETIME
);

-- 用户表（100万用户）
CREATE TABLE users (
  user_id INT PRIMARY KEY,
  reg_time DATETIME
);

需求：统计2023年新注册用户的每月订单总金额

-- 问题SQL
SELECT 
  DATE_FORMAT(u.reg_time, '%Y-%m') AS reg_month,
  SUM(o.amount) AS total_amount
FROM users u JOIN orders o
ON u.user_id = o.user_id
WHERE u.reg_time >= '2023-01-01'
GROUP BY reg_month;

整个查询过程，是一个下面的流程图：

(1) 筛选新用户：

只看2023年之后注册的用户。

(2) 关联订单：

找出这些用户的所有订单。

(3) 按月分组：

把订单按照用户注册的月份归类。

(4) 汇总金额：

统计每个月的订单总金额。

这样就能得到2023年新用户每月的消费总额了。

性能问题分析

(1) 执行计划诊断（EXPLAIN 结果）：

   +----+-------------+-------+------+---------------+------+---------+------+----------+---------------------------------+
   | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | Extra                           |
   +----+-------------+-------+------+---------------+------+---------+------+----------+---------------------------------+
   | 1  | SIMPLE      | u     | ALL  | PRIMARY       | NULL | NULL    | NULL | 1000000  | Using where; Using temporary    |
   | 1  | SIMPLE      | o     | ALL  | NULL          | NULL | NULL    | NULL | 50000000 | Using join buffer; Using where |
   +----+-------------+-------+------+---------------+------+---------+------+----------+---------------------------------+

说明：

u 表（用户表）是全表扫描，没有使用索引
o 表（订单表）也没有索引，也是全表扫
使用了临时表和连接缓冲区（Using join buffer），效率低

explain 常见type类型及性能排序：

1、 system：最快，表只有一行（系统表）。

2、 const：根据主键或唯一索引直接查询，返回一行。

3、 eq_ref：多表连接时，对于前表的每一行，后表只返回一行（使用主键或唯一索引）。

4、 ref：使用非唯一索引扫描，返回匹配某个单独值的所有行。

5、 fulltext：全文索引搜索。

6、 ref_or_null：类似ref，但是可以搜索包含NULL值的行。

7、 index_merge：表示使用了索引合并优化。

8、 unique_subquery：在子查询中使用主键或唯一索引。

9、 index_subquery：在子查询中使用非唯一索引。

10、 range：索引范围扫描（如BETWEEN, IN, >等）。

11、 index：全索引扫描（只遍历索引树）。

12、 ALL：全表扫描，性能最差。

(2) 性能瓶颈定位：

（1）全表扫描严重

两个表都没有合适的索引，导致数据全部读一遍，速度慢。

（2）临时表太大

涉及到的数据量大：
- 用户约20万
- 订单约1000万
分组字段只有12种值（按月份分组）
数据太多，内存装不下 → 转成磁盘处理，更慢

（3）连接顺序错误

先扫描了整个用户表，再关联订单，效率低。

（4）排序没用索引

GROUP BY 的字段没有索引，导致额外排序开销。

优化方案与实施

(1) 索引优化：

为了加快查询速度，在两个表上加上合适的索引：

   -- 添加必要索引
   ALTER TABLE users 
     ADD INDEX idx_reg_time (reg_time),
     ADD INDEX idx_user_reg (user_id, reg_time);
   
   ALTER TABLE orders 
     ADD INDEX idx_user_order (user_id, order_time, amount);

说明：

这些索引能帮助数据库更快地找到需要的数据，特别是按用户和时间筛选时。

(2) SQL重写：

原来的查询可能太慢，我们把它改得更高效一些：

   SELECT 
     reg_month,
     SUM(amount) AS total_amount
   FROM (
     -- 先过滤新用户，缩小数据集
     SELECT 
       user_id, 
       DATE_FORMAT(reg_time, '%Y-%m') AS reg_month
     FROM users
     WHERE reg_time >= '2023-01-01'
   ) u
   JOIN orders o ON u.user_id = o.user_id
   GROUP BY reg_month  -- 12个分组，内存完全足够

说明：

先从 users 表中选出注册时间在 2023 年之后的用户。
然后再去 orders 表里找这些用户的订单数据。
最后按月份统计总消费金额。

这样可以减少处理的数据量，提升效率。

(3) 执行计划验证：

运行下面命令，看看 SQL 是怎么执行的：

EXPLAIN 
SELECT ... （上面的SQL）

   +----+-------------+------------+-------+-----------------+-----------------+---------+------+--------+------------------------------+
   | id | select_type | table      | type  | possible_keys   | key             | key_len | ref  | rows   | Extra                        |
   +----+-------------+------------+-------+-----------------+-----------------+---------+------+--------+------------------------------+
   | 1  | PRIMARY     | <derived2> | ALL   | NULL            | NULL            | NULL    | NULL | 200000 | Using temporary              |
   | 1  | PRIMARY     | o          | ref   | idx_user_order  | idx_user_order  | 4       | u.id | 50     | NULL                         |
   | 2  | DERIVED     | users      | range | idx_reg_time    | idx_reg_time    | 5       | NULL | 200000 | Using where; Using index     |
   +----+-------------+------------+-------+-----------------+-----------------+---------+------+--------+------------------------------+

说明：

第一步是从 users 表中筛选出新用户（用了索引）。
第二步是把这些用户和他们的订单做关联。
最后按月份分组统计。

虽然中间用到了临时表，但整体效率已经比原来好很多。

优化效果对比

指标	优化前	优化后	提升效果
执行时间	38.7秒	0.8秒	快了48倍
扫描行数	5100万行	20万+1000行	减少了2550倍
临时表类型	磁盘临时表	内存临时表	更高效
磁盘读取	450MB	2MB	少了225倍

测试环境：MySQL 8.0, 4核CPU, 16GB内存, SSD磁盘

通过优化查询方式，大幅减少了数据扫描量和磁盘使用，让查询速度提升了几十到几百倍。

深度优化技巧

(1) 参数调优：

为了让查询跑得更快，先临时调高一些设置：

   -- 增加临时表内存大小
   SET tmp_table_size = 256M;
   SET max_heap_table_size = 256M;
   
   -- 启用分组优化模式
   SET SESSION optimizer_switch = 'prefer_ordering_index=on';

(2) 分阶段聚合：

先把每个用户的总消费算出来，存在一个临时表里，然后再按注册月份汇总。

-- 第一步：先按用户统计总消费
CREATE TEMPORARY TABLE tmp_summary
SELECT 
  user_id, 
  SUM(amount) as user_total
FROM orders
WHERE order_time >= '2023-01-01'
GROUP BY user_id;

-- 第二步：再和用户表关联，按注册月份统计总金额
SELECT 
  DATE_FORMAT(u.reg_time, '%Y-%m') AS reg_month,
  SUM(t.user_total) AS total_amount
FROM users u
JOIN tmp_summary t ON u.user_id = t.user_id
WHERE u.reg_time >= '2023-01-01'
GROUP BY reg_month;

(3) 聚合下推技术：

在子查询里就完成订单的分组统计，减少主查询的数据量。

-- 子查询中先按用户+月份统计消费额
SELECT 
  DATE_FORMAT(u.reg_time, '%Y-%m') AS reg_month,
  o.month_amount
FROM users u
JOIN (
  SELECT 
    user_id, 
    DATE_FORMAT(order_time, '%Y-%m') AS order_month,
    SUM(amount) AS month_amount
  FROM orders
  GROUP BY user_id, order_month
) o ON u.user_id = o.user_id
WHERE u.reg_time >= '2023-01-01';