首页  思维导图  详情

Mysql原理大全

2021-11-01 11:27:50   1  举报





AI智能生成

mysql核心原理大全，主要包括事务、锁、索引等内容

mysql

事务

索引

数据库

架构

作者其他创作

大纲/内容

备份恢复

物理备份

逻辑备份

Mysql集群

可用性

保证高可用的方法是冗余。但是数据冗余带来的问题是数据一致性问题。

一致性

相关参数

主

rpl_semi_sync_master_wait_for_slave_count

控制slave应答的数量，默认是1，表示master接收到几个slave应答后才commit

rpl_semi_sync_master_wait_point

AFTER_SYNC

slave响应后再提交

after_commit

提交后再等待响应

rpl_semi_sync_master_enabled

表示主上是否开启半同步复制功能，可以动态修改。可选值：ON\OFF

rpl_semi_sync_master_timeout

超过这个时间值没有收到信息，则切换到异步复制，默认10000毫秒

rpl_semi_sync_master_wait_no_slave

如果没有Slave链接，会切换到异步复制。是否允许master每个事务提交后都要等待slave的接收确认信号。默认为on，每一个事务都会等待

从

rpl_semi_sync_slave_enabled

表示从上是否开启半同步复制功能，可以动态修改。可选值：ON\OFF

Rpl_semi_sync_master_clients

说明支持和注册半同步复制的已连Slave数

Rpl_semi_sync_master_net_avg_wait_time

master等待slave回复的平均等待时间，单位毫秒

Rpl_semi_sync_master_net_wait_time

master总的等待时间

Rpl_semi_sync_master_net_waits

master等待slave回复的的总的等待次数

Rpl_semi_sync_master_no_times

master关闭半同步复制的次数

Rpl_semi_sync_master_no_tx

master没有收到slave的回复而提交的次数

Rpl_semi_sync_master_status

ON是活动状态（半同步），OFF是非活动状态（异步）

Rpl_semi_sync_slave_status

Slave上的半同步复制状态，ON表示已经被启用，OFF表示非活动状态

Rpl_semi_sync_master_tx_avg_wait_time

master花在每个事务上的平均等待时间

Rpl_semi_sync_master_tx_wait_time

master总的等待时间

Rpl_semi_sync_master_tx_waits

master等待成功的次数，即master没有等待超时的次数，也就是成功提交的次数

Rpl_semi_sync_master_wait_pos_backtraverse

master提交后来的先到了，而先来的还没有到的次数

Rpl_semi_sync_master_wait_sessions

前有多少个session因为slave的回复而造成等待

Rpl_semi_sync_master_yes_tx

master成功接收到slave的回复的次数，即半同步模式成功提交数量

主从并行复制

5.5单线程复制

5.6基于库的并行复制

从库为每一个库分配了一个线程。以此提高复制的效率。

开启并行复制SQL线程就变为了coordinator线程

判断可以并行执行，那么选择worker线程执行事务的二进制日志

如不可以并行执行，如DDL或是事务跨schema操作，则等待所有的worker线程执行完成之后，再执行当前的日志

5.7基于组复制的并行复制

所有处于 prepare 阶段的事务同属于一个组，一个组内的事务可以并行提交

同组事务到了 slave 端就可以并行处理

判断事务是否在同一组

开启GTID的情况

分支主题

last_committed 相同的属于同一个组

last_committed = 0 中只有 1 个事务

last_committed = 1 中有 2-8 7个事务

假设 slave 端的 parallel 是 10，那么就可以同时执行7个事务

未开启GTID情况

Anonymous_Gtid

分支主题

匿名GTID

存在的问题

如何设置binlog_group_commit_sync_delay的大小控制好并发以及延长等待的问题

组大小越大，对事物的延迟等待就越高

相关参数

slave-parallel-type

DATABASE（默认）

基于库的并行复制方式

LOGICAL_CLOCK

基于组提交(逻辑时钟)的并行复制方式

slave_parallel_workers

并发数，如为0则为单线程，大于1则为多线程

binlog_group_commit_sync_delay

多少微妙提交一次

binlog_group_commit_sync_no_delay_count

在等待上面参数超时之前达到多少个事务提交一次

slave_preserve_commit_order

强一致性的提交顺序

slave_pending_jobs_size_max

在队列中pening等待执行当前事务需要的内存大小，该值要比主库的max_allowed_packet大

开启组复制

[mysqld] # 只需在slave端设置 slave_parallel_workers = 4 ###并行复制的线程数 slave_parallel_type = LOGICAL_CLOCK ###并行复制的类型，默认database master_info_repository = table relay_log_info_repository = table relay_log_recovery = 1

5.7.22以上基于写集的并行复制

writeset

writeset的思想是：不同事物修改了不同行的数据，那么可以视为同一组

WriteSet是站在组提交这个巨人的基础之间建立起来的，且是在master上做的自适应打包分组

判断是否同一组：与组提交一样，通过last_committed，sequence_number

开启writeset(主库)

set global binlog_transaction_dependency_tracking = WRITESET

分支主题

transaction_write_set_extraction = XXHASH64

关闭writeset

set global binlog_transaction_dependency_tracking = COMMIT_ORDER

分支主题

解决的问题

组提交的尿性延迟问题

相比“组提交”来说更加灵活

并发度和性能上组提交会更加好

在一些WriteSet没有办法是否冲突时，能平滑过度到“组提交”模式

相关参数

哈希表存储大小:binlog_transaction_dependency_history_size

最高并发可达到该大小的一半左右，如12500

多源复制

mysql5.7之后支持N个Master对应1个Slave

不同的主库通过cannal来区分

延迟复制

允许Slave延迟回放接收到的二进制日志，为了避免主服务器上的误操作，马上又同步到了从服务器，导致数据完全丢失

CHANGEMASTERTOmaster_delay=3600--落后Master服务器1个小时

常用于备份架构设计中

级联复制

以从库作为主库再做一级复制

全同步复制

MGR

PXC

GTID

全局事务ID(Global Transaction ID)Mysql5.6开始

强化数据库的主备一致性，故障恢复及容错能力。取代过去传统的主从复制

主备切换的情况下其他slave可以自动在新主上找到正确的复制位置

基于GTID的复制可忽略已经执行过的事务减少数据发生不一致的风险

组成

分支主题

GTID是由server_uuid和事务id组成：GTID=server_uuid:transaction_id

server_uuid

MySQL第一次启动时自动生成唯一值并持久化到auto.cnf文件

transaction_id

从1开始的自增计数表示在这个主库上执行的第n个事务，事务与GTID之间1:1映射

一组连续的事务用"-"连接的事务序号范围表示

如：b6af5b5c-666f-11e9-bed3-000c29b85ea6:1-5

作用

发生切换时通过内部机制自动找点同步

实现多线程复制（基于库）

多线程复制是基于组提交方式实现的，而组提交信息是存储在GTID中

复制实现的工作原理

1、master更新数据时，会在事务前产生GTID，一同记录到binlog日志中

2、slave端的I/O线程将变更的binlog，写入到本地的relay log中

Slave连接到Master时，会把gtid_executed中的gtid发给Master，Master会自动跳过这些事务，只将没有复制的事务发送到Slave去

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_mode

OFF

不产生GTID，Slave只接受不带GTID的事务

OFF_PERMISSIVE

不产生GTID，Slave即接受不带GTID的事务，也接受带GTID的事务

ON_PERMISSIVE

产生GTID，Slave即接受不带GTID的事务，也接受带GTID的事务

产生GTID，Slave只能接受带GTID的事务

enforce-gtid-consistency

如果开启GTID功能则此参数必须要开启

slave在做同步复制时，无须找到binlog日志和POS点，直接change master to master_auto_position=1即可

gtid_executed

在当前实例上执行过的GTID集合，包含了所有记录到binlog中的事务

设置set sql_log_bin=0后执行的事务不会生成binlog事件，也不会被记录此中

执行reset master可以将该变量清空

gtid_purged

记录已经被清除了的binlog事务集合

是gtid_executed的子集，只有gtid_executed为空时才能手动设置该变量，此时会同时更新gtid_executed为和gtid_purged相同的值

gtid_executed为空意味着没有启动过基于GTID的复制或执行过reset master

执行reset master时同样也会把gtid_purged置空

即始终保持gtid_purged是gtid_executed的子集

gtid_next

AUTOMATIC

自动生成下一个GTID，实现上是分配一个当前实例上尚未执行过的序号最小的GTID

ANONYMOUS

设置后执行事务不会产生GTID

显式指定的GTID

可以指定任意形式合法的GTID值，但不能是当前gtid_executed中的已经包含的GTID，否则下次执行事务会报错

binlog_gtid_simple_recovery(5.7.7之后)

开启(5.7默认，建议)

mysql-server只需打开最老的和最新的这2个binlog文件

gtid_purged和gtid_executed值根据这些文件中的Previous_gtids_log_event或者Gtid_log_event计算得出

关闭(5.6默认)

所有的binlog都要被检查。可能需要非常长的时间

session_track_gtids

OFF

关闭

OWN_GTID

返回当前事务产生的GTID

ALL_GTIDS

返回系统执行的所有GTID，也就是GTID_EXECUTED

gtid_owned

表示正在执行的事务的gtid以及对应的线程ID

gtid_executed_compression_period

表示控制每执行多少个事务，对此表（mysql.gtid_executed）进行压缩

默认1000个事务

5.7新特性

在线开启和关闭GTID

存储GTID信息到表中mysql.gtid_executed，slave无需开启binlog

GTID信息的记录

开启了binlog

在切换binlog时将当前binlog的所有GTID插入gtid_executed表中

未开启binlog

每个事务在提交之前会执行一个等价的INSERT的操作

此操作是该事务的一部分，和事务的其他操作整体保持原子性。需要保证gtid_executed是innodb存储引擎

主从延迟

大事务延迟

状态为:reading event from the relay log

一个事务主库执行多久，从库回放就需要多久

大表DDL延迟

状态为:altering table

长期未提交的事务延迟

会造成延迟的瞬时增加

没有主键或者唯一键

状态为:system lock 或者 reading event from the relay log

产生的原因：event回放时候使用了二级索引让回放速度慢且进行了大量的内存数据查找造成了CPU 100%而没有I/O的现象

解决方法：增加主键

innodb层锁造成延迟

状态为:system lock 或者 reading event from the relay log

从库参数设置不合理

从库大批量查询

双主模式

MMM

分支主题

主从MHA

架构图

子主题

组成

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参数

Orchestrator

架构图

子主题

MGR

架构图

子主题

使用场景

特别适合应用于对于数据一致性要求极高的金融级业务场景

组件

MGR集群

Mysql Shell

Mysql Router

原理

MGR之间的数据同步并没有采用复制技术，而是采用GCS（GroupCommunicationSystem）协议的日志同步技术。

GSC本身是一种类似Paxos算法的协议，要求组中的大部分节点都接收到日志，事务才能提交

Paxos算法，集群的节点要求数量是奇数个

模式

单主

单主模式只有1个节点可以写入

MGR可以自动进行Failover切换，不用依赖外部的各种高可用套件，所有的事情都由数据库自己完成

多主

多主模式能让每个节点都可以写入。

如果存在变更同一行的冲突，MySQL会自动回滚其中一个事务，自动保证数据在多个节点之间的完整性和一致性。

缺点与限制

仅支持InnoDB表，并且每张表一定要有一个主键；

目前一个MGR集群，最多只支持9个节点；

有一个节点网络出现抖动或不稳定，会影响集群的性能；

表分区

将整个表按每个分区分成多个ibd文件

对于引擎层来说，这是多个表；

对于 Server 层来说，这是 1 个表。

分库分表

垂直拆分

一个库中的表拆到多个库

一个表拆按字段和业务成多个表

水平拆分

分片

范围

hash取模

一致性hash

扩容方案

停机扩容

平滑扩容

数据库架构演变

单机单库

数据量太大，一台机器无法承受

读写操作量太大，超出一台机器承受

一旦数据库宕机，应用整体崩溃，可用性低

主从架构（读写分离）

数据量太大，超出一台服务器承受能力

写操作量大，超过master承受能力

分库分表

垂直拆分

水平拆分

sass云数据库

体系架构

连接层

连接池

安全连接

认证授权

Server层

连接器

缓存

分析器

优化器

执行器

存储引擎层

InnoDB

MyISm

memory

系统文件层

日志文件

error log

general log

binlog

slow query log

InnoDB日志

redo log

undo log

配置文件

my.cnf/my.ini

数据文件

InnoDB

物理及内存结构图

子主题

内存结构

Buffer Pool

free list

flush list

lru list

Change Buffer

Log Buffer

Adaptive Hash Index

原理

Buffer Pool在线扩容

数据库偶发抖动

大数据量查询导致Buffer Pool需要腾出空间，淘汰未使用的脏页需要刷入磁盘

redolog写满后触发的刷脏

Buffer Pool线程安全问题

Buffer Pool分为多个instance，共享资源加锁，多个实例可以降低锁的粒度

磁盘结构

系统表空间

数据字典

Doublewrite Buffer

Change Buffer

Undo Logs

独立表空间

文件结构

子主题

TableSpace

Segment

Extent

Page

Row

数据存储格式

文件格式

Antelope

Barracuda

行格式(Row_format)

四种行格式支持

通用表空间

undo 表空间

临时表空间

redo log

示意图

后台线程

IO Thread

Purge Thread

Page Cleaner Thread

Master Thread

日志

Undo Log

Redo Log

索引

索引类型

从索引存储结构划分：B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引

从应用层次划分：普通索引、唯一索引、主键索引、复合索引

从索引键值类型划分：主键索引、辅助索引（二级索引）

从数据存储和索引键值逻辑关系划分：聚集索引（聚簇索引）、非聚集索引（非聚簇索引）

Full-Text全文索引

全文索引是MyISAM的一种特殊索引类型，主要用于全文索引；InnoDB从MYSQL5.6版本提供对全文索引的支持。

它用于替代效率较低的LIKE模糊匹配操作，而且可以通过多字段组合的全文索引一次性全模糊匹配多个字段。

同样使用B-Tree存放索引数据，但使用的是特定的算法，将字段数据分割后再进行索引（一般每4个字节一次分割），索引文件存储的是分割前的索引字符串集合，与分割后的索引信息，对应Btree结构的节点存储的是分割后的词信息以及它在分割前的索引字符串集合中的位置。

Hash索引

主要就是通过Hash算法，将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。

检索算法：在检索查询时，就再次对待查关键字再次执行相同的Hash算法，得到Hash值，到对应Hash表对应位置取出数据即可，如果发生Hash碰撞，则需要在取值时进行筛选。

MySQL目前有Memory引擎和NDB引擎支持Hash索引。

自适应哈希索引

示意图

分支主题

Innodb存储引擎会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，建立哈希索引可以带来速度的提升

经常访问的二级索引数据会自动被生成到hash索引里面去(最近连续被访问三次的数据)，自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。

优点

无序，没有树高

降低对二级索引树的频繁访问资源

自适应

缺点

hash自适应索引会占用innodb buffer pool；

自适应hash索引只适合搜索等值的查询，如select * from table where index_col='xxx'

极端情况下，自适应hash索引才有比较大的意义，可以降低逻辑读

MySQL自动管理，人为无法干预

状态监控

show engine innodb status\G

Hash table size 34673, node heap has 0 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s

34673：字节为单位，占用内存空间总量

通过hash searches、non-hash searches计算自适应hash索引带来的收益以及付出，确定是否开启自适应hash索引

查看开启状态

innodb_adaptive_hash_index

B树索引原理

二分查找

Hash表

B-tree

索引值和data数据分布在整棵树结构中

每个节点可以存放多个索引值及对应的data数据

树节点中的多个索引值从左到右升序排列

B+tree(mysql索引使用的存储结构)

非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值

叶子节点包含了所有的索引值和data数据

叶子节点用指针连接，提高区间的访问性能

执行计划

type

NULL：表示不用访问表，速度最快。

const：表示使用主键或唯一索引做等值查询，常量查询。

eq_ref：多表join查询，表示前表每一个记录都只能匹配后面表的一行结果。

ref：表示使用非唯一索引进行单值查询。

range：表示使用索引范围查询。使用>、>=、<、<=、in等等。

index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。

ALL：表示全表扫描，性能最差。

key

rows

Extra

Using where

Using index

Using index Condition

Using filesort

Using temprorary

索引优化

优化原则

离散度高的键做索引

覆盖索引和索引下推，优化回表查询

Null查询可用到索引，但最好设置为not null,因为会涉及到字段额外存储空间

虚拟列：alter table student add first_name varchar(2) generated always as (left(name, 1))

代码先行，索引后上

联合索引尽量覆盖条件

不要在小基数字段上加索引

长字符串采用前缀索引

group和order会用不上

where与order 冲突优先where

全值(等值)匹配是最优选择；

最佳左前缀法则，如联合索引(a,b,c)，可利用的索引就有(a), (a,b), (a,b,c)；

不在索引列上做任何操作（计算、函数、类型转换），会导致索引失效而转向全表扫描；

存储引擎不能使用索引中范围条件右边的列；

尽量使用覆盖索引，减少select；

is null ,is not null 也无法使用索引；

like "xxx%" 是可以用到索引的，like以通配符开头('%abc'或'%abc%')索引失效会变成全表扫描的操作；

字符串不加单引号索引失效；

少用or，用它来连接时会索引失效；

<、<=、>、>=、BETWEEN、IN 可用到索引，<>、not in、!= 则不行，会导致全表扫描；

分页查询优化

limit ?,?两个值的增大都会导致查询变慢

如果可以找到偏移的那条数据，就可以达到优化目的

根据自增且连续的主键排序的分页查询

先用主键过滤

根据非主键字段排序的分页查询

使用索引排序

join关联优化

常用算法

嵌套循环连接算法NLJ

基于块的嵌套循环连接BNLJ

优化

关联字段加索引，让mysql做join操作是尽量选择NLJ算法

小标驱动大表，写多表连接sql时如果明确知道哪张表是小表可使用straight_join写法固定驱动表

in和exsits优化

select A in /exists(B)，如果A>B,则用in，反之用exists

count查询优化

将总数维护到redis里

增加数据库计数表

show table status

order by 关键字优化

MySQL支持两种方式的排序：Index和FileSort；index 效果高（扫描索引本身完成排序），FileSort效率较低。

order by 子句尽量使用 Index方式排序，避免使用FileSort方式排序；

order by 语句使用索引最左前列；

使用where子句与order by子句条件组合满足索引最左前列；

filesort方式排序有两种算法

双路排序：MySQL4.1之前是使用该方式，两次扫描磁盘，最终得到数据。

单路排序：从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，效率高于双路排序。

优化策略

增大 sort_buffer_size 参数的设置；

增大 max_length_for_sort_data 参数的设置；

group by 关键字优化

group by 实际是先排序后进行分组，遵照索引建的最佳左前缀；

当无法使用索引列，增大 max_length_for_sort_data和sort_buffer_size参数的设置；

where 高于having，能在where限定的条件就不要去having限定了；

sql查询流程

流程示意图

分支主题

1.客户端请求

2.连接器（验证用户身份，给予权限）

3.查询缓存（存在缓存则直接返回，不存在则执行后续操作）

4.分析器（对SQL进行词法分析和语法分析操作）

5.优化器（主要对执行的sql优化选择最优的执行方案方法）

6.执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口）

7.去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）

8.返回给客户端

事务

事务及其ACID属性

持久性

分支主题

原子性

隔离性

一致性

分支主题

并发事务带来的问题

脏读

不可重复读

幻读

前提条件：InnoDB引擎，可重复读隔离级别，使用当前读时

快照读，是不会看到别的事务插入的数据的

表现：一个事务(同一个read view)在前后两次查询同一范围的时候，后一次查询看到了前一次查询没有看到的行

事务A读取到了事务B提交的新增数据，不符合隔离性

幻读专指新插入的行，读到原本存在行的更新结果不算。因为当前读的作用就是能读到所有已经提交记录的最新值。

解决方法

将两行记录间的空隙加上锁，阻止新记录的插入；这个锁称为间隙锁。

间隙锁与间隙锁之间没有冲突关系。跟间隙锁存在冲突关系的，是往这个间隙中插入一个记录这个操作。

隔离级别

读未提交

读已提交

可重复读

可串行化

事务的演进

排队执行

排他锁

引入锁之后就可以支持并发处理事务，如果事务之间涉及到相同的数据项时，会使用排他锁，或叫互斥锁，先进入的事务独占数据项以后，其他事务被阻塞，等待前面的事务释放锁。

读写锁

读和写操作：读读、写写、读写、写读。

读写锁就是进一步细化锁的颗粒度，区分读操作和写操作，让读和读之间不加锁，让两个事务就可以同时被执行。

读写锁，可以让读和读并行，而读和写、写和读、写和写这几种之间还是要加排他锁。

MVCC

多版本控制MVCC，也就是Copy on Write的思想。

MVCC除了支持读和读并行，还支持读和写、写和读的并行，但为了保证一致性，写和写是无法并行的。

在事务开始写操作的时候会copy一个记录的副本，其他事务读操作会读取这个记录副本，因此不会影响其他事务对此记录的读取，实现写和读并行。

MVCC

作用

读操作类型

快照读（SnapshotRead）

当前读（CurrentRead）

MVCC原理

快照链

分支主题

ReadView

事务链表

m_ids

min_trx_id

检索的行对应的事务id小于该值，说明已提交的，可见

max_trx_id

大于该id，说明未提交，不可见

creator_trx_id

当前事物id

事务执行过程

流程图

1、从磁盘文件加载缓存数据

2、写入数据旧值到undo日志文件

3、更新内存数据

4、将变更写入redo日志缓冲区，处于prepare准备提交阶段

5、redo处于准备提交事务状态(期间redo日志不定期刷入磁盘）

6、执行器将该操作生成binlog日志并写入磁盘

7、提交事务，写入binlog文件与位置和commit标记到redo日志文件

8、IO线程根据刷盘策略将脏数据写入数据文件

redo与binlog的区别

redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的

redo是物理日志，记录某个数据页上做的修改,binlog是逻辑日志,记录语句的原始逻辑

redo是循环写的,空间固定会用完；binlog是追加写,到一定大小后会切换且不会覆盖

两阶段提交

redo log 的写入拆成了两个步骤：prepare 和 commit

目的：为了让两份日志(redo和binlog)之间的逻辑一致

写redo prepare成功，binlog写之前数据库崩溃

重启恢复后发现没有commit,事务回滚，binlog没有记录，备份恢复后与源库数据一致

写redo prepare成功，binlog成功，commit之前崩溃

重启恢复后虽然没有提交成功，但是满足prepare和binlog的完整，重启后会从binlog日志的Xid中获取提交的信息重新将该事务重做并commit。备份恢复后与源库数据一致

步骤

1、事务写入redo，处于prepare阶段

2、写入binlog日志

3、事务写入redo，提交事务

不采用两阶段提交存在的问题

先写redo在写binlog

redo记录了变更，之后发生宕机，binlog没有记录，实例恢复后仍然能够把数据恢复回来，但日后通过binlog做数据恢复时则没有对应数据变更

先写binlog再写redo

binlog记录了变更，之后发生宕机，redo没有记录，实例恢复后该变更无效，但日后通过binlog做数据恢复时则会恢复对应数据变更

相关参数

innodb_flush_log_at_trx_commit

在提交事务时，每秒触发一次缓存日志回写磁盘操作，并调用操作系统fsync刷新IO缓存

在每个事务提交时，InnoDB立即将缓存中的redo日志回写到日志文件，并调用操作系统fsync刷新IO缓存

在每个事务提交时，InnoDB立即将缓存中的redo日志回写到日志文件，但并不马上调用fsync来刷新IO缓存，而是每秒只做一次磁盘IO缓存刷新操作

sync_binlog

每秒刷盘，写入os系统缓存

每次提交刷盘，不经过系统缓存，直接写入磁盘

每n次提交刷盘

组提交

前提：设置了双1

innodb_flush_log_at_trx_commit=1

sync_binlog=1

示意图

分支主题

目的：将binlog的多个刷盘操作合并成一个，最大化每次刷盘的收益，弱化磁盘瓶颈，提高性能

三个阶段

Flush 阶段

将Redo log中prepare阶段的数据刷盘

将binlog数据写入文件，此时只是写入文件系统的缓冲

Flush阶段队列的作用是提供了Redo log的组提交

如果这一步完成后数据库崩溃，由于协调者binlog中不保证有该组事务的记录，所以MySQL可能会在重启后回滚该组事务

Sync 阶段

这里为了增加一组事务中的事务数量，提高刷盘收益

binlog刷盘

binlog_group_commit_sync_delay=N

在等待N μs后，开始事务刷盘

默认0为不开启，单位微秒

最大 1000000ms == 1s

binlog_group_commit_sync_no_delay_count=N

如果队列中的事务数达到N个，忽略时间的等待，直接开始刷盘

默认0为不开启，依赖上一参数

Sync阶段队列的作用是支持binlog的组提交

如果在这一步完成后数据库崩溃，由于协调者binlog中已经有了事务记录，MySQL会在重启后通过Flush 阶段中Redo log刷盘的数据继续进行事务的提交

Commit 阶段

获取队列中的事务组，依次将Redo log中已经prepare的事务在引擎层提交

Commit阶段不用刷盘

Flush阶段中的Redo log刷盘已经足够保证数据库崩溃时的数据安全

此处队列的作用是承接Sync阶段的事务，完成最后的引擎提交，使得Sync可以尽早的处理下一组事务，最大化组提交的效率

锁

按性能分

悲观锁

悲观锁的实现

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