章节3：索引与性能优化

---

一、学习目标

完成本章学习后，你将能够：

1. 理解 B+ 树索引的底层数据结构与工作原理
2. 区分聚簇索引、二级索引、联合索引的适用场景
3. 使用 EXPLAIN 分析 SQL 执行计划
4. 定位慢查询并制定 SQL 优化策略
5. 识别常见索引失效场景并掌握避坑方法
6. 了解分库分表的基本策略与适用场景

---

二、核心知识点

3.1 B+ 树索引原理与索引类型

定义

B+ 树是 MySQL InnoDB 存储引擎默认的索引结构，具有以下特点：

• 所有数据存储在叶子节点，非叶子节点只存储索引键
• 叶子节点之间通过双向链表连接，支持高效范围查询
• 树的高度通常为 2~4 层，查询 IO 次数极少

索引类型

类型	说明
聚簇索引（Clustered Index）	主键索引，叶子节点存储整行数据，一张表只有一个
二级索引（Secondary Index）	非主键索引，叶子节点存储主键值，需回表查询
联合索引（Composite Index）	多列组合索引，遵循最左前缀原则
唯一索引（Unique Index）	索引列值唯一，允许 NULL（多个 NULL 算不同值）
全文索引（Full-Text Index）	用于大文本字段的全文搜索

语法与示例

-- 创建测试表
CREATE TABLE employee (
    id INT AUTO_INCREMENT,
    emp_no VARCHAR(20) NOT NULL,
    name VARCHAR(50) NOT NULL,
    age INT,
    department VARCHAR(50),
    salary DECIMAL(10,2),
    PRIMARY KEY (id)                      -- 聚簇索引
);

-- 唯一索引
CREATE UNIQUE INDEX idx_emp_no ON employee(emp_no);

-- 单列索引
CREATE INDEX idx_name ON employee(name);

-- 联合索引（部门 + 薪水）
CREATE INDEX idx_dept_salary ON employee(department, salary);

-- 查看表的索引
SHOW INDEX FROM employee;

-- 使用索引查询（自动匹配）
EXPLAIN SELECT * FROM employee WHERE department = '技术部' AND salary > 10000;

聚簇索引 vs 二级索引：如果查询列恰好都在二级索引中，则无需回表（覆盖索引），性能最佳。

---

3.2 执行计划 Explain 分析

定义

EXPLAIN 显示 MySQL 优化器如何执行 SQL 语句，是调优的核心工具。

关键字段解读

字段	含义	关注点
type	访问类型	ALL（全表扫描）→ index → range → ref → eq_ref → const（越左越差）
key	实际使用的索引	NULL 表示未使用索引
rows	预估扫描行数	越小越好
Extra	附加信息	Using filesort（需优化） / Using temporary（需优化） / Using index（覆盖索引，好！）

语法与示例

-- 全表扫描（需优化）
EXPLAIN SELECT * FROM employee WHERE name LIKE '%张%';

-- 范围扫描
EXPLAIN SELECT * FROM employee WHERE age BETWEEN 20 AND 30;

-- 等值引用（使用索引）
EXPLAIN SELECT * FROM employee WHERE name = '张三';

-- 联表分析
EXPLAIN SELECT e.*, d.name
FROM employee e
LEFT JOIN department d ON e.department = d.id
WHERE e.salary > 5000;

-- 分析分组排序
EXPLAIN SELECT department, AVG(salary)
FROM employee
GROUP BY department
ORDER BY NULL;

Extra 常见值与优化建议：

Extra 值	含义	建议
Using filesort	文件排序（非内存排序）	添加适当索引消除排序
Using temporary	使用临时表	优化 GROUP BY / DISTINCT
Using index	覆盖索引	✅ 最佳状态
Using where	索引后过滤	检查是否能索引下推
Using index condition	索引条件下推（ICP）	✅ MySQL 5.6+ 自动优化

---

3.3 慢查询定位与 SQL 优化

定义

慢查询 指执行时间超过预设阈值（默认 10 秒）的 SQL 语句。启用慢查询日志可精准定位性能瓶颈。

慢查询配置

-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询日志（session 级别，生产环境需配 my.cnf）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;       -- 超过 2 秒记录
SET GLOBAL log_queries_not_using_indexes = ON;

-- 查询慢查询日志位置
SHOW VARIABLES LIKE 'slow_query_log_file';

SQL 优化通用原则

-- ❌ 低效：SELECT * 不必要
SELECT * FROM employee WHERE name = '张三';

-- ✅ 高效：只取需要的列
SELECT id, name, salary FROM employee WHERE name = '张三';

-- ❌ 低效：函数作用于索引列
SELECT * FROM employee WHERE YEAR(hire_date) = 2024;

-- ✅ 高效：使用范围查询
SELECT * FROM employee WHERE hire_date >= '2024-01-01' AND hire_date < '2025-01-01';

-- ✅ 推荐：分页优化（延迟关联）
-- 传统分页（OFFSET 大时性能差）
SELECT * FROM employee ORDER BY id LIMIT 100000, 20;

-- 优化：先查主键再 JOIN
SELECT e.*
FROM employee e
INNER JOIN (
    SELECT id FROM employee ORDER BY id LIMIT 100000, 20
) tmp ON e.id = tmp.id;

---

3.4 索引失效场景与避坑指南

常见失效场景

场景	原因	示例
1. 最左前缀缺失	联合索引未从最左列开始	索引 (a,b,c)，查询只用到 b 或 c
2. 隐式类型转换	列类型为 VARCHAR，传入数字	WHERE phone = 13800000000
3. 索引列参与运算	列上使用函数或表达式	WHERE age + 1 = 20
4. 使用 LIKE '%xxx'	前模糊匹配无法走索引	WHERE name LIKE '%张'
5. OR 条件部分无索引	OR 两边只要一边无索引则全表扫	WHERE name = '张' OR age = 20
6. NOT / <> / NOT IN	负向查询通常不走索引	WHERE age <> 20
7. 数据分布不均匀	优化器认为全表扫更快（如性别字段）	WHERE gender = '男'（占比 50%）

语法与示例

-- 准备联合索引 (department, salary, age)
CREATE INDEX idx_dept_sal_age ON employee(department, salary, age);

-- ✅ 走索引：全匹配
EXPLAIN SELECT * FROM employee
WHERE department = '技术部' AND salary > 10000 AND age = 25;

-- ❌ 不走索引：跳过 department
EXPLAIN SELECT * FROM employee
WHERE salary > 10000 AND age = 25;

-- ❌ 不走索引：隐式类型转换
-- 假设 department 是 VARCHAR
EXPLAIN SELECT * FROM employee WHERE department = 123;

-- ✅ 走索引：正确写法
EXPLAIN SELECT * FROM employee WHERE department = '123';

-- ❌ 不走索引：LIKE 前模糊
EXPLAIN SELECT * FROM employee WHERE name LIKE '%张';

-- ✅ 不走索引但可优化为：LIKE 后模糊（走索引）
EXPLAIN SELECT * FROM employee WHERE name LIKE '张%';

-- ❌ OR 陷阱
EXPLAIN SELECT * FROM employee
WHERE department = '技术部' OR age = 25;

-- ✅ 优化为 UNION
EXPLAIN SELECT * FROM employee WHERE department = '技术部'
UNION
SELECT * FROM employee WHERE age = 25;

---

3.5 分库分表策略入门

定义

当单库或单表数据量过大（通常单表超过 500 万 ~ 1000 万行），或者 QPS 超过数据库承载能力时，需考虑分库分表。

策略	说明	适用场景
垂直分库	按业务拆分为不同数据库（订单库 / 用户库 / 商品库）	业务模块松耦合
垂直分表	大表拆分宽表为窄表（把不常用/大字段拆出去）	表列数多，行长度大
水平分库	按分片键（如 user_id % 4）将数据分散到多个库	单库连接数瓶颈
水平分表	同一库内按规则拆分多张结构相同的表	单表数据量过大

示例

-- ======== 水平分表示例 ========

-- 建表模板：按用户 ID 分 4 张表
-- user_0, user_1, user_2, user_3

CREATE TABLE user_0 (
    id BIGINT PRIMARY KEY,
    name VARCHAR(50),
    created_at DATETIME
) ENGINE=InnoDB;

-- 分片逻辑（应用层或中间件）
-- 分片键 = user_id % 4

-- ======== 常见分库分表中间件 ========
-- ShardingSphere-JDBC：轻量级，嵌入应用
-- MyCat：独立代理层
-- 云原生：TiDB / PolarDB 自动水平扩展（对应用透明）

避坑提醒：分库分表引入跨分片查询（如全局排序、跨库 JOIN）和分布式事务复杂度，优先考虑索引优化 → 读写分离 → 缓存 → 分库分表的渐进路线。

---

三、本章小结

知识点	关键要点
B+ 树索引	非叶子存键、叶子存数据+双向链表，2-4 层高度
聚簇 vs 二级	聚簇存整行（主键），二级存主键值（需回表）
EXPLAIN	关注 type（ALL→const）、key、rows、Extra（Using filesort 需警惕）
慢查询	long_query_time=2 记录超过 2 秒的 SQL，定期分析
索引失效	最左前缀、隐式转换、前模糊 LIKE、OR 陷阱是常见坑
分库分表	垂直/水平 分库/分表，优先用中间件或云原生方案

---

四、练习

基础练习

1. 为 employee 表创建一个联合索引 (department, hire_date)，并使用 EXPLAIN 分析以下查询是否走索引：
   • WHERE department = '技术部'
   • WHERE hire_date = '2024-01-01'
   • WHERE department = '技术部' AND hire_date > '2023-01-01'
2. 开启慢查询日志，执行一个 SLEEP(3) 的查询，观察日志输出
3. 编写一条 LIKE 前模糊查询并分析其 type，然后改写为后模糊或全文索引

进阶练习

4. 从 information_schema 查询当前数据库中占用空间最大的 5 张表：

   SELECT table_name, ROUND((data_length+index_length)/1024/1024,2) AS size_mb
   FROM information_schema.tables
   WHERE table_schema = 'your_db'
   ORDER BY size_mb DESC
   LIMIT 5;

5. 设计一个水平分表方案：用户表 user 按 user_id 分为 8 张子表，计算分片规则并写出数据路由伪代码
6. 分析一条生产环境慢查询（由讲师提供），给出索引优化方案并对比优化前后的 EXPLAIN 输出