likes
comments
collection
share

SQL语句重构建议

作者站长头像
站长
· 阅读数 14

SQL语句重构建议

等效语句替换

由于SQL是一种非常灵活的语言，不止一种SQL语句可以达成相同的效果，通过执行EXPLAIN PLAN来测算比较不同语句的执行效率，来选择其中最高效的SQL语句。

有如下两个等效SQL语句，在Table dept返回所有在Table emp中没有职员的departments。假定Column deptno在Table emp上有索引Index deptno_index。第一个SQL语句如下；

SELECT dname, deptno
   FROM dept
   WHERE deptno NOT IN
      (SELECT deptno FROM emp);

​

SQL语句重构建议

​

在该条SQL语句执行时，Table emp上的索引失效了，因为子查询中并没有用到WHERE条件，取而代之的是耗时更多的 Full Table Scan操作。那么如何才能用上索引呢？

SELECT dname, deptno
FROM dept
WHERE NOT EXISTS
​
(SELECT deptno
FROM emp
WHERE dept.deptno = emp.deptno);
​

​

SQL语句重构建议

​

现在子查询WHERE条件参考了Table emp的Column deptno，因此Index deptno_index就用上了（见上图Step 3）。显然，语句由于索引的使用，比使用Full Table Scan效率更高。

综上，如果SQL语句使用NOT IN关键字（如example.1），那么你可以考虑替换成NOT EXISTS的形式（如example.2）

MINUS and NOT EXISTS说明

MINUS 和 NOT EXISTS性能取决于很多因素

MINUS 默认对两个表进行full table scan，如果在where条件中有索引相关条件时将进行 index range scan。同时minus要求操作的列数量与列数据完全相同(通过类型转换达到一致也可)。

NOT EXISTS对于每一行数据都会执行子查询，如果关联字段存在索引，将进行index range scan。

上述关键字的选择依赖于返回的数据类型和表数据的大小。如果外部表相对小于内部表数据量(如果内部表有索引更好)，那么NOT EXISTS更合适，因为索引的时候会提升scan速度，而外部表数据量小降低了查询次数；如果两表数据量相近，那么MINUS比较好。

更改连接顺序

连接顺序同样对执行有一定程度影响，我们应当调整SQL在不影响结果的条件下，使得不必要的操作尽可能少。实现这一要求主要遵循一下三条：

  • 如果可以通过索引获取列，那么应当避免Full Table Scan
  • 如果所选索引能fetch到100行数据，那么请不要选那个fetch到10,000行的。我不确定具体含义是什么，但是至少读起来就够智障的了
  • 选择能让行数快速下降的连接顺序

下面就以上三条进行解释，通过该eample展示如何调整使得连接效率最高

SELECT info
FROM taba a, tabb b, tabc c
WHERE a.acol BETWEEN :alow AND :ahigh
​
AND b.bcol BETWEEN :blow AND :bhigh
AND c.ccol BETWEEN :clow AND :chigh
AND a.key1 = b.key1
AND a.key2 = c.key2;
​

  1. 选择驱动表

    前三个条件仅对单个表进行过滤，后两个条件为连接条件。过滤条件决定了驱动表和索引的选择：通常来说选择一个表作为驱动表，那么驱动表中应当包含一个能过滤绝大多数数据的过滤条件。对于上诉SQL来说，如果:alow和:ahigh所表示的区间相对窄，能筛选出少量数据；而Table b和Table c过滤条件相对宽松，那么Table a应当是驱动表。

  2. 选择索引

    在确定驱动表之后，选择合适的索引（或选择Full Table Scan，如果这样效率更高的话）。在主键or外键上的引用于将表连接到join tree上较早的表，并且连接将在索引上进行。除了驱动表，很少在非连接条件中选择索引(索引失效？)。在上述例子中，当Table a被选作驱动表后，分别选择在Column b.key1和Column c.key2上的索引来将对应的Table b和Table c与先前的表进行连接。

  3. 选择连接顺序

    选择最先与驱动表进行连接的表时，该表的数据应尽可能少，也就是说该表上的过滤条件能滤除大部分数据。以上述SQL语句为例，假设c.ccol BETWEEN :clow AND :chigh比b.bcol BETWEEN :blow AND :bhigh更为严格，那么Table c应当先于Table b进行连接，即

    SELECT info
FROM taba a, tabb b, tabc c
WHERE a.acol BETWEEN :alow AND :ahigh
​
AND b.bcol BETWEEN :blow AND :bhigh
AND c.ccol BETWEEN :clow AND :chigh
AND a.key2 = c.key2;
AND a.key1 = b.key1

使用未转换的列值

just like

where a.order_no = b.order_no

而不是

WHERE TO_NUMBER (SUBSTR(a.order_no, instr(b.order_no, '.') - 1))
= TO_NUMBER (SUBSTR(a.order_no, instr(b.order_no, '.') - 1))

尽量避免在SQL语句中使用聚合函数，这样会导致索引失效(?)。聚合函数的使用，尤其是在子查询中，指示你可以在记录中加入这一计算值，从而避免聚合函数的使用。

避免表达式中混合类型转换

这一点中尤其注意隐式类型转换。比如你想使用Type VARCHAR2的Column charcol，但是你的where条件却是这么写的

AND chrcol = <numexpr>

其中numexper是Type number，如1，USERENV('SESSIONID')，numcol+0等，那么Oracle将把该语句翻译成

AND TO_NUMBER(charcol) = numexper

这样将导致一下后果

  • 与聚合函数相似，类型转换导致优化器忽略列上的索引，造成索引失效(?)
  • 系统在处理Column charcol为不能转换为数字的字符串的单行数据时，将返回错误(建表测试)

为了避免以上情况出现，我们可以修改最初的SQL语句，显式转换类型

AND charcol = TO_CHAR(<numexper>)

对于Type number的Column numcol

numcol = charexper

是可以使用Column numcol上的索引，因为默认的转换顺序是char to number。但是这一顺序并不能被有力保证，所以我们还是应当显式声明类型转换，保证charexper将始终转换为Type number

SQL语句简单化

SQL并不是一个程序语言，使用一个SQL语句聚合多个功能并不是一个好事。如果你想通过SQL实现两个功能，最好的方法是用两个SQL语句，根据给出的参数分别实现，而不是合在一起。

优化发生在数据库知道哪些值将被插入到SQL中，因此execution paln不会依赖于插入值。

SELECT info 
FROM tables
WHERE ... 

AND somecolumn BETWEEN DECODE(:loval, 'ALL', somecolumn, :loval)
AND DECODE(:hival, 'ALL', somecolumn, :hival);

use ON and NOT IN with subquery carefully

WHERE (NOT) EXISTS是一个很好的替换。

(NOT) EXISTS和NOT IN并不是完全等效的

参考：