oracle与MySQL数据库之间数据同步的技术要点

1，需求描述

某ORCALE11生产数据库（下称源数据库），内含近万个表，需要从中每日同步几十个表的数据到mySQL5.7数据库（下称目标数据库）中，供第三方使用。

需要对生产数据库影响越小越好。

2，技术挑战

• 数据类型不完全一致。从Oracle中导出的建表语句到MySQL数据库中不一定能运行，因为二者的数据类型有差异，需要调整。
• 更新策略。本文考虑的方案是将数据从源库中提取出来，处理后再写入目标库中。那么，是采用全量更新，还是增量更新？
• 高效可靠。对源库访问的时间应尽可能短，取出的数据量应尽可能少，以减少对源库服务器的性能影响。

3，技术方案

3.1 数据类型转换

 首先，MySQL数据库建库时要指定使用UTF8字符集，然后采用以下数据类型转换：

oracle：varchar2，nvarchar2 --> mySQL: varchar

oracle：cblob --> mySQL: text

oracle：timestamp --> mySQL: datetime

oracle：number --> mySQL: numeric

。。。

以上基本是显而易见的，具体也可自己摸索。

另外还有个字符串是否区分大小写的问题，由于字符串在ORACLE数据库中查询时区分大小写，因此mysql表的ID字段（主键字段）也要严格区分大小写，否则会出现主键字段有重复导致记录无法导入的故障。

具体方法参考如下语句， 

ALTER TABLE table_name MODIFY `FID` VARCHAR(100) BINARY DEFAULT '' NOT NULL;

3.2 数据更新策略

为节省同步时间、减少对源数据库服务器和目标数据库服务器的影响，采用以下策略：

对大表宜采取增量同步方式，对小表采取全量同步方式。

区分大表和小表的目的是采取不同的更新策略。大表、小表的分类是相对的，比如对记录数>5000的表称为大表，否则称为小表；或者表空间>50MB的为大表，等等，看自己的实际情况确定一个标准。

• 增量同步方式是指，通过比对源和目标库表的主键和最后更新时间，来确定发生改变或新增的记录，然后仅将这些记录同步到目标库中。该方式更新量小，适用于大表更新，但前提是执行一次全量同步。
• 全量同步方式是指，同步时先将目标表清空，然后将源表记录全部插入的方式。该方式无需比对记录是否改变，适用于小表更新。

如果是大表，但是表内没有“最后更新时间”字段怎么办呢？那就只能采用全量更新了，但这是不推荐的，建议源表中增加该字段，并创建一个触发器自动更新字段值。

3.3 高效可靠

高效自然就是数据的读写操作要快，那就离不开索引的使用了，下面专门讨论。

可靠主要是要防止单次操作数据量太大造成问题，因此建议采取批次处理的方法。

4，增量更新时如何使用索引

此处专门谈一下大表的增量更新。

如上所述，基本思路就是从源库取出主键（FID）和最后更新时间（FLASTUPDATETIME），插入到目标库的T1表中，并与目标库中T_DST表的数据比对，以确定发生改变或新增的记录，然后再去源库中取出这些记录去目标库中执行增改操作。

以上T1 和T_DST 表都是百万行级别，都有FID作为主键，关键是如何快速比对出差异记录？

笔者一开始采用以下语句：

select a.FID, b.FLASTUPDATETIME as ot, a.FLASTUPDATETIME as nt from T1 a left join T_DST b on a.FID=b.FID where b.FID is null or b.FLASTUPDATETIME<>a.FLASTUPDATETIME;

结果运行速度非常慢，花了12分钟。mysql服务器性能并不差，那么问题就是语句需要优化。

首先采用expain工具解释执行计划，发现执行没有采用T1表的主键索引。

估计是left join的问题，因此将比对记录增改的一条语句改为两条语句， 分别比对记录增加 和比对记录改变 。

比对记录改变：

select a.FID, b.FLASTUPDATETIME as ot, a.FLASTUPDATETIME as nt from T1 a join T_DST b on a.FID=b.FID where b.FLASTUPDATETIME<>a.FLASTUPDATETIME;

比对记录增加：

select a.FID, b.FLASTUPDATETIME as ot, a.FLASTUPDATETIME as nt from T1 a left join T_DST b on a.FID=b.FID where b.FID is null

结果发现，上述第一条语句走了索引，但是第二条语句仍不走索引。

那么 left join就没法利用索引了吗？在网上搜索了下，发现下文提供了一种新的语法：

MySQL差集MINUS运算符 - MySQL教程 (yiibai.com)

select a.FID, b.FLASTUPDATETIME as ot, a.FLASTUPDATETIME as nt from T1 a left join T_DST b using(FID) where b.FID is null

实测发现，采用上述语句查询能走索引，于是问题解决。

5，总结

本文提出了异种数据库之间单向同步的技术要点，并重点讨论了大表增量更新查询的优化方法。总结如下：

1. 查询表都要有索引
2. 增加的记录集和修改的记录集分开查询，使其查询均能使用索引

• • 查询增加的记录集：left join using(fid) where b.fid is null;
  • 查询修改的记录集：join

另外补充一点，对于每日同步的大表来说，仅比对最近一段时期更新的记录即可，而不必做全表的记录比对，如此可轻松将查询量缩小一个数量级。