MySQL基础第十一天：SQL高级查询实战 + 索引优化落地 + 面试必刷题

大家好，我是白鹿为溪～本期是MySQL基础系列第十一天内容，承接第十天的SQL核心语法进阶与执行原理，重点聚焦「实战落地」——把前一天学到的底层原理，转化为可直接应用在工作、面试中的SQL技巧，全程干货无冗余，新手能夯实能力，进阶者可查漏补缺，同步配套高频面试题解析，助力大家吃透MySQL进阶知识点。

一、本章核心定位

第十天我们重点掌握了SQL核心语法进阶（复杂查询基础、多表连接等）和SQL执行底层原理，而第十一天的核心目标是「落地应用」：

熟练编写高频复杂查询，解决工作中常见的统计、筛选、排序场景；
掌握索引优化实战技巧，避开索引失效坑，提升SQL执行效率；
深度解读执行计划，能快速定位SQL性能问题；
吃透面试中高频出现的进阶题目，从容应对面试提问。

简单来说，第十天是「懂原理、会基础」，第十一天是「能实战、会优化」，前后衔接，形成完整的MySQL进阶学习闭环。

二、核心内容详解（实战为主，兼顾原理）

1. 高级查询语法强化（工作高频）

这部分是在第十天基础语法的延伸，重点解决「复杂场景下的查询需求」，每个知识点均搭配易懂案例+可直接运行的SQL语句，新手直接套用即可。

（1）子查询进阶：EXISTS vs IN + 关联子查询

核心原理：IN是把主查询和子查询结果集做匹配，子查询结果集越大，效率越低；EXISTS是判断子查询是否存在结果，只返回布尔值，结果集大小对效率影响极小。

前置准备：创建测试表并插入数据

-- 订单表
CREATE TABLE `orders` (
  `order_id` INT PRIMARY KEY AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `order_amount` DECIMAL(10,2) NOT NULL,
  `order_date` DATE NOT NULL
);

-- 用户表
CREATE TABLE `users` (
  `user_id` INT PRIMARY KEY AUTO_INCREMENT,
  `user_name` VARCHAR(50) NOT NULL,
  `age` INT NOT NULL
);

-- 插入测试数据
INSERT INTO `users` (`user_name`, `age`) VALUES 
('张三', 25), ('李四', 30), ('王五', 28), ('赵六', 35);

INSERT INTO `orders` (`user_id`, `order_amount`, `order_date`) VALUES 
(1, 199.99, '2026-04-01'), (1, 299.99, '2026-04-05'), 
(2, 89.99, '2026-04-02'), (3, 399.99, '2026-04-08');

IN写法：适合子查询结果集较小的场景（比如查询少量用户的订单）

-- 查询用户ID为1、2的所有订单
SELECT * FROM `orders` 
WHERE `user_id` IN (SELECT `user_id` FROM `users` WHERE `age` BETWEEN 25 AND 30);

EXISTS写法：适合子查询结果集较大的场景（比如百万级用户表查少量订单）

-- 等价查询，效率更优
SELECT * FROM `orders` o
WHERE EXISTS (SELECT 1 FROM `users` u WHERE u.`user_id` = o.`user_id` AND u.`age` BETWEEN 25 AND 30);

关联子查询：子查询引用主查询的字段，逐行匹配

-- 查询每个用户的最高金额订单
SELECT o1.* FROM `orders` o1
WHERE `order_amount` = (SELECT MAX(`order_amount`) FROM `orders` o2 WHERE o2.`user_id` = o1.`user_id`);

（2）分组统计高阶：GROUP_CONCAT + HAVING + 分组排序

核心原理：GROUP_CONCAT拼接分组内的字段值；HAVING过滤分组结果（WHERE过滤行，HAVING过滤分组）；分组后排序需结合ORDER BY。

GROUP_CONCAT案例：拼接每个用户的订单日期

-- 查询每个用户的订单ID和订单日期拼接结果
SELECT 
  `user_id`,
  GROUP_CONCAT(`order_id` ORDER BY `order_date` SEPARATOR ' | ') AS `order_ids`,
  GROUP_CONCAT(`order_date` SEPARATOR ', ') AS `order_dates`
FROM `orders`
GROUP BY `user_id`;

HAVING案例：筛选订单总金额大于300的用户

-- 先按用户分组，再筛选分组后总金额>300的结果
SELECT 
  `user_id`,
  SUM(`order_amount`) AS `total_amount`
FROM `orders`
GROUP BY `user_id`
HAVING `total_amount` > 300;

分组排序案例：按用户分组，查询每个用户的订单，并按订单金额降序排列

-- 分组内排序（需结合ORDER BY）
SELECT 
  `user_id`,
  `order_id`,
  `order_amount`
FROM `orders`
GROUP BY `user_id`, `order_id`, `order_amount`
ORDER BY `user_id` ASC, `order_amount` DESC;

（3）窗口函数基础：ROW_NUMBER/RANK/DENSE_RANK

核心原理：窗口函数不分组，保留原始行数据，基于指定范围（窗口）计算排名/统计值，解决TopN、排名等复杂场景。

前置准备：插入更多测试数据

INSERT INTO `orders` (`user_id`, `order_amount`, `order_date`) VALUES 
(1, 499.99, '2026-04-10'), (2, 299.99, '2026-04-06'), (3, 199.99, '2026-04-09');

三种窗口函数对比：

-- 按订单金额降序排名，对比三种函数差异
SELECT 
  `order_id`,
  `user_id`,
  `order_amount`,
  -- 行号唯一，相同金额也按顺序编号
  ROW_NUMBER() OVER (ORDER BY `order_amount` DESC) AS `row_num`,
  -- 相同金额排名相同，后续编号跳跃（如1,1,3）
  RANK() OVER (ORDER BY `order_amount` DESC) AS `rank_num`,
  -- 相同金额排名相同，后续编号连续（如1,1,2）
  DENSE_RANK() OVER (ORDER BY `order_amount` DESC) AS `dense_rank_num`
FROM `orders`;

TopN实战案例：查询每个用户金额最高的2笔订单

-- 按用户分区，按金额降序排名，取排名<=2的结果
SELECT * FROM (
  SELECT 
    `order_id`,
    `user_id`,
    `order_amount`,
    ROW_NUMBER() OVER (PARTITION BY `user_id` ORDER BY `order_amount` DESC) AS `rn`
  FROM `orders`
) t
WHERE `rn` <= 2;

（4）多表连接优化：JOIN顺序 + 避免笛卡尔积

核心原理：小表做驱动表（减少循环次数）；连接条件必须完整，否则触发笛卡尔积（返回大量无效数据）。

LEFT JOIN案例：查询所有用户及其订单（无订单也显示）

-- 左连接：以users为主表，orders为从表，保留所有用户数据
SELECT 
  u.`user_id`,
  u.`user_name`,
  o.`order_id`,
  o.`order_amount`
FROM `users` u
LEFT JOIN `orders` o ON u.`user_id` = o.`user_id`
ORDER BY u.`user_id`;

JOIN优化案例：避免笛卡尔积（错误写法vs正确写法）

-- 错误写法：缺少连接条件，触发笛卡尔积（返回5*6=30条无效数据）
-- SELECT * FROM `users` u JOIN `orders` o;

-- 正确写法：添加连接条件，返回有效关联数据
SELECT * FROM `users` u JOIN `orders` o ON u.`user_id` = o.`user_id`;

子查询进阶：区分关联子查询与非关联子查询，重点掌握EXISTS与IN的效率对比——当子查询结果集较大时，EXISTS效率更高（只判断存在性，不返回具体数据）；当子查询结果集较小时，IN效率更优。同时补充子查询优化技巧：避免多层嵌套，尽量改写为JOIN查询，减少数据库执行压力。
分组统计高阶：除了基础的GROUP BY分组，重点掌握GROUP_CONCAT函数（将分组后的字段值拼接为字符串，常用于多值展示）、HAVING子句（分组后过滤，注意与WHERE的区别：WHERE过滤行，HAVING过滤分组），以及分组后排序（GROUP BY + ORDER BY结合，解决统计结果排序需求）。
窗口函数基础：入门3个高频窗口函数——ROW_NUMBER()（给每行分配唯一序号，即使值相同也不重复）、RANK()（值相同则序号相同，后续序号跳跃）、DENSE_RANK()（值相同序号相同，后续序号连续），结合场景说明用法（如排名、TopN查询），避开新手易混淆的点。
多表连接优化：承接第十天的多表连接语法，重点讲解JOIN顺序选择（小表作为驱动表，减少循环次数）、避免笛卡尔积（确保连接条件完整，不遗漏ON子句），以及LEFT JOIN/RIGHT JOIN的使用场景与注意事项（避免NULL值导致的查询错误）。

2. SQL执行原理落地（吃透底层，才能优化）

基于第十天讲解的SQL执行流程，结合实战案例拆解关键环节，教大家如何用原理定位问题。

（1）SQL完整执行流程（结合案例理解）

以「查询用户ID为1的订单」为例，执行流程如下：

解析器：校验SQL语法（比如关键字拼写、表/字段是否存在），生成解析树；
优化器：分析解析树，选择最优执行计划（比如是否走user_id索引、选择哪个表做驱动表）；
执行器：调用存储引擎（InnoDB），根据优化器选择的计划执行SQL，返回结果。

实操验证：用EXPLAIN查看执行计划

EXPLAIN SELECT * FROM `orders` WHERE `user_id` = 1;

执行后重点看type（索引类型）、key（实际使用的索引）、Extra（额外信息），后续优化会详细讲解。

（2）索引命中规则：最左前缀 + 失效场景

核心原理：联合索引必须遵循「最左前缀原则」，否则索引失效；以下5种场景会导致索引失效，新手务必避开。

最左前缀原则案例：创建联合索引，验证索引命中情况

-- 创建联合索引：idx_user_date (user_id, order_date)
CREATE INDEX `idx_user_date` ON `orders` (`user_id`, `order_date`);

-- 命中索引：符合最左前缀（使用了联合索引第一个字段user_id）
EXPLAIN SELECT * FROM `orders` WHERE `user_id` = 1;

-- 命中索引：符合最左前缀（使用了联合索引前两个字段）
EXPLAIN SELECT * FROM `orders` WHERE `user_id` = 1 AND `order_date` = '2026-04-01';

-- 未命中索引：不符合最左前缀（跳过了第一个字段，直接用order_date）
EXPLAIN SELECT * FROM `orders` WHERE `order_date` = '2026-04-01';

索引失效5大场景（附案例）：

-- 1. 对索引字段进行隐式转换（字符串字段用数字查询）
-- 假设user_id是字符串类型，此处隐式转换为字符串，索引失效
EXPLAIN SELECT * FROM `orders` WHERE `user_id` = 1; 

-- 修正：用字符串类型查询，索引命中
EXPLAIN SELECT * FROM `orders` WHERE `user_id` = '1';

-- 2. 对索引字段使用函数（SUBSTR、CONCAT等）
EXPLAIN SELECT * FROM `orders` WHERE SUBSTR(`order_date`, 1, 7) = '2026-04'; -- 索引失效

-- 修正：直接匹配字段，索引命中
EXPLAIN SELECT * FROM `orders` WHERE `order_date` BETWEEN '2026-04-01' AND '2026-04-30';

-- 3. 模糊查询以%开头（前置通配符）
EXPLAIN SELECT * FROM `orders` WHERE `user_name` LIKE '%张三'; -- 索引失效

-- 修正：后置通配符，索引命中（需user_name有索引）
EXPLAIN SELECT * FROM `orders` WHERE `user_name` LIKE '张三%';

-- 4. OR连接的字段未全部命中索引
EXPLAIN SELECT * FROM `orders` WHERE `user_id` = 1 OR `order_amount` = 299.99; 
-- 只有user_id有索引，order_amount无索引，整体索引失效

-- 修正：为order_amount创建索引，或拆分OR为UNION
EXPLAIN SELECT * FROM `orders` WHERE `user_id` = 1
UNION
SELECT * FROM `orders` WHERE `order_amount` = 299.99;

-- 5. 违背联合索引最左前缀原则（前文已演示，此处再强调）

（3）临时表与文件排序：Using temporary + Using filesort

核心原理：

Using temporary：MySQL创建临时表存储分组/排序结果（常见于GROUP BY、ORDER BY、DISTINCT）；
Using filesort：MySQL无法通过索引排序，需额外执行文件排序操作，效率较低。

案例验证：

-- 触发Using temporary + Using filesort（无索引时）
EXPLAIN SELECT `user_id`, SUM(`order_amount`) FROM `orders` GROUP BY `user_id`;

-- 优化：为分组字段创建索引，避免临时表和文件排序
CREATE INDEX `idx_user_id` ON `orders` (`user_id`);
EXPLAIN SELECT `user_id`, SUM(`order_amount`) FROM `orders` GROUP BY `user_id`;
-- 此时Extra中无Using temporary/Using filesort，效率提升

SQL完整执行流程：再次梳理核心环节——解析器（校验SQL语法，生成解析树）→ 优化器（选择最优执行计划，比如选择是否使用索引、选择驱动表）→ 执行器（调用存储引擎执行SQL，返回结果），重点说明优化器的选择逻辑，帮助大家理解“为什么有时候写的SQL没走索引”。
索引命中规则：强化第十天的索引知识点，重点强调最左前缀原则（联合索引必须从最左侧字段开始匹配，否则失效）、索引失效的常见场景（隐式转换、使用函数、模糊查询%开头、OR连接未全部命中索引等），每个场景搭配简单案例，直观易懂。
临时表与文件排序：解释执行计划中Using temporary（产生临时表）、Using filesort（文件排序）的产生原因——多与GROUP BY、ORDER BY、DISTINCT相关，补充避免方法（合理创建索引、优化查询语句，减少临时表和文件排序的产生）。

3. 索引优化实战（生产级技巧）

索引是提升SQL效率的核心，这部分重点讲解“如何创建合理的索引”“如何优化已有的索引”，避免冗余索引、无效索引，适用于生产环境。

（1）联合索引设计原则

核心原则：高频查询字段在前、区分度高的字段在前；联合索引字段不宜过多（3-4个为宜）。

案例：根据业务场景设计索引

-- 业务场景：高频查询「按用户ID+订单日期筛选订单」
-- 错误设计：order_date在前，user_id在后（违背高频字段在前原则）
CREATE INDEX `idx_date_user` ON `orders` (`order_date`, `user_id`);

-- 正确设计：user_id在前（高频），order_date在后（低频）
CREATE INDEX `idx_user_date` ON `orders` (`user_id`, `order_date`);

-- 验证：查询命中正确索引
EXPLAIN SELECT * FROM `orders` WHERE `user_id` = 1 AND `order_date` = '2026-04-01';

（2）索引创建与删除规范

创建规范：

先分析查询场景，只为高频查询字段创建索引；
避免为低选择性字段（比如性别、状态，区分度极低）创建索引；
索引名称要规范（比如idx_字段1_字段2），便于维护。

删除规范：

删除前先通过EXPLAIN、慢查询日志确认该索引未被高频查询使用；
避免删除核心索引（比如主键索引、联合索引），防止SQL性能骤降。

实操案例：创建、删除索引

-- 创建普通索引（单字段）
CREATE INDEX `idx_order_amount` ON `orders` (`order_amount`);

-- 删除索引
DROP INDEX `idx_order_amount` ON `orders`;

-- 创建唯一索引（字段值唯一，比如订单编号）
CREATE UNIQUE INDEX `uk_order_no` ON `orders` (`order_no`);

（3）避免冗余索引、重复索引

核心定义：

冗余索引：已创建联合索引(a,b)，再创建索引(a)（联合索引的左字段已包含单字段索引的功能）；
重复索引：同一字段创建多个相同索引（无意义，增加维护成本）。

案例：清理冗余索引

-- 已创建联合索引idx_user_date (user_id, order_date)
-- 再创建单字段索引idx_user_id (user_id)，属于冗余索引
CREATE INDEX `idx_user_id` ON `orders` (`user_id`);

-- 清理冗余索引
DROP INDEX `idx_user_id` ON `orders`;

联合索引设计原则：遵循“高频查询字段在前、区分度高的字段在前”，避免创建过多联合索引（联合索引字段不宜过多，一般3-4个为宜），举例说明：如果高频查询条件是“user_id + order_date”，则联合索引应创建为(user_id, order_date)，而非(order_date, user_id)。
索引创建与删除规范：创建索引前，先分析查询场景，避免为低频查询字段创建索引；删除索引前，确认该索引未被任何高频查询使用（可通过慢查询日志、执行计划分析），防止删除索引后导致SQL性能下降。
避免冗余索引、重复索引：冗余索引（如已创建联合索引(a,b)，再创建索引(a)）会增加数据库维护成本，降低写入效率，需及时清理；重复索引（同一字段创建多个相同索引）无任何意义，需坚决删除。

4. 高频面试题解析（重点必背）

结合本期实战案例，整理4道MySQL进阶面试高频题，附原理+答案，面试前快速背诵，同时加深对知识点的理解。

问题1：子查询和JOIN哪个更快？
解析：没有绝对答案，取决于子查询结果集大小。子查询结果集小时，IN + 子查询效率优（逻辑简单，优化器易优化）；子查询结果集大时，JOIN或EXISTS效率更优（EXISTS只判断存在性，JOIN可利用索引匹配）；核心建议：避免多层嵌套子查询，优先改写为JOIN，让优化器选择最优计划。
关联案例：前文「EXISTS vs IN」案例，结果集小时IN更快，结果集大时EXISTS更快。
问题2：哪些情况会导致索引失效？
解析：5大核心场景（结合前文案例记忆）：1. 对索引字段做隐式转换（字符串字段用数字查询）；2. 对索引字段使用函数（SUBSTR、CONCAT等）；3. 模糊查询以%开头（前置通配符）；4. OR连接的字段未全部命中索引；5. 违背联合索引最左前缀原则（跳过左字段查询）。
问题3：EXPLAIN重点字段怎么看？
解析：重点关注4个核心字段，从优到劣判断SQL性能：1. type（索引类型，从优到劣：system > const > eq_ref > ref > range > ALL，尽量优化到ref及以上）；2. key（实际使用的索引，为NULL则未走索引）；3. Extra（避免出现Using temporary、Using filesort，这两种情况会降低执行效率）；4. rows（扫描的行数，越少说明SQL效率越高，扫描行数多意味着需要遍历更多数据）。
关联案例：前文用EXPLAIN分析索引命中、临时表和文件排序的案例，可结合查看这4个字段的变化。
问题4：窗口函数ROW_NUMBER()、RANK()、DENSE_RANK()的区别？
解析：核心区别在“值相同时的序号处理”：ROW_NUMBER()不重复，即使值相同序号也递增；RANK()值相同序号相同，后续序号跳跃（如1,1,3）；DENSE_RANK()值相同序号相同，后续序号连续（如1,1,2）。
关联案例：前文三种窗口函数对比的SQL案例，执行后可直观看到三种函数的序号差异，便于记忆。
问题1：子查询和JOIN哪个更快？
解析：没有绝对答案，取决于子查询结果集大小。子查询结果集小时，IN + 子查询效率优；子查询结果集大时，JOIN或EXISTS效率更优。建议避免多层子查询，尽量改写为JOIN，便于优化器选择最优执行计划。
问题2：哪些情况会导致索引失效？
解析：常见场景有5种：1. 对索引字段进行隐式转换（如字符串字段用数字查询）；2. 对索引字段使用函数（如SUBSTR(name,1,3)）；3. 模糊查询以%开头（如LIKE '%test'）；4. OR连接的字段未全部命中索引；5. 违背联合索引最左前缀原则。
问题3：EXPLAIN重点字段怎么看？
解析：重点关注4个字段：type（索引类型，从优到劣：system > const > eq_ref > ref > range > ALL，尽量优化到ref及以上）、key（实际使用的索引，为NULL则未走索引）、Extra（避免出现Using temporary、Using filesort）、rows（扫描的行数，越少越好）。
问题4：窗口函数ROW_NUMBER()、RANK()、DENSE_RANK()的区别？
解析：核心区别在“值相同时的序号处理”：ROW_NUMBER()不重复，即使值相同序号也递增；RANK()值相同序号相同，后续序号跳跃（如1,1,3）；DENSE_RANK()值相同序号相同，后续序号连续（如1,1,2）。

三、前后知识衔接（系列学习闭环）

承接前文（第九天、第十天）：第九天讲解事务隔离级别与锁机制，第十天讲解SQL语法进阶与执行原理，第十一天聚焦实战优化，将原理转化为技巧，同时为第九天的锁机制、事务性能优化提供支撑（优化SQL可减少锁竞争）。
衔接后续：本期内容是后续学习存储过程、函数、触发器，以及MySQL运维、性能调优的基础，只有熟练掌握复杂查询和索引优化，才能更好地应对后续更高级的知识点。

四、总结

本期MySQL基础第十一天，核心是“实战落地”——没有过多复杂的理论，重点是把第十天的原理转化为可直接使用的查询技巧、索引优化方法，同时配套面试高频题，兼顾学习与面试需求。建议大家结合本期知识点，多动手写SQL、分析执行计划，真正吃透每一个技巧，避免“一看就会，一写就错”。

下一期我们将讲解MySQL存储过程与函数，继续推进MySQL进阶学习，感兴趣的小伙伴可以持续关注～

posted @ 2026-04-15 11:13 白鹿为溪阅读(0) 评论(0) 收藏举报

上一篇：MySQL基础第十期：SQL核心语法进阶 + 执行原理 + 面试高频题解析

（注：文档部分内容可能由 AI 生成）

posted @ 2026-04-15 11:18 白鹿为溪阅读(29) 评论(0) 收藏举报

刷新页面返回顶部

bailu12

MySQL基础第十一天：SQL高级查询实战 + 索引优化落地 + 面试必刷题

MySQL基础第十一天：SQL高级查询实战 + 索引优化落地 + 面试必刷题

一、本章核心定位

二、核心内容详解（实战为主，兼顾原理）

1. 高级查询语法强化（工作高频）

（1）子查询进阶：EXISTS vs IN + 关联子查询

（2）分组统计高阶：GROUP_CONCAT + HAVING + 分组排序

（3）窗口函数基础：ROW_NUMBER/RANK/DENSE_RANK

（4）多表连接优化：JOIN顺序 + 避免笛卡尔积

2. SQL执行原理落地（吃透底层，才能优化）

（1）SQL完整执行流程（结合案例理解）

（2）索引命中规则：最左前缀 + 失效场景

（3）临时表与文件排序：Using temporary + Using filesort

3. 索引优化实战（生产级技巧）

（1）联合索引设计原则

（2）索引创建与删除规范

（3）避免冗余索引、重复索引

4. 高频面试题解析（重点必背）

三、前后知识衔接（系列学习闭环）

四、总结

公告