Java - MySQL 篇

2.1 慢查询定位与 EXPLAIN

如何定位慢查询

EXPLAIN 分析 SQL

使用 MySQL 自带的 EXPLAIN 分析执行计划。

Type 类型（性能由好到差）：

NULL → system → const → eq_ref → ref → range → index → ALL

2.2 索引

索引结构比较

二叉树 / 红黑树的缺点：树的高度太高，导致磁盘 IO 次数多，查询效率低。

B 树：节点中有指针指向不同数据范围，提高检索效率。

B+ 树：将具体数据都放在叶子节点上，非叶子节点只存索引，进一步提高了效率。

聚簇索引 vs 非聚簇索引 vs 回表

回表：通过二级索引找到主键 → 再通过主键去聚簇索引查完整数据。

覆盖索引 / 超大分页优化

覆盖索引：查询需要的列全部包含在索引中，无需回表。

超大分页优化思路：利用子查询 + 覆盖索引查找主键 id，再关联回表，避免原始 LIMIT 造成的逐行回表。

索引创建原则

1. 数据量较大、查询频繁的表建立索引
2. 针对常作为查询条件、排序、分组的字段
3. 尽量使用区分度高的列
4. 长字符串字段可建立前缀索引
5. 尽量使用联合索引，减少单列索引，利用覆盖索引避免回表
6. 控制索引数量，维护成本随索引数增长
7. 索引列不能存 NULL，用 NOT NULL 约束，帮助优化器选择

索引失效场景

2.3 SQL 优化经验

表设计优化（参考阿里开发手册）

1. 设置合适的数值类型，选择合适的字段长度
2. CHAR 定长效率高，VARCHAR 可变长效率稍低

SQL 语句优化

1. SELECT 指明字段名，避免 SELECT *
2. 避免索引失效的写法
3. 尽量用 UNION ALL 代替 UNION（UNION 多一次去重操作）
4. 避免在 WHERE 子句中对字段进行表达式操作
5. JOIN 优化：能用 INNER JOIN 就不用 LEFT/RIGHT JOIN。必须使用时，小表作为驱动表。内连接会自动优化将小表放外部；外连接不会调整顺序。

2.4 MySQL 事务

事务特性（ACID）

• A（原子性）：事务是不可分割的最小操作单元
• C（一致性）：事务前后数据保持一致
• I（隔离性）：事务之间互不干扰
• D（持久性）：事务提交后数据永久保存

ACID 在 InnoDB 里怎么实现

一句话：A 靠 Undo Log，D 靠 Redo Log，I 靠 MVCC + 锁；C 是目标，不是单独某一个日志。

A：Undo Log（回滚日志）

• 修改数据之前，先把旧值/反向操作记录到 Undo Log；
• 事务执行 ROLLBACK 时，按 Undo Log 逆向还原；
• Undo Log 同时是 MVCC 版本链的一部分，事务提交后由 purge 线程清理。

D：Redo Log（重做日志）

• 修改先发生在 Buffer Pool，同时记录 Redo Log；
• 提交时按 innodb_flush_log_at_trx_commit 决定是否 fsync 到磁盘；
• Redo Log 是顺序写，比随机写表文件快，这就是 WAL（Write-Ahead Logging）的意义；
• 崩溃恢复时重放 Redo Log，把「已提交但还没来得及刷回表文件」的修改补上；
• innodb_flush_log_at_trx_commit=1（默认）最安全，=2 提交只写 OS cache，=0 每秒刷一次；
• 生产常说的「双 1」：innodb_flush_log_at_trx_commit=1 + sync_binlog=1。

⚠️ Redo Log 保证的是「已提交事务的修改不丢」，不是「数据已经立刻写进表文件」。表文件由后台线程异步刷盘。

I：MVCC + 锁

• InnoDB 行里有隐藏列 DB_TRX_ID（最后修改它的事务 ID）和 DB_ROLL_PTR（指向 Undo Log 的版本链）；
• 普通 SELECT 走 MVCC：根据 ReadView 判断哪个版本可见，实现「读不阻塞写、写不阻塞读」；
• RC（读已提交）：每条语句生成一个 ReadView；
• RR（可重复读，默认）：事务第一次读时生成 ReadView，之后复用，所以同一事务内读到的是同一个快照；
• 写写冲突靠行锁排队；RR 下当前读还会用 next-key lock（记录锁 + 间隙锁）处理幻读。

C：一致性

• A/D/I 保证数据库层面的正确性；
• 还需要主键、唯一、非空、外键、CHECK 等约束；
• 业务逻辑本身必须正确——数据库不能替你保证「不能扣错账户」这种语义。

Undo / Redo / Binlog 的区别

并发事务带来的问题

• 脏读：读到其他事务未提交的数据
• 不可重复读：同一事务内两次读取结果不同
• 幻读：同一事务内两次查询结果集不同

事务隔离级别