MySQL基础知识（二）
MySQL索引与锁机制
1. 索引
1.1 什么是索引？
索引是数据库中用于优化查询性能的数据结构。它存储着满足特定查找的数据，并通过指针引用表中的数据，从而实现高效的数据访问。索引可以快速定位到所需的数据行，并避免全表扫描。
1.2 MySQL为何使用B+树？
-
优点：
- 范围查询支持：B+树节点天然有序，适用于范围查询，无需全表扫描。
- 覆盖索引：可以通过索引直接返回结果，而无需读取数据行，提高查询效率。
- 左匹配机制：在复合索引中，可以利用前缀匹配原理，提高查询效率。
-
与Hash索引的对比：
- Hash索引：底层使用哈希表，适用于等值查询，但对范围查询无效，需全表扫描。
- B+树：支持范围查询和顺序访问，适合数据库索引。
1.3 B+树和B-树的区别
-
B-树：
- 所有键值存储在树的每个节点中。
- 内部节点存储数据，导致查询复杂度不固定。
-
B+树：
- 所有关键字存储在叶节点，内部节点仅存储索引。
- 每个叶节点增加指针，便于顺序遍历。
- 查询时间复杂度为O(log n)，更高效。
2. 何时创建索引
以下场景建议创建索引：
- 主键：主键自动建立唯一索引。
- 频繁查询的字段：经常作为查询条件的字段应创建索引。
- 外键关系：与其他表关联的字段建立索引。
- 多字段查询：倾向于创建组合索引。
- 排序字段：查询中涉及排序的字段使用索引可提高排序速度。
- 统计或分组字段：涉及统计或分组的字段应考虑索引。
3. 索引分类
- 主键索引：用于唯一标识记录。
- 唯一索引：确保值的唯一性。
- 复合索引：由多个字段组成的索引。
- 聚集索引与非聚集索引：
- 聚集索引：数据存储顺序与索引顺序一致。
- 非聚集索引：数据存储顺序与索引顺序不一致。
- 全文索引：支持在MyISAM上，只能在特定类型数据（如CHAR、VARCHAR、TEXT）上使用。
4. 为什么使用B+树作为索引结构？
- 低磁盘读写代价：B+树的内部节点较小，减少了与磁盘的IO次数，提升查询效率。
- 顺序访问：数据存储在叶节点，方便进行顺序访问，可以一次性读取多个关键字。
5. MySQL的最左前缀原则与前缀原则
- 最左前缀原则：复合索引时，查询条件应从左到右匹配，才能利用索引。
- 前缀原则：索引列不参与计算，保持列“干净”，以便提高索引的使用效率。
5.1 建立索引的原则
- 最左前缀匹配原则。
- 选择区分度高的列作为索引。
- 索引列不能参与计算，保持列“干净”。
- 尽量扩展已有索引，而非新建索引。
6. 锁机制
6.1 共享锁与排他锁
- 共享锁：允许多个事务同时读取数据，但不允许修改。
- 排他锁：仅允许一个事务修改数据，其他事务无法读取或修改。
6.2 表级锁与行级锁
- 表级锁：锁定整个表，其他事务无法访问，适合写操作不频繁的场景。
- 行级锁：允许在表的不同行上并发操作，提高了并发性能。
6.3 乐观锁与悲观锁
- 乐观锁：假设不会发生冲突，允许并发操作，在更新时检测数据是否被修改，若未修改则允许更新。
- 悲观锁：假定会发生冲突，处理过程中对数据加锁，防止其他事务的修改。
6.4 事务隔离级别
- 读未提交：允许读取其他未提交事务的数据，可能导致脏读。
- 读已提交：只能读取已提交的数据，避免脏读。
- 可重复读：保证同一事务多次读取相同数据时结果一致。
- 串行化：最高隔离级别，确保事务之间不会产生干扰，但可能影响性能。
7. MySQL的锁类型
- 全局锁：针对整个数据库，阻止其他用户更新数据。
- 表级锁：锁定整个表，适用于读密集型应用。
- 行级锁：对单行数据进行锁定，提供更高的并发性能。
- 页级锁：介于行级锁和表级锁之间，适用于特定场景。