mysql二进制日志方式差异

为什么在mixed模式下插入删除300W条记录的时间跟row模式下差那么多呢？
难道是因为事务是在保存完二进制日志以后才提交的？
这个是由什么参数控制的呢？
还有为什么在row模式下第一次插入记录跟后面插入记录的时间差距也那么大？

QUOTE:mysql> set binlog_format=row;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test3 select * from test_no_myisam;
Query OK, 3288369 rows affected (3.00 sec)
Records: 3288369  Duplicates: 0  Warnings: 0

mysql> delete from test3;
Query OK, 3288369 rows affected (7.37 sec)

mysql> insert into test3 select * from test_no_myisam;
Query OK, 3288369 rows affected (13.44 sec)
Records: 3288369  Duplicates: 0  Warnings: 0

mysql> delete from test3;
Query OK, 3288369 rows affected (7.26 sec)

mysql> insert into test3 select * from test_no_myisam;
Query OK, 3288369 rows affected (13.30 sec)
Records: 3288369  Duplicates: 0  Warnings: 0

mysql> set binlog_format=mixed;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test3 select * from test_no_myisam;
Query OK, 3288369 rows affected (1.71 sec)
Records: 3288369  Duplicates: 0  Warnings: 0

mysql> delete from test3;
Query OK, 6576738 rows affected (1.27 sec)

mysql> insert into test3 select * from test_no_myisam;
Query OK, 3288369 rows affected (1.71 sec)
Records: 3288369  Duplicates: 0  Warnings: 0

mysql> delete from test3;
Query OK, 3288369 rows affected (0.06 sec)

mysql> insert into test3 select * from test_no_myisam;
Query OK, 3288369 rows affected (1.71 sec)
Records: 3288369  Duplicates: 0  Warnings: 0

mysql> delete from test3;
Query OK, 3288369 rows affected (0.07 sec

作者: linjia828 发布时间: 2011-05-13

innodb是先写二进制日志再commit

作者: ymhtt 发布时间: 2011-05-13

mysql二进制日志方式差异

热门频道