MySQL 这块内容，很多同学是“面试前突击一下索引、背两道 SQL 优化题”，真到线上慢查询、死锁、索引失效的时候又不知道怎么排查。这次我们来看一套覆盖 MySQL 核心知识体系的内容整理，从 B+树、联合索引、索引下推，到 SQL 优化、慢查询分析、MySQL 调优实战，再到高频面试题，一次讲透。
这不是零散的知识点罗列，而是按照“底层原理 -> 索引设计 -> SQL 优化 -> 实例调优 -> 面试输出”这条链路来组织的。你既可以把它当成系统学习 MySQL 的路线图，也可以直接拿里面的 SQL 在本地环境里做验证。本文会把这条学习/复习路径拆开，把每个阶段要掌握的知识点、可以实操的 SQL、以及常见坑位都写清楚。
适合的读者有三类：后端开发想系统补 MySQL 底层知识的、准备跳槽要刷 MySQL 面试题的、以及已经在负责线上数据库但面对慢 SQL 和参数调优没有头绪的同学。文中的所有 SQL 和配置示例，建议你在自己的 MySQL 实例上先跑一遍，不要只看不练。
1. 核心能力速览
先把这套内容包含的知识模块和能力方向列出来，方便按需查阅。
| 能力模块 | 覆盖内容 | 应用场景 |
|---|---|---|
| B+树原理 | 索引底层存储结构、为什么选 B+树、主键索引与二级索引 | 理解索引失效、回表、覆盖索引 |
| 联合索引 | 最左前缀原则、索引下推 ICP、索引排序 | 联合索引设计、慢查询优化 |
| 索引优化 | 前缀索引、覆盖索引、索引选择、冗余索引清理 | 表结构设计、查询加速 |
| SQL 优化 | 慢查询日志、EXPLAIN、索引失效场景、分页/排序/Join 优化 | 线上慢 SQL 治理 |
| MySQL 调优 | 锁等待、事务隔离级别、参数调优、临时表优化 | 数据库实例性能调优 |
| 高频面试题 | 索引、事务、锁、MVCC、调优类问答 | 面试准备、知识复盘 |
这套内容的最大特点就是“原理和实战都不缺”：B+树讲的是为什么，SQL 优化讲的是怎么做，调优实战讲的是在什么场景下做。单独学某一块也能用，但串起来之后理解深度会明显不一样。
2. 适用场景与学习边界
先说清楚这套 MySQL 知识体系适合哪些场景，不适合哪些场景。
适合的工作场景：
- 日常开发中写 SQL 经常遇到慢查询，需要定位问题并优化。
- 负责的项目里表数据量到了百万、千万级，需要设计合理索引。
- 面试前需要系统复盘 MySQL 的索引、事务、锁、MVCC 知识点。
- 线上数据库出现锁等待、CPU 飙高、磁盘 IO 异常，需要排查思路。
不适合的场景：
- 想学 MySQL 运维架构（主从复制、分库分表、集群部署），这套内容不是主线。
- 想深入学习 InnoDB 源码级实现，这套内容偏应用和面试向。
- 想找“一键优化所有慢 SQL”的工具，MySQL 没有银弹，这套内容也强调的是分析思路。
另外要强调合规边界：本地环境建议使用测试库，不要在生产环境直接执行无 WHERE 条件的 UPDATE/DELETE，不要随意加索引或改全局参数。如果你负责的是线上数据库，任何参数变更都要走变更评审流程，先在测试环境验证。涉及数据导出、脱敏、权限审计的部分，要遵守公司数据库安全规范和隐私合规要求。
3. 环境准备与 MySQL 安装部署
学习这套内容建议自己搭一个 MySQL 环境，方便执行 EXPLAIN 和慢查询分析。推荐使用 MySQL 8.0，理由有几点：8.0 支持窗口函数、CTE，默认字符集 utf8mb4，对索引下推、不可见索引、直方图等特性支持更好。如果你还在用 MySQL 5.7，索引和事务部分的知识也基本通用，但部分语法和默认行为有差异。
安装 MySQL 有几种方式，这里给两条常用路径。
3.1 Windows 本地安装 MySQL
Windows 下推荐直接下载 MySQL Installer，选择 Server only 即可。安装过程中会要求设置 root 密码，并且可以选择配置为 Windows 服务，方便开机自启。
安装完成后，使用命令行登录：
# 本机登录 MySQL mysql -uroot -p需要注意：如果安装时选择了“MySQL 8.0 Command Line Client”，它会自动登录默认实例；如果要用自定义端口或 socket，建议配置 PATH 后用命令行连接。
3.2 Docker 安装 MySQL
如果你不想污染本机环境，Docker 是更干净的方式。先确认 Docker 已安装，然后执行：
# 拉取 MySQL 8.0 镜像并启动容器 docker pull mysql:8.0 docker run -d \ --name mysql-study \ -p 3306:3306 \ -e MYSQL_ROOT_PASSWORD=root123456 \ -e TZ=Asia/Shanghai \ mysql:8.0启动后验证连接：
docker exec -it mysql-study mysql -uroot -p这里给一个简单的初始化 SQL 脚本，用来创建一个测试库和测试表：
CREATE DATABASE IF NOT EXISTS db_index DEFAULT CHARACTER SET utf8mb4; USE db_index; DROP TABLE IF EXISTS t_user; CREATE TABLE t_user ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_name VARCHAR(50) NOT NULL, phone VARCHAR(20), age INT, city VARCHAR(50), create_time DATETIME DEFAULT CURRENT_TIMESTAMP, KEY idx_phone (phone), KEY idx_city_age (city, age), KEY idx_create_time (create_time) ) ENGINE=InnoDB;测试表建好后，可以插入一些模拟数据，用于后面的索引验证和 EXPLAIN 分析。
4. MySQL 索引基础：B+树与索引存储结构
索引这一块的核心问题有三个：
- MySQL 为什么用 B+树存储索引？
- 主键索引和二级索引有什么区别？
- 什么是回表，回表为什么会影响查询性能？
4.1 为什么是 B+树
先对比一下几种常见数据结构：
| 数据结构 | 查询时间复杂度 | 是否适合磁盘存储 | 说明 |
|---|---|---|---|
| 哈希表 | O(1) | 不适合范围查询 | 等值查询快，但无法区间扫描 |
| 二叉搜索树 | O(log n) | 树高较高 | 数据量大了树太高，磁盘 IO 多 |
| B 树 | O(log n) | 每个节点存数据 | 非叶子节点也存数据，单页能存的键少 |
| B+树 | O(log n) | 数据只在叶子节点 | 叶子节点形成链表，适合范围查询 |
B+树的几个关键设计：
- 非叶子节点只存索引键，不存数据，所以同样大小的页可以容纳更多键，树更矮。
- 叶子节点用双向链表连接，适合范围查询和排序。
- 数据按主键顺序存储在叶子节点，所以主键有序插入性能好。
- 所有查询最终都要走到叶子节点，查询效率稳定。
这也是为什么 InnoDB 主键建议使用自增整数，而不是 UUID 字符串。UUID 主键无序，会导致 B+树频繁页分裂，写入性能下降，同时索引占用空间更大。
4.2 主键索引与二级索引
InnoDB 的索引组织方式叫聚簇索引，数据行存在主键索引的叶子节点上。
| 索引类型 | 叶子节点内容 | 说明 |
|---|---|---|
| 主键索引（聚簇索引） | 整行数据 | 表数据按主键顺序存储 |
| 二级索引（普通索引） | 索引列值 + 主键值 | 查到主键后还要回表 |
假设执行这条 SQL：
SELECT * FROM t_user WHERE phone = '13800138000';如果 phone 上建了二级索引，执行过程是先通过 idx_phone 找到主键 id，再通过主键索引回表查到完整行数据。这个过程就是“回表”。
如果查询只需要 phone 和 id，那就不需要回表，因为二级索引的叶子节点已经包含这两个字段，这叫“覆盖索引”。覆盖索引是 SQL 优化里非常重要的一种手段。
4.3 验证 B+树索引的查询行为
本地测试时，通过 EXPLAIN 可以看到索引使用情况：
-- phone 上有普通索引 idx_phone EXPLAIN SELECT * FROM t_user WHERE phone = '13800138000';关注 key 字段：如果显示 idx_phone，说明走了索引；如果显示 NULL，说明全表扫描。再看 Extra 字段：如果出现 Using index condition，可能是索引下推；如果出现 Using where，注意是什么条件导致过滤。
5. 联合索引、索引下推与覆盖索引
联合索引是工作中设计索引最多的一块，面试也常考。
5.1 最左前缀原则
联合索引 (city, age) 在 B+树中的排序规则是：先按 city 排序，city 相同再按 age 排序。所以查询条件只有同时满足“包含最左列”或者“从最左列开始的连续前缀”时，才能用到索引。
这里有一组典型验证 SQL：
-- 走索引：city 是最左列 EXPLAIN SELECT * FROM t_user WHERE city = '上海'; -- 走索引：city + age 是完整前缀 EXPLAIN SELECT * FROM t_user WHERE city = '上海' AND age = 25; -- 不一定走索引：条件只给了 age，没有 city EXPLAIN SELECT * FROM t_user WHERE age = 25; -- 注意：city 等值 + age 范围时，range 之后的列无法用于排序和过滤 EXPLAIN SELECT * FROM t_user WHERE city = '上海' AND age > 20;设计联合索引时，常见排序规则：区分度高的列放前面、查询频率高的列放前面。但如果查询中有一个经常做范围查询的列，把它放在联合索引的最后一位通常更合适，否则范围后面的列无法继续走索引。
5.2 索引下推 ICP
索引下推是 MySQL 5.6 引入的优化。简单理解：在没有 ICP 之前，联合索引只能根据索引前缀条件把记录从存储引擎捞出来，回表后再用其他条件过滤；有了 ICP 之后，可以在索引遍历过程中直接过滤掉不满足条件的记录，减少回表次数。
在 8.0 里 ICP 默认开启，可以通过 EXPLAIN 的 Extra 列看到 Using index condition。
验证方法：
-- 联合索引 idx_city_age(city, age) -- 没有 ICP 时，条件 age = 25 在回表后再过滤 -- 有 ICP 时，age 条件直接在索引层过滤 EXPLAIN SELECT * FROM t_user WHERE city = '上海' AND age = 25;索引下推对联合索引中“非最左列”的等值过滤效果明显，尤其是表行数大、回表代价高的时候。
5.3 覆盖索引
覆盖索引不是一种独立索引类型，而是“查询所需的字段都包含在索引树中”的状态。
举例：
-- idx_city_age(city, age) 覆盖了 city 和 age 两个字段 EXPLAIN SELECT city, age FROM t_user WHERE city = '上海';如果发现 Extra 里有 Using index，说明走了覆盖索引，不需要回表。覆盖索引对高频查询的收益很直接，但不要为了覆盖索引把所有字段都塞进索引，索引列越多，写入和维护成本越高。
5.4 前缀索引
对于长字符串列，比如 user_name 或 url，可以建前缀索引：
-- 对 user_name 前 10 个字符建索引 ALTER TABLE t_user ADD KEY idx_user_name_prefix (user_name(10)); -- 验证区分度 SELECT COUNT(DISTINCT LEFT(user_name, 10)) / COUNT(*) AS selectivity FROM t_user;前缀索引能明显减少索引空间，但排序无法使用前缀索引，也不能用于覆盖索引。
6. SQL 优化实战：慢查询与执行计划
SQL 优化是这套内容里最实用的一块。这里给出完整的排查链路：先打开慢查询日志，拿到问题 SQL，再用 EXPLAIN 分析执行计划，最后针对索引失效场景做修改。
6.1 开启慢查询日志
MySQL 8.0 可以通过以下命令临时开启：
-- 查看当前慢查询配置 SHOW VARIABLES LIKE 'slow_query_log%'; SHOW VARIABLES LIKE 'long_query_time'; -- 临时开启慢查询日志 SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; SET GLOBAL log_queries_not_using_indexes = 'ON';生产环境建议把 long_query_time 设置到一个合理阈值，比如 1 秒或 2 秒，避免日志量过大。临时配置重启后失效，要持久化得写入 my.cnf。
6.2 使用 EXPLAIN 分析执行计划
EXPLAIN 是定位慢 SQL 的核心工具：
EXPLAIN SELECT u.id, u.user_name, o.order_no FROM t_user u LEFT JOIN t_order o ON u.id = o.user_id WHERE u.city = '上海' ORDER BY u.create_time DESC;关键列解读如下：
| 列名 | 关注点 |
|---|---|
| type | ALL 全表扫描、range 范围扫描、ref 普通索引等值、const 主键等值 |
| possible_keys | 可能使用的索引 |
| key | 实际使用的索引 |
| rows | 预估扫描行数，越小越好 |
| Extra | Using index、Using where、Using index condition、Using temporary、Using filesort |
看见 Using filesort 时，优先看 ORDER BY 字段是否有索引支持；看见 Using temporary 时，大概率是 GROUP BY 或 DISTINCT 触发了临时表。
6.3 典型索引失效场景
这是面试和实战的高频区。整理几个最常见的索引失效情况：
| 失效场景 | 示例 | 解决方案 |
|---|---|---|
| 对索引列使用函数 | WHERE DATE(create_time) = '2026-01-01' | 改为 create_time >= '2026-01-01' AND create_time < '2026-01-02' |
| 隐式类型转换 | phone 是 varchar，WHERE phone = 13800138000 | 改为字符串类型 '13800138000' |
| 前导模糊查询 | WHERE user_name LIKE '%张%' | 改用覆盖索引或全文索引 |
| OR 连接非索引列 | WHERE city = '上海' OR age = 25 | 拆分为两个查询用 UNION ALL 合并 |
| 联合索引不满足最左前缀 | 索引 (city, age)，条件只有 age | 调整索引顺序或增加索引 |
| 对索引列进行计算 | WHERE age + 1 = 26 | 改写为 WHERE age = 25 |
值得注意其中一条：FIND_IN_SET这类函数即使字段有索引，通常也不会走索引，因为针对字段做了函数转换。如果状态类字段用逗号分隔存储，更合理的做法是拆成关联表，而不是依赖 FIND_IN_SET 查询。
6.4 分页查询优化
大分页是慢 SQL 重灾区。深分页的慢主要来自 LIMIT offset 太大时，MySQL 需要扫描并丢弃大量行。
优化前：
-- 扫描 100000 行后取 10 行 SELECT * FROM t_order ORDER BY id LIMIT 100000, 10;优化思路一：延迟关联，先查主键再回表。
SELECT t.* FROM t_order t INNER JOIN ( SELECT id FROM t_order ORDER BY id LIMIT 100000, 10 ) tmp ON t.id = tmp.id;优化思路二：记录上次查询的最大 id，用条件分页，适合按顺序翻页的场景。
SELECT * FROM t_order WHERE id > 100000 ORDER BY id LIMIT 10;这种方案对顺序滚动翻页有效，不适合随机跳页。
6.5 Join 与排序优化
Join 优化的核心是小表驱动大表、关联字段有索引、避免笛卡尔积。另一个要注意的是排序字段如果来自多张表，大概率走不了索引，会触发 filesort。这时候能用联合索引覆盖排序字段最好，否则考虑把排序放在应用层处理，或者在查询中减少参与排序的行数。
6.6 COUNT 查询优化
COUNT(*)在 InnoDB 中需要实时扫描，因为 MVCC 机制下无法像 MyISAM 一样直接读行数。优化思路：
- 数据量极大且对实时性要求不高时，用单独的计数表维护。
- 用 EXPLAIN 看 rows 估算值，避免在大表上频繁 COUNT。
- 有条件过滤时，确保 WHERE 条件能走索引，减少扫描行数。
7. MySQL 调优实战：参数、锁与事务
SQL 写完没问题，不代表数据库一定稳。调优篇主要关注参数配置、锁竞争、事务隔离级别这几个维度。
7.1 InnoDB 核心参数
最常调整的参数是 InnoDB 缓冲池大小：
# my.cnf 示例 [mysqld] # 缓冲池大小，建议设为物理内存的 60%-80% innodb_buffer_pool_size = 4G # 日志文件大小 innodb_log_file_size = 512M # 隔离级别，默认 REPEATABLE-READ transaction-isolation = READ-COMMITTED # 连接数上限 max_connections = 500需要说明：缓冲池设置过大可能影响操作系统层面的文件缓存；事务隔离级别从 REPEATABLE-READ 改为 READ-COMMITTED 会改变行为，必须经过业务评估，尤其是依赖 RR 可重复读来保证事务内多次读一致的场景。
7.2 锁等待与死锁排查
锁问题常见的报错是Lock wait timeout exceeded和Deadlock found。排查思路：
-- 查看当前锁等待和事务状态 SHOW ENGINE INNODB STATUS; -- 查看当前执行的线程 SHOW PROCESSLIST; -- 查看正在等待锁的事务表（8.0） SELECT * FROM performance_schema.data_lock_waits;减少锁竞争的建议：
- 事务中尽量只操作必要的行，缩小事务范围。
- 避免在事务中请求外部接口或做耗时操作。
- 更新语句尽量走主键或唯一索引定位行，避免间隙锁范围扩大。
- 多个事务更新多行时，保持相同的加锁顺序。
- 大批量 UPDATE/DELETE 拆成小批量执行，避免一次锁大量行。
7.3 临时表与文件排序
当 EXPLAIN 出现 Using temporary 或 Using filesort 时，常见于 GROUP BY、ORDER BY、DISTINCT 操作。可以通过 tmp_table_size、max_heap_table_size 控制内存临时表大小；如果临时表超过阈值，会落到磁盘，性能会明显下降。这属于参数层优化，但根本上还是要通过索引避免排序。
7.4 数据库开启审计引起索引争用
部分团队会在数据库层面开启审计功能，但审计如果记录粒度过细，比如每一条 SQL 都写审计日志，就会导致对共享资源或系统表的竞争加剧，甚至影响已有索引的正常使用。线上排查时如果出现“历史正常 SQL 突然变慢且伴随资源等待”，可以先确认最近有没有开启审计、慢查询日志、通用日志等额外开销。审计日志建议在独立实例或独立存储上汇总分析，避免审计过程影响主库性能。
8. MySQL 高频面试题整理
这套内容对照面试题来复盘，效果会更好。我把高频问题按主题整理成表格。
| 主题 | 高频问题 | 回答要点 |
|---|---|---|
| 索引 | 为什么 MySQL 用 B+树不用哈希表 | 范围查询、排序、稳定 IO |
| 索引 | 主键索引和二级索引的区别 | 叶子节点内容不同，回表 |
| 索引 | 什么是覆盖索引 | 查询字段都在索引中，避免回表 |
| 索引 | 什么是索引下推 | 索引遍历阶段过滤，减少回表 |
| 索引 | 联合索引最左前缀原则是什么 | 索引排序规则决定 |
| 索引 | 什么情况下索引会失效 | 函数、隐式转换、前导模糊、OR 等 |
| 索引 | 前缀索引怎么用 | 兼顾区分度和空间 |
| 事务 | 事务隔离级别有哪些 | RU、RC、RR、Serializable |
| 事务 | MVCC 实现原理 | 隐藏列、undo log、read view |
| 锁 | 什么是间隙锁 | RR 级别下防止幻读 |
| 锁 | 死锁怎么排查 | SHOW ENGINE INNODB STATUS |
| 优化 | 一条慢 SQL 怎么排查 | 慢查询日志、EXPLAIN、索引、改写 |
| 优化 | 深分页怎么优化 | 延迟关联、条件分页 |
| 优化 | 大表怎么加索引 | 在线 DDL、低峰期、分批执行 |
| 优化 | COUNT 慢怎么处理 | 计数表、减少扫描 |
面试层面，除了背结论，建议准备一两个自己做过的真实案例。比如一条慢 SQL 从 5 秒优化到 50 毫秒，过程是什么、用了什么工具、改了什么。这类问题比单纯背八股更能体现调优能力。
9. 常见问题与排查方法
| 问题现象 | 可能原因 | 排查方式 | 解决方案 |
|---|---|---|---|
| SQL 走了索引还是慢 | 回表次数多、扫描行数大、排序临时表 | EXPLAIN 看 rows 和 Extra | 优化为覆盖索引，减少回表 |
| EXPLAIN 显示全表扫描 | 索引失效或没有合适索引 | 检查 WHERE 条件函数/类型转换 | 改写 SQL 或调整索引 |
| 修改表结构卡住 | 元数据锁，长事务阻塞 DDL | SHOW PROCESSLIST 查看阻塞来源 | 结束长事务，或低峰期执行 |
| Lock wait timeout exceeded | 行锁竞争 | SHOW ENGINE INNODB STATUS | 缩小事务，检查 update 条件 |
| 深分页特别慢 | OFFSET 过大 | EXPLAIN 观察扫描行数 | 延迟关联或条件分页 |
| ORDER BY 触发 filesort | 排序字段无索引或跨表排序 | EXPLAIN 查看 Extra | 联合索引覆盖排序字段 |
| 开启审计后索引争用 | 审计日志写竞争 | 检查系统表等待 | 独立审计实例，降低记录粒度 |
| Docker 启动 MySQL 报端口占用 | 宿主机 3306 被占用 | netstat -ano 检查端口 | 映射为新端口如 3307:3306 |
| root 登录失败 | 密码策略或权限问题 | 查看错误日志 | 重置密码或检查 host 配置 |
10. 最佳实践与调优建议
给几条工程化建议，来自实际维护 MySQL 的经验总结。
第一，索引不是越多越好。每个索引都要占用磁盘空间和维护成本，写入量大时索引会拖慢 INSERT/UPDATE。建索引前先想清楚这个表的核心查询是什么。建议保持一个原则：单表索引数量控制在 5 到 6 个以内，冗余索引定期清理。
第二，SQL 上线前强制 EXPLAIN。不管简单查询还是复杂 Join，先在测试环境跑一次 EXPLAIN，确认 type 不是 ALL、rows 在预期范围内、Extra 里没有不合理 Using temporary。这个习惯能拦住大量潜在慢 SQL。
第三，大表变更要分批。ALTER TABLE 加索引或字段，即使是 MySQL 8.0 支持在线 DDL，大数据量下也可能产生主从延迟和磁盘压力。建议选择低峰期，并监控主从延迟。
第四，数据规模变化后要重新看执行计划。一套 SQL 在 10 万行时跑得快，不代表 1000 万行时也快。数据增长后，原来的索引选择可能失效，要定期捞慢查询日志，集中治理 Top N 慢 SQL。
第五，权限和审计要分离。线上环境默认不要开启日志级别过高的通用日志和审计，避免影响性能。审计需求单独规划，不能单靠业务库承载。
第六，所有优化先备份、后操作、可回滚。改参数前记录原值，改 SQL 前保留原 SQL，执行批量操作前备份相关表数据。
11. 总结与下一步建议
这套 MySQL 内容真正值得花时间消化的地方，是把 B+树、联合索引、索引下推这些基础概念，和慢 SQL 治理、参数调优这些实战动作串在了一起。先理解索引为什么这么设计，再实际跑一遍 EXPLAIN，最后用自己的项目 SQL 做一次优化验证，这样学到的知识才不是“背过的八股”。
建议第一次学习时，先照本文第 3 节搭好本地 MySQL 环境，建一张测试表，把第 5 节、第 6 节里的 SQL 全部跑一遍。重点验证三个点：联合索引的最左前缀是否生效、查询条件加函数/隐式转换后索引是否失效、深分页优化前后执行计划的变化。
最容易踩的坑有两个：一个是看到 EXPLAIN 里走了索引就觉得优化完成，忽略了回表次数和扫描行数；另一个是参数调优时盲目调大 innodb_buffer_pool_size，没有结合内存和业务特点评估。
后续可以继续扩展的方向：事务隔离级别与 MVCC 底层实现、InnoDB 间隙锁与死锁案例复现、主从复制延迟排查、分库分表方案对比。把本文中的 SQL 和排查思路吃透，就足够应对大多数日常 MySQL 开发、调优和面试场景了。
