MySQL事务的隔离级别和日志记录模式选择

导读：

MySQL的四种事务隔离级别：Read-uncommitted、Read-committed、Repeatable-read、Seriailizable，相信大家都清楚各自异同，不清楚的朋友可以查看另外一篇技术文章：MySQL_InnoDB之事务与锁详解。但是对于第二类、第三类隔离级别之间的性能区别和应用场景就会容易出现一些理解上的偏差，尤其是熟悉Oracle的技术朋友，为此专门撰写一篇技术文章，引导大家合理地选择这两种事务隔离级别。

 

测试环境及名词解释：

操作系统：CentOS release 5.5 (Final)

MySQL版本：5.1.40-community-log

InnoDB版本：build-in

测试的事务隔离级别：Read-committed（以下简称：RC）、Repeatable-read（以下简称：RR）

日志登记选项（简称：LBO）：STATEMENT-based  logging（简称：LBS）、

ROW-based  format（简称：LBR）

基于日志复制模式（简称：RBO）：STATEMENT、ROW、MIXED

 

事务隔离级别和日志模式组合的分析和总结：

n  事务隔离级别为：Read-committed（简称：RC）

事务安全性：不支持对InnoDB引擎表作DML（DML指：INSERT、UPDATE、DELETE），但是允许对非事务引擎表的数据进行一切操作；

事务性能：不支持对事务引擎InnoDB表进行操作；

 

u  RC与 STATEMENT配置组合

日志记录格式：所有的变更操作都以基于命令方式登记二进制日志（简称：LBS）；

复制安全性：对于SQL语句中，若存在不确定性的函数，则数据复制存在一致性；

IO量：无增加；

 

u  RC与 MIXED配置组合

事务安全性：结合InnoDB提供的MVCC功能，可以做到只看见已经提交事务修改后的数据，但是无法确保同一事务内，同一个查询语句二次执行，

获得的记录集相同；

事务性能：会比不提交读隔离级别性能低，但比可重复读隔离级别性能高；

日志记录格式：所有的变更操作都以基于行模式登记二进制日志（简称：LBR）；

复制安全性：能做到主备数据复制的一致性；

IO量：所有的DML操作都将转化成基于行模式登记二进制日志，那么会增加大量物理写IO；

 

u  RC与 ROW配置组合

若是事务隔离级别设置为：Read-committed（以下简称：RC），那么无论日志模式（注：binlog_format）设置为：MIXED　或者　ROW，二进制日志都将以ROW模式登记，为此与RC+MIXED配置组合相同，不赘述。

 

n  事务隔离级别为：Repeatable-read（简称：RR）

事务安全性：在RC隔离级别优点的基础之上，做到了同一个事务内，同一个查询请求，多次执行，获得的记录集一定相同；

事务性能：比RC事务隔离级别消耗的资源更多一些，也即性能低一些，但比

Seriailizable隔离级别的性能好；

u  RR与 STATEMENT配置组合

日志记录格式：基于命令行模式登记二进制日志（简称：LBS）；

复制安全性：对于SQL语句中，若存在不确定性的函数，则数据复制存在一致性；

IO量：无增加；

 

u  RR与 MIXED配置组合

日志记录格式：对于SQL语句中无不确定性函数的DML操作，则会基于命令行模式登记二

进制日志（简称：LBS）；但是对于包含不确定性函数的DML操作，则一定

会使用基于行模式登记二进制日志（简称：LBR）

复制安全性：能确保数据复制的正确性；

IO量：相比STATEMENT可能会增加，但是否增加二进制的量，主要看编写的SQL语句，是否包含一些不确定性的函数；

 

u  RR与 ROW配置组合

日志记录格式：对于所有的DML操作，都采用基于行的模式登记二进制日志，；

复制安全性：能确保数据复制的正确性；

IO量：全采用基于行的模式登记二进制日志，将明显增加物理IO；

 

事务隔离级别和日志模式组合适用的场景阐述：

u  RC与 STATEMENT配置组合

结合上述的分析和总结，提交读+基于命令行模式。首先是跑事务引擎的mysqld服务，不支持此组合模式，那么其适合场景：

1>.使用非事务引擎存储数据、支撑业务，不使用事务引擎 （一般指：InnoDB引擎）；

2>.不需要使用到mysql复制的架构，或者SQL语句确定不包含不确定性函数等内容；

 

u  RC与 MIXED配置组合

1>.允许事务中，存在同一个SQL查询语句多次执行获得的记录集不同，或者规避此类业务；

2>.读操作量远远大于写操作的业务场景；

3>.不需要打开二进制日志功能的业务场景；

 

u  RC与 ROW配置组合

对于事务隔离级别：RC，无论binlog_format设置为：MIXED　还是　ROW，其二进制日志登记模式都一样，所以其适合场景与RC与 MIXED配置组合一样。

u  RR与 STATEMENT配置组合

1>.需要确保事务中，同一个SQL查询语句多次执行获得的记录集相同的业务场景；

2>.不需要关心读写比例的业务场景；

3>.不使用mysql的复制功能，或者DML操作SQL确保不存在不确定性的内容；

u  RR与 MIXED配置组合

1>.需要确保事务中，同一个SQL查询语句多次执行获得的记录集相同的业务场景；

2>.需要使用mysql的复制功能，且不想关心 DML操作类SQL语句是否存在不确定性的内容；

3>.更新操作量还是比较多，且想减少登记二进制日志而增加的物理IO，以及加速mysql复制的速度；

u  RR与 ROW配置组合

1>.需要确保事务中，同一个SQL查询语句多次执行获得的记录集相同的业务场景；

2>.需要使用mysql的复制功能，且不想关心 DML操作类SQL语句是否存在不确定性的内容；

3>.以读为主的业务，更新量较少且从设计上规避行模式登记日志缺陷的业务场景；

推荐组合模式：

若需要打开二进制日志功能，且需要使用mysql复制，但业务是以读为主，且更新量为主的表，被设计成非常轻小型，也不想严格关心SQL写法。例如：常更新的字段放一起且最好是整形的，不常更新的字段存放一起，一定无大字段（注释：TEXT、BLOB等）。那么可以考虑使用：RC+MIXED组合模式。

若需要打开二进制日志功能，且需要使用mysql复制，但业务的读写量相差不大，且不想为规避登记二进制日志的问题而设计表，也不想严格关心SQL写法，那么建议使用：RR+MIXED组合模式。

当然对于不需要打开二进制日志功能的业务，那选择就容易，关键在选择事务隔离级别为：RC还是RR的问题，为事务安全性角度出发，选择：RR，为从事务消耗资源，也即性能出发，选择：RC。

为方便大家阅读，以及适应快餐式文化氛围，文章开头特意先写对比、分析和结论，那么接下来将把测试过程，以及一些对比信息告诉大家，建议一线技术人员一定要看下测试过程.测试过程，也是分设置不同事务隔离级别tx_isolation的值，配合设置不同binlog_format的值，然后执行数据的更新语句，再使用mysqlbinlog工具解读二进制日志文件的内容。

测试用例：

u  测试表的数据

备注: 测试过程中,更新ID=1 和 ID=4 的纪录。

u  用于测试的SQL

测试对比信息过程：

u  RC与 STATEMENT配置组合

MySQL 5.1系列设置 RC+STATEMENT组合的模式，无法对支持事务的InnoDB引擎作数据更新操作，为此我们在实际的生产环境中无法使用，当然若关闭mysql的二进制日志登记功能是可以的，或者打开允许不安全模式登记二进制日志的参数，也是可以的。

u  RC与 MIXED配置组合

事务隔离级别、日志模式、执行的SQL信息：

紧接着我们翻译二进制日志信息，看下二进制日志中登记的内容是啥？

1>.1号SQL对应的二进制日志信息 

我们只修改了TIMESTAMP类型字段字段：alterDate的值，其他值都没有变化，但是都被记录到二进制日志中，而且在WHERE 和SET 部分都有出现。

 

2>.3号SQL语句对应的二进制日志信息

备注：

编号为：3的SQL语句中使用了范围条件更新日期的方式，并且日期值也特意加入了随机函数，但从二进制日志中，我们可以发现，都是固定的值和只登记了更新到的记录内容。

对于其他SQL更新产生的内容也是类似的，其他3条SQL语句执行后，在二进制日志文件中登记的内容，读者们可以自己使用：mysqlbinlog –v –v  二进制日志文件　方式查看，节省点篇幅，文章后面我们总结日志记录特点。

u  RC与ROW配置组合

我们从RC+MIXED配置组合测试的内容，可以看到其全部转换成了行模式登记二进制日志文件内容，那么此模式就没有多阐述的地方，我们错开的方式，察看下编号为：４的SQL二进制日志信息，如图：

备注：

编号为4的SQL中涉及不确定性函数：UUID()，WHERE条件为：范围扫描，真实记录的二进制日志内容，都是固定的字段属性值，和真实被更新到的记录。

u  RR与 STATEMENT配置组合

请读者注意标注红线的地方，对于存在随机数函数，以及不确定性的UUID函数，二进制日志文件都是原样登记的，含有这些不确定性函数的SQL再次执行，将无法获得相同的值，这会给mysql的复制带来麻烦，以及使用二进制日志进行数据恢复的时候。

u  RR与 MIXED配置组合

此部分我们重点看2部分的日志，一部分是选择以命令行模式登记的二进制日志，另外一类是选择以行模式登记的二进制日志，第一类，如图：

请读者再回味下上一个节点：RR+STATEMENT组合产生的二进制日志，对比我们会发现，其都是以基于命令行模式，但是RR+MIXED组合配置，则增加了一些内容，例如图中红色字段部分，为使大家看得更详细，关于如何处理随机函数的问题，以确保在二进制日志用于恢复的时候，确保SQL产生的记录值是永恒固定的，再单独截图一张：

接下来我们再看下，对于无法通过添加类似Oracle的hint内容，解决不确定性函数登记到二进制日志文件中的问题，先看二进制日志截图：

对于mysqld没有支持或无法通过添加类似hint模式解决的不确定性问题，RR+MIXED模式下，二进制日志登记将会以行模式登记二进制日志，而解决不确定性问题。

u  RR与 ROW配置组合

对于RR+ROW组合配置，所有DML操作的SQL日志都是以基于行模式登记，为此我们只看编号为：1、4两条SQL的二进制日志内容，编号为１的SQL对应的二进制日志内容，如图：

接下来我们再看下编号为：4的SQL对应的二进制日志内容，如图：

通过阅读翻译后的二进制日志详细内容，以及对比不同事务隔离级别和binlog_format配置组合情况下，登记的二进制日志， 我们可以分析、总结出如下内容：

l  二进制日志文件中，表结构中字段名成都是用@符号，加数字编号的模式，且是按字段先后顺序编号的方式，此地方就是一个风险点，尤其双主复制模式 下，后续篇章讲解复制存在的风险点详细告诉大家；

l  RC模式下对事务引擎：InnoDB是全部采用行模式记录二进制日志；

l  行模式登记二进制日志时，字段的原内容是全部出现在：WHERE部分，需要被修改后的内容全部显示在：SET部分，为此若表字段多或有大字段，对数据做变更之时，产生的二进制日志内容将会很大，从而消耗大量系统的物理IO资源；

l  行模式登记二进制日志时，对于字段值更新的SQL中不确定性的内容，将会替换为确定性的值登记到二进制日志文件中；

l  行模式登记二进制日志时，若DML操作SQL语句，使用了范围条件，而登记到二进制日志中的内容，是实际更新的条数详细内容，此模式也会增加大量二进制日志的容量；

l  行模式登记二进制日志时，若是表有大字段或者某个表字段很多，那么产生的二进制日志容量就会非常大了，意味着消耗系统的物理IO资源将会急剧增加；

l  Binlog_format设置为：MIXED,对于二进制日志以基于命令行模式 还是 基于行模式登记，取决于：事务隔离级别和所涉及的SQL共同决定，但是对于RC事务隔离级别，就无论什么类型的DML操作SQL，都以行模式登记；

l  事务隔离级别为：RC情况下，binlog_format设置为MIXED还是为ROW，对于二进制日志登记而言都是一样的，以行模式登记二进制日志。

l  事务隔离级别为：RR情况下，binlog_format设置为MIXED，mysqld服务会根据DML操作的SQL语句情况，决定是以行模式登记二进制日志，还是以 命令行模式登记；

l  事务隔离级别为：RR情况下，binlog_format设置为MIXED，mysqld服务会添加类似Oracle的hint内容，解决部分不确定性函数而带来的SQL重复执行无法获得相同记录值的问题；