mysq 笔记

mysql架构图

innodb执行流程图

buffer pool 的内存数据结构

1. buffer pool 内存结构
mysql在启动的时候,会向系统申请一块内存地址,默认是128m,同时初始化一下空的缓存页(16k),每个缓存页会对应一个描述信息(800b左右),所以空间会大于128M

2. free 链表和缓存页哈希表
缓存页哈希表:记录那些数据页被加载进去了buffer pool,对应的地址是哪里
free 链表: 记录那些缓存页是空的,可以用来加载新的数据页进来
在第一次启动的时候,mysql所有的缓存页都是空的,所以free链表是所有缓存页的集合,随着数据不断被加载进来,free链表的不断被移除;然后buffer pool 刷数据回磁盘,对应的缓存页空了,又会被加进来

3. flush 链表
我们执行增删改的时候,势必会对哪些对应的缓存页进行修改,怎么记录这些修改过的缓存页呢?flush链表
flush 链表:记录哪些缓存页是被修改过的,有线程在合适的时机把这些修改过的缓存页刷回磁盘

4.基于冷热数据分离方案优化后的LRU链表
随着缓存页的使用,缓存页不够了,那要把哪些缓存页刷回磁盘呢?对一些经常访问的缓存页,我们当然希望是保留下来,刷那些不常使用的缓存页会磁盘.最近少使用的缓存页,就记录在了LRU链表(记录加载使用的,不一定就是修改的,跟flush链表有区别)
LRU链表:最近少使用链表,每次使用缓存页,就会把对应的缓存页移动到靠前的位置,所以尾部的就是比较少使用的.
mysql预读机制:在读取数据页进入缓存的时候,可能会把相邻的数据页一起读进来,因为mysql判断你可能会继续读取这个数据页,但是这样会导致如果这个预读进来的缓存页,如果不常用的话,就会在LRU链表的头部位置,而导致其他常用的缓存页被刷回去,最后引入了冷热数据分离的方案

5. 通过chunk来支持数据库运行期间的Buffer Pool动态调整

mysql可以有多个buffer pool ,每个buffer pool有多个chunk组成,每个chunk（默认128M）保存着若干描述数据块和缓存页,每个buffer pool是共用一个free,flush,lru链表。
如果现在要动态增加buffer pool的大小，比如从8g增加到16g，只需要再申请一系列chunk，然后分配给buffer pool，就可以实现扩容了。

mysql数据物理结构

redo log 结构

1. 我执行的事务操作,可能有多个修改记录,对应着一组redo log group ,这个是存在其他地方的,然后事务都执行完了,再把一组redo log 写入到block里面
2. 如果一组redo log太大,可能会存放在多个block里面;如果一组redo log 太小,则多个redo log存放在一个block
3. redo log 刷磁盘的时机:
   a. 如果写入redo log buffer的数据已经占到了buffer的一半,就是8M时,刷入磁盘.
   b. 一个事务提交的时候,会把这个事务redo log对应的block刷入磁盘,保证宕机等情况数据不会丢失.(实际是刷到OS缓存里面,可以配置参数,强制刷到磁盘文件)
   c. 后台定时刷新,每隔1秒,把redo log buffer里面的block都刷到磁盘
   d. mysql关闭时,把block刷到磁盘
   

undo log 版本链表

1. 事务A插入一条数据
   

2. 事务B(id=58)修改为值B
   

3. 事务C(id=69)修改为值C
   

ReadView

执行一个事务的时候,生成一个ReadView,包含以下信息:
m_ids: 此时有哪些事务没有提交,现在的活跃事务
min_trx_id: 最小事务id,m_ids里面最小值
max_trx_id: 最大事务id,mysql要生成的下个事务id,m_ids里面的最大值+1
creator_trx_id: 当前事务id
ReadView机制: 给予undo log版本链条实现的一套读视图机制,事务生成一个ReadView,如果是你事务自己更新的数据,自己可以读到,或者是在ReadView之前提交的事务修改值(id小于最小值),可以读到;但是如果生成ReadView已经是活跃事务,并且还修改了值提交了,此时你读取不到的;如果是你生成ReadView之后修改了数据,也是读取不到。

Read Committed隔离级别是如何基于ReadView机制实现的

在解决Read Committed阶段问题时,同一事务中(如事务A),每次查询都会新生成一个ReadView,如果事务B已经提交了,并且他的值可以被事务A读取到,事务A中多次查询的时候都会生成一个新的ReadView,此时因为事务B已经提交了,m_ids里面没有事务B的值,所以数据可以被读取到

Read Committed : 事务A和事务B同时开启,事务B修改值并提交了,事务A可以读取到事务B的修改值

1. 假设事务A和B开启之前数据库有一条数据存在,id=50
   

2. 事务A发起一次查询,查到的是原始值
   

3. 事务B发起update操作,更改值为值B并且提交了
   

4. 事务A再次发起查询,同时生成一个新的ReadView,由于事务B提交了,m_ids里面没有70的值了,这样trx_id大于min_trx_id,小于max_trx_id,但是在判断trx_id不在m_ids里面,所以根据readView机制,没有两个条件同时满足,可以查询到的事务B修改的值,实现了Read Committed
   

RR隔离级别，是如何基于ReadView机制实现的(解决重复读和幻读)

RR级别的事务,在事务开始时生成ReadView,之后不会更改了
RR:事务A和事务B同时开启,事务B修改值并提交了,事务A读取的还是原值,不会读到事务B的值

1. 假设事务A和B开启之前数据库有一条数据存在,id=50
   

2. 事务A发起一次查询,查到的是原始值
   

3. 事务B发起update操作,更改值为值B,并且提交了(前面跟RC级别基本一样)
   

4. 接着,事务A又发起一次查询,此时ReadView是不会再次生成的,还是原来的值,id=70的,根据ReadView机制,不能查询,但是下一个节点,id=50的,根据ReadView机制可以查询到,跟第一次查询的值还是同一个,解决了不可重复读的问题
   

5. 又有事务C,插入一条数据,id=80
   

6. 事务A又发起一次查询,ReadView还是一样的,跟id为80和70的数据比较,都不能查询,最后跟id=50的比较,可以查询,这样事务C插入的数据,不能影响到事务A,解决了幻读问题
   

锁机制(待完善)

锁是针对多个事务同时更新数据的情况的,
如果一个事务更新,另外一个事务是读数据,这种情况直接mvcc机制就可以了,没必要加锁
同理脏读,不可重复读,幻读,这些都是在读层面发生的,通过mvcc(ReadView+undo log版本链表)就可以解决了

1. 事务A,想要更新数据,会创建一把锁,包含自己的trx_id和锁状态,并且把这个锁和数据行关联起来
   
2. 事务B要去同样想要去更新数据,就会发现这个数据已经关联了一把锁,所以同样会生成一把锁,但是由于自己在等待,所以等待状态是true
   
3. 事务A更新完数据了,会释放锁,同时会查找是否有其他事务对数据加锁了,如果有就会修改事务B的等待状态为false,同时唤醒事务B
   
   通过这样的方式解决了脏写的问题

共享锁和独占锁

共享锁:读锁
独占锁:写锁

1. 独占锁,自己在更新会加上独占锁,如果其他事务想要更新,发现已经有锁了,所以会等待;
   那么如果是读取数据呢? 这种情况不用加锁,基于mvcc机制就可以读取到了,不用对这行数据加锁
   如果已经加了独占锁,一定要加共享锁(select * from table lock in share mode),也是不允许的,只能等待操作完成
2. 共享锁,自己在读取数据的时候会加上共享锁,如果其他事务想要加共享锁是可以成功了,因为都是对数据的读取,加了没有影响
   但是如果其他事务想要对数据进行更新,那是不允许的,会导致我读取数据错误
   

磁盘数据页的存储结构

数据页之间是双向链表结构 , 数据行之间是单向链表结构
数据页里每一行数据按照主键大小顺序存储,下一页的数据主键大于上一页数据页所有主键

页分裂

msyql的数据是一个按照主键顺序大小的结构存储的,数据页之间是双向链表的结构,但是如果我们的主键不是自增长的话,有可能会出现第二页的数据页主键要比第一也的主键小的情况
刚开始的行,类型是2,表示最小的一行
中间的行类型是0,是普通的数据
最后的行类型是3,表示最大的一行

此时会出现一个过程,叫页分裂，就是万一你的主键值都是你自己设置的，那么在增加一个新的数据页的时候，实际上会把前一个数据页里主键值较大的，挪动到新的数据页里来，然后把你新插入的主键值较小的数据挪动到上一个数据页里去，保证新数据页里的主键值一定都比上一个数据页里的主键值大。页分裂的核心目标就是保证下一个数据页的主键值比上一个数据页的主键值都大

索引页

主键索引

最下层的索引页保存最小id和对应的数据页号,上层索引页保存最小id和索引页号
同一级层的索引页组成双向链表,数据页之间也组成双向链表
此时如果要查找id=18的数据,就可以通过索引页46–>索引页35–>索引页20,找到页号8的数据页,然后在数据页里通过二分法进行找到,定位到id=18的数据

二级索引

主键之外的索引,我们在建立索引的时候,实际是会重新建立一个B+树,结构与上面的类似,叶子节点也是关联着数据页,但是数据页保存的是索引值和主键.
比如select * from table where name='xx’这样的语句,name字段建立了索引,我们在name索引树的叶子节点找到的数据就是name值和对应的主键值,然后通过主键进行回表,去找主键索引的B+树,最后找到所有的数据
如果select name, phone from table where name=‘xx’, name, phone建立了索引,所以查到的值就都是索引树,并不需要回表查询主键索引树,直接把数据返回就行了,这种就是覆盖索引

常见索引的使用规则

1.全值匹配规则: 就是你where条件里面的几个字段和联合索引(class_name, student_name, subject_name)的字段是一直的,而且都是等号查询,那么这种情况是100%会用到索引的,即使你where的字段顺序跟联合索引的顺序不一致也没关系,msyql的优化器会自动优化为联合索引去查找.
2.最左侧列匹配: 假设我们联合索引是KEY(class_name, student_name, subject_name),比如你可以写select * from student_score where class_name=’’ and student_name=’’，就查某个学生所有科目的成绩，这都是没有问题的。
但是假设你写一个select * from student_score where subject_name=’’，那就不行了，因为联合索引的B+树里，是必须先按class_name查，再按student_name查，不能跳过前面两个字段，直接按最后一个subject_name查的。
另外，假设你写一个select * from student_score where class_name=’’ and subject_name=’’，那么只有class_name的值可以在索引里搜索，剩下的subject_name是没法在索引里找的，道理同上。
3.最左前缀匹配原则: 即如果你要用like语法来查，比如select * from student_score where class_name like ‘1%’，查找所有1打头的班级的分数，那么也是可以用到索引的。
因为你的联合索引的B+树里，都是按照class_name排序的，所以你要是给出class_name的确定的最左前缀就是1，然后后面的给一个模糊匹配符号，那也是可以基于索引来查找的，这是没问题的。
但是你如果写class_name like ‘%班’，在左侧用一个模糊匹配符，那他就没法用索引了，因为不知道你最左前缀是什么，怎么去索引里找啊？
4.范围查找规则:这个意思就是说，我们可以用select * from student_score where class_name>‘1班’ and class_name<‘5班’这样的语句来范围查找某几个班级的分数。
这个时候也是会用到索引的，因为我们的索引的最下层的数据页都是按顺序组成双向链表的，所以完全可以先找到’1班’对应的数据页，再找到’5班’对应的数据页，两个数据页中间的那些数据页，就全都是在你范围内的数据了！
但是如果你要是写select * from student_score where class_name>‘1班’ and class_name<‘5班’ and student_name>’’，这里只有class_name是可以基于索引来找的，student_name的范围查询是没法用到索引的！
5.等值匹配+范围匹配的规则:如果你要是用select * from student_score where class_name=‘1班’ and student_name>’’ and subject_name<’’，那么此时你首先可以用class_name在索引里精准定位到一波数据，接着这波数据里的student_name都是按照顺序排列的，所以student_name>’‘也会基于索引来查找，但是接下来的subject_name<’'是不能用索引的。