一、问题描述
有下面一个SQL亟待优化

这个SQL执行计划如下


二、问题分析
题目SQL的功能一句话概括来说是用客户表里在大于所有年龄段平均人数的年龄段范围的客户更新客户明细表中注册日期在2019年1月且客户组为90的相同客户的客户名称和客户地址。两个表均无索引，从原生SQL执行计划发现共执行了6次TABLE ACCESS FULL查询，其中CSTOMER_DETAIL表1次，CUSTOMER表5次，另外CUSTOMER表做了2次GROUP BY,在更新CUSTOMER_DETAIL表两列时，表CUSTOMER执行了2次全表扫描。
所以我们优化的切入点主要有两个：
1、减少全表扫描资源开销；
2、减少CUSTOMER表GROUP BY资源开销；

三、优化方案
对于原生SQL，我们主要做了以下三部分优化：

优化1
原SQL:
1. exists (select 1
2. from customer t1
3. where t.cust_id = t1.cust_id
4. and t1.cust_age in
5. (select cust_age
6. from customer
7. group by cust_age
8. having count(*) > (select avg(count(*))
9. from customer
10. group by cust_age)));
1
2
3
4
5
6
7
8
9
10
优化后的SQL：
1.exists (
2. with
3. v as (select CUST_AGE,count(*)c from zq.CUSTOMER group by CUST_AGE),
4. a as (select CUST_AGE from v where v.c > (select avg(v.c) from v)
5. ) select 1
6. from zq.customer t1
7. where t.cust_id = t1.cust_id
8. and t1.cust_age in (select CUST_AGE from a));
1
2
3
4
5
6
7
8
这个过滤条件是对年龄进行限制，过滤出客户表中，客户年龄段的总人数大于所有年龄段的平均数，这样的记录。
优化时，拆成3步走：

1）首先获取各年龄段及各年龄段的人数，将表从2000W条记录压缩为100行
2）基于这个小表，统计平均年龄
3）筛选出符合条件的年龄
三个步骤一共扫描了2000W+100+100条记录，而原表通过两次全表扫描2000W+2000W。
这里我们使用WITH AS短语，在真正进行查询前预先构造了两个临时表，第一增加了SQL的易读性，结构更加清晰，第二保存在内存中，可以被多次使用，达到了“少读”的目标。观察WITH CLAUSE方法的执行计划，其中“SYS_TEMP_XXXX”便是在运行过程中构造的中间统计结果临时表。
另外，考虑到GROUP BY反复用到CUST_AGE，我们在CUST_AGE字段加了索引。

优化2
原SQL：
1.set t.cust_name =
- (select t1.cust_name from customer t1 where t.cust_id = t1.cust_id),
- t.cust_address =
- (select cust_name || 'jinrong'
- from customer t1
- where t.cust_id = t1.cust_id)
1
2
3
4
5
6
优化后的SQL：
1.set (t.cust_name, t.cust_address) =
2. (select t1.cust_name,t1.cust_name || 'jinrong' from zq.customer t1 where t.cust_id = t1.cust_id )
1
2
原SQL进行了两次全表扫描，优化后的SQL减少一次全表扫描，提高了查询效率。cusomer和customer_detail两个表的cust_id字段经常出现在where子句中，且为两表连接的字段，所以我们建立customer.cust_id和customer_detail.cust_id两个普通索引，但观察整个执行计划，customer_detail.cust_id上的索引并未被使用。是因为where没有对cust_id进行过滤，筛选出符合条件的customer_detail表后(下面简称为A表)，对A表中的每一行记录去匹配customer表，这里A表是全表扫描，customer_detail表使用了索引。所以，我们仅在customer表的cust_id列建立索引。
但并不是所有表连接操作，都只有一个索引生效，需要具体问题具体分析。

优化3
原SQL：
1.where to_char(register_time, 'yyyymm') = '201901' and t.group_id = 90
1
优化后的SQL：
1.where register_time > = to_date('20190101','yyyymmdd') and register_time < to_date('20190201','yyyymmdd') and t.group_id = '90';
1
通过第一步对表结构的分析，group_id字段是VARCHAR(2)类型的，当比较字符型和数值型的值时，oracle会把字符型的值隐式转换为数值型，因此优化为t.group_id = ‘90’。
在group_id和register_time上建立复合索引会提高速度。但是索引列上施加函数，会造成不使用索引，因此我们改用to date函数：

2.where register_time > = to_date('20190101','yyyymmdd') and register_time < to_date('20190201','yyyymmdd') and t.group_id = '90';
1
另外，复合索引的字段顺序，会影响查询速度，创建复合索引做SQL优化的一般原则是，如果两个字段在WHERE子句中使用频率相同,则将最具选择性的字段排在最前面，以下是分析结果：
register_time有2000W不重复值，可唯一标识每条记录；Group_id有100个不重复值。当建立（rigister_time，group_id）索引时，首先通过索引找到20190101和20190130两个叶子节点，再范围扫描170W条数据；当建立（Group_id，Register_time）索引时，首先通过索引找到group_id为90的叶子节点，再通过索引找到201901和201930两个起始点，随后范围扫描20W条数据。
因此，建立（Grouop_id ，Register_time）复合索引的性能更优。

三、解决效果
对原生SQL做以上三方面优化后，我们将执行时长从原来的40s+压缩到最快0.915s，随机执行5次（1.51s，2.02s，1.15s，1.05s，1.44s），平均1.43s，下面是优化后的SQL执行计划：


总结
通过上面SQL优化案例我们认识到，日常SQL开发过程中应该在代码满足简单易读、易维护的前提下注意SQL的写法对资源消耗比重，扫描的数据块，重复计算量的控制。
————————————————

免责申明：

本文系转载，版权归原作者所有，如若侵权请联系我们进行删除！

想了解或咨询更多有关袋鼠云大数据产品、行业解决方案、客户案例的朋友，浏览袋鼠云官网：https://www.dtstack.com/?src=bbs
同时，欢迎对大数据开源项目有兴趣的同学加入「袋鼠云开源框架钉钉技术群」，交流最新开源技术信息，群号码：30537511，项目地址：https://github.com/DTStack