MySQL

用实例验证MySQL对批量提交优化效果

Test MySQL's Optimization for BatchUpdate with examples

Posted by LiuShuo on December 20, 2018

接上一篇对MySQL内部对批量提交的优化的分析，本文通过实际测试看一下内部执行SQL时是否会做优化处理。MySQL版本：Mac 5.7 .21-log，mysql-connector-java版本：5.1.43，jdbc url开启rewriteBatchedStatements=true

正常的批量提交

如果是合法的SQL语句，则在batchUpdate()提交时无论下面哪种方式都会成功执行并且一次性返回全部记录的更新结果。 SQL1

1
2
3
4
5
6
7
8

private static final String INSERT_SQL1 = "INSERT INTO %s"
            + "(app_id, group_id, user_id, status, member_role, inviter_id, "
            + "group_member_setting, nickname, create_time, update_time, last_join_time) "
            + "VALUES(:app_id, :group_id, :user_id, :status, :member_role, :inviter_id, "
            + ":group_member_setting, :nickname, :create_time, :update_time, :last_join_time) "
            + "ON DUPLICATE KEY UPDATE status = VALUES(status), member_role = VALUES(member_role), "
            + "inviter_id = VALUES(inviter_id), group_member_setting = VALUES(group_member_setting), nickname = VALUES(nickname), "
            + "update_time = VALUES(update_time), last_join_time = VALUES(last_join_time)";

SQL2

1
2
3
4
5
6
7
8

private static final String INSERT_SQL2 = "INSERT INTO %s"
            + "(`app_id`,`group_id`,`user_id`,`status`,`member_role`,`inviter_id`,`group_member_setting`,`nickname`,"
            + " `create_time`,`update_time`,`last_join_time`)"
            + " VALUES(:app_id,:group_id,:user_id,:status,:member_role,:inviter_id,:group_member_setting,:nickname,"
            + " :create_time,:update_time,:last_join_time) "
            + " ON DUPLICATE KEY UPDATE `status`=:status,`member_role` =:member_role,"
            + " `inviter_id`=:inviter_id,`group_member_setting`=:group_member_setting,`nickname`=:nickname,"
            + " `update_time`=:update_time,`last_join_time`=:last_join_time ";

二者的区别在于，一个使用了MySQL内置的VALUES()函数，一个通过占位符的方式，这两种方式spring-jdbc均可以正常处理。我们需要验证在MySQL内部前一种写法在批量提交时是否会被拼成一个SQL执行，而后者由于写法的限制，只能是当做多条SQL执行。

场景

我们通过程序使用batchUpdate()更新三条数据，其中包括两条已经存在的唯一索引的数据，即其中第一第二条SQL数据的唯一索引冲突，并且和数据库内已存在数据唯一索引冲突，第三条SQL 只和数据库内已存在数据唯一索引冲突。

下面是每个SQL的执行的BINLOG和general log的记录。

SQL1 BINLOG

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94

#181222 14:17:14 server id 1  end_log_pos 15453 CRC32 0x8934853d 	Anonymous_GTID	last_committed=34	sequence_number=35	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 15453
#181222 14:17:14 server id 1  end_log_pos 15525 CRC32 0x7adedeaa 	Query	thread_id=59	exec_time=0	error_code=0
SET TIMESTAMP=1545459434/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=224/*!*/;
BEGIN
/*!*/;
# at 15525
#181222 14:17:14 server id 1  end_log_pos 15596 CRC32 0x822e3744 	Table_map: `test`.`group_member_0` mapped to number 108
# at 15596
#181222 14:17:14 server id 1  end_log_pos 15706 CRC32 0xd36b582d 	Write_rows: table id 108
# at 15706
#181222 14:17:14 server id 1  end_log_pos 15892 CRC32 0x1bd3d5fb 	Update_rows: table id 108
# at 15892
#181222 14:17:14 server id 1  end_log_pos 16002 CRC32 0x53f00f84 	Write_rows: table id 108 flags: STMT_END_F

BINLOG '
6tYdXBMBAAAARwAAAOw8AAAAAGwAAAAAAAEABHRlc3QADmdyb3VwX21lbWJlcl8wAAwIAwgIDwgB
CAEICAgCAAQQAEQ3LoI=
6tYdXB4BAAAAbgAAAFo9AAAAAGwAAAAAAAAAAgAM//8A8MMAAAAAAAAAAgAAAG8AAAAAAAAAeQAA
AAAAAAAAAAAAAAAAAAAAAXkAAAAAAAAAAimCj9RnAQAAKYKP1GcBAAApgo/UZwEAAC1Ya9M=
6tYdXB8BAAAAugAAABQ+AAAAAGwAAAAAAAAAAgAM/////wDwwwAAAAAAAAACAAAAbwAAAAAAAAB5
AAAAAAAAAAAAAAAAAAAAAAABeQAAAAAAAAACKYKP1GcBAAApgo/UZwEAACmCj9RnAQAAAPDDAAAA
AAAAAAIAAABvAAAAAAAAAHkAAAAAAAAAAAAAAAAAAAAAAAF5AAAAAAAAAAEtgo/UZwEAACmCj9Rn
AQAALYKP1GcBAAD71dMb
6tYdXB4BAAAAbgAAAII+AAAAAGwAAAAAAAEAAgAM//8A8MQAAAAAAAAAAgAAAG8AAAAAAAAAegAA
AAAAAAAAAAAAAAAAAAAAAXkAAAAAAAAAAS6Cj9RnAQAALoKP1GcBAAAugo/UZwEAAIQP8FM=
'/*!*/;
### INSERT INTO `test`.`group_member_0`
### SET
###   @1=195 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=0 is_null=0 */
###   @3=111 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @4=121 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @5='' /* VARSTRING(1024) meta=1024 nullable=1 is_null=0 */
###   @6=0 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @8=121 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @9=2 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @10=1545459434025 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @11=1545459434025 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @12=1545459434025 /* LONGINT meta=0 nullable=0 is_null=0 */
### UPDATE `test`.`group_member_0`
### WHERE
###   @1=195 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=0 is_null=0 */
###   @3=111 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @4=121 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @5='' /* VARSTRING(1024) meta=1024 nullable=1 is_null=0 */
###   @6=0 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @8=121 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @9=2 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @10=1545459434025 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @11=1545459434025 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @12=1545459434025 /* LONGINT meta=0 nullable=0 is_null=0 */
### SET
###   @1=195 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=0 is_null=0 */
###   @3=111 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @4=121 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @5='' /* VARSTRING(1024) meta=1024 nullable=1 is_null=0 */
###   @6=0 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @8=121 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @9=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @10=1545459434029 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @11=1545459434025 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @12=1545459434029 /* LONGINT meta=0 nullable=0 is_null=0 */
### INSERT INTO `test`.`group_member_0`
### SET
###   @1=196 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=0 is_null=0 */
###   @3=111 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @4=122 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @5='' /* VARSTRING(1024) meta=1024 nullable=1 is_null=0 */
###   @6=0 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @8=121 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @9=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @10=1545459434030 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @11=1545459434030 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @12=1545459434030 /* LONGINT meta=0 nullable=0 is_null=0 */
# at 16002
#181222 14:17:14 server id 1  end_log_pos 16033 CRC32 0x55250dca 	Xid = 678
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

SQL2 BINLOG

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130

#181222 14:40:21 server id 1  end_log_pos 16098 CRC32 0xca744540 	Anonymous_GTID	last_committed=35	sequence_number=36	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 16098
#181222 14:40:21 server id 1  end_log_pos 16170 CRC32 0xfa9e1fa9 	Query	thread_id=60	exec_time=0	error_code=0
SET TIMESTAMP=1545460821/*!*/;
BEGIN
/*!*/;
# at 16170
#181222 14:40:21 server id 1  end_log_pos 16241 CRC32 0x0e8cb1c9 	Table_map: `test`.`group_member_0` mapped to number 108
# at 16241
#181222 14:40:21 server id 1  end_log_pos 16351 CRC32 0x3b600ade 	Write_rows: table id 108 flags: STMT_END_F

BINLOG '
VdwdXBMBAAAARwAAAHE/AAAAAGwAAAAAAAEABHRlc3QADmdyb3VwX21lbWJlcl8wAAwIAwgIDwgB
CAEICAgCAAQQAMmxjA4=
VdwdXB4BAAAAbgAAAN8/AAAAAGwAAAAAAAEAAgAM//8A8MYAAAAAAAAAAgAAAG8AAAAAAAAAewAA
AAAAAAAAAAAAAAAAAAAAAXsAAAAAAAAAAqiqpNRnAQAAqKqk1GcBAACoqqTUZwEAAN4KYDs=
'/*!*/;
### INSERT INTO `test`.`group_member_0`
### SET
###   @1=198 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=0 is_null=0 */
###   @3=111 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @4=123 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @5='' /* VARSTRING(1024) meta=1024 nullable=1 is_null=0 */
###   @6=0 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @8=123 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @9=2 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @10=1545460820648 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @11=1545460820648 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @12=1545460820648 /* LONGINT meta=0 nullable=0 is_null=0 */
# at 16351
#181222 14:40:21 server id 1  end_log_pos 16382 CRC32 0xe1e8cf20 	Xid = 691
COMMIT/*!*/;
# at 16382
#181222 14:40:21 server id 1  end_log_pos 16447 CRC32 0x686073e1 	Anonymous_GTID	last_committed=36	sequence_number=37	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 16447
#181222 14:40:21 server id 1  end_log_pos 16519 CRC32 0x4ead8cf7 	Query	thread_id=60	exec_time=0	error_code=0
SET TIMESTAMP=1545460821/*!*/;
BEGIN
/*!*/;
# at 16519
#181222 14:40:21 server id 1  end_log_pos 16590 CRC32 0x2fb01039 	Table_map: `test`.`group_member_0` mapped to number 108
# at 16590
#181222 14:40:21 server id 1  end_log_pos 16776 CRC32 0xfbb986f2 	Update_rows: table id 108 flags: STMT_END_F

BINLOG '
VdwdXBMBAAAARwAAAM5AAAAAAGwAAAAAAAEABHRlc3QADmdyb3VwX21lbWJlcl8wAAwIAwgIDwgB
CAEICAgCAAQQADkQsC8=
VdwdXB8BAAAAugAAAIhBAAAAAGwAAAAAAAEAAgAM/////wDwxgAAAAAAAAACAAAAbwAAAAAAAAB7
AAAAAAAAAAAAAAAAAAAAAAABewAAAAAAAAACqKqk1GcBAACoqqTUZwEAAKiqpNRnAQAAAPDGAAAA
AAAAAAIAAABvAAAAAAAAAHsAAAAAAAAAAAAAAAAAAAAAAAF7AAAAAAAAAAGtqqTUZwEAAKiqpNRn
AQAAraqk1GcBAADyhrn7
'/*!*/;
### UPDATE `test`.`group_member_0`
### WHERE
###   @1=198 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=0 is_null=0 */
###   @3=111 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @4=123 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @5='' /* VARSTRING(1024) meta=1024 nullable=1 is_null=0 */
###   @6=0 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @8=123 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @9=2 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @10=1545460820648 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @11=1545460820648 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @12=1545460820648 /* LONGINT meta=0 nullable=0 is_null=0 */
### SET
###   @1=198 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=0 is_null=0 */
###   @3=111 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @4=123 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @5='' /* VARSTRING(1024) meta=1024 nullable=1 is_null=0 */
###   @6=0 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @8=123 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @9=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @10=1545460820653 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @11=1545460820648 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @12=1545460820653 /* LONGINT meta=0 nullable=0 is_null=0 */
# at 16776
#181222 14:40:21 server id 1  end_log_pos 16807 CRC32 0x226282d0 	Xid = 692
COMMIT/*!*/;
# at 16807
#181222 14:40:21 server id 1  end_log_pos 16872 CRC32 0x4991ac7b 	Anonymous_GTID	last_committed=37	sequence_number=38	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 16872
#181222 14:40:21 server id 1  end_log_pos 16944 CRC32 0x75c65e75 	Query	thread_id=60	exec_time=0	error_code=0
SET TIMESTAMP=1545460821/*!*/;
BEGIN
/*!*/;
# at 16944
#181222 14:40:21 server id 1  end_log_pos 17015 CRC32 0x22b2e21d 	Table_map: `test`.`group_member_0` mapped to number 108
# at 17015
#181222 14:40:21 server id 1  end_log_pos 17125 CRC32 0xec7902b1 	Write_rows: table id 108 flags: STMT_END_F

BINLOG '
VdwdXBMBAAAARwAAAHdCAAAAAGwAAAAAAAEABHRlc3QADmdyb3VwX21lbWJlcl8wAAwIAwgIDwgB
CAEICAgCAAQQAB3isiI=
VdwdXB4BAAAAbgAAAOVCAAAAAGwAAAAAAAEAAgAM//8A8MgAAAAAAAAAAgAAAG8AAAAAAAAAfAAA
AAAAAAAAAAAAAAAAAAAAAXsAAAAAAAAAAa2qpNRnAQAAraqk1GcBAACtqqTUZwEAALECeew=
'/*!*/;
### INSERT INTO `test`.`group_member_0`
### SET
###   @1=200 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=0 is_null=0 */
###   @3=111 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @4=124 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @5='' /* VARSTRING(1024) meta=1024 nullable=1 is_null=0 */
###   @6=0 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @8=123 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @9=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @10=1545460820653 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @11=1545460820653 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @12=1545460820653 /* LONGINT meta=0 nullable=0 is_null=0 */
# at 17125
#181222 14:40:21 server id 1  end_log_pos 17156 CRC32 0xbce285e4 	Xid = 693
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

SQL1 general_log

1
2
3
4
5
6
7
8
9
10
11

2018-12-22T06:17:14.670209Z        59 Connect   root@localhost on test using TCP/IP
2018-12-22T06:17:14.702121Z        59 Query     /* mysql-connector-java-5.1.43 ( Revision: 1d14b699eff3e6112aaedb1cbe5a151ab81f98f1 ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@have_query_cache AS have_query_cache, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS tx_isolation, @@wait_timeout AS wait_timeout
2018-12-22T06:17:14.736932Z        59 Query     SHOW WARNINGS
2018-12-22T06:17:14.739439Z        59 Query     SELECT @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type
2018-12-22T06:17:14.739820Z        59 Query     SHOW WARNINGS
2018-12-22T06:17:14.758843Z        59 Query     SET NAMES utf8mb4
2018-12-22T06:17:14.759231Z        59 Query     SET character_set_results = NULL
2018-12-22T06:17:14.759699Z        59 Query     SELECT @@session.autocommit
2018-12-22T06:17:14.787615Z        59 Query     select @@session.tx_read_only
2018-12-22T06:17:14.789080Z        59 Query     INSERT INTO group_member_0(app_id, group_id, user_id, status, member_role, inviter_id, group_member_setting, nickname, create_time, update_time, last_join_time) VALUES(2, 111, 121, 1, 2, 121, 0, '', 1545459434025, 1545459434025, 1545459434025) ,(2, 111, 121, 1, 1, 121, 0, '', 1545459434029, 1545459434029, 1545459434029) ,(2, 111, 122, 1, 1, 121, 0, '', 1545459434030, 1545459434030, 1545459434030)  ON DUPLICATE KEY UPDATE status = VALUES(status), member_role = VALUES(member_role), inviter_id = VALUES(inviter_id), group_member_setting = VALUES(group_member_setting), nickname = VALUES(nickname), update_time = VALUES(update_time), last_join_time = VALUES(last_join_time)
2018-12-22T06:17:14.820540Z        59 Quit

SQL2 general_log

1
2
3
4
5
6
7
8
9
10
11
12
13

2018-12-22T06:40:21.263072Z        60 Connect   root@localhost on test using TCP/IP
2018-12-22T06:40:21.275264Z        60 Query     /* mysql-connector-java-5.1.43 ( Revision: 1d14b699eff3e6112aaedb1cbe5a151ab81f98f1 ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@have_query_cache AS have_query_cache, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS tx_isolation, @@wait_timeout AS wait_timeout
2018-12-22T06:40:21.327249Z        60 Query     SHOW WARNINGS
2018-12-22T06:40:21.329548Z        60 Query     SELECT @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type
2018-12-22T06:40:21.331454Z        60 Query     SHOW WARNINGS
2018-12-22T06:40:21.338302Z        60 Query     SET NAMES utf8mb4
2018-12-22T06:40:21.338637Z        60 Query     SET character_set_results = NULL
2018-12-22T06:40:21.339027Z        60 Query     SELECT @@session.autocommit
2018-12-22T06:40:21.364224Z        60 Query     select @@session.tx_read_only
2018-12-22T06:40:21.364713Z        60 Query     INSERT INTO group_member_0(`app_id`,`group_id`,`user_id`,`status`,`member_role`,`inviter_id`,`group_member_setting`,`nickname`, `create_time`,`update_time`,`last_join_time`) VALUES(2, 111,123,1,2,123,0,'', 1545460820648,1545460820648,1545460820648)  ON DUPLICATE KEY UPDATE `status`=1,`member_role` =2, `inviter_id`=123,`group_member_setting`=0,`nickname`='', `update_time`=1545460820648,`last_join_time`=1545460820648
2018-12-22T06:40:21.404492Z        60 Query     INSERT INTO group_member_0(`app_id`,`group_id`,`user_id`,`status`,`member_role`,`inviter_id`,`group_member_setting`,`nickname`, `create_time`,`update_time`,`last_join_time`) VALUES(2, 111,123,1,1,123,0,'', 1545460820653,1545460820653,1545460820653)  ON DUPLICATE KEY UPDATE `status`=1,`member_role` =1, `inviter_id`=123,`group_member_setting`=0,`nickname`='', `update_time`=1545460820653,`last_join_time`=1545460820653
2018-12-22T06:40:21.421137Z        60 Query     INSERT INTO group_member_0(`app_id`,`group_id`,`user_id`,`status`,`member_role`,`inviter_id`,`group_member_setting`,`nickname`, `create_time`,`update_time`,`last_join_time`) VALUES(2, 111,124,1,1,123,0,'', 1545460820653,1545460820653,1545460820653)  ON DUPLICATE KEY UPDATE `status`=1,`member_role` =1, `inviter_id`=123,`group_member_setting`=0,`nickname`='', `update_time`=1545460820653,`last_join_time`=1545460820653
2018-12-22T06:40:21.438568Z        60 Quit

总结

  • 可以通过上面的BINLOG和general_log看到按SQL1的写法在MySQL内部执行时确实使用了一个事务来执行多条SQL，而按SQL2的写法则最终是每条记录各种执行了一个单独的事务。
  • 对于VALUES()函数在批量提交时可以很明显的降低数据库执行的事务的并发进而降低数据库执行的负载，提高性能。
  • batchUpdate()方法虽然可以保证发送给MySQL的语句和结果是发生在一个网络调用中的，但是数据库的执行性能却大不相同。
  • 默认情况下SELECT @@session.autocommit返回值为1，即开启了自动提交，而由于我们使用的是writer库，所以select @@session .tx_read_only返回值为0，即不是read_only模式。

手动模拟原子插入

除了使用程序进行测试之外，我们还可以通过命令行的方式进行测试，将三条更新操作写成一条SQL的方式，使用了VALUES(), (),..() 这样的语法并且在ON-DUPLICATE-KEY-UPDATE中也使用了VALUES()函数。

1
2
3
4
5
6
7

mysql> INSERT INTO group_member_0(app_id, group_id, user_id, status, member_role, inviter_id, group_member_setting, nickname, create_time, update_time, last_join_time) 
VALUES
(2, 111, 115, 1, 1, 101, 0, '', 1545189948076, 1545189948076, 1545189948076), 
(2, 111, 115, 1, 1, 101, 0, '', 1545189948078, 1545189948076, 1545189948078), 
(2, 111, 116, 1, 1, 101, 0, '', 1545189948079, 1545189948079, 1545189948079) 
ON DUPLICATE KEY UPDATE status = VALUES(status), member_role = VALUES(member_role), inviter_id = VALUES(inviter_id), 
group_member_setting = VALUES(group_member_setting), nickname = VALUES(nickname), update_time = VALUES(update_time), last_join_time = VALUES(last_join_time);

BINLOG

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115

#181221 11:59:33 server id 1  end_log_pos 12539 CRC32 0x0ea661dd 	Anonymous_GTID	last_committed=28	sequence_number=29	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 12539
#181221 11:59:33 server id 1  end_log_pos 12611 CRC32 0x5e5088d8 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1545364773/*!*/;
BEGIN
/*!*/;
# at 12611
#181221 11:59:33 server id 1  end_log_pos 12682 CRC32 0xf5fcda04 	Table_map: `test`.`group_member_0` mapped to number 108
# at 12682
#181221 11:59:33 server id 1  end_log_pos 13164 CRC32 0x9ca09cf3 	Update_rows: table id 108 flags: STMT_END_F

BINLOG '
JWUcXBMBAAAARwAAAIoxAAAAAGwAAAAAAAEABHRlc3QADmdyb3VwX21lbWJlcl8wAAwIAwgIDwgB
CAEICAgCAAQQAATa/PU=
JWUcXB8BAAAA4gEAAGwzAAAAAGwAAAAAAAEAAgAM/////wDwogAAAAAAAAACAAAAbwAAAAAAAABz
AAAAAAAAAAAAAAAAAAAAAAABcwAAAAAAAAABXyzpzmcBAAAWT53OZwEAAF8s6c5nAQAAAPCiAAAA
AAAAAAIAAABvAAAAAAAAAHMAAAAAAAAAAAAAAAAAAAAAAAFlAAAAAAAAAAGsen/EZwEAABZPnc5n
AQAArHp/xGcBAAAA8KIAAAAAAAAAAgAAAG8AAAAAAAAAcwAAAAAAAAAAAAAAAAAAAAAAAWUAAAAA
AAAAAax6f8RnAQAAFk+dzmcBAACsen/EZwEAAADwogAAAAAAAAACAAAAbwAAAAAAAABzAAAAAAAA
AAAAAAAAAAAAAAABZQAAAAAAAAABrnp/xGcBAAAWT53OZwEAAKx6f8RnAQAAAPCkAAAAAAAAAAIA
AABvAAAAAAAAAHQAAAAAAAAAAAAAAAAAAAAAAAFzAAAAAAAAAAHCg+jOZwEAABlPnc5nAQAAwoPo
zmcBAAAA8KQAAAAAAAAAAgAAAG8AAAAAAAAAdAAAAAAAAAAAAAAAAAAAAAAAAWUAAAAAAAAAAa96
f8RnAQAAGU+dzmcBAACven/EZwEAAPOcoJw=
'/*!*/;
### UPDATE `test`.`group_member_0`
### WHERE
###   @1=162 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=0 is_null=0 */
###   @3=111 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @4=115 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @5='' /* VARSTRING(1024) meta=1024 nullable=1 is_null=0 */
###   @6=0 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @8=115 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @9=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @10=1545364647007 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @11=1545359675158 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @12=1545364647007 /* LONGINT meta=0 nullable=0 is_null=0 */
### SET
###   @1=162 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=0 is_null=0 */
###   @3=111 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @4=115 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @5='' /* VARSTRING(1024) meta=1024 nullable=1 is_null=0 */
###   @6=0 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @8=101 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @9=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @10=1545189948076 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @11=1545359675158 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @12=1545189948076 /* LONGINT meta=0 nullable=0 is_null=0 */
### UPDATE `test`.`group_member_0`
### WHERE
###   @1=162 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=0 is_null=0 */
###   @3=111 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @4=115 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @5='' /* VARSTRING(1024) meta=1024 nullable=1 is_null=0 */
###   @6=0 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @8=101 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @9=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @10=1545189948076 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @11=1545359675158 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @12=1545189948076 /* LONGINT meta=0 nullable=0 is_null=0 */
### SET
###   @1=162 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=0 is_null=0 */
###   @3=111 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @4=115 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @5='' /* VARSTRING(1024) meta=1024 nullable=1 is_null=0 */
###   @6=0 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @8=101 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @9=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @10=1545189948078 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @11=1545359675158 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @12=1545189948076 /* LONGINT meta=0 nullable=0 is_null=0 */
### UPDATE `test`.`group_member_0`
### WHERE
###   @1=164 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=0 is_null=0 */
###   @3=111 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @4=116 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @5='' /* VARSTRING(1024) meta=1024 nullable=1 is_null=0 */
###   @6=0 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @8=115 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @9=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @10=1545364603842 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @11=1545359675161 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @12=1545364603842 /* LONGINT meta=0 nullable=0 is_null=0 */
### SET
###   @1=164 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=0 is_null=0 */
###   @3=111 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @4=116 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @5='' /* VARSTRING(1024) meta=1024 nullable=1 is_null=0 */
###   @6=0 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @8=101 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @9=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @10=1545189948079 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @11=1545359675161 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @12=1545189948079 /* LONGINT meta=0 nullable=0 is_null=0 */
# at 13164
#181221 11:59:33 server id 1  end_log_pos 13195 CRC32 0xea60a142 	Xid = 620
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

可以看到默认将多个记录拼成一条SQL发给server端执行，MySQL在执行时会用事务机制来保证全部记录插入的原子性，但是底层仍然是通过三条独立的SQL来执行。

general_log

1
2
3

2018-12-21T03:59:33.365923Z         2 Query     INSERT INTO group_member_0(app_id, group_id, user_id, status, member_role, inviter_id, group_member_setting, nickname, create_time, update_time, last_join_time) 
VALUES(2, 111, 115, 1, 1, 101, 0, '', 1545189948076, 1545189948076, 1545189948076), (2, 111, 115, 1, 1, 101, 0, '', 1545189948078, 1545189948076, 1545189948078), (2, 111, 116, 1, 1, 101, 0, '', 1545189948079, 1545189948079, 1545189948079) 
ON DUPLICATE KEY UPDATE status = VALUES(status), member_role = VALUES(member_role), inviter_id = VALUES(inviter_id), group_member_setting = VALUES(group_member_setting), nickname = VALUES(nickname), update_time = VALUES(update_time), last_join_time = VALUES(last_join_time)

这里和BINGLOG里有一点出入是因为general_log并不是真正执行时候的样子，而是收到SQL的样子，所以可能感觉是执行了一条，其实仍然是三条只不过通过事务保证了原子性。

How-to

  • 查看binlog的命令：SHOW MASTER LOGS
  • 打开general_log开关的命令：SET GLOBAL general_log=ON
  • 查看general_log信息的命令：SHOW VARIABLES LIKE ‘general%’

本文首次发布于 LiuShuo’s Blog, 转载请保留原文链接.

CATALOG
    FEATURED TAGS
    Patent 快手 IM 专利证书 消息 Java 会话 小米 MySQL Algorithm 群聊 安全 社交 交互 数据处理 表情 Mobile Redis Spring TroubleShooting 移动端 Design IDX Site 产品创意 分享 图片交互 图片处理 美国 群 评论 ElasticSearch RateLimit Search Streaming 信号 欧洲 资源 长连接 Encoding Generics Github HBase Messaging Python TCP Talk Travelling Troubleshooting Web 下载 俄罗斯 关系 多媒体 头像 安装包 弹幕 推送 搜索 数据下发 数据传输 数据同步 数据存储 数据展示 文件 日本 流控 测试 游戏 爬虫 监控 自动回复 转码 配置管理 验证码 AOP BinarySearch CAP CAS Caching CompletableFuture ConcurrentHashMap DNS DNSSEC Emoji Flask Gitalk Gliffy How-to IP IXD Iteration JVM LazyEvaluation Linux MacOS Maven Mock Nginx Numpy OOM OS Optimization Reduction Resilience4j SkipList Slide Sorting SpringBoot SpringMVC Synchronization TLD Testing ThreadLocal Timeout Tomcat Transaction Translation Video Wireshark ZipList