数据库索引、事务与隔离级别

🗄️ 索引是查询性能的核心，事务和隔离级别是数据一致性的保障。两者都需要深入理解才能在生产环境做出正确决策。

索引

B+ 树索引原理

所有数据存在叶子节点，叶子节点用链表相连
→ 适合范围查询（ORDER BY、BETWEEN）
→ 聚簇索引叶节点存整行数据，二级索引叶节点存主键值

索引使用原则

-- ✅ 最左前缀原则：(a, b, c) 索引可用于 a=1、a=1 AND b=2
SELECT * FROM t WHERE a = 1 AND b = 2;

-- ❌ 跳过最左列，索引失效
SELECT * FROM t WHERE b = 2;

-- ❌ 索引列上有函数，索引失效
SELECT * FROM t WHERE YEAR(created_at) = 2024;
-- ✅ 改写为范围查询
SELECT * FROM t WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

-- 覆盖索引：查询字段都在索引中，无需回表
SELECT id, name FROM t WHERE name = 'Alice';  -- 若 (name, id) 是索引

EXPLAIN 分析

EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND status = 'pending';
-- 关注：type（ref > range > index > ALL）
--       key（使用的索引）
--       rows（预估扫描行数）
--       Extra（Using filesort / Using temporary 是警告信号）

---

事务

ACID 属性

属性	说明
Atomicity	原子性：全成功或全回滚
Consistency	一致性：事务前后数据满足约束
Isolation	隔离性：并发事务互不干扰
Durability	持久性：提交后数据永久保存

常见并发问题

问题	描述
脏读	读到另一事务未提交的数据
不可重复读	同一事务两次读同一行，结果不同
幻读	同一事务两次查询，第二次多了/少了行

---

隔离级别

级别	脏读	不可重复读	幻读	性能
READ UNCOMMITTED	✅ 有	✅ 有	✅ 有	最高
READ COMMITTED	❌ 无	✅ 有	✅ 有	高
REPEATABLE READ	❌ 无	❌ 无	⚠️ 部分	中
SERIALIZABLE	❌ 无	❌ 无	❌ 无	最低

MySQL InnoDB 默认 REPEATABLE READ，通过 MVCC + Gap Lock 解决大部分幻读。

---

MVCC（多版本并发控制）

InnoDB 每行数据维护两个隐藏字段：
  trx_id：最后修改的事务 ID
  roll_pointer：指向 undo log 中的旧版本

读操作使用 ReadView 确定可见版本：
  创建时记录活跃事务列表
  只读 < min_trx_id 或已提交的版本

---

常见误区

• 在高并发场景使用 SERIALIZABLE，导致大量锁等待
• 长事务持有锁不释放，导致其他查询阻塞
• 没有在低选择性列（如 status）上单独建索引，该列区分度低索引效果差