前言：
今天遇到主从表不一致的情况，很奇怪为什么会出现不一致的情况，因为复制状态一直都是正常的。最后检查出现不一致的数据都是主键，原来是当时初始化数据的时候导致的。现在分析记录下这个问题，避免以后再遇到这个"坑"。
背景：
主从服务器，MIXED复制模式。
分析：
表：SPU
Table: SPU
Create Table: CREATE TABLE `SPU` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`trademark` varchar(255) NOT NULL COMMENT '品牌',
`item_code` varchar(255) NOT NULL COMMENT '货号',
`product_id` int(10) DEFAULT '',
PRIMARY KEY (`id`),
KEY `trademark` (`trademark`),
KEY `item_code` (`item_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='SPU'
当时的初始化操作的SQL：
INSERT INTO SPU(trademark, item_code)
SELECT * FROM
(
SELECT distinct lower(TRIM(a.`value`)) col1, lower(TRIM(b.`value`)) col2
FROM
product_property a LEFT JOIN product_property b
ON
a.product_id = b.product_id
WHERE
a.property_id = 14 AND b.property_id = 9 AND a.value IS NOT NULL AND a.value <> ''
AND
b.value IS NOT NULL AND b.value <> ''
) as aa
上面的SQL执行完之后，主从表的COUNT数量一样，但SPU表有个自增主键，就在这里出现主从插入SPU的顺序不一样，即主从SELECT出来的结果顺序不一样。由于上面的结果集很大，所以就取前10条记录看看：
主：
现在表中数据的顺序：
[email protected] : tt 04:38:48>select id,trademark col1,item_code col2 from SPU limit 10;
+----+-----------+-------------+
| id | col1 | col2 |
+----+-----------+-------------+
| 1 | 鼓浪屿 | gd rg |
| 2 | 山松 | 10020122 |
| 3 | coulter | 无 |
| 4 | oricell | mubmd-01101 |
| 5 | oricell | huxma-01101 |
| 6 | oricell | huxmf-01001 |
| 7 | oricell | rawmx-01001 |
| 8 | oricell | rasmx-01001 |
| 9 | oricell | rafmx-01101 |
| 10 | oricell | rbxmx-01001 |
+----+-----------+-------------+
10 rows in set (0.01 sec) 当时初始化的sql读出数据的顺序：
[email protected] : tt 04:40:25>SELECT distinct lower(TRIM(a.`value`)) col1, lower(TRIM(b.`value`)) col2
-> FROM
-> product_property a LEFT JOIN product_property b
-> ON
-> a.product_id = b.product_id
-> WHERE
-> a.property_id = 14 AND b.property_id = 9 AND a.value IS NOT NULL AND a.value <> ''
-> AND
-> b.value IS NOT NULL AND b.value <> '' limit 10;
+-----------+-------------+
| col1 | col2 |
+-----------+-------------+
| 鼓浪屿 | gd rg |
| 山松 | 10020122 |
| coulter | 无 |
| oricell | mubmd-01101 |
| oricell | huxma-01101 |
| oricell | huxmf-01001 |
| oricell | rawmx-01001 |
| oricell | rasmx-01001 |
| oricell | rafmx-01101 |
| oricell | rbxmx-01001 |
+-----------+-------------+
10 rows in set (0.01 sec)
上面结果col1,col2的顺序一模一样。
从：
现在表中数据的顺序：
[email protected] : tt 04:38:03>select id,trademark col1,item_code col2 from SPU limit 10;
+----+--------------------------------+-----------------+
| id | col1 | col2 |
+----+--------------------------------+-----------------+
| 1 | bio-rad | 125-0140 |
| 2 | oricell(tm) | huxma-90011 |
| 3 | oricell(tm) | huxmf-90011 |
| 4 | oricell(tm) | rawmx-90011 |
| 5 | oricell | rasmx-90011 |
| 6 | oricell | rafmx-90011 |
| 7 | oricell | rbxmx-90011 |
| 8 | 上海科技有限公司 | bsm03011 |
| 9 | oricell | caxmx-90011 |
| 10 | oricell(tm) | tedta-10001-100 |
+----+--------------------------------+-----------------+
10 rows in set (0.00 sec) 当时初始化的sql读出数据的顺序：
[email protected] : tt 04:38:58>SELECT distinct lower(TRIM(a.`value`)) col1, lower(TRIM(b.`value`)) col2
-> FROM
-> product_property a LEFT JOIN product_property b
-> ON
-> a.product_id = b.product_id
-> WHERE
-> a.property_id = 14 AND b.property_id = 9 AND a.value IS NOT NULL AND a.value <> ''
-> AND
-> b.value IS NOT NULL AND b.value <> '' limit 10;
+--------------------------------+-----------------+
| col1 | col2 |
+--------------------------------+-----------------+
| bio-rad | 125-0140 |
| oricell(tm) | huxma-90011 |
| oricell(tm) | huxmf-90011 |
| oricell(tm) | rawmx-90011 |
| oricell | rasmx-90011 |
| oricell | rafmx-90011 |
| oricell | rbxmx-90011 |
| 上海科技有限公司 | bsm03011 |
| oricell | caxmx-90011 |
| oricell(tm) | tedta-10001-100 |
+--------------------------------+-----------------+
10 rows in set (0.01 sec)
上面结果col1,col2的顺序一模一样。
到此为止，大家就清楚为什么SPU表的数据不一致了，准确来说是主键对应的数据不一致。要是自增主键只是提升INNODB的性能，没有业务上的意义，那么对于产品来说是没有影响的，可以忽略这个问题。否则，就需要好好的处理这个问题了。从另一个方面来说，也是因为复制的模式是STATEMENT引发这个问题的，因为同一个QUERY在2个地方执行出的结果不一样；要是ROW的复制模式，主会把所有字段的记录全部传送给从，就不会出现这个问题。
进一步分析： 为什么一样的SQL在主从上跑出来的数据顺序不一样呢？
通过EXPLAIN 看到主上的QUERY 先读 b表，再读a表；而从上的则是先扫描a表，再读b表。出现这样的情况，就是数据在块里面分布不一致，导致索引利用的方式也不一样，最终影响优化器的选择。因为INNODB是索引组织表的，一旦走的索引不一样，就会导致数据以不同的顺序被扫描出来。上面的这些结果刚好被验证。SQL执行计划如下：
主：先b再a
[email protected] : tt 09:18:04>explain SELECT distinct lower(TRIM(a.`value`)) col1, lower(TRIM(b.`value`)) col2 FROM product_property a LEFT JOIN product_property b ON a.product_id = b.product_id WHERE a.property_id = 14 AND b.property_id = 9 AND a.value IS NOT NULL AND a.value <> '' AND b.value IS NOT NULL AND b.value <> '';
+----+-------------+-------+------+------------------------+-------------+---------+----------------------+---------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+------------------------+-------------+---------+----------------------+---------+------------------------------+
| 1 | SIMPLE | b | ref | property_id,product_id | property_id | 4 | const | 4145932 | Using where; Using temporary |
| 1 | SIMPLE | a | ref | property_id,product_id | product_id | 4 | tt.b.product_id | 1 | Using where |
+----+-------------+-------+------+------------------------+-------------+---------+----------------------+---------+------------------------------+
2 rows in set (0.01 sec)
从：先a再b
[email protected] : tt 09:18:13>explain SELECT distinct lower(TRIM(a.`value`)) col1, lower(TRIM(b.`value`)) col2 FROM product_property a LEFT JOIN product_property b ON a.product_id = b.product_id WHERE a.property_id = 14 AND b.property_id = 9 AND a.value IS NOT NULL AND a.value <> '' AND b.value IS NOT NULL AND b.value <> '';
+----+-------------+-------+------+------------------------+-------------+---------+----------------------+----------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+------------------------+-------------+---------+----------------------+----------+------------------------------+
| 1 | SIMPLE | a | ref | property_id,product_id | property_id | 4 | const | 17079464 | Using where; Using temporary |
| 1 | SIMPLE | b | ref | property_id,product_id | product_id | 4 | tt.a.product_id | 5 | Using where |
+----+-------------+-------+------+------------------------+-------------+---------+----------------------+----------+------------------------------+
2 rows in set (0.13 sec)
主从对比发现，他们的执行计划和各表走的索引都不一样，导致最后出来的顺序也不一样的（结果集是一样的），这就验证了分析说的情况。那要是执行计划和索引一致呢？接下来继续验证下：
进一步验证：
因为INNODB是索引组织表的，索引就是数据，要是主从的执行计划一样，则他们的结果会是？
主的执行计划：
[email protected] : tt 05:42:15>explain SELECT distinct lower(TRIM(a.`value`)) col1, lower(TRIM(b.`value`)) col2
-> FROM
-> product_property a LEFT JOIN product_property b
-> ON
-> a.product_id = b.product_id
-> WHERE
-> a.property_id = 14 AND b.property_id = 9 AND a.value IS NOT NULL AND a.value <> ''
-> AND
-> b.value IS NOT NULL AND b.value <> '';
+----+-------------+-------+------+------------------------+-------------+---------+----------------------+---------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+------------------------+-------------+---------+----------------------+---------+------------------------------+
| 1 | SIMPLE | b | ref | property_id,product_id | property_id | 4 | const | 3771874 | Using where; Using temporary |
| 1 | SIMPLE | a | ref | property_id,product_id | product_id | 4 | tt.b.product_id | 1 | Using where |
+----+-------------+-------+------+------------------------+-------------+---------+----------------------+---------+------------------------------+
2 rows in set (0.01 sec)
从的执行计划：
[email protected] : tt 05:42:07>explain SELECT distinct lower(TRIM(a.`value`)) col1, lower(TRIM(b.`value`)) col2
-> FROM
-> product_property a LEFT JOIN product_property b
-> ON
-> a.product_id = b.product_id
-> WHERE
-> a.property_id = 14 AND b.property_id = 9 AND a.value IS NOT NULL AND a.value <> ''
-> AND
-> b.value IS NOT NULL AND b.value <> '';
+----+-------------+-------+------+------------------------+-------------+---------+----------------------+----------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+------------------------+-------------+---------+----------------------+----------+------------------------------+
| 1 | SIMPLE | b | ref | property_id,product_id | property_id | 4 | const | 17375012 | Using where; Using temporary |
| 1 | SIMPLE | a | ref | property_id,product_id | product_id | 4 | tt.b.product_id | 5 | Using where |
+----+-------------+-------+------+------------------------+-------------+---------+----------------------+----------+------------------------------+
2 rows in set (0.00 sec)
执行计划一样，都是先b表再a表，再重新执行初始化的SQL：
主：
[email protected] : tt 05:42:26>SELECT distinct lower(TRIM(a.`value`)) col1, lower(TRIM(b.`value`)) col2
-> FROM
-> product_property a LEFT JOIN product_property b
-> ON
-> a.product_id = b.product_id
-> WHERE
-> a.property_id = 14 AND b.property_id = 9 AND a.value IS NOT NULL AND a.value <> ''
-> AND
-> b.value IS NOT NULL AND b.value <> '' limit 10
-> ;
+-----------+-------------+
| col1 | col2 |
+-----------+-------------+
| 鼓浪屿 | gd rg |
| 山松 | 10020122 |
| coulter | 无 |
| oricell | mubmd-01101 |
| oricell | huxma-01101 |
| oricell | huxmf-01001 |
| oricell | rawmx-01001 |
| oricell | rasmx-01001 |
| oricell | rafmx-01101 |
| oricell | rbxmx-01001 |
+-----------+-------------+
10 rows in set (0.00 sec)
从：
[email protected] : tt 05:42:32>SELECT distinct lower(TRIM(a.`value`)) col1, lower(TRIM(b.`value`)) col2
-> FROM
-> product_property a LEFT JOIN product_property b
-> ON
-> a.product_id = b.product_id
-> WHERE
-> a.property_id = 14 AND b.property_id = 9 AND a.value IS NOT NULL AND a.value <> ''
-> AND
-> b.value IS NOT NULL AND b.value <> '' limit 10
-> ;
+-----------+-------------+
| col1 | col2 |
+-----------+-------------+
| 鼓浪屿 | gd rg |
| 山松 | 10020122 |
| coulter | 无 |
| oricell | mubmd-01101 |
| oricell | huxma-01101 |
| oricell | huxmf-01001 |
| oricell | rawmx-01001 |
| oricell | rasmx-01001 |
| oricell | rafmx-01101 |
| oricell | rbxmx-01001 |
+-----------+-------------+
10 rows in set (0.01 sec)
好了，要是执行计划一样，结果是：SQL在主从上跑出来的结果一致了。
PS：另一个方法就是用ROW模式，有兴趣的可以测试下。
总结：
主从复制在STATEMENT下面确实被忽略了一些问题，可以用ROW模式代替，但也要知道ROW模式有哪些问题，可以参考MySQL Binlog 【ROW】和【STATEMENT】选择。也要清楚数据在磁盘块里面分布不一致，影响优化器的选择而导致索引利用的方式也不一样，最终也影响到数据的顺序。
总之，在主从上执行一些比较大的数据量的操作（批量、初始化）的时候，尽可能的先去主从上查看他们的执行计划是否一样，走的索引是否一致，确保查询出来的结果一样。另：这篇文章：blog.xupeng.me/2013/10/11/mysql-replace-into-trap/(MySQL "replace into" 的坑) 也在一定程度上说明别用自增主键当成有意义的数据。