innodb
2019-08-13 10:55:53 7 举报AI智能生成
innodb存储引擎
作者其他创作
大纲/内容
线程
主线程：将缓冲池中的数据异步刷新到磁盘，保证数据一致性
innodb_purge_threads：回收已经使用并分配的undo页
innodb_page_clearns：刷新脏页
IO线程
主线程工作方式
4个循环
1.主循环
每秒循环
日志缓冲刷新到磁盘，即使这个事务还没有提交(总是)
合并插入缓冲(可能)
Innodb存储引擎会判断当前一秒内发生的IO次数是否小于5次，如果小于5次,innodb 认为当前的IO压力很小，可以执行合并插入缓冲的操作
脏页的数量超过设定的阈值，刷新100个脏页到磁盘
innodb_max_dirty_pages_pct
如果当前没有用户活动,切换到background loop(可能)
10秒循环
判断过去10秒之内磁盘的IO操作是否小于200次，刷新100个脏页到磁盘（可能）
合并至多5个插入缓冲(总是)
将日志缓冲刷新到磁盘(总是)
删除无用的Undo页(总是)
full purge操作
刷新100个或者10个脏页到磁盘(总是)
Innodb存储引擎会判断缓冲池中脏页的比例(buf_get_modified_ratio_pct），有超过70%的脏页，则只需刷新10%的脏页到磁盘
2.后台循环
删除无用的Undo页(总是)
合并20个插入缓冲(总是)
跳回到主循环(总是)
不断刷新100个页，直到符合条件(可能，跳转到flush loop中完成)
3.刷新循环
不断刷新100个脏页，直到脏页比例不超过阈值
4.暂停循环
参数
Innodb_io_capacity
innodb_adaptive_flushting
缓冲池
概述
1.innodb是基于磁盘存储的，将记录按照页的方式进行管理
2.通过内存来弥补磁盘响应速度较慢给数据库性能带来的影响
3.对于数据库中对页修改操作，首先修改缓冲池中的页，然后根据一定频率刷新到磁盘
4.checkpoint技术
5.innodb_buffer_pool_size 设置缓冲池大小
6.innodb_buffer_pool_instance 设置缓冲池实例个数
缓冲池中的数据页类型，页默认大小为16K
1.数据页
2.索引页
3.插入缓存页
4.自适应哈希索引
5.锁信息
6.数据字典
缓冲池管理方式
LRU LIST(最近最少使用)
1.innodb_old_blocks_pct
把lru列表分为new/old两部分，新读取的页放在中间点
2.innodb_old_blocks_time
超过设置的时间，还没有刷出列表，就可以设在new列表，主要是针对列表还没有满的情况
FREE LIST（free链）
启动的时候，缓冲池还是空的，所有可用空间均在free列表中
FLUSH LIST（按照脏数据时间早晚链接）
页被修改的同时，也会加入到刷新列表中，与lru列表互不影响
checkpoint技术
主要解决的问题
1.缩短数据库的恢复时间
缩短数据库恢复时间重做日志中记录了的checkpoint的位置，这个点之前的页已经刷新回磁盘，只需要对checkpoint之后的重做日志进行恢复。这样就大大缩短了恢复时间
2.缓冲池不够用，将脏页刷新回磁盘
缓冲池不够用时，根据LRU算法，溢出最近最少使用的页，如果页为脏页，强制执行checkpoint，将页刷新到磁盘
3.重做日志不可用（不可被重用，不可被覆盖），刷新脏页
分类
1.Sharp Checkpoint
Sharp Checkpoint发生在数据库关闭时，将所有的脏页都刷新回磁盘，这是默认的工作方式，即参数：innodb_fast_shutdown=1。不适合数据库运行时
2.Fuzzy Checkpoint
数据库运行过程中，只刷新一部分脏页
出现fuzzy checkpoint的情况
1. 异步刷新，每秒或每10秒从缓冲池脏页列表刷新一定比例的页回磁盘
2.lru列表需要保证有足够的空闲页
mysql5.6之后，也就是Innodb1.2.x开始，这个检查放在了单独的进程（Page Cleaner）中进行。好处：1.减少master Thread的压力 2.减轻用户线程阻塞。
3.做日志不可用的情况，需要强制刷新页回磁盘，此时的页时脏页列表选取的
MySQL5.6，这部分的刷新操作放在了单独的线程中：Page Cleaner Thread中，故不会阻塞用户查询线程。
4.脏页太多，强制checkpoint.保证缓冲池有足够可用的页
重做日志缓冲 redo log buffer
innodb_log_buffer_size
重做日志刷新到文件的情况
1.master thread 每秒刷新redo log buffer
2.每个事务提交
3.重做日志刷新空间不足一半
启动、关闭、恢复
innodb_fast_shutdown
0.完成所有的full purge、merge inset buffer、所有的脏页刷新到磁盘
1.不完成full purge、merge inset buffer，但是完成所有的脏页刷新到磁盘
2.不做任何操作
innodb_force_recovery
0.进行所有的恢复操作
还可设置1-6，当数据库无法正常启动时，如果错误日志中记录有关于该参数的情况下，可设置1，如果不行，可设置更大的数。
当数据库重启成功，修复完成后，需将该参数注释掉，还原默认值0，否则数据库运行会出现错误
当设置参数值大于0后，可以对表进行select,create,drop操作,但insert,update或者delete这类操作是不允许的
索引
B+树索引
聚簇索引
特性
一个表就是一个聚簇索引（一个表只能有一个聚集索引）
叶子节点中存放的是整张表的行记录
聚集方式
通过主键聚集数据（需要说明的一点是，若表中没有主键innodb会找表中唯一非空索引来代替，要是没有那么innodb会建一个隐式的主键）
优点
辅助索引（二级索引）
特性
区别于主键索引的是，叶子节点存放的是主键值
缺点
由于叶子节点存的是主键值，通过辅助索引查找时，是两次索引查找（先找到对应的主键值，在通过主键索引确定具体的行）
通过二级索引范围查找时会造成随机io（5.6有优化）
优点
分类
单列索引
前缀索引
多列索引
缺点
插入不是顺序的话造成大量的页分裂(特别是主键索引,因为叶子节点需要存储整行数据)
更新聚餐索引列的代价很高（强制该行更新到新的位置）
大量的页分裂会造成空间浪费，形成空间碎片，影响查询性能
特性
高度平衡，所有叶子节点在同一层，并包含所有中间节点索引值
中间节点只存放索引值，和指向下一节点的指针
叶子节点上的所有值按值的大小存放在同一层
叶子节点（数据页，每个数据页16K）之间通过双向指针相连
高扇出（矮胖的树），中间节点存的值多
B+树索引并不能找到具体的行，只能找到被查找数据行所在的页。通过读取具体的页到内存中在查找该行
Multi-Range Read(MRR5.6以后支持)优化
设计目的
减少磁盘的随机访问,并将随机访问转化为较为顺序的访问（join或使用辅助索引操作）
优点
mrr使数据访问变的较为顺序。在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序(放到一个缓冲中在排序)，并按照主键排序的顺序进行查找
减少缓冲池中页被替换的次数
如何减少？
作用
参数
read_rnd_buffer_size 控制键值的缓冲区大小（默认256K）
设置mrr=on时，表示启用MRR优化，mrr_cost_based 表示是否通过 cost base的方式来启用MRR.如果选择mrr=on,mrr_cost_based=off,则表示总是开启MRR优化
Index Condition Pushdown(ICP,5.6以后支持)优化
覆盖索引
哈希索引
自适应哈希索引
不能人工干预，系统自动创建
只能用作等值查询,不能用作范围查找
优化二级索引查找速度
全文索引
锁
什么是锁
锁的类型
共享锁（S）
允许多个事务去读一行，阻止其他事务获得相同数据集的排他锁
排它锁（x）
允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁
意向共享锁（IS）
事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁
意向排他锁（IX）
事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁
如果更改操作不是在索引上，那么会加表级锁（区别于lock table操作）
意向锁
设计目的
意向锁是粗粒度锁（表锁），其设计的主要目的是为了揭示下一个事务请求锁的类型
作用
如果另一个任务试图在该表级别上应用共享或排它锁，则受到由第一个任务控制的表级别意向锁的阻塞。第二个任务在锁定该表前不必检查各个页或行锁，而只需检查表上的意向锁
由于innodb支持行级锁，所以意向锁不会阻塞除全表扫（表锁）以外的任何操作
一致性非锁定读（MVCC 快照读）
实现
mvcc是通过保存某个时间点的快照来实现的（快照是通过undo段来实现的，而undo用来在事务中回滚数据，因此快照数据本身是没有额外消耗）
作用
读不加锁，读写不冲突
例:事务一update ..where id >5 and id<10 该事务不提交，事务二select * from 也能查出数据来
不同事务隔离级别下的表现
RC
对于快照数据，mvcc总是读取被锁定航的最新一份快照数据（不可重复读现象，违反了事务隔离性）
RR
对于快照数据，mvcc读取的总是事务开始的行数据版本
一致性锁定读（当前读）
隐士锁
insert , update delete （隐式x锁）
显示锁
select * from ?where? lock in share mode（显示s锁）
select * from ?where? for updaa(显示x锁
锁的三种算法
Record lock
单条索引记录上加锁
Gap lock
定义
在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身
作用
gap lock的机制主要是解决可重复读模式下的phantom problem（幻像）问题
显式关闭gap_lodk
1.事务隔离级别设置为RC
2.innodb_locks_unsafe_for_binlog=1
Next-Key lock
Gap lock+Record lock,锁定一个范围，包括记录本身 （前开后闭）
解决幻读问题
对于行的查询都是采用next_key_lock
查询使用的索引是唯一索引，那么next_key_lock降级为record_lock
查询使用的索引为辅助索引，那么还会锁定下一个键值gap_lock
锁与索引不得不说的小秘密
锁的问题
幻象问题（Phantom Problem）
定义：在同一个事务下连续执行两次同样的sql可能导致不同的结果
不可重复读：主要是针对更新
幻读：主要针对insert
事务
4种隔离级别
Read Uncommited 未提交读（不会用）
可以读取未提交记录。此隔离级别，不会使用，忽略。
Read Committed (RC) 提交读（会出现不可重复读现象）
快照读
针对当前读，RC隔离级别保证对读取到的记录加锁 (记录锁)，存在不可重复读现象。
Repeatable Read (RR)（默认） 可重复读（通过间隙锁防止幻读）
快照读
针对当前读，RR隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (间隙锁)，不存在幻读现象。
Serializable（从mvcc 退化为当前读）
从MVCC并发控制退化为基于锁的并发控制。部分快照读与当前读，所有的读操作均为当前读，读加读锁 (S锁)，写加写锁 (X锁)。
Serializable隔离级别下，读写冲突，因此并发度急剧下降，在MySQL/InnoDB下不建议使用。
特性
ACID 原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)
实现
redo log 重做日志
作用
用来实现事务的持久性（事务ACID中的D）
组成
重做日志缓冲 redo log buffer
刷新策略(fsync)，参数 innodb_flush_log_at_trx_commit 控制（落盘到redo log file）
0：事务提交时不进行写入重做日志操作，这个操作仅在master thread中完成（1s一刷，可能会丢失最后1s 数据）
1：事务提交时必须调用一次fsync操作，默认值,（受磁盘性能影响，性能低下）
2：事务提交时将重做日志写入重做日志文件，但只写入缓存，不进行fsync操作
一个事务中执行一条sql记录一条，事务commit时刷到redo log file
如何写入file
redo log都是以512字节进行存储的（和磁盘扇区的大小一样，因此redo log写入可以保证原子性，不需要double write）
重做日志文件 redo log file
checkpoint
它是redo log中的一个检查点，这个点之前的所有数据都已经刷新回磁盘，当DB crash后，通过对checkpoint之后的redo log进行恢复就可以了
性能影响
刷盘需要调用系统fsync，性能取决于磁盘性能。0和2性能优于1，但是会丢失部分数据
对比binlog
类型
二进制日志：记录数据库所有修改相关日志
重做日志：只记录innodb引擎本身的事务日志
内容
二进制日志：记录具体的数据操作内容，逻辑日志
重做日志：记录数据页的修改情况
时间
二进制日志：在事务提交前，进行提交，写一次磁盘
重做日志：在事务过程中，不断有重做日志写入重做日志文件
crash recovery（崩溃恢复）
不管运行时是否正常关闭，都会尝尽进行恢复操作（checkpoint之后的）
undo log
作用
记录数据修改前的值，在事务失败(或rollback)的时候用来回滚的。ACID中的A(原子性)
提供mvcc机制
当用户读取一行记录时，该行被其他事务占用，当前事务可以通过undo读取之前的行版本信息（有可能多个快照），来实现非锁定读。
存储
段管理机制
首先innodb 有回滚段，一个rollback segment 对应1024 个undo log segment，一个undo log segment对应一个事务记录。Innodb1.1版本以后支持最大128个rallback segment，故支持同时在线的事务限制 128*1024（1.1 之前就支持一个回滚段）
分类
Insert undo log (insert操作产生的undo log)
insert操作的记录 只对事务本身可见，对其他事务不可见（事务隔离性要求），故该undo log可以在事务提交后删除
update undo log (update 和delete操作产生)
update 和delete操作产生，该undo log 可能需要提供mvcc机制 ，不能事务提交后就删除 ，提交时放入undo log 链表（由purge线程去判断删除）
2PL (二阶段锁)：Two-Phase Locking
2PL就是将加锁/解锁分为两个完全不相交的阶段。加锁阶段：只加锁，不放锁。解锁阶段：只放锁，不加锁。
分类
扁平事务
带有保存点的扁平事务
链事务
嵌套事务
分布式事务
关键特性
插入缓冲
1.原理：判断插入的非聚集索引页是否在缓冲池中，如果在，直接插入；如果不在，先放入insert buffer对象中，然后以一定频率进行insert buffer和辅助索引页叶子结点的合并操作
2.非唯一的辅助索引
3.参数
innodb_change_buffering
表示索引列merge对象，all表示对IDU索引列都起作用，都进行merge，如果只想对insert索引列进行merge，就把all改为inserts
inserts 、deletes、purges、changes、 all、none
innodb_change_buffer_max_size
表示change buffer在buffer pool中的最大占比，默认25%，最大50%
何时进行插入缓存合并
1.辅助索引页被读取到缓冲池中
2.master thread
3.insert buffer bitmap页追踪到辅助索引页空间不够
二次写
1.内存中存在2M大小的doublewrite buffer，共享表空间存在128个连续的页
2.memcopy将脏页拷贝到buffer中，然后每次复制1M的数据到共享表空间，同步磁盘
3.将buffer的页写到各个表空间
4.通过 show status like 'innodb_dblwr';查询
系统在高峰时Innodb_dblwr_pages_written:Innodb_dblwr_writes远小于64:1，那么说明你的系统写入压力并不是很高
自适应哈希索引
只要开启，数据库自动进行，变量名：innodb_adaptive_hash_index
只能进行等值查询操作
异步IO
innodb_use_native_aio 开启
刷新临近页
刷新一个脏页的时候，innodb会检查该页所在区的所有页，如果判断为脏页，就一起进行刷新
0 条评论
下一页
