如何高效同步主库到子库：仅更新变更数据并插入指定新增数据

核心思路 #

利用MySQL原生的INSERT ... ON DUPLICATE KEY UPDATE语法，结合子库的专属筛选条件，单条SQL即可完成新增数据插入+已有数据更新的操作，彻底规避PHP循环对比带来的跨进程IO性能损耗；针对不同子库的差异化数据需求，编写对应筛选规则的SQL批量执行即可。

具体实现步骤 #

1. 前置条件：确保表存在唯一主键/唯一索引 #

ON DUPLICATE KEY UPDATE依赖唯一键判断数据是否已存在，因此必须保证papers表有唯一主键（比如id）或唯一索引，这是语法生效的核心前提。

2. 单张子库的同步SQL #

假设子库child_1仅需要主库中category_id = 1的数据，同步SQL如下：

INSERT INTO child_1.papers
SELECT * FROM master_db.papers
WHERE category_id = 1 -- 替换为当前子库的筛选条件
ON DUPLICATE KEY UPDATE
  -- 明确列出需要同步的字段，避免表结构变更时出现异常
  title = VALUES(title),
  content = VALUES(content),
  update_time = VALUES(update_time),
  views = VALUES(views);

如果需要同步所有字段（主键除外，主键用于判断重复），也可以用动态SQL生成字段列表，但生产环境建议手动指定字段，避免隐式问题。

3. 多子库批量执行 #

由于所有库在同一服务器，无需跨节点连接，直接在同一个MySQL会话中依次执行各子库的同步SQL即可。例如3个不同筛选规则的子库：

-- 同步child_1：仅同步分类ID为1的数据
INSERT INTO child_1.papers SELECT * FROM master_db.papers WHERE category_id=1 ON DUPLICATE KEY UPDATE title=VALUES(title), content=VALUES(content), update_time=VALUES(update_time);

-- 同步child_2：仅同步已发布状态的数据
INSERT INTO child_2.papers SELECT * FROM master_db.papers WHERE status='published' ON DUPLICATE KEY UPDATE title=VALUES(title), content=VALUES(content), update_time=VALUES(update_time);

-- 同步child_3：仅同步指定作者的数据
INSERT INTO child_3.papers SELECT * FROM master_db.papers WHERE author_id IN (10,20,30) ON DUPLICATE KEY UPDATE title=VALUES(title), content=VALUES(content), update_time=VALUES(update_time);

4. 性能优化建议 #

• 关闭自动提交：执行同步前先运行SET autocommit=0;，全部同步完成后执行COMMIT;，减少事务提交的开销。
• 临时禁用索引：如果子库数据量较大，同步前执行ALTER TABLE child_x.papers DISABLE KEYS;，同步完成后执行ALTER TABLE child_x.papers ENABLE KEYS;，避免每一次插入/更新都维护索引。
• 清理冗余数据：如果子库需要同步主库的删除操作，可单独执行清理SQL（用LEFT JOIN替代NOT IN避免性能问题）：

DELETE c FROM child_1.papers c
LEFT JOIN master_db.papers m ON c.id = m.id AND m.category_id=1
WHERE m.id IS NULL;

关于之前UPDATE尝试失败的说明 #

直接用UPDATE关联主库需要正确的关联逻辑，示例写法如下：

UPDATE child_1.papers c
JOIN master_db.papers m ON c.id = m.id
SET c.title = m.title, c.content = m.content
WHERE m.category_id=1;

但这种写法仅能更新已存在的数据，无法处理新增数据，因此需要配合INSERT语句使用，而INSERT ... ON DUPLICATE KEY UPDATE是更简洁的合并方案。

内容的提问来源于stack exchange，提问作者Kikloo