MySQL 5.6 新參數對binlog日誌量的最佳化

標籤：after   dev   傳輸   defaults   blank   user   start   style   replicat   

資料庫版本：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情況如下：

1. --建表語句
2. CREATE TABLE `pk_test`(
3. `id` bigint(20) NOT NULL,
4. `username` varchar(30) NOT NULL,
5. PRIMARY KEY (`id`)
6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
7. insert into pk_test values (1,2);
8. insert into pk_test values (2,2);
9. commit;
11. show master statusG;--記錄binlog檔案和pos
13. deletefrom pk_test where id =1;
14. update pk_test set username=‘3‘;
15. commit；
16. mysqlbinlog --no-defaults -v --start-position=637945822/mysqllog/3307/binlog/mysql-bin.000001| more
18. ### DELETE FROM `baofeng`.`pk_test`
19. ### WHERE
20. ### @1=1
21. ### @2=‘2‘
22. .....
23. ### UPDATE `baofeng`.`pk_test`
24. ### WHERE
25. ### @1=2
26. ### @2=‘2‘
27. ### SET
28. ### @1=2
29. ### @2=‘3‘

從上面我們可以看到，在預設為FULL的binlog_row_image下，無論表有沒有主鍵、唯一索引，全部按照全表欄位作為條件，且update會更新全部欄位。

binlog_row_image=minimal的情況下：

1. --建表語句
2. CREATE TABLE `ui_test`(
3. `id` bigint(20) NOT NULL,
4. `username` varchar(30) NOT NULL,
5. UNIQUE (`id`)
6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
8. CREATE TABLE `ui_test_null`(
9. `id` bigint(20),
10. `username` varchar(30) NOT NULL,
11. UNIQUE key (`id`)
12. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
14. CREATE TABLE `null_test`(
15. `id` bigint(20),
16. `username` varchar(30) NOT NULL
17. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
19. insert into pk_test values (1,2);
20. insert into ui_test values (1,2);
21. insert into ui_test_null values (1,2);
22. insert into null_test values (1,2);
23. commit;
25. update pk_test set username=‘4‘;
26. deletefrom pk_test;
27. deletefrom ui_test;
28. deletefrom ui_test_null;
29. update null_test set username=‘4‘;
30. deletefrom null_test;
32. ### UPDATE `baofeng`.`pk_test`
33. ### WHERE
34. ### @1=1
35. ### SET
36. ### @2=‘4‘
37. ....
38. ### DELETE FROM `baofeng`.`pk_test`
39. ### WHERE
40. ### @1=1
41. .....
42. ### DELETE FROM `baofeng`.`ui_test`
43. ### WHERE
44. ### @1=1
45. .....
46. ### DELETE FROM `baofeng`.`ui_test_null`
47. ### WHERE
48. ### @1=1
49. ### @2=‘2‘
50. .....
51. ### UPDATE `baofeng`.`null_test`
52. ### WHERE
53. ### @1=1
54. ### @2=‘2‘
55. ### SET
56. ### @2=‘4‘
57. .....
58. ### DELETE FROM `baofeng`.`null_test`
59. ### WHERE
60. ### @1=1
61. ### @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

MySQL 5.6 新參數對binlog日誌量的最佳化