lsn和ckpt
在page页的头,是递增的一个序列号,针对log buffer 生成,每条日志都会有字节量的占用
lsn记录的是日志字节量的变化
那些地方会存储lsn号码
数据页头部会有lsn号码的记录 会有当时落盘的lsn号码
当数据页修改完落盘的时候会更新lsn号码
也被成为 chekpoint lsn号码
log buffer 最新的lsn
redo 也存储最新的lsn
查看lsn号码
show engine innodb statusG
代码语言：javascript复制Log sequence number 106305304 #当前系统最新的lsn号码一个点
Log buffer assigned up to 106305304
Log buffer completed up to 106305304
Log written up to 106305304
Log flushed up to 106305304 #刷到磁盘redo最新的号码
Added dirty pages up to 106305304
Pages flushed up to 106305304
Last checkpoint at 106305304 #最后一次chakpoint lsn号码
chekpoint是什么
我们会经常在buffer pool中修改数据, 被修改的数据页有一天会刷新到磁盘
这个动作叫chekepoint
会产生很多io,运气不好的情况会产生很多随机io
触发条件
sharp chekpoint
完全检查点 数据库正常关闭时,会触发把所有的脏页都写入到磁盘
fuzzy chekpoint
模糊检查点,部分页写入磁盘
1master thread chekpoint
差不多以每秒或十秒的速度从缓冲池的脏页列表中刷新一定比例的页回磁盘,这个过程是异步的, 不会阻塞用户的查询
控制参数
show variables like '%io_cap%';
代码语言：javascript复制 innodb_io_capacity 200 #每次最小刷新多少数据页
innodb_io_capacity_max 2000 #每次最大刷新多少数据页
以上针对的是机械盘 如果磁盘更好可以调整这个参数2 flash_lru_list chakpoint
若没有多少个可用空闲页，那么InnoDB存储引擎会将LRU列表尾端的页移除。如果这些页中有脏页，那么需要进行Checkpoint
控制参数
show variables like '%lru�pth%'
代码语言：javascript复制 innodb_lru_scan_depth 1024 #lru列表可用页数量小于1024就会刷新3 async/sync flush chekpoint
一个叫异步刷新,一个叫同步刷新
重做日志文件不可用的情况，这时需要强制将一些页刷新回磁盘
redo日志lsn号码减去上一次chekpoint的lsn号码
日志文件redo达到了75%触发异步刷新
日志文件redo达到了90%触发强制刷新
4 dirty page too much checkpoint
脏页的数量太多 buffer pool中，触发强制刷新
控制参数
show variables like '%pct%';
代码语言：javascript复制 innodb_max_dirty_pages_pct 90.000000 #buffer pool中达到90%触发强制刷新
如果是固态盘可以调整小一点查看buffer pool中空间够不够用
show global status like '%wait_free%';
代码语言：javascript复制Innodb_buffer_pool_wait_free 0 #如果大于0说明buffer pool中没有感觉可用的块show global status like 'innodb_buffer_pool_pages%t%';
代码语言：javascript复制 Innodb_buffer_pool_pages_data | 1032 | #
Innodb_buffer_pool_pages_dirty | 0 | #脏页比例
Innodb_buffer_pool_pages_total | 8192 | #总的页数
mysql核心特性CR
简称断电恢复
需要用到的
代码语言：javascript复制redo 重做日志 inndb log buffer, ib_logfile.n
undo 回滚日志
lsn 数据页的lsn号码 最新的lsn号码
page 数据页
行记录 头部 db_trx_id , db_roll
IBP innodb buffer pool 缓冲的数据页
dp dirty page,内存中变化的数据页 没有写入到磁盘那些redo 前滚构造脏页
undo回滚未提交事务主要针对修改类操作(增删改类操作)
1把修改的数据页调到内存
代码语言：javascript复制数据页内容
(数据页内容lsn号码在数据页头部,page号)
(db_trx_id,db_roll_ptr)
数据行怎么找到的呢?(通过页目录)2在内存生成undo,记录原始数据行 生成事务id号, 对应前镜像 生成相对位置点(回滚指针)会回写更新到数据行的头部
生成undo的生成log buffer (记录undo的变化) (一旦变化完成就会快速把undo写入磁盘)
3修改数据页,生成log buffer 把lsn号变化记录
4触发提交操作, 先把log buffer的记录落盘简称redo日志
日志一旦落盘就代表操作成功了
redo落盘有两个阶段
一个是prepare阶段 准备提交阶段 提交redo写入磁盘
一个是comment阶段 提交阶段 写binlog并且将redo log的状态改成commit状态
变更操作完成
场景1每次做提交操作都会把redo提交,故障宕机
1启动数据库会读取各个文件,读取完redo文件拿到最新的lsn号码,读取表空间头部拿到老的lsn号码
2把修改过的数据页和redo日志加载到内存
3把数据页进行前滚
场景2在prepare阶段宕机
1启动数据库会读取各个文件,读取完redo文件拿到最新的lsn号码,读取表空间头部拿到老的lsn号码
2把修改过的数据页和redo日志加载到内存
3把数据页进行前滚
4调用数据页头上两个db_trx_id , db_roll_ptr进行undo回滚
ib buffer pool
mysql正常重启中,会把内存比较热的数据写入到磁盘的ib buffer pool中连续的io
控制参数
show variables like '%dump%';
代码语言：javascript复制| innodb_buffer_pool_dump_at_shutdown | ON |#关机是是不是会把热数据拿到磁盘
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |#拿的比例show variables like '%load%';
代码语言：javascript复制innodb_buffer_pool_load_at_startup | ON #下次启动是会加载文件里面的热点数据innodb事务详解
事务简称 transaction (交易)
为了保证数据库中线上交易的"和谐" 加入了事务工作机制
一个完整的事务有开始有结束
如何开启一个事务
代码语言：javascript复制begin; #只要敲完这个命令,接下来敲得所有命令都在一个事务内
commit; #只要敲完这个命令,提交事务
如果开启了,一个事务,做完操作后悔了
rollback; #不用提交,跟上这个命令就回滚了
事务中不能执行ddl语句,会触发隐士提交
如果在在敲完begin命令执行了几个dml类型语句又敲了一个begin myslq默认会在上一个begin最后处加一个commit
隐士回滚
会话窗口关闭
数据库关闭
出现事务锁事务的ACID
A原子性
不可再分性 一个事务中的dml语句,要么全成功,要么全失败,没有中间状态
undo保证
C一致性
事务的发生的前,中,后都最终保持一致
cr dwb保证
I隔离性
出现并发事务的情况下,不会受到其他事务的影响
读写隔离 隔离级别,mvcc
写写隔离 锁 ,隔离级别
隔离级别介绍
代码语言：sql复制查看隔离级别
show variables like '%iso%';
支持以下4种隔离级别(从事务的并发度,来说是依次变弱的se没有并发)
ru 读未提交数据 会出现 脏读 不可重复读 幻读
rc 读已提交数据 *** 会出现 不可重复读 幻读
rr 可重复读 **** 会出现 部分幻读问题
se 串行化 隔离性越高,事务的并发就低
问题读
脏读
开启两个事务
a事务在修改一个修改数据操作,此时数据未提交
b事务在查询同一个数据 会查询到a事务未提交的数据
此时a事务把事务回滚掉,b事务查询还是能查询到就是原来的值
这个操作,称为脏读(在统计计算情况下不准确)
不可重复读
开启两个事务
a事务在修改一个数据,把数据提交
b事务是在开启状态,此时就会查询到a事务提交的数据
大部分时间是可以允许的,不是太精准的情况下可以允许
幻读
开启两个事务
a事务在修改一列值不小于多少,未提交数据
b列插入一行小于a事务的值 两个事务都提交
此时a事务并不知道b事务插入一行值 当查询时就非常玄幻:简称幻读
D持久性
一但事务提交,永久生效(落盘)
redo保证 ckpt
锁的介绍
锁机制 : innodb中主要是写的隔离
作用 包含并发访问资源
锁的类型
内存资源层次 latch(闩锁):rwlock mutex 主要保护内存资源不被置换
buffer pool, log buffer
server层
MDL类型: metadata_lock 元数据锁(ddl),
元数据(除了数据行都是元数据)
表级别的锁
手工做加锁操作的时候是表级别
mysqldump xbk : 备份非innodb数据时,触发FTWRL全局锁表
不管是什么操作首先会在server层去对莫个对象去加锁
元数据(除了数据行都是元数据)
engine层
row lock:innodb 默认锁力度,加锁方式都是在索引加锁的
record lock :记录锁,在索引锁定 RC级别存在 record lock 改那一行就加那一行,针对聚簇索引
gap lock :间隙锁,在索引的间隙加锁,RR级别存在 防止幻读 索引做范围过滤,把中间的间隙(左开右合的范围)加锁
next key lock :下一键锁 gap record rr级别存在 防止幻读
功能性上
IS :意向共享锁先检查能不能上意向锁能上在判断上别的
S : 读锁 共享锁
IX :意向排它锁 先检查能不能上意向锁能上在判断上别的
x : 写锁,排它锁
锁和锁之间是有兼容性的
排他锁和 读锁,意向共享锁 ,排它锁,意向排它锁是不兼容的
意向排它锁 和排它锁,意向共享锁是不兼容的
共享锁 和 排它锁,意向排它锁不兼容
意向共享锁 和排它锁不兼容
MDL锁细分
GLOBAL 全局锁 范围 备份一般有
COMMIT 提交包含锁 范围 是否能够提交
SCHEMA 库锁 对象
TABLE 表锁 对象
MDL锁问题排查
代码语言：javascript复制查看select会不会阻塞
select * from performance_schema.metadata_locksG
进行排查
找到 pending状态
里面会记录当前系统所有的锁
LOCK_TYPE : 锁的级别
OBJECT_TYPE :锁的类型
OWNER_THREAD_ID :后台阻塞的线程id
找到对应的线程执行的语句
select * from performance_schema.events_statements_currentG
SQL_TEXT: 执行的语句
THREAD_ID:后台线程
1找到阻塞语句
2进行分析是不是这条语句阻塞了
3查看有没有GLDBAL级别的锁,查看是不是有人手动加入 lock tables
4找到processlist id kill掉
mdl锁卡住了如果不处理默认就是1年
控制参数
lock_wait_timeout | 31536000 在发生mdl锁一些等待是等待时间秒
innodb_lock_wait_timeout #innodb行级别的锁超时时间
engine级别锁
a锁定队形都是基于索引加锁
b分类 recore lock (记录锁)
GAP (间隙锁) 锁定两个记录的间隙 左开右闭
next key lock 下一键锁
RC级别下只有记录锁
RR级别下加锁力度
1加锁的基本单位是 next key lock并且next key lock (扫描的索引范围)是前开后闭的区间
2 查找过程中访问到所有才会加锁
3索引上的等值查询给唯一索引加锁的时候 会退化成行锁
(前提是自增主键4-5之间没有缺少,下一个数一定是连续的)
4索引上的等值查询 ,向右遍历且最后一个值不满足等值条件的时候,会退化成GAP
8019之前bug 唯一索引的范围查询会访问到不满足条件的第一个值为止
排查问题
select * from sys.innodb_lock_waits;
会告诉你如何处理
MVCC多版本并发控制
读写事务之间的隔离
功能 通过undo生成多版本的快照. readview(可见性)来实现非锁定读取
nudo保存了,修改之前的前镜像
乐观锁:乐观
悲观锁:悲观
1 mvcc采用乐观锁机制,实现非锁定读取
2 在rc级别下, 事务中可以立即读取到其他事务的commit过的readview
3 在rr级别下,事务中从第一次查询开始,生成一个一致性的readview,直到事务结束
创建readview
获取kernel_mutex
遍历trx_sys的trx_list链表,获取所有活跃事务,创建readview
innodb核心参数
innodb_buffer_pool_size 是mysql中最大的一块内存结构设置是物理内存50%-75%
innodb_buffer_pool_chunk_size 它是一个分配单元大小 innodb_buffer_pool_instances 把整个内存分成几个实例,内存中会有一些锁,可以设置一些个数可能会减少征用
怎么去判定buffer_pool够不够用
show global status like '%innodb%wait%';
show status like '%wait_free%';
如果有等待空闲的,说明设置的buffer pool 可能不够
redo参数
innodb_log_buffer_size log buffer的大小一般设置512-4g左右 磁盘文件建议1-2倍
innodb_log_file_size redo文件的大小
innodb_log_files_in_group 有几个redo文件一般有2-4个
change buffer
innodb_change_buffer_max_size 一般情况下不动一般是25%
作用是辅助索引页的修改没有在内存中,此时不会把数据页拿到内存会先把修改的信息放到change buffer中
等查询的时候会把数据页和修改信息合并
表空间
innodb_data_file_path = ibdata1:512M;ibdata2:512M:autoextend #设置共享表空间大小 后面是自动扩展
innodb_file_per_table 打开独立表空间
innodb_undo_tablespaces 打开独立undo模式,并设置undo的个数5.7以上版本建议初始化就打开
innodb_max_undo_log_size undo日志的大小 默认1g
innodb_undo_logtruncate 开启undo自动回收的机制
innodb_purge_rseg_truncate_frequency 触发自动回收条件 ,单位是检测次数
innodb_undo_directory undo日志的位置
io
innodb_io_capacity = 15000 #每次最小刷新多少数据页
innodb_io_capacity_max = 65536 #每次最大刷新多少数据页
一般在80-90
innodb_max_dirty_pages_pct = 70 #在buffer pool中脏页的数量达到多少触发刷新
tarnsaction_isolation=rcrr 并发高的就用rc并发低就用rr
innodb_lock_wait_timeout=10 行锁超时时间
lock_wait_timeout mdl锁等待 一般600秒
wait_timeout = 300 #非交互式链接超时 链接上来啥都不干
interact
innodb_flush_log_at_trx_commit = 1 #每次事务提交都会刷新redo
1每次事务提交都会刷磁盘
0按照秒刷
2按照秒刷
innodb_flush_method = O_DIRECT 刷新的模式
O_DIRECT 直接往磁盘刷新 前提磁盘是高速盘
fsync 先刷os cache 再刷磁盘 会导致额外的内存(os cache)占用
