第32章 查询优化实战
本章目标
- 掌握"发现慢查询 → EXPLAIN 分析 → 优化"的三步方法论
- 精通索引优化的核心策略：左前缀原则、覆盖索引、避免索引失效
- 掌握 7 种常见索引失效场景及对应的改写方案
- 深入掌握 JOIN、子查询、ORDER BY、GROUP BY、LIMIT 的优化技巧
- 掌握深分页问题的多种解决方案
- 理解 COUNT 优化在 InnoDB 下的特殊性
- 能够独立完成一条慢查询的端到端优化
前置知识
- 理解 B+Tree 索引结构和聚簇索引/二级索引的区别
- 能看懂 EXPLAIN 输出的字段含义（type, key, rows, Extra）
- 掌握基本的 SQL 编写能力（JOIN, 子查询, 聚合函数）
- 了解慢查询日志的配置和使用
32.1 查询优化的三步法
步骤 1: 发现慢查询
→ 慢查询日志 / 监控告警 / 应用日志
步骤 2: EXPLAIN 分析
→ 查看执行计划：type, key, rows, Extra
→ 定位瓶颈：全表扫描？索引未使用？临时表？文件排序？
步骤 3: 优化
→ 加索引 / 重写查询 / 调整表结构 / 调整参数
→ 验证优化效果（再次 EXPLAIN + 实际执行时间对比）32.1.1 优化效果验证的黄金标准
sql
-- 优化前
SET profiling = 1;
SELECT * FROM orders WHERE user_id = 12345 ORDER BY created_at DESC LIMIT 20;
SHOW PROFILES; -- 记录第 N 条的 Query_ID
-- 添加索引
CREATE INDEX idx_user_created ON orders(user_id, created_at);
-- 优化后（同一条查询）
SELECT * FROM orders WHERE user_id = 12345 ORDER BY created_at DESC LIMIT 20;
SHOW PROFILES; -- 对比前后耗时
-- 查看详细耗时分解
SHOW PROFILE FOR QUERY 15; -- 显示各阶段耗时
SHOW PROFILE CPU, BLOCK IO FOR QUERY 15;32.2 索引优化核心策略
32.2.1 左前缀原则（Leftmost Prefix）
sql
-- 创建复合索引
CREATE INDEX idx_a_b_c ON test_table(a, b, c);
-- 以下查询可以使用索引：
WHERE a = 1; -- 使用索引的 a 列
WHERE a = 1 AND b = 2; -- 使用索引的 a, b 列
WHERE a = 1 AND b = 2 AND c = 3; -- 使用索引全部列（最佳）
WHERE a = 1 AND c = 3; -- 使用索引的 a 列（跳过 b，c 无法使用）
WHERE a = 1 AND b > 2 AND c = 3; -- 使用 a, b 列（范围后 c 失效）
-- 以下查询无法使用索引：
WHERE b = 2; -- 不从 a 开始
WHERE b = 2 AND c = 3; -- 不从 a 开始
WHERE c = 3; -- 不从 a 开始
-- 以下查询可以使用索引（等值条件顺序无关）：
WHERE a = 1 AND b = 2; -- 优化器自动调整顺序
WHERE b = 2 AND a = 1; -- 与上一句等价32.2.2 覆盖索引（Covering Index）
覆盖索引：查询所需的所有列都包含在索引中，无需回表查询聚簇索引。
sql
-- 创建覆盖索引
CREATE INDEX idx_user_status_time ON orders(user_id, status, created_at);
-- 覆盖索引查询：Extra = "Using index"（无需回表）
EXPLAIN SELECT user_id, status, created_at
FROM orders
WHERE user_id = 100;
-- key: idx_user_status_time, Extra: Using index
-- 非覆盖索引查询：Extra = NULL（需要回表查 price 列）
EXPLAIN SELECT user_id, status, created_at, price
FROM orders
WHERE user_id = 100;
-- key: idx_user_status_time, Extra: NULL
-- 因为 price 不在索引中，需要回表
-- 覆盖索引的核心价值：
-- 1. 减少磁盘随机 I/O（不需要回表）
-- 2. 减少 Buffer Pool 污染（只需缓存索引页）
-- 3. 对于 COUNT 查询尤其重要
EXPLAIN SELECT COUNT(*) FROM orders WHERE user_id = 100;
-- InnoDB 会选择最小的可用索引进行扫描32.2.3 索引下推（Index Condition Pushdown / ICP）
MySQL 5.6+ 的优化：将 WHERE 条件中能通过索引判断的部分下推到存储引擎层处理，减少回表次数。
sql
CREATE INDEX idx_name_age ON employees(name, age);
-- 查询：name LIKE '张%' 且 age > 30
SELECT * FROM employees WHERE name LIKE '张%' AND age > 30;
-- 无 ICP（5.5）：
-- 存储引擎：通过索引找到 name LIKE '张%' 的所有行 → 全部回表
-- Server 层：对回表后的完整行数据过滤 age > 30
-- 有 ICP（5.6+）：
-- 存储引擎：通过索引找到 name LIKE '张%' 的行，
-- 同时检查索引中 age 列的值是否 > 30
-- 只有满足 age > 30 的行才回表
-- 结果：回表次数大大减少
-- Extra: Using index condition ← ICP 生效的标志32.3 索引失效的 7 种场景及解决方案
场景 1：对索引列使用函数
sql
-- 索引失效！
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
-- 优化方案：将索引列暴露出来
SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-- 更多例子：
-- 失效: WHERE DATE(created_at) = '2024-01-15'
-- 优化: WHERE created_at >= '2024-01-15' AND created_at < '2024-01-16'
-- 失效: WHERE LOWER(email) = 'user@example.com'
-- 优化: 使用大小写不敏感的排序规则（collation），如 utf8mb4_general_ci
-- 或者创建基于函数的索引（MySQL 8.0.13+）：
CREATE INDEX idx_email_lower ON users((LOWER(email)));
-- 然后 WHERE LOWER(email) = LOWER('User@Example.com') 可以使用索引场景 2：隐式类型转换
sql
-- phone 列是 VARCHAR 类型
CREATE INDEX idx_phone ON users(phone);
-- 索引失效！（字符串与数字比较，MySQL 将字符串转为数字）
SELECT * FROM users WHERE phone = 13800138000;
-- 优化方案：使用字符串值
SELECT * FROM users WHERE phone = '13800138000';
-- EXPLAIN 对比：
-- WHERE phone = 13800138000 → type: ALL (索引失效)
-- WHERE phone = '13800138000' → type: ref, key: idx_phone
-- 隐式转换规则：
-- 字符串 vs 数字 → 字符串转为数字
-- VARCHAR '138' 转为数字 138
-- VARCHAR '138abc' 转为数字 138（截断！）
-- EXPLAIN 中 Extra = "Using where; Using index" → 可能发生了隐式转换场景 3：LIKE 前导通配符
sql
-- 索引失效！（前导通配符无法确定起始范围）
SELECT * FROM users WHERE name LIKE '%John';
SELECT * FROM users WHERE name LIKE '%John%';
-- 可以使用索引（后导通配符 OK）
SELECT * FROM users WHERE name LIKE 'John%';
-- 解决方案：
-- 1. 使用全文索引（FULLTEXT）代替 LIKE '%keyword%'
ALTER TABLE products ADD FULLTEXT INDEX ft_name_desc (name, description);
SELECT * FROM products
WHERE MATCH(name, description) AGAINST('keyword' IN NATURAL LANGUAGE MODE);
-- 2. 使用专业的搜索引擎：Elasticsearch / Sphinx
-- 3. 如果需要前缀搜索，确保通配符在后面
-- WHERE name LIKE 'prefix%' ← 可以使用索引
-- 4. MySQL 8.0+ 反转索引（适用于后缀搜索场景）
-- 创建一个存储反转字符串的列，然后对该列建索引
-- WHERE reverse_name LIKE REVERSE('suffix') + '%'场景 4：OR 连接到无索引列
sql
CREATE INDEX idx_status ON orders(status);
-- 索引失效！（user_id 没有索引）
SELECT * FROM orders WHERE status = 'paid' OR user_id = 100;
-- 优化方案 1：使用 UNION ALL 分别利用各自的索引
SELECT * FROM orders WHERE status = 'paid'
UNION ALL
SELECT * FROM orders WHERE user_id = 100;
-- 优化方案 2：给 user_id 也建立索引
CREATE INDEX idx_user_id ON orders(user_id);
-- 优化方案 3：使用 IN（如果适用）
SELECT * FROM orders WHERE status IN ('paid', 'shipped');场景 5：负向操作符
sql
-- 通常无法使用索引
SELECT * FROM orders WHERE status != 'cancelled';
SELECT * FROM orders WHERE status <> 'cancelled';
SELECT * FROM users WHERE id NOT IN (1, 2, 3);
SELECT * FROM products WHERE NOT price > 100;
-- 优化方案：
-- 1. 改写为正向条件
-- 如果 status 有几个值：pending, paid, shipped, cancelled
SELECT * FROM orders WHERE status IN ('pending', 'paid', 'shipped');
-- 2. 使用 LEFT JOIN / NOT EXISTS 代替 NOT IN
-- 原查询：SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM blacklist);
-- 优化：
SELECT u.* FROM users u
LEFT JOIN blacklist b ON u.id = b.user_id
WHERE b.user_id IS NULL;
-- 3. 注意：SELECT * FROM orders WHERE status IS NULL 可以走索引
-- 但 SELECT * FROM orders WHERE status IS NOT NULL 通常不能场景 6：范围查询中断复合索引
sql
CREATE INDEX idx_a_b_c ON test_table(a, b, c);
-- 以下查询：c 列无法使用索引
WHERE a = 1 AND b > 10 AND c = 5;
-- 索引使用：a(等值=ref) → b(范围=range) → c(❌ 范围后失效)
-- Extra: Using index condition (仅 a,b 有 ICP)
-- 优化方案：调整索引列顺序
-- 将范围查询的列放在最后
CREATE INDEX idx_a_c_b ON test_table(a, c, b);
-- 现在 WHERE a = 1 AND c = 5 AND b > 10
-- 索引使用：a(等值) → c(等值) → b(范围) ← 全部使用！场景 7：在 ORDER BY 中使用不同的索引方向
sql
-- 这种场景较少见但有实际意义
CREATE INDEX idx_a_b ON test_table(a, b);
-- 可以使用索引排序：
ORDER BY a ASC, b ASC
ORDER BY a DESC, b DESC -- MySQL 8.0+ 支持降序索引
-- 索引失效排序：
ORDER BY a ASC, b DESC -- 方向不匹配（除非 MySQL 8.0+ 支持降序）
-- Extra: Using filesort32.4 JOIN 优化
32.4.1 基本原则
sql
-- 原则 1：驱动表应该小，被驱动表的 JOIN 列必须有索引
-- 错误示例：
SELECT * FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.created_at > '2024-01-01';
-- 如果 order_items.order_id 没有索引 → 全表扫描
-- 优化：确保被驱动表的 JOIN 列有索引
ALTER TABLE order_items ADD INDEX idx_order_id (order_id);sql
-- 原则 2：小结果集驱动大结果集
-- orders 表有 1000 万行，但 WHERE 条件过滤后仅剩 50 行
-- order_items 表有 5000 万行
SELECT o.*, oi.*
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.created_at > NOW() - INTERVAL 1 DAY;
-- MySQL 优化器通常会自动选择 rows 估算值小的表作为驱动表sql
-- 原则 3：避免笛卡尔积
-- 错误：缺少 JOIN 条件
SELECT u.name, o.order_no
FROM users u, orders o; -- 笛卡尔积！10000 × 1000000 行
-- 正确：始终写明 JOIN 条件
SELECT u.name, o.order_no
FROM users u
JOIN orders o ON u.id = o.user_id;32.4.2 JOIN 类型选择
sql
-- EXISTS vs IN
-- EXISTS：适用于外部表小，子查询结果大
-- 对外部表的每一行，子查询找到第一个匹配就停止
SELECT * FROM orders o
WHERE EXISTS (
SELECT 1 FROM order_items oi
WHERE oi.order_id = o.id AND oi.price > 1000
);
-- IN：适用于子查询结果小
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE vip_level > 3);
-- MySQL 8.0 对 IN 子查询进行了大量优化（自动转换为 semi-join）
-- 在 5.6+ 版本中，EXISTS 和 IN 的性能差异已经大大缩小32.4.3 STRAIGHT_JOIN
强制指定驱动表（覆盖优化器的选择）：
sql
-- 如果优化器选择了错误的驱动表，可以使用 STRAIGHT_JOIN
SELECT o.*, u.name
FROM users u
STRAIGHT_JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01';
-- users 必须作为驱动表
-- 注意：仅在确认优化器选择有误时使用
-- 大多数情况下，优化器的选择是正确的32.5 子查询优化
32.5.1 子查询 → JOIN
sql
-- 原查询（子查询）
SELECT * FROM orders
WHERE user_id IN (
SELECT id FROM users WHERE gender = 'F'
);
-- 优化方案（JOIN）
SELECT o.* FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.gender = 'F';
-- MySQL 8.0 中，优化器会自动将这种 IN 子查询转换为 semi-join
-- EXPLAIN 中 Extra = "FirstMatch(users)" 表示使用 semi-join32.5.2 关联子查询 → 派生表
sql
-- 原查询（关联子查询，对每个订单执行一次子查询）
SELECT o.*,
(SELECT COUNT(*) FROM order_items oi WHERE oi.order_id = o.id) AS item_count
FROM orders o;
-- 优化方案（派生表）
SELECT o.*, t.item_count
FROM orders o
LEFT JOIN (
SELECT order_id, COUNT(*) AS item_count
FROM order_items
GROUP BY order_id
) AS t ON o.id = t.order_id;32.5.3 使用 CTE（MySQL 8.0+）
sql
-- 使用 WITH 子句提高可读性
WITH user_order_stats AS (
SELECT
user_id,
COUNT(*) AS order_count,
SUM(total_amount) AS total_spent
FROM orders
GROUP BY user_id
)
SELECT u.name, s.order_count, s.total_spent
FROM users u
JOIN user_order_stats s ON u.id = s.user_id
WHERE s.total_spent > 10000;32.6 深分页优化
32.6.1 问题分析
sql
-- 问题查询：翻到第 100001 页
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;
-- 执行过程：
-- MySQL 需要扫描前 1,000,020 行，丢弃前 1,000,000 行
-- I/O 和 CPU 开销巨大
-- 实际返回给用户仅 20 行，效率极低32.6.2 解决方案 1：覆盖索引 + 延迟关联
sql
-- 优化方案：使用覆盖索引找到主键，再 JOIN 回去取完整数据
SELECT o.* FROM orders o
INNER JOIN (
SELECT id FROM orders
ORDER BY id
LIMIT 1000000, 20
) AS tmp ON o.id = tmp.id;
-- 工作流程：
-- 1. 子查询利用 PRIMARY KEY 的覆盖索引快速扫描 1,000,020 个 id 值（仅索引扫描）
-- 2. 取最后 20 个 id
-- 3. 用 20 个 id 去主表 JOIN（仅 20 次主键查找）
-- 性能提升：1,000,020 次全行扫描 → 1,000,020 次索引扫描 + 20 次主键查找
-- 如果 ORDER BY 的不是主键，需要额外的覆盖索引
CREATE INDEX idx_created_id ON orders(created_at, id);
-- 子查询使用这个覆盖索引32.6.3 解决方案 2：游标分页（推荐）
sql
-- 记录上一页最后一条数据的 id（或排序字段值）
-- 第一页：
SELECT * FROM orders ORDER BY id LIMIT 20;
-- 最后一行的 id = 980
-- 第二页（使用上一页最后的 id）：
SELECT * FROM orders WHERE id > 980 ORDER BY id LIMIT 20;
-- 优点：
-- 1. 每次只扫描 20 行
-- 2. 不受数据总量的影响
-- 3. 适合"下一页"式的无限滚动和移动端
-- 缺点：
-- 1. 无法直接跳转到第 N 页
-- 2. 如果排序字段可能重复（如按 created_at 排序），
-- 需要处理边界情况：
SELECT * FROM orders
WHERE created_at > '2024-01-15 10:30:00'
OR (created_at = '2024-01-15 10:30:00' AND id > 12345)
ORDER BY created_at, id
LIMIT 20;32.6.4 解决方案 3：业务折中
sql
-- 1. 限制最大页数（如最多第 100 页）
-- 2. 使用搜索引擎（Elasticsearch）处理搜索和分页
-- 3. 预计算/缓存热门数据的页
-- 4. 不提供精确总页数，使用"无限滚动"UI32.7 COUNT 优化
32.7.1 COUNT 函数的核心区别
sql
-- COUNT(*) vs COUNT(1) vs COUNT(col)
-- COUNT(*) 和 COUNT(1)：完全等价
-- 优化器在内部将 COUNT(1) 转换为 COUNT(*)
-- 都统计行数（包含 NULL 行）
-- COUNT(col)：统计 col 列非 NULL 的行数
-- 需要检查每一行的 col 是否为 NULL，但 NULL 检查本身很快
-- 性能对比（绝大多数场景）：
-- COUNT(*) ≈ COUNT(1) ≈ COUNT(主键) ≈ COUNT(非NULL列)
-- 但 COUNT(col) 在有 NULL 值的列上需要额外的 NULL 检查
-- 结论：直接用 COUNT(*)，语义最清楚，性能最好32.7.2 InnoDB 下的 COUNT 优化
sql
-- InnoDB 必须扫描索引来计算 COUNT（因为 MVCC，不同事务看到不同行数）
-- 优化技巧：MySQL 会选择最小的索引来扫描
-- 例：users 表有 PRIMARY KEY(id) 和 INDEX idx_email(email)
-- SELECT COUNT(*) FROM users;
-- → 优化器选择 idx_email 而不是 PRIMARY KEY
-- → 因为二级索引通常比聚簇索引小很多
-- 无需 WHERE 条件的大表近似计数：
SELECT TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'orders';
-- 注意：这是估算值（不精确），误差可能在 10-40%！
-- 如果有 WHERE 条件，只能老老实实扫描满足条件的行
-- 优化重点：确保 WHERE 条件用到索引32.7.3 大表计数的业务折中
sql
-- 方案 1：使用缓存计数器表
CREATE TABLE counter (
table_name VARCHAR(100) PRIMARY KEY,
row_count BIGINT NOT NULL DEFAULT 0
);
-- 使用触发器/应用代码维护计数
-- 或定期刷新：INSERT INTO counter ... ON DUPLICATE KEY UPDATE ...
-- 方案 2：使用 Redis 缓存计数
-- 方案 3：只显示模糊计数（如 "10000+" 条结果）
-- 方案 4：使用统计表
CREATE TABLE orders_stats (
stat_date DATE PRIMARY KEY,
order_count INT
);
-- 每日更新一次：INSERT INTO orders_stats SELECT CURDATE(), COUNT(*) FROM orders;32.8 ORDER BY 优化
32.8.1 利用索引排序
sql
-- 索引可用于 ORDER BY 的情况：
-- 1. ORDER BY 列顺序与索引左前缀完全匹配
-- 2. WHERE 有等值条件 + ORDER BY 剩余索引列
-- 例：INDEX(a, b, c)
ORDER BY a, b, c -- 可以使用索引（全匹配）
WHERE a = 1 ORDER BY b, c -- 可以使用索引（a 等值 + b,c 排序）
WHERE a = 1 AND b = 2 ORDER BY c -- 可以使用索引
WHERE a = 1 AND b > 2 ORDER BY b, c -- 可以使用索引（a 定值, b 范围, c 排序）
-- 索引无法用于 ORDER BY 的情况：
ORDER BY b, c -- 不从 a 开始
ORDER BY a, c -- 跳过 b
WHERE a = 1 ORDER BY c -- 跳过 b
WHERE a IN (1,2,3) ORDER BY b -- IN 不等价于等值（多个值）
WHERE a > 1 ORDER BY a, b -- a 是范围条件
-- 判断标准：EXPLAIN 的 Extra 列
-- "Using filesort" → 需要排序（可能需要优化）
-- 没有 "Using filesort" → 使用索引排序（理想情况）32.8.2 filesort 优化
当必须进行 filesort（无法利用索引排序）时，可以通过调整缓冲区大小来优化：
sql
-- 查看排序相关变量
SHOW VARIABLES LIKE 'sort_buffer_size';
SHOW STATUS LIKE 'Sort%';
-- Sort_merge_passes：排序过程中需要合并的次数（合并到磁盘的次数）
-- 如果此值较高，考虑增大 sort_buffer_size
-- sort_buffer_size：每个排序线程的内存缓冲区
-- 默认 256KB，生产环境可设置为 256KB-4MB（不y要太大）
-- 注意：这是 per-thread 分配！100 个连接 × 4MB = 400MB
-- 优化策略：
-- 1. 如果排序数据小于 sort_buffer_size → 内存排序（快）
-- 2. 如果超过 → 使用磁盘临时文件（慢）
-- 3. 尽量减少排序的数据量（SELECT 需要的列而非 SELECT *）32.9 GROUP BY 优化
32.9.1 松散索引扫描（Loose Index Scan）
sql
-- INDEX(a, b)
SELECT a, b FROM t GROUP BY a, b;
-- 优化器可以"跳着"扫描索引，直接跳到下一组的开头
-- Extra: Using index for group-by ← 最优！
-- 条件：
-- 1. 单表查询
-- 2. GROUP BY 列形成索引的最左前缀
-- 3. 没有 WHERE 子句中的范围条件（如 a > 1）
-- 4. 聚合函数仅 MIN() 和 MAX() 可以使用（且参数为同一列）32.9.2 紧凑索引扫描（Tight Index Scan）
sql
-- INDEX(a, b)
SELECT a, b FROM t WHERE a = 1 GROUP BY b;
-- 不能松散扫描，但索引顺序仍然匹配，扫描所有 a=1 的行
-- Extra: Using index ← 还不错32.9.3 临时表排序
sql
-- INDEX(a, b)
SELECT a, c FROM t GROUP BY c;
-- 索引不匹配 GROUP BY，MySQL 创建临时表 + filesort
-- Extra: Using temporary; Using filesort ← 需要优化32.9.4 UNION vs UNION ALL
sql
-- UNION：去重（隐式 DISTINCT）
SELECT name FROM users
UNION
SELECT name FROM archived_users;
-- 需要额外排序去重，性能差
-- UNION ALL：保留所有行（不删除重复）
SELECT name FROM users
UNION ALL
SELECT name FROM archived_users;
-- 无需去重，性能好
-- 优化原则：如果确定没有重复或者重复可以接受，用 UNION ALL
-- 如果必须去重，确保每个子查询都有可能去重（减少 UNION 去重的数据量）常见错误
错误 1：添加了索引但查询仍然全表扫描
问题排查清单：
1. 检查索引列是否在 WHERE 中被函数包裹
2. 检查是否存在隐式类型转换
3. 检查优化器估算的行数——如果返回超过 ~20% 的行，优化器可能选择全表扫描
4. 检查查询是否包含了导致索引失效的操作
5. 使用 EXPLAIN FORMAT=JSON 查看更详细的信息
6. 使用 FORCE INDEX 强制使用索引进行对比测试错误 2：SELECT * 导致的性能问题
问题：习惯使用 SELECT *，导致：
1. 覆盖索引无法使用（必须回表）
2. LIMIT 分页时传输大量无用数据
3. 排序时排序缓冲区装不下（数据量大）
解决：只 SELECT 业务需要的列错误 3：在谓词中使用 != 导致索引失效
问题：WHERE status != 'deleted' 无法使用索引
解决：改写为 IN
WHERE status IN ('active', 'pending', 'archived')
或者：如果 status 的 distinct 值很少，使用 != 可能优化器仍然会选择索引
（当 != 排除了大部分的值时，全表扫描不一定更慢）错误 4：过度依赖 FORCE INDEX / USE INDEX
问题：使用 FORCE INDEX 强制使用某个索引，但数据分布变化后该索引不再最优
解决：
- 在大多数情况下信任优化器
- 定期更新统计信息：ANALYZE TABLE table_name;
- 仅在确认优化器出错时使用 hint，并注明原因错误 5：忽略 EXPLAIN 中的 "Using temporary" 和 "Using filesort"
问题：看到 Extra 中有 "Using temporary" 或 "Using filesort" 但忽略
它们在 ORDER BY + GROUP BY + JOIN 组合场景下尤其常见
解决：
- Using temporary：检查 ORDER BY 和 GROUP BY 的列是否有索引
- Using filesort：检查排序是否能利用索引
- 两者同时出现 → 先考虑添加复合索引来避免
- 小表时可以接受，大表时必须优化本章练习
练习 1：索引失效场景演示
准备一个 10 万行的测试表，演示并记录 7 种索引失效场景：
- 执行并 EXPLAIN 每种失效场景
- 记录每种场景的 type 和 rows
- 给出每种场景的优化方案
- EXPAIN 优化后的查询，对比前后差异
练习 2：深分页优化实战
- 创建包含 500 万行的 orders 表
- 测试 LIMIT 4999990, 20 的执行时间
- 使用覆盖索引 + 延迟关联优化，测试执行时间
- 使用游标分页优化，测试执行时间
- 对比三种方案的执行计划和实际性能
练习 3：JOIN 优化实战
- 创建三个表：users(10万), orders(100万), order_items(500万)
- 设计一个复杂的多表 JOIN 查询（含 WHERE, GROUP BY, ORDER BY）
- 在无索引和加索引情况下分别 EXPLAIN 并测量性能
- 写出每一步的性能提升幅度
- 说明驱动表选择对性能的影响
练习 4：慢查询端到端优化
使用慢查询日志发现一个慢查询（或模拟一个），完成完整的优化流程：
- 从慢查询日志中提取 SQL 和执行统计
- 使用 EXPLAIN 分析执行计划
- 识别性能瓶颈
- 设计并实施优化方案（加索引、重写 SQL）
- 验证优化效果（EXPLAIN 对比 + 执行时间对比）
- 写一份优化报告
练习 5：GROUP BY 和 ORDER BY 优化
- 创建带有多种索引组合的测试表
- 测试能够利用索引排序的场景（5 种以上）
- 测试不能利用索引排序的场景（5 种以上）
- 对比 filesort 的磁盘 I/O 情况（增大 sort_buffer_size 前后）
- 测试松散索引扫描和紧凑索引扫描的性能差异
练习 6：COUNT 优化对比
- 在 1000 万行的表上测试：
- COUNT(*) 的执行时间
- COUNT(id) 的执行时间
- COUNT(1) 的执行时间
- COUNT(non_indexed_col) 的执行时间
- 查看 optimizer trace 确认优化器行为
- 测试有索引和无索引的 WHERE 条件下的 COUNT 性能
- 对比 information_schema 的 TABLE_ROWS 估算值