mysql innodb核心

lsn和ckpt

在page页的头,是递增的一个序列号,针对log buffer 生成,每条日志都会有字节量的占用

lsn记录的是日志字节量的变化

那些地方会存储lsn号码

数据页头部会有lsn号码的记录 会有当时落盘的lsn号码

当数据页修改完落盘的时候会更新lsn号码

也被成为 chekpoint lsn号码

log buffer 最新的lsn

redo 也存储最新的lsn

查看lsn号码

show engine innodb status\G

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%';

 innodb_io_capacity      200   #每次最小刷新多少数据页
 innodb_io_capacity_max  2000  #每次最大刷新多少数据页
以上针对的是机械盘 如果磁盘更好可以调整这个参数

2 flash_lru_list chakpoint

若没有多少个可用空闲页，那么InnoDB存储引擎会将LRU列表尾端的页移除。如果这些页中有脏页，那么需要进行Checkpoint

控制参数

show variables like '%lru%depth%'

 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%';

 innodb_max_dirty_pages_pct             90.000000 #buffer pool中达到90%触发强制刷新
 如果是固态盘可以调整小一点

查看buffer pool中空间够不够用

show global status like '%wait_free%';

Innodb_buffer_pool_wait_free   0  #如果大于0说明buffer pool中没有感觉可用的块

show global status like 'innodb_buffer_pool_pages%t%';

 Innodb_buffer_pool_pages_data  | 1032  | #
 Innodb_buffer_pool_pages_dirty | 0     | #脏页比例
 Innodb_buffer_pool_pages_total | 8192  | #总的页数

mysql核心特性CR

简称断电恢复

需要用到的

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把修改的数据页调到内存

数据页内容
(数据页内容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%';

| innodb_buffer_pool_dump_at_shutdown | ON    |#关机是是不是会把热数据拿到磁盘
| innodb_buffer_pool_dump_now         | OFF   |
| innodb_buffer_pool_dump_pct         | 25    |#拿的比例

show variables like '%load%';

innodb_buffer_pool_load_at_startup | ON  #下次启动是会加载文件里面的热点数据

innodb事务详解

事务简称 transaction (交易)

为了保证数据库中线上交易的"和谐" 加入了事务工作机制

一个完整的事务有开始有结束

如何开启一个事务

begin; #只要敲完这个命令,接下来敲得所有命令都在一个事务内
commit; #只要敲完这个命令,提交事务
如果开启了,一个事务,做完操作后悔了
rollback; #不用提交,跟上这个命令就回滚了
事务中不能执行ddl语句,会触发隐士提交
如果在在敲完begin命令执行了几个dml类型语句又敲了一个begin myslq默认会在上一个begin最后处加一个commit
隐士回滚
会话窗口关闭
数据库关闭
出现事务锁

事务的ACID

A原子性

不可再分性 一个事务中的dml语句,要么全成功,要么全失败,没有中间状态

undo保证

C一致性

事务的发生的前,中,后都最终保持一致

cr +dwb保证

I隔离性

出现并发事务的情况下,不会受到其他事务的影响

读写隔离 隔离级别,mvcc

写写隔离 锁 ,隔离级别

隔离级别介绍

查看隔离级别
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锁问题排查

查看select会不会阻塞
select * from performance_schema.metadata_locks\G
进行排查
找到 pending状态
里面会记录当前系统所有的锁
LOCK_TYPE : 锁的级别
OBJECT_TYPE :锁的类型
OWNER_THREAD_ID :后台阻塞的线程id
找到对应的线程执行的语句
select * from performance_schema.events_statements_current\G
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=rc\rr 并发高的就用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)占用