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