写高效的SQL语句

SQL优化总结

1，where的条件顺序影响查询速度
WHERE子句中的连接顺序：
ORACLE采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前，那些可以
过滤掉最大数量记录的条件必须写在WHERE子句的末尾。

2，SELECT * FROM TABLENAME WHERE A = "A" AND 1=1如何优化？

3，不要SELECT * 。也就是说查询的字段要尽量少。
为什么不写SELECT *呢，第一个是因为，数据库会首先把*，转化成表中的所有字段，这个是数据库引擎完成的，增加
了数据库的工作量。这个转换工作是通过查询数据字典来完成的。

4，WHERE 条件里的字段加索引。

5，如果表太大, 并且做过分区表的话, 指定分区表。

6，1=1 一般解释器是忽略的。

7，查询的模糊匹配
尽量避免在一个复杂查询里面使用 LIKE '%parm1%'—— 红色标识位置的百分号会导致相关列的索引无法使用，
最好不要用。
解决办法:
其实只需要对该脚本略做改进，查询速度便会提高近百倍。改进方法如下：
a、修改前台程序——把查询条件的供应商名称一栏由原来的文本输入改为下拉列表，用户模糊输入供应商名称时，直接
在前台就帮忙定位到具体的供应商，这样在调用后台程序时，这列就可以直接用等于来关联了。
b、直接修改后台——根据输入条件，先查出符合条件的供应商，并把相关记录保存在一个临时表里头，然后再用临时表
去做复杂关联

8，索引问题
在做性能跟踪分析过程中，经常发现有不少后台程序的性能问题是因为缺少合适索引造成的，有些表甚至一个索引都
没有。这种情况往往都是因为在设计表时，没去定义索引，而开发初期，由于表记录很少，索引创建与否，可能对性
能没啥影响，开发人员因此也未多加重视。然一旦程序发布到生产环境，随着时间的推移，表记录越来越多这时缺少
索引，对性能的影响便会越来越大了。这个问题需要数据库设计人员和开发人员共同关注法则：不要在建立的索引的
数据列上进行下列操作:
◆避免对索引字段进行计算操作
◆避免在索引字段上使用not，<>，!=
◆避免在索引列上使用IS NULL和IS NOT NULL
◆避免在索引列上出现数据类型转换
◆避免在索引字段上使用函数
◆避免建立索引的列中使用空值。

9，部分UPDATE、SELECT 语句 写得很复杂（经常嵌套多级子查询）——可以考虑适当拆成几步，先生成一些临时数据表，再进行关联操作。

10，使用UNION ALL 代替 UNION
UNION 因为会将各查询子集的记录做比较，故比起UNION ALL ，通常速度都会慢上许多。一般来说，如果使用UNION
ALL能满足要求的话，务必使用UNION ALL。还有一种情况大家可能会忽略掉，就是虽然要求几个子集的并集需要过滤掉
重复记录，但由于脚本的特殊性，不可能存在重复记录，这时便应该使用UNION ALL，如xx模块的某个查询程序就曾经存
在这种情况，见，由于语句的特殊性，在这个脚本中几个子集的记录绝对不可能重复，故可以改用UNION ALL）

11，在WHERE过滤条件中，尽量避免对索引进行计算

12，避免在WHERE过滤条件中，使用IN，NOT IN，OR，HAVING。可以使用 EXIST 和NOT EXIST代替 IN和NOT IN。可以
使用表链接代替 EXIST。HAVING可以用WHERE代替，如果无法代替可以分两步处理。

13，不要把数字定义为字符串
SELECT AGE FROM TABLENAME WHERE AGE = '29';  -- 把AGE定义为字符串varchar
SELECT AGE FROM TABLENAME WHERE AGE = 29;    -- 把AGE定义为整形 int
为什么呢？因为这样做会导致索引无效，产生全表扫描。日期同理。

14，避免使用耗费资源的操作，带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎 执行，
耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序。

15，优化器根据何种原则来删除索引
在多数情况下，ORACLE使用索引来更快地遍历表，优化器主要根据定义的索引来提高性能。但是，如果在SQL语句的
WHERE子句中写的SQL代码不合理，就会造成优化器删去索引而使用全表扫描，一般就这种SQL语句就是所谓的劣质SQL
语句。在编写SQL语句时我们应清楚优化器根据何种原则来删除索引，这有助于写出高性能的SQL语句。

15.1 如果某列存在空值（NULL或者''），即使对该列建索引也不会提高性能，因为这将导致索引失效。任何在WHERE
子句中使用IS NULL或IS NOT NULL的语句优化器是不允许使用索引的。

15.2. 联接列
　　对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的。我们一起来看一个例子，假定有
一个职工表(employee)，对于一个职工的姓和名分成两列存放(FIRST_NAME和LAST_NAME)，现在要查询一个叫
比尔.克林顿(Bill Cliton)的职工。
下面是一个采用联接查询的SQL语句，
SELECT * FROM EMPLOYSS WHERE FIRST_NAME||''||LAST_NAME ='Beill Cliton';
上面这条语句完全可以查询出是否有Bill Cliton这个员工，但是这里需要注意，系统优化器对基于LAST_NAME
创建的索引没有使用。当采用下面这种SQL语句的编写，ORACLE系统就可以采用基于LAST_NAME创建的索引。
SELECT * FROM EMPLOYSS WHERE FIRST_NAME ='Beill' AND LAST_NAME ='Cliton';

15.3带通配符(%)的like语句
　　同样以上面的例子来看这种情况。目前的需求是这样的，要求在职工表中查询名字中包含cliton的人。可以采用如下的查询SQL语句:
SELECT * FROM EMPLOYEE WHERE LAST_NAME LIKE '%cliton%';
这里由于通配符(%)在搜索词的首位置出现，所以Oracle系统不使用last_name的索引。在很多情况下可能无法避免这种
情况，但是一定要心中有底引得到了使用:
SELECT * FROM EMPLOYEE WHERE LAST_NAME LIKE 'c%';

15.4. Order by语句
　　ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制，
也可以将函数加入列中(象联接或者附加等)。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。
仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写order by
语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。

15.5. NOT
　　我们在查询时经常在WHERE子句使用一些逻辑表达式，如大于、小于、等于以及不等于等等，也可以使用
AND/OR/NOT。NOT可用来对任何逻辑运算符号取反。下面是一个NOT子句的例子:
WHERE NOT (STATUS ='VALID')
如果要使用NOT，则应在取反的短语前面加上括号，并在短语前面加上NOT运算符。NOT运算符包含在另外一个逻辑运算
符中，这就是不等于(<>)运算符。换句话说，即使不在查询WHERE子句中显式地加入NOT词，NOT仍在运算符中，见下例:
WHERE STATUS <>'INVALID';
对这个查询，可以改写为不使用NOT:
SELECT * FROM EMPLOYEE WHERE SALARY < 3000 OR SALARY > 3000;
虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许ORACLE对SALARY列使用
索引，而第一种查询则不能使用索引。

16，选择最有效率的表名顺序(只在基于规则的优化器中有效)：
ORACLE 的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被
最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查
询，那就需要选择交叉表(intersection table)作为基础表，交叉表是指那个被其他表所引用的表。

17，减少访问数据库的次数。
ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等；每访问一次，都要进行这
一系列的操作。和建立一个线程的开销类似，每次访问数据库也会带来较大的系统开销。

18，整合简单,无关联的数据库访问：
如果你有几个简单的数据库查询语句，你可以把它们整合到一个查询中(即使它们之间没有关系) 。

19，用WHERE子句替换HAVING子句：
避免使用HAVING子句，HAVING只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序，总计等操作。 如果
能通过WHERE子句限制记录的数目，那就能减少这方面的开销。(非ORACLE中)ON、WHERE、HAVING这三个都可以加条件的
子句中，ON是最先执行，WHERE次之，HAVING最后，因为ON是先把不 符合条件的记录过滤后才进行统计，它就可以减少
中间运算要处理的数据，按理说应该速度是最快的，WHERE也应该比HAVING快点的，因为它过滤数据后 才进行SUM，在两
个表联接时才用ON的，所以在一个表的时候，就剩下WHERE跟HAVING比较了。在这单表查询统计的情况下，如果要过滤的
条件没有涉及到要计算字段，那它们的结果是一样的，只是WHERE可以使用RUSHMORE技术，而HAVING就不能，在速度上后
者要慢如果要涉及到计算的字 段，就表示在没计算之前，这个字段的值是不确定的，根据上篇写的工作流程，WHERE的
作用时间是在计算之前就完成的，而HAVING就是在计算后才起作 用的，所以在这种情况下，两者的结果会不同。在多表
联接查询时，ON比WHERE更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由WHERE进
行过滤，然后再计算，计算完后再由HAVING进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件
应该在什么时候起作用，然后再决定放在那里。

20，减少对表的查询：
在含有子查询的SQL语句中,要特别注意减少对表的查询.例子：
SELECT  
    TAB_NAME
FROM
    TABLES
WHERE
    (TAB_NAME,DB_VER) = (
        SELECT
            TAB_NAME,DB_VER
        FROM  
            TAB_COLUMNS  
        WHERE  
            VERSION = 604
        )

21，使用表的别名(Alias)：
当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少
那些由Column歧义引起的语法错误。

22，识别'低效执行'的SQL语句：
虽然目前各种关于SQL优化的图形化工具层出不穷,但是写出自己的SQL工具来解决问题始终是一个最好的方法：
SELECT  EXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROM  V$SQLAREA
WHERE  EXECUTIONS>0
AND  BUFFER_GETS > 0
AND  (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY  4 DESC;

23，sql语句用大写的；因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行

24，用>=替代>
高效:
SELECT * FROM  EMP  WHERE  DEPTNO >=4
低效:
SELECT * FROM EMP WHERE DEPTNO >3
两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第
一个DEPT大于3的记录.


25，优化GROUP BY:
提高GROUP BY 语句的效率，可以通过将不需要的记录在GROUP BY 之前过滤掉。下面两个查询返回相同结果但第二个
明显就快了许多。
低效:
SELECT JOB , AVG(SAL)
FROM EMP
GROUP by JOB
HAVING JOB = ‘PRESIDENT'
OR JOB = ‘MANAGER'
高效:
SELECT JOB , AVG(SAL)
FROM EMP
WHERE JOB = ‘PRESIDENT'
OR JOB = ‘MANAGER'
GROUP by JOB

26，不要给“性别”列创建索引
首先，我们必须了解索引是如何加速对表的访问的。你可以将索引理解为基于一定的标准上对表进行划分的一种方式。如果你给类似于“性别”这样的列创建了一个 索引，你仅仅是将表划分为两部分：男和女。你在处理一个有1,000,000条记录的表，这样的划分有什么意义？记住：维护索引是比较费时的。当你设计索 引时，请遵循这样的规则：根据列可能包含不同内容的数目从多到少排列，比如：姓名+省份+性别。

27，不要忽略同时修改同一记录的问题
有时候，两个用户会同时修改同一记录，这样，后一个修改者修改了前一个修改者的操作，某些更新就会丢失。处理这种情况不是很难：创建一个timestamp字段，在写入前检查它，如果允许，就合并修改，如果存在冲突，提示用户。