MYSQL
2021-06-23 18:58:57 4 举报AI智能生成
mysql总结 InnoDB架构体系 事务 锁
作者其他创作
大纲/内容
MYSQL
索引
存储模型
红黑树
不够平衡
只有两路
内存里使用，实现一致性hash
哈希索引
时间复杂度O(1)
不是顺序存储，不能排序
hash冲突转链式效率低下
B树
分叉树（路数）比关键字多1
分叉树越多，树的深度越少
更新索引导致页分裂跟合并
B+树
B树有的B+树也有
关键字多，路数更多
关键字跟路数是相等的
枝节点只存索引
读写能力强
一次IO加载更多关键字
叶子节点存储数据
效率更加稳定
叶子节点增加指相邻叶子节点的指针
扫表能力强
排序能力强
假设一条记录16bytes，一个叶子节点（一页）16K能存放多少个指针
16K/16字节 ≈ 1000指针
1000 ^ 2（深度） = 千万
一张千万的表最多3次IO
存储文件
InnoDB
.frm
表结构
.ibd
数据就是索引，索引就是数据
聚集索引
索引键值的逻辑顺序跟表数据行的物理存储顺序一致
二级索引
叶子节点存储聚集索引
需要进行回表
显式主键当索引
第一个不包含NULL的值当索引
内置6字节长的ROWID当索引
索引离散度
count（不重复的表字段内容）：count（*）
列重复越多，离散度越低，重复值越少，离散度越高
联合索引
最左匹配原则
联合索引桥
覆盖索引
数据从索引中就能到不用回表
索引下推
过滤动作在引擎做完，不需要到server层过滤
ICP默认开启
MyISAM
.MYD
D:数据文件
.MYI
I:索引文件
事务
四大特性
Atomicity：原子性
一系列的操作要么全部成功，要么全部失败
Isolation：隔离性
一个事务不被其他事务所干扰 各事务之间是独立的
Durability：持久性
数据任意增删改查只要提交成功就是永久性的
Consistent：一致性
执行事务前后，数据保持一致
并发问题
脏读
一个事物读取到其他事物未提交的数据
不可重复读
一个事物读取到其他事物已提交的数据
幻读
一个事物读取到其他事物已提交的插入数据
隔离级别
Read Uncommitted:未提交读
没解决任何问题
Read Committed：已提交读
解决脏读
Repeatable Read：可重复读
解决脏读，解决不可重复度，InnoDB解决幻读
Serializable：串行化
排队操作，不存在并发
解决读一致性
LBCC
在读取数据前，对其加锁，阻止其他事务对数据进行修改
读多写少的情况会极大的影响操作数据的效率
MVCC
生成一个数据请求时间点的一致性数据快照，并用这个快照来提供一定级别的的一致性读取
建立数据快照，同一个事物无论查多少次都是相同的数据（第一层）
一个事务能看到的数据版本：1、第一次查询之前已经提交的事务的修改 2、本事务的修改（第二层）
一个事务不能看到的数据版本：1、本事务第一次查询之后创建的事务（事务ID比我的事务ID大）2、未提交的事务修改
利用隐藏字段实现
Read View 一致性识图（第三层）
m_ids{} 列表，当前系统的事务ID
min_trx_id m_ids最小值
max_trx_id 系统分配给下一个事务的ID
creator_trx_id 生成Read View事务的事务ID
判断规则
1、从事务最早的数据开始判断（在undo log）
2、数据版本事务ID等于自己的ID，本事务修改，可以访问
3、数据版本的事务ID小于未提交事务最小的ID，说明在生成Read View已经提交，可以访问
4、数据版本的事务ID 大于 下一个事务的ID，这个版本是Read View之后才开启的事务，不能访问
5、数据版本的事务ID在最小事务ID跟最大事务ID之间，看看是否在当前事务列表中，如果在，不可以，如果不在，可以
6、如果当前版本不可见，则到undo log链中找到下一个版本
RR的Read View是事务第一次查询建立的（解决幻读）
RC的Read View是事务每次查询建立的（没解决幻读）
锁
锁的粒度
行锁/表锁
MyISAM 表锁
InnoDB 行锁
锁定力度：表锁 > 行锁
加锁效率：表锁 > 行锁
冲突概率：表锁 > 行锁
并发性能：表锁 < 行锁
锁的类型
基本模式
行锁
Shared Locks(共享锁 S）
不同的事务对于同一行数据共享一把锁，只读不可修改
Exclusive Locks(排它锁 X)
一个事务获取了一行数据，其他事务不能获取该行的锁，增删改会有默认的排它锁 查询需要加上 select ······ for update
表锁
Intention Shared Locks(意向共享锁）
事务准备给数据行加入共享锁，一个数据行加共享锁前必须先取得该表的IS锁
Intention Exclusive Locks(意向排它锁)
事务准备给数据行加入排他锁，一个数据行加排他锁前必须先取得该表的IX锁
意向锁是有数据引擎自己维护的，用户无法手动操作
锁的算法
在什么情况下锁定什么范围
Record 记录锁
条件：唯一性等值查询
Gap 间隙锁
条件：记录不存在，锁住查询范围
专门用来阻塞插入
只在RR级别中存在
Next-key 临键锁
条件：使用范围查询，包含记录跟区间
间隙锁 + 记录锁
插入意向锁等等
事务隔离级别的实现
RU
无
Serializable
所有语句会被隐式转化 加上共享锁
RC
查询
DML
只有记录锁
RR
记录锁
间隙锁
临键锁
如何选择
1、RR间隙锁会导致锁定范围的扩大
2、条件列未使用到索引，RR锁表，RC锁行
3、RC的半一致性读可以增加update的并发性
只要正常的加锁，正确的写sql语句，选哪个都行
死锁
原因
1、资源互斥
2、不能强行剥夺
3、形成等待环路
解决
1、顺序访问
2、数据排序
3、申请足够级别的锁
4、避免没有where条件的操作（不命中索引）
5、大事务分解成小事务
6、使用等值查询而不是范围查询
MYSQL体系结构
连接层
客户端
TCP/IP
UDP
...
服务层
解析器
词法解析
语法解析
预处理器
表名是否存在
解析*
查询优化器
基于最小开销生成执行计划
执行计划
explain
存储引擎
存在那张表
由谁去执行
存储引擎层
执行引擎
调用对应的API
InnoDB 架构
内存结构
Buffer Pool
存储页面信息，数据，索引
预读机制
线性预读
随机预读
LRU
存储热点数据管理缓冲池
Map+Node
5/8热区头尾
3/8冷区头尾
Change Buffer
写缓冲
节省随机读磁盘的IO消耗
通过扩大该值支持写多读少
Adaptive Hash Index
Log Buffer
redo Log刷盘时机
延迟写
适时写（默认）
每秒写
刷盘越快越安全，越消耗性能
磁盘结构
系统表空间
数据字典
存储表和索引的元数据
双写缓冲区
redo 的备份
系统一页4K，InnoDB一页16K 需要写四次才完成 需要备份机制
顺序写入
change Buffer
Undo Logs
记录语句修改前的内容
回滚操作
修改了很多次的数据会形成链条，叫undo log 链
通用表空间
独立表空间
临时表空间
临时表
Redo Log
崩溃恢复操作
InnoDB的特性
记录某个数据页上做了什么修改，属于物理日志
Bin Log日志
记录所有DDL跟DML语句
做删库恢复
隐藏字段
DB_ROW_ID 6字节：行标识
BD_TRX_ID 6字节：插入或更新的最后一个事务的事务ID，自动递增（创建版本号）
DB_ROLL_PTR 7字节：回滚指针（删除版本号）指向undo log里的内容
0 条评论
回复 删除
下一页
