开发者学堂课程【数据仓库 ACP 认证课程：快速学习云原生数据仓库 AnalyticDB MySQL 版 _解析与实践3】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/928/detail/14625
云原生数据仓库 AnalyticDB MySQL 版_解析与实践3
前言
上节课介绍云原生数据仓库 analyticDB MySQL 版的一些基本概念、操作等基础之上，本节课进行继续介绍。
内容介绍
一、数据导入导出与同步链路搭建
二、SQL 优化与慢查询解决
三、数据更新
四、演示
五、试题
六、回顾与总结
一、数据导入导出与同步链路搭建
1. 导入方案
众所周知，Ananylyticdb mySQ 不生产数据，其数据大多数来自业务数据库及生产数据库，通过 ETL 过程导入到数据中，所以数据的导入导出是数据库的一项最基本的功能。同时针对阿里云的系列产品，可以通过数据传入服务来实现同步链路搭建。
Ananylyticdb mySQL 版提供多种导入方案，可满足不同场景下的数据导入需求
数据库 |
RDS MYSQL ;POLAR DB-X (原DRDS);POLARDB MYSQL ;ORACE ;SQL SERVER |
Oss |
INSERT 外表;通过外表导入 oss 数据 Dataworks 数据工厂：配置 oss 数据源 |
大数据 Max computer |
INSERT 外表;通过 INSERT 外表方式导入 MAx compute数据 Dataworks：配置 max computer 数据源 Hadoop：flink |
消息队列 kafka |
使用 logstash 插件：使用 logstash 将 kafka 数据写入analyticdbmySQL Dataworks：配置 Kafka 读取 |
日志类数据 |
SLS；将 SLS 数据投递到 analyticdb MYSQL Dataworks 配置 sls 读取 日志数据使用 logstash 插件：使用实时采集日志数据 |
本地数据 |
Load DATA 使用 LOAD DATA 导入不同场景下的数据导入需求 Dataworks：现将数据导入 os s或 FTP，在使用 oss 读取或者FTP 读取的方式导入 导入工具：使用 AnalyticDBmyqsl 版导入工具导入本地数据 |
其中：
Dataworks 是阿里云当中非常重要的一个数据加工工具，通过它，可以开发设计应用一系列的数据处理工具。
大数据 Max computer，也是阿里云当中针对更大数据集的批量处理的一个产品。
2. DML 本地数据导入
步骤
1. 本地客户端
配置 my.cnf 配置文件，开启 local-file 功能（可以读取本地文件）
2. AnalyticDBmyqsl 创建目标表（也就是导入的数据能够放置到哪个表内）
3. DML 命令导入
以下是 LOAD DATA 的代码（其中有些是非必须的）
LOAD DATA LOC AL
INFLE “FILE-name’//对应的文件名从那个文件导入数据
【REPLACE\IGNROE]
INTO TABLE TABLE-NAMES//作为创建的目标表
【{FELDS\COLUMNS]
【terminated / BY‘ string’】
//需要制定数列中的分隔符，比如逗号，竖杠等等
{（opTIDNART } ENCLOSED BY CHAR}}
【lines}
【terminated / BY STRING】
{，column-name-or- user-war }...0】
通过 LOAD 实现本地数据的导入
3. DML 外表式数据导入导出
（1）说明讲解
通过 AnalyticDB MySQL 映射表进行数据导入导出操作。支持 oss、 mysol 、max compute，所有外表为映射表，创建在 analyticdb 数据库中，所有外表指向数据源。
说明：
通过 RDS 进行产品数据的导入时，RDSMySQL 与 AnalyticDB 必须在同一个 vpc下，此时 ADB 可以通过 VPC 的支持访问 MySQ L也可以是 EC S自建 MySQL ，此外 OSS 与 AnalyticDB 所属区域必须相同。
（2）通过 DML 外表是数据导入导出-创建外部表：
①首先在 AnalyticDB MySQL 中创建 adb-demo 数据库，并在数据库中创建一个外部映射表，简称外表 courses-extenal-table，表的名字根据自己的业务需求创建。
CREATE TABLE IF NOT EXISTS courses-extenal-table
//如果表格不存在，就创建表
Id bigint not null
Name varchar （32）NOT NULL
Grade varchar （32）NOT NULL
Sumbmission-date TIMESTEAMP NOT NULL
）
ENGINE=’mysql’
TABLE_PROPERTIES=’{
“url”：“jdbc：mysql://mysql-vpc-address：3306/test_adb”
“tablename”:”courses”,
“username”:”mysql-user-name”
“password”:”mysql-user-password”
}’
//表的主键为id
②参数说明：
ENGINE=’MYSQL’
——表示该表是外部表，使用存储引擎为 MySQL
若是创建普通表和维表是直接在 mysql 创建，不包含 ENGINE=格式
TABLE_PROPERTIES
——用于告知 AnalyticDB 如何访问 mysql 中的数据
通过该语句即可访问到所需外部数据
Url
——mysql 中的内网地址，即 VPC 链接地址，地址格式为：
“jdbc：mysql://mysql-vpc-address：3306/test_adb”
tablename 所需要的表，
username&password
——mysql 中访问数据库的用户名和密码
如果有开发的经验只需访问 MySQ L即可访问到对应的数据库
（3）DML 外表使数据导入导出 MySQL
通过 AnalyticDB MySQL映射表进行数据导入导出操作。
导入实际案例，完成该过程需要三个步骤：
①前提准备：首先做好准备工作，完成实例的创建，设置白名单、创建账号和数据库、创建表。示例：分别在两个数据库中创建表，导入导出数据的表。
首先，先在ids创建一张表，
CREATE TABLE rds courses｛
Id bigint NOT NULL，
Name varchar（32）NOT NULL，
PRIMARY KEY（id）
然后在 adb 当中也创建一张表，
CREATE TABLE adb courses｛
Id bigint AUTO INCREMENT,
Name varchar NOT NULL，
}DISTRIBUTE BY HASH(ID)}
前提：已经完成集群、实例的创建，设置白名单、创建账号和数据库。
之后登录 AnalyticDB mySQL创建需导入导出数据的表的映射表
创建一张表与上述两张表结构相同，表中存在引擎的说明
ENGINE “mysql”访问表的名称
“url”“：”Jdbc，mysql：//mysql.Vpc.address：3306/xxx-db”
以及相关计算引擎的信息，对应的数据库的那张表，对应的用户名，密码。代码如下：
CREATA TABLE IF NOT EXISTS courses_external_table（
Id bigint NOT NULL，
Name varchar（32）NOT NULL，
PRIMARY KEY（id）
）
ENGINE=’mysql’
TABLE_PROPERTIES=’{
“url”：“jdbc：mysql://mysql-vpc-address：3306/test_adb”
“tablename”:”courses”,
“username”:”mysql-user-name”
“password”:”mysql-user-password”
}’
根据这些信息，保证我们可以访问到对应表中对应数据的信息。
外部表创建完成后，通过 insert into 语句实现数据的导入导出
存在两条 insert into 语句，注意语句表的名称。
insert into courses-external-table
Select*from adb-courses，-AnyalyticDB 导入到 Rds
从 adb 中取得数据，添加到外物表中，对于 AnyalyticDB 而言实现数据导出的功能AnyalyticDB 导出到 rds 中
如果向实现 rds 导入到 AnyalyticDB，则更换语句
insert into adb-courses
Select*from courses-external-table
即可实现 rds 导入到 AnyalyticDB
4. DML 外表式数据导入导出-Maxcomputer
步骤：
（1）首先确定目标和源表
Maxcompute：数据表
AnalyticDB：数据表
AnyalyticDB 和maxcomputer 两者之间的数据的导入导出
（2）创建映射表
AnalyticDB 中创建该数据表的映射表
（3）DML命令导出
通过 INSERT INTO 或者 INSERT overwrite 来实现数据的导入
Maxcomputer 是阿里云产品中的一款大数据计算产品，支持海量数据的批处理计算
参数定义
ENGINE=”ODPS”存储引擎为 odps
ENGINE= ’ODPS’---储存引擎是 MaxCompute
TABLE_PROPERTIES=’{
“endpoint" :"http://service.cn.maxcompute.aliyun-inc.com/api",
---MaxCompute的EndPoint （域名节点）
“accessid”: "LTAIF*****5FsE・，
---访问 MaxCompute的AccessKey ID
“AccessKey”：“Ccw****iWjv*“，
---对应的ID 的 Access key Secret
“project_name”"odps-project1”,
---MaxCompute 中的工作空间名称
“table_name”: *xxxx_xxxx"
---MaxCompute 中的数据表名称
说明：目前仅支持 analyticDB MYSQL 通过 maxcomputer 的 vpc 网络 endpoint访问 maxcomputer
5. DML 外表式数据导入导出-oss
步骤与刚才讲解的类似：
（1）确定目标和源表：
OSS：文件
AnalyticDB：数据表
首先确定目标和源表，oss 上均是以文件的形式存储，以文件形式进行导入，加载到 analyticDB MYSQL 当中，同样可以将 analyticDB MYSQL 数据导出到文件当中。
（2）创建映射表：
AnalyticDB 中创建该数据表的映射表
（3）DML 命令导出
通过 INSERT INTO 或者 INSERT overwrite 来实现数据的导入
在 adb 中创建表数据表，此时即可通过 INSERT INTO 命令实现数据的导入导出
（4）外部表对应的参数
ENGINE=”oss“对应的引擎为 oss 对象存储
如果需要访问 oss 当中对应的文件需要知道“end point” “url’’”acessid””acesskey”以及在 oss 当中列分隔符”delimiter”
注意 oss 与 analyticDB MySQL 所属的区域相同，如都是华东，即在该地区创建产品，若是不在同一地区则无法实现该数据的导入导出。
ENGINE=”OSS”---存储引擎为 odps
TABLE_PROPERTIES=’{
“endpoint" :"oss-cn-xxxxxx-internal.aliyuncs.com",
---OSS 的 EndPoint （域名节点）
“url”:”oss://bucket-name/xxx/”
---OSS 中文件夹的地址，以/结尾。
“accessid”: "LTAIF*****5FsE・，
“AccessKey”：“Ccw****iWjv*“，
---访问 OSS 文件的 Access key 和 Secret
“delimiter”：“；”
---定义 OSS 中数据文件的列分隔符
6. 数据同步：RDS 到 Analytic DB MYsql 同步链路整体介绍
数据的导入导出为一次性的实时的，无法进行数据的改变，通过数据同步，阿里云的同步输入来搭建同步数据链路，来实现对数据源实时数据的传输。
DTS：数据传输服务，支持关系型数据库、NOSQL 及大数据（qlap）等数据源之间的数据传输与交换。在阿里云的多款产品之间通过数据传输进行数据的导入导出实现同步链路的搭建。
通过 DTS 同步到多种数据源数据到 analyticDB MySQL 中，analyticDB MySQ L为数仓产品，本身并不生产数据，数据均来源于外部数据源，通过多个数据源传输数据来实现交互式 B I分析和数仓迁移
数据源支持 ORACLE、POLARDB mysql、 POLARDB-X 等等，这些数据源通过 DTS 来实现数据加载到 ADB mysql 当中，同时 mysql 为后面的商业大表、大屏，来提供数据的支撑。
同样 DTS 也可以实现数据的迁移，下面重点介绍 RDS MYSQL 到 analyticDB MySQL 的同步流程，其他数据源同步流程类似。
7. 数据同步：整体介绍
同步由结构初始化，全量同步，增量同步三个步骤组成
很好理解，原始数据存在于 RDS MySQL，数据存在自己的数据结构，需要在 adb当中实现结构初始化，把表中数据结构在 analyticDB MySQL 建立结构，再进行全量同步，将 RDS MySQL 存量历史数据全量同步到在 analyticDB MySQL，随着后续 RDS MySQL 为业务数据库面向生产日常业务，数据进行增删改查，通过增量同步保证 RDS MySQL 同步到 analyticDB MySQL 当中，这就是数据同步的三个过程。
（1）结构初始化：
是同步的第一个环节，即在 analyticDB MySQL 端创建于源 MySQL 对应表的结构。
①确认 analyticDB MySQL 和 MySQL 间表的结构关系。
②根据配置的表结构信息，DTS 会自动的在 analyticDB MySQ L端创建表。例如下图中，指定了表和主键列和分布列，DTS 会根据这些信息，加上其他列的信息，在AnalyticDB MySQL 端建表。
分布列是 AnalyticDB MySQL 的表结构属性，AnalyticDB MySQL 会根据该列把数据在多个节点上进行分布式储存。注意，源端必须存在主键列，否则无法正确同步数据。
（2）全量同步
在结构初始化后，DTS 会进行全量同步，然后在此基础上，在基于 binlog 进行增量同步，全量同步的方法，基于主键划分区间，然后并行同步到 analyticDB MySQL 中，全量数据一般规模大，为了提高效率对原表进行划分，进行并行处理。
可以简单的理解为按主键范围把数据从 MySQL 中查询出来，并写入 analyticDB MySQL，可以实现高效并行写，加快全量同步性能。
在此注意，为什么需要全量同步？
MySQL 中存在大量历史数据，这些数据对应的 binlog 或许已经被删除，无法通过重放 binlog 来同步这部分数据。同时，并行、批量拉取数据并写入 analyticDB MySQL，效率也比逐行解析 binlog 要高。
也就是说在全量同步阶段，DTS 是通过将源表按照主键进行划分，然后将每一部分数据，单独、并行写入 analyticDB MySQL 当中。
（3）增量同步阶段
MySQL 端的修改会产生 binlog，dts 通过捕获并解析 mysql 端的 binlog 日志，转换为 insert/update/delete 等语句，并在 analyticDB MySQL 端回放这些操作，实现 mysql 到 analyticDB MySQL 的增量同步
结构初始化和全量同步都是一次性的，增量同步则是持续的，只要 mysql 端有变化，DTS 就会捕获并同步到 analyticDB MySQL 端。
日常业务流程通过 DML/DDL 进行修改 rdsMySQL 产生 binlog 日志，DTS 在进行捕获、解析进行转换，在 analyticDB MySQL 中重新执行操作从而实现两个数据源的同步。
