MySQL 面试总结

这是我自己的学习笔记，尽量把原理整理清楚，只有把各个地方的原理串起来才不用死记硬背。很多时候并不是你记不住八股文，而是你不了解为什么，一旦你知道里面的原理是怎么回事，八股文根本不用背。

1. 什么是存储引擎，存储引擎的作用是什么

对于数据库来讲存储引擎就是用来管理表中数据的底层实现，他决定了以下几个事情：

• 数据怎么存储在磁盘上
• 怎么安全（加锁/事务）快速（索引）的读写数据
• 是否支持事务，外键以及索引的方式

innoDB 存储引擎和myISAM存储引擎的主要区别是什么？

了解这两个存储引擎之前先要铺垫一下聚合索引和非聚合索引的概念，如果你是初级阶段，可以简单的这么理解：
前提在InnoDB中，主键索引等价聚簇索引，非主键索引（二级索）等价非聚簇索引。
聚簇索引就是数据和索引放在一起进行存储的，非聚簇索引是把数据和索引分别存放的。聚簇索引叶子节点是存放的索引+数据，非聚簇索引需要分别说：

• innoDB: 叶子节点是存放的索引+地址（聚簇索引id）。
• myISAM: 叶子节点存储的是索引 + 数据在文件中的偏移量

因此，使用innoDB存储引擎查询的时候如果用到了非聚簇索引并且还需要查询非聚簇索引不包含的字段，需要先查非聚簇索引，根据非聚簇索引的值再去聚簇索引获取其他字段，这个过程叫“回表”

前提在MyISAM中，不存在聚簇索引这个概念。所有的索引都是非聚簇索引，索引的叶子节点存放的是数据文件的物理地址，也正是这个原因，在MyISAM引擎中查询的时候不需要回表
下面我们进行归纳这个问题的答案
InnoDB:

• 同时支持聚簇索引和非聚簇索引
• 从 5.6以后开始支持全文索引
• 支持事务和外键
• 支持行级锁和间隙锁
• 崩溃恢复容易（redo log）

Myisam：索引和数据是分开存放的

• 仅支持非聚簇索引
• 一直都支持全文索引
• 不支持事务和外键
• 只支持表锁，不支持行级锁（支持读多写少）
• 崩溃恢复困难

2. 数据库什么时候开始分库分表？

这个没有一个固定的答案，给你存储的单条数据容量有关系
阿里的推荐是单表行数超过500w或者单表容量超过2Gb，推荐进行分库分表。如果你的数据3年内达不到这个数据请不要在创建表的时候进行分库分表

3. count(*),count(1),count(字段)的区别是什么？

• select count(1) 和select count（* ）功能和性能没有本质的区别（可以使用explain验证，执行计划是一样的），底层会把count（* ）优化成一个常量count（0）。
• count（字段）会排除值为null的数据

4. explain 的时候Extra 有哪些取值给索引相关，代表什么意思？

• Using index ：覆盖索引查询，所有查询的内容都来自索引，不需要回表
• Using index for group-by：覆盖索引+group by优化
• Using index condition：使用索引条件下推，不用每行都回表，可以减少回表次数。这里的下推其实是提前在索引的逻辑里去掉不满足条件的行
• Using where：有 WHERE 条件需要在 Server 层过滤
• NULL：无额外信息

5. 索引的优缺点

其实就我个人理解来说，这个面试题的意义不大，但是需要知道关键的一点是，在你磁盘空间不足的时候你想通过删除数据来腾退空间，有可能你删除数据空间并没有释放，需要刷一下索引才能释放出更多的空间（OPTIMIZE TABLE your_table_name;）
顺便列一下优缺点
优点

• 可以提高查询速度，减少磁盘IO

缺点：

• 需要占用更多的磁盘空间
• 增删改需要有额外的性能的损耗
• 创建和维护索引需要有消耗计算资源

6. 索引条件下推的使用条件

• 只能使用于range、ref、eq_ref、ref_or_null访问方法
• where条件中使用and而不是or
• 索引条件下推适用于分区表
• 不适用于子查询作为条件
• 不适用于存储函数作为条件

7. 索引失效的场景（索引有效的前提是有序）

• 联合索引的情况下：带头大哥不能死，中间兄弟不能断（如果中间断了，后面的索引都没有顺序了，索引就失效了<查看表的索引 show index from ${table_name}>）
• 索引列避免函数 / 运算：将运算逻辑移到查询值侧，或提前预处理数据。
• 保证类型 / 字符集一致：查询值、关联列与索引列的类型、字符集必须完全匹配避免隐式类型转换。
• 优化模糊查询：优先使用前缀匹配（LIKE ‘xxx%’），或用全文索引替代 %xxx%。
• 避免 OR 连接非索引列：给非索引列加索引，或拆分为 UNION 查询。
• 谨慎使用否定条件：尽量用正向逻辑替代 NOT IN / != / IS NOT NULL。
• 创建覆盖索引：将查询常用列（SELECT 字段）加入索引，减少回表。

8. 事务

事务的作用：对于一组操作要么全部成功，要么全部失败，目的是保证数据的最终一致性
事务的4个特性：

• 原子性：当前事务内的操作要么全部成功，要么全部失败。原子性是由undo log来保证的

什么是undo log：
简单理解就是你执行了 一个create表操作，undo log就自动记录一个drop 表的命令，你增加一行数据，undo log 就会添加一个delete 你这行数据的操作

• 一致性：事务的最终一致性，由业务代码逻辑保证
• 隔离性：并发操作数据，会互不影响
• 持久性：一旦提交了事务，它对数据库的改变就是永久的。持久性由redo log<写文件>来保证

9. InnoDB引擎中，4种隔离级别以及各有什么问题？

• 读未提交：存在脏读问题
• 读已提交：存在不可重复读问题,不可重复读针对的是一行或者几行数据
• 可重复读（快照读）：存在幻读问题（幻读一般是删除或者插入了新的数据<整张表的维度>）
• 串行化：没问题

10. InnoDB存储引擎RC、RR隔离级别中的MVCC

此图来自B站视频齐老师

在4种隔离级别只有RC（Oracle默认隔离级别）和RR（MySQL默认隔离级别）的隔离级别在实现的时候使用了mvcc技术

• 多个事务的情况下，如果中间的某个数据修改被回滚，删除或者提交，版本链会断吗？

• 不会的。undo log的版本链不会被立即删除，版本链的删除是由MySQL 确保版本链数据不会被引用以后进行删除的

• 什么是快照读和当前读

• 快照读（使用的是mvcc）：就是最普通的select查询
• 当前读（使用的是行锁+间隙锁）：执行 insert、update、delete或者select 。。。 lock for in share mode产生的视图。
  快照读读取的是读快照里面的数据，是read view里面的数据
  当前读是读取数据库里面最新的内存

在RC的隔离级别下：每次快照读（select） 查询都会产生一个readview
在RR的隔离级别下：仅在第一次执行快照读的情况下生成readview，后面复用。所以在一些情况下RR的级别能解决一些幻读的问题，当然也有例外《例外的情况是在两次快照读之间有当前读，当前读之后会重新生成readview，这时候是产生幻读的情况》

select for update 是加表锁/where 条件中有索引列，这时候加锁就是加行锁（临界锁+间隙锁）

11. 数据库的3大日志及其解决的问题是什么

11.1 undo log

• 什么是undo log它的作用是什么？：

• 当你开启事务修改数据的时候，MySQL数据库为了保证原子性，会把你开启事务那一刻的数据进行备份记录到undo log里，以方便你撤销修改或者修改失败了的情况下恢复数据，即便你有好几条sql都执行成功了，后面有一条执行失败了，也需要把之前的修改进行回滚，原子性就是这样保证的。这就是undo log
• 现在就有一个问题，你的数据都已经修改完了，回滚的时候怎么再回到之前没有修改的数据版本呢?

每个数据都有多个版本，每次修改都会记录一个新的版本进行记录这次修改之前的值是什么。同一条数据把不同版本的数据通过一个回滚指针串起来这就是大佬们常说的undo log 版本链，如果事务正常提交，那就结束；如果事务异常或者手动的回滚，就根据undo log的版本链找回旧版本的数据回滚。
undo log 还有个功能就是配合readview和隐藏字段去实现数据的mvcc（个人理解：mvcc 解决的问题是数据的可见性）

11.2 redo log

redo log出现的原因（它的出现是解决什么问题的）？

• 由于大多数数据都有数据空间局部性的特性，在你select 数据的时候你只查询一条数据，数据库就会把与他相邻的数据都取出来放到buffer pool里面。等你下次取的时候直接从buffer pool取，写数据也是一样先写道buffer pool，不会立即刷盘，而是先把被修改的这个page标记成脏页，后续后台线程再某个时间进行统一刷盘。
• 读完上面出现的原因你貌似还没明白为什么需要redo log<因为我还没说>，buffer pool是在内存中的，一旦出现后台线程还没刷盘，MySQL断电了，这时候一定会丢数据。所以为了解决这个异常，就有了redo log。所以MySQL每次把某个磁盘页做了什么修改都记录到redo log里面，事务提交就刷redo log到磁盘中。
• 上面就是为什么需要redo log（后台线程还没来得及把buffer pool刷盘MySQL就挂了，重启以后buffer pool 数据丢失，MySQL根据redo log 来恢复数据，这就是事务持久性的底层实现<崩溃恢复就是从redo log 中恢复>）。
• 现在还有个一个问题？ 为什么修改完buffer pool不直接进行刷盘呢？而是去写redo log,然后刷盘redo log？

因为刷盘buffer pool 是随机io，而刷盘redo log 是文件追加，文件追加是顺序IO，这就是为什么redo log 只记录磁盘页的物理修改，而不是直接记录数据
现在又有一个问题，redo log 没刷盘，MySQL就崩溃了，怎么办？

• 其实redo log 也有缓存，叫 redo log buffer。redo log会先写入redo log buffer，然后统一把redo log buffer 刷盘。不过这个什么时候刷盘是可以通过 innodb_flash_log_and_trx_commit的参数，它可以控制什么时候刷盘。
  0: 只是把redo log 写入到redo log buffer 中，并不会刷盘《这就比较危险，没刷盘MySQL挂了就可能会丢数据》
  1: 提交事务，redo log buffer 进行刷盘，刷盘完成之后给客户端同步消息，事务执行成功
  2: 把redo log 写入操作系统的文件缓存里（page cache），这时候什么写入文件是由操作系统来控制的

• 最后一个问题。redo log能用来做备份或者数据库的恢复吗？恶劣一点：删库跑路以后能用redo log恢复数据吗？

• 不能。它只能做崩溃恢复，不能做数据库的恢复，因为它是的文件大小是固定的，循环写，边写边擦除。主要是记录事务提交之后没由刷盘的log，对于已经提交的事务redo log，一旦文件超过某个阈值，就滚动删除了。

所以现在就引出了另外一个概念

11.3 bin log

只要由数据库变更（包括表变更）就会记录bin log（现在需要明确的知道redo log 是循环写，bin log是追加写），全量日志，所以bin log 可以做数据备份，主从复制，全量数据的数据恢复（所以你删除跑路的时候一定要把bin log也删掉再跑😏）
redo log 是物理日志，bin log是逻辑日志<类似于sql语句本身>

• 那么bin log 有没有cache呢？

• 也是有的。这里延申一点，要想不丢数据就得记录日志，redis 持久化（AOF & RDB）也是这么做的

11.4 redo log 和 bin log 的一致性问题：

如果一个刷成功了一个刷失败了，这时候数据库挂了怎么办？

这就有个redo log的两阶段提交：将redo log的提交拆分成2个阶段: prepare和commit
整个过程分为这样几个阶段

• 提交redo log（prepare阶段）

此时异常就是prepare阶段之前异常了，此次redo log 和bin log都没有，事务没有成功直接回滚。

• 提交bin log （commit 阶段）

• prepare正常，commit 阶段异常，此时如果redo log 正常，没有bin log，需要回滚事务

• prepare正常，commit 阶段异常，此时如果redo log 正常，记录了bin log，此时redo log和bin log是一致的，MySQL也认为数据是完整的，这时候提交事务
  由此看来两阶段提交，最终还是要看bin log 的完整性

12 索引为什么一般都使用顺序自增的字段当索引？

这是因为在InnoDB存储引擎中，如果往中间插入一行数据，很可能引起page分裂导致的数据移动，进而造成插入时间效率变慢