MySQL基础知识（二）

MySQL索引与锁机制

1. 索引

1.1 什么是索引？

索引是数据库中用于优化查询性能的数据结构。它存储着满足特定查找的数据，并通过指针引用表中的数据，从而实现高效的数据访问。索引可以快速定位到所需的数据行，并避免全表扫描。

1.2 MySQL为何使用B+树？

• 优点：

  • 范围查询支持：B+树节点天然有序，适用于范围查询，无需全表扫描。
  • 覆盖索引：可以通过索引直接返回结果，而无需读取数据行，提高查询效率。
  • 左匹配机制：在复合索引中，可以利用前缀匹配原理，提高查询效率。

• 与Hash索引的对比：

  • Hash索引：底层使用哈希表，适用于等值查询，但对范围查询无效，需全表扫描。
  • B+树：支持范围查询和顺序访问，适合数据库索引。

1.3 B+树和B-树的区别

• B-树：

  • 所有键值存储在树的每个节点中。
  • 内部节点存储数据，导致查询复杂度不固定。

• B+树：

  • 所有关键字存储在叶节点，内部节点仅存储索引。
  • 每个叶节点增加指针，便于顺序遍历。
  • 查询时间复杂度为O(log n)，更高效。

2. 何时创建索引

以下场景建议创建索引：

1. 主键：主键自动建立唯一索引。
2. 频繁查询的字段：经常作为查询条件的字段应创建索引。
3. 外键关系：与其他表关联的字段建立索引。
4. 多字段查询：倾向于创建组合索引。
5. 排序字段：查询中涉及排序的字段使用索引可提高排序速度。
6. 统计或分组字段：涉及统计或分组的字段应考虑索引。

3. 索引分类

1. 主键索引：用于唯一标识记录。
2. 唯一索引：确保值的唯一性。
3. 复合索引：由多个字段组成的索引。
4. 聚集索引与非聚集索引：
   • 聚集索引：数据存储顺序与索引顺序一致。
   • 非聚集索引：数据存储顺序与索引顺序不一致。
5. 全文索引：支持在MyISAM上，只能在特定类型数据（如CHAR、VARCHAR、TEXT）上使用。

4. 为什么使用B+树作为索引结构？

• 低磁盘读写代价：B+树的内部节点较小，减少了与磁盘的IO次数，提升查询效率。
• 顺序访问：数据存储在叶节点，方便进行顺序访问，可以一次性读取多个关键字。

5. MySQL的最左前缀原则与前缀原则

• 最左前缀原则：复合索引时，查询条件应从左到右匹配，才能利用索引。
• 前缀原则：索引列不参与计算，保持列“干净”，以便提高索引的使用效率。

5.1 建立索引的原则

1. 最左前缀匹配原则。
2. 选择区分度高的列作为索引。
3. 索引列不能参与计算，保持列“干净”。
4. 尽量扩展已有索引，而非新建索引。

6. 锁机制

6.1 共享锁与排他锁

• 共享锁：允许多个事务同时读取数据，但不允许修改。
• 排他锁：仅允许一个事务修改数据，其他事务无法读取或修改。

6.2 表级锁与行级锁

• 表级锁：锁定整个表，其他事务无法访问，适合写操作不频繁的场景。
• 行级锁：允许在表的不同行上并发操作，提高了并发性能。

6.3 乐观锁与悲观锁

• 乐观锁：假设不会发生冲突，允许并发操作，在更新时检测数据是否被修改，若未修改则允许更新。
• 悲观锁：假定会发生冲突，处理过程中对数据加锁，防止其他事务的修改。

6.4 事务隔离级别

1. 读未提交：允许读取其他未提交事务的数据，可能导致脏读。
2. 读已提交：只能读取已提交的数据，避免脏读。
3. 可重复读：保证同一事务多次读取相同数据时结果一致。
4. 串行化：最高隔离级别，确保事务之间不会产生干扰，但可能影响性能。

7. MySQL的锁类型

1. 全局锁：针对整个数据库，阻止其他用户更新数据。
2. 表级锁：锁定整个表，适用于读密集型应用。
3. 行级锁：对单行数据进行锁定，提供更高的并发性能。
4. 页级锁：介于行级锁和表级锁之间，适用于特定场景。