存储引擎
Mysql 中的存储引擎
-
查询存储引擎的命令 show engines;
-
Archive
只支持 insert 与select操作,
不支持索引
不支持事务
适用于存储需要长期保存,但是很少访问的数据,例如 历史日志 -
BlackHole
不存储数据,但是会记录写入操作
适用于性能测试 语言验证等情况 -
MyISAM
高性能读操作
表级别锁
不支持事务
不支持外键
索引存储结构采用的B+tree 非聚集索引
生成文件为三个文件 表结构文件.sdi 数据文件.MYD 索引文件.MYI
支持全文索引
不支持数据缓存
适用于读多写少 不需要事务 不需要外键的场景 -
InnoDB 重点
支持外键
支持事务
支持行级锁
支持MVCC多版本并发控制
索引结构采用的B+tree结构 聚集索引(主键与数据存储在一起)
适用于对数据完整性与一致性要求严格的场景 -
Memory
缓存存储,数据存储在缓存中,不存到磁盘上
读写速度快
支持事务
支持索引
索引结构 支持hash 与btree 默认采用hash -
CSV
以CSV格式存储数据
不支持外键、事务、索引
事务
-
什么是事务
事务是一种机制,用来保证数据的一致性与完整性,要么全部完成,要么全部失败 -
事务的特性
ACID 原子性 一致性 隔离性 持久性
A 原子性: 通过undolog 来实现的
C 一致性: 通过其他三个特性来保证数据的一致性
I 隔离性: 通过锁与MVCC机制来保证数据的隔离性
D 持久性: 通过redolog来实现的 -
事务的操作sql
start transaction; #开启事务
commit; 提交事务
rollback; 回滚事务 -
事务的隔离级别
读未提交 read-uncommitted: 会产生不可重复读 幻读 脏读
读已提交 read-committed: orcale 默认隔离级别 会产生不可重复读 幻读
可重复读 repeatable read: Mysql 默认隔离级别 会产生重复读 幻读
序列化 serializable: 最高的隔离级别, 通过强制事务排序 来解决幻读, 在所有的读操作都会默认机上读锁
三大日志
-
undo log 回滚日志
undolog 用户保证数据的原子性
undolog 会存储修改数据之前的旧值,用户事务回滚 或者 MVCC机制旧数据构建的支持 -
redo log 重做日志
redolog 是来保证事务持久性的日志文件
在事务提交的时,日志缓存区的内容会先写到redolog中,然后再写入到磁盘数据文件中,
磁盘的随机写比较耗时,而redolog 是顺序写,redolog的顺序写高于随机写,所以会先将修改记录写到redolog中
当数据库重启的时候会先读取redolog中的记录重新执行这些修改操作,将数据恢复到提交后的状态,保证数据的一致性持久性
- bin log 二进制日志
binlog是记录了数据库表结构与表数据的增删改操作的二进制文件
用于数据恢复
主从复制
mysql5.7之后是默认开启的
MVCC 多版本并发控制
-
MVCC多版本并发控制
MVCC: 是一种并发控制技术,实现数据库的高效并发访问,允许在同一时间内多个事务对同一数据进行读取操作 -
MVCC 工作原理
首选需要了解
事务ID: 在Innodb存储引擎中,会为每一个事务分配一个唯一的事务ID,这个ID是递增的
版本号: Innodb中每行数据上都会有隐藏的创建版本号与删除版本号, 这个数据的版本号就是操作数据的事务ID
MVCC 是 通过每行数据上隐藏的创建版本号与删除版本号结合事务ID来做到的, 通过事务ID 与数据的创建版本号、删除版本号的比较来判断出这个事务可以看到的数据范围 ,
当前事务所能看到的数据范围是: 当前事务ID >= 创建版本号 并且当前事务ID < 删除版本号 的数据,如果版本号大于当前事务ID, 那么会通过undo log 来构建旧版本数据,来保证读取的数据是这个事务之前的数据
- MVCC 用途
保证并发性能
保证事务的隔离性
索引结构
-
Hash
Hash的结构是数组+链表的形式, 不支持范围查询,存在hash冲突的情况 -
红黑树
红黑树又叫二叉查找树, 节点下边只有两个分支,数据多的情况层级会很高,影响查询 -
Btree
Btree节点支持存储多个数据,分支有多个分支,节点存储的可能是索引或者是数据或者是数据的物理地址 -
B+tree
B+tree节点支持存储多个数据,分支也有多个分支,主键索引树,非叶子节点是冗余出来的索引,叶子节点存储的是索引与数据,由于非叶子节点存储的之后索引数据 相较于Btree可以存储更多的索引,层级也会更低, 同时叶子节点之间有双向链表关联,更便于范围查询
SQL 优化
- 创建索引创建在不为null 并且重复值少的列, 索引字段尽量不频繁改动
- 通过explain 执行计划 来查看sql是否命中索引
- like 与 组合索引 遵从最左前缀原则
- 使用 EXISTS 替代 IN
- 调整MySQL内存分配
- 索引列不要使用表达式或者函数操作会破会索引值的有序性 会失效
- 注意类型的隐式转换,比如数值类型 使用字符串判断
