MySQL 索引与事务详解

目录

一、索引（Index）

二、事务（Transaction）

三、总结

---

一、索引（Index）

索引的本质：一种数据结构（如 B+Tree、Hash），用于快速定位数据，避免全表扫描。
核心作用：提升查询效率，但会牺牲一定的写性能（增删改需维护索引）。

1. 索引类型

• B+Tree 索引（默认）
  • 特点：支持范围查询、排序、最左前缀匹配。
  • 适用场景：=, >, <, BETWEEN, ORDER BY, GROUP BY 等操作。
  • InnoDB 聚簇索引：数据直接存储在 B+Tree 叶子节点，主键即聚簇索引。
  • 非聚簇索引（二级索引）：叶子节点存储主键值，需回表查询数据。
• 哈希索引
  • 特点：O(1) 时间复杂度，仅支持精确匹配（=），不支持范围查询。
  • 适用场景：内存表（如 MEMORY 引擎）、等值查询频繁的场景。
  • 限制：哈希冲突、无法排序。
• 全文索引（FULLTEXT）
  • 特点：基于分词技术，支持自然语言搜索。
  • 适用场景：MATCH() ... AGAINST 全文检索（如文章内容搜索）。
  • 引擎支持：MyISAM 和 InnoDB（5.6+）。
• 空间索引（R-Tree）
  • 适用场景：地理空间数据（如经纬度查询）。
  • 引擎支持：MyISAM。

2. 索引创建与使用

• 创建语法：

CREATE INDEX idx_name ON table(column);          -- 普通索引

CREATE UNIQUE INDEX idx_name ON table(column);   -- 唯一索引

ALTER TABLE table ADD PRIMARY KEY(column);       -- 主键索引

• 联合索引（复合索引）
  • 最左前缀原则：索引 (a, b, c) 可生效于 a, a+b, a+b+c，但无法单独使用 b 或 c。
  • 覆盖索引：查询字段全部在索引中时，无需回表（性能最优）。
• 索引失效场景
  • 对索引列进行运算或函数操作（如 WHERE YEAR(date) = 2023）。
  • 使用 LIKE 以通配符开头（如 LIKE '%abc'）。
  • 数据类型隐式转换（如字符串列用数字查询）。
  • OR 连接非索引列（除非所有列均有索引）。
  • 优化器认为全表扫描更快（小表或低区分度数据）。

3. 索引优化建议

1. 选择高区分度的列：区分度越高（如唯一键），过滤效果越好。
2. 避免冗余索引：联合索引可替代多个单列索引。
3. 控制索引长度：使用前缀索引（如 INDEX(column(10))）减少存储。
4. 监控索引使用率：通过 SHOW INDEX FROM table 或 INFORMATION_SCHEMA.STATISTICS 分析。
5. 执行计划分析：用 EXPLAIN 查看 type（访问类型）、key（使用索引）、Extra（是否覆盖索引）。

二、事务（Transaction）

事务的本质：一组原子性操作的集合，保证数据一致性。
ACID 特性：

• Atomicity（原子性）：事务全部成功或全部回滚。
• Consistency（一致性）：事务前后数据满足业务约束。
• Isolation（隔离性）：并发事务相互隔离。
• Durability（持久性）：事务提交后数据永久存储。

1. 事务隔离级别

隔离级别	脏读	不可重复读	幻读	实现机制
READ UNCOMMITTED	✔️	✔️	✔️	无锁，直接读最新数据
READ COMMITTED (RC)	✖️	✔️	✔️	快照读（MVCC）
REPEATABLE READ (RR)	✖️	✖️	△	快照读 + 间隙锁（InnoDB）
SERIALIZABLE	✖️	✖️	✖️	所有操作加锁

• InnoDB 默认隔离级别：REPEATABLE READ（通过 MVCC + 间隙锁解决幻读）。
• 幻读：RR 级别下，通过 SELECT ... FOR UPDATE 可能触发间隙锁，阻止其他事务插入。

2. 事务实现机制

• Redo Log（重做日志）
  • 作用：保证持久性，记录物理修改（如页的修改）。
  • 写入流程：事务提交时先写 redo log（顺序写，高性能），再异步刷盘。
• Undo Log（回滚日志）
  • 作用：保证原子性，记录数据修改前的版本，用于回滚或 MVCC。
  • 存储位置：InnoDB 的 undo tablespace。
• MVCC（多版本并发控制）
  • 核心思想：每个事务看到的数据快照版本不同。
  • 实现细节：
    • 隐藏字段：DB_TRX_ID（事务ID）、DB_ROLL_PTR（回滚指针）。
    • ReadView：事务启动时生成活跃事务ID列表，决定可见性。
    • RC vs RR：RC 每次读生成新 ReadView；RR 使用事务启动时的 ReadView。
• 锁机制
  • 行锁：锁住单行数据（如 SELECT ... FOR UPDATE）。
  • 间隙锁（Gap Lock）：锁住索引范围间隙，防止插入（解决幻读）。
  • 临键锁（Next-Key Lock）：行锁 + 间隙锁，锁住左开右闭区间。

3. 事务最佳实践

1. 控制事务长度：避免长事务占用锁资源，导致死锁或性能下降。
2. 明确事务边界：业务逻辑中尽早提交或回滚。
3. 合理选择隔离级别：根据业务需求权衡一致性与性能。
4. 死锁处理：
   • 设置 innodb_lock_wait_timeout 控制锁等待超时。
   • 使用 SHOW ENGINE INNODB STATUS 分析死锁日志。
5. 避免隐式提交：如 DDL 语句（ALTER TABLE）会自动提交当前事务。

三、总结

• 索引优化：根据查询模式设计索引，权衡读写性能，避免过度索引。
• 事务设计：选择合适隔离级别，利用 MVCC 和锁机制平衡并发与一致性。
• 监控工具：善用 EXPLAIN、SHOW PROFILE、INFORMATION_SCHEMA 等分析性能瓶颈。