Boost MySQL Analytics with AnalyticDB and DTS: A Step‑by‑Step Guide

Introduction

In the era of data‑driven decision making, a high‑performance data analysis engine not only provides efficient data support but also resolves traditional OLTP query performance bottlenecks and data inconsistency challenges. This article introduces how AnalyticDB MySQL combined with DTS can solve MySQL data analysis performance problems.

Key Advantages of AnalyticDB MySQL

Simple and Easy to Use : Fully compatible with MySQL protocol and supports various SQL standards, including window, funnel, retention, and path analysis functions.

High Performance : Real‑time visibility of massive data writes, strong consistency, and query speeds many times faster than traditional relational databases.

Low Cost : Separation of storage and compute, elastic scaling, and tiered storage reduce both compute and storage expenses.

Broad Application Scenarios : Business reporting, interactive operational analysis, and real‑time data warehousing.

Solution Overview

One‑Click Acceleration to build an enterprise‑level data analysis platform using AnalyticDB MySQL.

Resource Deployment Steps

Create a VPC for private networking.

Provision a Cloud Database RDS MySQL instance for OLTP workloads.

Provision an AnalyticDB MySQL cluster as the target for data synchronization.

Set up Data Management Service (DMS) to manage connections.

Create Data Synchronization Accounts and Resource Groups

In the AnalyticDB console, create a high‑privilege account (e.g., adb_admin) and an Interactive resource group (e.g., xihe_mpp) with appropriate ACU settings, then bind the account to the resource group.

Similarly, create a high‑privilege account (e.g., db_user) in the RDS console.

Build Test Data

Use DMS to generate test data for the following tables: city_info, user_info, product_info, and order_info.

-- Create database
CREATE DATABASE IF NOT EXISTS workshop;

-- User information table
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='用户信息表';

-- City information table
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='城市信息表';

-- Product information table
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='产品信息表';

-- Order information table
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='订单信息表';

Configure Data Synchronization

Log in to the DTS console, create a synchronization task from the RDS MySQL instance to the AnalyticDB MySQL cluster, enable full and incremental checksum verification, and set column mapping as required.

Solution Validation

After data sync completes, execute complex SQL queries and window‑function analyses on AnalyticDB MySQL to verify performance advantages.

-- Complex query with CTEs
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;

Running the same query on the RDS MySQL instance shows significantly longer execution time, confirming AnalyticDB MySQL’s superior performance on large datasets.

Window Function Examples

1. ROW_NUMBER() – rank orders by amount per user.

SELECT u.user_id, u.user_name, o.order_id, o.quantity, p.product_price,
       o.quantity * p.product_price AS order_amount,
       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;

2. SUM() OVER – cumulative order amount per user.

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;

3. LAG() – compare each order amount with the previous one.

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;

Query Execution Monitoring

In the AnalyticDB console, filter completed queries by resource group, user, and database to view execution details and verify that queries are routed to the Interactive resource group.