Mysql原理大全
2021-11-01 11:27:50 1 举报AI智能生成
登录查看完整内容
mysql核心原理大全，主要包括事务、锁、索引等内容
作者其他创作
大纲/内容
物理备份
逻辑备份
备份恢复
保证高可用的方法是冗余。但是数据冗余带来的问题是数据一致性问题。
最近数据读主库
一致性
可用性
主从读拓展
分库分表读写拓展
拓展性
主从复制
数据恢复
使用场景
sync_binlog参数控制
刷盘时机
statment
row
mixed
日志格式
binlog日志
数据备份，避免影响业务（高可用）
读写分离，提供查询服务（读扩展）
实时灾备，用于故障切换（高可用）
作用
分支主题
1.主库将数据库的变更操作记录到Binlog日志文件中
2.从库读取主库中的Binlog日志文件信息写入到从库的Relay Log中继日志中
步骤
上述过程都是异步操作，俗称异步复制，存在数据延迟现象
主从异步复制原理
为了提升数据安全，MySQL让Master在某一个时间点等待Slave节点的 ACK（Acknowledgecharacter）消息，接收到ACK消息后才进行事务提交
1、事务写入redo，处于prepare阶段
2、写入binlog日志
3、事务写入redo，提交事务
4、发送到从库
主库事务写入分为 4个步骤
当Master需要在第三步等待Slave返回ACK时，即为 after-commit，半同步复制（MySQL 5.5引入）
主库等待从库写入 relay log 并返回 ACK 后才进行Engine Commit。
当Master需要在第二步等待 Slave 返回 ACK 时，即为 after-sync，增强半同步（MySQL 5.7引入）
有损半同步复制
区别于有损，无损半同步在ack之后再提交
开启时默认创建了单独的应答接收线程，变成了双工模式，发送和接收互不影响
SLAVE 从库接收完二进制日志后给主库一个确认并不管relaylog中继日志是否正确执行完
即使SQL线程报错了，半同步复制还是不会切换成异步复制
半同步复制是跟 IO_THREAD 有直接关系，跟 SQL_THREAD 没关系
控制slave应答的数量，默认是1，表示master接收到几个slave应答后才commit
rpl_semi_sync_master_wait_for_slave_count
slave响应后再提交
AFTER_SYNC
提交后再等待响应
after_commit
rpl_semi_sync_master_wait_point
表示主上是否开启半同步复制功能，可以动态修改。可选值：ON\\OFF
rpl_semi_sync_master_enabled
超过这个时间值没有收到信息，则切换到异步复制，默认10000毫秒
rpl_semi_sync_master_timeout
如果没有Slave链接，会切换到异步复制。是否允许master每个事务提交后都要等待slave的接收确认信号。默认为on，每一个事务都会等待
rpl_semi_sync_master_wait_no_slave
主
表示从上是否开启半同步复制功能，可以动态修改。可选值：ON\\OFF
rpl_semi_sync_slave_enabled
说明支持和注册半同步复制的已连Slave数
Rpl_semi_sync_master_clients
master等待slave回复的平均等待时间，单位毫秒
Rpl_semi_sync_master_net_avg_wait_time
master总的等待时间
Rpl_semi_sync_master_net_wait_time
master等待slave回复的的总的等待次数
Rpl_semi_sync_master_net_waits
master关闭半同步复制的次数
Rpl_semi_sync_master_no_times
master没有收到slave的回复而提交的次数
Rpl_semi_sync_master_no_tx
ON是活动状态（半同步），OFF是非活动状态（异步）
Rpl_semi_sync_master_status
Slave上的半同步复制状态，ON表示已经被启用，OFF表示非活动状态
Rpl_semi_sync_slave_status
master花在每个事务上的平均等待时间
Rpl_semi_sync_master_tx_avg_wait_time
Rpl_semi_sync_master_tx_wait_time
master等待成功的次数，即master没有等待超时的次数，也就是成功提交的次数
Rpl_semi_sync_master_tx_waits
master提交后来的先到了，而先来的还没有到的次数
Rpl_semi_sync_master_wait_pos_backtraverse
前有多少个session因为slave的回复而造成等待
Rpl_semi_sync_master_wait_sessions
master成功接收到slave的回复的次数，即半同步模式成功提交数量
Rpl_semi_sync_master_yes_tx
从
相关参数
无损半同步复制
5.5单线程复制
从库为每一个库分配了一个线程。以此提高复制的效率。
判断可以并行执行，那么选择worker线程执行事务的二进制日志
如不可以并行执行，如DDL或是事务跨schema操作，则等待所有的worker线程执行完成之后，再执行当前的日志
开启并行复制SQL线程就变为了coordinator线程
5.6基于库的并行复制
所有处于 prepare 阶段的事务同属于一个组，一个组内的事务可以并行提交
同组事务到了 slave 端就可以并行处理
last_committed 相同的属于同一个组
last_committed = 0 中只有 1 个事务
last_committed = 1 中有 2-8 7个事务
假设 slave 端的 parallel 是 10，那么就可以同时执行7个事务
开启GTID的情况
匿名GTID
Anonymous_Gtid
未开启GTID情况
判断事务是否在同一组
如何设置binlog_group_commit_sync_delay的大小控制好并发以及延长等待的问题
组大小越大，对事物的延迟等待就越高
存在的问题
基于库的并行复制方式
DATABASE（默认）
基于组提交(逻辑时钟)的并行复制方式
LOGICAL_CLOCK
slave-parallel-type
并发数，如为0则为单线程，大于1则为多线程
slave_parallel_workers
多少微妙提交一次
binlog_group_commit_sync_delay
在等待上面参数超时之前达到多少个事务提交一次
binlog_group_commit_sync_no_delay_count
强一致性的提交顺序
slave_preserve_commit_order
在队列中pening等待执行当前事务需要的内存大小，该值要比主库的max_allowed_packet大
slave_pending_jobs_size_max
[mysqld]# 只需在slave端设置slave_parallel_workers = 4 ###并行复制的线程数slave_parallel_type = LOGICAL_CLOCK ###并行复制的类型，默认databasemaster_info_repository = tablerelay_log_info_repository = tablerelay_log_recovery = 1
开启组复制
5.7基于组复制的并行复制
writeset的思想是：不同事物修改了不同行的数据，那么可以视为同一组
WriteSet是站在组提交这个巨人的基础之间建立起来的，且是在master上做的自适应打包分组
判断是否同一组：与组提交一样，通过last_committed，sequence_number
set global binlog_transaction_dependency_tracking = WRITESET
transaction_write_set_extraction = XXHASH64
开启writeset(主库)
set global binlog_transaction_dependency_tracking = COMMIT_ORDER
关闭writeset
writeset
组提交的尿性延迟问题
相比“组提交”来说更加灵活
并发度和性能上组提交会更加好
在一些WriteSet没有办法是否冲突时，能平滑过度到“组提交”模式
解决的问题
最高并发可达到该大小的一半左右，如12500
哈希表存储大小:binlog_transaction_dependency_history_size
5.7.22以上基于写集的并行复制
主从并行复制
mysql5.7之后支持N个Master对应1个Slave
不同的主库通过cannal来区分
多源复制
允许Slave延迟回放接收到的二进制日志，为了避免主服务器上的误操作，马上又同步到了从服务器，导致数据完全丢失
CHANGEMASTERTOmaster_delay=3600--落后Master服务器1个小时
常用于备份架构设计中
延迟复制
以从库作为主库再做一级复制
级联复制
MGR
PXC
全同步复制
强化数据库的主备一致性， 故障恢复及容错能力。取代过去传统的主从复制
主备切换的情况下其他slave可以自动在新主上找到正确的复制位置
基于GTID的复制可忽略已经执行过的事务减少数据发生不一致的风险
全局事务ID(Global Transaction ID)Mysql5.6开始
GTID是由server_uuid和事务id组成：GTID=server_uuid:transaction_id
MySQL第一次启动时自动生成唯一值并持久化到auto.cnf文件
server_uuid
从1开始的自增计数表示在这个主库上执行的第n个事务，事务与GTID之间1:1映射
transaction_id
如：b6af5b5c-666f-11e9-bed3-000c29b85ea6:1-5
一组连续的事务用\"-\"连接的事务序号范围表示
组成
发生切换时通过内部机制自动找点同步
实现多线程复制（基于库）
多线程复制是基于组提交方式实现的，而组提交信息是存储在GTID中
1、master更新数据时，会在事务前产生GTID，一同记录到binlog日志中
Slave连接到Master时，会把gtid_executed中的gtid发给Master，Master会自动跳过这些事务，只将没有复制的事务发送到Slave去
2、slave端的I/O线程将变更的binlog，写入到本地的relay log中
3、SQL线程从relaylog中获取GTID，再对比slave端的binlog是否有记录（MySQL5.6 slave端必须开启binlog）
4、如果有记录，说明该GTID的事务已经执行，slave会忽略
5、如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog
6、在解析过程中会判断是否有主键，如果没有就用二级索引，如果再没有就用全部扫描
复制实现的工作原理
1、 MySQL5.7之后才开始支持动态切换GTID相关的参数
2、 不支持CREATE TABLE ... SELECT statements
3、 不支持CREATE TEMPORARY TABLE statements inside transactions
4、 transaction or statement 既更新了事务表又更新了非事务表
5、 使用GTID复制从库跳过错误时，不支持执行sql_slave_skip_counter参数的语法
使用限制
不产生GTID，Slave只接受不带GTID的事务
OFF
不产生GTID，Slave即接受不带GTID的事务，也接受带GTID的事务
OFF_PERMISSIVE
产生GTID，Slave即接受不带GTID的事务，也接受带GTID的事务
ON_PERMISSIVE
产生GTID，Slave只能接受带GTID的事务
ON
gtid_mode
如果开启GTID功能则此参数必须要开启
slave在做同步复制时，无须找到binlog日志和POS点，直接change master to master_auto_position=1即可
enforce-gtid-consistency
在当前实例上执行过的GTID集合，包含了所有记录到binlog中的事务
设置set sql_log_bin=0后执行的事务不会生成binlog事件，也不会被记录此中
执行reset master可以将该变量清空
gtid_executed
记录已经被清除了的binlog事务集合
是gtid_executed的子集，只有gtid_executed为空时才能手动设置该变量，此时会同时更新gtid_executed为和gtid_purged相同的值
gtid_executed为空意味着没有启动过基于GTID的复制或执行过reset master
即始终保持gtid_purged是gtid_executed的子集
执行reset master时同样也会把gtid_purged置空
gtid_purged
自动生成下一个GTID，实现上是分配一个当前实例上尚未执行过的序号最小的GTID
AUTOMATIC
设置后执行事务不会产生GTID
ANONYMOUS
可以指定任意形式合法的GTID值，但不能是当前gtid_executed中的已经包含的GTID，否则下次执行事务会报错
显式指定的GTID
gtid_next
mysql-server只需打开最老的和最新的这2个binlog文件
gtid_purged和gtid_executed值根据这些文件中的Previous_gtids_log_event或者Gtid_log_event计算得出
开启(5.7默认，建议)
所有的binlog都要被检查。可能需要非常长的时间
关闭(5.6默认)
binlog_gtid_simple_recovery(5.7.7之后)
关闭
返回当前事务产生的GTID
OWN_GTID
返回系统执行的所有GTID，也就是GTID_EXECUTED
ALL_GTIDS
session_track_gtids
表示正在执行的事务的gtid以及对应的线程ID
gtid_owned
表示控制每执行多少个事务，对此表（mysql.gtid_executed）进行压缩
默认1000个事务
gtid_executed_compression_period
在线开启和关闭GTID
存储GTID信息到表中mysql.gtid_executed，slave无需开启binlog
5.7新特性
在切换binlog时将当前binlog的所有GTID插入gtid_executed表中
开启了binlog
每个事务在提交之前会执行一个等价的INSERT的操作
此操作是该事务的一部分，和事务的其他操作整体保持原子性。 需要保证gtid_executed是innodb存储引擎
未开启binlog
GTID信息的记录
GTID
状态为:reading event from the relay log
一个事务主库执行多久，从库回放就需要多久
大事务延迟
状态为:altering table
大表DDL延迟
会造成延迟的瞬时增加
长期未提交的事务延迟
状态为:system lock 或者 reading event from the relay log
产生的原因：event回放时候使用了二级索引让回放速度慢且进行了大量的内存数据查找造成了CPU 100%而没有I/O的现象
解决方法：增加主键
没有主键或者唯一键
innodb层锁造成延迟
从库参数设置不合理
从库大批量查询
主从延迟
主从模式
MMM
双主模式
子主题
架构图
MHA Manager（管理节点）
和MHA Node（数据节点）
MHA Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其他的slave重新指向新的master，整个故障转移过程对应用程序完全透明。
（1）从宕机崩溃的master保存二进制日志事件（binlogevents）；
（2）识别含有最新更新的slave；
（3）应用差异的中继日志（relaylog）到其他slave；
（4）应用从master保存的二进制日志事件（binlogevents）；
（5）提升一个slave为新master；
（6）使其他的slave连接新的master进行复制。
故障切换流程
原理
自动故障转移快
主库崩溃不存在数据一致性问题
性能优秀，支持半同步复制和异步复制
一个Manager监控节点可以监控多个集群
优点
只支持BINLOGV4版本，要求MySQL5.0或更高版本。
候选master节点必须开启log-bin参数，如果所有从节点都为开启，则不进行故障转移
在MHA0.52版本前不支持多master模式
HA默认不支持多级主从复制，通过修改配置文件和设置multi_tier_slave参数
缺点
主从MHA
Orchestrator
特别适合应用于对于数据一致性要求极高的金融级业务场景
MGR集群
Mysql Shell
Mysql Router
组件
MGR之间的数据同步并没有采用复制技术，而是采用GCS（GroupCommunicationSystem）协议的日志同步技术。
GSC本身是一种类似Paxos算法的协议，要求组中的大部分节点都接收到日志，事务才能提交
Paxos算法，集群的节点要求数量是奇数个
单主模式只有1个节点可以写入
MGR可以自动进行Failover切换，不用依赖外部的各种高可用套件，所有的事情都由数据库自己完成
单主
多主模式能让每个节点都可以写入。
如果存在变更同一行的冲突，MySQL会自动回滚其中一个事务，自动保证数据在多个节点之间的完整性和一致性。
多主
模式
仅支持InnoDB表，并且每张表一定要有一个主键；
目前一个MGR集群，最多只支持9个节点；
有一个节点网络出现抖动或不稳定，会影响集群的性能；
缺点与限制
Mysql集群
将整个表按每个分区分成多个ibd文件
对于引擎层来说，这是 多个表；
对于 Server 层来说，这是 1 个表。
表分区
一个库中的表拆到多个库
一个表拆按字段和业务成多个表
垂直拆分
水平拆分
范围
hash取模
一致性hash
分片
停机扩容
平滑扩容
扩容方案
分库分表
数据量太大，一台机器无法承受
读写操作量太大，超出一台机器承受
一旦数据库宕机，应用整体崩溃，可用性低
单机单库
数据量太大，超出一台服务器承受能力
写操作量大，超过master承受能力
主从架构（读写分离）
sass云数据库
数据库架构演变
连接池
安全连接
认证授权
连接层
连接器
缓存
分析器
优化器
执行器
Server层
InnoDB
MyISm
memory
存储引擎层
error log
general log
binlog
slow query log
redo log
undo log
InnoDB日志
日志文件
my.cnf/my.ini
配置文件
数据文件
系统文件层
体系架构
物理及内存结构图
free list
flush list
lru list
Buffer Pool
Change Buffer
Log Buffer
Adaptive Hash Index
Buffer Pool在线扩容
大数据量查询导致Buffer Pool需要腾出空间，淘汰未使用的脏页需要刷入磁盘
redolog写满后触发的刷脏
数据库偶发抖动
Buffer Pool分为多个instance，共享资源加锁，多个实例可以降低锁的粒度
Buffer Pool线程安全问题
内存结构
数据字典
Doublewrite Buffer
Undo Logs
系统表空间
TableSpace
Segment
Extent
Page
Row
文件结构
Antelope
Barracuda
文件格式
四种行格式支持
行格式(Row_format)
数据存储格式
独立表空间
通用表空间
undo 表空间
临时表空间
示意图
磁盘结构
IO Thread
Purge Thread
Page Cleaner Thread
Master Thread
后台线程
Undo Log
Redo Log
日志
从索引存储结构划分：B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引
从应用层次划分：普通索引、唯一索引、主键索引、复合索引
从索引键值类型划分：主键索引、辅助索引（二级索引）
从数据存储和索引键值逻辑关系划分：聚集索引（聚簇索引）、非聚集索引（非聚簇索引）
索引类型
全文索引是MyISAM的一种特殊索引类型，主要用于全文索引；InnoDB从MYSQL5.6版本提供对全文索引的支持。
它用于替代效率较低的LIKE模糊匹配操作，而且可以通过多字段组合的全文索引一次性全模糊匹配多个字段。
同样使用B-Tree存放索引数据，但使用的是特定的算法，将字段数据分割后再进行索引（一般每4个字节一次分割），索引文件存储的是分割前的索引字符串集合，与分割后的索引信息，对应Btree结构的节点存储的是分割后的词信息以及它在分割前的索引字符串集合中的位置。
Full-Text全文索引
主要就是通过Hash算法，将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。
检索算法：在检索查询时，就再次对待查关键字再次执行相同的Hash算法，得到Hash值，到对应Hash表对应位置取出数据即可，如果发生Hash碰撞，则需要在取值时进行筛选。
MySQL目前有Memory引擎和NDB引擎支持Hash索引。
Hash索引
Innodb存储引擎会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，建立哈希索引可以带来速度的提升
经常访问的二级索引数据会自动被生成到hash索引里面去(最近连续被访问三次的数据)，自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。
无序，没有树高
降低对二级索引树的频繁访问资源
自适应
hash自适应索引会占用innodb buffer pool；
自适应hash索引只适合搜索等值的查询，如select * from table where index_col='xxx'
极端情况下，自适应hash索引才有比较大的意义，可以降低逻辑读
MySQL自动管理，人为无法干预
34673：字节为单位，占用内存空间总量
通过hash searches、non-hash searches计算自适应hash索引带来的收益以及付出，确定是否开启自适应hash索引
show engine innodb status\\G
innodb_adaptive_hash_index
查看开启状态
状态监控
自适应哈希索引
二分查找
Hash表
索引值和data数据分布在整棵树结构中
每个节点可以存放多个索引值及对应的data数据
树节点中的多个索引值从左到右升序排列
B-tree
非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
叶子节点包含了所有的索引值和data数据
叶子节点用指针连接，提高区间的访问性能
B+tree(mysql索引使用的存储结构)
B树索引原理
NULL：表示不用访问表，速度最快。
const：表示使用主键或唯一索引做等值查询，常量查询。
eq_ref：多表join查询，表示前表每一个记录都只能匹配后面表的一行结果。
ref：表示使用非唯一索引进行单值查询。
range：表示使用索引范围查询。使用>、>=、<、<=、in等等。
index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。
ALL：表示全表扫描，性能最差。
type
key
rows
Using where
Using index
Using index Condition
Using filesort
Using temprorary
Extra
执行计划
离散度高的键做索引
覆盖索引和索引下推，优化回表查询
代码先行，索引后上
联合索引尽量覆盖条件
不要在小基数字段上加索引
group和order会用不上
长字符串采用前缀索引
where与order 冲突优先where
全值(等值)匹配 是最优选择；
不在索引列上做任何操作（计算、函数、类型转换），会导致索引失效而转向全表扫描；
存储引擎不能使用索引中范围条件右边的列；
尽量使用覆盖索引，减少select；
like "xxx%" 是可以用到索引的，like以通配符开头('%abc'或'%abc%')索引失效会变成全表扫描的操作；
字符串不加单引号索引失效；
少用or，用它来连接时会索引失效；
<、<=、>、>=、BETWEEN、IN 可用到索引，<>、not in、!= 则不行，会导致全表扫描；
优化原则
如果可以找到偏移的那条数据，就可以达到优化目的
先用主键过滤
根据自增且连续的主键排序的分页查询
使用索引排序
根据非主键字段排序的分页查询
分页查询优化
嵌套循环连接算法NLJ
基于块的嵌套循环连接BNLJ
常用算法
关联字段加索引，让mysql做join操作是尽量选择NLJ算法
小标驱动大表，写多表连接sql时如果明确知道哪张表是小表可使用straight_join写法固定驱动表
优化
in和exsits优化
将总数维护到redis里
增加数据库计数表
show table status
count查询优化
join关联优化
MySQL支持两种方式的排序：Index和FileSort；index 效果高（扫描索引本身完成排序），FileSort效率较低。
order by 语句使用索引最左前列；
使用where子句与order by子句条件组合满足索引最左前列；
order by 子句尽量使用 Index方式排序，避免使用FileSort方式排序；
双路排序：MySQL4.1之前是使用该方式，两次扫描磁盘，最终得到数据。
单路排序：从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，效率高于双路排序。
filesort方式排序有两种算法
增大 sort_buffer_size 参数的设置；
增大 max_length_for_sort_data 参数的设置；
优化策略
order by 关键字优化
group by 实际是先排序后进行分组，遵照索引建的最佳左前缀；
当无法使用索引列，增大 max_length_for_sort_data和sort_buffer_size参数的设置；
where 高于having，能在where限定的条件就不要去having限定了；
group by 关键字优化
索引优化
流程示意图
1.客户端请求
2.连接器（验证用户身份，给予权限）
3.查询缓存（存在缓存则直接返回，不存在则执行后续操作）
4.分析器（对SQL进行词法分析和语法分析操作）
5.优化器（主要对执行的sql优化选择最优的执行方案方法）
6.执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口）
7.去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）
8.返回给客户端
sql查询流程
索引
持久性
原子性
隔离性
事务及其ACID属性
脏读
不可重复读
快照读，是不会看到别的事务插入的数据的
前提条件：InnoDB引擎，可重复读隔离级别，使用当前读时
表现：一个事务(同一个read view)在前后两次查询同一范围的时候，后一次查询看到了前一次查询没有看到的行
事务A读取到了事务B提交的新增数据，不符合隔离性
幻读专指新插入的行，读到原本存在行的更新结果不算。因为当前读的作用就是能读到所有已经提交记录的最新值。
将两行记录间的空隙加上锁，阻止新记录的插入；这个锁称为间隙锁。
间隙锁与间隙锁之间没有冲突关系。跟间隙锁存在冲突关系的，是往这个间隙中插入一个记录这个操作。
解决方法
幻读
并发事务带来的问题
读未提交
读已提交
可重复读
可串行化
隔离级别
排队执行
引入锁之后就可以支持并发处理事务，如果事务之间涉及到相同的数据项时，会使用排他锁，或叫互斥锁，先进入的事务独占数据项以后，其他事务被阻塞，等待前面的事务释放锁。
排他锁
读和写操作：读读、写写、读写、写读。
读写锁就是进一步细化锁的颗粒度，区分读操作和写操作，让读和读之间不加锁，让两个事务就可以同时被执行。
读写锁，可以让读和读并行，而读和写、写和读、写和写这几种之间还是要加排他锁。
读写锁
多版本控制MVCC，也就是Copy on Write的思想。
MVCC除了支持读和读并行，还支持读和写、写和读的并行，但为了保证一致性，写和写是无法并行的。
在事务开始写操作的时候会copy一个记录的副本，其他事务读操作会读取这个记录副本，因此不会影响其他事务对此记录的读取，实现写和读并行。
MVCC
事务的演进
快照读（SnapshotRead）
当前读（CurrentRead）
读操作类型
快照链
事务链表
m_ids
检索的行对应的事务id小于该值，说明已提交的，可见
min_trx_id
大于该id，说明未提交，不可见
max_trx_id
当前事物id
creator_trx_id
ReadView
MVCC原理
1、从磁盘文件加载缓存数据
2、写入数据旧值到undo日志文件
3、更新内存数据
4、将变更写入redo日志缓冲区，处于prepare准备提交阶段
5、redo处于准备提交事务状态(期间redo日志不定期刷入磁盘）
6、执行器将该操作生成binlog日志并写入磁盘
7、提交事务，写入binlog文件与位置和commit标记到redo日志文件
8、IO线程根据刷盘策略将脏数据写入数据文件
流程图
redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的
redo与binlog的区别
redo log 的写入拆成了两个步骤：prepare 和 commit
写redo prepare成功，binlog写之前数据库崩溃
重启恢复后虽然没有提交成功，但是满足prepare和binlog的完整，重启后会从binlog日志的Xid中获取提交的信息重新将该事务重做并commit。备份恢复后与源库数据一致
写redo prepare成功，binlog成功，commit之前崩溃
目的：为了让两份日志(redo和binlog)之间的逻辑一致
redo记录了变更，之后发生宕机，binlog没有记录，实例恢复后仍然能够把数据恢复回来，但日后通过binlog做数据恢复时则没有对应数据变更
先写redo在写binlog
binlog记录了变更，之后发生宕机，redo没有记录，实例恢复后该变更无效，但日后通过binlog做数据恢复时则会恢复对应数据变更
先写binlog再写redo
不采用两阶段提交存在的问题
在提交事务时，每秒触发一次缓存日志回写磁盘操作，并调用操作系统fsync刷新IO缓存
0
在每个事务提交时，InnoDB立即将缓存中的redo日志回写到日志文件，并调用操作系统fsync刷新IO缓存
1
在每个事务提交时，InnoDB立即将缓存中的redo日志回写到日志文件，但并不马上调用fsync来刷新IO缓存，而是每秒只做一次磁盘IO缓存刷新操作
2
innodb_flush_log_at_trx_commit
每秒刷盘，写入os系统缓存
每次提交刷盘，不经过系统缓存，直接写入磁盘
每n次提交刷盘
n
sync_binlog
两阶段提交
innodb_flush_log_at_trx_commit=1
sync_binlog=1
前提：设置了双1
目的：将binlog的多个刷盘操作合并成一个，最大化每次刷盘的收益，弱化磁盘瓶颈，提高性能
将Redo log中prepare阶段的数据刷盘
将binlog数据写入文件，此时只是写入文件系统的缓冲
Flush阶段队列的作用是提供了Redo log的组提交
如果这一步完成后数据库崩溃，由于协调者binlog中不保证有该组事务的记录，所以MySQL可能会在重启后回滚该组事务
Flush 阶段
这里为了增加一组事务中的事务数量，提高刷盘收益
在等待N μs后，开始事务刷盘
默认0为不开启，单位微秒
最大 1000000ms == 1s
binlog_group_commit_sync_delay=N
如果队列中的事务数达到N个，忽略时间的等待，直接开始刷盘
默认0为不开启，依赖上一参数
binlog_group_commit_sync_no_delay_count=N
binlog刷盘
Sync阶段队列的作用是支持binlog的组提交
如果在这一步完成后数据库崩溃，由于协调者binlog中已经有了事务记录，MySQL会在重启后通过Flush 阶段中Redo log刷盘的数据继续进行事务的提交
Sync 阶段
获取队列中的事务组，依次将Redo log中已经prepare的事务在引擎层提交
Flush阶段中的Redo log刷盘已经足够保证数据库崩溃时的数据安全
Commit阶段不用刷盘
此处队列的作用是承接Sync阶段的事务，完成最后的引擎提交，使得Sync可以尽早的处理下一组事务，最大化组提交的效率
Commit 阶段
三个阶段
组提交
事务执行过程
1、传统的关系型数据库使用这种锁机制，比如行锁、表锁、读锁、写锁等
2、Java 里面的同步 synchronized 关键字的实现
悲观锁的实现
处理加锁的机制会会产生额外的开销，增加产生死锁的几率。降低并行性
为数据处理的安全提供了保证
悲观锁
1、CAS 实现：Java 中java.util.concurrent.atomic包下面的原子变量
2、版本号控制：在数据表加上数据版本号 version 字段，表示数据被修改的次数。当数据被修改时，version 值会 +1。当线程 A 要更新数据时，在读取数据的同时也会读取 version 值，在提交更新时，若刚才读取到的 version 值与当前数据库中的 version 值相等时才更新，否则重试更新操作，直到更新成功。
乐观锁的实现
不会产生任何锁和死锁
乐观锁
按性能分
共享锁
读锁
排它锁
写锁
按类型分(都是悲观)
记录锁Record Lock
间隙锁Gap Lock
临键锁Next-key Lock
行锁
意向读锁
意向写锁
表锁
页锁
按粒度分
非innodb引擎，需要使用Flushtablewithreadlock命令
使用--single-transaction参数，利用mvcc提供一致性视图，而不使用全局锁，不会影响业务的正常运行
innodb引擎，mysqldump命令
加全局锁
mysql>unlock tables
断开加锁session的连接
解全局锁
如果在主库上做全局锁操作，业务基本停摆
如果在从库上做全局锁操作，备份期间从库不能更新主库同步过来的binlog，可能导致主从不一致
如果不加锁，备份完成后可能得到不一致的状态，不安全，所以一定要加锁
影响
全局锁
对于MDL锁而言，select会阻塞alter，而alter不会阻塞select。在rename的瞬间，alter是会阻塞select的
表上存在未提交的事务，导致alter等待MDL，对于MDL锁而言，update会阻塞alter，同样alter也会阻塞update。
存在一个查询失败的语句，比如查询不存在的列，语句失败返回，但是事务没有提交，此时alter仍然会被堵住。
当做DML操作时，会申请一个MDL读锁
当做DDL操作时，会申请一个MDL写锁
读锁之间不互斥，读写和写写之间都互斥
元数据锁metadatalock
服务层锁
锁住整张表，通过不同的表锁设置，控制并发访问
引擎层锁
按层级分
锁
事务
Mysql原理大全
职业：暂无
0 条评论
回复 删除
下一页
