如何查询SQL优化?

单表查询


索引和扫描选择
分析型数据库默认为全索引，对于查询的多个条件分别检索索引，得出多个结果集（行集合），然后采用流式归并算法得出满足组合条件的最终结果集。索引的性能主要受key的分布影响，包括：cardinality(散列程度)，范围查询的记录数/表记录数。
但是在以下四种情况下，索引性能较差。

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

对于以上四种情况，扫描性能反而比索引的性能好。用户通过加hint的方法强制查询不走索引。
Hint格式如下：<PRE prettyprinted? linenums>

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

例如：<PRE prettyprinted? linenums>

2. /*+ 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，则将该行数据加入返回结果。

二级分区查询优化


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

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改写为：<PRE prettyprinted? linenums>

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：<PRE prettyprinted? linenums>

1. Select * from table where year（date_test） > 1990

应该改为<PRE prettyprinted? linenums>

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

多表查询


首先我们来复习一下可加速join的条件：
对于COMPUTENODE Local/Merge计算模式的表Join查询，需要满足以下几个条件：

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

子查询使用
对于子查询，分析型数据库会首先执行子查询，并将子查询的结果保存在内存中，然后将该子查询作为一个逻辑表，执行条件筛选。由于子查询没有索引，所有条件筛选走扫描。因此如果子查询结果较大时，性能比较差；反之当子查询结果集较小时，扫描性能反而超过索引查询。
对于join查询，由于分析型数据库默认采用hash join算法，如果其中一张表结果集（条件筛选后）较大时，扫描性能会比索引差很多，因此尽量不要采用子查询。例如以下SQL：<PRE prettyprinted? linenums>

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的条数较多时，应改成：<PRE prettyprinted? linenums>

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操作的查询模式。典型的使用场景有：

• 非分区列的连接。如：

<PRE prettyprinted? linenums>

1. select name from student1 a join student2 b on a.name = b.name

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

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

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

• 子查询内不包含分区列。如：

<PRE prettyprinted? linenums>

1. select name from student1 where name in (select name from student2 where id = 10)

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

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个查询为例，原查询为：<PRE prettyprinted? linenums>

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

将小表结果进行前置，进行join的reordering后，查询性能提高了近1倍<PRE prettyprinted? linenums>

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