mysql增量sql幂等_从 SQL Server 到 MySQL（二）：在线迁移，空中换发动机

，我们给大家介绍了从 SQL Server 到 MySQL 异构数据库迁移的基本问题和全量解决方案。 全量方案可以满足一部分场景的需求，但是这个方案仍然是有缺陷的： 迁移过程中需要停机，停机的时长和数据量相关。 对于核心业务来说，停机就意味着损失。 比如用户中心的服务，以它的数据量来使用全量方案，会导致迁移过程中停机若干个小时。 而一旦用户中心停止服务，几乎所有依赖于这个中央服务的系统都会停摆。

能不能做到无缝的在线迁移呢？系统不需要或者只需要极短暂的停机？ 作为有追求的技术人，我们一定要想办法解决上面的问题。

在线迁移的原理和流程

针对 Oracle 到 MySQL，市面上已经有比较成熟的解决方案 - alibaba 的 yugong 项目。 在解决 SQL Server 到 MySQL 在线迁移之前，我们先研究一下 yugong 是如何做到 Oracle 的在线迁移。

下图是 yugong 针对 Oracle 到 MySQL 的增量迁移流程：

yugong-oracle.png

这其中有四个步骤：增量数据收集 (创建 Oracle 表的增量物化视图)

进行全量复制

进行增量复制 (可并行进行数据校验)

原库停写，切到新库

Oracle 物化视图(Materialized View)是 Oracle 提供的一个机制。 一个物化视图就是主库在某一个时间点上的复制，可以理解为是这个时间点上的 Snapshot。 当主库的数据持续更新时，物化视图的更新可以通过独立的批量更新完成，称之为 refreshes。 一批 refreshes 之间的变化，就对应到数据库的内容变化情况。 物化视图经常用来将主库的数据复制到从库，也常常在数据仓库用来缓存复杂查询。

物化视图有多种配置方式，这里比较关心刷新方式和刷新时间。 刷新方式有三种：Complete Refresh：删除所有数据记录重新生成物化视图

Fast Refresh：增量刷新

Force Refresh：根据条件判断使用 Complete Refresh 和 Fast Refres

刷新机制有两种模式： Refresh-on-commit 和 Refresh-On-Demand。

Oracle 基于物化视图，就可以完成增量数据的获取，从而满足阿里的数据在线迁移。 将这个技术问题泛化一下，想做到在线增量迁移需要有哪些特性？ 我们得到如下结论(针对源数据库)：增量变化：支持增量获得增量数据库变化

延迟：获取变化数据这个动作耗时需要尽可能低

幂等一致性：变化数据的消费应当做到幂等，即不管目标数据库已有数据什么状态，都可以无差别消费

回到我们面临的问题上来，SQL Server 是否有这个机制满足这三个特性呢？ 答案是肯定的，SQL Server 官方提供了 CDC 功能。

CDC 的工作原理

什么是 CDC？ CDC 全称 Change Data Capture，设计目的就是用来解决增量数据的。 它是 SQL Server 2008 新增的特性， 在这之前只能使用 SQl Server 2005 中的 after insert / after delete / after update Trigger 功能来获得数据变化。

CDC 的工作原理如下：

cdc-data-flow.png

当数据库表发生变化时候，Capture process 会从 transaction log 里面获取数据变化， 然后将这些数据记录到 Change Table 里面。 有了这些数据，用户可以通过特定的 CDC 查询函数将这些变化数据查出来。

CDC 的数据结构和基本使用

CDC 的核心数据就是那些 Change Table 了，这里我们给大家看一下 Change Table 长什么样，可以有个直观的认识。

通过以下的函数打开一张表(fruits)的 CDC 功能。-- enable cdc for dbsys.sp_cdc_enable_db;-- enable by tableEXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'fruits', @role_name = NULL;-- list cdc enabled tableSELECT name, is_cdc_enabled from sys.databases where is_cdc_enabled = 1;

至此 CDC 功能已经开启，如果需要查看哪些表开启了 CDC 功能，可以使用一下 SQL：-- list cdc enabled tableSELECT name, is_cdc_enabled from sys.databases where is_cdc_enabled = 1;

开启 CDC 会导致产生一张 Change Table 表 cdc.dbo_fruits_CT，这张表的表结构如何呢？.schema cdc.dbo_fruits_CT

name            default  nullable  type          length  indexed--------------  -------  --------  ------------  ------  -------__$end_lsn      null     YES       binary        10      NO

__$operation    null     NO        int           4       NO

__$seqval       null     NO        binary        10      NO

__$start_lsn    null     NO        binary        10      YES

__$update_mask  null     YES       varbinary     128     NO

id              null     YES       int           4       NO

name            null     YES       varchar(255)  255     NO

这张表中以 __ 开头的字段是 CDC 所记录的元数据，id 和 name 是 fruits 表的原始字段。 这意味着 CDC 的表结构和原始表结构是一一对应的。

接下来我们做一些业务操作，让数据库的数据发生一些变化，然后查看 CDC 的 Change Table：-- 1 stepDECLARE @begin_time datetime, @end_time datetime, @begin_lsn binary(10), @end_lsn binary(10);-- 2 stepSET @begin_time = '2017-09-11 14:03:00.000';SET @end_time   = '2017-09-11 14:10:00.000';-- 3 stepSELECT @begin_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than', @begin_time);SELECT @end_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);-- 4 stepSELECT * FROM cdc.fn_cdc_get_all_changes_dbo_fruits(@begin_lsn, @end_lsn, 'all');

这里的操作含义是：定义存储过程中需要使用的 4 个变量

begin_time / end_time 是 Human Readable 的字符串格式时间

begin_lsn / end_lsn 是通过 CDC 函数转化过的 Log Sequence Number，代表数据库变更的唯一操作 ID

根据 begin_lsn / end_lsn 查询到 CDC 变化数据

查询出来的数据如下所示：__$start_lsn          __$end_lsn  __$seqval             __$operation  __$update_mask  id  name--------------------  ----------  --------------------  ------------  --------------  --  ------0000dede0000019f001a  null        0000dede0000019f0018  2             03              1   apple

0000dede000001ad0004  null        0000dede000001ad0003  2             03              2   apple2

0000dede000001ba0003  null        0000dede000001ba0002  3             02              2   apple2

0000dede000001ba0003  null        0000dede000001ba0002  4             02              2   apple3

0000dede000001c10003  null        0000dede000001c10002  2             03              3   apple4

0000dede000001cc0005  null        0000dede000001cc0002  1             03              3   apple4

可以看到 Change Table 已经如实的记录了我们操作内容，注意 __$operation 代表了数据库操作：1 => 删除

2 => 插入

3 => 更新前数据

4 => 更新后数据

根据查出来的数据，我们可以重现这段时间数据库的操作：新增了 id 为 1 / 2 的两条数据

更新了 id 为 2 的数据

插入了 id 为 3 的数据

删除了 id 为 3 的数据

CDC 调优

有了 CDC 这个利器，终于意味着我们的方向是没有问题的，我们终于稍稍吁了一口气。 但除了了解原理和使用方式，我们还需要深入了解 CDC 的工作机制，对其进行压测、调优， 了解其极限和边界，否则一旦线上出现不可控的情况，就会对业务带来巨大损失。

我们先看看 CDC 的工作流程，就可以知道有哪些核心参数可以调整：

Influence of capture job parameters

上图是 CDC Job 的工作流程：蓝色区域是一次 Log 扫描执行的最大扫描次数：maxscans number(maxscans)

蓝色区域同时被最大扫描 transcation 数量控制：maxtrans

浅蓝色区域是扫描间隔时间，单位是秒：pollinginterval

这三个参数平衡着 CDC 的服务器资源消耗、吞吐量和延迟， 根据具体场景，比如大字段，宽表，BLOB 表，可以调整从而达到满足业务需要。 他们的默认值如下：maxscan 默认值 10

maxtrans 默认值 500

pollinginterval 默认值 5 秒

CDC 压测

掌握了能够调整的核心参数，我们即将对 CDC 进行了多种形式的测试。 在压测之前，我们还需要确定关键的健康指标，这些指标有：内存：buffer-cache-hit / page-life-expectancy / page-split 等

吞吐：batch-requets / sql-compilations / sql-re-compilations / transactions count

资源消耗：user-connections / processes-blocked / lock-waits / checkpoint-pages

操作系统层面：CPU 利用率、磁盘 IO

出于篇幅考虑，我们无法将所有测试结果贴出来， 这里放一个在并发 30 下面插入一百万数据(随机数据)进行展示：

cdc-metrics.png

cdc-system-load.png

测试结论是，在默认的 CDC 参数下面：

CDC 的开启/关闭过程中会导致若干个 Process Block， 大流量请求下面(15k TPS)过程会导致约 20 个左右 Process Block。 这个过程中对服务器的 IO / CPU 无明显波动， 开启/关闭瞬间会带来 mssql.sql-statistics.sql-compilations 剧烈波动。 CDC 开启后，在大流量请求下面对 QPS / Page IO 无明显波动， 对服务器的 IO / CPU 也无明显波动， CDC 开启后可以在 16k TPS 下正常工作。

作者：alswl

链接：https://www.jianshu.com/p/6f8381de2765