全部产品
分析型数据库

7.4 查询SQL优化

更新时间:2017-06-07 13:26:11   分享:   

单表查询

索引和扫描选择

分析型数据库默认为全索引,对于查询的多个条件分别检索索引,得出多个结果集(行集合),然后采用流式归并算法得出满足组合条件的最终结果集。索引的性能主要受key的分布影响,包括:cardinality(散列程度),范围查询的记录数/表记录数。

但是在以下四种情况下,索引性能较差。

  • 范围查询(或等值查询)筛选能力差,即满足条件的记录数/表总记录超过10%。
  • 不等于条件查询(不包括not null)。
  • 中缀或后缀查询,例如 like ‘%abc’ 或like ‘%abc%’。
  • AND 条件中某一条件具有高筛选能力,其他条件走索引性能比扫描性能差。

对于以上四种情况,扫描性能反而比索引的性能好。用户通过加hint的方法强制查询不走索引。

Hint格式如下:

  1. /*+ no-index=[table1.x;table2.x;y]*/

例如:

  1. /*+ no-index=[table1.time]*/ select * from table1 where x= 3 and time between 0 and 10000

表示强制条件time between 0 and 10000走扫描。计算引擎首先检索列x的索引,得出满足条件x=3的行集合,然后读取每行所对应的time列数据,如果满足time between 0 and 10000,则将该行数据加入返回结果。

二级分区查询优化

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

例子:

  1. select * from table where id = 3 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 table where id = 3 and time between 2016-04-01 00:00:00 and 2016-04-01 12:00:00 and pid = 20160401;

pid为二级分区列名。

条件改写

当SQL中条件为函数时,无法走索引过滤,自动走扫描。在大多数情况下,性能会比较差,因此尽量改写条件去除函数。例如以下SQL:

  1. Select * from table where yeardate_test > 1990

应该改为

  1. Select * from table where date_test > 1990-00-00

多表查询

首先我们来复习一下可加速join的条件:

对于COMPUTENODE Local/Merge计算模式的表Join查询,需要满足以下几个条件:

  • 连接计算的所有事实表必须在同一个表组。
  • 所有事实表的一级分区数相同。
  • 事实表Join时, on条件必须包含分区列且必须是等值查询,其他列无限制。
  • Left join时,右表是事实表时,左表不能是维度表。

子查询使用

对于子查询,分析型数据库会首先执行子查询,并将子查询的结果保存在内存中,然后将该子查询作为一个逻辑表,执行条件筛选。由于子查询没有索引,所有条件筛选走扫描。因此如果子查询结果较大时,性能比较差;反之当子查询结果集较小时,扫描性能反而超过索引查询。

对于join查询,由于分析型数据库默认采用hash join算法,如果其中一张表结果集(条件筛选后)较大时,扫描性能会比索引差很多,因此尽量不要采用子查询。例如以下SQL:

  1. Select A.id from table1 A join (select table2.id from table2 where table2.y = 6) B on A.id= B.id where A.x=5

当满足条件x=5 和y=6的条数较多时,应改成:

  1. Select A.id from table1 A join table2 B on A.id = B.id where B.y = 6 and A.x=5

一个例外情况是,当结果集较大的表是实时表,应尽量采用子查询。原因在于,实时表的最新数据(基线合并后写入的数据),索引能力很弱,查询性能非常差。子查询可以减少搜索范围,性能反而更好。

小表广播/Full MPP Mode查询优化

Full MPP Mode 与COMPUTENODE Local/Merge模式选择

COMPUTENODE Local/Merge模式是分析型数据库支持的一种极速查询模式,通过对用户SQL的有效甄别,快速判断是否和数据分布相匹配,从而达到优化数据shuffle逻辑,下沉大量计算操作的目的。

使用Full MPP Mode一般需用户使用Hint显示指定。

用户也可通过在查询hint中指定来强制查询使用MPP或COMPUTENODE模式。典型使用COMPUTENODE Local/Merge模式的场景有:

  • 不包含表连接或者子查询的任意查询。
  • 表连接条件为等值条件且连接列均为一级分区列的任意查询。
  • 子查询包含了分区列的的groupby操作的任意查询。
小表广播

小表广播是基于COMPUTENODE Local/Merge模式扩展出的一种支持子查询内部不包含分区列的join操作的查询模式。典型的使用场景有:

  • 非分区列的连接。如:
  1. select name from student1 a join student2 b on a.name = b.name

两个表分区列都为id,由于表连接为非分区列,该查询不能使用模式。若student2表数据量不超过3万条,使用小表广播后,查询可以改写为:

  1. select name from student1 a join (select /*+broadcast=true*/ name from student2) b on a.id = b.id

即可通过COMPUTENODE模式进行快速查询。

  • 子查询内不包含分区列。如:
  1. select name from student1 where name in (select name from student2 where id = 10)

该查询无法使用COMPUTENODE模式计算出正确结果,若id=10的name记录数不大于3万条,可修改为小表广播后:

  1. select name from student1 where name in (select /*+broadcast=true*/ name from student2 where id = 10)
Full MPP Mode最优写法

当查询无法使用COMPUTENODE模式进行查询时,对使用MPP查询的SQL进行必要的修改,可以很大程度的提高查询的速度。以TPC-H测试中第2个查询为例,原查询为:

  1. select
  2. s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment
  3. from
  4. (select p_partkey, p_mfgr from part p where p_size = 15 and p_type like '%BRASS') inner join partsupp ps on p_partkey = ps_partkey
  5. inner join supplier s on s_suppkey = ps_suppkey
  6. inner join (
  7. select p_partkey as min_p_partkey, min(ps_supplycost) as min_ps_supplycost from part p
  8. inner join partsupp ps on p_partkey = ps_partkey
  9. inner join supplier s on s_suppkey = ps_suppkey
  10. inner join nation n on s_nationkey = n_nationkey
  11. inner join region r on n_regionkey = r_regionkey and r_name = 'EUROPE'
  12. group by p_partkey
  13. )A on ps_supplycost = A.min_ps_supplycost and p_partkey =A.min_p_partkey
  14. inner join nation n on s_nationkey = n_nationkey
  15. inner join region r on n_regionkey = r_regionkey and r_name = 'EUROPE'
  16. order by
  17. s_acctbal desc,
  18. n_name,
  19. s_name,
  20. p_partkey
  21. limit 100

将小表结果进行前置,进行join的reordering后,查询性能提高了近1倍

  1. /*+engine=MPP*/select
  2. s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment
  3. from
  4. region r
  5. inner join nation n on n_regionkey = r_regionkey and r_name = 'EUROPE'
  6. inner join supplier s on s_nationkey = n_nationkey
  7. inner join partsupp ps on s_suppkey = ps_suppkey
  8. inner join (select p_partkey, p_mfgr from part p where p_size = 15 and p_type like '%BRASS') p on p_partkey = ps_partkey
  9. inner join (
  10. select p_partkey as min_p_partkey, min(ps_supplycost) as min_ps_supplycost from region r
  11. inner join nation n on n_regionkey = r_regionkey and r_name = 'EUROPE'
  12. inner join supplier s on s_nationkey = n_nationkey
  13. inner join partsupp ps on s_suppkey = ps_suppkey
  14. inner join part p on p_partkey = ps_partkey
  15. group by p_partkey
  16. )A on ps_supplycost = A.min_ps_supplycost and p_partkey =A.min_p_partkey
  17. order by
  18. s_acctbal desc,
  19. n_name,
  20. s_name,
  21. p_partkey
  22. limit 100
本文导读目录
本文导读目录
以上内容是否对您有帮助?