MySQL到SLS：一站式查询分析可视化场景介绍_SLS日志服务_实战派

本文主要介绍如果使用日志服务对MySQL的导入数据进行一站式查询分析可视化。

目标读者

本文主要面向业务系统中的数据分析、运维人员。

背景介绍

在业务系统中，MySQL经常存储着核心的业务数据。例如电商系统中MySQL存储着核心的订单数据，MySQL主要关注数据的一致性，对于订单的分析运营并不擅长，将数据导入SLS，让SLS来做更擅长的分析、运维、可视化等功能。

方案架构

自建数据库

自建数据库主要包括在ECS上自建的数据库或者客户环境部署的数据库，对于ECS自建数据库，往往是部署在VPC网络下，日志服务提供了直接通过VPC链接ECS自建数据库的能力。对于云下环境部署的数据库，出于安全考虑一般不会提供公网的访问链接，可以使用SLS的Logtail插件进行MySQL数据的导入，对于Logtail采集MySQL数据可以参考《采集MySQL查询结果》，这了不在赘述。

RDS数据库

对于RDS数据库，日志服务数据接入提供了原生的网络连通能力，支持直接在VPC网络下的连通，不走公网流量，VPC方式是比较推荐的使用方式。

方案优势

日志服务SLS是云原生观测和分析平台，为Log、Metric、Trace等数据提供大规模、低成本、实时的平台化服务。日志服务是提供一站式数据采集、加工、查询与分析、可视化、告警、消费与投递等功能。全面提升在研发、运维、运营、安全等场景的数字化能力。SLS在数据导入方面提供了丰富的数据源，如OSS导入、MaxCompute、MySQL、SQL Server等数据源，本文主要以MySQL数据源为例，来介绍如何使用SLS的功能来对导入的MySQL数据进行分析。

首先来看下SLS的一些功能简述。

高性能查询分析

日志服务提供了高性能数据的查询分析能力，支持秒级处理十亿级数据。

支持灵活的分析语法，支持SQL92语法，与MySQL的数据查询无缝切换。使用参考分析概述。
对于数据量巨大的情况，日志服务提供了独享SQL，支持千亿级数据的高性能分析。适合分析性能要求高的场景、大规模业务数据分析场景、长周期数据分析场景。可以有效的弥补MySQL分析数据能力不足。
提供了丰富的内置机器学习函数，可以通过机器学习函数调用机器学习算法，分析某一字段或若干字段在一段时间内的特征。
支持Scheduled SQL，支持对海量数据的Roll Up。使用可以参考Scheduled SQL。

报表可视化

日志服务提供了丰富的可视化功能，支持创建仪表盘、统计报表、和第三方可视化工具。通过SQL查询出的结果，选择合适的统计图表类型来展示分析结果。

仪表盘作为图表的聚合容器，支持自动刷新、报表订阅、设置告警等功能。可以作为企业大盘来使用。

支持丰富的图表类型，包括表格、线图、流图以及灵活的变量配置和自定义事件等功能。

数据异常告警

日志服务提供了一站式的告警监控、降噪、事务管理、通知分派的智能运维平台。商务运营人员（市场、客户运营、财务人员）可以通过告警功能对各种数据库或者指标（例如用户数、活跃度、广告点击率、商品转化率）等进行持续监控；识别指标的变化情况或者异常并相应，以提升运营效率、减少业务风险或财务风险。

方案实施

前提条件：导入MySQL数据到SLS

这里我们以MySQL订单数据导入为例，来对订单的数据进行到分析、可视化、告警，MySQL导入可以参考导入MySQL数据，这里假设订单表的结构如下：

这里主要是以演示为目的，所以不涉及非常复杂的情况，不包含购物车等，主要包含单品的购买、用户ID、金额、是否使用优惠券，订单时间等。接下来的分析也会以此为基础来介绍可以使用SLS的分析场景。文中涉及的订单表中数据为模拟数据。

CREATE TABLE IF NOT EXISTS orders (
        id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL COMMENT '用户ID',
    sku_id BIGINT COMMENT 'sku ID',
    sku_category VARCHAR(100) COMMENT 'sku 分类',
    sku_count INT COMMENT '商品数量',
    sku_price INT COMMENT '商品价格(分)',
    total_price INT COMMENT '商品总价(分)',
    supplier_type VARCHAR(100) COMMENT '供应商类型',
    pay_id VARCHAR(50) COMMENT '支付流水号',
    pay_channel VARCHAR(20) COMMENT '支付渠道',
    pay_amount INT COMMENT '支付总价（分）',
    coupon_id INT DEFAULT 0 COMMENT '优惠券ID，0表示不使用优惠券',
    buy_channel VARCHAR(50) DEFAULT '' COMMENT '购买渠道',
    state TINYINT(3) NOT NULL COMMENT '订单状态',
    client_ip VARCHAR(15) DEFAULT '' COMMENT '客户端IP',
    create_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间' 
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

在数据导入完成后，首先需要配置索引，配置索引可以使用SLS的自动生成索引功能，SLS的索引可以和数据库表中字段一一对应，SLS具有比MySQL更强的分析能力。

分析场景

场景1：查询分析可视化：电商销售分析

SLS支持完整的SQL92语法，接下来的场景会使用到SQL语法。

查询分类订单数，订单分类销售额

数据库中含有订单分类sku_category和销售额total_price等字段。在订单中可以分析哪些分类的销售单数比较高，哪些分类的销售额比较高，并且可以通过饼图可视化的方式展示出来。

分析语句为：

* | SELECT sku_category, COUNT(*) as cnt GROUP BY sku_category ORDER BY cnt desc limit 10000

查询后默认以表格形式展示，显示每个分类下的订单数分布。

接下来在右侧的通用配置->图表类型选择饼图类型，通过饼图可以直观的看出哪些分类的销售单数比较高。从图中可以看出coffee、drinks、women clothes的分类下单次数比较多，也就可以分析出这这些商品的分类比较受欢迎。

类似的，也可以计算按照分类来统计销售额哪些分类占比比较高。

订单趋势

比如我们可以查看某类商品的下单趋势，使用线图来展示，通过图示可以看出在16点后有一个峰值，如果这段时间出现了定时抢购之类的运营活动，可以认为这个峰值是正常的。

同样也可以通过流图来展示，每个分类的商品的下单趋势

下单热点地域

也可以使用ip_to_city_geo的函数，获取下单客户端的IP，然后通过聚合函数可以获取每个城市下单的数量，通过日志服务的热力图来展示下单多的热区。热力图对电商的备货有很高的参考价值。

供应商分析

通过按照供应商类型聚合查看哪些供应商的下单比例比较高，同时也可以通过时间函数，来分析每个供应商的订单销售趋势。

还可以对两家供应商比如company1，company3进行订单数的趋势对比。

高频购买用户

同时也可以对购买用户进行统计，比如拉取下单前十的用户。

* | SELECT user_id, COUNT(*) as cnt GROUP BY user_id ORDER BY cnt DESC LIMIT 10

大盘订阅

上面的查询分析可视化的图表可以选择添加到仪表盘中，并且支持定时将仪表盘发送到邮件、微信、钉钉、飞书等，可以定时查看销售情况。

场景2：智能机器学习算法

日志服务提供了功能丰富的时序分析算法，支持时序序列的预测、异常检测、变点检测、折点检测、多周期估计算法等。在订单中可以使用预测与异常检测函数来对下单的数量进行预测和异常检测；

示例SQL：

* | select ts_predicate_simple(stamp, value, 6) from (select to_unixtime(create_time) - to_unixtime(create_time) % 60 as stamp, count(*) as value from log GROUP BY stamp order by stamp)

然后选择时序图的可视化方式，可以清晰的看到算法计算出的有异常的点，可以通过对异常的点的分析，可以了解订单系统的健康状态。

场景3：数据加工

日志服务提供可托管、可扩展、高可用的数据加工服务。数据加工服务可用于数据的规整、富化、流转、脱敏和过滤。

在本例中，订单的SKU分类我们由于数据库中保存的是英文，展示出来并不直观，可以使用数据加工对字段进行富华，在富华的过程中对英文进行翻译，将sku_category翻译成sku_category_cn，然后保存加工结果到另外一个logstore。数据加工语法示例如下，主要用到了e_dict_map函数。

e_dict_map(
    {
        "fresh foods": "生鲜",
        "bedding": "床上用品",
        "women clothes": "女装",
        "coffee": "咖啡",
        "electrical": "电器",
        "home fabric": "家居",
        "drinks": "饮料",
        "men closthes": "男装",
        "tea": "茶",
        "*": "其他",
    },
    "sku_category",
    "sku_category_cn",
)

通过对加工后的logstore进行查询，可以看出中文分类下的订单分类。

场景4：异常数据告警

通过日志服务的告警功能，在订单数据中，可以认为每天同一时段的下单数基本保持一致，比如每天晚上6点左右和11点左右都是下单高峰期，如果某一时刻的数据环比昨日下降或者增长过多，则需要引起关注，可以将告警消息通知到运维或者运营人员。

下单总数环比昨日陡增陡降告警

比如查询15分钟的订单数据跟昨天15分的订单数据进行对比。

* | SELECT diff[1] as today, diff[2] as yesterday, ((diff[3] - 1) * 100) as ratio FROM (SELECT compare(cnt, 86400) AS diff FROM (SELECT COUNT(*) AS cnt FROM log))

使用单值图的同环比可以展示订单数的变化。

在查询框后面可以选择另存为告警->新版告警。

告警配置如下：

告警触发后钉钉通知的效果如下：

同样的利用同环比我们可以对钉钉在过去15分钟与上一个15分钟相比，有没有陡增或者陡降。同样也可以利用机器学习函数发现订单中数量或者金额是否发生显著变化。

参考

日志服务（SLS）：https://www.aliyun.com/product/sls
导入MySQL数据：https://help.aliyun.com/document_detail/407399.html
数据加工概述：https://help.aliyun.com/document_detail/125384.html
日志服务告警：https://help.aliyun.com/document_detail/209951.html
开启SQL独享版：https://help.aliyun.com/document_detail/223777.html
日志服务机器学习语法与函数：https://help.aliyun.com/document_detail/93023.html
欢迎扫群加入阿里云-日志服务（SLS）技术交流或关注公众号，获取第一手资料与支持