MYSQL原理笔记

MYSQL基础篇

MYSQL前文第一篇

MYSQL存储结构

局部性原理
	操作系统分页，每页4kb大小
	操作系统读取数据时会局部性拿数据，会多拿所需数据所在的那一页数据放入内存中，
	下次如果查询这一页的其他数据则不需要再从磁盘读取

	innodb中也是按页读取数据，一页16kb，16384个字节
	innodb页结构
		File Header
		Page Header
		Infimun + Supremun Records
		User Records：实际存储行记录内容
		Free Space ：页中尚未使用的空间
		Page Directory：页中某些记录的相对位置
		File Trailer：校验页是否完整

innodb行格式有几种，一行最多65535个字节，也就是最多放65535个变量，超过则报错
	Dynamic行格式
	COMPACT行格式
	(最大65532个变量，因为变长字段长度列表，NULL标志位，记录头信息占三个变量)
		变长字段长度列表
		NULL标志位
		记录头信息
			其中包含下一条记录相对位置
		列1数据
		列2数据
		...

所以当一行变量超过一页16384字节，则需要跨页存储
在这种情况下
	COMPACT行格式是当前页存储部分数据+下一页地址
	Dynamic行格式是当前页存储下一页地址，在下一页地址放具体数据，当前页空间则释放出来

1，4，10，20为innodb页结构中的Page Directory
图1

myisam数据保存和我们插入数据顺序一致，innodb数据则是我们插入数据后按照主键排序后保存

innodb排序好处

从图1中，假设查询id为5数据，从页目录二分法开始找，从4页目录进入找，当遍历到id8数据仍未找到则不需要继续遍历，可以直接判断没有该数据

图2

这也就是B+树的由来

---

设置主键索引过程

在真实的记录数据中，还包含三个隐藏列：row_id行id，事务id，回滚指针

当innodb未设置主键，则会判断是否有唯一索引，把唯一索引当成主键，
唯一索引也没有则会用row_id(行id)自动创建主键索引

主键索引为什么不能太复杂

主键索引太复杂占太大空间，会导致每页能存放数据减少，会导致B+树层数变大，增加数据查询复杂度

开辟新页流程

innodb中当第一页数据放不下时，会在第二页空间先复制第一页数据，
然后在第三页存放下一页数据，再把第一页变成目录页

计算B+树能存放多少个数据

一页16384个字节，一对键值对bigint(6字节)+8字节 -> 16384/14 = 1170个键值对
假设一行数据1kb，16kb/1kb = 16个数据每页
两层B+树则可以存放 1170*16个数据
三层B+树则可以存放 1170*1170*16个数据

辅助索引

原理一致，然后数据存储主键索引所在id，在回表通过主键索引拿到具体数据

事务

MYSQL可以加入保存点，回滚时不会全部回滚，会回滚到上一个保存点位置

MYSQL读已提交实现

查询数据时 select 
会产生readview
	readview下m_ids数组保存活跃中还未提交的事务id，
	通过排除这个数组中所有活跃事务id找到已经提交的最大事务id来读取数据
	每次查询时活跃事务id会重新校验，如果有事务已经提交则把该事务从m_ids数组中删除

MYSQL可重复度实现

m_ids数组不停复用，如果有事务已经提交则把该事务从m_ids数组中不会删除

两者相加就是MVCC多版本控制

写操作

insert和delete操作先加X锁，然后执行操作
update：
	被更新的列修改前后导致存储空间变化，则先加X锁，然后对记录进行修改
	如果不导致储存空间变化，先加X锁，然后删除数据，然后插入数据

加读锁后不能再加写锁

解决幻读

行锁+间隙锁

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁

索引失效的产生情况，这些会导致行锁升级表锁

间隙锁也会导致死锁

主从

主从复制流程
	(my.cnf配置文件)主节点配置唯一id和binlog日志存放目录，重启
	(my.cnf配置文件)从节点配置唯一id，读取主节点数据的存放目录，重启，登录mysql建立关系
	开启binlog，从节点开启两个线程，一个io线程读取主库中binlog信息，一个sql线程执行io读取到的信息

主从同步原理

主从延迟