MySQL的lsn与Oracle的scn和MySQL的GTID的区别

1. MySQL的LSN

1.1 Log Sequence Number(LSN)

LSN全称：Log Sequence Number ：日志序列号，该序列号是一个不断增大的数字，主要存在于数据页、buffer_pool、redolog_buffer、redologfile中.

在 MySQL 5.6.3 之前，LSN 是一个 4 字节的无符号整数。当重做日志文件大小限制从 4GB 增加到 512GB 时，LSN 在 MySQL 5.6.3 中变成了一个 8 字节的无符号整数，因为需要额外的字节来存储额外的大小信息。基于 MySQL 5.6.3 或更高版本构建的使用 LSN 值的应用程序应使用 64 位而不是 32 位变量来存储和比较 LSN 值。

简单理解就是一个数字标识，也可以理解为软件的版本号。当数据库有更新操作时，就会产生新的LSN。

mysql> show engine innodb status;

.....
LOG
---
Log sequence number          6712189836
Log buffer assigned up to    6712189836
Log buffer completed up to   6712189836
Log written up to            6712189836
Log flushed up to            6712189836
Added dirty pages up to      6712189836
Pages flushed up to          6712189836
Last checkpoint at           6712189836
11087 log i/o's done, 0.00 log i/o's/second
----------------------

.........

Log sequence number:表示当前redo log(in buffer)中的LSN

Log flushed up to : 表示刷到redo log file on disk中的LSN

pages flushed up to :表示已经刷新到磁盘数据页上的LSN

Last checkpoint at :上一次检查点所在的位置LSN

$\textcolor{Red}{LSN 是本地有效的，主从各有自己的LSN：}$

1.2 checkpoint

官方解释：当对缓存在缓冲池中的数据页进行更改时，这些更改会在稍后的某个时间写入数据文件 （写入磁盘），这个过程称为刷新。检查点是已成功写入数据文件的最新更改（由 LSN值表示)的记录。

checkpoint的作用：

Mysql在进行增删改除的时候，是通过将数据页从磁盘上加载到buffer_pool中(内存)，当Mysql对数据页进行了DML操作后，为了提高性能，减少磁盘I/O的次数，这时会设置一个刷脏页的策略：例如master thread checkpoint
为了防止内存中修改的脏页消失，Mysql引入了一个redolog日志，redolog日志最大的作用是1是保证binlog能安全落盘，2是能减少mysql数据库故障恢复时间
说回正题，关于checkpoint的总结是：按照一定的条件将内存中的脏页刷到磁盘上。

当数据存在innodb buffer pool中，Mysql做update操作时，不是直接修改硬盘的页，而是对buffer pool中对应的页进行修改的。因为在内存修改更快，这时候可能就会产生脏页，因为可能内存中的页和磁盘中的页数据不一致。

page_cleaner_thread线程就会把上面的数据不一致进行刷新同步，刷新一次就会记录一个检查点(checkpoint),可以理解检查点checkpoint是动态更新的，checkpoint变量存储的变量就是当前的LSN。

page_cleaner_thread同步刷新一次，checkpoint变量就记录当前的LSN。

$\textcolor{Blue}{}$ checkpoint分两种：完全检查点和模糊检查点

sharp checkpoint：完全检查点，数据库正常关闭时，会触发把所有的脏页都写入到磁盘上(这时候logfile的日志就没用了，脏页已经写到磁盘上了)。

InnoDB Fuzzy Checkpoint 模糊检查点的发生，模糊检查点可以理解为区域检查点。

数据库在运行时不会使用sharp checkpoint，在引擎内部使用fuzzy checkpoint，即只刷新一部分脏页，而不是刷新所有的脏页回磁盘。

下面就是模糊检查点的4中发生情况

① Master Thread Checkpint

master线程定时去checkpint

差不多以每秒或每十秒的速度从缓冲池的脏页列表中刷新一定比例的页回磁盘，这个过程是异步的，不会阻塞用户查询。

mysql5.6以后单独放到了page_cleaner_thread线程中了。

② Flush_LRU_LIST checkpoint

当innodb pool中的free list空闲的页没有了，当有新的页需要缓存时，需要把LRU列表尾端的页移除，当这些页是脏页的时候，就需要checkpoint.

可以通过参数innodb_lru_scan_depth控制LRU列表中可用页的数量,也就是一次性刷新页的数量，该值默认为1024。

③ Async/Sync Flush checkpoint 重做日志重用

指的是重做日志文件不可用的情况，这时需要强制将一些页刷新回磁盘，而此时脏页是从脏页列表中选取的。若将已经写入到重做日志的LSN记为redo_lsn，将已经刷新回磁盘最新页的LSN记为checkpoint_lsn，则可定义：

checkpoint_age = redo_lsn - checkpoint_lsn

再定义以下的变量：

async_water_mark = 75% * total_redo_log_file_size

sync_water_mark = 90% * total_redo_log_file_size

④ Diry Page too much Checkpoint

即脏页的数量太多，导致InnoDB存储引擎强制进行Checkpoint。其目的总的来说还是为了保证缓冲池中有足够可用的页。其可由参数innodb_max_dirty_pages_pct控制：这里的75指的是75%，当脏页数的比例超过75%就会刷新。

Innodb_buffer_pool_pages_data：数据页的大小，有多少个页

Innodb_buffer_pool_pages_dirty：脏页的数量，单位是页

Innodb_buffer_pool_pages_total：总的页数

Innodb_buffer_pool_pages_dirty / Innodb_buffer_pool_pages_total：表示脏页在buffer 的占比

MySQL与Oracle的checkpoint触发机制

注意：Oracle的CKPT进程不进行checkpoint操作，它只是负责：

使用检查点信息更新数据文件头；
使用检查点信息更新控制文件；
在完全检查点时向DBWn发出信号。

MySQL	Oracle
master线程定时去checkpint	每次切换redo日志文件（日志达到一定大小，自动切换）
Flush_LRU_LIST checkpoint	当符合LOG_CHECKPOINT_TIMEOUT，LOG_CHECKPOINT_INTERVAL，fast_start_io_target,fast_start_mttr_target参数设置的时候
重做日志重用（跟Oracle切日志有点像）	执行alter system switch logfile（手动日志切换）
Diry Page too much Checkpoint	执行 alter system checkpoint
	执行alter tablespace … begin/end backup
	alter tablespace … offline
	shutdown immediate（正常关闭数据库）

1.3 LSN是怎样产生的

我们来说说LSN具体是怎么产生的？
　　1.首先修改内存中数据页时，在修改后的数据页中记录一个LSN号,暂时称为：data_in_buffer_lsn
　　2.在修改内存中数据页的同时，redo_log_buffer(也是一块内存区域，在buffer_pool中)中同时也会记录修改的物理变化，发生变化会产生redo log，这时会记录redo log 的一个LSN,注意这个LSN是在内存中，暂时称为：redo_log_in_buffer_lsn
　　3.当日志写了部分后会触发一个日志落盘策略，该策略是由参数:innodb_flush_log_at_trx_commit控制，该参数在前面的内容有介绍，当将redo_log_buffer中redo log日志刷到磁盘上后，此时在redo log日志也会记录一个LSN(该LSN此时在磁盘上)，这个操作也是我们经常说的WAL机制，就是日志优先写机制，该机制是用来防止mysql在未经进行刷脏页时出现宕机，可以用redolog file 来进行恢复。暂时称为：redo_log_on_disk_lsn；
　　4.我们在内存中修改的数据页不可能一直在内存中，这时我们的猪脚出现(checkpoint机制)，它就表示在一定的条件下将脏页(数据脏页和日志脏页)刷到磁盘上，当脏页刷到磁盘后，所以会在本次checkpoint刷页结束后，在redo log中记录checkpoint的LSN位置，暂且称之为checkpoint_lsn。
　　5.要记录checkpoint所在位置很快，只需要设置一个标志即可，但是刷数据页并不一定很快，比如一次性刷的数据页非常多，也就是说要刷入的数据页需要一定的时间来完成，中途刷入的每个数据页都会记录当前页所在的LSN,暂时称为data_page_on_disk_lsn

2. Oracle的scn

SCN即系统改变号(System Change Number)，是在某个时间点定义数据库已提交版本的时间戳标记。 Oracle为每个已提交的事务分配一个唯一的SCN。 SCN的值是对数据库进行更改的逻辑时间点。 Oracle使用此编号记录对数据库所做的更改。在数据库中，SCN也可以说是无处不在，数据文件头，控制文件，数据块头，日志文件等等都标记着SCN。也正是这样，数据库的一致性维护和SCN密切相关。不管是数据的备份，恢复都是离不开SCN的。

常见的十分重要的SCN

控制文件中的有三种SCN：系统SCN、数据文件SCN、数据文件结束SCN。
数据文件中的SCN：数据文件头部的SCN，叫start SCN，也叫启动SCN。
redo logfile中的SCN：每一条日志记录有一个SCN，每一个日志文件有一个first scn和一个next scn.
数据块中的SCN：数据块头部ITL事务槽中有SCN(在跑日志时，会对ITL中的SCN和日志文件中的SCN进行比较，如果ITL中的SCN大一些，那么会进行空跑日志，如果ITL中的SCN小一些，则实际修改数据块.
回滚段事务表中也有SCN。

我们知道日志文件分为CURRENT, ACTIVE, INACTIVE三者，CURRENT表示当前正在使用的日志文件，ACTIVE表示日志文件中对应的脏块还没有写到磁盘中，而INACTIVE则表示日志文件中对应的所有脏块都写到了磁盘中。

系统SCN、文件SCN、数据文件头部SCN三者的值等于ACTIVE, CURRENT中最老的一个日志文件的头部的first SCN. 这三个值只有在日志文件由ACTIVE变为INACTIVE时，才会更新。因为INACTIVE日志对应的脏块都写入到了磁盘中，不需要恢复。而增量checkpoint操作只是将checkpoint-Q中的最老的脏块对应的日志的地址即LRBA写到控制文件中，它并不会修改SCN.所以在恢复时，可以根据系统SCN与日志文件头部的first SCN比较就可以找到要使用那个日志文件，然后再向新的日志方向寻找LRBA地址对应的具体的那一条日志记录。然后从LRBA到On disk RBA进行前滚，前滚之后在利用undo进行回滚。

日志记录中的SCN与commit的关系：

当我们执行 commit时，LGWR会将log buffer写入redo logfile，同时也会将对应的SCN同步写入到redo logfile内(wait-until-completed)。因此当你commit transaction时，在成功返回之前，LGWR必须先完整的完成上述行为之后，否则你是看不到提交成功的返回的。

可以查询目前系统最新的SCN

  SQL> select dbms_flashback.get_system_change_number scn from dual;

3. MySQL的gtid

GTID 分成两部分，一部分是服务的UUID，另外一部分就是事务ID了，随着事务的增加，值依次递增。也就是说，GTID实际上是由UUID+TID组成的。其中UUID是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量。

GTID 格式 UUID:TID ，通过调用 get_server_sidno()和Gtid_state::get_automatic_gno()生成。

mysql [root@localhost:((none)) 14:19:04]>show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000191
         Position: 581899732
     Binlog_Do_DB:
 Binlog_Ignore_DB: mysql,information_schema,performance_schema
Executed_Gtid_Set: 49be11b8-4437-11eb-828f-52540a015b8d:1-7464792,
c9f00ce8-442c-11eb-89e4-52540a015b8f:1-51201043,
d024497f-966f-11e7-ad27-525400229d96:1-11757330,
d4c1d2f9-442c-11eb-aca8-52540a015b8e:1-75526689
1 row in set (0.00 sec)

mysql [root@localhost:((none)) 14:19:04]>show slave status\G
....
           Retrieved_Gtid_Set: c9f00ce8-442c-11eb-89e4-52540a015b8f:49750407-51209767
            Executed_Gtid_Set: 49be11b8-4437-11eb-828f-52540a015b8d:1-7464792,
c9f00ce8-442c-11eb-89e4-52540a015b8f:1-51209767,
d024497f-966f-11e7-ad27-525400229d96:1-11757330,
d4c1d2f9-442c-11eb-aca8-52540a015b8e:1-75526689
                Auto_Position: 1
....

Retrieved_Gtid_Set : 从库已经接收到主库的事务编号

Executed_Gtid_Set : 从库自身已经执行的事务编号

GTID工作原理

master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。
slave端的i/o 线程将变更的binlog，写入到本地的relay log中。
sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。
如果有记录，说明该GTID的事务已经执行，slave会忽略。
如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。
在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。

GTID的优缺点

优点：

一个事务对应一个唯一GTID，一个GTID在一个服务器上只会执行一次。
GTID是用来代替传统复制的方法，GTID复制与普通复制模式的最大不同就是不需要指定二进制文件名和位置。
减少手工干预和降低服务故障时间，当主机挂了之后通过软件从众多的备机中提升一台备机为主机。

缺点：

不支持非事务引擎。
不支持create table ... select 语句复制(主库直接报错)。原理：（会生成两个sql，一个是DDL创建表SQL，一个是insert into插入数据的sql。

MySQL5.7和MySQL8.0都不支持，这两点是一致的：
由于DDL会导致自动提交，所以这个sql至少需要两个GTID，但是GTID模式下，只能给这个sql生成一个GTID ）。
不允许一个SQL同时更新一个事务引擎表和非事务引擎表。
开启GTID需要重启（5.7除外）。
对于create temporary table 和 drop temporary table语句不支持。
不支持sql_slave_skip_counter。

目录CONTENT

MySQL的lsn与Oracle的scn和MySQL的GTID的区别

MySQL的lsn与Oracle的scn和MySQL的GTID的区别

1. MySQL的LSN

1.1 Log Sequence Number(LSN)

1.2 checkpoint

1.3 LSN是怎样产生的

2. Oracle的scn

3. MySQL的gtid

GTID工作原理

GTID的优缺点

参考文章：

评论区