本文 的 原文 地址

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

本文 的 原文 地址

本文作者：

• 第一作者 老架构师 肖恩（肖恩 是尼恩团队 高级架构师，负责写此文的第一稿，初稿 ）
• 第二作者 老架构师 尼恩 （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 的执行流程

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

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

核心流程：

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

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

1. 逐行扫描数据：

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

1. 聚合操作：

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

内存管理：

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

性能陷阱：

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

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

触发条件：

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

核心流程：

1. 数据排序（可选）：

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

1. 顺序处理：

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

1. 聚合计算：

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

优势：

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

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磁盘

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

深度优化技巧

篇幅太长，请参考原文

本文 的 原文 地址

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

本文 的 原文 地址