mysql
2022-06-28 10:59:32 2 举报
AI智能生成
登录查看完整内容
innodb存储引擎 详解
作者其他创作
大纲/内容
不支持事务
表锁
联机分析处理OLAP(On-Line Analytical Processing
OLAP 系统则强调数据分析,强调SQL执行市场,强调磁盘I/O,强调分区等
面向olap
myisam
替代myisam
行锁
支持缓存数据索引文件
支持mvcc
支持事务、非事务
更好的blob字段处理
maria
默认引擎
OLTP 系统强调数据库内存效率,强调内存各种指标的命令率,强调绑定变量,强调并发操作
联机事务处理OLTP(on-line transaction processing)
行锁、外键、非锁定读
默认repeatable
mvcc获取高并发性
next-key locking策略避免幻读
主键是行的唯一标识符,应该自增
离散的访问非聚簇索引会随机读取,性能下降
主键插入应该顺序插入,避免磁盘随机读取,即为什么不要用uuid做主键
背景
非聚簇索引插入或更新,先判断索引页是否在缓冲池,若不在先放在insert buffer中
以一定的频率对insert buffer 和辅助叶子节点merge,可以将多个操作合并在一个操作中,提高非聚簇索引的插入性能
b+树
原理
索引是辅助索引(secondary index)
索引不是唯一的
条件
插入缓存(insert buffer)性能提升
部分写失效(写页时宕机,此时无法通过redo log 恢复)
doubler write buffer
共享表空间连续128个页,即2个区
组成
脏页刷盘时,先将脏页复制到doubule write buffer
然后分两次顺序写入共享表空间
最后写入表空间文件
若写入失败从共享表空间恢复
过程
需要较快的性能时可以关闭
需要数据高可用时要打开
skip_innodb_doublewrite 可以禁止使用两次写
两次写(double write)可靠性提升
根据访问频率和模式自动为热点页建立hash索引
要对该页的访问模式必须一样的,即查询条件一样
只能用来做等值查询,不能范围查询
自适应哈希索引(adaptive hash index)
异步io(async io)
刷新脏页时,刷新该页所在区所有脏页
机械硬盘开启
固态硬盘关闭
innodb_flush_neighbors
刷新邻接表(flush neighbor page)
关键特性
权限校验(如果命中缓存)---》查询缓存---》分析器---》优化器---》权限校验---》执行器---》引擎
查询语句执行过程
分析器----》权限校验----》执行器---》引擎---redo log(prepare 状态---》binlog---》redo log(commit状态)
更新语句执行过程
总览
异步,存储引擎可以进行其他操作
脏页刷回
undo页回收
合并插入缓冲(insert buffer)
master thread(缓冲池中数据异步刷回磁盘,保证数据一致性)
write
read
insert buffer
log io thread
处理io 请求回调
io thread
回收undo页,减轻master 压力
purge thread
脏页刷新放到单独线程完成,减轻master压力,减轻用户查询线程阻塞
page cleaner thread
体系架构 多线程架构
弥补磁盘速度慢对性能的影响
缩短数据库恢复时间(只对check point后的redo log做恢复)
缓冲池不够时,将脏页刷回磁盘(lru溢出的页,强行checkpoint)
redo log不可用时刷新脏页
发生在数据库关闭时
将所有脏页刷回磁盘
sharp checkpoint
只刷新一部分脏页
master thead checkpoint(异步,不阻塞)
mysql5.6后 放在page cleaner单独线程中进行,不阻塞
flush_lru_list checkpoint(保证lru有100个可用页)
fuzzy checkpoint
发生在redo log 不可用时,强制将部分页刷回磁盘
保证redo log循环使用的可用性
单独page cleaner线程,非阻塞
async/sync flush checkpoint
dirty page too much checkpoint
check point策略(本质是将脏页刷回磁盘)
数据库页的修改,首先修改缓冲池的页,再刷回磁盘
缓冲池命中率(buffer pool hit rate)应大于95%,否则需观察是否有全表扫描污染lru
脏页:lru中的页被修改后称为脏页,即缓冲池的页和磁盘页不一致
LRU(latest recent used)算法管理,midpoint 默认5/8
缓冲池
master thread 每秒刷一次
每个事务提交
redo log buffer 剩余空间小于1/2
刷盘策略
redo log buffer
额外缓冲池
减少数据库内部资源竞争,提高并发
innodb_buffer_pool_instances(缓冲池实例个数)
innodb_buffer_pool_size(缓冲池容量)
innodb_old_blocks_pct(lru的midpoit,默认5/8)
innodb_log_buffer_size(重做日志缓冲 默认8MB)
innodb_lru_scan_depth(lru可用页数量,默认1024)
innodb_max_dirty_pages_pct(触发checkpoint脏页数量百分比,默认75)
调优
内存
架构
B+树叶子节点
数据段
B+非叶子节点
索引段
回滚段
段
连续的页组成,为了保持连续,一次申请4-5个区
任何情况下大小为1M
区
b+树不能找到具体的某一条记录,把整个页载入内存,然后通过page directory 二分查找
innodb磁盘管理最小单位,默认16k
innodb_page_size 设置
最多存放16k
数据页(b-tree node)
undo页 (undo log page)
事务数据页 (transcation system page)
插入缓存空闲列表页(insert buffer free list)
未压缩的
已压缩的
大对象页(blob page)
页
一个页中存放的行数越多效率越高
NULL不占用空间(char varchar 都不占用空间)
事务id列(transaction id)
回滚指针列(roll pointer)
两个隐藏列(若么有主键,会增加rowId列)
compact
5.0之前的行记录方式
NULL值(varchar不占用空间,char占用)
redundant
保证页至少放下两条行记录,否则将数据保存到溢出页
行溢出数据
行
逻辑存储结构
主键排序查找和范围查找速度非常快
叶子节点包含完整行记录
聚簇索引(clustered index)
所以查询时,先找到聚簇索引,然后根据聚簇索引查找行记录
叶子节点保存相应数据行的聚簇索引
辅助索引(secondary index)
第二个键值做了排序
最左匹配原则
联合索引
从辅助索引就可以拿到查询的记录,就不需要再次查询聚簇索引
对于统计查询count,不需要根据聚簇索引统计
索引覆盖
表示索引中唯一值的数目估计,与id数量比值应该接近1,如果很小删除该索引
优化器会根据该值判断是否走索引
非实时更新
随机采样分析,随机选8个叶子节点分析
cardinality
show index from table
查看冗余索引
应该尽量扩展已有的索引而不是创建新索引
schema_redundant_indexes
B+树索引
倒排索引
全文检索
不可人为干预
innodb_adaptive_hash_index(默认开启)
哈希索引
虽然辅助索引是有序的,但是如果再需要聚簇索引查数据行会随机读取
当查找数据大于表数据20%时
范围查找
join
优化器选择不适用索引的情况
目的就是为了减少磁盘的随机访问,并且将随机访问转化为较为顺序的数据访问
将查询得到的辅助索引键值存放于一个缓存中,这时缓存中的数据是根据辅助索引键值排序的
将缓存中的键值根据RowID进行排序
根据RowID的排序顺序来访问实际的数据文件
MRR使数据访问变得较为顺序。在查询辅助索引时,首先根据得到的查询结果,按照主键进行排序,并按照主键排序的顺序进行书签查找
减少缓冲池中页被替换的次数
批量处理对键值的查询操作
好处
Multi-Range Read(MRR)优化
当进行索引查询时,首先根据索引来查找记录,然后再根据WHERE条件来过滤记录。在支持Index ConditionPushdown后,MySQL数据库会在取出索引的同时,判断是否可以进行WHERE条件的过滤,也就是将WHERE的部分过滤操作放在了存储引擎层
Index Condition Pushdown(ICP)优化
索引优化
索引
为了支持对共享资源进行并发访问,提供数据的完整性和一致性
目的
允许事务读
共享锁(S锁)
允许事务写
排他锁(X锁)
事务在请求S锁前,要先获得IS锁
意向共享锁(IS)
事务在请求S锁前,要先获得IX锁
意向排他锁(IX)
类型
锁住索引记录,如果没有索引就是用隐式主键索引
单个行记录上的锁
Record Lock
间隙锁,锁定范围,不包括记录本身
Gap Lock
解决幻读
当查询的索引含有唯一属性时,降级为Record Lock,提高并发性
结合Record Lock 和Gap Lock ,锁定一个范围
Next-Key Lock
Phantom Problem是指在同一事务下,连续执行两次同样的SQL语句可能导致不同的结果,第二次的SQL语句可能会返回之前不存在的行
Next-Key Locking机制来避免
REPEATABLE READ级别下
幻读
不需等待X锁释放
通过undo段完成
read committed 读取最新的snapshot
repeatable read 读取事务开始时的snapshot
行多版本控制策略 MVCC(multi version concurrency control)
一致性非锁定读(consistent nonlocking read)
明确指定主键,且主键存在
无锁
明确指定主键,且主键不存在
无主键
主键不明确
select … for update
行记录S锁
select … lock in share mode
一致性锁定读
不是事务完成后释放,完成自增长值的插入后立即释放
自增长列必须是索引,必须是第一列,否则异常
自增长锁
read comitted 只使用行锁
行锁(record lock)
锁定一个范围不包括自身
间隙锁(gap lock)
锁定范围,包括自身
当查询属性含有唯一索引时,并且是等值查询,降级为行锁
next-key lock
算法
读取到未提交的数据
区别脏页,脏页时正常现象,不会影响数据一致性
脏读
同一事务中读取数据不一致
违反数据一致性
不可重复读
行加X锁,操作串行化
事务的更新被另一个事务覆盖
丢失更新
问题
锁的信息链
事务等待链
深度优先遍历,若存在环,即存在死锁
检测到死锁后立马回滚一个事务
返回结果中包括死锁相关事务的详细信息,如引发死锁的SQL语句,事务已经获得的锁,正在等待什么锁,以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施
show engine innodb status
wait-for graph(等待图)死锁检测
死锁
锁
这时SQL语句通过索引列b进行查询,因此其使用传统的Next-Key Locking技术加锁,并且由于有两个索引,其需要分别进行锁定
对于聚集索引,其仅对列a等于5的索引加上Record Lock
而对于辅助索引,其加上的是Next-Key Lock,锁定的范围是(1,3)
特别需要注意的是,InnoDB存储引擎还会对辅助索引下一个键值加上gap lock,即还有一个辅助索引范围为(3,6)的锁
SELECT*FROM z WHERE b=3 FOR UPDATE
例子
atomicity(原子性)
consistency(一致性)
该事务提交前对其他事务不可见
isolation(隔离性)
duralibity(持久性)
ACID
不能提交或回滚事务的某一部分
扁平事务
存在savepoint(保存点),发生错误后能回到保存点当时的状态
带有保存点的扁平事务
叶子节点是扁平事务
任何子事务只有在顶层事务提交后才真正提交
任何一个事务回滚都会引起子节点回滚
嵌套事务
分类
保证原子性、持久性
恢复事务对页的操作
redo 记录的是数据修改之后的值,不管事务是否提交都会记录下来
物理日志
log sequence number 当前lsn
log flushed up to 刷新到redo log的lsn
last checkpoint at 刷新到磁盘的lsn
show engine innodb status
重做日志写入总量
checkpoint位置
在每个页的头部,有一个值FIL_PAGE_LSN,记录了该页的LSN,表示该页最后刷新时LSN的大小
页的版本
作用
LSN(log sequence number)存在于每个页的头部
innodb启动时,无论上次是否正常关闭都会进行恢复操作
redo log
根据每行进行记录
保证一致性
只是将逻辑回滚到原来的样子
因为并发存在,所以不可能将数据结构和页本身回到原来的样子
存的是和当前事务相反的操作
逻辑日志
存在共享表空间undo段
为回滚做准备
实现mvcc
会产生redolog
因为插入操作只对本身事务可见
事务提交后直接删除
插入undo log
update 或者delete产生
等待purge线程删除
事务提交后放入undo log链表
更新undo log
两种类型
undo log
实现
delete操作时 将行记录delete flag置为1
若没有可以删除
因为mvcc所以不能立即删除,防止有其他事务引用该行
通过purge判断是否可以删除
用于完成delete/update的最终操作
purge
事务隔离级别必须设置为SERIALIZABLE
InnoDB支持XA
一个或者多个资源管理器(Resource Manager)
一个事务管理器(Transaction Manager)
一个应用程序(APP)
第一阶段 所以参与事务的节点prepare ,告诉事务管理器准备好了
第二阶段 事务管理器告诉资源管理器rollback/commit
两阶段提交(two-phase commit)
外部XA事务
JTA支持
存在于存储引擎和插件之间或者存储引擎与存储引擎之间
主服务器(master)把数据更改记录到二进制日志(binlog)中
从服务器(slave)把主服务器的二进制日志复制到自己的中继日志(relay log)中
从服务器重做中继日志中的日志,把更改应用到自己的数据库上,以达到数据的最终一致性
1、2成功后,3失败
主从不一致情况
先进行prepare操作,将事务id记录
写入binlog
如果同步binlog后,主节点宕机,那么下次启动检查prepare的事务id是否提交,若否重新提交
内部XA事务保证主从一致
主从复制过程
内部XA事务
分布式事务
事务
宕机时,通过redo log恢复数据,即ACID中D的要求
write ahead log (事务提交时,先写日志再修改页)
记录每个页更改的物理情况
扇区是最小写入单位(512kb),可以保证写入成功,不需要doubler write
默认1,即事务提交必须刷盘
0,事务提交不进行写入redo log操作,只在master thead中操作
innodb_flush_log_at_trx_commit
show variables like 'log_error' 定位
错误日志(error log)
记录所有更改的操作,不包括select、show等非更改操作
事务提交后一次写入
主从复制
不需要记录每一行的变化,减少bin log日志,节约io,提升性能
可能导致主从不一致,比如执行函数()
记录逻辑sql语句
statement
记录表物理更改情况
可以将事务隔离设置为 read committed 获取更好并发性
会产生大量日志
增加磁盘开销
主从同步增加网络开销
row(通常情况使用)
默认statement 记录日志
表存储引擎为ndb
使用了uuid() 等不确定函数
特殊情况用row
mixed
bin_log_format
基于会话,事务开始时分配一个cache
记录未提交(commit)的二进制日志,事务提交后写入二进制日志
不能设置太小,若日志大于缓冲池,会被写入临时文件,增加磁盘io
bin_log_cache_size(二进制日志缓冲池,默认32k)
max_bin_log_size(单个二进制文件最大值,默认1G)
二进制日志 (bin log)
long_query_time ,默认10,即超过10秒为慢查询
log_queries_not_using_indexs (查询没有用到索引行记录)
慢查询日志(slow query log)
记录所有对mysql请求的日志,无论是否执行成功
查询日志(log)
日志
所有查询必须限定查询范围
限定数据的范围
主库写,从库读
读写分离
列数据变小,简化表结构
优点
主键冗余
需要join操作
缺点
垂直拆分
水平拆分最好拆库,分布在不同的机器上
水平拆分
大表优化
特性
innodb
mysql
0 条评论
回复 删除
下一页