mysql为什么使用B+树、主从同步延时大、MVCC控制方法

mysql相关

• 有一道MySQL的面试题，为什么MySQL的索引要使用B+树而不是其它树形结构?比如B树？

B树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低；
而B+树则只有叶子节点保存数据，进而降低了IO的次数从而节约时间；

• InnoDB数据页为16k,文件系统的页为4k,磁盘的扇区为512字节；B+树树高大概为1-2；假设key+page指针为14字节，数据为1k则叶子节点就可以存储16行，则161024/14=1170；树高为2则可以存储的行数为：11701170*16=21902400；
• mysql主从同步延迟大问题？

1. 主库DML请求频繁即create/update/delete语句频繁；（短时间内产生大量的binlog日志同步给从，是因为在业务高峰期间的主库写入数据是并发写入的，而从库SQL Thread为单线程回放binlog日志，很容易造成relaylog堆积，产生延时）
2. 网络延迟引起 （排查延时）
3. 主从服务器配置不同 （查询配置信息）
4. 主库执行大事务，即类似全表update/delete等语句（拆分大事务语句到若干小事务中，这样能够进行及时提交，减小主从复制延时。）
5. 主库对大表执行DDL语句（对大表增加、删除字段、索引等，尽量选择非高峰阶段处理）
6. 表缺乏主键或合适索引（尽量每个表增加自增索引）
7. 从库自身压力过大(CPU/IO负载过高引起从库主从复制延时)
   解决方法：1. 二次读取即从库没读取到则读取主库，2. 写库后的读操作读主库；3. 关键业务的读取使用主库（一般我们是先优化，优化一些慢查询，优化业务逻辑的调用或者加入缓存等，如果真的优化到没东西优化了然后才上集群，先读写分离，读写分离之后顶不住就再分库分表。）

主从复制延时过高排查方法

通过SHOW SLAVE STATUS与SHOW PROCESSLIST查看现在从库的情况。（顺便也可排除在从库备份时的类似原因）；

1. 若Exec_Master_Log_Pos不变，考虑大事务、DDL、无主键，检查主库对应的binlog及position即可；
2. 若Exec_Master_Log_Pos变化，延时逐步增加，考虑从库机器负载，如IO、CPU等，并考虑主库写操作与从库自身压力是否过大。

mysql 四大特性

特性

• 原子性
• 一致性
• 持久性
• 隔离性 (读未提交、读已提交、可重复读、串行化) 解决问题：脏读、不可重复读问题、幻读，解决方法：MVCC

MVCC方法

• MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问；
• 在mysql的InnoDB引擎中就是指在已提交读和可重复读这两种隔离级别下的事务对于Select操作则访问版本链中记录的过程；这就使得别的事务可以修改这条记录，反正每次修改都会在版本链中记录；

版本链

• 在InnoDB引擎表中，他的聚簇索引记录中必有两个必要的隐藏列即trx_id: 这个id用来存储每次对某条聚簇索引记录进行修改的事务ID；roll_pointer:指向这条聚簇索引记录的上一个版本的位置，通过它来获取上一个版本的记录信息；注意：插入操作是没有这个指针，因为它没有老版本）

举例

id	name	trx_id	roll_pointer
3	Mr	50	上一个版本的地址(指向上一条记录)
3	MrL	30

ReadView

• ReadView主要就是有个列表表来存储我们系统中当前活跃着的读写事务，也就是begin了还未提交的事务，通过这个列表来判断记录的某个版本是否对当前事务可见；

举例

• 假设当前列表中的事务是[80,100];
• 如果访问的记录版本号为50，比当前列表最小80小，则说明这个事务已经提交，所以对当前活动事务来说是可访问的；
• 如果访问的事务id为90，在当前事务列表中间；则表示该事务还未提交所以版本不能访问；
• 这些记录都是去版本链中寻找，先找最近的记录，如果最近这条记录事务ID不符合条件即不能被访问的花，则根据roll_pointer寻找上一个版本记录看能否被访问，以此类推只能返回可见版本或者结束；

已提交读与可重复读的区别

• 如果是已提交读隔离级别，则每次selectd都需要重新生成ReadView；所以在同一事务中每次读的数据都是最新的；
• 如果是可重复读隔离级别，则ReadView只在事务开始时生成以此；所以在同一事务中每次读的数据都是相同的；

举例说明

• 假设在已提交读隔离级别
• 此时有一个事务id为100，修改name，使得named等于MrLUO,但是事务提交，则此时版本链为：

id	name	trx_id	roll_pointer
3	MrLuo	100	上一个版本地址
3	Mr	50	上一个版本的地址(指向上一条记录)
3	MrL	30

• 此时另一个事务发起了select要查询id为3的记录，那此时生成的ReadView列表为[100]；查询版本链最近的记录trx_id=100，在ReadView列表内不能访问，则根据roll_poiner查询上一版本记录，发现trx_id=50,小于readView列表最小值故可以；
• 这时候吧id=100事务提交，并新建一个事务id=110修改name=MrLuo1的记录，不提交；

id	name	trx_id	roll_pointer
3	MrLuo1	110	上一个版本地址
3	MrLuo	100	上一个版本地址
3	Mr	50	上一个版本的地址(指向上一条记录)
3	MrL	30

• 再开启一个查询事务，查询id=3
• 此时，已提交读隔离级别，会重新生成ReadView，活动事务列表值为[110];
• 而如果是可重复读隔离级别，这时候不会重新生成ReadView；所以同一事务内每次读的内容都相同；

总结

• 已提交读隔离级别下的事务，每次查询的开始都会生成一个独立的ReadView,而可重复读隔离级别则在同一事务内第一次读的时候生成一个ReadView，之后便不会更新直到事务结束；