曾若潇
数据质量管理处
· 编者按
金融审计数据分析平台(FAD)作为我行对接国家审计署及分行特派办的重要系统，使用银行传统关系型数据库Oracle，含数据量200+T，覆盖我行30+个业务系统重要贴源数据，可谓是银行审计检查及监管报送的重要系统支撑和数据保障。面对海量增、存量数据的挑战，如何对数据库进行优化已成为一场持久命题战。后续将为大家分享金融审计数据分析平台在做性能调优时所用到的一些技术经验及思路、心得，以敲砖引玉。
一、嵌套循环(NESTED LOOP/NL)
Oracle及传统关系型数据库的运算分析中，表与表之间的连接方式非常重要。如果CBO选择了错误的连接方式 ，本来几秒就能出结果的SQL 可能执行一天都执行不完。如果想要快速的定位超大型sql的性能问题，我们就必须深入理解表连接方式。
在取多表关联的结果集时，一般情况下只能是两个表先关联，关联后的结果再和其他表或结果集关联。如果执行计划中出现了filter，这时可以一次性关联多个表，但是大多数filter会影响SQL本身执行效率。
(一)算法介绍
当驱动表每返回一行数据时，这一行数据将通过连接列传值给被驱动表。也就是说驱动表返回多少行数据，被驱动表就要被扫描多少次。嵌套循环可以快速返回两表关联的前几条数据，如果SQL中添加了HINT:first_rows，在两表关联时，优化器将会更倾向选择嵌套循环。
(二)优化思路
1.“嵌套循环被驱动表必须走索引”
如果连接列没有包含在索引中，那么被驱动表就只能走全表扫描，而且是反复多次的全表扫描，SQL可能执行不出结果。所以嵌套循环被驱动表必须走索引。如index unique scan或者 index range scan。
通过日常数据分析工作我们不难发现，嵌套循环被驱动表的连接列基数应该很高，如果被驱动表的连接列基数低，那么被驱动表就不应该走索引。这样一来驱动表只能进行全表扫描，但与此同时被驱动表也走不了全表扫描。
2.“两表关联返回少量数据才能走嵌套循环”
如果我们需要返回一百万行数据时，被驱动表会走索引。那么就会产生一百万次回表，而回表一般是单块读，这时候SQL效率极低。所以只有当两表关联返回数据量少时才能走嵌套循环。
3.“更改嵌套循环驱动表”
在执行计划中，离nested loops关键字最近的就是嵌套循环的驱动表。当两表使用外链接关联，如果执行计划走的是嵌套循环，此时将无法更改嵌套循环的驱动表，驱动表只能是外链接主表。
如果外链接表中有过滤条件，那么此时外链接会变成内连接。
如：
select/*+leading(e) use_nl(d,e)*/ * from
dept d
left join emp e on d.deptno=e.deptno
where e.sal<800;
这时可指定驱动表。
当HINT指定让从表EMP作为驱动表，执行计划中没有out时，说明SQL已经变为内连接，
因为过滤条件已经排除了从表和主表没有关联显示为null的情况。
二、实战案例
审计署近期在我行进行经常性审计及现场检查时，经常需要执行各类SQL以做相关数据查询。某次查询需关联查询数据量均在千万级以上的A表、B表数据，同时关联数据量很小的C表，开启并行查询效率仍然不高，因此审计署人员提出数据查询支持需求。
金审项目组在查看执行计划后发现优化器自动选择HASH连接方式，根据HASH算法，需将驱动表加载读取放入PGA后，再借由HASH值算法进行连接计算，效率极低。经讨论分析，后对连接列按基数及选择性筛选创建连接列组合索引，并指定NL驱动表为C表，查询效率大幅提高。最终返回数据量为一万余条。
Q&A
Q
两表关联是否走嵌套循环取决于两表返回后的数据量还是驱动表的返回数据量？
需判断两表间数据对应关系。如果两表是1：N的关系，如驱动表为1，被驱动表为N并且N很大时，即使驱动表返回数据量很小也不能走嵌套循环，因为两表关联之后数据量会很多。
A
所以判断两表关联是否需要走嵌套循环应判断两条关联之后返回的数据量。如果两表关联之后返回的数据量少，可以走嵌套循环，返回数据量多的应该走哈希。
Q
大表是否可以当嵌套循环的驱动表？
可以。如果大表通过条件过滤之后返回的数据量很少就可以当NL驱动表。
A
Q
“select * from a,b where a.id=b.id” 如果a 有100 条数据，b有一百万数据，a:b=1：N，当N很低时该怎么优化？
可以在连接列上建立索引，让a 和b 走嵌套循环，这样b表会被扫描100次，但是每次扫描的时候走的是Id列上的索引(范围扫描)，b表最多查询出表中几百行数据 (100*N)。而如果让a和b进行哈希连接，b表会被全表扫描 (因为没有过滤条件)。 需要查询表中的100万行数据。
A
所以一般情况下，一个小表和一个大表关联，我们考虑让小表NL大表。让大表连接列走索引(如果有连接列，过滤条件和连接列建立组合索引)，从而避免大表进行全表扫描。
Q
嵌套循环是否支持不等值连接？
支持。
A
3937
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
