前言
在订单系统中，基于订单数据对客户和商家商品进行画像分析是一种常见的需求。常见的分析需求有：
- 基于主键、分区键数据的条件组合检索，例如获取某用户最近 30 的订单列表。
- 根据非主键列、分区键的条件组合检索工作，例如查询过去一天异常订单列表、查询过去一天成交额最大的10 笔订单。
- 聚合统计类需求，比如统计某店铺过去一个月各商品销售额排名；统计双十一期间销售额前 10 的店铺；统计双十一期间某店铺每天订单数等等。
客户会更倾向使用 SQL 的方式直接查询这些数据，但是传统的 MySQL 架构却无法承担这样的需求的，存在的问题有：
- 即使采用了分布式的 MySQL 框架解决了数据存储和一些简单查询上的问题，但是涉及到数据分析时的复杂 SQL 时，这种架构是无能为力的。因为分布式 MySQL 意味着其在非分区键上的索引能力会非常差，一般情况下，分布式 MySQL 架构会采用多数据备份的方式支持不同的分区键，但画像分析、数据检索场景可能涉及到需要进行检索的字段非常多，采用多数据备份方式是不现实的。
- 数据分析时的 SQL 可能会非常复杂，如果直接在 MySQL 库上进行查询，会对该库造成性能压力，可能会影响线上查询。
- 可以将 MySQL 中的数据导入 HIVE 等分布式开源数据库。但这样就增加了运维成本，需要专业的大数据开发运维团队才能维护这一套系统；另外 HIVE 在线分析速度也并不理想。
而 Tablestore 结合云原生数据湖分析（DLA），其对 SQL 的支持能力，可以使得上述需求变得格外简单。下面我们将：
- 对 DLA 进行介绍。
- 展示开通 DLA 服务并进行关联 Tablestore的配置。
- 使用程序以读 MySQL 方式读取 DLA。
- 使用 DLA 进行数据分析。
DLA 简介
云原生数据湖分析 DLA（Data Lake Analytics）是无服务器（Serverless）化的云上交互式查询分析服务，支持通过 Presto 和 Spark 引擎分析多种数据源中的数据。其中 Serverless Presto 是云原生数据湖团队基于 Presto 打造的交互式分析引擎，相比于 HIVE 它在执行速度上有很大的优势，特别适合用来做 Adhoc 查询、BI 分析、轻量级 ETL 等数据分析工作。详细介绍见 Serverless Presto 概述。
将 Tablestore 中的数据映射成为 DLA 中的 MySQL 数据库，此时可以直接通过 SQL 的方式在 DLA 中进行数据分析，也可以在程序中以读取 MySQL 数据库的方式读取 Tablestore 中的数据。而此时的 SQL 执行依赖于 DLA 底层的分布式计算框架 Serverless Presto，不再局限于分布式 MySQL 数据库，也不再局限于传统数据库索引，且可以算子下推，利用 Tablestore 中的多元索引。此方案极大的提高了数据分析的可行性和效率。此时系统架构图如图：
DLA 服务配置
DLA 服务开通
在阿里云官网，进入DLA首页。点击立即购买，开通DLA服务。服务开通后，在 DLA 首页点击管理控制台进入可以看到以下页面。
账号配置
进入DLA首页，点击账号管理，点击创建子账号，创建账号，设置密码。
设定账号名称、密码。
此时账号创建完成，可以在账号管理页面看到刚刚创建的页面。
账号赋权
点击 Serverless Presto -> SQL 执行，在此页面可以对刚刚创建的账号进行赋权，语法同 MySQL 语法。可参考：Grant。本文章中使用语句为
grant all on *.* to li_s1831126559450753其中 li_s1831126559450753 为文中创建的子账号账号名。用户可以根据实际情况确认执行语句。
点击 Serverless Presto ->SQL 访问点，这里可以看到 DLA 的公网访问地址以及端口。
继续在这个页面，点击右侧的设置白名单，本文章中风险极低，白名单设为0.0.0.0/0，开放所有 ip 访问权限。
创建映射表
然后点击右侧登录到DMS，在这里配置 DLA 关联 Tablestore 的关联表。
将 Tablestore 的实例映射成 DLA 的一个 DataBase 实例。建立 DLA 的 Database 映射前，首先需要在 Tablestore 中创建实例。执行下面的 SQL，其中 catalog 填入 ots，表示要建立的是一个 Tablestore 映射实例。 Location 填入需要填入的 Tablestore 在 VPC 网络下的域名。Instance 为 Tablesstore 的实例名。第一行中的的test_tablestore 为创建出的库名。
CREATE SCHEMA test_tablestore WITH DBPROPERTIES (
catalog = 'ots',
location = 'https://test-20210609.cn-hangzhou.vpc.tablestore.aliyuncs.com',
instance = 'test-20210609'
);SQL 执行后，可以在 DMS 管理界面看到名为 test_tablestore 新的映射库。
然后在新建的映射库中建外部表，建表语句如下：
CREATE EXTERNAL TABLE order_contract (
oId int NOT NULL ,
c_id String NOT NULL ,
c_name String NOT NULL ,
create_time String,
has_paid int,
p_brand String NOT NULL ,
p_count int,
p_id String,
p_name String,
p_price double,
pay_time int,
s_id String,
s_name String,
total_price double,
primary key(oId)
);执行后，可以看到外部表 order_contract 。Tablestore 中的 order_contract 表结构和数据已经在 DLA 中被映射出来了。可以使用 SQL 语句对 order_contract 表中的数据进行查询分析。
程序读取 DLA
下面的将通过 Java 程序使用 Mybatis 以读 MySQL 库的方式读取 Tablestore 中的数据。
创建 SpringBoot 程序，使用 MyBatis 连接 DLA，其代码和配置与读 MySQL 数据库一致。下图是程序代码结构图，可见，程序结构以及读取数据方式和普通读取 MySQL 的 Springboot 程序完全一致。
数据库配置如下，其中jdbcurl中要填入在Serverless Presto ->SQL访问点页面看到的公网域名和端口，在本例中为1k1cpg1qpk1-2kle6xrexz1q.cn-hangzhou.datalakeanalytics.aliyuncs.com:10000。实例名填入在DLA中建立的 Schema 的名字，即映射库的名字，在本例中为 test_tablestore。
dlasource:
jdbcUrl: jdbc:mysql://1k1cpg1qpk1-2kle6xrexz1q.cn-hangzhou.datalakeanalytics.aliyuncs.com:10000/test_tablestore
user: li_s1831126559450753
password:
driverClass: com.mysql.jdbc.Driver在 MyBatis 的 Mapper 文件中写入如下 SQL 。
<select id="getOrderByConsumers" resultType="com.aliExample.bean.ConsumerOrderCount" >
select count(*) as count,c_id cId from order_contract group by c_id order by count desc
</select>启动程序运行对应查询，可以得到正确结果。可见，可以像访问 MySQL 数据库一样访问 DLA 中的 Tablestore 数据。
程序地址：
数据分析
使用程序在源 MySQL 中写入 1亿 条记录模拟订单系统，DTS 将记录同步到 Tablestore 。此时，在 DLA 端，可以查询到这 1亿 条记录。
Tablestore 中多元索引如下图
基于买家、卖家的数据检索
需求分析
基于买家视角、卖家视角进行数据检索是订单系统中最常见的需求，例如
- 检索某买家用户过去30天下过的订单
- 检索某买家用户过去30天交易额大于指定值的订单
- 检索某卖家在过去一个月成交的订单，以成交时间倒序排列
在分布式 MySQL 中读取数据一般受限于分区键，一般订单系统下分布式 MySQL 分区键一般为 买家id 或者 卖家id，在此类场景下一般可以直接利用 MySQL 分区键以及以分区键起始的联合索引进行检索。而 Tablestore 中多元索引也支持此类需求，下面本文举几个例子来对此场景下两者性能进行比较。
性能对比
表中列举了基于买家视角、卖家视角检索的几个例子，并比较 DLA 与 MySQL 的执行性能。可以看到，在以主键、分区键起始的组合查询的场景下，MySQL 在性能上仍具有明显优势。通过买家 id 字段或卖家 id 字段，建立联合索引，可以有效过滤掉不符合条件的数据。
但这里使用 SQL 查询 Tablestore 仍可以作为分层存储中读写分离方案进行应用。通过亚秒级完成查询工作，可以将读流量从 MySQL 数据库上转移到 Tablestore 中，减轻数据库压力，使数据库专注于写的操作。
另外，值得一提的是，一般实际场景中，参与组合查询的字段会非常多，比如十几个字段。而 MySQL 索引需要遵循最左匹配原则，因此，在一种场景下适用的索引，在另一种场景下，就很可能不适用。因此，实际场景中，一般根据分区键建立索引，不会建立本场景下 s_id,pay_time,total_price 这样的联合索引。实际 MySQL 多组合查询性能还要比表格中的性能差一些。
需求 |
DLA SQL / 执行时间 |
MySQL / 执行时间 |
说明 |
统计某用户 2021 年 6 月 30 日零点以来金额在 2000 元以上的订单，按支付时间倒序取前 20 |
/*+ots-index-first=auto,ots-pushdown-enabled=true,ots-groupby-pushdown-enabled=true*/ select * from order_contract where c_id = 'user2908110' and pay_time >= 1624982400000000 and total_price > 2000 order by pay_time desc limit 20 执行时间 300ms |
select * from order_contract where c_id = 'user2908110' and pay_time >= '2021-06-30 00:00:00' and total_price > 2000 order by pay_time desc limit 20 执行时间小于 50ms |
MySQL 在字段 c_id 上建立索引。 user2908110客户共有 14 张订单。 |
统计某店铺在 2021 年 6 月 30 日零点以来金额在 2000 元以上的订单，按订单金额倒序取前 20 |
/*+ots-index-first=auto,ots-pushdown-enabled=true,ots-groupby-pushdown-enabled=true*/ select * from order_contract where s_id = 'store995' and pay_time > 1624982400000000 and total_price > 2000 ORDER BY total_price desc limit 20 执行时间在亚秒级 |
select * from order_contract where s_id = 'store995' and pay_time > '2021-06-30 00:00:00' and total_price > 2000 ORDER BY total_price desc limit 20 执行时间在亚秒级 |
符合筛选条件的记录数1278。MySQL 建有 s_id,pay_time,total_price 联合索引。 |
统计某店铺在 2021 年 6 月 30 日零点以来金额在 2000 元以上的订单，按订单金额倒序取前 20（符合条件的订单记录更多） |
/*+ots-index-first=auto,ots-pushdown-enabled=true,ots-groupby-pushdown-enabled=true*/select * from order_contract where s_id = 'store996' and pay_time > 1624982400000000 and total_price > 2000 ORDER BY total_price desc limit 20 执行时间亚秒到秒级 |
select * from order_contract where s_id = 'store996' and pay_time > '2021-06-30 00:00:00' and total_price > 2000 ORDER BY total_price desc limit 20 执行时间在亚秒级 |
符合条件的订单 52w，MySQL中建有s_id,pay_time,total_price 联合索引。 |
基于订单金额、状态等的检索
需求分析
在订单系统中，有一些组合检索需求是不涉及主键、分区键的。比如，查找过去一段时间某品牌成交额最大的十笔订单、查找过去一天状态异常的订单。分布式 MySQL 中建立的索引，一定是以分区键起始的联合索引，而 MySQL 索引需要遵循最左匹配原则，因此此类查询在分布式 MySQL 中很难被支持。
性能对比
根据表格可知，在一些场景下，MySQL恰当建立索引，可以支持此类检索工作；但存在一些场景，比如模糊匹配的组合搜索，根据非筛选条件排序等，即使建立了索引，MySQL性能也很难达到需求要求。
另外分布式 MySQL 还存在两个问题，其一，分布式 MySQL 更适合建立以主键、分区键起始的联合索引，并不适合对任意字段建立索引；其二，MySQL 索引需要遵守最左匹配原则，在组合检索场景下，索引将很难覆盖所有情况。例如，一张表有A、B、C、D四个字段需要进行检索，针对检索A、B、C字段的场景建立联合索引ABC，但当用户搜索A、C或者B、C时，联合索引ABC就会衰退或失效，此时若要支持对于A、C和B、C的检索，还需要建立其他索引。对于可能存在很多组合检索的订单场景，很难通过一个一个建立索引来覆盖所有检索需求。
Tablestore 多元索引，不需遵守最左匹配规则，可以通过建立一份索引，覆盖任意组合的检索需求，这一点是分布式 MySQL 难以匹敌的。在检索条件更加多变的非主键、分区键检索场景下，DLA SQL 优于 MySQL。
需求 |
DLA SQL / 执行时间 |
MySQL / 执行时间 |
说明 |
查找过去一段时间某品牌成交额最大的十笔订单 |
/*+ots-index-first=auto,ots-pushdown-enabled=true,ots-groupby-pushdown-enabled=true*/ select * from order_contract where p_brand = "品牌22" and pay_time >= 1624982400000000 order by total_price desc limit 100 执行时间，亚秒级 |
select * from order_contract where p_brand = "品牌22" and pay_time >= '2021-06-30 00:00:00' order by total_price desc limit 100 执行时间，亚秒级 |
符合筛选条件的记录有 2936 条。MySQL 建有 p_brand,pay_time 的联合索引。 |
|
搜索2021年6月30日零点以来成交额在2000元以上，且商品品牌中包含特定关键字的订单，按商品单价倒序排列取前 1000。 MySQL 中建立有p_price,total_price，pay_time的联合索引。 |
/*+ots-index-first=auto,ots-pushdown-enabled=true,ots-groupby-pushdown-enabled=true*/ select * from order_contract where total_price > 2000 and pay_time > 1624982400000000 and p_brand like "%牌22%" order by p_price desc limit 1000 执行时间秒级 |
select * from order_contract where total_price > 2000 and pay_time > '2021-06-30 00:00:00' and p_brand like "%牌22%" order by p_price desc limit 1000 执行时间分钟级 |
符合筛选条件的记录有162887条，MySQL 中建立有p_price,total_price,pay_time 的联合索引。 |
筛选商品品牌包含关键字，店铺名称包含关键字的订单，按支付时间倒序排列 |
select * from order_contract where p_brand like "%牌22%" and s_name like "%店292%" order by pay_time desc limit 100 执行时间亚秒级 |
select * from order_contract where p_brand like "%牌22%" and s_name like "%店292%" order by pay_time desc limit 100 执行时间分钟级 |
符合筛选条件的记录有5126条，MySQL 在 pay_time 字段上建立索引 |
报表分析、运营推广
需求分析
- 统计过去一个月各店铺成交额并排序；
- 统计过去一个月各店铺成交的最大单笔订单金额；
- 统计当天成交订单数最大的 100 位客户
此类需求在订单系统的报表工作、数据分析、运营推广当中会非常常见，主要考验数据库对聚合操作的支持能力。DLA 结合 Tablestore 的多元索引，开启算子下推，其在此类场景下的性能远高于 MySQL。
性能对比
表格中列举了三种依赖聚合操作的场景，对于每种场景，给出了 DLA 和 MySQL 的 SQL 语句以及运行时间。DLA 开启算子下推后，三种场景执行时间都在亚秒到秒级，远远好于 MySQL 性能。三种场景下，MySQL 中都建立了适合此场景的联合索引，在有索引并无需回表的情况下，统计仍需要几十秒到几分钟的时间；而需要回表时，MySQL 性能会极速衰退。MySQL 索引需要遵守最左匹配原则，在现实环境当中，很难像这里的三个场景都建立了恰当的索引，甚至并不会建立类似于 pay_time, c_id, total_price 这样的联合索引，因此，现实场景大数据下 MySQL 对此类需求的支持能力更加糟糕。多元索引不需遵守最左匹配原则，可以以一份索引覆盖所有列，因此也不存在回表问题。
可以看到，在聚合场景下，DLA SQL 性能远高于 MySQL。
需求 |
DLA SQL / 执行时间 |
MySQL / 执行时间 |
说明 |
统计2021年6月30日零点以来，各店铺成交订单数量，订单数量降序排序。 |
/*+ots-index-first=auto,ots-pushdown-enabled=true,ots-groupby-pushdown-enabled=true*/ select s_id, count(*) as c from order_contract where pay_time >= 1624982400000000 group by s_id order by c desc 执行时间: 亚秒至秒级 |
select s_id, count(*) as c from order_contract where pay_time >= '2021-06-30 00:00:00'group by s_id order by c desc 执行时间约 25s |
MySQL 建有 pay_time、s_id联合索引，无需回表。时间范围内记录数约1200w。 |
统计2021年6月30日零点以来，各店铺成交订单数量、总成交额、平均成交额、最大订单金额，按成交额降序排序。 |
/*+ots-index-first=auto,ots-pushdown-enabled=true,ots-groupby-pushdown-enabled=true*/ select s_id, count(*),sum( total_price) as c, avg( total_price),max( total_price) from order_contract where pay_time >= 1624982400000000 group by s_id order by c desc 执行时间 ：亚秒至秒级 |
select s_id, count(*),sum( total_price) as c, avg( total_price),max( total_price) from order_contract where pay_time >= '2021-06-30 00:00:00' group by s_id order by c desc 执行时间在一个小时以上 |
MySQL 建有 pay_time、s_id联合索引，需要回表。时间范围内记录数约1200w。 |
统计2021年6月30日零点以来，下单金额最高的100个客户。 |
/*+ots-index-first=auto,ots-pushdown-enabled=true,ots-groupby-pushdown-enabled=true*/ SELECT c_id ,sum(total_price) as a FROM order_contract where pay_time >= 1624982400000000 group by c_id order by a desc limit 100 执行时间：秒级 |
SELECT c_id ,sum(total_price) as a FROM order_contract where pay_time >= '2021-06-30 00:00:00' group by c_id order by a desc limit 100 执行时间约2分半 |
MySQL 建有pay_time, c_id, total_price 的联合索引，无需回表。时间范围内记录数约1200w。 |
DLA 性能优势总结
根据前面的测试，我们可以看到，首先，Tablestore使用多元索引结合 DLA，可以作为 MySQL 的读库，帮助前置的分布式 MySQL 分担读压力，实现读写分离。而在分布式 MySQL 并不擅长的场景如非主键分区键多组合检索、聚合统计下，Tablestore 使用多元索引结合 DLA 的性能远远好于 MySQL，它可以补齐 MySQL 在检索以及聚合统计上面的短板，且成本更低，更易于维护。
但需要注意，Tablestore 中数据同步进入多元索引存在 10s 左右的延时，适合对实时性、一致性要求不是极其严格的场景，如订单检索。
总结
阿里云提供的 DLA 服务，结合表格存储 Tablestore，不仅支持基于 SQL 语句的数据分析工作，而且性能上也要远远好于传统的分布式 MySQL 方案。这种方案不仅可以统计实时数据，不影响 MySQL，而且运维成本低，使用者的学习成本极低。DLA 还支持程序以连接 MySQL 的方式连入，对于程序适配极其便捷。