如何同步RDS数据库中的数据到ODPS上

用户在购买完RDS后，接下来就可以开始往RDS迁入数据了。在RDS刚刚对外提供服务的时候，用户只能通过将自己的数据库dump成为sql文件，然后再将sql文件source到RDS中去：数据迁移至RDS-MySQL之使用MySQLdump工具，数据迁移至RDS-SQLserver之利用SQL Server客户端工具，这两种方法是最简单的方法，但是局限性也非常的多：

.用户的数据库太大了，逻辑sql导入的方式速度太慢了，严重影响停机时间；

.在导入的过程中报错很多，或者导入一半的过程中中断了，需要重新来过；

.在迁入RDS过程中，希望我的数据库还能能正常提供服务；

大量的用户入云全部堵在迁移数据上面，用户与RDS的缘分就差么这临门一脚。工欲善必先利其器，为了更好的帮助用户入云,RDS对现有的用户入云迁移方式进行改进，帮助用户快速稳定迁移入云，分别为用户提供了mysql和sqlserver两套改良迁移工具:

.mysql迁移工具支持在线迁移，用户可以不中断业务的情况下把数据迁移到RDS中来

.sqlserver的迁移工具采用物理备份的方法，将用户的物理备份上传到FTP中后还原到RDS，提升迁移的速度

这两套工具目前都已经集成到了RDS的控制台中，可以参考：数据迁移至RDS-MySQL之使用阿里云控制台和 数据迁移至RDS-SQLserveru阿里云控制台.

很多用户在控制台上看到的只是一个黑盒子，在工单中多次咨询迁移的原理，在这里大致讲一下这两个工具的迁移实现：

Mysql在线迁移的原理：

第一步:预检查，主要是验证用户网络的通畅性，账号和环境的检查；

第二步:全量备份，该步骤会把用户的数据全量的dump出一份出来，然后还原到RDS；

第三步:增量迁移，该步骤会解析用户全量期间以及后续产生的binlog应用到RDS；

第四步:切换，当RDS的数据完全追上用户的数据库后，用户就可以开始进行切换了；

1. 什么是ODPS

简单讲就是数据仓库，可以存储海量数据，可针对海量数据进行分析、计算。

本命其实叫 MaxCompute ，本文介绍统称为ODPS 

官方文档链接： https://help.aliyun.com/document_detail/27800.html?spm=a2c4g.11186623.6.542.17ae65d4wAeKXV

DataWorks 开发套件

是数据工场，对ODPS数据进行加工处理，主要提供了： 数据集成 、 数据开发 、 数据管理 、 数据治理 、 数据分享 等功能。

官方文档链接： https://help.aliyun.com/document_detail/73015.html?spm=a2c4g.11186623.2.13.5ef65b9cBmTZdQ#concept-wqv-qbp-r2b

2. 登录篇（阿里云子账号）

子账号登录地址： https://signin.aliyun.com/login.htm

产品列表：数加 · DataWorks

账号赋权：如需要进行数据开发，需要根据业务需求，赋对应的工作空间的对应权限。

进入DataWorks> 工作空间列表页面，单击对应项目中的进入工作区，即可进入数据开发页面。（如下图）

2.使用篇

目前数据仓库的整体概况

目前承载的业务

业务 *** 作日志备份分析

其他日志：系统运行日志

BI 数据分析相关（市场部BI）

开发前环境准备

开通DataWorks 权限的子账号

创建项目（1）

官方的文档： https://help.aliyun.com/document_detail/27815.html?spm=a2c4g.11186623.6.568.60d01df0XvZAoh

目前我们的工作空间

新建调度资源（2）

一般进行简单的数据分析只需要默认的调度资源就满足业务需求（目前的模式就是按量付费）

需要进行特殊的数据集成、数据 *** 作时会用到自定义资源

PyOdps 资源组：执行py脚本的资源组

mongoDB 资源组：进行MongDb -->ODPS 时会用到资源进行数据同步。

新增数据源（3）

路径：选择项目 ->选择数据集成 ->同步资源管理 ->数据源

按照官方文档新增即可

数据源列表

批量数据上云（4）

路径：选择项目 ->选择数据集成 ->同步资源管理 ->数据源 ->整库数据迁移

数据开发前准备工作完成，可以进入开发阶段。

3 开发篇

数据开发

基本概念：

业务流程：解决一个业务的抽象模型，可以是一个问题的处理流程。

解决方案：多个业务流程组合成一个解决方案，在同一个解决方案里面可以复用相同的业务流程。

其他的概念： https://help.aliyun.com/document_detail/73017.html?spm=a2c4g.11186623.6.543.3b757c78aHPhAD

数据开发流程：

数据开发流程：

选取两个现有的业务进行数据开发演示

财务部门需求

数据埋点分析

流程图如下

4 运维

运维中心：

作者 | 如期

来源 | 阿里技术公众号

菜鸟供应链金融慢sql治理已经有一段时间，自己负责的应用持续很长时间没有慢sql告警，现阶段在推进组内其他成员治理应用慢sql。这里把治理过程中的一些实践拿出来分享下。

在分页查询治理的文章里已经介绍过我们系统旧的分页查询逻辑，上面的查询sql明显就是分页查询获取总记录数，通过XXX_rules表的分页查询接口溯源，找到发起调用的页面是我们小二后台的一个 *** 作商家准入的页面，页面打开后直接调用分页查询接口，除了分页参数，不传入其他任何查询参数，导致扫描全表。

灵魂拷问：为什么要扫描全表？全表数据展示到页面，花里胡哨的数据有用吗？

调研：和经常使用这个页面的运营聊后了解到，打开页面查询出的全表数据对运营是没有用的，他们根本不看这些数据。运营的 *** 作习惯是拿到商家id，在页面查询框中输入商家id，查到商家数据后进行 *** 作。

由此优化方案就很明朗了：打开页面时不直接查询全量数据，等运营输入商家id后，将商家id作为参数进行查询。XXX_rules表中，商家id这一常用查询条件设置为索引，再结合分页查询优化，全表扫描慢sql得以解决。

优化后的小二后台页面如下：

打开页面时未查询任何数据，查询条件商家账户为必填项。

优化后的sql为:

执行EXPLAIN得到结果如下：

可以看到命中了索引，扫描行数为3，查询速度明显提高。

扫描全表治理简单来说就是加入查询条件，命中索引，去除全表扫描查询，虽然有些粗暴，但并不是没有道理。实际业务场景中，很少有要扫描全表获取全部数据的情况，限制调用上游必须传入查询条件，且该查询条件能命中索引，能很大程度上避免慢sql。

另外，再引申下，XXX_rules初始的用意是准入表，记录金融货主维度的准入情况，最多也就几千条数据，但是很多同事将这张表理解为规则表，写入很多业务相关规则，导致这个表膨胀到一百多万条数据，表不clean了。这就涉及到数据表的设计使用，明确表的使用规范，不乱写入数据，能给后期维护带来很大的便利。

除了时间、 *** 作人字段，XXX_rules表就rule_name、rule_value、status、product_code四个字段，表的索引对这四个字段做各种排列组合。存在如下问题：

1、rule_name离散度不高，放在索引首位不合适；

2、前三个索引重合度很高；

显然是对索引的命中规则不够了解。XXX_rules表很多业务有定时任务对其写入删除，索引多、混乱，对性能有很大的影响。

高性能的索引有哪些，再来回顾下：

1、独立的列：索引列不能是表达式的一部分；

2、选择区分度高的列作为索引；

3、选择合适的索引列顺序：将选择性高的索引列放在最前列；

4、覆盖索引：查询的列均在索引中，不需要回查聚簇索引；

5、使用索引扫描来做排序

6、在遵守最左前缀的原则下，尽量扩展索引，而不是创建索引。

但凡记得第3和6规则，也不至于把索引建成这样。

对索引进行整合如下：

系统中有很多任务拉取整个产品下的准入记录，然后进行处理，所以将区分度较高的product_code放在索引首位，然后添加rule_name、status字段到索引里，进一步过滤数据，减少扫描行数，避免慢sql。针对常用的rule_value查询条件，可以命中UK，因此不用单独建立索引。

很多业务逻辑中，需要拉取满足某个条件的记录列表，查询的sql语句带有order by，记录比较多的情况，排序代价往往很大，但是查询出来的记录是否有序对业务逻辑没有影响，比如分页治理里讨论的count语句，只需要统计条数，order by对条数没有影响，再比如查出记录列表后，不依赖记录的顺序遍历列表处理数据，这时候order by多此一举。

查询sql无limit语句，且业务处理逻辑不依赖于order by后列表记录的顺序，则去除查询sql中的order by语句。

业务中有很多定时任务，扫描某个表中某个产品下所有数据，对数据进行处理，比如：

三个查询条件都是区分度不高的列，查出的数据有27W条，加索引意义也不大。

实际业务量没那么大，顶多几千条数据，表里的数据是从上游同步过来的，最好的办法是让上游精简数据，但是由于业务太久远，找上游的人维护难度太大，因此只能想其他的办法。

这个定时任务目的是拉出XXX_rules表的某些产品下的数据，和另一张表数据对比，更新有差异的数据。每天凌晨处理，对时效性没有很高的要求，因此，能不能转移任务处理的地方，不在本应用机器上实时处理那么多条数据？

数据是离线任务odps同步过来的，首先想到的就是dataWork数据处理平台。

建立数据对比任务，将定时任务做的数据对比逻辑放到dataWork上用sql实现，每天差异数据最多几百条，且结果集含有区分度很高的列，将差异数据写入odps表，再将数据回流到idb。

新建定时任务，通过回流回来的差异数据中区分度高的列作为查询条件查询XXX_rules，更新XXX_rules，解决了慢sql问题。

这个方法的前提是对数据实效性要求不高，且离线产出的结果集很小。

explain上述查询语句，得到结果如下：

XXX_white_list表有将biz_id作为索引，这里查询XXX_white_list表有传入biz_id作为查询条件，为啥explain结果里type为ALL，即扫描全表？索引失效了？索引失效有哪些情况？

索引失效场景

1、OR查询左右有未命中索引的；

2、复合索引不满足最左匹配原则；

3、Like以%开头；

4、需要类型转换；

5、where中索引列有运算；

6、where中索引列使用了函数；

7、如果mysql觉得全表扫描更快时（数据少时）

上述查询语句第8行，customer_id为XXX_level_report表字段，未命中XXX_white_list表索引，导致索引失效。

这个语句用condition、枚举、join花里胡哨的代码拼接起来的，改起来好麻烦，而且看起来“OR customer_id LIKE CONCAT(t.biz_id, '@%')”这句不能直接删掉。最后重构了该部分的查询语句，去除or查询，解决了慢sql。

---