技术总结 | MySQL面试知识点

1.存储引擎

Mysql 中的存储引擎
查询存储引擎的命令 show engines;

1.1 Archive

1. 只支持 insert 与select操作,
2. 不支持索引
3. 不支持事务
4. 适用于存储需要长期保存,但是很少访问的数据,例如 历史日志

1.2 BlackHole

1. 不存储数据,但是会记录写入操作
2. 适用于性能测试 语言验证等情况

1.3 MyISAM

1. 高性能读操作
2. 表级别锁
3. 不支持事务
4. 不支持外键
5. 索引存储结构采用的B+tree 非聚集索引
6. 生成文件为三个文件 表结构文件.sdi 数据文件.MYD 索引文件.MYI
7. 支持全文索引
8. 不支持数据缓存
9. 适用于读多写少 不需要事务 不需要外键的场景

1.4 InnoDB (重点记一下哦)

1. 支持外键
2. 支持事务
3. 支持行级锁
4. 支持MVCC多版本并发控制
5. 索引结构采用的B+tree结构 聚集索引(主键与数据存储在一起)
6. 适用于对数据完整性与一致性要求严格的场景

1.5 Memory

1. 缓存存储,数据存储在缓存中,不存到磁盘上
2. 读写速度快
3. 支持事务
4. 支持索引
5. 索引结构 支持hash 与btree 默认采用hash

1.6 CSV

1. 以CSV格式存储数据
2. 不支持外键、事务、索引

2. 事务

2.1. 什么是事务

事务是一种机制,用来保证数据的一致性与完整性,要么全部完成,要么全部失败

2.2. 事务的特性

** ACID 原子性 一致性 隔离性 持久性 **

• A 原子性: 通过undolog 来实现的
• C 一致性: 通过其他三个特性来保证数据的一致性
• I 隔离性: 通过锁与MVCC机制来保证数据的隔离性
• D 持久性: 通过redolog来实现的

2.3. 事务的操作sql

start transaction; #开启事务
commit; 提交事务
rollback; 回滚事务

2.4. 事务的隔离级别

• 读未提交 read-uncommitted: 会产生不可重复读 幻读 脏读
• 读已提交 read-committed: orcale 默认隔离级别 会产生不可重复读 幻读
• 可重复读 repeatable read: Mysql 默认隔离级别 会产生重复读 幻读
• 序列化 serializable: 最高的隔离级别, 通过强制事务排序 来解决幻读, 在所有的读操作都会默认机上读锁

3.三大日志

3.1. undo log 回滚日志

undolog 用户保证数据的原子性
undolog 会存储修改数据之前的旧值,用户事务回滚 或者 MVCC机制旧数据构建的支持

3.2. redo log 重做日志

redolog 是来保证事务持久性的日志文件

在事务提交的时,日志缓存区的内容会先写到redolog中,然后再写入到磁盘数据文件中,

磁盘的随机写比较耗时,而redolog 是顺序写,redolog的顺序写高于随机写,所以会先将修改记录写到redolog中

当数据库重启的时候会先读取redolog中的记录重新执行这些修改操作,将数据恢复到提交后的状态,保证数据的一致性持久性

3.3. bin log 二进制日志

• binlog是记录了数据库表结构与表数据的增删改操作的二进制文件
• 用于数据恢复
• 主从复制
• mysql5.7之后是默认开启的

4. MVCC 多版本并发控制

4.1. 介绍

MVCC: 是一种并发控制技术,实现数据库的高效并发访问,允许在同一时间内多个事务对同一数据进行读取操作

4.2. MVCC 工作原理

首选需要了解
事务ID: 在Innodb存储引擎中,会为每一个事务分配一个唯一的事务ID,这个ID是递增的
版本号: Innodb中每行数据上都会有隐藏的创建版本号与删除版本号, 这个数据的版本号就是操作数据的事务ID

MVCC 是 通过每行数据上隐藏的创建版本号与删除版本号结合事务ID来做到的, 通过事务ID 与数据的创建版本号、删除版本号的比较来判断出这个事务可以看到的数据范围 ,
当前事务所能看到的数据范围是: 当前事务ID >= 创建版本号 并且当前事务ID < 删除版本号 的数据,如果版本号大于当前事务ID, 那么会通过undo log 来构建旧版本数据,来保证读取的数据是这个事务之前的数据

4.3. MVCC 用途

保证并发性能
保证事务的隔离性

5. 索引结构

5.1. Hash

Hash的结构是数组+链表的形式, 不支持范围查询,存在hash冲突的情况

5.2. 红黑树

红黑树又叫二叉查找树, 节点下边只有两个分支,数据多的情况层级会很高,影响查询

5.3. Btree

Btree节点支持存储多个数据,分支有多个分支,节点存储的可能是索引或者是数据或者是数据的物理地址

5.4. B+tree

B+tree节点支持存储多个数据,分支也有多个分支,主键索引树,非叶子节点是冗余出来的索引,叶子节点存储的是索引与数据,由于非叶子节点存储的之后索引数据 相较于Btree可以存储更多的索引,层级也会更低, 同时叶子节点之间有双向链表关联,更便于范围查询

6. SQL 优化

1. 创建索引创建在不为null 并且重复值少的列, 索引字段尽量不频繁改动
2. 通过explain 执行计划 来查看sql是否命中索引
3. like 与 组合索引 遵从最左前缀原则
4. 使用 EXISTS 替代 IN
5. 调整MySQL内存分配
6. 索引列不要使用表达式或者函数操作会破会索引值的有序性 会失效
7. 注意类型的隐式转换,比如数值类型 使用字符串判断

有总结的不对的地方/或者问题 请指正, 我在努力中