MySQL高效查询：聚合、分组与联合查询

一、聚合查询深度解析

1.1 核心聚合函数家族

MySQL提供五大基础聚合函数构成数据处理基石：

SELECT 
    COUNT(*) AS total_records,         -- 行数统计
    SUM(salary) AS total_salary,       -- 数值求和
    AVG(age) AS average_age,           -- 平均值计算
    MAX(score) AS highest_score,       -- 最大值获取
    MIN(join_date) AS earliest_date    -- 最小值提取
FROM employees;

特殊场景处理技巧：

-- 统计不重复值
SELECT COUNT(DISTINCT department) AS unique_departments 
FROM employees;

-- 安全除法处理（避免除零错误）
SELECT 
    SUM(sales) / NULLIF(COUNT(*), 0) AS safe_avg 
FROM sales_records;

1.2 聚合函数运行机制

查询优化器处理聚合查询的典型执行流程：

1. 全表扫描或索引扫描获取数据
2. 在内存中构建临时聚合结果集
3. 应用过滤条件进行数据筛选
4. 返回最终计算结果

性能优化方案对比：

-- 低效写法（全表扫描）
EXPLAIN SELECT AVG(price) FROM products;

-- 优化方案（索引覆盖）
ALTER TABLE products ADD INDEX idx_price (price);
EXPLAIN SELECT AVG(price) FROM products USE INDEX (idx_price);

1.3 多维度聚合实践

组合聚合的典型应用场景：

SELECT 
    COUNT(*) AS total_orders,
    SUM(amount) AS total_sales,
    AVG(amount) AS avg_order_value,
    MAX(amount) AS max_single_order
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

二、分组查询进阶技巧

2.1 GROUP BY原理剖析

分组查询执行过程分解：

1. 数据读取（全表/索引）
2. 临时分组表构建
3. 聚合计算执行
4. 结果集过滤
5. 最终结果返回

多字段分组示例：

SELECT 
    department_id,
    job_title,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id, job_title
ORDER BY department_id, job_title;

2.2 HAVING与WHERE对比

两种过滤方式的差异对比实验：

-- 使用WHERE过滤原始数据
SELECT department, AVG(salary)
FROM employees
WHERE salary > 5000
GROUP BY department;

-- 使用HAVING过滤聚合结果
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 8000;

2.3 高级分组功能

WITH ROLLUP实现多维分析：

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

分组排序优化方案：

SELECT 
    department,
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

三、联合查询深度应用

3.1 UNION核心机制

UNION执行流程解析：

1. 执行各个子查询
2. 合并结果集
3. 去重处理（UNION）
4. 最终排序输出

性能对比测试：

-- UNION去重版本
SELECT * FROM table1
UNION
SELECT * FROM table2;

-- UNION ALL快速版本
SELECT * FROM table1
UNION ALL
SELECT * FROM table2;

3.2 复杂联合查询实践

多表联合示例：

(
    SELECT 
        'Q1' AS quarter,
        product_id,
        SUM(quantity) AS total_sales
    FROM sales_q1
    GROUP BY product_id
)
UNION ALL
(
    SELECT 
        'Q2' AS quarter,
        product_id,
        SUM(quantity) AS total_sales
    FROM sales_q2
    GROUP BY product_id
)
ORDER BY quarter, total_sales DESC;

3.3 联合查询优化策略

索引优化案例：

-- 原始查询
SELECT id FROM users_2022
UNION
SELECT id FROM users_2023;

-- 优化方案
ALTER TABLE users_2022 ADD INDEX idx_id (id);
ALTER TABLE users_2023 ADD INDEX idx_id (id);
EXPLAIN SELECT id FROM users_2022
UNION
SELECT id FROM users_2023;

四、综合应用实战

4.1 电商数据分析案例

多表关联分析查询：

SELECT 
    c.category_name,
    COUNT(o.order_id) AS order_count,
    SUM(oi.quantity * oi.unit_price) AS total_sales,
    AVG(oi.quantity) AS avg_quantity
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY c.category_id
HAVING total_sales > 100000
ORDER BY total_sales DESC;

4.2 查询性能优化方案

执行计划分析示例：

EXPLAIN 
SELECT 
    department,
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary
FROM employees
WHERE hire_date > '2010-01-01'
GROUP BY department
HAVING avg_salary > 5000;

索引优化策略：

-- 创建复合索引
ALTER TABLE employees ADD INDEX idx_dept_hire_salary (department, hire_date, salary);

-- 优化后的查询执行计划分析
EXPLAIN 
SELECT 
    department,
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary
FROM employees
WHERE hire_date > '2010-01-01'
GROUP BY department
HAVING avg_salary > 5000;

五、最佳实践与常见陷阱

5.1 常见错误分析

错误案例1：分组字段遗漏

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

-- 正确写法
SELECT department, employee_name, AVG(salary)
FROM employees
GROUP BY department, employee_name;

错误案例2：隐式排序依赖

-- 不可靠的排序方式
SELECT department, COUNT(*) 
FROM employees
GROUP BY department;

-- 显式指定排序
SELECT department, COUNT(*) 
FROM employees
GROUP BY department
ORDER BY department;

5.2 进阶技巧总结

1. 聚合函数嵌套技巧：

SELECT 
    MAX(avg_salary) AS max_avg_salary
FROM (
    SELECT 
        department,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_salaries;

2. 条件聚合实现：

SELECT 
    department,
    COUNT(*) AS total_employees,
    SUM(CASE WHEN salary > 10000 THEN 1 ELSE 0 END) AS high_earners
FROM employees
GROUP BY department;

3. 分组连接字符串：

SELECT 
    department,
    GROUP_CONCAT(DISTINCT job_title ORDER BY job_title SEPARATOR ' | ') AS positions
FROM employees
GROUP BY department;