作者：洪斌
爱可生南区负责人兼技术服务总监，MySQL ACE，擅长数据库架构规划、故障诊断、性能优化分析，实践经验丰富，帮助各行业客户解决 MySQL 技术问题，为金融、运营商、互联网等行业客户提供 MySQL 整体解决方案。
本文来源：转载自公众号-玩转MySQL
*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。
简单对比测试
对比禁用与启用 redo log 两种场景下的执行效率，处理 100w 记录（1.8G）文件，sysbench 标准表结构。
从实际测试情况来看，禁用与启用 redo log 有 10%~30% 的执行时间差异。
mysql [localhost:8021] {msandbox} (test) > ALTER INSTANCE DISABLE INNODB REDO_LOG;Query OK, 0 rows affected (0.10 sec)mysql [localhost:8021] {msandbox} (test) > load data infile 'sbtest.txt' into table sbtest1;Query OK, 10000000 rows affected (2 min 39.66 sec)Records: 10000000 Deleted: 0 Skipped: 0 Warnings: 0mysql [localhost:8021] {msandbox} (test) > truncate sbtest1;Query OK, 0 rows affected (0.36 sec)mysql [localhost:8021] {msandbox} (test) > set global sync_binlog=0;set global innodb_flush_log_at_trx_commit=0;Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql [localhost:8021] {msandbox} (test) > load data infile 'sbtest.txt' into table sbtest1;Query OK, 10000000 rows affected (2 min 30.61 sec)Records: 10000000 Deleted: 0 Skipped: 0 Warnings: 0
启用 redo log load data
mysql [localhost:8021] {msandbox} (test) > ALTER INSTANCE ENABLE INNODB REDO_LOG;Query OK, 0 rows affected (0.09 sec)mysql [localhost:8021] {msandbox} (test) > set global sync_binlog=1;set global innodb_flush_log_at_trx_commit=1;Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql [localhost:8021] {msandbox} (test) > load data infile 'sbtest.txt' into table sbtest1;Query OK, 10000000 rows affected (3 min 37.55 sec)Records: 10000000 Deleted: 0 Skipped: 0 Warnings: 0mysql [localhost:8021] {msandbox} (test) > set global sync_binlog=0;set global innodb_flush_log_at_trx_commit=0;Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql [localhost:8021] {msandbox} (test) > truncate sbtest1;Query OK, 0 rows affected (0.34 sec)mysql [localhost:8021] {msandbox} (test) > load data infile 'sbtest.txt' into table sbtest1;Query OK, 10000000 rows affected (2 min 49.84 sec)Records: 10000000 Deleted: 0 Skipped: 0 Warnings: 0
禁用 redo log add index
mysql [localhost:8021] {msandbox} (test) > ALTER INSTANCE DISABLE INNODB REDO_LOG;Query OK, 0 rows affected (0.00 sec)mysql [localhost:8021] {msandbox} (test) > set global sync_binlog=1;set global innodb_flush_log_at_trx_commit=1;Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql [localhost:8021] {msandbox} (test) > alter table sbtest1 add index idx_c(c);Query OK, 0 rows affected (38.96 sec)Records: 0 Duplicates: 0 Warnings: 0mysql [localhost:8021] {msandbox} (test) > set global sync_binlog=0;set global innodb_flush_log_at_trx_commit=0;Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql [localhost:8021] {msandbox} (test) > alter table sbtest1 drop index idx_c;Query OK, 0 rows affected (0.05 sec)Records: 0 Duplicates: 0 Warnings: 0mysql [localhost:8021] {msandbox} (test) > alter table sbtest1 add index idx_c(c);Query OK, 0 rows affected (35.13 sec)Records: 0 Duplicates: 0 Warnings: 0
启用 redo log add index
mysql [localhost:8021] {msandbox} (test) > ALTER INSTANCE ENABLE INNODB REDO_LOG;Query OK, 0 rows affected (0.00 sec)mysql [localhost:8021] {msandbox} (test) > set global sync_binlog=1;set global innodb_flush_log_at_trx_commit=1;Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql [localhost:8021] {msandbox} (test) > alter table sbtest1 add index idx_c(c);Query OK, 0 rows affected (47.05 sec)Records: 0 Duplicates: 0 Warnings: 0mysql [localhost:8021] {msandbox} (test) > set global sync_binlog=0;set global innodb_flush_log_at_trx_commit=0;Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql [localhost:8021] {msandbox} (test) > alter table sbtest1 drop index idx_c;Query OK, 0 rows affected (0.00 sec)Records: 0 Duplicates: 0 Warnings: 0mysql [localhost:8021] {msandbox} (test) > alter table sbtest1 add index idx_c(c);Query OK, 0 rows affected (47.32 sec)Records: 0 Duplicates: 0 Warnings: 0
总结一下
禁用 redo log 不影响 binlog 功能，可以正常同步。 禁用 redo log 是实例级，不支持表级。 禁用 redo log 若发生 crash 是无法 recovery 的，OLTP 系统谨慎使用。 适用于大量数据导入场景。