SQL语句的优化

SQL优化的原则是：将一次操作需要读取的BLOCK数减到最低。

调整不良SQL通常可以从以下几点切入：
检查不良的SQL，考虑其写法是否还有可优化内容;
检查子查询考虑SQL子查询是否可以用简单连接的方式进行重新书写;
检查优化索引的使用;
考虑数据库的优化器;

----------------------------------------------------------------------------------
--查询的一般规则
---------------------------------------------------------------------------------
1. 明确指出检索的字段，尽量减少对多余的列与多余的行读取。
 避免使用 select * from table_name ……的方式访问表。

 ps.by scholar:简单说.就是要哪列数据取哪列数据.实际应用当中还没有取全部数据的情况.至少我还没有遇到.如果只是简单的寻找是否有某些数据.并直接操作数据库且数据量不大的话.这样写倒是无所谓.但如果写在程序里.你全取出来啥用?还能择(zhai)的开吗?

2. 在一个SQL语句中，如果一个where条件过滤的数据库记录越多，定位越准确，则该where条件越应该前移。

 ps.by scholar:这点倒是应该注意.平时写sql的时候.常常忽略这一点.或者说根本就不知道这一点.关键还是不知道数据库究竟是怎么处理你的sql的.说实话.到现在我也不知道.以后应该会知道.如果我想的话.

3. 查询时尽可能使用索引覆盖。即对SELECT的字段建立复合索引，这样查询时只进行索引扫描，不读取数据块。

 ps.by scholar:我的出身不好.不是计算机专业.数据库学的也很一般.就知道 select. 哪儿知道啥是索引.所以刚抽空儿补了补课.索引这东西吧.是个好东西.对于小型数据库或者数据量不大的数据库来说.一般用不到.既然不会用.我也不去用它!.是不是很消极?.总之啦.一时半会儿我还接触不到大型的数据库.用的时候再抱佛脚吧.

4. 在判断有无符合条件的记录时不要用SELECT COUNT (*) 语句。 如：
 BF: select count(*) from table where condition
 在MSSQL和Sybase中，使用 if exists (select 1 from table_name where condition)性能较好，
 在Oracle中，使用select 1 from table_name where condition较好。

 ps.by scholar:SQLServer中我用select count(1) from table_name 也好使...要单纯就是为了知道有多少列.就这么用吧.

5. 内层限定原则
 在拼写SQL语句时，将查询条件分解、分类，并尽量在SQL语句的最里层进行限定，以减少数据的处理量。
 应绝对避免在order by子句中使用表达式。

 ps.by scholar:我不知道这里说的最里层是不是子查询.如果是.那么得说清楚.有些查询条件就得写里边.有些也非得写外边.(这是不是较真儿了有点儿)...总之.人家意思是能写里边限定就写里边!.平时写sql.用到order by 的要么特繁琐.要么就是要排个序.前者真不敢随便order by 表达式.后者...马马虎虎的有时候也用.但都是比较简单的表达式.(难的也写不出来.)

----------------------------------------------------------------------------------
--正确使用INDEX的SQL(INDEX就是索引.s-u-o-y-i-n)
--如果和查询条件相关的column上有建index,以下几点能帮助SQL正确的使用index 。
----------------------------------------------------------------------------------
ps.by scholar:索引的问题.上边说过了.不啰嗦了.以下就单纯的对照sql语句说吧...

1. 避免显式或隐含的类型转换。
 where子句中存在数据类型隐形转换的,如用Numeric 型和 Int型的列的比较时，不能使用index。

 ps.by scholar:转换就已经折磨了数据库一次了.index就省省吧...

2. WHERE子句中任何对列的操作都将无法使用index，它包括数据库函数、计算表达式等等，所以要尽量减少在＝左边的列的运算。如：
BF：select staff_no, staff_name from staff_member where salary*2 <= 10000;
RP：select staff_no, staff_name from staff_member where salary <= 5000;

 ps.by scholar:以前没注意过这个问题.还真是不知道.这是不是和order by不加表达式有相同的道理?.未知.不过既然有这么个道理.那以后就按套路出牌吧.就像我的某老师说的.:你先知道怎么用.别问为什么!

WHERE子句中使用不等于(<>)运算的，将无法使用index。可以用union all改写。如：
 BF：select staff_no, staff_name from staff_member where dept_no<>2001;
 RP：select staff_no, staff_name from staff_member where dept_no < 2001
    Union all select staff_no, staff_name from staff_member where dept_no > 2001;
Oralce中可以考虑使用函数索引。

 ps.by scholar:我理解里边所说的"无法使用index".应该是index不起作用的意思吧...还有.我不知道用 union all 会不会影响sql的执行效率.按范围查两次然后把结果集放一起和查一次到底哪个快点?.还是数据库底层的知识问题...赶紧补课!.

3.  WHERE子句中使用substr字符串函数的，将无法使用index，可以用like改写。如：
BF：select staff_no, staff_name from staff_member where substr(last_name,1,4)=’FRED’;
RP：select staff_no, staff_name from staff_member where last_name like ’FRED％’;

 ps.by scholar:听说 like 用法挺精妙的.但是没深入了解过.

4.  WHERE子句中‘%’通配符在第一个字符的，将无法使用index。如:
select staff_no, staff_name from staff_member where first_name like ‘%DON’;
这种情况的优化方式比较复杂，在后面有关index优化的内容中我们介绍一种在oracle中使用反向索引的优化方式。

 ps.by scholar:我没往后看.不知道有没有...

5.  LIKE语句后面不能跟变量，否则也不会使用索引。
where Prod_name like :v_name || '%' -- 不会使用索引
如果一定要使用变量，可以使用如下技巧：
where Prod_name between :v_name and :v_name || chr(255) -- 会使用索引

6. WHERE子句中使用IS NULL和IS NOT NULL不会使用索引。好的设计习惯是表中尽量不使用允许为空的字段，可以根据业务逻辑，将字段设为NOT NULL的同时，提供一个DEFAULT值。另外，当表中建有索引的字段包含NULL时，索引的效率会降低。

 ps.by scholar: 这种"好的设计习惯"我还真是没有...突然觉得.还真是得有.不过想想.如果每个字段都不能为空.对业务逻辑的要求实在太高了.很多时候.开发者都是在误区里徘徊:用提高服务器的硬件要求来弥补被烂程序搞慢的效率.幸亏现在我们公司做的大多是B/S结构的东西.如果做C/S的.很难想象到不堪设想的地步.

7.  WHERE子句中使用字符串连接(||)的，将无法使用index。我们应该改写这个查询条件。如：
BF：select staff_no, staff_name from staff_member where first_name||''||last_name ='Beill Cliton';
RP：select staff_no, staff_name from staff_member where first_name ＝‘Beill’ and last_name ='Cliton';
 
 ps.by scholar: 平时写sql.我真不会按照第一种写法写.觉得那种写法.有点儿"臭显".没必要.后者的写法就一个字儿:清楚! 

8.  WHERE条件中使用’in’子句的情况,如：
BF：select count(*) from staff_member Where id_no in (‘0’,’1’);
WHERE条件中的'in'在逻辑上相当于'or'，所以语法分析器会将in ('0','1')转化为id_no ='0' or id_no='1'来执行。我们期望它会根据每个or子句分别查找，再将结果相加，这样可以利用id_no上的索引；但实际上（根据showplan）,它却采用了"OR策略"，即先取出满足每个or子句的行，存入临时数据库的工作表中，再建立唯一索引以去掉重复行，最后从这个临时表中计算结果。我们可以将or子句分开：
RP：DECLARE
a integer;
        b integer;
BEGIN
   select count(*) into a from stuff where id_no='0';
   select count(*) into b from stuff where id_no='1';
a := a + b;
END;

9. 如果在table上创建了一个顺序为col1，col2，col3的复合index时，在查询中只有以下三种where条件子句能有效的使用

index：
…where col1= @col1 and col2= @col2 and col3= @col3;
…where col1= @col1 and col2= @col2;
…where col1= @col1

 ------------------------------------------------------------------------------------
--子查询的调整
--------------------------------------------------------------------------------------
a.       调整具有IN和EXISTS子句的子查询

具有IN的子查询：
BF：
   select emp_id from EMP
   where dep_id IN ( select dep_id from DEP
where dep_no = ‘001’);

具有EXISTS的子查询：
BF：
   select emp_id from EMP e
   where exists ( select dep_id from DEP d
where e.dep_id = d.dep_id
and d.dep_no = ‘001’);

用对等连接调整具有IN和EXISTS的子查询：
上面的例子中的子查询有两种情况，dep_id unique和nounique。
当dep_id是unique,
RP：
   select e.emp_id from EMP e, DEP d
   where e..dep_id = d.dep_id and d.dep_no = ‘001’；

当dep_id是nounique,   RP：
 select e.emp_id from EMP e,
(select distinct dep_id from DEP where dep_no = ‘001’ ) d
where e..dep_id = d.dep_id；
需要注意的是，具有IN子句的非关联子查询和EXISTS子句的关联子查询，Oracle的优化器虽然能将其转换为标准的连接操作，但Oracle转换用的是NESTED LOOPS连接操作，而且有很多其他因素支配着SQL优化器是否将一个子查询自动转换为一个连接操作。首先，连接操作的两个数据表列通常都应该有唯一的数据索引。所以，我们应该自己重写这些子查询。

b. 调整具有NOT IN和NOT EXISTS子句的子查询
具有NOT IN的子查询：
BF：
         select emp_id from EMP
         where dep_id NOT IN ( select dep_id from DEP
where dep_no = ‘001’)；
具有NOT EXISTS的子查询：
BF：
  select emp_id from EMP e
   where NOT EXISTS ( select dep_id from DEP d
where e dep_id = d. dep_id and .d.dep_no = ‘001’)；
用外联接调整具有NOT IN和NOT EXISTS的子查询
RP：
   select e.emp_id from EMP e,DEP d
   where e.dep_id = d.dep_id(+)
        and d.dep_id is null
        and d.dep_no (+)= ‘001’；

c． 调整具有自连接的子查询
 所谓具有自连接的子查询，实际上是在一种特殊需求下使用的具有IN子句的关联子查询。我们可以用连接的方式重写该子查询。

例如以下的需求：
查询每个部门中工资高于该部门平均工资的员工ID,名称，工资，部门ID：
  BF：
select a.emp_id ,a.emp_name,a.emp_salary,a.dep_id from salary a
where a.emp_salary >
(select avg(b.emp_salary) from salary b where b.dep_id = a.dep_id )；

  RP：
select a.emp_id ,a.emp_name,a.emp_salary,a.dep_id
from salary a,
(select dep_id,avg(emp_salary) avg_salary from salary group by dep_id) b
where a.dep_id = b.dep_id
and a.emp_salary > b.avg_salary；

---------------------------------------------------------------------------
--使用绑定变量优化SQL
---------------------------------------------------------------------------
使用绑定变量可以提高Library Cache的Hit Ratio，减少SQL语句的重编译，从而达到提高查询效率的目的。
BF：
SQL> ***** system flush shared_pool;
系统已更改。
SQL> declare
 2    type rc is ref cursor;
 3    l_rc rc;
 4    l_dummy all_objects.object_name%type;
 5    l_start number default dbms_utility.get_time;
 6 begin
 7    for i in 1..1000
 8    loop
 9       open l_rc for
 10       ''select object_name
 11           from all_objects
 12        where object_id =''|| i;
 13        fetch l_rc into l_dummy;
 14        close l_rc;
 15     end loop;
 16     dbms_output.put_line
 17     (round((dbms_utility.get_time - l_start)/100,2)||'' seconds...'');
 18 end;
 19 /
18.36 seconds...
PL/SQL 过程已成功完成。

这是从all_objects进行的单条查询，在循环1000次的情况下，Oracle对其进行的1000次编译，需要18.36秒才能完成。
RP：
SQL> ***** system flush shared_pool;
系统已更改。
SQL> declare
 2    type rc is ref cursor;
 3    l_rc rc;
 4    l_dummy all_objects.object_name%type;
 5    l_start number default dbms_utility.get_time;
 6 begin
 7    for i in 1..1000
 8    loop
 9       open l_rc for
 10       ''select object_name
 11           from all_objects
 12        where object_id =:x''
 13        using i;
 14        fetch l_rc into l_dummy;
 15 close l_rc;
 16     end loop;
 17     dbms_output.put_line
 18     (round((dbms_utility.get_time - l_start)/100,2)||'' seconds...'');
 19 end;
 20 /
.56 seconds...
PL/SQL 过程已成功完成。
这是改用绑定变量之后的结果。这时Oracle对该PL/SQL 进行1次编译。执行时间明显减少。我在分别提交这两个PL/SQL 之前都执行了***** system flush shared_pool;命令，以保证对比结果的真实有效。

------------------------------------------------------------------------------------
--减少查询的次数
------------------------------------------------------------------------------------
在一次执行多条SQL时，考虑正确的SQL顺序，减少查询的次数。如：
 BF:
IF NOT EXISTS(SELECT count(*) FROM Item WHERE fchrItemID=@chrItemID and fchrA=@chrA)
    INSERT INTO Item (fchrItemID ,fchrItem ,fchrItemName , fchrA , flotQ , flotMQ )
VALUES (@chrItemID , @chrItem , @chrItemName , @chrA , -1*@lotQ , -1*@lotMQ )
else
      UPDATE Item SET flotQ=flotQ-@lotQ,flotMQ=flotMQ-@lotMQ
      WHERE fchrItemID=@chrItemID and fchrA=@chrA
对于这个SQl来说，select和update对Item做了两次查询操作。实际上我们只需要一次查询就可以实现功能。
RP：
UPDATE Item SET flotQ=flotQ-@lotQ,flotMQ=flotMQ-@lotMQ
WHERE fchrItemID=@chrItemID and fchrA=@chrA
IF @@rowcount = 0
    INSERT INTO @List (fchrItemID ,fchrItem ,fchrItemName , fchrA , flotQ , flotMQ )
    VALUES (@chrItemID , @chrItem , @chrItemName , @chrA , -1*@lotQ , -1*@lotMQ )

ps.by scholar: 以上没有ps.的东西.是凭我的能力不能信口开河的.得三思着说...暂时思不到三.就暂时不说.
   ...一个朋友说.一个好的数据库程序员薪水很可观的.数据库程序员不止会写sql.还得懂数据库的原理的什么什么过程.好的数据库程序员不仅要懂原理过程.还得...反正挺牛逼的.我不是数据库程序员.所以..我暂时要求自己:要会写'好'的sql..是不是很没追求.?.嗯.不是...我说不是就不是.没得商量!.