3分钟
MySQL 面试问题整理
数据库的三大范式
- 第一范式：表中的每一列都不可再拆分
- 第二范式：在第一范式的基础上，表中的每一列都和主键相关
- 第三范式：在第二范式的基础上，表中的每一列都和主键直接相关，不能间接相关
MySQL 常见的存储引擎
存储引擎
存储引擎是在数据库中进行创建、读取、修改和删除数据的底层组件。不同的存储引擎提供不同的存储机制，而且还有许多不同的功能，在 MySQL 中我们可以使用show engines;来查看所支持的存储引擎状况。
MyISAM
MyISAM 是 MySQL 5.5 之前默认的存储引擎，性能高但不支持事务，也不支持外键。
- 高性能读取
- 保存了表内记录条数，使用
count时不会查全表 - 只支持表锁，虽然开销小、加锁快，但容易发生冲突
InnoDB
InnoDB 是一个支持事务的存储引擎，是现在 MySQL 的默认存储引擎。
- 支持事务、外键
- 支持行级锁，粒度小，并发处理能力更强
数据库事务
事务是指具有 ACID 特性的一组操作，使用commit来提交一个事务，用rollback来回滚一个事务。
- 原子性（Atomicity）
原子性是指事务被视为不可分割的最小执行单元，简单地说就是一个事务要么全成功，要么全失败，失败时会利用回滚日志（Undo Log）将数据会滚到事务发生之前的状态。
- 一致性（Consistency）
一致性是指在事务执行前后，数据库的完整性约束没有被破坏。
A（100元） 向 B（0元） 转账 100 元，转账前后 A 和 B 两人共 100 元不变
- 隔离性（Isolation）
隔离性是指一个事务所修改的内容在提交之前对其他事务是不可见的，InnoDB 依靠 MVCC 多版本并发控制来保证隔离性。
- 持久性（Durability）
持久性是指当事务提交后，这些修改应该永久性的保存到数据库（文件系统）中，即使系统崩溃，执行结果也不能丢失。当系统发生崩溃时使用重做日志（Redo Log）来恢复数据。
并发情况下会出现哪些问题
丢失修改
是指两个事务同时修改一条记录，可能会造成其中一个事务的结果被另一个覆盖。
脏读
是指在一个事务中读取到了另外一个事务修改未提交的数据。
不可重复读
是指在一个事务中读取到别的事务已提交的修改，此时会造成事务中前后若干次读取的数据不一致。
幻读
和不可重复读比较类似，幻读是指在一个事务中读取到另外一个事务插入或者删除的数据，此时当前事务中前后若干次读取的数据记录条数不一致。
事务的隔离级别有哪些？分别解决了什么问题
| 隔离级别 | 描述 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|---|
| 读未提交（Read Uncommitted） | 事务中的修改即使没有提交对其他事务也是可见的 | ❌ | ❌ | ❌ |
| 读已提交（Read Committed） | 只能读取到已经提交了的事务修改的数据 | ✅ | ❌ | ❌ |
| 可重复读（Repeatable Read） | 保证在事务中多次读取同一数据结果一致 | ✅ | ✅ | ❌ |
| 串行化（Serializable） | 强制所有的事务串行化执行，不会有任何并发 | ✅ | ✅ | ✅ |
有哪些类型的锁
锁是协调多个进程或线程并发访问某一资源的一种机制，在数据库中是用于保证数据并发访问一致性的一种手段。
按锁的粒度分
行锁
行锁的锁定粒度是最小的，只需对操作的当前行进行锁定，能够支持较大的并发量。在 InnoDB 引擎中，只有通过索引检索数据的时候才能使用行锁。
行锁的获取分为两步：
- 锁住主键索引
- 锁住非主键索引
因此行锁在并发量大的时候容易出现死锁的问题。
表锁
表锁是粒度最大的一种锁，会对当前操作的整张表加锁，所以不适合高并发场景。在 InnoDB 引擎中，当无法通过索引检索数据的时候会使用表锁。
页锁
页锁是一种锁粒度介于行锁和表锁中间的一种锁，在 MySQL 中仅有 BDB 引擎支持页锁。
按锁的类型分
读写锁
- 互斥锁（X 锁）：又称写锁
- 共享锁（S 锁）：又称读锁
从两个锁的名字就能看出其特点：当一个事务对数据加了写锁，事务本身可以对加了锁的对象读取和修改，但其他事务只能读取不能修改；当一个事务对数据加了读锁，所有事务都可以对其读取，但不能修改。
意向锁
意向锁是一种表级锁，可以更容易地支持多粒度封锁。事务 A 持有表中一条数据的写锁，此时事务 B 想要对表加写锁，此时会发生冲突，所以在事务 B 加写锁之前要对表中记录逐行扫描检测是否有其他的写锁，这样费时又费力；所以引入了意向锁这个概念，一个事务要想获取表中记录的锁，那就必须先获取表的对应意向锁，此时其他事务加锁前只需检测表中是否有其他意向锁即可。
- 意向互斥锁（IX 锁）
事务要想获取某些记录的写锁，必需先获取表的意向互斥锁。
- 意向共享锁（IS 锁）
事务要想获取某些记录的共享锁，必须先获取表的意向共享锁。
| 各种锁的兼容情况 | 互斥锁 | 共享锁 | 意向互斥锁 | 意向共享锁 |
|---|---|---|---|---|
| 互斥锁 | ❌ | ❌ | ❌（行级别的可以兼容） | ✅ |
| 共享锁 | ❌ | ✅ | ❌（行级别的可以兼容） | ✅ |
| 意向互斥锁 | ❌（行级别的可以兼容） | ❌ | ✅ | ✅ |
| 意向共享锁 | ❌（行级别的可以兼容） | ✅ | ✅ | ✅ |
锁有哪些算法实现
Record Lock（记录锁）
记录锁就是加在索引记录上的锁，通过索引检索数据时使用的都是记录锁。
Gap Lock（间隙锁）
锁定索引之间的间隙，不包含索引本身。例如当一个事务执行以下语句，其它事务就不能在 t.c 中插入 15。
SELECT c FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE;
Next-Key Lock
是记录锁和间隙锁的结合，它锁定一个前开后闭的区间，主要用于解决幻读的问题。
MVCC 多版本并发控制
多版本并发控制（Muti-Version Concurrency Control）是 InnoDB 引擎实现隔离级别的一种具体方式，主要用于实现读已提交和可重复读两种隔离级别。
对于读未提交要求每次读取最新的数据，所以无需MVCC；对于串行化需要对所有事物加锁，单纯使用 MVCC 无法解决。
基本思想
MVCC 利用多版本的思想，使读操作读取旧版本快照，而写操作更新最新的快照，没有互斥关系。在 MVCC 中事务的修改操作会使得记录增加一个快照，为了解决脏读和不可重复读，MVCC 规定只能读取以提交的快照。
原理
MVCC 的多版本指的是多个版本的快照，快照存储在 Undo Log 日志中，利用回滚指针把数据所有版本的快照串起来。MVCC 维护了一个 ReadView 的结构，该结构记录了系统中所有未提交的事务 ID，同时记录这些事务 ID 的最小值和最大值用于判断。
当执行 Select 操作时，会根据数据行快照的事务 ID 和 ReadView 中的事务 ID 进行比较：
- 当前快照事务 ID 小于 ReadView 中事务 ID 的最小值，说明该快照是在当前所有未提交的事务之前修改的，可用
- 当前快照事务 ID 大于 ReadView 中事务 ID 的最大值，说明该快照是在事务启动之后提交的，所以不可用
- 当前快照事务 ID 介于 ReadView 中事务 ID 最大值和最小值之间则要根据隔离级别来判断快照是否可用
- 读已提交：如果快照事务 ID 在 ReadView 中，说明事务未提交，不可用；反之则可用
- 可重复读：不可用，因为可用是对所有事务的，此时会造成不可重复读的现象
如果当前快照不可用时，会根据 Undo Log 中的回滚指针找到上一版本的快照，重复上述的判断。
索引的类型
按结构分类
-
B+ Tree 索引
-
Hash 索引
-
Full-Text 索引
在MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；5.6 之后的版本 InnoDB 和 MyISAM 都支持全文索引；只有文本类型的字段才可以使用全文索引。
按存储方式分类
- 聚簇索引：索引树的叶节点包含了全部字段
- 辅助索引：索引树的叶子节点仅记录主键的值而不是地址
按字段分类
- 主键索引
- 普通索引
- 前缀索引：截取字段的前 n 个字符建立的索引
- 覆盖索引：从辅助索引中就可以得到想要的结果
按字段个数分类
- 单列索引
- 组合索引（联合索引）
索引有哪些优缺点
优点：
- 加快查询速度，减少查询时间
缺点：
- 维护索引比较耗时
- 索引会占用额外的空间
- 在增删改的时候要同步修改索引
索引使用的策略及优化
- 全列匹配：当按照索引中所有列精确查找时会成功使用索引。原则上语句中索引的顺序会对是否能匹配上索引有影响，但 MySQL 优化器会自动帮你调整为索引列的顺序。
- 最左匹配原则：在索引
<a,b,c>中使用了<a,b>，此时可以使用索引 - 使用了索引列的精确匹配，但缺少中间某个索引列：比如在索引
<a,b,c>中仅使用了<a,c>，此时可以使用索引，但仅有a列可以匹配，c列仍需全表扫描 - 匹配索引列的前缀：使用
like字段时如果通配符出现在最前面的时候不能使用索引，通配符仅能出现在后面，例如：like 'xxx%'可以使用索引，但like '%xxx'不可以 - 查询条件中含有函数或者表达式：不能匹配索引
- 出现了隐式类型转换时不能使用索引，比如查询一个
varchar索引列的时候没有加两侧的单引号，此时不会命中索引 or左右两侧必须都为索引列，否则不能使用索引
索引列的选择
- 表记录表较少的时候可以不建立索引
- 索引的选择性较低时不建立索引
索引的选择性是指索引列不重复的字段值数量占中数量的比例
B Tree 和 B+ Tree 索引的区别
- B+ Tree 的非叶子结点不存储数据，只存储节点信息。而 B Tree 的非叶子结点也会存储数据。所以 B+ Tree 单个节点数据量更小，相同 IO 次数下可以找到更多节点。
- B+ Tree 的叶子结点增加了指针，仿佛一整个单链表，非常适合 MySQL 的范围查找
选用 B+ Tree 的原因
- 不使用 Hash 的原因是虽然 Hash 精确查找效率很高，但范围查找效率很糟糕
- 二叉平衡树/红黑树在结点数量大的时候树的深度会很大
- B Tree/B+ Tree 的每一个节点的大小设置为一个页的大小，这样一个节点只需一次磁盘 IO 即可全部载入
- 因为 B+ Tree 的非叶子结点不存储数据，这样在相同大小下可以存储更多子节点信息，这样会大幅提高我们查找的效率
- 同时 B+ Tree 叶子结点之间是有指针的，这样可以大幅增加我们范围查找的效率
为什么InnoDB表必须有主键，并且推荐使用整型的自增主键
- 因为 InnoDB 引擎的数据文件要按照主键聚集，所以必须要有主键。如果不主动指定主键，引擎会帮你创建一个隐藏的主键列。
- 整型的主键更容易排序生成 B+ Tree
- 自增主键可以有效的减少 B+ Tree 的结点分裂，因为直接放到最后即可
MySQL 的日志类型
Redo Log
用来实现和事务持久化相关的日志，由 Redo Log Buffer 和 Redo Log File 两部分组成。Redo Log 随着事务的创建而产生，但事务提交后不一定写入 Redo Log File，因为 Redo Log 要先写入 Redo Log Buffer 然后根据不同的磁盘写入策略（innodb_flush_log_at_trx_commit）在不同的时机写入文件中。
innodb_flush_log_at_trx_commit 字段取值：
-
0：每次事务提交时写入 Redo Log Buffer，每秒从 Redo Log Buffer 写入到磁盘中
-
1：每次事务提交时写入 OS Buffer，同时写入到磁盘
-
2：每次事务提交时写入 OS Buffer，每秒从 OS Buffer 写入到磁盘中
Undo Log
主要用来保证事务的原子性，同时和 MVCC 有着紧密联系。Undo Log 产生于数据修改之前，但并不会在数据修改后删除，而是将 Undo Log 放到一个链表中，是否删除由 Purge 线程决定
BinLog
二进制文件主要用于进行基于时间点的恢复及主从复制环境的建立。BinLog 有三种格式：
- Statement：记录每次执行的 SQL 语句
- Row：记录每行数据的修改
- Mixed：默认情况下采用 Statement 格式，在某些情况会切换到 Row 格式
- 使用函数或者系统变量时
- 含有 AUTO_INCREMENT 列时
Slow Query Log
记录执行时间较长的语句，超时时间需自己配置。通过show variables like '%slow_query_log%'可以查看相关参数值。
MySQL 死锁
在 MySQL 中出现死锁的条件
- 两个及两个以上的事务
- 每个事务都已经持有锁，并且申请新的锁
- 锁同时只能被一个事务占有
- 事务间因为持有锁和申请锁导致彼此循环等待
如何避免出现死锁
- 合理设计索引，尽可能使语句利用索引
- 避免大事务
- 以固定的顺序访问表和行
- 尽量按主键查找记录
- 在高并发的系统中不要显示加锁，例如
select xxx from for update
MySQL 主从复制
主从复制是指数据可以从数据库的主节点复制到一个或多个从节点中，可以给数据库提供一个有效的备份方式，同时可以有效的缓解数据库的压力。
实现原理
- 主库将变更内容写入 binlog
- 从库启动一个 IO 线程向主库请求 binlog，主库根据从库提交的偏移量发送 binlog
- 从库获取到 binlog 后写入到 Relay Log（中继日志）
- 从库启动 SQL 线程根据 Relay Log 中的内容进行重放
同步模式
- 异步模式：MySQL 默认的同步模式，主库在执行完客户端的请求后直接响应客户端，不管 binlog 有没有成功同步到从库
- 半同步模式：主库在执行完客户端的请求等至少收到一个从库返回的确认信息再响应客户端
- 全同步模式：主库在执行完客户端的请求等收到所有从库返回的确认信息再响应客户端
- 并行模式：从库启动多个线程分别读取 Relay Log 中不同数据库的日志，并行重放
主要用途
- 热备
- 读写分离
- 高可用
如何解决主从复制的延时
- 分库，降低主库的压力
- 并行复制
MySQL 的内连接、左连接、右连接
- 内连接：保留两张表完全匹配的结果集
- 左连接：返回左边所有的行，无论右边是否有
- 右连接：返回右边所有的行，无论左边是否有