MySql 三大知识点——索引、锁、事务

MySQL 的三大核心知识点——索引、锁、事务，是数据库性能优化和并发控制的关键，以下是详细解析：

定义：索引是数据库的“目录”，通过快速定位数据提升查询效率，类似书籍目录通过页码查找内容。

• 优点：

  天生排序：数据按索引字段有序存储。

  快速查找：通过B+树结构减少磁盘IO次数。

• 缺点：

  占用空间：索引数据需额外存储。

  降低写入速度：数据更新时需同步维护索引。

• 适用场景：

  小表（全表扫描更快）、中大表（推荐使用）、超级大表（索引效果有限）。

• 索引类型：

  实现方式：

  聚集索引：数据按索引顺序物理存储（InnoDB主键默认聚集）。

  辅助索引：非聚集索引，存储主键值而非数据本身。

  功能分类：

  普通索引：无约束，基础索引类型。

  唯一索引：确保字段值唯一，允许空值（仅一个）。

  主键索引：特殊的唯一索引，不允许空值。

  复合索引：多列组合索引，遵循最左前缀原则。

  外键索引：InnoDB特有，维护表间数据一致性。

  全文索引：仅支持InnoDB/MyISAM，对英文文本分词检索（生产环境常用ES/Solr替代）。

• B+树结构：

  非叶子节点：仅存储索引和指针，不存数据，提高单节点索引容量。

  叶子节点：存储数据和指向相邻节点的指针，形成链表结构。

  性能优势：

  3层B+树可存储约10亿索引，查询仅需2-3次磁盘IO。

  支持范围查询和排序操作。

  设计建议：

  主键尽量短（如自增INT），避免UUID导致页分裂。

  分库分表时，可保留自增主键，逻辑主键作为唯一索引。

核心目标：保证并发事务下的数据一致性，通过锁的粒度和类型控制访问冲突。

• 锁类型：

  按操作分：

  共享锁（S锁）：读锁，允许多事务并发读取。

  排他锁（X锁）：写锁，独占资源，禁止其他事务读写。

  意向锁：表级锁，标记行锁存在，避免全表扫描检查。

  按策略分：

  悲观锁：通过SELECT ... FOR UPDATE显式加锁。

  乐观锁：通过版本号（如version字段）实现CAS机制。

• 锁粒度：

  表锁：锁定整张表，并发度低（如MyISAM引擎）。

  页锁：锁定数据页，中间粒度（BerkeleyDB引擎）。

  行锁：锁定单行，高并发（InnoDB引擎）。

• 锁算法：

  Record Lock：锁定单行记录。

  Gap Lock：锁定索引间隙，防止幻读。

  Next-Key Lock：Record Lock + Gap Lock，锁定记录及间隙（InnoDB默认RR隔离级别使用）。

• 特殊场景：

  默认读操作：使用MVCC（多版本并发控制）实现一致性非锁定读，不上锁。

  手动加锁：SELECT ... FOR UPDATE（X锁）或LOCK IN SHARE MODE（S锁）实现一致性锁定读。

  SERIALIZABLE级别：通过串行化执行避免丢失更新（如转账场景）。

• 行锁失效场景：

  查询未命中索引时，优化器可能降级为表锁。

ACID特性：

• 原子性（Atomicity）：通过Undo Log回滚未提交变更。

• 一致性（Consistency）：由原子性、隔离性、持久性共同保证。

• 隔离性（Isolation）：通过MVCC和锁实现，解决脏读、不可重复读、幻读。

• 持久性（Durability）：通过Redo Log保证事务提交后数据不丢失。

• 隔离级别：

  未提交读（RU）：允许脏读（事务未提交的修改可见）。

  已提交读（RC）：避免脏读，但允许不可重复读（每次读生成新Read View）。

  可重复读（RR）：避免脏读和不可重复读（事务内使用同一Read View），MySQL通过MVCC和Next-Key Lock避免幻读。

  串行化（SERIALIZABLE）：完全串行执行，避免所有并发问题（性能最低）。

• MVCC实现：

  隐藏字段：每行记录包含事务ID、回滚指针、删除标记等。

  Read View：事务启动时生成，决定可见数据版本。

  Undo Log：存储数据历史版本，形成版本链。

• 索引：优先使用短主键和复合索引，避免超级大表索引失效。
• 锁：根据业务选择合适粒度，高并发场景慎用表锁。
• 事务：合理设置隔离级别，RR级别通常能满足需求，SERIALIZABLE仅用于极端场景。

通过理解这三大核心机制，可有效优化MySQL性能并避免并发问题。