MySql实战原理
2021-03-18 15:21:31 21 举报AI智能生成
MySql实战原理
作者其他创作
大纲/内容
5.Linux存储系统
VFS层
文件系统
NFS文件系统
Ext2文件系统
Ext3文件系统
Page Cache
通用Block层
IO调度层
Block设备驱动层
Block设备层
6.MVCC多版本并发控制机制
unon log多版本链条
txr_id(事务id)
roll_pointer(指向本次修改之前的undo log)
readView机制
核心字段
m_ids
此时有那些还没提交的事务id
min_trx_id
m_ids里最小的值
max_trx_id
mysql下一个要生成的事务id，就是最大事务id
creator_trx_id
当前的事务id
RC隔离级别
每次查询都会创建一个readView
运行期间m_ids中已提交的事务会被移出m_ids列表，提交的数据可以被查询到
RR隔离级别(默认)
trx_id大于max_trx_id的事务数据不能被查询到
readView一但被创建m_ids列表就不会被更改
读取数据默认开启mvcc机制，不需加锁
7.事务并发
问题
脏写
多个事务修改同一条数据，某一事务回滚导致值被覆盖的问题
脏读
一个事务读取其他事务未提交的数据，其他事务回滚了，下次就读不到了
不可重复读
同一事务中多次读取同一条数据，别的事务老是修改数据值，多次读取的值不同
幻读
同一事务中进行范围查询，每次查到的数据不同，别的事务插入新数据，会读到更多的数据
隔离级别
read uncommitted(读未提交)
避免脏写问题
read commit(读已提交)
不会发生脏写和脏读问题
repeatable read(可重复读)
不会发生脏写、脏读、不可重复读问题
mysql的RR级别解决幻读问题
serializable串行化)
强制事务只能串行化执行
8.锁\索引
锁
行锁
独占锁(写锁)
X锁（Exclude锁）
trx_id、等待状态(false\true)
X锁与X锁之间需要等待锁释放
select * from xxxx for update 加的是独占锁，表明查出来的数据要update
共享锁(读锁)
S锁
S锁与S锁不会互斥
S锁和X锁是互斥的
表锁
意向独占锁
意向共享锁
主键索引
索引页
页目录
数据页最小主键
页号
行实际物理位置
有序单向链表
B+树存储
同一层级索引页双向链表
索引页放满也会造成分裂，再形成更上层的索引页
聚簇索引
如果一颗大的B+树索引数据结构里，叶子节点就是数据页自己本身，那么此时我们就可以称这颗B+树索引为聚簇索引
数据页
双向链表
页分裂
如果数据不是自增的话，会有一个数据挪动的过程，保证下一页的数据主键会大于上一页的数据主键
数据挪动
数据行(记录)
行类型
开始（2）
普通数据（0）
结束（3）
单向链表
按照数据大小有序排列
二级索引
重新弄一颗B+树
数据页
主键
多个索引字段
有序单向链表
回表
通过索引找到主键数据后，还需要进行回表查询，查聚簇索引把其他字段查出来
索引匹配规则
等值匹配规则
=
最左侧列匹配
最左前缀匹配原则
like A%
范围查找规则
Order by排序使用索引
order by字段在联合索引中，可以走索引进行排序
Group by分组使用索引
group by字段在联合索引中，可以走索引进行组合
mysql的一些值得思考的问题
spring的事务传播及其底层原理
REQUIRED：如果当前没有事务，就新建一个事务，如果已经存在一个事务中，加入到这个事务中。Spring的默认事务传播类型
SUPPORTS：支持当前事务，如果当前没有事务，就以非事务方式执行
MANDATORY：使用当前的事务，如果当前没有事务，就抛出异常
REQUIRES_NEW：新建事务，如果当前存在事务，把当前事务挂起（暂停）
NOT_SUPPORTED：以非事务方式执行操作，如果当前存在事务，就把当前事务挂起
NEVER：以非事务方式执行，如果当前存在事务，则抛出异常
NESTED：如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则执行与PROPAGATION_REQUIRED类似的操作
mysql的隔离级别使用
mysql为何默认设置为RR：可重复读级别
主从复制问题：binlog日志顺序错乱
正常互联网公司设置为RC：读已提交
1.首先不可能使用读未提交或串行化
2.到底使用读已提交还是可重复读
两个疑问
事务不生效的一些原因
mysql存储引擎设置必须为inodb
@Transactional必须加在接口的实现类上，不能加在接口上
类的内部方法调用不生效
必须抛出异常才可以进行回滚操作，如果dao方法中catch了异常，则不会回滚
1.存储引擎
innoDB
版本
老版本 InnoDB
支持ACID、行锁设计、MVCC
InnoDB 1.0.x
继承了上述版本所有功能，增加了compress和dynamic页格式
InnoDB 1.1.x
继承了上述版本所有功能，增加了Linux AIO，多回滚段
InnoDB 1.2.x
继承了上述版本所有功能，增加了全文索引支持、在线索引添加
SQL执行组件
SQL接口
解析器
优化器
执行器
InnoDB关键特性
插入缓冲（Insert Buffer）
优点
然后再以一定的频率和情况进行Insert Buffer和辅助索引页子节点的merge（合并）操作，这时通常能将多个插入合并到一个操作中（因为在一个索引页中），这就大大提高了对于非聚集索引插入的性能
InnoDB存储引擎开创性地设计了Insert Buffer，对于非聚集索引的插入或更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，则直接插入
若不在，则先放入到一个Insert Buffer对象中好，似欺骗数据库这个非聚集的索引已经插到叶子节点，而实际并没有，只是存放在另一个位置
使用条件
索引是辅助索引（secondary index）
索引不是唯一（unique）的
两次写（Double Write）
自适应哈希索引（Adaptive Hash Index）
异步IO（Async IO）
刷新邻接页（Flush Neighbor Page）
执行步骤
1.从磁盘文件加载缓存数据到BufferPool中
2.将旧数据的值写入undo日志中，以便回滚
3.执行器更新Buffer Pool的缓存数据
4.写redo日志到Redo Log Buffer中
5.准备提交事务，将redo日志写入磁盘
6.准备提交事务，将binlog日志写入磁盘
7.将写入的binlog文件于位置更新到redo日志中，写入commit标记
8.后台IO线程随机将内存更新后的脏数据回刷磁盘
组件
缓冲池（Buffer Pool）
缓存页
数据缓存哈希表
free链表
flush链表
lru链表
undo日志
redo Log buffer
redo日志文件
binlog日志文件
后台线程
Master Thread
Master Thread是一个非常核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲（INSERTBUFFER）、UNDO页的回收等
IO Thead
在InnoDB存储引擎中大量使用了AIO（Async IO）来处理写IO请求，这样可以极大提高数据库的性能
而IO Thread的工作主要是负责这些IO请求的回调（callback）处理
InnoDB 1.0版本之前共有4个IO Thread，分别是write、read、insert buffer和log IO thread
从InnoDB 1.0.x版本开始，read thread和write thread分别增大到了4个
根据参数innodb_read_io_threads及innodb_write_io_threads来设置的读写线程，并且读线程的ID总是小于写线程
Purge Thread
事务被提交后，其所使用的undolog可能不再需要，因此需要PurgeThread来回收已经使用并分配的undo页
而从InnoDB 1.1版本开始，purge操作可以独立到单独的线程中进行，以此来减轻Master Thread的工作，从而提高CPU的使用率以及提升存储引擎的性能
在InnoDB 1.1版本中，即使将innodb_purge_threads设为大于1，InnoDB存储引擎启动时也会将其设为1
从InnoDB 1.2版本开始，InnoDB支持多个Purge Thread，这样做的目的是为了进一步加快undo页的回收
同时由于Purge Thread需要离散地读取undo页，这样也能更进一步利用磁盘的随机读取性能
Page Cleaner Thread
Page Cleaner Thread是在InnoDB 1.2.x版本中引入的。其作用是将之前版本中脏页的刷新操作都放入到单独的线程中来完成。
需要检查LRU列表中是否有足够的可用空间操作发生在用户查询线程中
倘若没有100个可用空闲页，那么InnoDB存储引擎会将LRU列表尾端的页移除
并且用户可以通过参数innodb_lru_scan_depth控制LRU列表中可用页的数量，该值默认为1024
2.执行计划分析
type
const
本质都是基于索引查询，只要你索引查出来的数据量不是特别大，一般性能都极为高效
ref
本质都是基于索引查询，只要你索引查出来的数据量不是特别大，一般性能都极为高效
range
本质都是基于索引查询，只要你索引查出来的数据量不是特别大，一般性能都极为高效
index
扫描二级索引的意思，就是说不通过索引树的根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，这种速度还是比较慢的，大家尽量还是别出现这种情况
all
直接全表扫描，扫描你的聚簇索引的所有叶子节点，也就是一个表里一行一行数据去扫描
多个索引树，如果不全都是等值匹配的时候，会取数据少的索引树进行遍历，然后回表查询字段
如果是多个索引树是等值匹配的时候，会将结果都查出来之后取交集，再进行回表查询字段
3.核心组件
innoDB buffer_pool(缓冲池)
多个Buffer Poll实例
从InnoDB 1.0.x版本开始，允许有多个缓冲池实例
每个页根据哈希值平均分配到不同缓冲池实例中
SHOW ENGINE INNODB STATUS
默认大小128MB,按机器Buffer Pool可以配置2GB大小
数据页类型
索引页
数据页
undo页
插入缓冲(insert buffer)
自适应哈希索引(adaptive hash index)
InnoDB存储的锁信息
lock info
数据字典信息(data dictionary)等
chunk
默认大小128MB
innodb_buffer_pool_chunk_size = 128MB
多个chunk组成一个完整的Buffer Pool
多个chunk共享一套lru、free、flush链表结构
数据描述块
数据页
每页默认16kb
一行数据
每次加载一页或者多页
缓存页
更新缓存页的数据，由于跟磁盘数据不一致，导致缓存页变成脏页
淘汰缓存页
把缓存页进行刷盘操作，释放空闲缓存页到free链表
预读机制
1.如果访问一个区里的数据页超过一定的阈值会触发预读机制，把下一个相邻区里的所有数据页都加载到缓存里
innodb_read_ahead_threshold
2.如果Buffer Pool缓存了一个区里13个相邻的数据页，而且这些数据页被频繁访问的时候，也会触发预读机制，将这个区里剩下的数据页加载到缓存里
innodb_random_read_ahead
数据缓存哈希表
key-value数据结构
<表空间号+数据页号,缓存页地址>
使用数据页的时候先读哈希表，如果不存在就加载数据页
内置链表数据结构
free链表
双向链表
记录空闲的缓存页，使用一个缓存页就会从free链表中移除
当需要从缓冲池中分页时，首先从Free列表中查找是否有可用的空闲页，若有则将该页从Free列表中删除，放入到LRU列表中。
count基础节点
引用链表的头节点、尾节点
lru链表(核心数据结构)
midpoint位置
新读取到的页，虽然是最新访问的页，但并不是直接放入到LRU列表的首部，而是放入到LRU列表的midpoint位置
在默认配置下，该位置在LRU列表长度的5/8处
LRU列表用来管理已经读取的页，但当数据库刚启动时，LRU列表是空的，即没有任何的页。这时页都存放在Free列表中。
挂载逻辑
每次从磁盘加载一个数据页到缓存页的时候，缓存页的数据描述块会放到lLRU链表的表头位置
每次查询、修改数据的时候，都会放到LRU链表的表头位置
没有空间的缓存页时，会将尾部节点的缓存页进行刷盘，释放free链表、flush链表
冷热数据
冷数据默认占比3:7
innodb_old_blocks_pct
参数innodb_old_blocks_pct默认值为37，表示新读取的页插入到LRU列表尾端的37%的位置（差不多3/8的位置）
热数据区
对冷数据再次进行访问的时候，会将数据放去热数据区的头部
热数据区域的后3/4部分的缓存页被访问，才会移动到链表头部去
冷数据区
第一次加载数据的时候会加载到冷数据链表的头部
1s秒之后再次访问缓存页，会将这个缓存页放到热数据区的链表头部去
预读机制加载的数据页会放在冷数据区的头部位置
缓存页不够的时候，直接释放冷数据区域的尾部缓存页，刷入磁盘
flush链表
双向链表
在LRU列表中的页被修改后，称该页为脏页（dirty page），即缓冲池中的页和磁盘上的页的数据产生了不一致。这时数据库会通过CHECKPOINT机制将脏页刷新回磁盘，而Flush列表中的页即为脏页列表。
需要注意的是，脏页既存在于LRU列表中，也存在于Flush列表中。LRU列表用来管理缓冲池中页的可用性，Flush列表用来管理将页刷新回磁盘，二者互不影响
count基础节点
引用链表的头节点、尾节点
undo log日志
回滚事务日志
刷盘机制
后台线程定时将lru冷数据区尾部的一些缓存页刷入磁盘
刷盘会释放flush链表，重新加入free链表，从lru链表中移除
redo log_buffer(重做日志缓冲)
该值可由配置参数innodb_log_buffer_size控制，默认为8MB
组件
redo log block
512字节
多条redo log数据
redo log
用户顺序追加写入redo log日志
缓冲
InnoDB存储引擎首先将重做日志信息先放入到这个缓冲区，然后按一定频率将其刷新到重做日志文件
刷盘时机
当重做日志缓冲池剩余空间小于1/2时，重做日志缓冲刷新到重做日志文件
每个事务提交的时候，必须把他那些redo log所在的redo log block都刷入磁盘里去
Master Thread每一秒将重做日志缓冲刷新到重做日志文件
重做日志缓冲一般不需要设置得很大，因为一般情况下每一秒钟会将重做日志缓冲刷新到日志文件，因此用户只需要保证每秒产生的事务量在这个缓冲大小之内即可
后台线程定时刷新，有一个后台线程每隔1秒就把redo log buffer 里的redo log block刷到磁盘里
Mysql关闭的时候，redo log block都会刷入到磁盘里
checkpoint机制
为了避免发生数据丢失的问题，当前事务数据库系统普遍都采用了Write Ahead Log策略，即当事务提交时，先写重做日志，再修改页
时机
Master Thread中发生的Checkpoint，差不多以每秒或每十秒的速度从缓冲池的脏页列表中刷新一定比例的页回磁盘。这个过程是异步的，即此时InnoDB存储引擎可以进行其他的操作，用户查询线程不会阻塞
倘若没有足够可用的空闲页，那么InnoDB存储引擎会将LRU列表尾端的页移除。如果这些页中有脏页，那么需要进行Checkpoint，而这些页是来自LRU列表的，因此称为FLUSH_LRU_LIST Checkpoint
Async/Sync Flush Checkpoint指的是重做日志文件不可用的情况，这时需要强制将一些页刷新回磁盘，而此时脏页是从脏页列表中选取的
innodb_max_dirty_pages_pct值为75表示，当缓冲池中脏页的数量占据75%时，强制进行Checkpoint，刷新一部分的脏页到磁盘
解决问题
缩短数据库的恢复时间
当数据库发生宕机时，数据库不需要重做所有的日志，因为Checkpoint之前的页都已经刷新回磁盘。故数据库只需对Checkpoint后的重做日志进行恢复。这样就大大缩短了恢复的时间。
缓冲池不够用时，将脏页刷新到磁盘
此外，当缓冲池不够用时，根据LRU算法会溢出最近最少使用的页，若此页为脏页，那么需要强制执行Checkpoint，将脏页也就是页的新版本刷回磁盘
重做日志不可用时，刷新脏页
重做日志出现不可用的情况是因为当前事务数据库系统对重做日志的设计都是循环使用的，并不是让其无限增大的，这从成本及管理上都是比较困难的
innodb_additional_mem_pool_size额外内存池)
在InnoDB存储引擎中，对内存的管理是通过一种称为内存堆（heap）的方式进行的
在对一些数据结构本身的内存进行分配时，需要从额外的内存池中进行申请，当该区域的内存不够时，会从缓冲池中进行申请
例如，分配了缓冲池（innodb_buffer_pool），但是每个缓冲池中的帧缓冲（framebuffer）还有对应的缓冲控制对象（buffer control block），这些对象记录了一些诸如LRU、锁、等待等信息，而这个对象的内存需要从额外内存池中申请。
4.生产经验
机器配置
16核32GB内存
SDS固态硬盘
压测工具
sysbench
观察网卡流量
dstat -n
观察磁盘IO
dstat -d
dstat -r
性能提升
Buffer Pool
配置多个Buffer Pool提升并发能力
innodb_buffer_pool_size = 8GB(配置的时候用字节值)
innodb_buffer_pool_instances = 4
动态扩容Buffer Pool
增加多个chunk
可以配置机器总内存的60%作为Buffer Pool的大小
show engine innodb status 查看状态
0 条评论
下一页
