面试小结 - MySQL

记录一下秋招面试遇到关于 MySQL 的问题，会持续更新。

文章目录

• 1. 事务（并发）
• • 1.1 怎么解决不可重复读 / 怎么实现可重复读
  • 1.2 怎么解决幻读
  • 1.3 讲讲行锁和表锁
  • 1.4 行锁实现原理
  • 1.5 讲讲 MVCC
• 2. 索引
• • 2.1 什么时候索引会失效？
  • 2.2 其他数据结构可以实现索引吗？
  • 2.3 索引覆盖？
  • 2.4 最左匹配原则?
  • 2.5 怎样建立索引？
  • 2.6 讲讲 Innodb 的索引
  • 2.7 讲讲 B+ 树
• 3. SQL
• • 3.1 慢 SQL 原因？
  • 3.2 慢 SQL 排查？
  • 3.3 慢 SQL 优化？
• 4. 存储引擎
• • 4.1 讲讲 Innodb 和 Myisam 区别

1. 事务（并发） 1.1 怎么解决不可重复读 / 怎么实现可重复读

MySQL 有个机制叫做 MVCC，也就是多版本并发控制。

基于乐观锁的思想，主要依赖于记录里的 3 个隐式字段，undo 日志， Read View 来实现的。

隐式字段：隐藏ID，innodb 自动产生的一个聚簇索引，版本号是当前数据所属的事务版本号，回滚指针是配合 undo 日志，指向上一个旧版本的数据。

undo 日志：

1. insert undo log：代表事务在 insert 新记录时产生的 undo log，只在事务回滚时需要，并且在事务提交后可以被立即丢弃。
2. update undo log：事务在进行 update 或 delete 时产生的 undo log；不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快照读或事务回滚不涉及该日志时，对应的日志才会被 purge 线程统一清除。

Read View：数据库当前时刻的一个快照，记录并维护系统当前活跃事务的 ID 列表。

所以可重复读就是在第一次快照读后创建快照及 Read View，此后调用快照读的时候，使用的都是第一次的 Read View，这样对生成 Read View 时刻之后的修改都是不可见的，也就实现了可重复读。

1.2 怎么解决幻读

1. 串行化隔离级别
2. 可重复读隔离级别 + next-key 锁

1.3 讲讲行锁和表锁

表锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率最低，并发度最高。

1.4 行锁实现原理

行锁是 Innodb 存储引擎特有的。

一个 sql 的筛选过程就是先 Index Key 到 Index Filter 再到 Table Filter。

1. 当 sql 语句的检索条件不是索引，sql 在 Table Filter 阶段会进行全表扫描，这意味着在所有的聚簇索引上加锁，也就相当于一个表锁。

2. 当检索条件是普通索引的时候，sql 在 Index Key 筛选的数据不具备唯一性，所以会加 Next-Key Lock。（Record Lock + Gap Lock）

3. 当检索条件是唯一索引或者说主键的时候，sql 在 Index Key 阶段可以确定唯一的一条数据，所以会在对应的聚簇索引上加 Record Lock。

由此可知，Innodb 对于行锁不是锁住整个数据行，而是去锁住对应的索引。

1.5 讲讲 MVCC

MVCC，也就是多版本并发控制。

基于乐观锁的思想，主要依赖于记录里的 3 个隐式字段，undo 日志， Read View 来实现的。

隐式字段：隐藏 id（ innodb 自动产生的一个聚簇索引），版本号（当前数据所属的事务版本号），回滚指针（配合 undo 日志，指向上一个旧版本的数据）。

undo 日志：

1. insert undo log：代表事务在 insert 新记录时产生的 undo log，只在事务回滚时需要，并且在事务提交后可以被立即丢弃
2. update undo log：事务在进行 update 或 delete 时产生的 undo log；不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快照读或事务回滚不涉及该日志时，对应的日志才会被 purge 线程统一清除。

Read View：数据库当前时刻的一个快照，记录并维护系统当前活跃事务的 ID 列表。

已提交读隔离级别下的事务在每次查询的开始都会生成一个独立的ReadView，而可重复读隔离级别则在第一次读的时候生成一个ReadView，之后的读都复用之前的ReadView。

工作流程：

1. 判断数据行的事务版本是否比 Read View 的最小值小，如果小就说明在事务开启前就提交，所以数据是可见的。
2. 判断数据行的事务版本是否比 Read View 的最大值大，如果大就说明在事务开启后才生成，所以数据是不可见的。
3. 如果处于 Read View 的最大值和最小值之间，就遍历 Read View 判断是否有与之对应的事务 ID，如果有就说明该事务在开启时处于活跃状态，所以数据是不可见的；否则相反。

当数据不可见的时候可利用回滚指针去 update undo log 查看旧版本的数据。

2. 索引 2.1 什么时候索引会失效？

1. 不符合最左匹配原则
2. 索引列查询进行表达式计算
3. 索引列和索引列比较
4. 索引列上使用函数
5. 索引列数据类型强转
6. is null，is not null 索引失效
7. in 条件过多，会索引失效
8. like 通配符前置
9. or 涉及的列包含非索引列

2.2 其他数据结构可以实现索引吗？

• 链表：全表查询。

• 哈希：O(1) 定位，查询速度快，但是无法支持比较，排序和区间查询，哈希冲突多时维护代价大。

• 搜索树：数据单调时会退化成链表。

• 红黑树（自旋次数只要 2~3 次，故不考虑平衡树）：树的高度随数据量增加而变大，磁盘 I/O 代价大。

• B树：非叶子节点也存储数据，指针空间少。

• B+树：叶子节点存放数据，其余节点存放指针（使得这棵树更加矮胖，查询时 I/O 操作次数也更少），叶子节点间是双链表，优化区间查询。

2.3 索引覆盖？

查询的列与索引对应就无需回表查询。

联合索引 A,B,C 。场景是 B 范围查询如何命中，如果必须要命中 C 怎么做？

索引覆盖，select a,b,c from tab where a = ? and b > ? and c = ?

2.4 最左匹配原则?

对于联合索引，就是索引顺序跟查询条件顺序一致，就会驱动索引，如果不一致也就是匹配断片的情况，就只有部分索引生效。

值得一提的是，这个所谓的顺序不是要一模一样，因为 MySQL 内部的优化器会进行调整。

2.5 怎样建立索引？

条件：

• 出现在 SELECT、UPDATE、DELETE 语句的 WHERe 从句中的列
• 包含在 ORDER BY、GROUP BY、DISTINCT 中的字段
• 并不要将符合 1 和 2 中的字段的列都建立一个索引， 通常将 1、2 中的字段建立联合索引效果更好
• 多表 join 的关联列

顺序：

• 区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）
• 尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO 性能也就越好）
• 使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）

2.6 讲讲 Innodb 的索引

Innodb 的数据⽂件本身就是索引⽂件，其表数据⽂件本身就是按 B+Tree 组织的⼀个索引结构，树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据⽂件本身就是主索引，这被称为 聚簇索引，而其余的索引都作为辅助索引，辅助索引的 data 域存储相应记录主键的值而不是地址，这也是和 MyISAM 不同的地⽅。在根据主索引搜索时，直接找到 key 所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走⼀遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

2.7 讲讲 B+ 树

B+ 树是 为磁盘或其他直接存取的辅助存储设备而设计的一种平衡搜索树。

叶子节点存放数据，其余节点存放指针；叶子节点间是双链表，优化区间查询。

与其他数据结构相比，磁盘 I/O 性能以及范围查询都有极好的性能。

3. SQL 3.1 慢 SQL 原因？

• 大多数情况很正常，偶尔慢

1. 数据库在刷新脏页
   • redo log 写满了，需要同步到磁盘，也就是刷脏页。
   • 内存不够用了，淘汰的内存数据页刚好是脏数据页。
   • MySQL 认为系统空闲。
   • MySQL 正常关闭，会把内存的脏页 flush 到磁盘。
2. 执行的时候，遇到锁阻塞，如表锁、行锁。

• 一直很慢

肯定是索引问题，要么数据库没有建立正确的索引，要么 SQL 语句写歪了，没有使用到索引。

3.2 慢 SQL 排查？

• 数据库设置 SQL 慢查询

set global slow_query_log = ON
set global long_query_time = 3600;
set global log_querise_not_using_indexes = ON;

• show processlist 定位 SQL
• 慢 SQL 分析

explain ：

1. type：表示MySQL在表中找到所需行的方式，或者叫访问类型

   • type=ALL，全表扫描，MySQL遍历全表来找到匹配行

   • type=index，索引全扫描

   • type=range，索引范围扫描

   • type=eq_ref，唯一索引

   • type=NULL，MySQL不用访问表或者索引，直接就能够得到结果（性能最好）

2. possible_keys: 表示查询可能使用的索引

3. key: 实际使用的索引

4. key_len: 使用索引字段的长度

5. rows: 扫描行的数量

6. Extra：

   • using index：覆盖索引，不回表
   • using where：回表查询
   • using filesort：需要额外的排序，不能通过索引得到排序结果

3.3 慢 SQL 优化？

考虑从多方面回答：索引 + sql 语句 + 数据库结构优化 + 优化器优化 + 架构优化。

索引：

1. 尽量索引覆盖。
2. 联合索引符合最左匹配原则。
3. 避免索引失效。
4. 在读少写多的场景下，可以选择普通索引而不要选择唯一索引

更新时，普通索引可以使用 change buffer 进行优化，减少磁盘IO，将更新操作记录到 change bufer，等查询来了将数据读到内存再进行修改。（也就是懒修改，类似于线段树的懒惰标记）

5. 索引建立原则（一般建在 where 和 order by，基数要大，区分度要高，不要过度索引，外键建索引）

sql语句：

1. 多条插入语句写成一条（批插入）
2. 数据有序插入（主键索引）

数据库结构优化：

1. 将字段多的表分解成多个表
2. 对于经常联合查询的表可以考虑建立中间表

磁盘预读：

请求一页的数据时，可以把后面几页的数据也一起返回，放到数据缓冲池中，这样如果下次刚好需要下一页的数据，就不再需要到磁盘读取（局部性原理）

架构优化：

读写分离，主库写从库读

4. 存储引擎 4.1 讲讲 Innodb 和 Myisam 区别

Myisam：支持表锁，适合读密集的场景，不支持外键，不支持事务，索引与数据在不同的文件。

Innodb：支持行、表锁，默认为行锁，适合并发场景，支持外键，支持事务，索引与数据同一文件。