解读MySQL事务的隔离级别和日志登记模式选择技巧

忘是亡心i 2023-06-15 12:27 42阅读 0赞

MySQL的四种事务隔离级别：Read-uncommitted、Read-committed、Repeatable-read、 Seriailizable，相信大家都清楚各自异同。但是对于第二类、第三类隔离级别之间的性能区别和应用场景就会容易出现一些理解上的偏差，尤其是熟 悉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  
　　事务隔离级别和日志模式组合的分析和总结：

■ 事务隔离级别为：Read-committed(简称：RC)  
　　　事务安全性：不支持对InnoDB引擎表作DML(DML指：INSERT、UPDATE、DELETE)，但是允许对非事务引擎表的数据进行一切操作；  
　　　事务性能：不支持对事务引擎InnoDB表进行操作；

● RC与 STATEMENT配置组合  
　　　　日志记录格式：所有的变更操作都以基于命令方式登记二进制日志(简称：LBS)；  
　　　　复制安全性：对于SQL语句中，若存在不确定性的函数，则数据复制存在一致性；  
　　　　IO量：无增加；

● RC与 MIXED配置组合  
　　　　事务安全性：结合InnoDB提供的MVCC功能，可以做到只看见已经提交事务修改后的数据，但是无法确保同一事务内，同一个查询语句二次执行，获得的记录集相同；  
　　　　事务性能：会比不提交读隔离级别性能低，但比可重复读隔离级别性能高；  
　　　　日志记录格式：所有的变更操作都以基于行模式登记二进制日志(简称：LBR)；  
　　　　复制安全性：能做到主备数据复制的一致性；  
　　　　IO量：所有的DML操作都将转化成基于行模式登记二进制日志，那么会增加大量物理写IO；

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

■ 事务隔离级别为：Repeatable-read(简称：RR)  
　　事务安全性：在RC隔离级别优点的基础之上，做到了同一个事务内，同一个查询请求，多次执行，获得的记录集一定相同；  
　　事务性能：比RC事务隔离级别消耗的资源更多一些，也即性能低一些，但比Seriailizable隔离级别的性能好；

● RR与 STATEMENT配置组合  
　　日志记录格式：基于命令行模式登记二进制日志(简称：LBS);  
　　复制安全性：对于SQL语句中，若存在不确定性的函数，则数据复制存在一致性;  
　　IO量：无增加;

● RR与 MIXED配置组合  
　　日志记录格式：对于SQL语句中无不确定性函数的DML操作，则会基于命令行模式登记二进制日志(简称：LBS);但是对于包含不确定性函数的DML操作，则一定会使用基于行模式登记二进制日志(简称：LBR)；  
　　复制安全性：能确保数据复制的正确性；  
　　IO量：相比STATEMENT可能会增加，但是否增加二进制的量，主要看编写的SQL语句，是否包含一些不确定性的函数;

● RR与 ROW配置组合  
　　日志记录格式：对于所有的DML操作，都采用基于行的模式登记二进制日志;  
　　复制安全性：能确保数据复制的正确性;  
　　IO量：全采用基于行的模式登记二进制日志，将明显增加物理IO;  
  
  
　　事务隔离级别和日志模式组合适用的场景阐述：  
　　● RC与 STATEMENT配置组合  
　　结合上述的分析和总结，提交读+基于命令行模式。首先是跑事务引擎的mysqld服务，不支持此组合模式，那么其适合场景：  
　　1>.使用非事务引擎存储数据、支撑业务，不使用事务引擎 (一般指：InnoDB引擎);  
　　2>.不需要使用到mysql复制的架构，或者SQL语句确定不包含不确定性函数等内容;

● RC与 MIXED配置组合  
　　1>.允许事务中，存在同一个SQL查询语句多次执行获得的记录集不同，或者规避此类业务;  
　　2>.读操作量远远大于写操作的业务场景;  
　　3>.不需要打开二进制日志功能的业务场景;

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

● RR与 STATEMENT配置组合  
　　1>.需要确保事务中，同一个SQL查询语句多次执行获得的记录集相同的业务场景;  
　　2>.不需要关心读写比例的业务场景;  
　　3>.不使用mysql的复制功能，或者DML操作SQL确保不存在不确定性的内容;

● RR与 MIXED配置组合  
　　1>.需要确保事务中，同一个SQL查询语句多次执行获得的记录集相同的业务场景;  
　　2>.需要使用mysql的复制功能，且不想关心 DML操作类SQL语句是否存在不确定性的内容;  
　　3>.更新操作量还是比较多，且想减少登记二进制日志而增加的物理IO，以及加速mysql复制的速度;

● 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工具解读二进制日志文件的内容。  
　　测试用例：  
　　● 测试表的数据  
![format_png][]  
　　　备注: 测试过程中,更新ID=1 和 ID=4 的纪录。  
　　● 用于测试的SQL  
![format_png 1][]  
  
  
　　测试对比信息过程：  
　　● RC与 STATEMENT配置组合  
![format_png 2][]  
　 　MySQL 5.1系列设置 RC+STATEMENT组合的模式，无法对支持事务的InnoDB引擎作数据更新操作，为此我们在实际的生产环境中无法使用，当然若关闭mysql的二 进制日志登记功能是可以的，或者打开允许不安全模式登记二进制日志的参数，也是可以的。  
　　● RC与 MIXED配置组合  
　　事务隔离级别、日志模式、执行的SQL信息：  
![format_png 3][]  
　　紧接着我们翻译二进制日志信息，看下二进制日志中登记的内容是啥?  
　　1>.1号SQL对应的二进制日志信息  
![format_png 4][]

### www.jobbible.cn ###

[format_png]: https://imgconvert.csdnimg.cn/aHR0cDovL3d3dy5kZWRlY21zLmNvbS91cGxvYWRzL2FsbGltZy8xMjA2MDYvMTAwMDNJOUItMC5qcGc?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cDovL3d3dy5kZWRlY21zLmNvbS91cGxvYWRzL2FsbGltZy8xMjA2MDYvMTAwMDNHOTQ2LTEuanBn?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cDovL3d3dy5kZWRlY21zLmNvbS91cGxvYWRzL2FsbGltZy8xMjA2MDYvMTAwMDNNOTE1LTIuanBn?x-oss-process=image/format,png
[format_png 3]: https://imgconvert.csdnimg.cn/aHR0cDovL3d3dy5kZWRlY21zLmNvbS91cGxvYWRzL2FsbGltZy8xMjA2MDYvMTAwMDNIUDgtMy5qcGc?x-oss-process=image/format,png
[format_png 4]: https://imgconvert.csdnimg.cn/aHR0cDovL3d3dy5kZWRlY21zLmNvbS91cGxvYWRzL2FsbGltZy8xMjA2MDYvMTAwMDNHUjQtNC5qcGc?x-oss-process=image/format,png