MYSQL
基础
几个重要的概念
- 脏读：事务读取到了其他事务未提交的数据，当发生回滚时，出错
- 可重复读：在一个事务中重复读取某条数据结果是相同的
- 不可重复读：在一个事务中重复读取某条数据结果是不同的
- 幻读： 在事务中读取到了新插入的数据
隔离级别
- 读未提交：允许事务读到其他事务未commit的数据，最低的隔离级别，有脏读
- 读已提交：一个事务只能看见已经提交事务所做的改变，有不可重复读和虚读的风险；大多数数据库默认的隔离级别
- 可重复读：一个事务中多个实例在并发读取数据的时候会读取到一样的数据，可能产生幻读的问题，innodb存储引擎通过MVCC机制解决了该问题
- 序列化：强制按照序列id提交
数据库的四大特性
- 原子性：要么全部成功，要么全部失败
- 一致性：
- 隔离性：隔离性指在并发环境下，每个事务的执行都相互隔离，互不干扰
- 持久性：持久性要求一旦事务提交成功，对数据库的修改操作应该被永久保存
索引
索引的底层数据结构
-
二叉树是一种特殊的树结构，它由一组称为节点的元素组成，这些节点通过边连接起来。每个节点最多可以有两个子节点，分别称为左子节点和右子节点。这种特性使得二叉树的结构具有层次性。
-
满二叉树，所有层的节点数都是满的
-
完全二叉树，除了最后一层节点数都是满的，且最后一层靠左排列
-
二叉查找树，满足二叉树的条件下，左子树的节点值总是小于根的节点值，右子树的节点值总是大于根的节点值
-
平衡二叉树(AVL树)：首先符合二叉查找树的定义，其次必须满足任何节点的两个子树的高度之差的绝对值不超过 1
-
多路：一颗树每个节点最多可以拥有大于2个子节点的树
-
平衡树： 一颗树左子树和右子树保持相对平衡，不会出现一边层级特别多，一边层级特别少的情况
Hash
对key进行一次hash计算，可以快速获取磁盘文件的索引，对于指定索引查找文件非常快，对于范围查询不支持，而且有hash冲突的情况
红黑树
红黑树是是一种自平衡的二叉查找树，他在插入、删除操作时能够保证较好的平衡性，他的查找，插入，删除的时间复杂度都为logn
红黑树的特点
一个节点非红即黑
根节点是黑色
叶子节点（null节点）是黑色
一个节点如果是红色，那么他的两个子节点一定是黑色
任意一个节点到叶子节点的路径都包含相同的黑色节点
这些规则能够保证根节点到任意一个叶子的路径不会超过最短路径的两倍
缺点 当存储大数据量时，树的高度就会变的不可控，查询效率会降低
BTree 结构
B+Tree 是在 BTree 基础上进行演变的, 所以我们先来看看 BTree, BTree 又叫多路平衡搜索树, 一颗 m 叉 BTree 特性如下:
- 树中每个节点最多包含 m 个孩子.
- 除根节点与叶子节点外, 每个节点至少有[ceil(m/2)] 个孩子(ceil 函数指向上取整).
- 若根节点不是叶子节点, 则至少有两个孩子.
- 每个非叶子节点由 n 个 Key 和 n+1 个指针组成, 其中 [ceil(m/2) -1 ] <= n <= m-1. 以 5 叉 BTree 为例, key 的数量: 公式推导 [ceil(m/2) -1 ] <= n <= m-1.
B+Tree
多路平衡搜索树
非叶子节点存储索引,叶子节点存储索引和数据
叶子节点间用链表连接
二级索引(辅助索引)
二级索引在构建索引时叶子结点数据仅存放索引和主键 ID
联合索引(复合索引)
比如 idex_name_age
联合索引建立 B+tree 时，会以建立索引的顺序来排列数据，首先以 name 字段排序，再以 age 字段来排序
这是为什么要符合最左前缀原则
聚簇索引和稀疏索引
聚簇索引即叶子结点中包含所有完整行记录，叶子节点中包含索引及其他所有字段信息，InnoDB 存储引擎中以主键索引构建的 B+tree 即为聚簇索引，其他的皆为稀疏索引。例如二级索引、联合索引、MyISAM存储引擎的索引全是稀疏索引。
面试题
01 为什么没有采用B树而是B+树呢？
B树非叶子节点既存储索引又存储数据,相同的数据量,树的高度较高
数据访问时在非叶子节点和叶子节点所有范围,需要对非叶子节点和叶子节点遍历
叶子节点用链表连接,便于范围查找和顺序查找
02 为什么 B+tree 不在非叶子结点存储除索引外的其他数据呢？
答: 为了继续降低树的高度，同时让非叶子结点可以存储更多的索引。
03 数据页默认大小为16KB(16384b)
04 为什么DBA建议 InnoDB 表必须建主键，并且推荐使用整型的自增主键
如果没有主键会采用唯一索引,没有唯一索引会自己建立一个隐藏列,如果使用主键来查询单条记录时可以避免回表
05 那么非主键索引结构叶子节点都存储完整的行记录不是查询更快吗更快？为什么 MySQL 非主键索引结构叶子节点存储的是主键值去回表查询，而不是存储所有行字段信息呢？
- 一致性问题，如果每个非主键索引叶子结点都存储完整行记录，那么当更新一条记录时，只有所有的索引中都更新成功这条记录才能说这条记录更新成功，增加了更新记录时的复杂性和事务的开销。
- 磁盘占用问题，如果每个非主键索引叶子结点都存储完整行记录，虽然速度会比回表查询更快，但是有多少个索引就会有多少份完整数据，那么原来占用1GB大小的表，如果有4个非主键索引，那么原来占用1GB大小的表就会占用5GB大小的磁盘，得不偿失。
参考资料
面试: 索引的底层数据结构
mysql数据结构详解:深入理解MySQL索引底层数据结构
引擎 MylSAM和InnoDB
MYISAM索引和数据是分开存储的,存储到不同的文件中
InnoDB锁的最小粒度是行锁,MYISAM锁的最小粒度是表锁
锁的粒度和数据的存储
MVCC多版本并发控制
什么是mvcc
是指并发访问数据库，通过对数据的多版本管理，避免因写锁的阻塞造成读数据的并发阻塞的问题
mvcc是通过undolog日志中的版本链和read_view一致性视图来实现的，且只在读已提交，可重复的的隔离级别下有效
innodb mvcc实现的核心知识点
事务版本号：
每次事务开启前都会从数据库获取一个自增长的事务ID，可以从事务ID判读事务的执行先后顺序
表的隐藏列：
每条数据都有三个隐藏的列
- db_trx_id： 事务id，记录的是当前事务在做INSERT或UPDATE语句操作时的事务ID
- db_roll_ptr: 指向上一个版本数据在undo log 里的位置指针
- db_id：隐藏id ，当创建表没有合适的索引作为聚集索引时，会用该隐藏ID创建聚集索引;
undolog
Undo Log，也被称为撤销日志或回滚日志，是MySQL数据库中非常重要的一部分，特别是在InnoDB存储引擎中。其主要功能是在事务执行过程中，记录数据被修改前的状态
每当对数据库中的记录进行INSERT、DELETE或UPDATE操作时，Undo Log会记录下这些修改之前的数据状态。例如，插入一条记录时，会记录这条记录的主键值；删除一条记录时，会记录这条记录的内容；修改一条记录时，会记录这条记录修改前的旧值
回滚时 会按照事务操作的逆序，依次处理每个日志记录，执行反向操作来撤销之前的修改
undolog的用途
- 保证事务进行rollback时的原子性和一致性，当事务进行回滚的时候可以用undo log的数据进行恢复。
- 用于MVCC快照读的数据
read_view
重要属性
trx_ids：当前系统活跃（未提交）的事务版本号集合
low_limit_id： 创建当前 Read view 时系统中的“当前系统最大事务版本号+1”，这个低限制版本号标识着当前事务开始之后产生的所有事务的起始点
up_limit_id： 当前系统最小的活跃事务版本号，当前事务开始之前产生的所有事务的终止点
crater_trx_id： 创建当前 Read view 的事务版本号
read_view 匹配条件
-
当前数据的事务id<最小的活跃事务版本号，显示
-
当前数据的事务id> low_limit_id 不显示
-
在up_limit_id<=n<low_limit_id
找不到，说明事务已提交可以看到
找到了，且数据事务id是read_view的事务id则显示
找到了，但数据事务id不是read_view的事务id，则不显示
-
如果在read_view规则中找不到，则去undolog中，找不到则没有
innodb实现mvcc的原理
- 获取事务版本号
- 获取一个read_view
- 查询数据，与read_view事务版本号进行匹配.
- 不符合read_view 规则的从updolog里获取历史版本
- 返回符合规则的数据
补充
各种事务隔离级别下的Read view 工作方式
- RC(read commit) 级别下同一个事务里面的每一次查询都会获得一个新的read view副本。这样就可能造成同一个事务里前后读取数据可能不一致的问题（重复读）
- RR(重复读)级别下的一个事务里只会获取一次read view副本，从而保证每次查询的数据都是一样的。
快照读和当前读
快照读
快照读是指读取数据时不是读取最新版本的数据，而是基于历史版本读取的一个快照信息，不加锁
当前读
当前读，当前读是读取的数据库最新的数据，当前读和快照读不同，因为要读取最新的数据而且要保证事务的隔离性，所以当前读是需要对数据进行加锁的
面试题
会存在幻读吗
mvcc是快照读，在一个事务中，其他事务是可以insert的，但是在rr级别，获取的read_view是相同的，而第一次获取read_view,是不包含新插入的行
当前读是利用间隙锁，在索引上申请一个范围，其他事务就不能在这个间隙中插入数据，从而避免了幻读的发生；
一条sql的执行过程
首先mysql有这么几个重要的组件
- 连接器 用来管理连接和权限校验的
- 缓存 -用来提高查询效率,但是对于经常更新的表来说,每一次更新都会清空缓存,极大的浪费了资源,且命中率低
- 将数据库的query_cache_type设置为2手动放入缓冲,对于一下静态表(数据不经常更新的)我们可以在执行sql中时手动加上sql_cache,将查询结果放入缓存
- 词法解析器(,包含词法解析,语义检查,构建语法树
- 优化器 执行计划的生产和索引的选择
- 执行器 调用存储引擎接口获取查询结果
主要有两层 server 层,store层
存储引擎层采用的插件式架构,主要用的引擎有:innodb,myIsam,memory
bufferPool
为什么用bufferPool
首先,如果来一个请求,就读对磁盘文件随机读写,然后更新,他的性能是非常低的,这样数据库根本抗不住高并发的请求;
而bufferPool这套机制确保每一次更新请求都是更新内存bufferPool的,然后顺序写日志文件,更新内存和顺序读写的性能远高于随机读写磁盘文件
流程
举例:
以update user set name='zhangsan' where id=1;
- 将磁盘上ID=1的整页数据加载到内存中去
- 将更新前的值存入undolog日志中,便于回滚
- 更新内存数据
- 写redolog日志
- 准备提交事务,将redolog写入磁盘,
- 准备提交事务,将binlog写入磁盘(binlog主用于用数据恢复和主从同步)
- 将commit标志写入redolog文件中,该标志为了 让redolog和binlog数据一致
- IO线程随机写入磁盘,以page为单位
几个部分:缓存池(bufferPool),磁盘,redo log buffer,undolog文件,redolog文件,binlog文件,IO线程
redolog的作用,如果内存中还有一些数据未写入磁盘,可以与undo日志里的数据恢复bufferpool中的缓存数据
补充
binlog
binlog的主要用途: 发生故障时恢复数据和搭建主从集群时用于主从数据库间的数据同步
如何开启biglog
在my.cnf中
设置bin-log的位置
binlog format的格式有三种:
statement:每次更新时,将执行的sql写记录一份,不会产生大量的日志文件,但是对于有函数的sql语句,每次执行时产生的结果可能不同,用数据不一致性的风险,尤其是依据于bin-log的主从同步
row: 每次更新后,生产大量的逻辑日志,但安全性高
mixed: 上面两种的综合,如果sql不会产生数据不一致的风险则,用statement,反之则用row
参考资料
面试: 索引的底层数据结构
mysql数据结构详解:深入理解MySQL索引底层数据结构
