sql线程应用中继日志，在binlog_format是row格式的时候，是居于主键更新，下面结合一张图来证明
从一个大神那边得到一张图片，sql线程应用中继日志流程，下面就实验验证一下:(ps,我个人认为这张图binlog_format为row格式是正确的)
2.验证有pk表情况
在主库创建表结构
create table `table_pk` (
`id` int(11) not null ,
`name` varchar(20) not null ,
`age` tinyint not null ,
`sex` tinyint not null comment '0,man,1,woman' ,
primary key (`id`)
) engine=innodb;
插入测试数据
insert into table_pk (`id`,`name`,`age`,`sex`) values(111,'zhangsan',20,0);
insert into table_pk (`id`,`name`,`age`,`sex`) values(222,'lisi',22,1);
insert into table_pk (`id`,`name`,`age`,`sex`) values(333,'wangwu',22,1);
insert into table_pk (`id`,`name`,`age`,`sex`) values(444,'lilei',32,0);
insert into table_pk (`id`,`name`,`age`,`sex`) values(555,'hanmeimei',30,1);
(dg6)root@localhost [(none)]> use mytest;
(dg6)root@localhost [mytest]> select * from table_pk;
+-----+-----------+-----+-----+
| id | name | age | sex |
| 111 | zhangsan | 20 | 0 |
| 222 | lisi | 22 | 1 |
| 333 | wangwu | 22 | 1 |
| 444 | lilei | 32 | 0 |
| 555 | hanmeimei | 30 | 1 |
rows in set (0.00 sec)
(dg6)root@localhost [mytest]> show global variables like '%binlog_format%';
+---------------+-------+
| variable_name | value |
| binlog_format | row |
row in set (0.00 sec)
那么我们去从库看看
(dg7)root@localhost [mytest]> select * from table_pk;
(dg7)root@localhost [mytest]> show global variables like '%binlog_format%';
+--------------------------+-------------------+
| variable_name | value |
| binlog_format | row |
(dg7)root@localhost [mytest]> show slave status\g
*************************** 1. row ***************************
slave_io_state: waiting for master to send event
master_host: 192.168.80.106
master_user: repl
master_port: 3306
connect_retry: 60
master_log_file: dg6-logbin.000001
read_master_log_pos: 4469
relay_log_file: dg7-relay-bin.000002
relay_log_pos: 4681
relay_master_log_file: dg6-logbin.000001
slave_io_running: yes
slave_sql_running: yes
replicate_do_db:
replicate_ignore_db: mysql
replicate_do_table:
replicate_ignore_table:
replicate_wild_do_table:
replicate_wild_ignore_table:
last_errno: 0
last_error:
skip_counter: 0
exec_master_log_pos: 4469
relay_log_space: 4883
until_condition: none
until_log_file:
until_log_pos: 0
master_ssl_allowed: no
master_ssl_ca_file:
master_ssl_ca_path:
master_ssl_cert:
master_ssl_cipher:
master_ssl_key:
seconds_behind_master: 0
master_ssl_verify_server_cert: no
last_io_errno: 0
last_io_error:
last_sql_errno: 0
last_sql_error:
replicate_ignore_server_ids:
master_server_id: 1
master_uuid: b888e1ea-9739-11e4-a24e-000c29b24887
master_info_file: /data/mydata/master.info
sql_delay: 0
sql_remaining_delay: null
slave_sql_running_state: slave has read all relay log; waiting for the slave i/o thread to update it
master_retry_count: 86400
master_bind:
last_io_error_timestamp:
last_sql_error_timestamp:
master_ssl_crl:
master_ssl_crlpath:
retrieved_gtid_set: b888e1ea-9739-11e4-a24e-000c29b24887:1-17
executed_gtid_set: a9926b45-975d-11e4-a339-000c29b24888:1-9,
b888e1ea-9739-11e4-a24e-000c29b24887:1-17
auto_position: 1
数据是复制过来的，mysql主从复制是正常的，那么我们为了验证mysql复制sql线程是居于刚才那张图的流程，有主键，就按主键更新匹配更新记录。
那么我们在从库修改一行数据，故意制造不一致。
(dg7)root@localhost [mytest]> update `table_pk` set `name`='laowang' where `id`=333；
query ok, 0 rows affected (0.00 sec)
rows matched: 1 changed: 0 warnings: 0
| 333 | laowang | 22 | 1 |
这时候主从数据不一致了
主库
(dg6)root@localhost [mytest]> select * from table_pk where id=333;
+-----+--------+-----+-----+
| id | name | age | sex |
| 333 | wangwu | 22 | 1 |
从库
(dg7)root@localhost [mytest]> select * from table_pk where id=333;
+-----+---------+-----+-----+
| id | name | age | sex |
| 333 | laowang | 22 | 1 |
(dg7)root@localhost [mytest]>
那么，我们在主库更新一行数据。
(dg6)root@localhost [mytest]> update `table_pk` set `name`='wangzi' where `id`=333;
query ok, 1 row affected (0.01 sec)
rows matched: 1 changed: 1 warnings: 0
| 333 | wangzi | 22 | 1 |
我们来看一下从库状态，是不是主库的更新给复制过来了，见证奇迹的时候到了
###############################################
######################### 神奇的是主库的更新过来了#############################################
#########################那么看一下mysql主从复制状态看看，也是正常的######################
read_master_log_pos: 5249
relay_log_pos: 5461
exec_master_log_pos: 5249
relay_log_space: 5663
retrieved_gtid_set: b888e1ea-9739-11e4-a24e-000c29b24887:1-20
executed_gtid_set: a9926b45-975d-11e4-a339-000c29b24888:1-11,
b888e1ea-9739-11e4-a24e-000c29b24887:1-20
3.验证没有索引的情况
主库创建表和插入记录
create table `table_index` (
`id` int(11) not null,
`name` varchar(20) not null,
`age` tinyint(4) not null,
`sex` tinyint(4) not null comment '0,man,1,woman'
) engine=innodb
(dg6)root@localhost [mytest]> select * from table_index;
(dg6)root@localhost [mytest]>
从库看看
(dg7)root@localhost [mytest]> select * from table_index;
我们在从库继续搞破坏，把name为lisi的age修改为33，这时候主从已经不一致了。
(dg7)root@localhost [mytest]> select * from table_index where name='lisi';
+-----+------+-----+-----+
| id | name | age | sex |
| 222 | lisi | 22 | 1 |
(dg7)root@localhost [mytest]> update table_index set age=33 where name='lisi';
| 222 | lisi | 33 | 1 |
那么我们还是在主库更新一下记录。把lisi的age修改成30，看看从库能不能更新过来
(dg6)root@localhost [mytest]> select * from table_index where name='lisi';
(dg6)root@localhost [mytest]> update table_index set age=30 where name='lisi';
| 222 | lisi | 30 | 1 |
回到从库看看，数据没有更新过来，lisi的年龄还是33，这时候主从复制也是异常的,提示1032错误（找不到记录）
| 222 | lisi | 33 | 1 |
read_master_log_pos: 7376
relay_log_file: dg7-relay-bin.000003
relay_log_pos: 724
slave_sql_running: no
last_errno: 1032
last_error: could not execute update_rows event on table mytest.table_index; can't find record in 'table_index', error_code: 1032; corrupted replication event was detected, error_code: 1610; handler error ha_err_end_of_file; the event's master log dg6-logbin.000001, end_log_pos 7345
exec_master_log_pos: 7112
relay_log_space: 8090
seconds_behind_master: null
last_sql_errno: 1032
last_sql_error: could not execute update_rows event on table mytest.table_index; can't find record in 'table_index', error_code: 1032; corrupted replication event was detected, error_code: 1610; handler error ha_err_end_of_file; the event's master log dg6-logbin.000001, end_log_pos 7345
slave_sql_running_state:
last_sql_error_timestamp: 150425 08:30:49
retrieved_gtid_set: b888e1ea-9739-11e4-a24e-000c29b24887:1-28
executed_gtid_set: a9926b45-975d-11e4-a339-000c29b24888:1-14,
b888e1ea-9739-11e4-a24e-000c29b24887:1-27
4.验证有唯一索引情况
测试方法都一样，下面步骤我都就贴结果了。（核心思想就是，从库先修改记录，做成主从数据不一致这种情况，然后主库再更新，看看从库有没有同步主库记录）
+-----+-----+-----------+-----+-----+
| id | sid | name | age | sex |
| 111 | 1 | zhangsan | 20 | 0 |
| 222 | 2 | lisi | 30 | 1 |
| 333 | 3 | wangzi | 22 | 1 |
| 444 | 4 | lilei | 32 | 0 |
| 555 | 5 | hanmeimei | 30 | 1 |
(dg6)root@localhost [mytest]> select * from table_index where sid=3;
+-----+-----+--------+-----+-----+
| id | sid | name | age | sex |
| 333 | 3 | wangzi | 22 | 1 |
(dg6)root@localhost [mytest]> update table_index set name='wangwu' where sid=3;
query ok, 1 row affected (0.00 sec)
| 333 | 3 | wangwu | 22 | 1 |
从库看看，能更新过来，而且主从复制状态是正常的
(dg7)root@localhost [mytest]> update table_index set name='laowang' where sid=3;
(dg7)root@localhost [mytest]> select * from table_index where sid=3;
+-----+-----+---------+-----+-----+
| id | sid | name | age | sex |
| 333 | 3 | laowang | 22 | 1 |
read_master_log_pos: 13038
relay_log_file: dg7-relay-bin.000005
relay_log_pos: 5841
exec_master_log_pos: 13038
relay_log_space: 6615
retrieved_gtid_set: b888e1ea-9739-11e4-a24e-000c29b24887:1-52
executed_gtid_set: a9926b45-975d-11e4-a339-000c29b24888:1-26,
b888e1ea-9739-11e4-a24e-000c29b24887:1-52
read_master_log_pos: 13302
relay_log_pos: 6105
exec_master_log_pos: 13302
relay_log_space: 6879
retrieved_gtid_set: b888e1ea-9739-11e4-a24e-000c29b24887:1-53
b888e1ea-9739-11e4-a24e-000c29b24887:1-53
5.验证有主键和有普通索引情况
(dg6)root@localhost [mytest]> select * from table_key;
| 666 | lucy | 30 | 1 |
| 777 | lili | 30 | 1 |
| 888 | lintao | 32 | 0 |
(dg6)root@localhost [mytest]> update table_key set name='zhangsir' where age=20;
| 111 | zhangsir | 20 | 0 |
(dg7)root@localhost [mytest]> select * from table_key;
(dg7)root@localhost [mytest]> desc update table_key set name='xiaozhang' where age=20;
+----+-------------+-----------+-------+---------------+-----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |
| 1 | simple | table_key | range | age_index | age_index | 1 | const | 1 | using where |
(dg7)root@localhost [mytest]> update table_key set name='xiaozhang' where age=20;
query ok, 1 row affected (0.03 sec)
| 111 | xiaozhang | 20 | 0 |
read_master_log_pos: 16026
relay_log_pos: 8829
exec_master_log_pos: 16026
relay_log_space: 9603
retrieved_gtid_set: b888e1ea-9739-11e4-a24e-000c29b24887:1-63
executed_gtid_set: a9926b45-975d-11e4-a339-000c29b24888:1-28,
b888e1ea-9739-11e4-a24e-000c29b24887:1-63
6.验证只有普通索引情况
`sex` tinyint(4) not null comment '0,man,1,woman',
key age_index (`age`)
(dg6)root@localhost [mytest]>select * from table_key where age=20;
+-----+----------+-----+-----+
| id | name | age | sex |
| 111 | zhangsir | 20 | 0 |
(dg6)root@localhost [mytest]>update table_key set name='zhaoliu' where age=20;
| 111 | zhaoliu | 20 | 0 |
(dg7)root@localhost [mytest]> select * from table_key where age=20;
(dg7)root@localhost [mytest]> update table_key set name='zhangsan' where age=20;
| 111 | zhangsan | 20 | 0 |
##########################提示1032错误，找不到记录
read_master_log_pos: 16463
relay_log_pos: 8993
last_error: could not execute update_rows event on table mytest.table_key; can't find record in 'table_key', error_code: 1032; column 'name' cannot be null, error_code: 1048; column 'age' cannot be null, error_code: 1048; column 'sex' cannot be null, error_code: 1048; handler error ha_err_end_of_file; the event's master log dg6-logbin.000001, end_log_pos 16432
exec_master_log_pos: 16190
relay_log_space: 10040
last_sql_error: could not execute update_rows event on table mytest.table_key; can't find record in 'table_key', error_code: 1032; column 'name' cannot be null, error_code: 1048; column 'age' cannot be null, error_code: 1048; column 'sex' cannot be null, error_code: 1048; handler error ha_err_end_of_file; the event's master log dg6-logbin.000001, end_log_pos 16432
last_sql_error_timestamp: 150425 09:43:27
retrieved_gtid_set: b888e1ea-9739-11e4-a24e-000c29b24887:1-65
executed_gtid_set: a9926b45-975d-11e4-a339-000c29b24888:1-29,
b888e1ea-9739-11e4-a24e-000c29b24887:1-64
7.binlog格式是sbr,mbr格式的时候（ps，因为我使用了gtid，所以找了另外两台机测试）
(dg1)[email protected] [mytest]> select * from table_key;
(dg1)[email protected] [mytest]> show global variables like '%binlog_format%';
| binlog_format | mixed |
(dg1)[email protected] [mytest]> update table_key set name='zhangzong' where age=20;
从库看一下
(dg2)[email protected] [mytest]> select * from table_key where age=20;
row in set (0.01 sec)
(dg2)[email protected] [mytest]> update table_key set name='zhangsir' where age=20;
| 111 | zhangzong | 20 | 0 |
(dg2)[email protected] [mytest]> show slave status\g
master_host: 192.168.80.101
master_port: 3307
master_log_file: dg1.000001
read_master_log_pos: 3340
relay_log_file: mysql3307-relay-bin.000002
relay_log_pos: 3355
relay_master_log_file: dg1.000001
replicate_ignore_db:
exec_master_log_pos: 3340
relay_log_space: 3532
master_server_id: 12
master_uuid: fbc1bdf1-829b-11e4-9bdf-000c29b24882
master_info_file: /data/3307/master.info
retrieved_gtid_set:
executed_gtid_set:
auto_position: 0
(dg2)[email protected]
<b>本文来自云栖社区合作伙伴“dbgeek”</b>
![SQL优化SQL语句优化的目的[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)