一、MySQL 基础篇
1. MySQL 的架构组成
MySQL 架构通常分为三层，从上到下依次是连接层、服务层和存储引擎层。
连接层主要负责处理客户端的连接请求，包括身份认证（验证用户名密码）以及管理连接池和维护线程。当应用程序发起连接时，首先经过这一层。
服务层是核心层，包含查询缓存、解析器、优化器和执行器等组件。虽然 MySQL 8.0 已移除查询缓存（因命中率低），但其他组件依然关键：
- 解析器：进行词法和语法分析，生成解析树。
- 优化器：选择最优执行计划，比如决定使用哪个索引。
- 执行器：调用存储引擎接口执行 SQL。
存储引擎层负责数据的存储和读取，支持插件式扩展，常见的有 InnoDB 和 MyISAM。
一条 SQL 的执行流程大致为：客户端发送 SQL → 连接器验证 → 解析器解析 → 优化器优化 → 执行器执行 → 存储引擎读写数据。
2. InnoDB 与 MyISAM 的区别
这是最常用的两个存储引擎，主要区别体现在以下几个方面：
- 事务支持：InnoDB 支持 ACID 特性，可 COMMIT/ROLLBACK；MyISAM 不支持事务，出错无法回滚。
- 锁粒度：InnoDB 支持行级锁，并发性能更好；MyISAM 仅支持表级锁，写操作会锁住整表。
- 外键约束：InnoDB 支持外键，保证引用完整性；MyISAM 不支持。
- 索引结构：InnoDB 使用聚簇索引，数据文件与索引绑定，主键叶子节点存数据；MyISAM 是非聚簇索引，数据与索引分离，叶子节点存地址。
- COUNT(*) 性能：MyISAM 保存了总行数，直接返回；InnoDB 需全表扫描统计。
- 崩溃恢复：InnoDB 有 redo log 和 undo log，支持恢复；MyISAM 无此机制，易丢失数据。
目前推荐默认使用 InnoDB（5.5+ 版本起即为默认），除非是只读且无需事务的场景。
3. 聚簇索引与非聚簇索引
聚簇索引（Clustered Index） 的特点是索引与数据存储在一起。InnoDB 的主键索引即为聚簇索引，B+Tree 叶子节点直接存储完整数据行。可以理解为按拼音排序的字典，找到拼音就找到了内容。
非聚簇索引（二级索引） 的叶子节点存储的是主键值。例如在 username 字段建普通索引，查询时需先在索引树中找到主键 ID，再回到聚簇索引中查找完整数据，这个过程叫回表。
回表有性能损耗，因此实际开发中常使用覆盖索引来避免。如果查询字段都在索引中，就不需要回表。
-- 假设有联合索引 idx_name_age(name, age)
SELECT name, age FROM user WHERE name = '张三';
-- 此查询用到覆盖索引，无需回表
InnoDB 要求表必须有主键。若未显式定义，它会优先找非空唯一索引，若无则自动生成 6 字节隐藏主键 ROW_ID。建议始终显式定义自增主键，顺序插入效率更高。
二、索引篇
1. 为什么选择 B+Tree？
相比 Hash 和 B-Tree，B+Tree 更适合数据库场景：
不用 Hash 的原因：
- 不支持范围查询（如
WHERE age > 20）。 - 不支持排序。
- 不支持最左前缀匹配。
- 存在 Hash 冲突风险。
B+Tree 优势：
- 树高更低：非叶子节点不存数据，单页能存更多 Key，减少磁盘 IO。
- 范围查询高效：叶子节点通过双向链表连接，遍历方便。
- 查询稳定：所有查询都走到叶子节点，路径长度一致。
假设节点存 1000 个 Key，3 层 B+Tree 可存 10 亿条数据，仅需 3 次磁盘 IO。
2. 最左前缀原则
联合索引必须从最左边开始匹配，不能跳过中间列。例如索引 idx_a_b_c(a, b, c)：
-- 有效
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
-- 部分有效（c 用不上）
WHERE a = 1 AND c = 3
-- 无效
WHERE b = 2
WHERE c = 3
注意：查询条件顺序不影响索引使用（优化器会自动调整），但范围查询会导致后续列失效。
WHERE a = 1 AND b > 5 AND c = 3
-- a 走索引，b 走索引（范围），c 失效
3. 索引失效场景
常见导致索引失效的情况包括：
- 违反最左前缀：联合索引缺少最左列。
- 索引列运算或函数：如
LEFT(name, 3)='张'或YEAR(create_time)=2024。应改为范围查询或前缀匹配。 - 隐式类型转换：字符串字段传数字参数会导致索引失效。
- LIKE 以 % 开头：
LIKE '%张'无法利用索引。 - OR 条件含非索引列：整体不走索引，可用 UNION 替代。
- 否定查询：
NOT IN,!=,<>可能触发全表扫描。 - NULL 值判断：视数据分布而定，优化器可能放弃索引。
总结：索引列要保持'干净'，避免加工。
三、事务篇
1. ACID 特性及实现
- 原子性（Atomicity）：不可分割，要么全成功要么全失败。通过 undo log 实现回滚。
- 一致性（Consistency）：事务前后状态一致，由其他三个特性共同保证。
- 隔离性（Isolation）：并发互不干扰。通过 锁 和 MVCC 实现。
- 持久性（Durability）：提交后永久生效。通过 redo log 刷盘实现。
日志区别：redo log 是物理日志，用于崩溃恢复；undo log 是逻辑日志，用于回滚和 MVCC。
2. 事务隔离级别
MySQL 提供四种隔离级别：
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| READ UNCOMMITTED | 可能 | 可能 | 可能 |
| READ COMMITTED | 不会 | 可能 | 可能 |
| REPEATABLE READ | 不会 | 不会 | 可能（InnoDB 基本解决） |
| SERIALIZABLE | 不会 | 不会 | 不会 |
- RC (Read Committed)：Oracle 默认，解决脏读，存在不可重复读。
- RR (Repeatable Read)：MySQL 默认，解决脏读和不可重复读，InnoDB 通过 Next-Key Lock 缓解幻读。
3. MVCC 原理
多版本并发控制（MVCC）通过保存历史版本让读写不冲突。
核心组件：
- 隐藏字段：每行数据含
DB_TRX_ID（修改事务 ID）、DB_ROLL_PTR（回滚指针）、DB_ROW_ID（隐藏主键）。 - Undo Log：形成版本链，记录旧数据。
- Read View：快照读时生成，包含活跃事务列表等，用于判断可见性。
可见性规则：对比事务 ID 与 Read View 中的最小/最大 ID 及活跃列表。
生成时机：RC 级别每次 SELECT 生成新视图；RR 级别首次 SELECT 生成并复用，保证了可重复读。
四、锁篇
1. 锁的类型
- 全局锁：锁整个实例，用于备份。
- 表级锁：开销小并发低，包括表锁、MDL、意向锁。
- 行级锁：InnoDB 支持，并发度高。
模式分类：
- 共享锁（S 锁）：读锁，允许并发读。
- 排他锁（X 锁）：写锁，独占资源。
InnoDB 行锁细分：
- Record Lock：锁住单条记录。
- Gap Lock：锁住间隙，防幻读。
- Next-Key Lock：Record + Gap，RR 级别默认算法。
2. 死锁处理
死锁是多个事务互相等待对方锁导致的阻塞。
检测策略：
- 超时：
innodb_lock_wait_timeout，默认 50 秒。 - 死锁检测：
innodb_deadlock_detect=ON，自动回滚代价小的事务。
避免方法：
- 固定顺序访问资源。
- 减小锁粒度和持有时间。
- 合理使用索引，避免全表扫描锁行。
- 降低隔离级别或使用乐观锁。
排查命令：
SHOW ENGINE INNODB STATUS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
3. 乐观锁 vs 悲观锁
- 悲观锁：认为总有冲突，先加锁再操作（如
SELECT ... FOR UPDATE）。适合写多读少，安全性高但性能开销大。 - 乐观锁：假设冲突少，更新时检查版本号（CAS 思想）。适合读多写少，性能好但需重试机制。
示例：
UPDATE product SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 10;
五、SQL 优化篇
1. 慢 SQL 定位与优化
步骤：
- 开启慢查询日志：设置
long_query_time阈值。 - EXPLAIN 分析：关注
type（访问类型）、key（使用索引）、rows（扫描行数）、Extra（额外信息）。- 目标：避免 ALL（全表扫描），争取 range 或 ref。
- Extra 中出现 Using filesort 或 temporary 需优化。
- 针对性优化：
- 添加合适索引（联合索引注意顺序）。
- 避免
SELECT *和索引列上的函数。 - 深分页优化（延迟关联或游标分页）。
- 批量操作代替循环单条插入。
2. 实战案例
某订单查询接口在 500 万数据下响应超 5 秒。
原始 SQL：
SELECT * FROM order
WHERE user_id = 12345 AND status IN (1,2,3)
AND create_time >= '2024-01-01'
ORDER BY create_time DESC LIMIT 0, 20;
优化方案：
- 加索引：创建
idx_user_status_time(user_id, status, create_time)，将 type 从 ALL 变为 range。 - 字段精简：只查必要字段，避免回表。
- 分页优化：使用游标分页（
WHERE id < last_id）替代深度 offset。
效果：耗时从 5 秒降至 50 毫秒内。
六、其他高频问题
1. 主从复制原理
基于 binlog 的三步流程：
- Master 写入 binlog。
- Slave IO 线程读取 binlog 写入 relay log。
- Slave SQL 线程重放 relay log。
binlog 格式：
- STATEMENT：记录 SQL，省空间但不保证一致性。
- ROW：记录行变化，一致性好但日志大。
- MIXED：混合模式。
推荐使用 ROW 格式。主从延迟可通过并行复制、硬件升级或避免大事务缓解。
2. 缓存一致性
Cache Aside Pattern（旁路缓存）：
- 读：先读缓存，未命中读库并回写。
- 写：先更新 DB，再删除缓存。
为何删缓存而非更？ 避免并发更新导致旧数据覆盖新数据。 为何先更 DB 再删？ 防止先删缓存期间读请求加载旧数据入缓存。
极端不一致可通过延迟双删或 Canal 监听 binlog 异步更新解决。
3. 索引设计原则
- 选对字段：高频查询、排序、JOIN 条件、高区分度字段。
- 联合索引：最左前缀，区分度高的在前，范围查询放最后。
- 控制数量：一般不超过 5 个，过多影响写入。
- 主键选择：推荐自增 ID 或雪花算法，避免 UUID 导致页分裂。
- 去冗余：去除被覆盖的索引（如
(a,b)存在时(a)冗余）。 - 前缀索引：长字符串字段可只索引前 N 位。
实践上，依据业务查询设计，定期 EXPLAIN 检查，结合慢日志调优。
