第20章：MySQL之事务和事务相关的功能

第20章：MySQL之事务和事务相关的功能

===================

目录

修订日期：2021-08-13

一. 事务

1.1. 事务相关的功能

  • transaction
  • redo
  • undo
  • purge
  • group commit
  • XA
  • transaction programming

1.2 开启/结束一个事务

  • 方法一

    • begin;
    • SQL…;
    • commit / rollback;

  • 方法二

    • start transaction;
    • SQL…;
    • commit / rollback;
START TRANSACTION
    [transaction_characteristic [, transaction_characteristic] ...]

transaction_characteristic:
    WITH CONSISTENT SNAPSHOT
  | READ WRITE
  | READ ONLY

BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET autocommit = {0 | 1}
  • 案例1:
--begin 开始的事务
mysql root@localhost:(none)> use mytest;
You are now connected to database "mytest" as user "root"
Time: 0.002s

mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s

mysql root@localhost:mytest> insert into t_ai_1 values (NULL, 20);
Query OK, 1 row affected
Time: 0.001s

mysql root@localhost:mytest> rollback;
Query OK, 0 rows affected
Time: 0.001s
  • 案例2:
-- start transaction开启事务
mysql root@localhost:mytest> start transaction;
Query OK, 0 rows affected
Time: 0.001s

mysql root@localhost:mytest> insert into t_ai_1 values (NULL, 20);
Query OK, 1 row affected
Time: 0.001s

mysql root@localhost:mytest> rollback;
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest>

用到 start transaction 的原因是因为 begin 在 存储过程 中是一个关键字，表示代码块开始的

1.3. 事务的ACID

  • A - Atomicity（原子性）

    • 一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。
    • undo技术来保证。
    • 相关的MySQL功能包括：
      • 自动提交设置
      • COMMIT语句
      • ROLLBACK语句
      • INFORMATION_SCHEMA 表的操作数据

  • C - Consistency（一致性）

    • 数据库总是从一个一致性的状态转换到另一个一致性的状态，事务的开启和结束，没有破坏数据的结构和约束。
    • redo技术来保证。
    • 相关的MySQL功能包括：
      • InnoDB双写缓冲
      • InnoDB崩溃恢复

  • I - Isolation （隔离性）

    • 通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的
    • lock技术来保证。
    • 相关的MySQL功能包括：
      • 自动提交设置
      • 声明SET ISOLATION LEVEL
      • InnoDB锁的底层细节。在性能调整期间，您可以通过INFORMATION_SCHEMA表查看这些细节。

  • D - Durable（持久性）

    • 在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。
    • redo & undo技术来保证。
    • 相关的MySQL功能包括：
      • innodb_doublewrite
      • innodb_flush_log_at_trx_commit
      • sync_binlog
      • innodb_file_per_table
      • 在存储设备（如磁盘驱动器，SSD或RAID阵列）中写入缓冲区。
      • 操作系统是否支持fsync()系统调用
      • 备份策略
      • 分布式或托管式数据应用程序，MySQL服务器硬件所在的数据中心的特定特性以及数据中心之间的网络连接。
      • 存储设备中的电池备份缓存。
      • 不间断电源（UPS），保护运行MySQL服务器和存储MySQL数据的所有计算机服务器和存储设备的电源。

1.4. 事务的类型

  • 扁平事务(Flat Transactions)

    • 扁平事务中，所有操作都处于同一层次，其由BEGIN WORK开始，由COMMIT WORK或ROLLBACK WORK结束，其间的操作是原子的，要么都执行，要么都回滚，因此扁平事务的应用程序称为原子操作的的基本组成模块
    • 其中commit work 约占96%，ROLLBACK WORK约占3%，1%强制结束

  • 带有保存点的扁平事务(Flat Transactions with Savepoints)

    • 除了扁平事务支持的操作外，允许在事务执行过程中回滚到同一事务中较早的一个状态
    • 在MySQL内 非常有用

  • 链事务(Chained Transactions)

    • 在提交一个事务的时候，释放不需要的数据对象，将必要的处理上下文隐式的传给下一个要开始的事务
    • 提交事务操作和开始下一个事务操作将合并成为一个原子操作。这意味着下一个事务将看到上一个事务的结果，就好像在一个事务中执行的一样
    • 相当于在一个事务commit之后，立即打了一个begin（系统自动）

  • 嵌套事务(Nested Transactions)

    • InnoDB不支持
    • BDB支持

  • 分布式事务(Distrihuted Transactions)

    • 在分布式环境下运行的扁平事务
    • 参与分布式的节点都要支持ACID
    • 举例：持卡人从招行银行的储蓄卡转账10000元到工商银行的储蓄卡

1.5. 事务演示

1.5.1 扁平事务(Flat Transactions)

mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> insert into t_ai_1 values (NULL, 20);
Query OK, 1 row affected
Time: 0.001s
mysql root@localhost:mytest> rollback;
Query OK, 0 rows affected
Time: 0.001s

mysql root@localhost:mytest> start transaction;
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> insert into t_ai_1 values (NULL, 20);
Query OK, 1 row affected
Time: 0.001s
mysql root@localhost:mytest> commit;

1.5.2 带有保存点的扁平事务(Flat Transactions with Savepoints)

mysql root@localhost:mytest> create table h (a int(11) NOT NULL,primary key(a));
Query OK, 0 rows affected
Time: 0.007s
mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> insert into h values (88);
Query OK, 1 row affected
Time: 0.001s
mysql root@localhost:mytest> savepoint h1; --保存点h1
Query OK, 0 rows affected
Time: 0.007s
mysql root@localhost:mytest> insert into h values (99);
Query OK, 1 row affected
Time: 0.001s
mysql root@localhost:mytest> savepoint h2; --保存点h2
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> select * from h;
+----+
| a  |
+----+
| 88 |
| 99 |
+----+
2 rows in set
Time: 0.009s

mysql root@localhost:mytest> rollback to savepoint h1;  --恢复到上一个保存点h2，且该事务没有rollback或者commit
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> select * from h;
+----+
| a  |
+----+
| 88 |
+----+
1 row in set
Time: 0.010s
mysql root@localhost:mytest>

--  如果执行rollback，则整个事务rollback
--  如果此时执行commit，则h1之前的SQL会被提交，因为h2的SQL已经被rollback掉了

带保存点的平事务在写存储过程的时候，做逻辑判断时，可能会用到; 当系统发生崩溃时,所有保存点将丢失;恢复时，需要从开始处进行恢复。

二. REDO

2.1. REDO的组成

  • REDO的组成

    • redo log block
      • redo log 都是reod log block组成,每块的大小为 512 字节。
    • redo log buffer
      • --innodb_log_buffer参数
      • 通常8M已经足够使用
    • redo log file
      • --innodb_log_file_size 单个redo文件大小（推荐4~8G，官方建议等于buffer_pool_size大小）
      • --innodb_log_files_in_group 表示日志组中的日志文件的数量。默认值和推荐值是2
      • --innodb_log_group_home_dir redo文件的保存目录，建议redo文件和数据文件分开保存，选择更快的磁盘。

mysql root@localhost:mytest>  show variables like "%innodb_log%";
+-----------------------------+------------+
| Variable_name               | Value      |
+-----------------------------+------------+
| innodb_log_buffer_size      | 16777216   |  --配置innodb_log_buffer大小为16M
| innodb_log_checksums        | ON         |
| innodb_log_compressed_pages | ON         |
| innodb_log_file_size        | 2147483648 |  --redo log文件大小为2G
| innodb_log_files_in_group   | 2          |  --默认配置为2组redo log 
| innodb_log_group_home_dir   | ./         |  --redo文件存放的位置。默认和数据文件放一起。线上建议分开放
| innodb_log_write_ahead_size | 8192       |
+-----------------------------+------------+
7 rows in set
Time: 0.013s
mysql root@localhost:mytest>

[root@localhost-m(252) /r2/mysqldata]# ls -lh |grep ib_log
-rw-r----- 1 mysql mysql 2.0G 2月   1 15:28 ib_logfile0  --两个 redo logfile 文件
-rw-r----- 1 mysql mysql 2.0G 2月   1 15:28 ib_logfile1
[root@localhost-m(252) /r2/mysqldata]#

2.2. REDO Log Buffer的刷新条件

2.2.1 刷新条件

  1. master thread 每秒刷新redo的buffer到logfile

    • mysql 5.6版本后，增加 innodb_flush_log_at_timeout 参数，可以设置刷新间隔，默认为 1秒

  2. redo log buffer 使用量大于1/2时进行刷新

  3. 事务提交时进行刷新

    • --innnodb_flush_log_at_trx_commit =

2.2.2 redo日志写入过程

  • 当ib_logfile0写满时，去写ib_logfile1,redo日志组进行轮询插入
    • 优点：
      • 这样做的好处是不需要归档减少了IO操作 ；
    • 缺点：
      • 如果redo_log_file太小，则可能需要等待。因为当要覆盖log_file中的log_block 时，如果该 log_block 中的脏页还没有进行刷新的话，则需要等待这个脏页进行刷新，所以需要把 redo log file 设置的尽可能的大

2.2.3 如何判断 innodb_log_file_size 设置小了？

mysql root@localhost:(none)> show engine innodb status;
+--------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Type   | Name | Status                                                                                                                                                 |
+--------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB |      |                                                                                                                                                        |
|        |      | =====================================                                                                                                                  |
|        |      | 2018-02-02 09:57:03 0x7f254c6b3700 INNODB MONITOR OUTPUT                                                                                               |

----------省略部分输出-----------
|        |      | ---                                                                                                                                                    |
|        |      | LOG                                                                                                                                                    |
|        |      | ---                                                                                                                                                    |
|        |      | Log sequence number 33272794440                                                                                                                        | --当前内存中的LSN
|        |      | Log flushed up to   33272794440                                                                                                                        |
|        |      | Pages flushed up to 33272794440                                                                                                                        | --最后刷新到磁盘的页上的，最新的LSN
|        |      | Last checkpoint at  33272794431                                                                                                                        | 
|        |      | 0 pending log flushes, 0 pending chkp writes                                                                                                           |
|        |      | 5086 log i/o's done, 0.00 log i/o's/second                                                                                                             |
|        |      | ----------------------                                                                                                                                 |

  • Pages flushed和Log sequence number两者之差表示 redo log 还有多少没有刷新的磁盘

  • 如果该差值 接近 重做日志的总大小的75%时，表明你的innodb_log_file_size设置小了(75%左右就强制刷新了)

  • innnodb_flush_log_at_trx_commit

    • 0:每次事务提交时不将redo log buffer的数据写入redo log file，而是 log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行.
      • 该模式下,innodb会每秒一次或者redo log buffer的数据大于50%时执行fsync操作。
    • 1:每次事务提交时将redo log buffer的数据写入redo log file，并且进行fsync操作(刷到磁盘).
      • 该模式下,innodb会实时执行fsync操作。
    • 2:每次事务提交时将redo log buffer的数据写入redo log file，仅写入file system的cache中，但是fsync操作(刷到磁盘)并不会同时进行.
      • 该模式下,innodb会每秒执行一次fsync操作。

0 可能会丢失1秒的数据，1 可以保证数据不丢失，2 如果是mysql停止，不会丢数据，因为在缓存里面，但是当系统宕机了，在缓存里面的数据就丢失了。建议设置为1

  • innodb_flush_log_at_timeout
    • 该值设置的越大，相对性能就好一些（io操作变少），但是万一发生宕机，丢失的数据也就越多。

2.3. 组提交

2.3.1 组提交

  1. 一次fsync刷新多个事务

    • 性能提高10~100+倍
    • 通过sysbench工具，压试sysbench update_non_index.lua脚本（5.5有bug，性能较差）

  2. InnoDB存储引擎原生支持

2.3.2 fsync

  • fsync
    • O_DIRECT 仅对写数据时有用,redo log 是不通过 O_DIRECT 方式写入到磁盘的，而是写到文件系统的缓存中
    • O_DIRECT 仅写数据到磁盘，但是数据的 元数据 没有同步，比如time、owner、size等等
      • 从数据的角度看，fsync可以将元数据同步到磁盘
    • fsync 可以将 redo日志 （redo log buffer）从文件系统的缓存同步到磁盘
graph LR
A(IOPS)-->B(fsync)
B-->C(TPS)

假设HDD磁盘IOPS为100（即每秒只有100次fsync），且一个事物中只有一条 insert into 的SQL时，那1秒钟内就只能插入100条数据，即TPS就只有100

假设使用 组提交 ，假设一次 fsync 可以刷新10个事物，那在IOPS为100的情况下，也可以提交500个事物。这样性能就得到了提升（一次IO提交多个事物）

2.4 REDO日志的分类

  • Redo日志的分类
    • 物理日志：记录整个页的变化（diff）
    • 逻辑日志：Like SQL语句
    • 物理逻辑日志：根据页进行记录，内容逻辑
+---------------+----------+---------+---------------+
| redo_log_type | space no | page no | redo log body |
+---------------+----------+---------+---------------+
-- redo log 类型  表空间号	 页号	  redolog的内容

+------+--------+------+---------+------------+-------+---------+-----------+----------+
| type | space  | page | cur_rec | len &	  |  info | origin  | mis_match | rec body |
|	   |   no   |  no  | _offset | extra_info | _bits | _offset | _index	|	       |
+------+--------+------+---------+------------+-------+---------+-----------+----------+
--LOG_REC_INSERT插入日志的过程，不记录sql语句，记录页的变化

+------+----------+---------+--------+
| type | space no | page no | offset |
+------+----------+---------+--------+
--LOG_REC_DELETE 删除日志的过程，rec body 中记录的是 页的变化 ，并非SQL语句

2.5 redo日志优化

  • redo日志优化参考下面指导原则：
    • 日志文件的组合大小（innodb_log_file_size * innodb_log_files_in_group）不能超过最大值512GB。
    • 使您的重做日志文件大，甚至与缓冲池(buffer_pool_size)一样大 。
      • 当InnoDB写完重做日志文件时，必须将修改后的缓冲池的内容写入磁盘的 检查点。小的重做日志文件导致许多不必要的磁盘写入。虽然从历史上看，大的重做日志文件会导致冗长的恢复时间，但现在恢复速度更快，您可以放心使用大型重做日志文件。
      • 重做日志文件的大小和数量使用innodb_log_file_size 和 innodb_log_files_in_group 配置选项进行配置。
    • 考虑增加日志缓冲区（redo log buffer）的大小 。
      • 一个大的日志缓冲区允许大事务运行，而无需在事务提交之前将日志写入磁盘。因此，如果您有更新，插入或删除多行的事务，则使日志缓冲区更大可节省磁盘I/O。
      • 日志缓冲区大小使用innodb_log_buffer_size 配置选项进行配置。

三. binary log(binlog日志)

binlog记录了对MySQL数据库执行更改的所有操作，但是不包括SELECT SHOW 这类操作，因为这类操作对数据本身并没有修改。然而，若操作
本身并没有导致数据库发生变化，那么该操作可能也会写入二进制日志。

  • 通过在my.cnf中配置参数log-bin [=name] 来启动二进制日志
    • log_bin = /r2/mysqldata/binlog
  • binlog文件以自己定义的name [binlog]开头的，在datadir中以 binlog.000001,binlog.000001 ....显示
  • binlog.index 为二进制索引文件

为什么在MySQL中要搞两份日志？

  • 1.binlog是在MySQL层产生
  • 2.redolog在innodb层产生

如果MySQL只有一个INNODB引擎，其实只有redo日志是可以的（类似Oracle），但是MySQL还有其他存储引擎，如果不使用binlog，意味着每个存储引擎都要实现一次binlog所提供的功能，诸如复制恢复等等。

一次事务提交，既要写binlog，又要写redolog

3.1. 查看当前binlog状态

mysql root@localhost:(none)> show master status \G;
***************************[ 1. row ]***************************
File              | binlog.000022                      -- File：当前binlog写入的文件
Position          | 709909091                          -- Position：写入的偏移量，如果有新的数据插入，这个值就会改变（字节数）
Binlog_Do_DB      |
Binlog_Ignore_DB  |
Executed_Gtid_Set | 3eae5854-d89f-11e7-ab20-246e960a8d84:1-9874,
746122da-663e-11e7-9de1-b8ca3a6567c4:1-19113,
a57cd625-663e-11e7-9ba9-b8ca3a64d66c:18606-190213162:190249486-190252207:190327015-207860771:207860811-244861160:244862559-348804170
1 row in set
Time: 0.010s

mysql root@localhost:(none)> show variables like "%max_binlog_size%";
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| max_binlog_size | 1073741824 |      --binlog文件的大小，配置为是1G
+-----------------+------------+
1 row in set
Time: 0.013s

binlog随着事务执行变化不断的增大，当超过配置的最大值时（1G）,最后一个事务要执行完，可能大于1G就会切割binlog（一个事务不能跨越两个binlog）

  • 通过查看 binlog_cache_use 、binlog_cache_disk_use 的状态
mysql root@localhost:mytest> show variables like 'binlog_cache_size';
+-------------------+---------+
| Variable_name     | Value   |
+-------------------+---------+
| binlog_cache_size | 4194304 |
+-------------------+---------+
1 row in set
Time: 0.011s

mysql root@localhost:mytest> show global status like 'binlog_cache%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 0     | --记录了使用临时文件写二进制日志的次数
| Binlog_cache_use      | 0     | --记录了使用缓冲写二进制日志的次数
+-----------------------+-------+
2 rows in set
Time: 0.011s
mysql root@localhost:mytest>

3.2. binlog的类型

  • 1.statement

    • 记录SQL语句（那如果有一些不确定的SQL语句，类似UUID()的函数、limit without order by，主从会出现不一致）

  • 2.row

    • 设置RC隔离级别，必须选这个（5.7.7默认）
    • 记录SQL语句操作的哪些行（行的变化）
    • 一张表一定要有主键（性能较高）
    • 数据一致性高，可flashback

  • 3.mixed

    • 结合statement和row栺式（不推荐），默认使用STATEMENT 格式，下面情况下会使用 ROW 格式：
        1. 表的存储引擎为 NDB ，这时对表的 DML 操作都会以 ROW 格式记录。
        1. 使用了 UUIDO USERO CURRENT_ USERO FOUND_ROWSO ROW_COUNTO等不确定函数。
        1. 使用了 INSERT DELAY 语句。
        1. 使用了用户定义函数 (UDF)
        1. 使用了临时表(temporary table)
mysql root@localhost:mytest> show variables like "binlog_format";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set
Time: 0.013s
mysql root@localhost:mytest>

3.3 binlog events

查看当前binlog中的内容

mysql root@localhost:mytest> show binary logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000021 | 309285    |
| binlog.000022 | 709909114 |
| binlog.000023 | 194       |
+---------------+-----------+
3 rows in set
Time: 0.010s
mysql root@localhost:mytest> show binlog events in 'binlog.000023';
+---------------+-----+----------------+-----------+-------------+---------------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                        | 
+---------------+-----+----------------+-----------+-------------+---------------------------------------------+
| binlog.000023 | 4   | Format_desc    | 1         | 123         | Server ver: 5.7.18-log, Binlog ver: 4       | --mysql版本和日志版本
| binlog.000023 | 123 | Previous_gtids | 1         | 194         | 3eae5854-d89f-11e7-ab20-246e960a8d84:1-9874 | --GTID号
+---------------+-----+----------------+-----------+-------------+---------------------------------------------+

mysql root@localhost:mytest> flush binary logs;  -- or flush logs 刷新binlog，切换产生新的binlog
Query OK, 0 rows affected
Time: 0.007s

mysql root@localhost:mytest> show binary logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000021 | 309285    |
| binlog.000022 | 709909114 |
| binlog.000023 | 238       |
| binlog.000024 | 194       |   --新生成日志文件
+---------------+-----------+
4 rows in set
Time: 0.010s

mysql root@localhost:mytest> show binlog events in 'binlog.000024';
+---------------+-----+----------------+-----------+-------------+---------------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                        |
+---------------+-----+----------------+-----------+-------------+---------------------------------------------+
| binlog.000024 | 4   | Format_desc    | 1         | 123         | Server ver: 5.7.18-log, Binlog ver: 4       |
| binlog.000024 | 123 | Previous_gtids | 1         | 194         | 3eae5854-d89f-11e7-ab20-246e960a8d84:1-9874 |
+---------------+-----+----------------+-----------+-------------+---------------------------------------------+
2 rows in set
Time: 0.011s
mysql root@localhost:mytest>

3.4. binlog 演示

  • 两种binlog格式的区别
mysql root@localhost:mytest> show master status \G;
***************************[ 1. row ]***************************
File              | binlog.000024
Position          | 194
Binlog_Do_DB      |
Binlog_Ignore_DB  |
Executed_Gtid_Set | 3eae5854-d89f-11e7-ab20-246e960a8d84:1-9874,
746122da-663e-11e7-9de1-b8ca3a6567c4:1-19113,
a57cd625-663e-11e7-9ba9-b8ca3a64d66c:18606-190213162:190249486-190252207:190327015-207860771:207860811-244861160:244862559-348804170
1 row in set
Time: 0.010s

mysql root@localhost:mytest> show binlog events in 'binlog.000024';
+---------------+-----+----------------+-----------+-------------+---------------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                        |
+---------------+-----+----------------+-----------+-------------+---------------------------------------------+
| binlog.000024 | 4   | Format_desc    | 1         | 123         | Server ver: 5.7.18-log, Binlog ver: 4       |
| binlog.000024 | 123 | Previous_gtids | 1         | 194         | 3eae5854-d89f-11e7-ab20-246e960a8d84:1-9874 |
+---------------+-----+----------------+-----------+-------------+---------------------------------------------+
2 rows in set
Time: 0.011s

mysql root@localhost:mytest> select * from f;
+----+
| a  |
+----+
| 1  |
| 3  |
| 5  |
| 7  |
| 10 |
+----+
5 rows in set
Time: 0.010s

mysql root@localhost:mytest> insert into f values (99);
Query OK, 1 row affected
Time: 0.003s
                  
mysql root@localhost:mytest> show binlog events in 'binlog.000024';
+---------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                                                 |
+---------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------+
| binlog.000024 | 4   | Format_desc    | 1         | 123         | Server ver: 5.7.18-log, Binlog ver: 4                                |
| binlog.000024 | 123 | Previous_gtids | 1         | 194         | 3eae5854-d89f-11e7-ab20-246e960a8d84:1-9874                          |
| binlog.000024 | 194 | Gtid           | 1         | 259         | SET @@SESSION.GTID_NEXT= '3eae5854-d89f-11e7-ab20-246e960a8d84:9875' |
| binlog.000024 | 259 | Query          | 1         | 333         | BEGIN                                                                | --begin开启事务
| binlog.000024 | 333 | Table_map      | 1         | 379         | table_id: 2453 (mytest.f)                                            | --写入目标表mytest.f
| binlog.000024 | 379 | Write_rows     | 1         | 419         | table_id: 2453 flags: STMT_END_F                                     | --写入Write_rows
| binlog.000024 | 419 | Xid            | 1         | 450         | COMMIT /* xid=4590 */                                                | --commit提交事务
+---------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------+
7 rows in set
Time: 0.012s

--  Table_map :  把表名映射到ID，并且记录列的类型，和表的元数据信息
--  Write_rows:  插入的类型，但是看不到插入的数据

mysql root@localhost:mytest> set binlog_format="STATEMENT";  --临时修改为binlog格式STATEMENT
Query OK, 0 rows affected
Time: 0.001s

mysql root@localhost:mytest> insert into f values (88);
Query OK, 1 row affected
Time: 0.001s

mysql root@localhost:mytest> show binlog events in 'binlog.000024';
+---------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                                                 |
+---------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------+
| binlog.000024 | 4   | Format_desc    | 1         | 123         | Server ver: 5.7.18-log, Binlog ver: 4                                |
| binlog.000024 | 123 | Previous_gtids | 1         | 194         | 3eae5854-d89f-11e7-ab20-246e960a8d84:1-9874                          |
| binlog.000024 | 194 | Gtid           | 1         | 259         | SET @@SESSION.GTID_NEXT= '3eae5854-d89f-11e7-ab20-246e960a8d84:9875' |
| binlog.000024 | 259 | Query          | 1         | 333         | BEGIN                                                                |
| binlog.000024 | 333 | Table_map      | 1         | 379         | table_id: 2453 (mytest.f)                                            |
| binlog.000024 | 379 | Write_rows     | 1         | 419         | table_id: 2453 flags: STMT_END_F                                     |
| binlog.000024 | 419 | Xid            | 1         | 450         | COMMIT /* xid=4590 */                                                |
| binlog.000024 | 450 | Gtid           | 1         | 515         | SET @@SESSION.GTID_NEXT= '3eae5854-d89f-11e7-ab20-246e960a8d84:9876' |
| binlog.000024 | 515 | Query          | 1         | 598         | BEGIN                                                                | --begin开启事务
| binlog.000024 | 598 | Query          | 1         | 701         | use `mytest`; insert into f values (88)                              | --insert语句
| binlog.000024 | 701 | Xid            | 1         | 732         | COMMIT /* xid=4605 */                                                | --commit提交事务
+---------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------+
11 rows in set
Time: 0.013s
mysql root@localhost:mytest>

3.5. mysqlbinlog

[root@localhost-m(252) /r2/mysqldata]# mysqlbinlog binlog.000030  
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180203 14:40:40 server id 1  end_log_pos 123 CRC32 0x0e8ba826 	Start: binlog v 4, server v 5.7.18-log created 180203 14:40:40
BINLOG '
aFl1Wg8BAAAAdwAAAHsAAAAAAAQANS43LjE4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
ASaoiw4=
'/*!*/;
# at 123
#180203 14:40:40 server id 1  end_log_pos 194 CRC32 0x8611e174 	Previous-GTIDs
# 3eae5854-d89f-11e7-ab20-246e960a8d84:1-9901
# at 194
#180203 14:40:53 server id 1  end_log_pos 259 CRC32 0x0973cd11 	GTID	last_committed=0	sequence_number=1
SET @@SESSION.GTID_NEXT= '3eae5854-d89f-11e7-ab20-246e960a8d84:9902'/*!*/;
# at 259
#180203 14:40:53 server id 1  end_log_pos 381 CRC32 0x38153efb 	Query	thread_id=5	exec_time=0	error_code=0
use `mytest`/*!*/;
SET TIMESTAMP=1517640053/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=192,@@session.collation_connection=192,@@session.collation_server=192/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
DROP TABLE `t_bin` /* generated by server */
/*!*/;
--------------------------------省略其他输出-------------------------------------

[root@localhost-m(252) /r2/mysqldata]# mysqlbinlog binlog.000031 -vv      --增加 -vv 参数，可以看到注释信息
--------------------------------省略其他输出-------------------------------------
# at 333
#180203 14:45:53 server id 1  end_log_pos 384 CRC32 0x13dd8014 	Table_map: `mytest`.`t_bin` mapped to number 2637
# at 384
#180203 14:45:53 server id 1  end_log_pos 455 CRC32 0x71f44f4c 	Delete_rows: table id 2637 flags: STMT_END_F

BINLOG '
oVp1WhMBAAAAMwAAAIABAAAAAE0KAAAAAAEABm15dGVzdAAFdF9iaW4AAgMDAAMUgN0T
oVp1WiABAAAARwAAAMcBAAAAAE0KAAAAAAEAAgAC//wEAAAAPQAAAPwEAAAAPQAAAPwEAAAAPQAA
APwEAAAAPQAAAExP9HE=
'/*!*/;
### DELETE FROM `mytest`.`t_bin`
### WHERE
###   @1=4 /* INT meta=0 nullable=1 is_null=0 */
###   @2=61 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `mytest`.`t_bin`
### WHERE
###   @1=4 /* INT meta=0 nullable=1 is_null=0 */
###   @2=61 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `mytest`.`t_bin`
### WHERE
###   @1=4 /* INT meta=0 nullable=1 is_null=0 */
###   @2=61 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `mytest`.`t_bin`
### WHERE
###   @1=4 /* INT meta=0 nullable=1 is_null=0 */
###   @2=61 /* INT meta=0 nullable=1 is_null=0 */
# at 455
#180203 14:45:53 server id 1  end_log_pos 486 CRC32 0xd85c12e7 	Xid = 5641
COMMIT/*!*/;
# at 486
#180203 14:46:08 server id 1  end_log_pos 530 CRC32 0x626124b1 	Rotate to binlog.000032  pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@localhost-m(252) /r2/mysqldata]#

3.5. binlog_rows_query_log_events

打开该参数，可以在row格式下，查看到对应的sql信息

mysql root@localhost:mytest> show  variables  like  "binlog_rows_query_log_events";
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| binlog_rows_query_log_events | OFF   |  --默认关闭
+------------------------------+-------+
1 row in set
Time: 0.013s
mysql root@localhost:mytest> set  binlog_rows_query_log_events=1;  --配置参数启动
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> show  variables  like  "binlog_rows_query_log_events";
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| binlog_rows_query_log_events | ON    |  --已启动
+------------------------------+-------+
1 row in set
Time: 0.012s
mysql root@localhost:mytest> insert into t_bin values(49,2222);  --插入一条语句
Query OK, 1 row affected
Time: 0.001s
mysql root@localhost:mytest> delete from t_bin where a=100;     --delete一条语句
You\'re about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
Query OK, 1 row affected
Time: 0.001s
mysql root@localhost:mytest> show binlog events in "binlog.000033";    --显示binlog.000033里面的事务
+---------------+------+----------------+-----------+-------------+----------------------------------------------------------------------+
| Log_name      | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                 |
+---------------+------+----------------+-----------+-------------+----------------------------------------------------------------------+
| binlog.000033 | 4    | Format_desc    | 1         | 123         | Server ver: 5.7.18-log, Binlog ver: 4                                |
| binlog.000033 | 123  | Previous_gtids | 1         | 194         | 3eae5854-d89f-11e7-ab20-246e960a8d84:1-9909                          |
-------------省略其他输出------------------
| binlog.000033 | 1281 | Gtid           | 1         | 1346        | SET @@SESSION.GTID_NEXT= '3eae5854-d89f-11e7-ab20-246e960a8d84:9914' |
| binlog.000033 | 1346 | Query          | 1         | 1420        | BEGIN                                                                |
| binlog.000033 | 1420 | Rows_query     | 1         | 1477        | # insert into t_bin values(49,2222)                                  |  --对应的insert语句
| binlog.000033 | 1477 | Table_map      | 1         | 1528        | table_id: 2637 (mytest.t_bin)                                        |  
| binlog.000033 | 1528 | Write_rows     | 1         | 1572        | table_id: 2637 flags: STMT_END_F                                     |  --只能看到页的变化
| binlog.000033 | 1572 | Xid            | 1         | 1603        | COMMIT /* xid=53828 */                                               |
| binlog.000033 | 1603 | Gtid           | 1         | 1668        | SET @@SESSION.GTID_NEXT= '3eae5854-d89f-11e7-ab20-246e960a8d84:9915' |
| binlog.000033 | 1668 | Query          | 1         | 1742        | BEGIN                                                                |
| binlog.000033 | 1742 | Rows_query     | 1         | 1795        | # delete from t_bin where a=100                                      |  --对应的delete语句
| binlog.000033 | 1795 | Table_map      | 1         | 1846        | table_id: 2637 (mytest.t_bin)                                        |
| binlog.000033 | 1846 | Delete_rows    | 1         | 1890        | table_id: 2637 flags: STMT_END_F                                     |
| binlog.000033 | 1890 | Xid            | 1         | 1921        | COMMIT /* xid=53838 */                                               |
+---------------+------+----------------+-----------+-------------+----------------------------------------------------------------------+
34 rows in set
Time: 0.017s
mysql root@localhost:mytest>
--
-- binlog 里面的变化
--
SET @@SESSION.GTID_NEXT= '3eae5854-d89f-11e7-ab20-246e960a8d84:9914'/*!*/;
# at 1346
#180205 11:23:40 server id 1  end_log_pos 1420 CRC32 0xebccb765 	Query	thread_id=17799	exec_time=0	error_code=0
SET TIMESTAMP=1517801020/*!*/;
BEGIN
/*!*/;
# at 1420
#180205 11:23:40 server id 1  end_log_pos 1477 CRC32 0xaba87de7 	Rows_query
# insert into t_bin values(49,2222)                                                                                                         --对应的insert语句
# at 1477
#180205 11:23:40 server id 1  end_log_pos 1528 CRC32 0xae551a60 	Table_map: `mytest`.`t_bin` mapped to number 2637
# at 1528
#180205 11:23:40 server id 1  end_log_pos 1572 CRC32 0x38a7c65f 	Write_rows: table id 2637 flags: STMT_END_F

BINLOG '
PM53Wh0BAAAAOQAAAMUFAACAACFpbnNlcnQgaW50byB0X2JpbiB2YWx1ZXMoNDksMjIyMinnfair
PM53WhMBAAAAMwAAAPgFAAAAAE0KAAAAAAEABm15dGVzdAAFdF9iaW4AAgMDAANgGlWu
PM53Wh4BAAAALAAAACQGAAAAAE0KAAAAAAEAAgAC//wxAAAArggAAF/Gpzg=
'/*!*/;
### INSERT INTO `mytest`.`t_bin`
### SET
###   @1=49 /* INT meta=0 nullable=1 is_null=0 */                                                                                          --insert语句更改的变化
###   @2=2222 /* INT meta=0 nullable=1 is_null=0 */
# at 1572
#180205 11:23:40 server id 1  end_log_pos 1603 CRC32 0xaa39ff00 	Xid = 53828
COMMIT/*!*/;

3.6. row和statement 简单性能对比

  • 1.当写入的数据量 较小 时，row和statement所占用的磁盘空间差不多；
  • 2.当写入的数据量 较大 时, 例如：导入数据，或者批量操作时[update tb set a=a+1;]），ROW要记录每行的变化，所以占用磁盘空间较大。
  • 3.且写入的数据量 很大 时，ROW格式下，commit会比较慢，因为他还要先写如binlog再提交才写入磁盘，时间耗费多◦
    • 假设更新一张千万的表，产生的binlog可能会有几百上千兆，当commit时，写入的数据量就是几百兆，所以会有阻塞等待的效果。表现为binlog在写入到磁盘。

3.7. binlog_cache

3.7.1 判断当前 binlog_cache_size 的设置是否合适?

当使用事务的表存储引擎(如InnoDB）时，所有未提交(uncommitted) 进制日志会被记录到binlog缓存中去，等该事务提交 (committed) 接将缓冲中的binlog写入binlog file。

mysql root@localhost:mytest> show variables like '%binlog_cache%';
+-----------------------+------------+
| Variable_name         | Value      |
+-----------------------+------------+
| binlog_cache_size     | 4194304    |   -- 默认为32K,这里配置4M,session级别的变量，建议勿配置太大
| max_binlog_cache_size | 4294967296 |
+-----------------------+------------+
2 rows in set
Time: 0.012s

mysql root@localhost:mytest> show global status like 'binlog_cache%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 0     | --记录了使用临时文件写二进制日志的次数
| Binlog_cache_use      | 0     | --记录了使用缓冲写二进制日志的次数
+-----------------------+-------+
2 rows in set
Time: 0.011s
mysql root@localhost:mytest>

  • 通过查看 binlog_cache_use 、binlog_cache_disk_use 的状态,可以判断当前 binlog_cache_size 的设置是否合适。

    • binlog_cache_size 是基于会话 (session) 的，也就是说，当一个线程开始一个事务时， MySQL会自动分配一个大小为 binlog_cache_size 的缓存，因此该值的设置不能过大。
    • 当一个事务的记录大于设定的 binlog_cache_size 时， MySQL 会把缓冲中的日志写人一个临时文件中，因此该值的设置不能太小。
    • 如果参数 Binlog_cache_disk_use 次数很多，就要看一下 binlog_cache_size 设置是否 太小 ，或者 事物本身 是否太大MySQL使用在OLTP的场景下，应该是很快速的小事物。如果有大的事物，应该把 大的事物拆成小事物 去执行

3.8. binlog与redo的一致性

  • 使用 内部分布式事务 来保证一致性，
graph TD
A(user/system commit)-->B(prepare log)
B-->C(binlog)
C-->D(redo_file)

在 commit时，会有如下几个步骤：

  • 1.InnoDB层写prepare log
    • 写的还是 redo file （或者就是redo log，只是内容不一样，这里不是记录页的变化了）
    • 写入的是 xid （事物id，show binlog events in “bin.000056”）
      • 准确的说， xid 是写在 undo 页上的
  • 2.MySQL层 写 binlog
  • 3.InnoDB层 写 commit log （这里同样也是redo log file）

注意：这里的写入是指写入到磁盘（落盘成功）

  1. 假设，如果 没有 第一步的 prepare log ，而是直接写第二步的MySQL binlog，以及接着写第三步的 InnoDB commit log

    此时假设出现 binlog写入成功 ，而 commit log(redo)写入失败 的情况（比如宕机），那随后机器重启时 恢复 时，就会对该事物 回滚 ；

    万一此时的 binlog 已经传递到了 slave 机器上，且slave上commit 了。那此时 主从就不一致 （Master上回滚了）
  2. 现在有 prepare log 了以后， prepare log写入成功 ， binlog写入成功 ，而 commit log(redo)写入失败 的情况下；

    此时事物恢复的时候， 检查到prepare log写入成功，binlog写入成功 ，那就直接 commit 了（可以理解成补了那次失败的commit），而 不管commit log是否写入成功 了。
  3. 如果 prepare log写入成功 ， binlog写入失败 了，那恢复时，也会回滚
  4. 如果 没开binlog ，就没有第一和第二步， 只写第三步的commit log ，恢复的时候没有commit log，就会回滚

  • 一个事物在prepare log中写入成功，在binlog中写入成功，那就 必须要提交 （commit））
  • xid 即 写入prepare log 中，也会 写入到binlog 中，当恢复时，会 对比xid在两个文件中是否都存在，如果都存在，该xid对应的事物才会提交
    • 1.在MySQL5.6以后， 写入binlog（步骤二） 和 写入commit log（步骤三） ，都是通过 组提交 的方式刷入（fsync）到磁盘的。
    • 2.在MySQL 5.7以后，写入 prepare log（步骤一） 也是通过 组提交 的方式刷入（fsync）到磁盘的（在写binlog之前执行一次fsync，就批量刷入prepare log）

注意：组提交中失败了，并 不会回滚 该组中的 所有事物 ，而是哪个失败了，就回滚哪个

3.9. REDO 和 binlog的总结

  • 1.redolog是 InnoDB 层的；binlog是 MySQL 层的。
  • 2.redolog是 物理逻辑 日志；binlog是 逻辑 日志。
  • 3.写入的时间点不一样
    • redo log 可以有多个写入点，比如master thread刷新，buffer大于1/2，事物提交等等
    • binlog只会在 事物提交 的时候写入

3.10. 分布式事务

  • 终端1
mysql root@localhost:mytest> create table t_xa_1 (a int primary key);
Query OK, 0 rows affected
Time: 0.011s

mysql root@localhost:mytest> xa start 'A';     -- 开始一个分布式事务A，不是传统的begin。而是 xa start
Query OK, 0 rows affected
Time: 0.001s

mysql root@localhost:mytest> insert into t_xa_1 values(49);  
Query OK, 1 row affected
Time: 0.001s

mysql root@localhost:mytest> xa end 'A';  -- 结束一个分布式事务A，此时并未提交
Query OK, 0 rows affected
Time: 0.001s

mysql root@localhost:mytest> xa prepare 'A';  -- 两阶段事务 -prepare
Query OK, 0 rows affected
Time: 0.001s 

mysql root@localhost:mytest> xa recover;   -- 查看分布式事务
+----------+--------------+--------------+------+
| formatID | gtrid_length | bqual_length | data |
+----------+--------------+--------------+------+
| 1        | 1            | 0            | A    |
+----------+--------------+--------------+------+
1 row in set
Time: 0.016s
mysql root@localhost:mytest>
  • 终端2
mysql gcdb@localhost:mytest>  select * from t_xa_1; -- 虽然在会话1中已经 end 了，但是其实在会话2中是看不到的，符合ACID
+---+
| a |
+---+
0 rows in set
Time: 0.010s
  • 终端1
mysql root@localhost:mytest> xa commit 'A'; -- xa commit 才是提交分布式事务
Query OK, 0 rows affected
Time: 0.001s
  • 终端2

mysql gcdb@localhost:mytest> select * from t_xa_1;
+----+
| a  |
+----+
| 49 |    -- 会话1提交后，会话2中能看到插入的数据
+----+
1 row in set
Time: 0.010s
mysql gcdb@localhost:mytest>

XA START xid # 开启一个分布式事务

XA END xid # 结束一个分布式事务

XA PREPARE xid # 将分布式事务变成prepare状态

XA COMMIT xid # 提交一个分布式事务

XA ROLLBACK xid # 回滚一个分布式事务

XA RECOVER # 查看分布式事务

上述在单实例中操作分布式事务其实是没有意义的，仅仅作为一个语法的演示。

  1. 分布式事物是 串行 执行的（不能快照读），在分布式事物中，使用的是两阶段事物，如果prepare成功了，就一定要提交。
  2. 如果发生commit失败，事物就变成了 悬挂 事物，需要人工介入，查看prepare是否成功，而后决定commit 或者 rollback

四. UNDO

4.1. Checkpoint

MySQL 的 Checkpoint 是记录在 redo log file （重做日志）的 前2K字节 中（一个logblock=512Byte，所以是前四个log block）

  • 存在两个checkpoint（CP1和CP2），两个checkpoint循环写入

    • 所以说，redo log的写入其实 不是完全顺序 ，因为还需要更新checkpoint中的内容。
    • 通过判断两个checkpoint中的值的大小，可以判断哪个checkpoint是最后写入的。即使使用小的checkpoint，也无非多恢复一些数据、多花一点时间而已。
    • 设计成两个，主要是为了容灾考虑。

4.2. UNDO对象

  • rollback segment – 回滚段
  • undo log segment – undo段
    • undo 段才是真正的存储undo 日志
  • undo page
  • undo log
  • undo log record

4.2.1 rollback segment（回滚段）

  • MySQL 5.5 前只有1个 rollback segment
  • MySQL 5.5+ 有 128个 rollback segment
  • MYSQL 5.7.2 之后可以定制
    • innodb_undo_logs=128 –-表示Rollback segmentd的配置个数128
    • innodb_undo_tablespaces=3 –- 保存3个undo log文件
    • innodb_undo_directory=/r2/undologs/ –-表示undo存放的独立目录
  • 回滚段slot 分配如下：
    • slot 0 ，预留给系统表空间；
    • slot 1- 32，预留给临时表空间，每次数据库重启的时候，都会重建临时表空间；
    • slot 33-127，如果有独立表空间，则预留给UNDO独立表空间；如果没有，则预留给系统表空间；
  • InnoDB undo的空间管理简图如下：

  • undo默认存放在 ibdata1中，即系统表空间中。

  • 系统表空间中的内容:

    • 1.double write
    • 2.元数据信息（数据字典）
    • 3.undo信息（5.6后可分离），分开放可避免竞争
      • innodb_undo_directory
      • innodb_undo_logs
      • innodb_undo_tablespaces
    • 4.insert buffer / change buffer

MySQL5.5 中只有一个Rollback Segment，即 只有1024个 undo log segment，那就表示 最多只有1024个并发 事椌(线程)去执行undo ；

如果 用不到undo，其实是 可以超过1024 个线程的 ；

在MySQL5.7以后，Rollback Segment从128个 减小到96个 ，剩余的 32个rollback segment 预留给临时表空间（ ibtmp1 ）使用,可执行 96*1024 个并发事务操作。

4.2.2 undo log segment （undo段）

  • undo log segment
    • undo 日志
    • undo 日志页头（undo log page header）
      • 每个事务最多需分配两个undo log page header
    • undo段段头（undo log segment header ）

4.2.3 undo page

  • undo page
    • 每个undo page可以保存多个事务的undo log
    • undo页重用
      • 当页的使用空间小于¾

redo写prepare日志，其实就是把上图中的 TRX_UNDO_STATE 修改成 PREPARE 状态。（redo记录的是页的变化，undo也是页）

4.2.3 undo log

  • undo log 以逻辑的方式进行存储

  • undo log header 用于保存每个事务undo日志通用信息

  • undo log record

    • 1.insert undo log record – 记录insert
    • 2.update undo log record – 记录update和delete

  • 包含undo 日志详细信息的undo页描述

undo 的相关对象整体结构:

  • ro ll back segment (trxOrseg. *) ;
  • undo segment (trxOundo. * ) ;
  • undo log (trxOrec . *) ;
  • undo 10g beader !
  • undo 10g record
  • 整体结构

4.3. Purge

  • 真正的删除记录

  • 删除undo log

  • 案例：

    • 表t1中有记录pk=1，2，3；执行DELTE FROM t1 WHERE PK=1;
    • 1.将pk=1的记录标记为删除 （delete-mark，info bits），数据库中pk=1的记录此时还是存在的，空间并没有被释放，该操作为同步操作（SQL执行完，也就标记完成了）
    • 2.purge调用后台线程（purge线程）异步操作，会真正的删除该记录，且空间被释放。purge线程是系统自动的，无法人工控制

  • 标记为已删除的原因：

    • 1.该事椌可能需要回滚，先作保留。
    • 2.当 事务1 去删除pk=1且 没有提交 时，事务2 应该要能看到pk=1的记录（事务的隔离性）。

  • 过滤条件是聚集索引

    • 1.delete 将该记录标记为 delete-mark 。
    • 2.update 将该记录 先物理delete（聚簇索引里主键相同的行最多只能有1个），然后 insert（或者可以原地更新[in place update]）（即使删除了，也可以通过undo进行还原）。

  • 过滤条件是二级索引

    • 1.delete 将该记录标记为 delete-mark 。
    • 2.update 将该记录标记为 delete-mark（索引列是columns + pk，即使是唯一索引更新也是和原来的不一样），然后 insert。

  • 为什么没有insert

    • 1.insert操作是不需要异步去purge，因为insert之前记录是不存在的;
    • 2.不存在记录（未提交）是没有别的事务能引用到的，所以insert以后，对应的undo可以直接删除，而不需要等待异步purge

  • 总结：

    • 1.delete-mark的记录最后会被purge线程回收，Purge会检测记录上是否有其他事物在引用undo，如果没有就可以删除。
    • 2.innodb_purge_threads （5.6以后），可以设置的大一些，回收的速度会快一些。
posted @ 2018-01-05 18:04  貔貅小米豆  阅读(334)  评论(0)  编辑  收藏  举报