前言
工作上博主刚工作时候是做的Oracle开发DBA后来同时搞了大数据，也拿到了Oracle和华为大数据的专家认证。
日常工作生活中，经常有同事、网友咨询SQL开发和调优的各种问题，于是我下定决心做个系列的SQL开发博客文章。
这一系列文章主要是分为两块：一小块是SQL基础，一大块是企业级实战案例讲解。
我不会去给大家讲具体语法和底层实现原理，因为我感觉语法大家可以看官方文档或看其他博主博客，很多书籍配以语法简单的查询案例讲解语法，而实战特别是实际开发工作的实战SQL级的难度特少，所以我的这一系列文章重点突出讲企业SQL查询实战案例！
做完这一系列博主考虑是否开一系列SQL调优案例，但是博主还想继续开一系列hadoop、zookeeper、kafka、hbase、flume、Hive、Spark、Flink的博客文章，等做完这一系列SQL开发再考虑后续咋个安排吧！！！
接下来，给大家先分享一个本人工作中的SQL开发优化案例来作为这一系列博客的开章！！！
一、问题描述
有下面一个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))); - [ ] 优化后的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)); 这个过滤条件是对年龄进行限制，过滤出客户表中，客户年龄段的总人数大于所有年龄段的平均数，这样的记录。
优化时，拆成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) - [ ] 优化后的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 ) 原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 - [ ] 优化后的SQL：
1.where register_time > = to_date('20190101','yyyymmdd') and register_time < to_date('20190201','yyyymmdd') and t.group_id = '90'; 通过第一步对表结构的分析，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'; 另外，复合索引的字段顺序，会影响查询速度，创建复合索引做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的写法对资源消耗比重，扫描的数据块，重复计算量的控制。
