SQL语句的良好编写方法

发布时间：2010-03-03 20:57:08，阅读人数：210

1.涉及到多个表的JOIN语句的写法
很多开发人员对于涉及到多个表的JOIN语句，不知道如何下手，以及好何写出一个效率好的JOIN语句来，在这里，我根据我的一些经验，给出一些建议供大家参考：

（1） 了解一个SELECT 语句的结构

通常，一个SELECT语句的结构是这样的：

SELECT select_column_list——— step_4

FROM table_list———step_1

WHERE join_condition———step_2

AND predicates——— step_3

ORDER BY orderby_clause——— step_5

（2） SELECT语句的书写顺序

通常情况下，SELECT语句的结构都是如我上面所描述的那样，大家在写SQL语句时，不妨先写好框架，如：先写SELECT语句的一些关键字：

SELECT *

FROM

WHERE

ORDER BY

框架写好后，我们再逐步加精. 书写的顺序按上图我标的顺序：

n 第一步，先写查询所要涉及到的表，不管有多少个表，我们先把表名列上去，为了后面语句的简洁，可以给表加上别名,如果有同一个表要使用几次，则要使用不同的别名来标识,或者容易产生岐义：

SELECT *

FROM pm_user a, pm_company b…pm_user c

WHERE

ORDER BY

n 写好table_list后,我们开始来定义JOIN的连接条件，注意，对于涉及到多个表的连接，我们一定要避免不定义连接条件的情况，因为这会产生迪卡尔连接(Cartesian Joins)

SELECT *

FROM pm_user a, pm_company b…pm_user c….表列表

WHERE a.compid=b.compid……连接条件

ORDER BY

n 当所有的连接条件都定义好了以后，我们要再检查一下，看是否有其中的两个表完全没有定义连接条件的，这在JOIN的表很多的情况下，大家很容易犯的错误。一个好的检测方法是：书写好SQL后，在pl/sql developer中按F5键查看执行计划，当SQL的执行计划中有出现关键字为Cartesian的操作时，就表示我们不小心产生了一个迪卡尔连接，要回去再REVIEW一下我们定义的连接条件

n 写好连接条件后，接下来，我们就可以在WHERE子句中接着写过滤条件(我们把它称之为predicates),即有具体值,能过滤掉一部分记录的WHERE条件。通常，良好的编写习惯是将过滤条件写在WHERE子句的最下部,这样，SQL引擎在对表进行连接操作时，会先过滤掉参与连接的表的一部分记录，这样，参与连接的数据集就会少一点，从而连接操作的成本也小一点:

SELECT *

FROM pm_user a, pm_company b…pm_user c….表列表

WHERE a.compid=b.compid……连接条件

And a.userid=123456 and …….过滤条件

ORDER BY

n 写好过滤条件后，基本上WHERE子句就写完了，接着我们就可以来定义查询字段列表了(select_column_list),即查询执行完后我们想要看到的字段。如果有同名的字段，可以定义字段别名(如下例中的a.name和b.name)

SELECT a.userid,a.name as username,b.name as company_name…查询字段列表

FROM pm_user a, pm_company b…pm_user c….表列表

WHERE a.compid=b.compid……连接条件

And a.userid=123456 and …….过滤条件

ORDER BY

n 定义ORDER BY 子句。ORDER BY即排序字段。因为排序操作通常是一个SELECT语句的最后一步操作，所以，通常，ORDER BY 子句也可以放到最后来写,如果查询字段列表中有定义别名，排序字段中也可以使用表名(如下例中的username):

SELECT a.userid,a.name as username,b.name as company_name…查询字段列表

FROM pm_user a, pm_company b…pm_user c….表列表

WHERE a.compid=b.compid……连接条件

And a.userid=123456 and …….过滤条件

ORDER BY a.userid,username….排序字段

经过上面这些步骤，基本上，一个SELECT语句就完成了。

（3） 良好性能JOIN语句的写法

下面介绍一下如何写一个执行性能良好的JOIN语句

<1> 尽量写平级的连接，不要写嵌套的连接

什么是平级的连接呢？平级的连接是指连接的表都在同一级的查询结构，如下例的表，即为平级的连接：

SELECT a.userid,a.name as username,b.name as company_name…查询字段列表

FROM pm_user a, pm_company b…pm_user c….表列表

WHERE a.compid=b.compid……连接条件

And a.userid=123456 and …….过滤条件

ORDER BY a.userid,username….排序字段

表pm_user,pm_company 等都位于同一级的查询结构。

那么，什么又是嵌套的连接呢？嵌套的连接同常出现在有子查询的SELECT语句中，子查询中的表有连接到外部查询（或者说父查询）的表，如下例中,tab1和tab3的连接即为一个嵌套的连接：

SELECT *

FROM tab1,tab2…

WHERE tab1.col in (select tab3.col from tab3 where tab3.col2=tab1.col2 )

还有一种嵌套连接的例子出现在select_column_list，请看下面的例子：

SELECT tab1.col,

(select tab3.col from tab3 where tab3.col2=tab1.col2 ) as col_alias

FROM tab1,tab2…

WHERE tab1.col=tab2.col

此例中，标红色的部分，即tab1和tab3也是一个嵌套连接。这种格式在ORACLE中称之为标量子查询表达式(Scalar Subquery Expressions，即一个子查询只能返回一行值的子查询)。

通常情况下，嵌套连接的成本要比平级连接的成本要大。这好比我们写程序中的多层循环语句，代价是OutLoop*InnerLoop,所以，在ORACLE中，有专门的针对嵌套子查询，或嵌套连接的优化方法，叫做展平(Unnesting of Nested Subqueries or Nested Joins),或者说解嵌套。

<2> 如果不可避免的会有用到嵌套连接，则尽量使用EXISTS，而不要使用IN

比如下面的语句：

SELECT *

FROM tab1,tab2…

WHERE tab1.col in (select tab3.col from tab3 where tab3.col2=tab1.col2 )

如果我们的业务逻辑不可避免，一定要用到嵌套连接，ORACLE中也把这种查询叫做关联子查询(Correlated Subqueries),则我们通常可以用EXISTS来实现，而不用IN来实现。当然，这是指通常的情况，而不是绝对。我们也不是说EXISTS一定比IN效率高，但根据我个人的情况，大部分情况下，使用 EXISTS会比IN效果好，所以上面的语句可以这样实现：

SELECT *

FROM tab1,tab2…

WHERE EXISTS (select ‘X’ from tab3 where tab3.col1=tab1.col1 )

那么，为什么在嵌套连接中EXISTS通常会比IN效率好呢？嵌套连接好比是2个嵌套的循环，对于外部循环中的每一行，都要到内部循环中去搜索(或者说遍历)一遍，EXISTS和IN的差别就在于，EXISTS在内部循环中只到找到第1行，它就跳出内部循环，没有必要继续往下搜索，这样就结省了一部分搜索内部循环的成本。而IN呢？IN的情况是，即使是在内部循环中找到1行，它也会继续搜索下去，直到遍历完内部循环的全部记录。我用下图来解释一下：

OuterLoop:tab1
Row2
Rowm
Row1
找到，跳出内循环
InnerLoop:tab3
Row1
Row2
Rown
OuterLoop:tab1
Row2
Rowm
Row1
找到,继续遍历内循环
InnerLoop:tab3
Row1
Row2
Rown
(EXISTS) (IN)

COST=m*1 COST=m*n

<3> 何时用EXISTS，何时用IN，ORACLE有一个建议，大家可以参考一下：

n 如果在子查询中有包含选择性的谓词(过滤器filter),并且在这个谓词字段或连接字段(join column)上有建索引,则使用IN

n 如果在父查询中包含选择性的谓词(过滤器filter),则采用EXISTS

我们来看下面这个SQL:

SELECT * FROM departments

WHERE EXISTS

(SELECT * FROM employees

WHERE departments.department_id = employees.department_id

AND employees.salary > 2500);

如果employee的salary这个字段的差异值比较多(即具有很多不同的字段值,这表示选择性selectivity比较好),并且salary字段有建索引,很显然,我们应该采用IN:

SELECT *
FROM DEPARTMENTS
WHERE DEPARTMENT_ID IN
(SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE EMPLOYEES.SALARY > 2500);

如果salary字段具有很多相同的字段值即选择性不好,且没有建索引,则采用EXISTS性能会好一些.或者假如department这个表多了一个谓词,且这个谓词很具选择性,则采用EXISTS,这样,在评估EXISTS里面的子查询时,外部的父查询先可以应用这个谓词过滤一些数据,这样就减少了EXISTS内部子查询所需要访问的数据量.

IN和EXISTS在ORACLE中都称之为半连接（semi-join）.半连接的意思可能是这么来的，查询只能返回（即SELECT LIST）IN/EXISTS外部表的字段，它消除了连接中来自内表（即IN或EXISTS里面的表）的冗余值。所以，IN和EXISTS在某些情况下也都能达到“去重”的目的，但是ORACLE在处理IN和EXISTS时所采用的方法还是不同的，以下面的SQL为例：

SELECT d.*
FROM departments d
WHERE d.department_id IN (SELECT/*+ NO_UNNEST */ e.department_id FROMemployees e)

如果ORACLE优化器并没将这个子查询展平成连接,则针对in的外部表DEPARTMENTS的每一行，ORACLE都会执行一下子查询SELECT e.department_idFROM employees e，并且将子查询返回的department_id进行排序和去除重复值，然后拿departments的department_id与子查询返回的结果集进行比较，如果是该结果集的成员，则查询返回外部表的记录。

所以IN比EXISTS多了一个成本：排序去重。所以要将IN改写成JOIN的话，先要将IN内表作一个排序去重的操作（加上DISTINCT），上面的SQL改写成JOIN就是下面这个样子：

SELECT d.*
FROM departments d,(SELECT DISTINCT department_id FROM employees ) e
WHERE d.department_id=e.department_id

2.用Where子句替换GROUP BY的HAVING子句
因为GROUP BY的 HAVING子句是在对数据进行分组后才对结果集进行过滤,这个处理需要排序,汇总合计等操作,所以如果能通过WHERE子句限制需要进行分组(grouping)的记录的数目,那就能减少这方面的开销,我们来看下面的例子:

低效:

SELECT REGION，AVG(LOG_SIZE)

FROM LOCATION

GROUP BY REGION

HAVING REGION REGION != ‘SYDNEY’

AND REGION != ‘PERTH’

高效

SELECT REGION，AVG(LOG_SIZE)

FROM LOCATION

WHERE REGION REGION != ‘SYDNEY’

AND REGION != ‘PERTH’

GROUP BY REGION

我们要尽量把过滤条件写在where子句中,而不是写在having子句中,这是一条非常实用的原则.

3.在有用到union,minus,intersect,group by等sql语句中不使用distinct和order by
因为union,minus,intersect,group by已经会对相关的字段进行排序和去除重复值,所以就没有必要在这些SQL语句中再使用distinct和order by子句,如下面的SQL语句:

SELECT distinct LOCATION_ID, DEPARTMENT_NAME “Department”, TO_CHAR(NULL) “Warehouse”

FROM DEPARTMENTS
UNION
SELECT distinct LOCATION_ID, TO_CHAR(NULL) “Department”, WAREHOUSE_NAME
FROM WAREHOUSES

Order by LOCATION_ID;

上面的SQL中,由于union会对每个查询中的结果集进行排序去从(sort unique)操作,所以再添加distinct和order by location_id是没有必要的.

4.不要在创建有索引的谓词字段上运用函数
如果在谓词字段上有运用函数,如类型转换函数(to_char(),to_date(),to_number()..)和大小写转换函数(upper(),lower()),则既使这些字段上有建索引,ORACLE优化器也不会使用这个索引,除非有建立基于函数的索引(function based index).我们来看下面的例子:

SELECT * FROM pm_role
WHERE UPPER(NAME)=’RAINNY’


上面的SQL中,由于谓词字段:name上有运用一个upper()函数,所以既使我们在pm_role.name上有建一个索引,Oracle也不会用到这个索引,所以我们一般要把函数写到等号(=)的右边.

5.不要在创建有索引的谓词字段上运用操作符
如果有对谓词字段运用操作符,则ORACLE同样不会用到索引,我们来看下面的例子:

SELECT * FROM pm_role
WHERE ‘R’||NAME=’RAINNY’

上面的SQL中因为对谓词有用到||操作符,所以ORACLE优化器也不会用到name字段上的索引

6.避免谓词发生隐式的数据类型转换
SELECT *
FROM PM_ROLE
WHERE CODE >= ‘1000003002001002001002000000000000′
CODE <= ‘1000003002001002001002999999999999′

PM_ROLE.CODE的数据类型是数值型,在上面的SQL中,WHERE条件输入的是字符串型,这样就发生了隐式的类型转换,ORACLE会将CODE字段值从数值型转换为字符串型,然后与右边的条件进行比较运算,这时,既使CODE字段上有建索引,ORACLE优化器也用不到索引.所以,我们要避免谓词发生隐式的数据类型转换,确保输入的WHERE条件和谓词字段的数据类型一致.

7.使用通配符会使oracle不去使用索引
SELECT ename FROM emp
WHERE ename LIKE ‘%C%’

应改成

SELECT ename FROM emp
WHERE ename LIKE ‘C%’

8.尽量避免在COUNT(*)时使用ORDER BY
当我们使用COUNT（*）统计总记录数时，尽量不要在SELECT语句中加入ORDER BY ：

select count(*) from pt_sche_detail order by itemname

这个要将order by 去掉，改成：

select count(*) from pt_sche_detail