【每日八股】复习 MySQL Day2：索引

昨日内容复习

MySQL 事务的四大特性？

• 原子性：事务当中的操作要么全部成功，要么全部失败；
• 一致性：数据库总是从一个一致性的状态迁移到另一个一致性的状态；
• 隔离性：事务执行成功之前所做的修改应该对其他事务不可见；
• 持久性：事务执行成功之后所做的修改应当持久化到本地磁盘当中。

并发事务会产生哪些问题？

• 脏读：事务还未提交，其所做的修改就可以被其他事务看到；
• 不可重复读：事务开启时读取到的数据与事务执行过程中读取到的数据不一致；
• 幻读：主要出现在区间查询的场景下，事务执行期间，两次区间查询的结果不一致。

MySQL 事务的隔离级别？

• 读未提交：级别最低，可能发生脏读、不可重复读、幻读；
• 读已提交：可能发生不可重复读和幻读；
• 可重复读：InnoDB 的默认隔离级别，可能发生幻读；
• 串行化：失去并发行，性能最差，每一次读写操作都需要独立加锁，安全性与一致性最强。

MVCC 实现原理？

针对数据库当中的每一条记录，都会新增若干个字段，其中最关键的一个字段是 DB_TRX_ID，它记录的是最后一个修改当前记录的事务 ID。此外，还有一个重要的 DB_ROLL_PTR 字段，它是回滚指针，指向当前记录之前的版本。

此外，针对每一次查询操作，会生成一个 Read View，它具有：

• m_ids：生成 Read View 时活跃的事务 ID 列表；
• min_trx_id：m_ids 中最小的事务 ID；
• max_trx_id：m_ids 中最大的事务 ID；
• creator_trx_id：创建该 Read View 的事务 ID。

可见性判断算法

• 比较记录的 DB_TRX_ID 与 creator_trx_id，如果一致说明上一次修改由当前事务完成，当前记录对该事务可见。
• 比较记录的 DB_TRX_ID 与 min_trx_id，如果 DB_TRX_ID 小于 min_trx_id，说明之前对该记录做出修改的事务已经提交，该记录对当前事务可见。
• 比较记录的 DB_TRX_ID 与 max_trx_id，如果 DB_TRX_ID 大于等于 max_trx_id，说明当前记录由当前事务启动后的事务修改，该版本对当前事务不可见，通过 DB_ROLL_PTR 回滚到可见版本。
• 检查 DB_TRX_ID 是否在 m_ids 中，如果在说明对当前记录做出修改的事务活跃，该记录对当前事务不可见。

幻读如何解决？

快照读（一致性非锁定读）
对于普通的 SELECT 查询语句，InnoDB 使用 MVCC 进行版本控制，避免看到其他事务插入的数据。

使用间隙锁 + 临键锁

• 间隙锁：锁定索引记录之间的间隙；
• 临键锁：锁定记录及其之前的间隙。

读已提交隔离级别如何实现？

在读已提交隔离级别下，每次执行 SELECT 时都会生成一个 Read View。创建 Read View 时已经提交的事务所做的修改对当前事务是可见的（即该隔离级别下会产生不可重复读的问题），但未提交及该事务之后开启的事务所做的修改对当前事务不可见。

复习 MySQL Day2：索引

MySQL 使用 B+ 树作为索引的优势是什么？

B+ 树是一种多叉树，其非叶子结点仅存放索引，而叶子结点才存放真正的数据，叶子结点之间会通过链表进行顺序连接。

通过 B+ 树存储千万级别的数据只需要三到四层，也就是说在进行数据库查询时只需要进行三到四次磁盘 I/O。此外，通过 B+ 数叶子结点之间的链表连接可以非常方便地进行区间查询。

在对 B+ 树进行修改时，只需要修改叶子结点即可，不需要修改非叶子结点，因此不涉及树结构的变动。

索引有哪几种？

• 单值索引：一个索引只包含一个列，一个表可以有多个单值索引。
• 唯一索引：索引列的值必须唯一，但允许有重复的 null。
• 主键索引：设定数据库表中的某个键为主键时，它会自动成为主键索引。
• 复合索引：一个索引包含多个列。
• 前缀索引：对字符类型字段的前几个字符建立索引。建立前缀索引可以减少索引占用的存储空间，提升查询效率。

什么是最左匹配原则？

MySQL 的最左匹配原则是 B+ 树联合索引的核心特性，指查询时必须从索引的最左列开始，并按联合索引定义的列顺序依次匹配，否则联合索引失效。

1. 联合索引的结构
假设在表上建立了一个联合索引 INDEX(col1, col2, col3)，B+ 树会按照 col1 -> col2 -> col3 的顺序组织数据：

• 先按 col1 进行排序；
• col1 相同则按 col2 进行排序；
• col2 相同则按 col3 进行排序。

一个建立联合索引的例子如下：

CREATE TABLE users (id INT,name VARCHAR(50),age INT,city VARCHAR(50),INDEX idx_name_age_city (name, age, city)
);

上述 sql 语句在建表时，按照 name -> age -> city 的顺序建立了联合索引。

2. 最左匹配原则的规则

查询条件	是否走索引	原因
WHERE name = 'Alice'	✅	匹配最左列 name
WHERE name = 'Alice' AND age = 25	✅	按顺序匹配 name -> age
WHERE age = 25 AND city = 'Beijing'	❌	未从最左列 name 开始匹配
WHERE name = 'Alice' AND city = 'Beijing'	⚠️	仅 name 走索引，city 因跳过了 age 而无法匹配
WHERE name LIKE 'A%'	✅	最左前缀匹配
WHERE name LIKE '%A'	❌	非前缀匹配，无法利用索引

3. 特殊情况
（1）跳过中间列
仅被跳过列之前的列会使用索引，导致查询效率下降。

（2）范围查询之后的列失效
使用范围查询会停止后序列的索引匹配。

（3）函数或表达式导致索引失效
对索引列使用函数或计算会破坏索引顺序。

索引区分度？

当查询优化器发现某个值在表中出现的数据行占比超过 30% 时，会忽略索引，全表扫描。

联合索引如何排序？

可以利用索引的有序性，对索引列和排序列建立联合索引。

使用索引有哪些缺陷？

索引实际上也是一张表，建立索引同样需要占用内存空间。

此外，索引不宜在需要频繁增删改的字段上建立，因为如果需要频繁增删改，不仅需要修改数据表，还需要修改索引，时间开销较大。

什么时候需要建立索引，什么时候不需要建立索引？

需要使用索引

• 表的主键：自动建立主键索引；
• 为表的字段引入唯一约束：建立唯一索引；
• 直接条件查询的字段：经常使用 WHERE 查询条件的字段宜建立索引，以提高查询效率；
• 查询中与其他表有关联的字段：宜对外键建立索引；
• 查询中排序的字段：对排序字段建立索引可以大大提高排序速度；
• 查询中统计或分组统计的字段：经常用于 GROUP BY 和 ORDER BY 的字段，可以创建联合索引。

不需要建立索引

• 表的记录太少；
• 经常增删改的字段；
• 数据重复且分布平均的表字段；
• 经常和主字段一起查询但主字段索引值较多的表字段。

使用索引的注意事项

• like 语句的前导模糊查询会使索引失效，但非前导的模糊查询可行。
• 负向查询不能使用索引；
• 联合查询应注意最左匹配原则；
• 避免过度使用索引，因为索引会引入额外的存储与维护开销；
• 更新十分频繁的字段不宜建立索引；

WHERE 语句索引使用的注意事项

• 联合索引时注意最左匹配原则；
• 避免在索引列上引入计算或函数，这样会使索引列失效退化为全表扫描；
• 使用范围查询后索引列失效；
• LIKE 前导模糊查询会使索引失效；

索引什么时候失效？

• 查询条件中带 OR 时可能失效，除非所有条件均建立了索引；
• like 模糊查询时使用前导模糊，会使索引失效；
• 索引列上引入计算和函数会使索引失效；
• 联合索引查询时如违背最左匹配原则会导致索引部分失效或全部失效；
• 如果 MySQL 估计全表扫描比使用索引更快，会全表扫描。