mysql5.6 binlog参数,MySQL 5.6 新参数对binlog日志量的优化

数据库版本：5.6.*

1.row日志image类型

参数binlog_row_image 控制着这种image类型，默认为FULL(log all columns)，即记录before&after images。

该参数还有两种，minimal和noblob，minimal表示只记录after更改后的值，并且如果有主键或者非空唯一索引，则只以该字段作为where条件判断；noblob同full，只是不记录blob、text列。

2.binlog日志

对于insert则没有什么好说的，我们主要重点关注一下update和delete操作。

binlog_row_image=full的情况下，对于update和delete所有的表(包含带有主键、非空唯一索引，唯一索引，没有索引)产生的binlog均一致，binlog情况如下：

--建表语句

CREATE TABLE`pk_test`(

`id`bigint(20)NOT NULL,

`username`varchar(30)NOT NULL,

PRIMARY KEY(`id`)

)ENGINE=InnoDBDEFAULT CHARSET=utf8;

insertintopk_test values(1,2);

insertintopk_test values(2,2);

commit;

show master statusG;--记录binlog文件和pos

deletefrompk_testwhereid=1;

update pk_testsetusername='3';

commit；

mysqlbinlog--no-defaults-v--start-position=637945822/mysqllog/3307/binlog/mysql-bin.000001|more

### DELETE FROM `baofeng`.`pk_test`

### WHERE

### @1=1

### @2='2'

.....

### UPDATE `baofeng`.`pk_test`

### WHERE

### @1=2

### @2='2'

### SET

### @1=2

### @2='3'

从上面我们可以看到，在默认为FULL的binlog_row_image下，无论表有没有主键、唯一索引，全部按照全表字段作为条件，且update会更新全部字段。

binlog_row_image=minimal的情况下：

--建表语句

CREATE TABLE`ui_test`(

`id`bigint(20)NOT NULL,

`username`varchar(30)NOT NULL,

UNIQUE(`id`)

)ENGINE=InnoDBDEFAULT CHARSET=utf8;

CREATE TABLE`ui_test_null`(

`id`bigint(20),

`username`varchar(30)NOT NULL,

UNIQUE key(`id`)

)ENGINE=InnoDBDEFAULT CHARSET=utf8;

CREATE TABLE`null_test`(

`id`bigint(20),

`username`varchar(30)NOT NULL

)ENGINE=InnoDBDEFAULT CHARSET=utf8;

insertintopk_test values(1,2);

insertintoui_test values(1,2);

insertintoui_test_null values(1,2);

insertintonull_test values(1,2);

commit;

update pk_testsetusername='4';

deletefrompk_test;

deletefromui_test;

deletefromui_test_null;

update null_testsetusername='4';

deletefromnull_test;

### UPDATE `baofeng`.`pk_test`

### WHERE

### @1=1

### SET

### @2='4'

....

### DELETE FROM `baofeng`.`pk_test`

### WHERE

### @1=1

.....

### DELETE FROM `baofeng`.`ui_test`

### WHERE

### @1=1

.....

### DELETE FROM `baofeng`.`ui_test_null`

### WHERE

### @1=1

### @2='2'

.....

### UPDATE `baofeng`.`null_test`

### WHERE

### @1=1

### @2='2'

### SET

### @2='4'

.....

### DELETE FROM `baofeng`.`null_test`

### WHERE

### @1=1

### @2='2'

从上面的例子可以看到，当binlog_row_image=minimal的情况下，where条件只有主键或不为空的唯一索引，且只会更新被改变的字段。

3.总结：

在上面的测试我们可以看到，如果采用minimal格式，将减少主键和非空唯一索引表的before值，以及减少所有表update的after未被改变的值。

从效率上来说，减少了网络传输以及加快了update的效率。

参考资料：

https://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_binlog_row_image