如何进行异构数据库同步

1、简介

最近一阵子笔者在进行完成同样功能的两套异构数据库系统的同步工作，有一些心得体会分享给大家，欢迎技术同仁拍砖。

该项目有一个运行若干年（5～10年）的旧系统，采用的是SQL Server数据库，因为旧平台功能较弱，所以所有对数据库的访问操作都通过存储过程进行操作。

新系统采用笔者公司的平台，应客户需求采用Oracle数据库，完成的功能与旧系统基本相同，但表设计与原有系统不同，有些表对应旧系统中的一张表，但字段名称等大都不一样。另外还有新系统中一张表对应旧系统中多张小表的情况，也有新系统中多个表对应旧系统中多张表的情况。新系统较少依赖数据库，数据库操作都在业务逻辑层完成。

因为旧系统在全国几十个点上运行，而新系统全部替换旧系统需要比较长的时间，该项目采用的是按地区进行逐步替换的原则，所以涉及到需要在两种不同数据库类型、不同数据库结构的异构数据库进行同步。

2、重要术语

2.1 异构数据库

异构数据库系统是相关的多个数据库系统的集合，可以实现数据的共享和透明访问，每个数据库系统在加入异构数据库系统之前本身就已经存在，拥有自己的DBMS。异构数据库的各个组成部分具有自身的自治性，实现数据共享的同时，每个数据库系统仍保有自己的应用特性、完整性控制和安全性控制。

可以是同为关系型数据库系统的Oracle、 SQL Server等，也可以是不同数据模型的数据库，如关系、模式、层次、网络、面向对象，函数型数据库共同组成一个异构数据库系统。

2.2 数据捕获

数据的捕获是数据库同步的基础，变化数据的捕获主要有基于快照法、基于触发器法、基于日志法、基于API法、影子表法和控制表变化法。基于快照法效率比较低一般不能用于同步，可使用基于触发器法、基于日志法或基于API法和控制表变化法进行变化数据的捕获。

旧系统的数据库操作都是通过存储过程，所以可将存储过程作为数据捕获点。

新系统可采用笔者公司平台底层提供了同步程序，该程序能将某个Linux用户本平台进程下所有对指定表（需要同步的表）的所有INSERT、UPDATE和DELETE语句都捕获到，并同步给另一个用户下的同步数据接收进程，该进程能指定接收到同步数据时进行的操作，例如写文件、调用指定的业务进行处理等。

3、同步方案

3.1 需要同步的内容

3.1.1 确保哪些SQL需要同步

在数据库表创建以后，有SELECT、INSERT、UPDATE和DELETE四种操作的语句，因为SELECT语句不会影响表数据的改变，所以只需要INSERT、UPDATE和DELETE操作进行同步。

3.1.2 确认那些表需要同步

并不是所有的表都需要进行同步的，例如如下表就不需要同步：

（1）在系统创建之初需要导入数据，后期基本不需要改动或绝少改动的表：这些表的数据基本只需要在新系统初期将数据导入即可。

（2）一方系统具有，另一方不具有的功能对应的表：例如新系统加上了一些额外的功能，而旧系统没有，这些表不需要同步。

3.1.3 确认异构数据库之间的表和字段的对应

这个是异构数据库编码之前最耗费时间的工作，也是最重要的工作，因为只有严格对应，才能使两者同步后数据库在同步数据后不管用哪套系统都能完成同样的工作。

首先，要对新旧系统中的近50张表找出对应关系：某一张表在对方有一一对应的表？还是对应对方多张表？还是只是对应对方表的一部分？

接着，需要找出字段的对应关系，字段名称是否相同？字段类型是否相同？一个字段是否对应对方表的多个字段？

笔者在Excel表格中列出了所有表与对方表结构的对应关系，这个表格很重要，是后面无论采用何种同步方案都需要用到的同步的依据。

3.2 可选方案

3.2.1 编写触发器进行同步

当数据库为同步对象创建相应的触发器，当对同步对象进行INSERT、UPDATE和DELETE等DML（Data Manipulation Language）操作时，触发器被唤醒，将变换传播到目标数据库。

采用此种方式时，需要在两边的数据库中都创建对需要同步的每个表的INSERT、UPADTE和DELETE操作的三个触发器，当源表发生INSERT、UPDATE和DELETE操作时触发器被启动。因此若两边都有50个需要同步的表，需要编写的触发器个数为：(50+50)*3 = 300（个）。

采用该种方式的缺点是：

（1） 需要为每个需要同步的表编写三个触发器，工作量巨大；

（2） 触发器具有不容易排错、可移植性差、占用资源大等缺点；

（3） 代码可复用性不好。

3.2.2 按系统进行同步

按系统操作同步就是在所有进行数据库更新操作的地方都将其转换成对方的数据库操作进行，例如在新系统进行注册操作（可能涉及到5、6个表进行操作）时，转换为对方的注册操作。

这种同步方式的缺点在于：

（1）不通用：在每加一个系统操作或做一些小改动时，都需要对代码进行修改；

（2）代码耦合度高：需要在所有操作的地方进行处理，转换成对方的SQL语句，代码耦合度非常高；

（3）工作量很大：需要对所有两边的操作进行一次手工“转译”操作，工作量很大。

3.2.3按数据库表操作进行同步

按表操作同步的“原子”是对单个表的的INSERT、UPDATE和DELETE操作，它并不关注操作，例如：如果一个注册操作对应5个INSERT操作、2个UPDATE操作，它将其作为7个原子操作依次处理，并不对这些SQL进行关联。

这种同步方式的优点在于：

（1）相对比较通用：有一些比较简单的对应关系的表，例如只是因为字段名称、表名和字段个数等不同而需要进行同步的表可交给“SQL语句解析器通用程序”进行处理，对于某些复杂的表才需要进行单独编写业务来进行转换处理；

（2）代码耦合度比较低：只需要捕获对同步的表的INSERT、UPDATE和DELETE操作的语句，基本不需要在代码中加入对同步的处理。旧系统中不需要在上层进行处理，在存储过程中相关语句前处理同步即可。新系统只需要在同步接收进程中指定需要处理的业务即可。

推荐使用该同步方式。

3.3 难点问题

3.3.1 自增主键

当表的主键为自增序列号时，在插入时并不指定该字段的值，在某一方插入后，转换为对方的SQL语句后，插入对方的数据库，很大可能两边的这条记录的主键ID不一致。在根据自增序列号进行这条记录的update和delete操作时，因为两边同一条记录的id不一样，很大可能导致删除或更新的记录并不是想要进行删除或更新的记录。

因此，在系统中尽量少用自增序列号主键，若能找到某几个字段作为复合主键，可进行修改，万一找不到，可采用字符类型的唯一标识号，例如：时间+自动机号+若干位随机数数，在INSERT操作时指定该主键的值。

不过，一些只进行INSERT操作的表，例如未接来电表采用自增序列号暂时也不用遇到什么问题。在笔者所遇到的平台中，新系统基本去除了自增主键，旧系统涉及自增主键的表并不太多，而且基本都能找到表中其它的2、3个字段作为唯一主键。

3.3.2 事务问题

无法实现一些带事务的操作。例如注册等流程，因为采用按照数据库操作进行同步，注册操作被分解成多个原子操作，只能当成单个多条SQL语句单独进行处理。

需要事务的操作并不多，可将这些操作改成采用“按系统操作同步”，例如当旧系统进行注册流程中，调用新系统提供的接口，由接口程序也进行一个在新Oracle库的注册流程。

3.3.3 定期数据校验

数据校验也是异构数据库的一个重要问题，进行一段时间的同步后，怎么能保证两边的数据库是同步的？数据校验的周期如何，是一天，几天，还是？

进行数据校验首先要确定校验指标，最简单的校验指标是重要表的数据量是否相等，另外就是检查表里面的数据是否一致，是否能保证能完成同样的功能，可采用抽查机制等，这些工作不可能靠手工完成，因此需要提供数据校验的程序。数据校验的周期应该是可配置的。

3.3.4 日志记录

在进行同步的过程中，有可能因为各种原因导致转换为对方的数据库语句后执行失败，为了日后进行处理和分析，进行错误日志的记录也是非常必要的。

 

=================================================================================================

 

1、详细设计

1.1 SQL语句通用解析程序

首先需要一个通用的“SQL语句通用解析程序”，因为异构数据库的大部分表都与另外的表有一一对应的关系，该解析程序用于对这些一一对应的表进行处理。

表与表之间的对应关系基于上篇提到的整理的Excel文档，“SQL语句通用解析程序”如何读取对应关系对收到的INSERT、UPDATE和DELETE语句进行处理呢？笔者采用的是将对应关系写入XML文档中，当然也可以采用另外的方式。

参考XML格式如下（Oracle 新系统 -> SQL Server旧系统）：

< table name ="freephone" partyName ="PLAT_FreePhone" fieldCount ="8" >
< field name ="Phone" partyName ="Phone" primary ="true" />
< field name ="ProductID" type ="varchar" partyName ="ProductID" partyType ="int" />
< field name ="AreaId" partyName ="" />
< field name ="Effect" partyName ="" />
< field name ="EffectLess" partyName ="" />
< field name ="Addtime" type ="varchar" partyName ="AddTime" partyType ="datetime" partyValue />
< field name ="Operator" partyName ="Operator" />
< field name ="Remark" partyName ="Remark" />
</ table >

当一方对freephone表操作的INSERT语句：

insert into freephone (Phone, ProductID, AreaId, Effect, EffectLess, Addtime, Operator, Remark) values (‘ 13122223333 ’,‘ 003 ’, 25 ,‘ 20110724000000 ’,‘ 20111024235959 ’,‘ 20110702409000000 ’, ‘amigo’, ‘amigo add !’)

根据上面XML配置和通用解析程序的处理，对应对方的新的SQL语句为：

INSERT INTO PLAT_FreePhone(Phone, ProductID, AddTime, Operator, Remark) values (‘ 13122223333 ’, 3 , GETDATE (), ‘amigo’, ‘amigo add !’)

再看一条UPDATE语句：

update freephone set ProductID = ’ 004 ’, AreaId = 26 where Phone = ’ 13122223333 ’

转换为对方的语句为：

update PLAT_FreePhone set ProductID = 4 where Phone = ’ 13122223333 ’

另外除了如上所演示的简单的类型不同、字段名称不同，有些字段在对方没有外，有时候有些字段还需要一定的转换，例如在一方存储的limited字符串是二进制方式，例如111111，而对方存储的是二进制对应的十进制的值，再例如，一方存储的是6位的时间（时分秒），而对方存储的只是4位的时间（时分），那么可做一次自定义的substr操作。

看另一个参考的XML实例：

< table name ="phonelimited" partyName ="PLAT_PhoneLimited" fieldCount ="6" >
< field name ="Phonenumber" partyName ="Phone" primary ="true" />
< field name ="AreaId" type ="int" partyName ="AreaID" partyType ="int" />
< field name ="Limited" type ="varchar" partyName ="Limited" partyType ="int" function ="binToDec" />
< field name ="AddTime" type ="varchar" partyName ="AddTime" partyType ="datetime" />
< field name ="Operator" partyName ="Operator" />
< field name ="Remark" partyName ="Remark" />
</ table >

其中：function="binToDec"表示需要进行一次二进制到十进制的转换，一方的如下语句：

insert into phonelimited(Phonenumber, AreaId, Limited) values (‘ 13122223333 ’, 25 , ‘ 10110 ’)

转换成对方的INSERT语句如下：

insert into PLAT_PhoneLimited(Phone, AreaID, Limited) values (‘ 13122223333 ’, 25 , 22 )

需要做取字串操作的参考定义如下：

< field name ="StartTime" length ="6" partyName ="OpenTime" partyLength ="4" function ="substr" />

另外，还有一些字段本端没有，对端具有并且有点还是必填字段，并且对端的这些字段需要填写固定的值，因此在INSERT、UPDATE和DELETE语句都需要进行特别的操作，参考XML定义如下：

……
< insert fieldCount ="2" >
< field name ="Node" value ="0" />
< field name ="Key" value ="" />
</ insert >
< update condition ="and Node=0 and key=''" />
< delete condition ="and Node=0 and key=''" />
……

1.2 SQL语句接收器程序

异构数据库的SQL语句传过来时，需要一个SQL语句接收器程序，该接收器解析该语句操作的表名（例如phonelimited）、执行的操作名（INSERT、UPDATE还是DELETE），根据这些信息读取配置文件信息，决定是调用“1.1 SQL语句通用解析程序”进行处理，如果不是存在一对一对应关系的表，扔给配置的指定业务进行特别处理。

该程序的主要功能如下：

（1）接收SQL语句：只接收需要同步的表的INSERT、UPDATE和DELETE语句，SELECT语句直接丢弃；

（2）分发SQL语句：将语句分发给不同程序进行处理，解析接收到的SQL语句的表名和操作名称，决定分发给通用解析进行处理还是特定解析程序进行处理。

需要关注的问题：当多条SQL语句并发发送过来时，是启动多个独立的自动机处理，还是将语句放入队列中，依次出队列进行处理，还是采用其它方式进行处理呢？每条SQL语句启动独立的自动机处理效率最高，但是也存在问题：因为多条语句是竞争操作，如果操作的是同样的记录但做相反操作时怎么办？这些都是开发人员需要考虑到的问题。

1.3 SQL单个表特定解析程序

在上篇中提到，并不是所有的表在两端有一一对应的表，对于一些本端一个表对应对端数据库多个表，或者本端多个表对应对端一个表的情况，通用解析程序不好处理这些语句，可将这些表采用特定的解析程序进行处理。

对于一个表对应多个表的情况，可能一条INSERT语句对应对端数据库多条INSERT或UPDATE语句。

对于多个表对应一个表的情况，一条INSERT语句可能对应对方的一条INSERT语句或UPDATE语句。

1.4 带事务的操作

像注册等流程，要严格保证事务，因此采用新旧系统提供接口（例如http接口或SOAP接口等）的方式，当某一端调用注册流程成功后，调用对端系统提供的注册的接口完成操作。

因为这种操作并不多，所以并不需要太多的工作量。

1.5 数据校验程序

要定期（一般是一天）对两边数据进行校验，对一些错误的数据及时的更正，进行数据校验首先要确定哪些东西需要进行数据校验，接着有针对性的进行核对。

2、扩展阅读

2.1 数据同步方法

对象变化是数据同步的基础，它直接决定了数据同步的更新方式和选时方式，所以数据同步常常按照变化捕获的不同进行分类，一般归结于如下集中基本方法：

（1）基于快照法：快照是数据库中存储对象在某一时刻的即时映像。通过同步对象定义一个快照或采用类似方法，可以将它的当前映像作为更新副本的内容。

（2）基于触发器法：在源数据库为同步对象创建相应的触发器，当同步对象进行INSERT、UPDATE和DELETE等DML命令时，触发器被唤醒，将变化传播到目标数据库。

（3）基于日志法：数据库日志作为维护数据库完整性的数据库恢复的重要工具，其中已经包含了全部成功提交的操作记录信息。该方法通过分析数据库日志的信息来捕获同步对象的变化序列。

（4）基于API法：一些小型的数据库和非关系型数据库没有触发器和日志机制，可以在应用程序和数据库之间引用一层中间件，由它提供一系列API，在API上来完成应用程序对数据库修改的同时，记录同步对象的变化序列。

（5）基于影子表法：许多情况下，源程序无须了解同步对象的每一个操作，只要知道最后总共发生了什么变化就够了。因此，可以在初始化时为同步对象表T建立一个影子表S，作为一份当时的拷贝，以后通过在适当时机通过比较当前T和S的内容获取净变化信息。

（6）基于控制表变化法：就是为每个要同步的表创建一个控制表C，C包含了主键字段Pk和一些控制信息字段，当T中某个字段发生改变时，C中同主键Pk的记录也随即被修改。这一过程通常可以通过触发器实现，到时候只需根据C就知道T中的变化信息。

3、附录

（1）《异构数据库同步问题研究》：

http://wenku.baidu.com/view/d3b283bff121dd36a32d8293.html