MySQL DQL深度解析：从SELECT基础到JOIN优化与性能调优实战

1. 从“查”开始：为什么DQL是数据库的命脉

干了这么多年后端开发，我越来越觉得，一个程序员对数据库的理解深度，很大程度上就体现在他对查询语言的掌控上。我们每天写的业务代码，无论是Java、Go还是Python，最终大部分都要落到数据库的查询操作上。你可以把数据库想象成一个巨大的、结构化的仓库，而DQL（Data Query Language）就是你与这个仓库管理员沟通的唯一语言。你说得越精准、越高效，管理员（数据库引擎）就能越快、越准地把你要的“货”搬出来。

很多人刚开始学MySQL，可能更关注怎么建表（DDL）、怎么插数据（DML），觉得查询无非就是SELECT * FROM table。但真正在线上环境跑过业务、处理过海量数据、优化过慢查询的人都知道，DQL的学问深了去了。它不仅仅是“把数据拿出来”，更是“在什么时间、以什么方式、拿出什么样的数据”。一次糟糕的查询，轻则让页面加载慢几秒，用户体验变差；重则直接拖垮数据库，引发线上事故。看看那些热搜词，“mysql锁表”、“mysql索引”、“mysql 查询连接数”，哪一个不是和DQL的使用息息相关？处理不好，这些都是定时炸弹。

所以，这篇内容，我想抛开那些安装配置（mysql安装教程、ubuntu安装mysql）的基础问题，也不深入讨论集群架构（mysql mgr集群配置、mysql 主从复制）。我们就扎扎实实地聊透DQL本身。无论你是正在被mysql面试题困扰的求职者，还是想优化手中业务性能的开发者，抑或是需要从数据库里提取报表的数据分析师，掌握DQL的精髓，都能让你事半功倍。接下来，我会从一个核心的SELECT语句出发，带你层层拆解，看看这条简单的命令背后，到底藏着多少我们需要关注的细节、技巧和“坑”。

2. SELECT语句解剖：远不止SELECT *

一提到查询，所有人脑子里蹦出来的第一个词肯定是SELECT。但SELECT * FROM employees;这条“万能”语句，在真实生产环境里，几乎可以算作是一种“反模式”。为什么？我们来把它拆开揉碎了看。

2.1 SELECT子句：你要什么，就明确地拿什么

SELECT后面跟着的，是你想从表中获取的列。使用*通配符，意味着“所有列”。这听起来很方便，但问题很大。

首先，是性能问题。表中的列可能很多，包含大文本（TEXT）、二进制（BLOB）等重型字段。当你SELECT *时，数据库需要从磁盘读取所有这些列的数据，通过网络传输到应用服务器，应用层再将其封装成对象。这中间涉及的I/O和网络带宽消耗，远大于你实际需要的几列。特别是在关联查询（JOIN）中，多个表的*相乘，会瞬间产生巨大的数据量。

其次，是稳定性与维护性问题。表结构是会变化的。今天你SELECT *，程序里可能按位置索引第3列是username。明天DBA为了优化，在表中间加了一列middle_name，你的代码逻辑可能就全乱了，因为现在第3列变成了middle_name。显式地指定列名，相当于给你的代码和数据库之间建立了一份明确的契约。

所以，一个良好的习惯是：始终指定你需要的列名。

-- 不推荐 SELECT * FROM orders; -- 推荐 SELECT order_id, customer_id, order_amount, order_date FROM orders;

这不仅仅是规范，在mysql索引优化中，这可能是关键一步。如果order_date列上有索引，但你的查询用*，数据库可能仍然需要“回表”去主键索引里取其他列的数据。而只查询索引包含的列（即覆盖索引），查询效率会高得多。

2.2 FROM子句与表别名：你的数据从哪里来

FROM子句指定查询的数据源。最简单的就是单表，但实际业务中多表关联才是常态。这里就引出了表别名这个非常重要的技巧。

给表起一个简短、有意义的别名，能让查询语句更清晰，尤其是在多表JOIN和子查询中。

-- 冗长且易错 SELECT orders.order_id, customers.customer_name, order_details.product_name FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id INNER JOIN order_details ON orders.order_id = order_details.order_id; -- 使用别名，清晰明了 SELECT o.order_id, c.customer_name, od.product_name FROM orders o INNER JOIN customers c ON o.customer_id = c.customer_id INNER JOIN order_details od ON o.order_id = od.order_id;

别名不仅减少了打字量，更重要的是，当表名很长或者需要自关联时，它是必不可少的。例如，查询员工及其经理的信息（员工和经理信息都在employees表里）：

SELECT e.emp_name AS employee, m.emp_name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.emp_id;

这里的e和m就是同一张表employees的两个不同别名，代表了不同的角色。

2.3 WHERE子句：过滤的艺术与陷阱

WHERE子句是DQL的“过滤器”，它决定了哪些行能进入你的结果集。这里面的门道，直接关系到查询效率和结果正确性。

核心原则：尽量使用索引列进行过滤。这是解决mysql锁表和慢查询的最有效手段之一。如果你在WHERE中对某个未索引的列进行条件过滤，数据库将不得不进行全表扫描（Full Table Scan），在数据量大时极其缓慢，并且长时间占用大量资源，容易导致锁争用。

常见陷阱1：对索引列进行函数操作或计算。

-- 假设create_time字段有索引 -- 错误的写法：索引失效 SELECT * FROM logs WHERE DATE(create_time) = '2023-10-27'; SELECT * FROM products WHERE price + 10 > 100; -- 正确的写法：保持索引列“干净” SELECT * FROM logs WHERE create_time >= '2023-10-27 00:00:00' AND create_time < '2023-10-28 00:00:00'; SELECT * FROM products WHERE price > 90;

当你对索引列使用函数（DATE(),YEAR(),UPPER()等）或进行运算时，MySQL通常无法使用该列的索引。

常见陷阱2：模糊查询LIKE的通配符位置。

-- `%`在前，索引失效（最左前缀原则） SELECT * FROM users WHERE username LIKE '%admin%'; SELECT * FROM users WHERE username LIKE '%admin'; -- `%`在后，索引可能有效 SELECT * FROM users WHERE username LIKE 'admin%';

LIKE ‘%keyword’这种写法，因为无法利用索引的最左前缀匹配特性，会导致全表扫描。如果必须进行前后模糊匹配，且数据量巨大，需要考虑使用全文索引（FULLTEXT）或专门的搜索引擎。

常见陷阱3：NULL值判断。在WHERE条件中，判断一个字段是否为NULL，不能使用=或!=，必须使用IS NULL或IS NOT NULL。

-- 错误，结果永远为空 SELECT * FROM employees WHERE manager_id = NULL; SELECT * FROM employees WHERE manager_id != NULL; -- 正确 SELECT * FROM employees WHERE manager_id IS NULL; SELECT * FROM employees WHERE manager_id IS NOT NULL;

这是一个非常基础的坑，但每年都有新手程序员掉进去。

2.4 ORDER BY与LIMIT：排序、分页与性能深坑

ORDER BY用于对结果集排序，LIMIT用于限制返回的行数，常用于分页。它们俩组合起来，是Web应用中最常见的模式，也是性能问题的重灾区。

问题场景：深度分页。典型的分页查询是这样写的：

-- 获取第1页，每页20条 SELECT * FROM articles ORDER BY create_time DESC LIMIT 0, 20; -- 获取第2页 SELECT * FROM articles ORDER BY create_time DESC LIMIT 20, 20;

当页码很小的时候，速度很快。但是，如果你想获取第1000页的数据呢？

SELECT * FROM articles ORDER BY create_time DESC LIMIT 20000, 20;

这条语句的问题在于，MySQL会先排序出前20020条记录，然后丢弃前面的20000条，只返回最后的20条。这个排序和丢弃的过程，在数据量巨大（offset值很大）时，消耗会非常惊人，CPU和内存压力剧增，响应时间直线上升。

优化方案1：使用索引覆盖扫描 + 子查询。如果create_time上有索引，且id是主键，可以这样优化：

SELECT a.* FROM articles a INNER JOIN ( SELECT id FROM articles ORDER BY create_time DESC LIMIT 20000, 20 ) AS t ON a.id = t.id ORDER BY a.create_time DESC;

内层子查询只选择主键id并利用create_time索引进行排序和分页，由于id也在索引中，这是一个纯粹的索引覆盖扫描，速度极快。拿到20个目标id后，再通过主键回表查询所有列。这种方法将巨大的排序开销转移到了高效的索引扫描上。

优化方案2：记录上次查询的边界值。更适合“无限滚动”的场景。记录上一页最后一条记录的排序字段值（如create_time）和唯一标识（如id），下一页查询时直接以此为起点。

-- 假设上一页最后一条记录的create_time是 ‘2023-10-26 12:00:00’， id是 12345 SELECT * FROM articles WHERE (create_time < ‘2023-10-26 12:00:00’) OR (create_time = ‘2023-10-26 12:00:00’ AND id < 12345) ORDER BY create_time DESC, id DESC LIMIT 20;

这种方式完全避免了OFFSET，无论翻到多深，性能都几乎恒定。

注意：ORDER BY的字段顺序也很关键。如果排序条件涉及多个字段，要留意联合索引的建立顺序，必须遵循索引的最左前缀原则，否则索引可能无法用于排序。

3. 多表关联（JOIN）：连接的逻辑与性能抉择

单表查询满足不了复杂业务，JOIN就成了必备技能。但JOIN用不好，就是性能杀手。我们需要理解不同类型的JOIN及其底层机制。

3.1 JOIN的类型与语义

首先必须厘清几种JOIN的区别，这是正确性的基础。我们以A表和B表为例。

• INNER JOIN（内连接）：返回两个表中连接条件匹配的所有行。这是最常用的一种。如果A的某行在B中没有匹配，则不会返回。
• LEFT JOIN（左外连接）：返回左表（A）的所有行，即使右表（B）中没有匹配的行。如果B中没有匹配，则B侧的列以NULL填充。
• RIGHT JOIN（右外连接）：与LEFT JOIN相反，返回右表（B）的所有行。实践中较少使用，因为通常可以通过调换表顺序用LEFT JOIN代替，使逻辑更统一。
• FULL OUTER JOIN（全外连接）：返回左右两表的所有行。当某一行在另一表中没有匹配时，另一侧的列补NULL。MySQL原生不支持FULL OUTER JOIN，但可以通过LEFT JOIN和RIGHT JOIN的UNION来模拟。
• CROSS JOIN（交叉连接）：返回两表的笛卡尔积，即A表的每一行与B表的每一行组合。除非业务需要，否则慎用，数据量会爆炸式增长。

一个关键的心智模型：把JOIN理解为先产生一个临时的“笛卡尔积”中间结果，然后再根据ON后面的条件进行过滤。INNER JOIN就是过滤出符合条件的行；LEFT JOIN则是先保证左表行全部保留，再去匹配右表。

3.2 ON vs. WHERE：筛选时机决定结果集

这是JOIN查询中一个非常容易混淆的点，直接影响结果。

• ON子句：指定表之间如何连接的条件。它发生在生成连接结果的阶段。
• WHERE子句：对连接后形成的整个结果集进行过滤。它发生在连接完成之后。

对于INNER JOIN，把条件放在ON里和WHERE里，结果通常是相同的。但对于OUTER JOIN（如LEFT JOIN），区别就大了。

-- 场景：查询所有部门及其员工（包括没有员工的部门） SELECT d.dept_name, e.emp_name FROM departments d LEFT JOIN employees e ON d.dept_id = e.dept_id;

这条语句会列出所有部门，如果部门没有员工，emp_name为NULL。

-- 如果想在连接时，只连接特定条件的员工（如状态为‘active’），条件应放在ON里 SELECT d.dept_name, e.emp_name FROM departments d LEFT JOIN employees e ON d.dept_id = e.dept_id AND e.status = ‘active’;

这样，每个部门仍然会列出，但只连接status=‘active’的员工，其他员工不会出现，对应位置为NULL。

-- 如果把条件放在WHERE里，结果就完全不同了 SELECT d.dept_name, e.emp_name FROM departments d LEFT JOIN employees e ON d.dept_id = e.dept_id WHERE e.status = ‘active’; -- 或者 e.dept_id IS NULL

WHERE e.status = ‘active’会过滤掉所有e.status为NULL或非‘active’的行。由于没有员工的部门，其连接后的e.status就是NULL，因此也会被过滤掉！这就把LEFT JOIN的效果退化成了INNER JOIN，失去了查询“所有部门”的本意。

经验法则：ON用于定义表之间的关系，WHERE用于定义最终结果的过滤条件。对于OUTER JOIN，需要保留主表所有行的过滤条件（如e.column IS NULL）可以放在WHERE中；而针对从表的过滤条件，如果想影响连接行为就放ON里，如果想过滤最终结果就放WHERE里（但要清楚其可能将外连接变为内连接的效果）。

3.3 JOIN的底层算法与性能优化

MySQL执行JOIN主要有三种算法，了解它们有助于我们写出更高效的查询。

1. Nested-Loop Join (NLJ，嵌套循环连接)：这是最简单也是最基础的算法。想象两个循环：遍历驱动表（外表）的每一行，对于每一行，再去被驱动表（内表）里全表扫描或利用索引找匹配的行。如果驱动表有M行，被驱动表有N行，复杂度大约是O(M*N)。当被驱动表有高效索引时（通常是在ON条件的列上），MySQL会使用“Index Nested-Loop Join”，性能尚可。如果没索引，就是“Simple Nested-Loop Join”，性能灾难。

2. Block Nested-Loop Join (BNL，块嵌套循环连接)：当被驱动表没有可用索引时，MySQL可能会使用BNL。它不再一行一行地处理，而是将驱动表的多行读入一个内存缓冲区（join buffer），然后批量地去扫描被驱动表进行比较。这减少了内表被扫描的次数。你可以通过join_buffer_size系统变量来调整缓冲区大小。但BNL本质上仍是二次方复杂度，只是常数项小了，并非根本解决方案。

3. Hash Join (MySQL 8.0.18引入)：这是MySQL 8.0带来的重大优化。对于等值连接（=），它会将较小的表（基于统计信息判断）在内存中构建为一个哈希表，然后扫描较大的表，并对每一行计算哈希值去哈希表中查找匹配。在内存充足且连接条件没有索引的情况下，Hash Join的性能通常远优于BNL。从MySQL 8.0.20开始，BNL已被移除，Hash Join成为没有索引可用时的默认连接算法。

给开发者的优化建议：

• 为JOIN条件建立索引：这是黄金法则。确保ON d.dept_id = e.dept_id中的e.dept_id字段上有索引。通常，在“多”的一方（如employees表的dept_id）建立外键索引是标准做法。
• 小表驱动大表：在决定JOIN顺序时（有时优化器会自动选择），尽量让数据量小的表作为驱动表（外层循环），这样可以减少内层循环的次数。在INNER JOIN中，MySQL优化器通常会帮你做出好选择；但在LEFT JOIN中，左表固定为驱动表，因此如果左表很大而右表很小，可能不是最优。
• 避免复杂的JOIN条件：尽量避免在ON或WHERE中对连接列使用函数或表达式，这会导致索引失效。
• 理解执行计划：使用EXPLAIN或EXPLAIN ANALYZE（MySQL 8.0.18+）查看查询的执行计划，关注type列（访问类型，如ref,eq_ref,index,ALL），key列（使用的索引），以及rows列（预估扫描行数）。这是诊断JOIN性能问题的终极工具。

4. 聚合与分组：数据汇总的核心操作

当我们不再关心单条记录，而是想知道“总数、平均、最大、最小”时，就需要用到聚合函数和GROUP BY。这也是数据分析的基石。

4.1 常用聚合函数

• COUNT()：计数。COUNT(*)统计行数（含NULL），COUNT(column)统计该列非NULL值的数量。
• SUM()：求和。仅对数值列有效。
• AVG()：平均值。数值列，忽略NULL。
• MAX()/MIN()：最大值/最小值。适用于数值、日期、字符串。
• GROUP_CONCAT()：MySQL特有，将同一分组内的字符串连接起来。非常实用，例如“查询每个部门的所有员工姓名，用逗号分隔”。

4.2 GROUP BY的运作机制与严格模式

GROUP BY的逻辑是：根据指定的列将数据分成若干“组”，然后对每一组应用聚合函数，每组产生一行结果。

一个常见的错误是：在SELECT子句中，出现了既非聚合函数，又非GROUP BY子句中的列。

-- 假设一个订单详情表 order_details (order_id, product_id, quantity) -- 错误的查询：product_id 既不在GROUP BY中，也不是聚合函数 SELECT order_id, product_id, SUM(quantity) FROM order_details GROUP BY order_id;

这条语句在语义上是模糊的：一个order_id分组里可能有多个不同的product_id，那么结果中该显示哪一个呢？在MySQL 5.7及以后版本的默认SQL模式（包含ONLY_FULL_GROUP_BY）下，这条语句会直接报错。

正确的写法是：

-- 要么把product_id也加入GROUP BY SELECT order_id, product_id, SUM(quantity) FROM order_details GROUP BY order_id, product_id; -- 要么对product_id使用聚合函数 SELECT order_id, GROUP_CONCAT(product_id), SUM(quantity) FROM order_details GROUP BY order_id; -- 或者只选择聚合列和分组列 SELECT order_id, SUM(quantity) as total_quantity FROM order_details GROUP BY order_id;

启用ONLY_FULL_GROUP_BY模式（强烈建议在生产环境启用）能强制你写出语义明确的GROUP BY查询，避免难以察觉的逻辑错误。

4.3 HAVING子句：对分组后的结果进行过滤

WHERE和HAVING的区别，是另一个面试高频考点。

• WHERE：在分组前过滤数据行。它不能包含聚合函数。
• HAVING：在分组后过滤分组。它通常与聚合函数一起使用。

-- 找出总订单金额超过10000的客户 SELECT customer_id, SUM(order_amount) as total_amount FROM orders GROUP BY customer_id HAVING SUM(order_amount) > 10000; -- 找出2023年10月之后，总订单金额超过10000的客户 SELECT customer_id, SUM(order_amount) as total_amount FROM orders WHERE order_date >= ‘2023-10-01’ -- WHERE先过滤掉10月前的订单，减少分组的数据量 GROUP BY customer_id HAVING SUM(order_amount) > 10000; -- HAVING再过滤分组

性能提示：尽可能将过滤条件写在WHERE中，而不是HAVING中。因为WHERE在分组前执行，可以提前减少需要处理的数据量，而HAVING是在所有数据分组聚合后才执行过滤。

4.4 WITH ROLLUP：生成小计与总计

WITH ROLLUP是GROUP BY的一个扩展，它会在分组结果的基础上，添加一层层的“小计”和最终的“总计”行。

SELECT YEAR(order_date) as order_year, MONTH(order_date) as order_month, SUM(order_amount) FROM orders GROUP BY order_year, order_month WITH ROLLUP;

结果会先按年、月分组汇总，然后会生成每个年份的小计行（month列为NULL），最后生成所有年份的总计行（year和month列均为NULL）。这在制作报表时非常方便。

5. 子查询与衍生表：在查询中嵌套查询

子查询，顾名思义，就是嵌套在其他SQL语句中的查询。它非常强大，但也容易导致性能问题。

5.1 子查询的类型与位置

根据返回结果和出现的位置，子查询主要分几类：

1. 标量子查询：返回单一值（一行一列）。可以出现在SELECT、WHERE、HAVING甚至ORDER BY子句中，几乎可以当作一个常量值使用。

   -- 查询工资高于平均工资的员工 SELECT emp_name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); -- 在SELECT子句中 SELECT emp_name, salary, (SELECT AVG(salary) FROM employees) as avg_salary FROM employees;

2. 列子查询：返回一列多行。通常与IN、ANY/SOME、ALL操作符一起使用。

   -- 查询销售部的所有员工 SELECT emp_name FROM employees WHERE dept_id IN (SELECT dept_id FROM departments WHERE dept_name = ‘销售部’);

3. 行子查询：返回一行多列。较少使用。

   -- 查询和‘张三’在同一个部门且职位相同的员工 SELECT * FROM employees WHERE (dept_id, job_title) = (SELECT dept_id, job_title FROM employees WHERE emp_name = ‘张三’);

4. 表子查询（衍生表）：返回一个多行多列的结果集，必须作为“表”使用，必须要有别名。

   -- 查询每个部门工资最高的员工 SELECT e.dept_id, e.emp_name, e.salary FROM employees e INNER JOIN ( SELECT dept_id, MAX(salary) as max_salary FROM employees GROUP BY dept_id ) t ON e.dept_id = t.dept_id AND e.salary = t.max_salary;

   这里的(SELECT ... GROUP BY dept_id) t就是一个衍生表。

5.2 关联子查询与非关联子查询

这是理解子查询性能的关键。

• 非关联子查询：子查询可以独立执行，不依赖于外层查询。像上面的(SELECT AVG(salary) FROM employees)就是非关联的。它执行一次，得到一个固定值，然后外层查询使用这个值。
• 关联子查询：子查询的执行依赖于外层查询的当前行。它会对外层查询的每一行都执行一次。

  -- 查询每个部门中工资高于该部门平均工资的员工 SELECT e1.dept_id, e1.emp_name, e1.salary FROM employees e1 WHERE salary > ( SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id -- 关联条件！ );

  对于e1表中的每一行，子查询都要根据e1.dept_id去计算一次该部门的平均工资。如果外层有1万行，这个子查询就要执行1万次！性能通常很差。

5.3 用JOIN重写关联子查询

由于关联子查询的性能问题，一个重要的优化技巧就是尽可能用JOIN来重写它。上面的例子可以改写为：

SELECT e1.dept_id, e1.emp_name, e1.salary FROM employees e1 INNER JOIN ( SELECT dept_id, AVG(salary) as dept_avg_salary FROM employees GROUP BY dept_id ) t ON e1.dept_id = t.dept_id WHERE e1.salary > t.dept_avg_salary;

这样，计算部门平均工资的子查询只执行一次，生成一个衍生表，然后通过JOIN与主表关联。效率远高于关联子查询。

经验之谈：在写SQL时，看到关联子查询要条件反射般地思考：能否用JOIN重写？大多数情况下是可以的，而且性能会更好。当然，有些非常复杂的逻辑可能用子查询表达更清晰，这时就需要权衡可读性和性能，并通过EXPLAIN来验证。

6. 集合操作与窗口函数：进阶查询利器

除了基本的SELECT ... FROM ... WHERE模式，DQL还提供了更高级的集合操作和窗口函数，用于处理复杂的数据分析需求。

6.1 集合操作：UNION, INTERSECT, EXCEPT

集合操作将多个SELECT语句的结果集进行合并、取交集或差集。所有参与集合操作的查询必须拥有相同数量和兼容类型的列。

• UNION：合并两个结果集，自动去除重复行。UNION ALL则保留所有行，包括重复的，性能更好，因为不需要去重。

  -- 查询所有经理和所有薪资超过20000的员工（同一个人可能同时满足两个条件，用UNION会去重） SELECT emp_id, emp_name FROM employees WHERE job_title LIKE ‘%经理%’ UNION SELECT emp_id, emp_name FROM employees WHERE salary > 20000;

• INTERSECT：返回两个结果集的交集（MySQL 8.0.31+ 才原生支持）。在早期版本，可以通过INNER JOIN或EXISTS子查询模拟。

  -- MySQL 8.0.31+ SELECT emp_id FROM employees WHERE dept_id = 1 INTERSECT SELECT emp_id FROM employees WHERE salary > 10000; -- 早期版本模拟 SELECT DISTINCT e1.emp_id FROM employees e1 INNER JOIN employees e2 ON e1.emp_id = e2.emp_id WHERE e1.dept_id = 1 AND e2.salary > 10000;

• EXCEPT(或MINUS): 返回第一个结果集有而第二个结果集没有的行（差集）。MySQL 8.0.31+ 原生支持EXCEPT。

  -- 查询在部门1但薪资不高于10000的员工 SELECT emp_id FROM employees WHERE dept_id = 1 EXCEPT SELECT emp_id FROM employees WHERE salary > 10000;

6.2 窗口函数：跨行的计算能力

窗口函数是MySQL 8.0引入的强大特性。它允许你在不改变原表行数的情况下，对与当前行相关的“窗口”内的数据进行计算。这是GROUP BY无法做到的，因为GROUP BY会折叠行。

一个窗口函数调用通常包含：

1. 窗口函数本身：如ROW_NUMBER(),RANK(),DENSE_RANK(),SUM(),AVG(),LAG(),LEAD()等。
2. OVER()子句：定义“窗口”。
   • PARTITION BY：将数据分成多个分区，窗口函数在每个分区内独立计算。类似于GROUP BY的分组，但不会合并行。
   • ORDER BY：定义分区内的排序顺序，这对于排名函数和累计计算至关重要。
   • ROWS/RANGE BETWEEN：定义窗口的帧（frame），即计算时具体包含哪些行。默认为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（从分区第一行到当前行）。

经典用例1：排名

-- 给每个部门的员工按薪资排名 SELECT emp_name, dept_id, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) as rank_in_dept, -- 连续唯一排名 RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as rank_with_gap, -- 并列会跳号 DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as dense_rank_no_gap -- 并列不跳号 FROM employees;

经典用例2：累计计算

-- 计算每个员工截止到当前日期（按入职日期排序）的累计薪资支出 SELECT emp_name, hire_date, salary, SUM(salary) OVER (ORDER BY hire_date) as running_total_salary FROM employees ORDER BY hire_date;

经典用例3：访问前后行的数据（LAG/LEAD）

-- 查看每个员工的上一条和下一条薪资记录（按调整日期） SELECT emp_name, change_date, new_salary, LAG(new_salary) OVER (PARTITION BY emp_name ORDER BY change_date) as previous_salary, LEAD(new_salary) OVER (PARTITION BY emp_name ORDER BY change_date) as next_salary FROM salary_changes;

窗口函数极大地简化了原本需要复杂自连接或子查询才能完成的报表类SQL，是数据分析的利器。掌握它，能让你在解决“部门内排名”、“累计值”、“同比环比”等问题时游刃有余。

7. 执行计划（EXPLAIN）：读懂查询的“体检报告”

无论你掌握了多少语法和技巧，最终都要落实到数据库如何执行你的查询上。EXPLAIN命令就是查看MySQL优化器决定的查询执行计划的工具。它是SQL性能调优的“显微镜”。

7.1 EXPLAIN输出关键列解读

执行EXPLAIN SELECT ...，你会得到一个表格，其中以下几列最为关键：

• id：查询的序列号。id相同，执行顺序从上到下；id不同，值越大优先级越高，越先执行（如子查询）。
• select_type：查询类型。常见的有：
  • SIMPLE：简单查询（无子查询或UNION）。
  • PRIMARY：复杂查询中最外层的SELECT。
  • SUBQUERY：子查询中的第一个SELECT。
  • DERIVED：衍生表（FROM子句中的子查询）。
  • UNION：UNION中的第二个及以后的SELECT。
• table：当前行正在访问哪张表。
• type：访问类型，性能的核心指标。从好到坏大致是：
  • system>const>eq_ref>ref>range>index>ALL。
  • const/eq_ref：通过主键或唯一索引进行等值查找，性能最好。
  • ref：使用非唯一索引进行等值查找。
  • range：利用索引进行范围扫描（BETWEEN,>,<,IN等）。
  • index：全索引扫描（遍历整个索引树，比全表扫描ALL快，因为索引文件通常更小）。
  • ALL：全表扫描，需要极力避免，尤其是在大表上。
• possible_keys：查询可能用到的索引。
• key：查询实际用到的索引。NULL表示没用到索引。
• key_len：使用的索引长度。可用于判断是否充分利用了联合索引。
• rows：MySQL预估需要扫描的行数。这个值越小越好。
• Extra：额外信息，包含很多重要提示：
  • Using index：使用了覆盖索引，性能极佳。
  • Using where：在存储引擎检索行后，服务器层再次进行了过滤。
  • Using temporary：使用了临时表，常见于GROUP BY、ORDER BY、DISTINCT等操作，需关注。
  • Using filesort：使用了文件排序（非索引排序），当排序数据量大时性能差。
  • Using join buffer：使用了连接缓冲区，可能意味着连接条件缺少有效索引。

7.2 使用EXPLAIN诊断慢查询

假设我们有一个慢查询：

SELECT * FROM orders WHERE customer_id = 100 AND order_date > ‘2023-01-01’ ORDER BY total_amount DESC;

使用EXPLAIN分析：

EXPLAIN SELECT * FROM orders WHERE customer_id = 100 AND order_date > ‘2023-01-01’ ORDER BY total_amount DESC;

可能的输出及分析：

• 如果type是ALL，key是NULL，说明进行了全表扫描。需要建立索引。
• 如果type是ref，key是idx_customer，说明用到了customer_id的索引，但order_date条件可能是在服务器层用Using where过滤的。
• 如果Extra里有Using filesort，说明ORDER BY total_amount DESC无法利用索引排序，需要建立(customer_id, total_amount)或(customer_id, order_date, total_amount)的联合索引来优化。
• 如果rows值非常大，即使有索引，也可能需要优化查询条件或考虑数据归档。

更强大的工具：EXPLAIN ANALYZE (MySQL 8.0.18+)EXPLAIN只是预估计划，EXPLAIN ANALYZE会实际执行查询（只读），并返回实际的执行时间、循环次数等详细信息，比EXPLAIN更准确。

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 100 AND order_date > ‘2023-01-01’ ORDER BY total_amount DESC;

输出会包含每个步骤的实际耗时（如actual time=0.100..120.500），是性能调优的终极利器。

8. 实战中的经验与避坑指南

最后，结合我这些年踩过的坑，分享一些书本上不一定有，但非常实用的DQL经验。

8.1 关于索引的“玄学”

1. 最左前缀原则是铁律：对于联合索引(a, b, c)，它能加速a、(a,b)、(a,b,c)的查询，但无法加速b、c、(b,c)的查询。设计索引时，要把最常用作查询条件的列放在最左边。
2. 索引不是越多越好：索引会占用磁盘空间，更严重的是，每次INSERT、UPDATE、DELETE操作都需要维护索引，降低写性能。需要权衡读写比例。
3. 区分度低的列不适合建索引：比如“性别”列，只有‘男’、‘女’两个值，建索引几乎没用，因为优化器可能认为全表扫描更快。
4. 长字符串字段索引：对VARCHAR(255)这样的字段建索引，索引会很大。可以考虑前缀索引INDEX(column_name(20))，但前缀长度要足够保证区分度。或者使用crc32等哈希函数生成一个短整型字段并对其建索引。

8.2 分页查询的再思考

除了前面提到的深度分页优化，对于总数查询COUNT(*)也要小心。SELECT COUNT(*) FROM big_table WHERE condition;在数据量巨大时可能很慢。如果业务不需要精确总数，可以考虑：

• 用EXPLAIN的rows列估算。
• 使用缓存，定期更新总数。
• 对于WHERE条件复杂的，考虑在汇总表或使用其他计数系统。

8.3 隐式类型转换的坑

MySQL在比较时，如果字段类型和传入值类型不一致，会进行隐式类型转换，这可能导致索引失效。

-- user_id 是 VARCHAR 类型，但有索引 SELECT * FROM users WHERE user_id = 123; -- 这里123是数字，会发生类型转换，索引可能失效 SELECT * FROM users WHERE user_id = ‘123’; -- 正确的写法，类型匹配

养成习惯，确保WHERE条件中的值与字段类型一致。

8.4 慎用SELECT FOR UPDATE

SELECT ... FOR UPDATE用于在事务中锁定选中的行，防止其他事务修改。但它很容易导致死锁和锁等待超时。使用时务必：

• 尽量使用主键或唯一索引进行精确锁定，缩小锁定范围。
• 保持事务简短，锁的持有时间尽可能短。
• 在业务逻辑允许的情况下，尝试使用乐观锁（版本号）代替悲观锁。

8.5 理解“NULL”的语义

NULL在数据库中表示“未知”，它与任何值（包括它自己）的比较结果都是NULL（即FALSE）。这导致了很多陷阱：

• WHERE column = NULL永远不成立，要用IS NULL。
• NULL参与聚合函数（如COUNT(column)、SUM(column)）时会被忽略。
• 对包含NULL的列进行DISTINCT或GROUP BY时，所有NULL会被视为同一组。
• 在ORDER BY中，NULL值默认被视为最小值（ASC排在最前，DESC排在最后）。

写查询时，心里要时刻绷紧NULL这根弦，明确业务上“空值”到底应该用NULL还是空字符串‘’、0等特殊值来表示，并在查询时做相应处理。

说到底，写出高效、正确的DQL语句，一半靠扎实的语法基础，另一半靠对数据库底层运行机制的理解和大量的实战经验。多使用EXPLAIN分析你的查询，多思考数据是如何被访问和处理的，遇到性能问题多从索引、JOIN方式、子查询、数据量这几个维度去排查，慢慢地你就会形成一种“数据库思维”，在设计和编写查询时就能自然而然地避开大多数坑。