开发者社区> 李欣529> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

表格存储最佳实践:使用多元索引加速 SQL 查询

简介: 表格存储(Tablestore)在 2022 年 5 月正式发布了 SQL 商业化版本,业务上只需要在数据表上建立映射关系,就可以基于 SQL 引擎方便地对表格存储中的数据进行访问和计算,大大地降低了用户的学习成本。
+关注继续查看

表格存储(Tablestore)在 2022 年 5 月正式发布了 SQL 商业化版本,业务上只需要在数据表上建立映射关系,就可以基于 SQL 引擎方便地对表格存储中的数据进行访问和计算,大大地降低了用户的学习成本。下面以一个电商订单场景为例,介绍 SQL 的使用方式和加速策略。


场景介绍

某电商平台需要搭建订单管理系统,有一亿条订单明细数据保存在表格存储 Tablestore 的订单表 orders 中,用户创建了订单表的映射表,现需要通过 Tablestore SQL 来实现如下几个查询需求:


映射表结构

CREATE TABLE `orders` (
    `order_id` VARCHAR(1024) # 订单号,
    `o_price` DOUBLE # 订单金额,
    `o_time` BIGINT(20) # 下单时间,
    `customer_name` MEDIUMTEXT # 顾客姓名,
    `merchant_id` MEDIUMTEXT # 商家号,
    `product_name` MEDIUMTEXT # 商品名,
    PRIMARY KEY(`order_id`)
);

查询需求

  • 通过订单号查询订单。
  • 查询顾客一个月的购买记录。
  • 根据商品名检索订单。
  • 计算商家销售额排名。


无索引的 SQL 查询

当订单表 orders 上没有索引时使用 SQL 查询,SQL 引擎会采用 scan table + filter 的执行策略。

  1. 基于主键列的点查、范围查询,SQL 引擎扫描的数据量较小,返回速度较快,可以支持非常高的并发。例如上述订单场景的第一个查询需求,根据订单号 order_id 查询某一条订单明细。
select * from `orders` where order_id = '000017f7864f43608995f16e8a837b64'

image

  1. 当需要根据属性列字段来组合查询时,SQL 引擎扫描的数据会非常大,这会导致 SQL 查询性能下降,极端情况下可能会超过服务的限制,导致抛出 scan rows exceeds quota 错误。例如上述的第二查询需求,查询某顾客一个月内的购买记录,需要根据顾客姓名 customer_name 和下单时间 o_time 的范围来查询。SQL 引擎通过扫表的方式查询使得耗时急剧上升,如下示例:
select * from `orders` where customer_name = '顾客380' and o_time between 1652688493000 and 1655366893782 limit 20;

image


基于多元索引的 SQL 查询


什么是多元索引

多元索引是建立数据表上的,采用倒排索引和列式存储等索引结构,提供了多字段组合查询、全文检索、地理位置查询等能力,同时可以支持轻量级的统计聚合。在数据表上建立多元索引后,SQL 引擎将自动选择最优的索引策略来大幅度提高查询性能,完全避免了 scan table 的查询方式带来的问题,同时能够支持更多的查询功能,例如全文检索。还是以上文的订单场景为例,来看看多元索引适用于哪些 SQL 查询场景。


创建多元索引

为了实现 SQL 查询加速和功能扩展,首先需要在 Tablestore 控制台上创建一个多元索引,多元索引创建后需要先等待表中的存量数据同步到索引中。

image


场景一:多字段组合查询

当需要使用多个属性列字段组合筛选数据时,利用多元索引的倒排索引特性,可以加速 SQL 语句的执行速度,避免整表扫描。例如查询顾客一个月的购买记录,可以看出同样的 SQL 查询耗时下降了 20 倍以上。

select * from `orders` where customer_name = '顾客380' and o_time between 1652688493000 and 1655366893782 limit 20;

image


场景二:统计聚合

查询场景中需要对某个字段做聚合计算(Sum、Avg 等)或者按照某个字段进行分组时,SQL 引擎会利用多元索引的轻量级聚合能力。例如计算商家的销售额排名,需要按照商家号 merchant_id 分组并对订单金额 o_price 计算求和。

select merchant_id,sum(o_price) as sales from `orders` group by merchant_id order by sales desc limit 3;

image


场景三:全文检索

全文检索是多元索引对 SQL 查询能力的扩展,当查询场景中需要对某个属性列的值进行全文检索,则需要依赖多元索引提供的分词和匹配查询功能。例如根据商品名检索订单,搜索商品名 product_name 中包含了 “笔” 的订单。

select * from `orders` where text_match(product_name, "笔", "or", "1") limit 10;

image


总结

多元索引提供了在百亿数据规模下毫秒级检索的能力,当 SQL 查询场景中需要使用到多字段组合查询、统计聚合、全文检索等,通过在数据表上创建多元索引,能够带来极大的性能提升。更多关于 Tablestore SQL 的使用姿势和多元索引的功能介绍,欢迎参考表格存储 Tablestore 官方文档,或者加入“表格存储技术交流群 - 2”咨询,群内提供免费的在线专家服务,欢迎扫码加入,群号 23307953。

image

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
C#编程-97:索引器在类中的使用
C#编程-97:索引器在类中的使用
73 0
slmgr激活提示执行slui 0x2a 0xC004E015
slmgr激活提示执行slui 0x2a 0xC004E015
1155 0
rancher使用阿里云容器服务NAS存储
在docker容器编排管理工具中,有许多出色的工具,如阿里云容器服务Kubernetes,rancher等等。2019年云栖大会期间,Rancher和阿里云在容器领域合作再度升级,Rancher v2.3及之后产品将集成阿里云开放云原生应用中心(Cloud Native App Hub),企业可以在此基础上实现容器应用一键部署。
2611 0
k8s使用glusterfs实现动态持久化存储
简介 本文章介绍如何使用glusterfs为k8s提供动态申请pv的功能。glusterfs提供底层存储功能,heketi为glusterfs提供restful风格的api,方便管理glusterfs。
2157 0
表格存储如何在控制台使用多元索引(SearchIndex)功能
# 背景: 多元索引(SearchIndex)是TableStore 2018年重点打造的核心能力,目的是为在线数据平台(TableStore)提供丰富的查询能力, 目前提供了多种索引能力,包括倒排索引、多维空间索引等。
2554 0
表格存储新手指南:Java SDK异步接口的使用
本篇文章主要会介绍下表格存储的Java SDK提供的异步接口,如何使用以及应用场景。
6012 0
+关注
李欣529
阿里云数据库表格存储 Tablestore 解决方案架构师
13
文章
3
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载