Oracel执行计划优化的一次案例分析

同事在编写SQL遇到一段SQL脚本，仅修改了一个字符，执行时间从原来1分钟左右变为6~10秒，变化较大。事实上，类似情形在SQL调优过程中是司空见惯的。笔者就曾经遇到过原先2个小时以上不出结果的查询，通过一系列优化措施后，变成耗时仅几分钟。下面就以此案例进行一些简单的总结分析，以备后查。希望对经常编写SQL的朋友会有一些启发和帮助，至少能够更加清楚地解读SQL语句的执行计划（execution plan）。本文尽可能用通俗语言来阐述，所以有些地方会不够严谨。

 

（这一段比较乏味可跳过）多表连接时主要是考虑二个问题：表连接顺序（join order）、单表访问路径（access path）。前者指多个表两两连接时的先后顺序，3种最经典的连接方法是：嵌套循环（Nested Loop，简记NL），合并连接（Merge Join，简记MJ），哈希连接（Hash Join，，简记HJ）。后者指如何提取单表记录（record），通常为全表扫描（Full Scan），索引扫描（Index Scan）等。假定要将A（m条记录），B（n条记录）表进行连接，NL可以理解为2个for循环，即对A中每一条记录，去B表中遍历查询匹配，其复杂度最大为O(mn)。而MJ则要求A、B两表在连接条件上是有序的，从而A，B表只需顺序扫描一遍，复杂度最大为O(m+n)，但是排序代价会比较大。HJ则采用哈希技术，将其中较小表做成哈希表，再提取大表中记录，通过哈希查找与小表进行匹配，其复杂度依赖于哈希算法，由于哈希查找性能很高，故目前大表连接时经常会采用HJ。关于单表存取路径，全表扫描指顺序读取表数据块，遍历每一条记录；索引扫描是指先根据筛选条件去查找索引，获取满足条件记录的rowid，再通过rowid直接定位到该条记录所在的块。经验显示当选择度（selectivity）约小于15%时，用索引扫描会更好。至于索引的组织结构（Btree，mtree），有专门书籍详细讨论，此处不再赘言。

 

言归正传，先抛出SQL语句

SELECT COUNT(*) FROM CIS_TRADE TLEFT JOIN TA.TAACCOINFO I ON T.TAACCOUNTID = I.TAACCOUNTIDLEFT JOIN TA.TACUSTOMER C ON C.CUSTOMERID = I.CUSTOMERID,VM.CIS_TRADE_NOTICE N WHERE 1=1AND T.BUSINESSCODE = N.BUSINESSCODEAND LENGTH(I.TAACCOUNTID) = 12AND C.EMAILADDRESS LIKE '_%@_%._%';

其中VM.CIS_TRADE_NOTICE只有20条记录，CIS_TRADE约18万，TA.TAACCOINFO约1000万，TA.TACUSTOMER约900万，最终满足条件的结果集只有几千条记录。

 

执行计划如图所示（操作符前边的数字表示操作先后顺序），将LENGTH(I.TAACCOUNTID)中的表别名I改成T后得到执行计划2。其中执行计划1比较耗时。语句中涉及4个表，故采用两两连接时需要进行3次连接操作。计划1连接顺序为(N, (C, (T, I)))，计划2连接顺序为(((T, I), C), N)，连接顺序可以被看作一颗后序二叉树。其中连接后中间结果集可被视为一个视图，目前大多数操作符都可以采用管道（pipeline，或流水线）模式，能快速地返回数据集起始行（如Oracle的first_row规则）

 

 

执行计划1执行计划2

1全表扫描N，创建哈希表

2全表扫描C，创建哈希表

3全表扫描T，创建哈希表

4全表扫描I

5将T与I哈希连接

6将C与(T, I)哈希连接

7将N与(C, (T, I))哈希连接

8统计count

 

注：C 表中customerid是主键，I表中taaccountid为主键

1全表扫描

2索引扫描I表（基于taaccountid）获取rowid

3通过rowid访问I某条记录

4将T，I进行嵌套循环连接

5索引扫描C表（基于customerid）获取rowid

6通过rowid访问C某条记录

7将(T, I)与C嵌套循环连接，输出结果做哈希表

8全表扫描N表

9将((T, I), C)与N哈希连接

10统计count

 

因为查询结果集最终只输出几千条记录，所以计划1耗时主要原因有：连接顺序不合理；对C和I这2个千万级大表进行了全表扫描；选择C表这个大表做哈希表。计划2性能较好原因：优化器考虑到返回结果集比较小，所以采用了嵌套循环连接；同时对C、I表进行了索引扫描（避免了全表扫描）。即先对T表筛选后只剩下约几千条记录，再通过taaccountid对索引扫描I表后进行嵌套循环连接，此后通过customerid索引扫描C后进行嵌套循环连接。这样子使得磁盘I/O次数大大缩减，而磁盘I/O通常是SQL语句慢的罪魁祸首，所以计划2总体上代价较小，速度更快。

 

从上述例子可看出，执行计划好坏与否是SQL执行时长的决定因素。SQL开发人员最好能够清楚把握表的总行数、单条记录长度（KB），表总大小(MB)、表上哪些字段有索引等一些物理属性。此外对Oracle 的数据（索引）文件结构，即段、区、块也要有所了解。在实践中多通过执行计划来分析SQL执行语句，相信功力一定会日久见长。