揭秘MySQL高手常用的高级SQL技巧 (二)

前言

今天，我们将更进一步，深入探索MySQL中更强大的分析和优化工具。这些技巧将彻底改变你处理复杂报表、重构冗长SQL以及进行深度性能调优的方式。准备好迎接窗口函数、CTE等现代SQL特性的洗礼吧！

技巧 6: 窗口函数(Window Functions) - 分析型查询的瑞士军刀

背景与痛点:

一个经典的业务需求：“查询每个部门工资最高的3名员工”。

如果用传统SQL，你会怎么做？你可能会想到使用子查询或者自连接，写出非常复杂且性能低下的语句。GROUP BY 只能对每个分组返回一条聚合结果，无法满足“组内排序取Top N”的需求。这个问题在窗口函数出现之前，一直是SQL的一大难题。

奇技淫巧:

使用窗口函数，如 ROW_NUMBER(), RANK(), DENSE_RANK()。

原理:

窗口函数与GROUP BY不同，它在计算时不会将多行压缩为一行。相反，它会为查询结果集中的每一行计算一个值，这个计算基于一个与当前行相关的行集合，这个集合就被称为“窗口”。窗口由 OVER() 子句定义，其中可以包含：

• PARTITION BY: 定义窗口的分区，类似于 GROUP BY。窗口函数将在每个分区内独立计算。
• ORDER BY: 定义分区内行的排序规则。
• Frame Clause (ROWS/RANGE): 更精细地定义窗口内计算的范围（例如“当前行及之前两行”），常用于计算移动平均值。

举例:

假设我们有员工表 employees：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees VALUES
(1, '张三', '技术部', 8000), (2, '李四', '技术部', 9000),
(3, '王五', '技术部', 8500), (4, '赵六', '技术部', 9000),
(5, '孙七', '市场部', 6000), (6, '周八', '市场部', 6500),
(7, '吴九', '市场部', 5500), (8, '郑十', '市场部', 6500);

现在我们来解决“每个部门工资最高的3名员工”的问题。

WITH RankedEmployees AS (
    SELECT
        name,
        department,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rn,
        RANK()       OVER (PARTITION BY department ORDER BY salary DESC) as rk,
        DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as drk
    FROM employees
)
SELECT *
FROM RankedEmployees
WHERE rn <= 3;

ROW_NUMBER() 的结果:

ROW_NUMBER(), RANK(), DENSE_RANK() 的区别:
这三个函数都用于排名，但处理并列情况时行为不同。假设工资为 9000, 9000, 8500, 8000:

• ROW_NUMBER(): 1, 2, 3, 4 (不考虑并列，给出连续唯一的排名)
• RANK(): 1, 1, 3, 4 (并列排名相同，但会跳过后续排名)
• DENSE_RANK(): 1, 1, 2, 3 (并列排名相同，且不会跳过后续排名)

窗口函数是现代SQL的基石，尤其在数据分析和报表领域，它是解决复杂排名、占比、移动平均等问题的最佳工具。

技巧 7: 通用表表达式(CTE) - 让你的复杂查询“深呼吸”

背景与痛点:

随着业务逻辑变复杂，你可能会写出层层嵌套的子查询，形成所谓的“SQL套娃”或“死亡缩进”。这样的SQL极难阅读、维护和调试。

-- 示例：难以阅读的嵌套子查询
SELECT ...
FROM (
    SELECT ...
    FROM (
        SELECT ... FROM ... WHERE ...
    ) AS step1
    JOIN ... ON ...
) AS step2
WHERE ...;

奇技淫巧:

使用通用表表达式（Common Table Expressions, CTE），即 WITH 子句。

原理:

CTE允许你为查询中的子查询（即派生表）定义一个临时的、可读性强的名称。你可以把它看作是一个只在当前查询中有效的“临时视图”。它将复杂的查询分解为一系列简单、逻辑清晰的步骤。

举例:

假设我们要找出平均工资高于公司总平均工资的部门。

传统写法 (子查询):

SELECT
    department,
    avg_dept_salary
FROM (
    -- 步骤1: 计算每个部门的平均工资
    SELECT department, AVG(salary) as avg_dept_salary
    FROM employees
    GROUP BY department
) AS dept_salaries
WHERE avg_dept_salary > (
    -- 步骤2: 计算公司总平均工资
    SELECT AVG(salary) FROM employees
);

CTE 写法:

WITH DeptSalaries AS (
    -- 步骤1: 定义一个名为 DeptSalaries 的CTE，计算部门平均工资
    SELECT department, AVG(salary) as avg_dept_salary
    FROM employees
    GROUP BY department
),
TotalAvgSalary AS (
    -- 步骤2: 定义另一个名为 TotalAvgSalary 的CTE，计算总平均工资
    SELECT AVG(salary) as avg_total_salary
    FROM employees
)
-- 步骤3: 主查询，逻辑清晰
SELECT
    ds.department,
    ds.avg_dept_salary
FROM DeptSalaries ds
JOIN TotalAvgSalary tas ON ds.avg_dept_salary > tas.avg_total_salary;

通过CTE，我们将复杂的逻辑分解成了两个独立的、易于理解的步骤 DeptSalaries 和 TotalAvgSalary，最后在主查询中像使用普通表一样使用它们。代码的可读性和可维护性得到了质的飞跃。

进阶： CTE还支持递归查询，这对于处理树状或层级结构数据（如组织架构、商品分类）非常有用。

技巧 8: FIND_IN_SET() - 查询逗号分隔字符串的“权宜之计”

背景与痛点:

在一些不规范的表设计中，我们常常会遇到将多个ID或标签存储在一个字段里的情况，用逗号隔开，例如 tags 字段的值为 'mysql,database,sql'。

现在，如何查询所有包含 sql 标签的文章？
使用 LIKE '%sql%' 是不准确的，因为它会匹配到 nosql。使用 LIKE '%,sql,%' 或复杂的正则表达式又很繁琐。

奇技淫巧:

使用 FIND_IN_SET() 函数。

原理:

FIND_IN_SET(str, strlist) 函数用于在一个逗号分隔的字符串列表 strlist 中查找字符串 str。如果找到，它返回 str 在列表中的位置（从1开始）；如果没找到，返回 0。它进行的是全词匹配。

举例:

假设有 articles 表：

-- 正确查询包含 'sql' 标签的文章
SELECT * FROM articles WHERE FIND_IN_SET('sql', tags) > 0;
-- 这将只返回 id 为 1 的文章

-- 查询 'nosql'
SELECT * FROM articles WHERE FIND_IN_SET('nosql', tags) > 0;
-- 这将只返回 id 为 2 的文章

专家警告 (非常重要):

虽然 FIND_IN_SET() 很方便，但在数据库字段中存储逗号分隔的列表是一种反范式设计，它违反了数据库第一范式（1NF）。这种设计有几个严重缺点：

1. 无法有效使用索引: FIND_IN_SET 无法利用B-Tree索引，导致查询时几乎总是全表扫描，性能极差。
2. 数据维护困难: 更新、删除一个标签非常麻烦。
3. 数据一致性差: 容易出现 'sql, mysql' 和 'mysql,sql' 这样的不一致数据。

正确的设计应该是使用一个关联表（Junction Table），实现多对多关系。

erDiagram
    ARTICLES {
        int id PK
        varchar title
    }
    TAGS {
        int id PK
        varchar name
    }
    ARTICLE_TAGS {
        int article_id FK
        int tag_id FK
    }
    ARTICLES ||--|{ ARTICLE_TAGS : "has"
    TAGS ||--|{ ARTICLE_TAGS : "belongs to"

尽管如此，当你不得不处理历史遗留系统或无法修改表结构时，FIND_IN_SET() 是一个不可或缺的实用工具。

技巧 9: STRAIGHT_JOIN - 当你比优化器更聪明时

背景与痛点:

MySQL的查询优化器在大多数情况下都非常智能，它会分析统计信息，自动选择最优的表连接顺序。但有时，由于统计信息不准确或查询过于复杂，优化器可能会做出错误的选择，比如先用一个大表去驱动连接一个小表，导致性能灾难。

奇技淫巧:

使用 STRAIGHT_JOIN 强制指定连接顺序。

原理:

STRAIGHT_JOIN 的功能与 JOIN 完全一样，唯一的区别是它告诉优化器：“请严格按照我写的顺序，从左到右连接这些表，不要自作聪明！”。

举例:

假设我们有两张表：

• orders (订单表，1000万行)
• vip_users (VIP用户表，100行)

我们要查询所有VIP用户的订单。

可能出错的查询:

-- 优化器可能错误地选择 orders 作为驱动表
EXPLAIN SELECT o.* FROM orders o JOIN vip_users v ON o.user_id = v.user_id;

如果优化器选择 orders 作为驱动表，它将对1000万行订单进行全表扫描，然后逐一去 vip_users 表中匹配，效率极低。

使用 STRAIGHT_JOIN 进行干预:

-- 强制 vip_users 作为驱动表
EXPLAIN SELECT o.* FROM vip_users v STRAIGHT_JOIN orders o ON v.user_id = o.user_id;

这个查询强制MySQL先读取 vip_users 表（只有100行），然后拿着这100个 user_id 去 orders 表中进行索引查找。这显然是最高效的方式。

使用场景与警告:

STRAIGHT_JOIN 是一把双刃剑。请只在以下情况使用：

1. 你通过 EXPLAIN 明确发现优化器选择了错误的连接顺序。
2. 你对数据的分布和业务逻辑有非常深刻的理解。
3. 你已经尝试过 ANALYZE TABLE 更新统计信息但仍然无效。

滥用 STRAIGHT_JOIN 可能会让性能变得更糟，因为它禁用了优化器进行动态优化的能力。

技巧 10: 表达式索引(MySQL 8.0+) - 让函数查询也能用上索引

背景与痛点:

在 WHERE 子句中对列使用函数是一个常见的索引失效场景。例如，查询本月注册的所有用户：

SELECT * FROM users WHERE DATE_FORMAT(created_at, '%Y-%m') = '2025-10';

即使 created_at 字段上有索引，这条查询也无法使用它，因为MySQL无法直接在索引树中查找一个函数计算后的结果，只能对每一行都计算 DATE_FORMAT 的值，最终导致全表扫描。

奇技淫巧:

在MySQL 8.0及更高版本中，可以直接在表达式上创建索引（也称为函数索引）。

原理:

数据库会预先计算表达式 (DATE_FORMAT(created_at, '%Y-%m')) 的值，并将这些计算结果存储在索引中。当查询条件与索引表达式完全匹配时，MySQL就可以直接利用这个索引进行快速定位。

举例:

1. 创建表达式索引:

   -- 注意需要用双括号包裹表达式
   CREATE INDEX idx_users_created_month ON users ((DATE_FORMAT(created_at, '%Y-%m')));
   
   

2. 再次执行查询:

   EXPLAIN SELECT * FROM users WHERE DATE_FORMAT(created_at, '%Y-%m') = '2025-10';
   
   

在创建索引后，EXPLAIN 的结果会显示 type 变为 ref，并且 key 列会显示我们刚刚创建的 idx_users_created_month 索引。查询速度将从秒级甚至分钟级提升到毫秒级。

替代方案 (MySQL 8.0之前):

在旧版本MySQL中，可以通过添加一个“生成列”（Generated Column）并对其建立索引来模拟此功能。

ALTER TABLE users
ADD COLUMN created_month VARCHAR(7) AS (DATE_FORMAT(created_at, '%Y-%m')),
ADD INDEX idx_users_created_month (created_month);

-- 查询时直接使用新列
SELECT * FROM users WHERE created_month = '2025-10';

这两种方式都能解决问题，但表达式索引显然更直接、更优雅。