# MySQL- 面经
# 存储引擎
# 简介
MySQL 目前默认的存储引擎为 InnoDB, 并且 5.7, 8.0 版本只有 InnoDB 是事务性存储引擎
存储引擎相关的命令
# 查看 MySQL 提供的所有存储引擎
show engines;
# 查看 MySQL 当前默认的存储引擎
show variables like '%storage_engine%';
# 查看表的存储引擎
show table status like "table_name";
2
3
4
5
6
7
8
# MyISAM 与 InnoDB 的区别
# 简介
MySQL 5.5 之前, MyISAM 存储引擎是 MySQL 的默认存储引擎
MySQL 5.5 之后, InnoDB 存储引擎是 MySQL 的默认存储引擎
MyISAM 存储引擎优缺点
优点 :
- 性能还行
- 各种特性也不错 (全文索引, 压缩, 空间函数等)
缺点 :
- 不支持事务
- 不支持行级锁
- 崩溃后无法安全恢复
# MyISAM 与 InnoDB 的对比
# 是否支持行级锁
MyISAM 只有表级锁 (table-level locking), 而 InnoDB 支持行级锁 (row-level locking) 和表级锁, 默认为行级锁
小贴士
也就是说, MyISAM 一锁就是锁住了整张表, 这不利于并发, 而 InnoDB 默认只锁需要的行, 并发效率更高
# 是否支持事务
MyISAM 不支持事务
InnoDB 支持事务, 具有 提交 (commit) 和 回滚 (rollback) 事务的能力
# 是否支持外键
MyISAM 不支持外键
InnoDB 支持外键
# 是否支持数据库异常崩溃后安全恢复
MyISAM 不支持数据库异常崩溃后安全恢复
InnoDB 支持数据库异常崩溃后安全恢复
小贴士
使用 InnoDB 的数据库在异常崩溃后, 数据库重新启动的时候会保证数据库恢复到崩溃前的状态, 这个恢复的过程依赖于 redo log
扩展
MySQL InnoDB存储引擎使用redo log(重做日志) 保证事务的 持久性, 使用undo log(回滚日志) 保证事务的 原子性MySQL InnoDB存储引擎通过 锁机制, MVVC 等手段来保证事务的隔离性 (默认支持的隔离级别是可重复度)- 保证了事务的 持久性, 原子性, 隔离性 之后, 一致性 才能得到保障
# 是否支持 MVVC
MyISAM 不支持 MVVC
InnoDB 支持 MVVC
小贴士
MVCC 可以看作是行级锁的一个升级, 可以有效减少加锁操作, 提高性能
# 锁机制和 InnoDB 锁算法
# MyISAM 和 InnoDB 存储引擎使用的锁
MyISAM采用表级锁 (table-level locking)InnoDB采用行级锁 (row-level locking) 和表级锁, 默认为行级锁
# 表级锁和行级锁对比
- 表级锁 :
MySQL中锁定 粒度最大 的一种锁, 对当前操作的整张表加锁. 实现简单, 资源消耗也比较少, 加锁快, 不会出现死锁, 出发锁冲突概率最高, 并发度最低,MyISAM和InnoDB存储引擎都支持表级锁 - 行级锁 :
MySQL中锁定 粒度最小 的一种锁, 只针对当前操作的行加锁, 行级锁能大大减少数据库操作的冲突, 加锁粒度最小, 并发度高, 加锁的开销最大, 加锁慢, 会出现死锁
表级锁和行级锁的区别
- 开销 : 表级锁加锁开销少, 行级锁加锁开销大
- 加锁粒度 : 表级锁加锁粒度最大, 行级锁加锁粒度最小
- 是否会出现死锁 : 表级锁不会出现死锁, 行级锁会出现死锁
- 加锁速度 : 表级锁加锁速度快, 行级锁加锁速度慢
- 并发度 : 表级锁并发度低, 行级锁并发度高
# InnoDB 存储引擎的锁的算法
InnoDB 存储引擎的锁的算法有三种 :
Record lock: 记录锁, 单个行记录上的锁Gap lock: 间隙锁, 锁定一个范围, 不包括记录本身Next-key lock:Record+Gap临键锁, 锁定一个范围, 包含记录本身
# 一条 SQL 语句在 MySQL 中如何被执行的?
# MySQL 基础架构分析
# MySQL 基本架构概览
下图是 MySQL 的一个简要架构图, 从下图你可以很清晰的看到用户的 SQL 语句在 MySQL 内部是如何执行的
图示
图解
- 连接器 : 身份认证和权限相关 (登录
MySQL的时候) - 查询缓存 : 执行查询语句的时候, 会先查询缓存 (
MySQL8.0 版本后移除) - 分析器 : 没有命中缓存的话,
SQL语句就会经过分析器, 分析器说白了就是要先看你的SQL语句要干嘛, 再检查你的SQL语句语法是否正确 - 优化器 : 按照
MySQL认为最优的方案去执行
简单来说 MySQL 主要分为 Server 层和存储引擎层 :
- Server 层 : 主要包括连接器, 查询缓存, 分析器, 优化器, 执行器等, 所有跨存储引擎的功能都在这一层实现, 比如存储过程, 触发器, 视图, 函数等, 还有一个通用的日志模块
binlog日志模块 - 存储引擎 : 主要负责数据的存储和读取, 采用可以替换的插件式架构, 支持
InnoDB、MyISAM、Memory等多个存储引擎, 其中InnoDB引擎有自有的日志模块redolog模块. 现在最常用的存储引擎是InnoDB, 它从MySQL 5.5.5版本开始就被当做默认存储引擎了
# Server 层基本组件介绍
# 连接器
连接器主要和身份认证和权限相关的功能相关, 就好比一个级别很高的门卫一样
主要负责用户登录数据库, 进行用户的身份认证, 包括校验账户密码, 权限等操作, 如果用户账户密码已通过, 连接器会到权限表中查询该用户的所有权限, 之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据, 也就是说, 后续只要这个连接不断开, 即使管理员修改了该用户的权限, 该用户也是不受影响的
# 查询缓存 (MySQL 8.0 版本后移除)
查询缓存主要用来缓存我们所执行的 SELECT 语句以及该语句的结果集
连接建立后, 执行查询语句的时候, 会先查询缓存, MySQL 会先校验这个 sql 是否执行过, 以 Key - Value 的形式缓存在内存中, Key 是查询预计, Value 是结果集. 如果缓存 key 被命中, 就会直接返回给客户端, 如果没有命中, 就会执行后续的操作, 完成后也会把结果缓存起来, 方便下一次调用. 当然在真正执行缓存查询的时候还是会校验用户的权限, 是否有该表的查询条件
MySQL 查询不建议使用缓存, 因为查询缓存失效在实际业务场景中可能会非常频繁, 假如你对一个表更新的话, 这个表上的所有的查询缓存都会被清空. 对于不经常更新的数据来说, 使用缓存还是可以的
所以, 一般在大多数情况下我们都是不推荐去使用查询缓存的
MySQL 8.0 版本后删除了缓存的功能, 官方也是认为该功能在实际的应用场景比较少, 所以干脆直接删掉了
# 分析器
MySQL 没有命中缓存, 那么就会进入分析器, 分析器主要是用来分析 SQL 语句是来干嘛的, 分析器也会分为几步 :
第一步, 词法分析, 一条 SQL 语句有多个字符串组成, 首先要提取关键字, 比如 select, 提出查询的表, 提出字段名, 提出查询条件等等. 做完这些操作后, 就会进入第二步
第二步, 语法分析, 主要就是判断你输入的 sql 是否正确, 是否符合 MySQL 的语法
完成这 2 步之后, MySQL 就准备开始执行了, 但是如何执行, 怎么执行是最好的结果呢? 这个时候就需要优化器上场了
# 优化器
优化器的作用就是它认为的最优的执行方案去执行 (有时候可能也不是最优, 这篇文章涉及对这部分知识的深入讲解), 比如多个索引的时候该如何选择索引, 多表查询的时候如何选择关联顺序等
可以说, 经过了优化器之后可以说这个语句具体该如何执行就已经定下来
# 执行器
当选择了执行方案后, MySQL 就准备开始执行了, 首先执行前会校验该用户有没有权限, 如果没有权限, 就会返回错误信息, 如果有权限, 就会去调用引擎的接口, 返回接口执行的结果
# 语句分析
# 查询语句
sql 语句
select * from tb_student A where A.age = '18' and A.name = ' 张三 ';
执行流程
先检查该语句是否有权限, 如果没有权限, 直接返回错误信息, 如果有权限, 在
MySQL8.0版本以前, 会先查询缓存, 以这条sql语句为key在内存中查询是否有结果, 如果有直接缓存, 如果没有, 执行下一步通过分析器进行词法分析, 提取
sql语句的关键元素, 比如提取上面这个语句是查询select, 提取需要查询的表名为tb_student, 需要查询所有的列, 查询条件是这个表的id='1'. 然后判断这个sql语句是否有语法错误, 比如关键词是否正确等等, 如果检查没问题就执行下一步.接下来就是优化器进行确定执行方案, 上面的
sql语句, 可以有两种执行方案 :- 先查询学生表中姓名为"张三”的学生, 然后判断是否年龄是 18.
- 先找出学生中年龄 18 岁的学生, 然后再查询姓名为"张三”的学生.
那么优化器根据自己的优化算法进行选择执行效率最好的一个方案 (优化器认为, 有时候不一定最好) . 那么确认了执行计划后就准备开始执行了
进行权限校验, 如果没有权限就会返回错误信息, 如果有权限就会调用数据库引擎接口, 返回引擎的执行结果
# 更新语句
sql 语句
update tb_student A set A.age = '19' where A.name = ' 张三 ';
执行流程
其实这条语句也基本上会沿着上一个查询的流程走, 只不过执行更新的时候肯定要记录日志啦, 这就会引入日志模块了, MySQL 自带的日志模块是 **bin log (归档日志) ** , 所有的存储引擎都可以使用, 我们常用的 InnoDB 引擎还自带了一个日志模块 **redo log (重做日志) **, 我们就以 InnoDB 模式下来探讨这个语句的执行流程. 流程如下 :
- 先查询到张三这一条数据, 如果有缓存, 也是会用到缓存
- 然后拿到查询的语句, 把
age改为 19, 然后调用引擎API接口, 写入这一行数据,InnoDB引擎把数据保存在内存中, 同时记录redo log, 此时redo log进入prepare状态, 然后告诉执行器, 执行完成了, 随时可以提交 - 执行器收到通知后记录
bin log, 然后调用引擎接口, 提交redo log为提交状态 - 更新完成
小贴士
这是因为最开始 MySQL 并没有 InnoDB 引擎 (InnoDB 引擎是其他公司以插件形式插入 MySQL 的) , MySQL 自带的引擎是 MyISAM, 但是我们知道 redo log 是 InnoDB 引擎特有的, 其他存储引擎都没有, 这就导致会没有 crash-safe 的能力(crash-safe 的能力即使数据库发生异常重启, 之前提交的记录都不会丢失), binlog 日志只能用来归档
并不是说只用一个日志模块不可以, 只是 InnoDB 引擎就是通过 redo log 来支持事务的. 那么, 又会有同学问, 我用两个日志模块, 但是不要这么复杂行不行, 为什么 redo log 要引入 prepare 预提交状态?这里我们用反证法来说明下为什么要这么做?
- 先写
redo log直接提交, 然后写bin log, 假设写完redo log后, 机器挂了,binlog日志没有被写入, 那么机器重启后, 这台机器会通过redo log恢复数据, 但是这个时候binlog并没有记录该数据, 后续进行机器备份的时候, 就会丢失这一条数据, 同时主从同步也会丢失这一条数据 - 先写
bin log, 然后写redo log, 假设写完了bin log, 机器异常重启了, 由于没有redo log, 本机是无法恢复这一条记录的, 但是bin log又有记录, 那么和上面同样的道理, 就会产生数据不一致的情况
如果采用 redo log 两阶段提交的方式就不一样了, 写完 bin log 后, 然后再提交 redo log 就会防止出现上述的问题, 从而保证了数据的一致性. 那么问题来了, 有没有一个极端的情况呢?假设 redo log 处于预提交状态, bin log 也已经写完了, 这个时候发生了异常重启会怎么样呢? 这个就要依赖于 MySQL 的处理机制了, MySQL 的处理过程如下 :
- 判断
redo log是否完整, 如果判断是完整的, 就立即提交 - 如果
redo log只是预提交但不是commit状态, 这个时候就会去判断bin log是否完整, 如果完整就提交redo log, 不完整就回滚事务
这样就解决了数据一致性的问题
# 总结
- MySQL 主要分为 Server 层和引擎层, Server 层主要包括连接器、查询缓存、分析器、优化器、执行器, 同时还有一个日志模块 (binlog) , 这个日志模块所有执行引擎都可以共用, redolog 只有 InnoDB 有
- 引擎层是插件式的, 目前主要包括, MyISAM,InnoDB,Memory 等
- 查询语句的执行流程如下 : 权限校验 (如果命中缓存) ---> 查询缓存 ---> 分析器 ---> 优化器 ---> 权限校验 ---> 执行器 ---> 引擎
- 更新语句执行流程如下 : 分析器 ----> 权限校验 ----> 执行器 ---> 引擎 ---> redo log (prepare 状态) ---> binlog ---> redo log (commit状态)