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

【笔记】最佳实践—偏分析场景的实践和优化

简介: PolarDB-X是一款以TP为主的HTAP数据库,也支持一定场景的分析需求。而典型的分析场景一般有以下几类特征:
+关注继续查看
  • 少量的写或者更新请求,大多数是读请求;
  • 每次查询都从数据库中读取大量的行,但是同时又仅需要少量的列;
  • 大多数查询都是比较复杂的查询,查询的并发不会很大,但单个查询需要高吞吐量;
  • 对于简单查询,允许一定的延迟;
  • 分析场景上分布式事务可能不是必须的;
  • 大部分查询中往往会涉及到事实表和维表的关联,是典型的大小表关联场景;
  • 查询结果明显小于源数据,即数据被过滤或聚合后能够被盛放在单台服务器的内存中;
  • 分析的数据往往是最近的业务数据,历史数据可以被清理或者被归档。

依据上述对分析场景的归纳,分析场景做性能优化除了要沿用TP数据库的优化思路,还会有自身不一样的优化思路。这主要会体现在结构设计和查询优化两个方面。

结构设计

在结构设计上主要包括如何选择表类型、分区键、主键以及聚簇,使表的性能达到最优。

设计为分区表或者广播表

  1. 广播表会在集群的每个数据节点都存储一份数据,建议广播表的数据量不宜太大,每张广播表存储的数据不超过20万行,这样在大表和广播表做关联时,可以计算下推,让关联贴近数据层做计算,避免大表数据拉取到计算节点做计算。
  2. 其他业务数据尽可能做成分区表,可以充分利用分布式系统的查询能力。理论上表的分区数量越多越好,这样多个分区表可以做并行扫描。存储层更易做到水平扩展,存储千万条甚至上亿条数据。不过实际使用中建议一个分区表的数量在500w~5000w之间。114.png
  3. 选择合适的分区键

PolarDB-X默认按照主键做分拆,主要为了降低您使用分布式数据库的成本。同时我们也支持通过指定分区键建分区表,在分析场景建议您根据如下依据选择的分区键:

  1. 尽可能选择参与JOIN的字段作为分区键,这样做的目的还是为了关联条件下推,避免数据被拉取到计算层做计算。
  2. 尽可能选择值分布均匀的字段作为分区键,这样可以避免由于分布式不均导致出现计算长尾现象,严重托慢大查询性能。

合理设计二级分区

PolarDB-X支持二级分区。当数据量过大或者有数据倾斜时,二级分区的选择至关重要,如果数据量大的表中没有二级分区或者二级分区切分不合理,也会影响性能。如果业务明确有增量数据导入需求,主要是对最近数据的报表分析,那么建议用日期格式做二级分区,避免对历史过期数据的扫描。


//直接用col先做一级分区
PARTITION BY HASH(col) 
SUBPARTITION BY LIST (ds) 
//ds转换后的月做分区
SUBPARTITION TEMPLATE (
   PARTITION p1 VALUES LESS THAN ('2021-08-00'),
   PARTITION p2 VALUES LESS THAN ('2021-09-00'),
)

合理设计索引

如果业务已经按照关联字段,合理的设计了分区键。但依然还有部分复杂查询涉及到对该表的其他列做关联,无法做到关联查询下推,此时可以考虑基于该非分区键的列做全局二级索引。这样复杂查询对该表做关联,可以转化成与该全局二级索引做关联。同时了为了避免回表的代价,对于分析场景建议所有的全局二级索引都建成聚簇索引。

查询优化

在分析场景中,由于会涉及比较大的数据,且对简单查询的延迟有一定的容忍度,推荐您采用MPP执行模式,既利用多个计算节点(CN)的计算资源承担复杂计算。一般只在只读实例默认开启MPP能力,如果您可以允许在主实例做分析需求,请联系阿里云技术支持。

在查询过程中,PolarDB-X首先会基于优化器选择合适的分布式执行计划,然后将计划调度到各个计算节点,充分发挥整个集群的计算资源加速查询。这个过程生成的分布式执行计划完全是基于统计信息做代价选择,所以及时的信息采集至关重要;同时由于优化器生成的计划不一定是最优的,所以这里也给到您在SQL编写和优化时的经验。

收集统计信息

PolarDB-X会及时定时收集统计信息,如果发现PolarDB-X生成的分布式执行计划不是最优的。可以通过ANALYZE TABLE手动对某个表做统计信息收集。

SQL编写技巧

  • 去掉不必要的列由于分析场景大多数是高吞吐的,所以应该去除返回过程中不必要的列,减少对带宽的压力。在编写SQL时一定要确认业务需要返回的列,不要直接使用星号(*)进行查询。
//不合适写法

select * from T1 where a1>100 and a2<1000;
//更合适写法,只需要返回业务关心的列
select a1, a2 from T2 where a1>100 and a2<1000;
  • 基于局部索引做过滤很多分析场景都期望用时间做二级分区,这样做大数据扫描的时候可以把时间做过滤条件,过滤掉绝大多数历史数据。
select a1,c2 from T1 where time >='2010-01-01 00:00:00';
  • 为了避免全部扫描,目前默认会在这个分区列上做局部索引。同样的在很多高吞吐的扫描场景下,可以考虑基于过滤条件做局部索引。
  • 避免低效的SQL语法如果表记录数非常大,扫描会很慢,直接导致查询缓慢。所以在SQL编写过程中我们需要注意以下几点:第一,避免索引失效
    1. 不在索引列上做任何操作,计算、函数、类型转换(自动或手动),会导致索引失效而转向全表扫描。
mysql> explain execute select * from staffs where name= 'hu';
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74 | const | 1 | 100 | NULL |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
1 row in set , 1 warning (0.00 sec)
//在索引列上做了其他操作,导致索引试下
mysql> explain execute select * from staffs where left(name,4)= 'hu';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 198 | 100 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set , 1 warning (0.00 sec)
    1. 在使用不等于(!=或<>)的时候,无法使用索引导致全表扫描。
    2. is null,is not null也无法使用索引。
mysql>  explain execute select * from staffs where name is null ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set
    1. like以通配符开头,mysql索引失效会进行全表扫描的操作。
mysql>  explain exeucte select * from staffs where name like '%hu' ;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 198 | 11.11 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set
mysql> explain execute select * from staffs where name like 'hu%' ;
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | staffs | NULL | range | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74 | NULL | 1 | 100 | Using index condition |
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
1 row in set
  • 第二,尽量少用like,like操作一般不会很高效,尽量使用范围条件到达目的。比如between...and...
    第三,多表关联场景下:
    1. 尽量包含分区列条件。如果不包含,则尽量通过WHERE条件过滤掉多余的数据。
    2. outer join的on和where作用域不同。on是作用于join的过程,where是作用于join之后的结果,所以应该将能在join的时候提前过滤的条件写在on上,也可以写在join表的子查询里,这样可以减少join原始表的数据量。

数据倾斜的检查和处理

如果出现查询异常缓慢,或者资源利用率不均匀的情况,则需要确认是否出现了数据倾斜。一般解决倾斜有三种策略:

  1. 通过show info from table检查某个分片在各个节点上的数据分布计数,如果各节点上的数据分布明显不均匀,则可以考虑对该表的分区键进行调整。
  2. 如果是出现了严重Join Key热点问题,将倾斜的Key用单独的逻辑来处理。例如两边的Key中有大量NULL数据导致了倾斜,则需要在Join前先过滤掉NULL数据或者补上随机数,然后再进行Join,示例如下。
SELECT * FROM A JOIN B ON CASE WHEN A.value IS NULL THEN CONCAT('value',RAND() ) ELSE A.value END = B.value;
  1. 在实际场景中,如果您发现已经数据倾斜,但无法获取导致数据倾斜的Key信息,可以使用如下方法查看数据倾斜。
--执行如下语句查询数据倾斜。
SELECT * FROM a JOIN b ON a.key=b.key;
--您可以执行如下SQL,查看Key的分布,判断执行Join操作时是否会有数据倾斜。
SELECT left.key, left.cnt * right.cnt FROM
(select key, count(*) AS cnt FROM a GROUP BY key) LEFT
JOIN
(SELECT key, COUNT(*) AS cnt FROM b GROUP BY key) RIGHT
ON left.key=right.key;
  1. 如果Group By Key出现了热点问题,可以考虑对SQL进行改写,添加随机数,把长Key进行拆分。例如:
SELECT Key,COUNT(*) AS Cnt FROM TableName GROUP BY Key;
//优化成以下SQL,先对热点做打散预聚合,再做最终聚合
-- 假设长尾的Key已经找到是KEY001。
SELECT a.Key
, SUM(a.Cnt) AS Cnt
FROM (
SELECT Key
, COUNT(*) AS Cnt
FROM TableName
GROUP BY Key,
CASE
WHEN Key = 'KEY001' THEN rand() % 50
ELSE 0
END
) a
GROUP BY a.Key;

调整执行策略

按照上述策略调整后,查询性能依然不理想且计算和存储资源都未到达瓶颈,这个时候可以调整下执行策略。主要有两种方式去调整:

  1. 加大并发度,您可以通过HINT /+TDDL:MPP_PARALLELISM=4/ 指定MPP执行器并行度。
mysql> /+TDDL:TDDL:MPP_PARALLELISM=4/ select a.k, count(*) cnt from sbtest1 a, sbtest1 b where a.id = b.k and a.id > 1000 group by k having cnt > 1300 or
der by cnt limit 5, 10;
  1. 通过HINT指定特定的算法,如何调整更好的聚合算法和关联算法,请参见聚合关联

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

相关文章
【笔记】最佳实践—偏分析场景的实践和优化
PolarDB-X是一款以TP为主的HTAP数据库,也支持一定场景的分析需求。而典型的分析场景一般有以下几类特征:
0 0
【笔记】最佳实践—偏高并发场景的实践和优化
本文介绍了如何判断查询语句是否为“点查”,以及如何将查询优化为“点查”。 “点查”是应用访问OLTP数据库的一种常见方式,特点是返回结果前只扫描表中的少量数据,在淘宝上查看订单/商品信息对应到数据库上的操作就是点查。PolarDB-X对点查的响应时间(Response Time, RT)和资源占用做了较多优化,能够支持较高的吞吐,适合高并发读取场景使用。
0 0
最佳实践—偏分析场景的实践和优化
PolarDB-X是一款以TP为主的HTAP数据库,也支持一定场景的分析需求。而典型的分析场景一般有以下几类特征:
0 0
应用 Serverless 化,让业务开发心无旁骛
本文是在 2022 杭州云栖大会上分享的内容。伴随着云产品的全面 Serverless 化,应用架构也发生了很大变化,我们认为应用 Serverless 化是未来的趋势,可以让业务开发更加心无旁骛的聚焦业务创新。
0 0
重磅嘉宾畅聊大数据&AI开源话题,零距离感受激荡开源江湖
「开源人说」第四期——大数据& AI专场在今年云栖大会举办,阿里巴巴开源委员会大数据AI领域副主席王峰和阿里云AI开源项目EasyRec负责人施兴现场分享热门开源项目背后的故事。开源中国创始人&CTO红薯,白鲸开源联合创始人代立冬,浙大博导赵俊博,InfoQ总编辑王一鹏、Apache软件基金会成员李钰等嘉宾圆桌共话,对开源热点及痛点问题展开激烈讨论。
0 0
Python语言如何使用MindOpt建模并求解混合整数线性规划问题
MindOpt是一款高效的优化算法软件包,求解算法实现了线性规划(LP)、混合整数线性规划(MILP)、二次规划(QP),可以支持命令行、c、c++、java和python调用。接下来我们将发布一系列文章,讲述各个语言如何使用 MindOpt 来求解数学规划问题。
0 0
消灭空指针,Java 8 给我们更好的解决方案
在平时的业务开发中,空指针是我们经常遇到的问题,
0 0
拥抱开源,云原生时代下的开源牧码人的初心与坚守
王峰 阿里巴巴开源委员会大数据AI领域副主席 阿里云开源大数据平台负责人 Flink中文社区发起人
0 0
【golang之路】——govaluate
【golang之路】——govaluate
0 0
【算法手札】深入理解宽度遍历(bfs)和深度遍历(dfs)搜索
【算法手札】深入理解宽度遍历(bfs)和深度遍历(dfs)搜索
0 0
文章
问答
文章排行榜
最热
最新
相关电子书
更多
性能优化方法论
立即下载
瓜子二手车网的性能优化实践之路
立即下载
如何做小程序性能优化
立即下载