前言
前面我们介绍了基于 MySQL + Tablestore 分层架构的订单系统。订单数据储存进入 Tablestore 后，用户可以使用 SDK 中的 API 访问数据，也可以继续使用 SQL 访问 Tablestore 中的数据。Tablestore 提供了多种 SQL 的接入方式，客户可以通过 DLA 访问 Tablestore，也可以利用 Tablestore 自身对 SQL 的支持能力，直接使用 SQL 访问 Tablestore。
下面本文将展示，如何直接利用 SQL 直接读取 Tablestore 中的数据。
控制台使用SQL
添加自定义列
目前使用 SQL 需要将 Tablestore 中的数据列定义为预定义列。
进入表格存储控制台，点击进入实例。
点击进入对应的表 order_contract。
点击添加预定义列。将订单表字段均设置为预定义列，设置结果如图。
创建映射表
在控制台创建 order_contract 的映射表。在实例管理页面，点击 SQL 查询。可以看到 SQL 输入框。点击加号，选择表 order_contract。点击生成SQL。
调整 SQL 的字段类型，在输入框输入以下建表 SQL。若有报错，可以尝试将 SQL 缩进至一行中，然后再执行。
create table order_contract (
oId VARCHAR(1024),
create_time VARCHAR(1024),
total_price DOUBLE,
p_brand VARCHAR(1024),
p_price DOUBLE,
pay_time BIGINT(20),
has_paid BIGINT(20),
s_id VARCHAR(1024),
p_name VARCHAR(1024),
c_id VARCHAR(1024),
c_name VARCHAR(1024),
s_name VARCHAR(1024),
p_count BIGINT(20),
p_id VARCHAR(1024),
primary key(oId));点击刷新按钮，可以看到映射表已经建好。
页面查询
根据用户id查询订单
在SQL 输入框中输入以下 SQL。
select * from order_contract where c_id = "user1370786" limit 100可以看到查询结果。
统计过去一段时间各店铺成交额
在SQL 输入框中输入以下 SQL。
select count(*) ,s_id from order_contract where pay_time > 1628784000000 group by s_id order by count(*) desc可以看到查询结果。
性能比较
在 Tablestore 中压入 1亿 条订单记录，根据不同需求场景进行性能测试。
订单搜索、数据检索
需求分析
- 根据订单id读取订单详情。
- 搜索某店铺过去一段时间的订单例表。
- 获取分页获取用户订单列表。
- 搜索某用户购买过的包含某关键字的商品。
这一类需求为订单系统中最常见的数据检索需求。对于基于买家 id、订单 id、卖家 id 的数据检索，MySQL可以通过建立索引来解决，而对于多条件组合的查询，MySQL一般很难处理，需要通过 Elasticsearch 提供数据检索能力来对外提供搜索支持。
性能对比
下表中列举了几个常见场景下的数据检索需求，以及对应的 Tablestore SQL、MySQL的性能比较。可以看到 Tablestore SQL 在基于订单id、买家id、卖家id上的搜索性能可以与 MySQL 相媲美。而在一些复杂条件的搜索场景下，Tablestore SQL 基于多元索引，提供了比 MySQL 更加强大的检索能力，这一点是单独的 MySQL 架构无法提供的。
需要说明的是，MySQL 索引需要遵守最左匹配原则，一个索引可能只能应对一个或几个需求。因此在复杂组合检索条件下，可能需要构建很多索引表才能够满足检索需求，这不仅会占用很多磁盘空间，MySQL 的维护也会变得复杂。而 Tablestore SQL 底层基于多元索引，一张索引可以应对所有数据检索需求。
需求 |
Tablestore SQL / 执行时间 |
MySQL / 执行时间 |
说明 |
根据订单id获取订单详情 |
select * from order_contract where oId = "1623228187378_user978315" 执行时间：小于50ms |
select * from order_contract where oId = "1623228187378_user978315" 执行时间：小于50ms |
|
统计某店铺在 2021 年 6 月 30 日零点以来金额在 2000 元以上的订单，按订单金额倒序取前 20 |
select oId,c_id,c_name,c_name,p_brand,p_name,total_price,s_id from order_contract where s_id = 'store995' and pay_time > 1624982400000000 and total_price > 2000 ORDER BY total_price desc limit 20 执行时间: 亚秒至秒级 |
select oId,c_id, c_name,c_name, p_brand,p_name, total_price, s_id 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 |
select oId,c_id,c_name,c_name,p_brand,p_name,total_price,s_id from order_contract where c_id = 'user2908110' and pay_time >= 1624982400000000 and total_price > 2000 order by pay_time desc limit 20 执行时间: 小于50ms |
select oId,c_id,c_name,c_name,p_brand,p_name,total_price,s_id 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日零点以来成交额在9995元以上，且商品品牌中包含特定关键字的订单，按商品单价倒序排列取前 100。 MySQL 中建立有p_price,total_price，pay_time的联合索引。 |
select oId,c_id,s_id,total_price,c_name,p_brand,pay_time,p_price from order_contract where total_price > 9995 and pay_time > 1624982400000000 and p_brand like "%品牌22%" order by p_price desc limit 100 执行时间约 100 ms |
select oId,c_id,s_id,total_price,c_name,p_brand,pay_time,p_price from order_contract where total_price > 9995 and pay_time > '2021-06-30 00:00:00' and p_brand like "%品牌22%" order by p_price desc limit 100 执行时间：分钟级 |
MySQL 在字段 p_price, total_price, pay_time 建有联合索引。 符合条件的记录数约16条 |
报表分析、运营推广
需求分析
- 统计过去一个月各店铺成交额并排序；
- 统计过去一个月各店铺成交的最大单笔订单金额；
- 统计当天成交订单数最大的 100 位客户
此类需求在订单系统的报表工作、数据分析、运营推广当中会非常常见，主要考验数据库对聚合操作的支持能力。SQL 解析结合 Tablestore 的多元索引，在此类场景下的性能远高于 MySQL。
性能对比
表格中列举了三种依赖聚合操作的场景，对于每种场景，给出了各 SQL 语句以及运行时间。Tablestore SQL 在三种场景执行时间都在亚秒级，远远好于 MySQL 性能。三种场景下，MySQL 中都建立了适合此场景的联合索引，在有索引并无需回表的情况下，统计仍需要几十秒到几分钟的时间；而需要回表时，MySQL 性能会极速衰退。MySQL 索引需要遵守最左匹配原则，在现实环境当中，很难像这里的三个场景都建立了恰当的索引，甚至并不会建立类似于 pay_time, c_id, total_price 这样的联合索引，因此，现实场景大数据下 MySQL 对此类需求的支持能力更加糟糕。多元索引不需遵守最左匹配原则，可以以一份索引覆盖所有列，因此也不存在回表问题。
可以看到，在聚合场景下，Tablestore SQL 性能远高于 MySQL。
需求 |
Tablestore SQL / 执行时间 |
MySQL / 执行时间 |
说明 |
统计2021年6月30日零点以来，各店铺成交订单数量，订单数量降序排序。 |
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日零点以来，各店铺成交订单数量、总成交额、平均成交额、最大订单金额，按成交额降序排序。 |
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个客户。 |
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。 |
总结
Tablestore 支持 SQL 查询。这使得用户开发工作、程序迁移工作，变得更加简单。SQL 解析，结合 Tablestore 的多元索引，为用户提供了更加强大的 SQL 查询、检索能力，其数据检索能力，远强于 MySQL。