阿里云开发者：通过 AnalyticDB MySQL + DTS 解决 MySQL 数据分析性能问题

作者：卢纶

阿里云开发者 是阿里巴巴官方技术号，专注于展示阿里的技术创新。

引言

在数据驱动决策的时代，一款性能卓越的数据分析引擎不仅能提供高效的数据支撑，同时也解决了传统 OLTP 在数据分析时面临的查询性能瓶颈、数据不一致等挑战。本文将介绍通过 AnalyticDB MySQL + DTS 来解决 MySQL 的数据分析性能问题。

背景

在应对大规模业务数据的在线统计分析需求时，传统数据库常常难以满足高性能和实时分析的要求。随着业务数据的不断累积，数据量迅速膨胀，虽然可以通过扩展数据库配置来暂时提升查询性能，但扩容过程中会影响用户体验和业务连续性。如何快速灵活地将复杂查询操作与日常业务事务处理分开，通常需要大量开发工作来实现 OLTP 数据库与 OLAP 数据库之间的数据同步，并确保数据的一致性。

解决方案

本文将介绍结合 AnalyticDB MySQL + DTS 来解决 MySQL 的数据分析性能问题。通过 DTS（数据传输服务），实现 MySQL 到云原生数据仓库 AnalyticDB MySQL 版的实时数据同步。DTS 提供全量校验和增量校验功能，确保两者之间的数据一致性。在云原生数据仓库 AnalyticDB MySQL 版中创建交互式资源组，并通过数据库账号将其绑定至资源组，SQL 查询根据绑定关系路由到相应的资源组进行执行，最终由云原生数据仓库 AnalyticDB MySQL 版对外提供应用查询服务。

优势

1. 简单易用

   • AnalyticDB MySQL 高度兼容 MySQL 协议和多种 SQL 标准，且提供了窗口函数、圈人函数、漏斗留存函数、路径分析函数等多种函数，满足多种数据分析场景。

2. 高性能

   • 超大规模数据写入实时可见，确保数据的强一致性。支持秒级甚至毫秒级对海量数据进行查询和计算，复杂 SQL 查询速度相比传统的关系型数据库快多倍。

3. 低成本

   • 存储计算分离，支持计算资源按需在线扩缩容、分时弹性和按需弹性等功能。同时支持冷热数据分层存储，按实际使用的存储空间计费，降低了计算和存储的成本。

4. 应用场景广泛

   • 业务报表统计：利用 AnalyticDB MySQL 的高性能分析能力，在金融、零售、制造业等领域提供快速的报表查询引擎。
   • 交互式运营分析：利用 AnalyticDB MySQL 的实时交互式查询分析能力，帮助用户在多维数据集中更全面的分析和决策。
   • 实时数仓：利用 DTS 和 AnalyticDB MySQL 实现数据实时同步，通过实时数据的快速分析和洞察，为业务决策提供了有力支持。

方案概览

一键加速：AnalyticDB MySQL 版构建企业级数据分析平台

使用 DMS 的测试数据构建模拟生成生产数据到云数据库 RDS MySQL 版实例，利用 DTS 实现用户可视化操作，通过一键数据同步，灵活配置云数据库 RDS MySQL 版实例与云原生数据仓库 AnalyticDB MySQL 版集群之间的数据表实时同步。DTS 提供全量校验和增量校验功能，确保云数据库 RDS MySQL 版实例与云原生数据仓库 AnalyticDB MySQL 版集群的数据一致性。借助云原生数据仓库 AnalyticDB MySQL 版集群的在线实时分析能力，解决大规模业务数据的在线统计分析需求。

方案架构

1. 技术架构包括以下基础设施和云服务：

   • 1个专有网络 VPC：为云数据库 RDS MySQL 版实例和云原生数据仓库 AnalyticDB MySQL 版集群等云资源构建云上私有网络。
   • 1个云数据库 RDS MySQL 版实例：用于在线事务处理（OLTP）系统的数据库，存储业务系统数据。
   • 1个 DTS（数据传输服务）实例：用于实现云数据库 RDS MySQL 版实例与云原生数据仓库 AnalyticDB MySQL 版集群之间的数据实时同步。
   • 1个云原生数据仓库 AnalyticDB MySQL 版集群：作为 DTS 数据同步的目标端，同时优化数据查询性能，为实时报表生成和交互式运营分析等 OLAP 业务提供快速响应支持。
   • 数据管理 DMS：用于数据库实例管理，用来连接云原生数据仓库 AnalyticDB MySQL 版集群和云数据库 RDS MySQL 版实例。

2. 技术架构图如下：
   

操作流程

部署资源

规划好资源后，按照以下步骤部署方案中的所有资源。

1. 创建专有网络 VPC 和交换机
   

2. 创建云数据库 RDS MySQL 版实例
   

3. 创建云原生数据仓库 AnalyticDB MySQL 版集群
   

创建数据同步账号

在资源部署的阶段，已经创建了云数据库 RDS MySQL 版实例、云原生数据仓库 AnalyticDB MySQL 版集群。接下来，需要对云数据库 RDS MySQL 版实例创建高权限账号用于后续的数据同步，对云原生数据仓库 AnalyticDB MySQL 版集群创建一个高权限账号和资源组，以执行数据分析的操作。

1. 创建云原生数据仓库 AnalyticDB MySQL 版集群高权限账号与资源组

   • 创建高权限账号
     
   • 创建 Interactive 型资源组
     
     

2. 创建云数据库 RDS MySQL 版实例高权限账号
   

构建测试数据

本阶段构建用于测试的业务系统表数据，为后续数据同步、数据分析做准备。

1. 登录云数据库 RDS 控制台
   

2. 创建数据库和表

   sql 复制代码

   -- 创建数据库
   CREATE DATABASE IF NOT EXISTS workshop;
   
   -- 用户信息表
   CREATE TABLE IF NOT EXISTS workshop.user_info (
       user_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
       user_name VARCHAR(255) NOT NULL COMMENT '用户名',
       gender ENUM('M', 'F') COMMENT '性别，M为男性，F为女性',
       city_id INT COMMENT '城市ID',
       create_dt DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
       update_dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
   ) COMMENT='用户信息表';
   
   -- 城市信息表
   CREATE TABLE IF NOT EXISTS workshop.city_info (
       city_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '城市ID',
       city_name VARCHAR(255) NOT NULL UNIQUE COMMENT '城市名称',
       create_dt DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
       update_dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
   ) COMMENT='城市信息表';
   
   -- 产品信息表
   CREATE TABLE IF NOT EXISTS workshop.product_info (
       product_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '产品ID',
       product_name VARCHAR(255) NOT NULL UNIQUE COMMENT '产品名称',
       product_price DECIMAL(10, 2) NOT NULL COMMENT '产品价格',
       create_dt DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
       update_dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
   ) COMMENT='产品信息表';
   
   -- 订单信息表
   CREATE TABLE IF NOT EXISTS workshop.order_info (
       order_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '订单ID',
       user_id INT NOT NULL COMMENT '用户ID',
       product_id INT NOT NULL COMMENT '产品ID',
       quantity INT NOT NULL COMMENT '数量',
       order_status ENUM(
           'PENDING',     -- 待处理
           'PAID',        -- 已支付
           'SHIPPED',     -- 已发货
           'DELIVERED',   -- 已送达
           'CANCELLED',   -- 已取消
           'RETURNED'     -- 已退货
       ) NOT NULL DEFAULT 'PENDING' COMMENT '订单状态',
       order_date DATETIME NOT NULL COMMENT '订单时间',
       is_delete TINYINT DEFAULT 0 COMMENT '0 表示未删除,1 表示已删除',
       create_dt DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
       update_dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
   ) COMMENT='订单信息表';

3. 测试数据构建
   
   

配置数据同步

到目前为止，我们通过测试数据构建模拟生成业务系统数据，接下来我们需要将云数据库 RDS MySQL 版实例中业务系统数据同步到云原生数据仓库 AnalyticDB MySQL 版集群中。

1. 登录数据传输服务 DTS 控制台
   

2. 高级配置
   

3. 数据校验
   

4. 库表列配置
   

5. 任务启动
   
   

方案验证

数据已经同步到云原生数仓 AnalyticDB MySQL 版集群中，接下来通过执行复杂逻辑和使用窗口函数验证云原生数仓 AnalyticDB MySQL 版的高性能查询，并查看 SQL 查询绑定关系的资源组执行情况。

1. 执行复杂逻辑，验证云原生数仓 AnalyticDB MySQL 高性能查询

   sql 复制代码

   -- 查询每个城市的订单总数、总金额、平均订单金额，并且包括每个用户的订单统计信息
   WITH user_order_stats AS (
       SELECT
           u.user_id,
           u.user_name,
           u.city_id,
           COUNT(o.order_id) AS user_total_orders,
           SUM(o.quantity * p.product_price) AS user_total_amount,
           AVG(o.quantity * p.product_price) AS user_average_amount
       FROM
           workshop.order_info o
       JOIN
           workshop.user_info u ON o.user_id = u.user_id
       JOIN
           workshop.product_info p ON o.product_id = p.product_id
       WHERE
           o.is_delete = 0
       GROUP BY
           u.user_id, u.user_name, u.city_id
   ),
   city_order_stats AS (
       SELECT
           c.city_id,
           c.city_name,
           COUNT(o.order_id) AS total_orders,
           SUM(o.quantity * p.product_price) AS total_amount,
           AVG(o.quantity * p.product_price) AS average_amount
       FROM
           workshop.order_info o
       JOIN
           workshop.user_info u ON o.user_id = u.user_id
       JOIN
           workshop.city_info c ON u.city_id = c.city_id
       JOIN
           workshop.product_info p ON o.product_id = p.product_id
       WHERE
           o.is_delete = 0
       GROUP BY
           c.city_id, c.city_name
   )
   SELECT
       cos.city_id,
       cos.city_name,
       cos.total_orders,
       cos.total_amount,
       cos.average_amount,
       uos.user_id,
       uos.user_name,
       uos.user_total_orders,
       uos.user_total_amount,
       uos.user_average_amount
   FROM
       city_order_stats cos
   JOIN
       user_order_stats uos ON cos.city_id = uos.city_id
   ORDER BY
       cos.total_orders DESC, uos.user_total_orders DESC
   LIMIT 100;

2. 使用云原生数仓 AnalyticDB MySQL 窗口函数，进行数据分析

   • 使用排序窗口函数 ROW_NUMBER

     sql 复制代码

     -- 假设我们要计算 user_id 是 1 的用户，订单按购买金额倒序排序
     SELECT
         u.user_id,
         u.user_name,
         o.order_id,
         o.quantity,
         p.product_price,
         o.quantity * p.product_price AS order_amount,
         o.order_date,
         ROW_NUMBER() OVER (PARTITION BY u.user_id ORDER BY o.quantity * p.product_price DESC) AS rank_by_amount
     FROM
         workshop.order_info o
     JOIN
         workshop.user_info u ON o.user_id = u.user_id
     JOIN
         workshop.product_info p ON o.product_id = p.product_id
     WHERE u.user_id=1 AND o.is_delete = 0
     ORDER BY
         u.user_id, o.quantity * p.product_price DESC;

   • 使用聚合窗口函数 SUM

     sql 复制代码

     -- 假设我们要计算 user_id 是 1 的用户，按照下单时间正序排序，查看截止到每一笔累计订单金额明细
     SELECT
         u.user_id,
         u.user_name,
         o.order_date,
         o.order_id,
         o.quantity,
         p.product_price,
         o.quantity * p.product_price AS order_amount,
         SUM(o.quantity * p.product_price) OVER (PARTITION BY u.user_id ORDER BY o.order_date ASC) AS cumulative_amount
     FROM
         workshop.order_info o
     JOIN
         workshop.user_info u ON o.user_id = u.user_id
     JOIN
         workshop.product_info p ON o.product_id = p.product_id
     WHERE u.user_id=1 AND o.is_delete = 0
     ORDER BY
         u.user_id, o.order_date ASC;

   • 使用值窗口函数 LAG

     sql 复制代码

     -- 假设我们要计算 user_id 是 1 的用户，按照下单时间正序排序，查看每个订单与其前一个订单的金额差异
     SELECT
         o.order_id,
         u.user_name,
         o.order_date,
         o.quantity,
         p.product_price,
         o.quantity * p.product_price AS order_amount,
         LAG(o.quantity * p.product_price) OVER (PARTITION BY u.user_id ORDER BY o.order_date ASC) AS previous_order_amount,
         (o.quantity * p.product_price) - LAG(o.quantity * p.product_price) OVER (PARTITION BY u.user_id ORDER BY o.order_date ASC) AS amount_difference
     FROM
         workshop.order_info o
     JOIN
         workshop.user_info u ON o.user_id = u.user_id
     JOIN
         workshop.product_info p ON o.product_id = p.product_id
     WHERE
         o.is_delete = 0 AND u.user_id=1
     ORDER BY o.order_date ASC;

3. 查看 SQL 查询，根据绑定关系路由到相应资源组的执行情况
   

云原生数仓 AnalyticDB MySQL 提供了丰富的窗口函数，具体可以点击阅读原文查看方案详情。

end