MySQL GROUP BY：从原理到高性能实践

一、GROUP BY 核心原理剖析

（1）数据分组本质是将物理存储的二维表结构转化为多维分析结构。当执行GROUP BY department时，MySQL 会创建临时分组键值映射表：

CREATE TEMPORARY TABLE tmp_group_map (
    original_row_id INT PRIMARY KEY,
    group_key VARCHAR(50),
    INDEX (group_key)
);

（2）存储引擎处理流程：

1. 全表扫描或索引扫描获取数据
2. 将每行数据的分组键存入内存哈希表
3. 相同哈希值的记录进入同一分组桶
4. 对每个分组桶执行聚合计算

（3）内存优化策略：

• 当分组数量超过tmp_table_size（默认16MB）时转为磁盘临时表
• 8.0版本引入的哈希聚合算法可提升3-5倍性能

二、完整语法结构与执行顺序

SELECT 
    [DISTINCT] select_expr [, select_expr ...]
    [FROM table_references]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING having_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]

执行顺序解析：

1. FROM 子句：确定数据来源
2. WHERE 过滤：减少后续处理的数据量
3. GROUP BY 分组：创建内存中的分组结构
4. HAVING 过滤：对分组结果进行筛选
5. SELECT 投影：选择最终显示的列
6. ORDER BY 排序
7. LIMIT 限制

三、聚合函数深度解析

（1）COUNT 的三种形态对比：

SELECT 
    COUNT(*) AS total_rows,
    COUNT(1) AS const_count,
    COUNT(email) AS email_count,
    COUNT(DISTINCT department) AS unique_dept
FROM employees;

（2）SUM 的精度处理：

CREATE TABLE financial (
    id INT PRIMARY KEY,
    amount DECIMAL(20,6)
);

SELECT 
    SUM(amount) AS raw_sum,
    CAST(SUM(amount*100) / 100 AS DECIMAL(20,2)) AS precise_sum
FROM financial;

（3）统计函数组合示例：

SELECT 
    department,
    AVG(salary) AS avg_salary,
    STD(salary) AS salary_stddev,
    VARIANCE(salary) AS salary_var,
    BIT_OR(privileges) AS combined_priv
FROM employees
GROUP BY department;

四、高级分组技巧

（1）表达式分组：

SELECT 
    CASE 
        WHEN age < 20 THEN 'Teen'
        WHEN age BETWEEN 20 AND 35 THEN 'Young'
        ELSE 'Mature'
    END AS age_group,
    COUNT(*) AS total
FROM users
GROUP BY age_group;

（2）JSON字段分组（MySQL 5.7+）：

SELECT 
    JSON_EXTRACT(profile, '$.address.city') AS city,
    COUNT(*) AS user_count
FROM users
GROUP BY city;

（3）多级嵌套分组：

SELECT 
    YEAR(order_date) AS order_year,
    QUARTER(order_date) AS order_quarter,
    product_category,
    SUM(amount) AS total_sales
FROM orders
GROUP BY 
    order_year,
    order_quarter,
    product_category
WITH ROLLUP;

五、性能优化全方案

（1）索引设计黄金法则：

• 对于GROUP BY a,b,c的查询，最佳索引是(a,b,c)
• 覆盖索引可避免回表：

ALTER TABLE sales ADD INDEX idx_region_product (region, product_id, amount);

（2）执行计划分析示例：

EXPLAIN 
SELECT region, COUNT(*) 
FROM sales 
WHERE year = 2023 
GROUP BY region;

关键指标解读：

• Using index: 使用覆盖索引
• Using temporary: 需要临时表
• Using filesort: 需要额外排序

（3）内存参数调优：

# my.cnf 配置
tmp_table_size = 256M
max_heap_table_size = 256M
group_concat_max_len = 1048576

六、典型错误案例解析

（1）非聚合字段错误：

-- 错误示例
SELECT department, employee_name, AVG(salary)
FROM employees
GROUP BY department;

-- 正确修改
SELECT 
    department,
    GROUP_CONCAT(employee_name) AS members,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

（2）隐式排序陷阱：

-- 8.0版本前默认按分组字段排序
SELECT department, COUNT(*) 
FROM employees
GROUP BY department
ORDER BY NULL;  -- 显式取消排序

（3）NULL值处理：

SELECT 
    IFNULL(department, '未分配') AS dept,
    COUNT(*) AS cnt
FROM employees
GROUP BY department;

七、横向扩展方案

（1）分库分表后聚合：

-- 按月份分表场景
SELECT 
    product_id,
    SUM(total_sales) AS annual_sales
FROM (
    SELECT product_id, amount AS total_sales FROM sales_202301
    UNION ALL
    SELECT product_id, amount FROM sales_202302
    -- ... 其他月份表
) AS all_sales
GROUP BY product_id;

（2）结合窗口函数（MySQL 8.0+）：

SELECT 
    department,
    employee_name,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

八、监控与调试

（1）状态变量监控：

SHOW SESSION STATUS LIKE 'Handler%';

关键指标：

• Handler_read_first: 全索引扫描次数
• Handler_read_key: 索引查找次数
• Handler_tmp_write: 临时表写入量

（2）性能模式分析：

SELECT 
    EVENT_NAME,
    COUNT_STAR,
    SUM_TIMER_WAIT
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE '%tmp%';

（3）慢查询日志分析：

# my.cnf 配置
slow_query_log = 1
long_query_time = 2
log_queries_not_using_indexes = 1

九、真实场景案例

电商订单分析系统：

SELECT 
    DATE_FORMAT(order_time, '%Y-%m') AS month,
    product_category,
    COUNT(DISTINCT user_id) AS active_buyers,
    SUM(quantity) AS total_quantity,
    SUM(quantity * price) AS gmv,
    AVG(quantity) AS avg_purchase
FROM orders
WHERE order_status = 'completed'
GROUP BY 
    month,
    product_category
WITH ROLLUP
HAVING gmv > 100000
ORDER BY 
    month DESC,
    gmv DESC;

执行计划优化步骤：

1. 创建复合索引(order_status, order_time, product_category)
2. 添加覆盖索引(user_id, quantity, price)
3. 分区表按月份范围分区

十、版本特性演进

（1）MySQL 5.6 改进：

• 优化器可以更早执行WHERE条件过滤
• 改进临时表存储格式

（2）MySQL 8.0 重大更新：

• 哈希聚合算法（Hash Aggregation）
• 窗口函数支持
• 通用表表达式（CTE）
• 不可见索引（方便测试索引效果）

（3）未来路线图预览：

• 更智能的自动索引推荐
• 机器学习驱动的查询优化
• 分布式集群的并行聚合