全部产品
阿里云办公

8.8.2 SQL优化实践

更新时间:2018-10-09 07:48:15

注:本文档针对ADS 2.4版本现状总结的SQL优化建议,后续版本针对目前的一些情况,优化器持续改进。

1.总则

ADS是一个分布式数据库,在编写和优化SQL时,记住ADS是一个数据库,同时要考虑其分布式特性。如下总结4点SQL编写和优化要求和经验。

SQL编写原则: 追求简单

大部分情况下性能随 SQL复杂度下降,比如:单表查询 (冗余设计)优于 表关联查询。

SQL优化核心方法:减少IO

通过索引,返回最小量数据量,减少IO同时也减少内存开销。

分布式计算:本地计算&并行计算

大数据计算情况,本地计算避免数据跨节点,充分利用分布式多计算资源的能力。

高QPS:分区裁剪

业务系统要求高QPS,毫秒级RT,请记住一定要将表和SQL设计为分区裁剪模式。


2.常见SQL优化细节

如下罗列了常见SQL优化细节,尽量在编写SQL时作为编写规划要求。

2.1. 限制和约束

  • 返回记录限制:默认返回记录最大行数 10000

  • 查询超时设置:默认30秒

  • 两阶段查询,防止FN节点内存不足,有如下限制

    • group by记录限制,默认1000万

    • count(distinct)限制,默认10000

  • 分页查询limit M,N,分页返回记录数限制M+N<50000,N<10000,如果保持有序必须order by字句

2.2. 去掉不必要的列

ADS是列存数据库,返回的列的数量直接影响性能,在编写SQL时一定要写明业务确认需要返回的列,切记不要直接使用 *。

典型错误写法:

  1. select * from tab1 where c1>100 and c1<1000;

正确的写法:明确要返回的列。

  1. select c1,c2 from tab1 where c1>100 and c1<1000;

2.3. 索引&扫描

当SQL包含多个查询条件情况,需要优先选择高筛选条件,其他条件可以通过扫描方式。原理:ADS内部采用列存方式,可以通过单列高效过滤后直接通过内部记录指针扫描其他列值,可以减少其他列的索引查询开销,

场景1:

比如如下SQL: 通过c1=3可以快速查询到少量记录(假设10000),而单独通过time>’2010-01-01 00:00:00’返回记录数非常大。

  1. select c1,c2 from tab1 where c1=3 and time >='2010-01-01 00:00:00';

可以通过如下方式只通过c1索引,time通过内部扫描方式

  1. /*+ no-index=[tab1.time]*/ select c1,c2 from tab1 where c1=3 and time >='2010-01-01 00:00:00';

表示强制条件time >=’2010-01-01 00:00:00’走扫描。计算引擎首先检索列c1的索引,得出满足条件c1=3的行集合,然后读取每行所对应的time列数据,如果满足time >=’2010-01-01 00:00:00’,则将该行数据加入返回结果。

场景2:

不等于条件查询,如c2<>100,这样的条件无法有效的过滤掉记录,可以不通过索引扫描,走内部扫描更快。

  1. select c1,c2 from tab1 where c1=3 and c2<>100;

可以增加no-index hint,如下:

  1. /*+ no-index=[tab1.c2]*/ select c1,c2 from tab1 where c1=3 and c2<>100;

场景3:

中缀或后缀查询,例如 like ‘%abc’ 或like ‘%abc%’;

  1. select c1,c2 from tab1 where c1=3 and c3 like '%abc%';

可以增加no-index hint,如下:

  1. /*+ no-index=[tab1.c3]*/ select c1,c2 from tab1 where c1=3 and c3 like '%abc%';

2.4. 索引失效

导致索引失效,直接进行了扫描,如果表记录数非常大情况,直接导致查询缓慢。容易出现的情况有:

  • 函数(列)

  • 类型转换

  • like ‘%abc%’

场景1:

函数转换导致索引失效,time为timestamp类型,存储时间’2017-12-10 10:00:23’:

  1. select c1,c2 from tab1 where substr(cast(time as varchar),1,10)='2017-10-12';

需要修改为如下SQL:

  1. select c1,c2 from tab1
  2. where time>='2017-10-12 00:00:00' and time<='2017-10-12 23:59:59';

2.5. is not null

去掉不必要的is not null过滤条件。

如下SQL:

  1. Select c1,c2 from tab1 where c1>100 and c1<1000 and c1 is not null;

and c1 is not null为不多余的条件,可以直接修改为:

  1. Select c1,c2 from tab1 where c1>100 and c1<1000 ;

2.6. 二级分区查询优化

一级分区包含多个二级分区;计算时,每个二级分区依次执行条件查询,并将所有二级分区的结果进行汇总。由于每个二级分区都要参与所有条件筛选(索引查询),当二级分区较多时,查询性能较差。如果能够预知数据的分布,确定二级分区的范围,可以在查询条件中增加二级分区列条件,这样可以快速过滤无效的二级分区,减少搜索范围。

  1. select * from tab1 where id = 3
  2. and time between '2016-04-01 00:00:00' and '2016-04-01 12:00:00';

增加二级分区过滤条件,如果根据业务场景确认满足time between ‘2016-04-01 00:00:00’ and ‘2016-04-01 12:00:00’ 的二级分区列为20160401,则可以将该SQL改写为:

  1. select * from tab1 where id = 3
  2. and time between '2016-04-01 00:00:00' and '2016-04-01 12:00:00'
  3. and pid = 20160401;

2.7.分区裁剪

当要求高QPS查询业务时,需要从表的设计和SQL上利用分区裁剪能力。即查询条件使用一级分区键/二级分区键。如下示例:

建表语句如下:

  1. CREATE TABLE t_fact_mail_status (
  2. mail_id varchar COMMENT '',
  3. scan_timestamp timestamp COMMENT '',
  4. biz_date bigint COMMENT '',
  5. org_code varchar COMMENT '',
  6. org_name varchar COMMENT '',
  7. dlv_person_name varchar COMMENT '',
  8. receiver_name varchar COMMENT '',
  9. receiver_phone varchar COMMENT '',
  10. receiver_addr varchar COMMENT '',
  11. product_no varchar COMMENT '',
  12. mag_no varchar COMMENT '',
  13. op_1_timestamp bigint COMMENT '',
  14. op_2_timestamp bigint COMMENT '',
  15. op_3_timestamp bigint COMMENT '',
  16. op_4_timestamp bigint COMMENT '',
  17. op_5_timestamp bigint COMMENT '',
  18. PRIMARY KEY (mail_id,org_code,biz_date)
  19. )
  20. PARTITION BY HASH KEY (org_code) PARTITION NUM 128
  21. SUBPARTITION BY LIST KEY (biz_date)
  22. SUBPARTITION OPTIONS (available_partition_num = 30)
  23. TABLEGROUP ads_demo
  24. OPTIONS (UPDATETYPE='realtime')
  25. COMMENT '';

查询SQL:

  1. select
  2. sum(mail_id) as cn
  3. from
  4. t_fact_mail_status t
  5. where
  6. t.org_code = '21111101'
  7. and t.biz_date