Sql优化文档

1. 目的

   数据库参数进行优化所获得的性能提升全部加起来只占数据库应用系统性能提升的40%左右，其余60%的系统性能提升全部来自对应用程序的优化。许多优化专家甚至认为对应用程序的优化可以得到80%的系统性能提升。因此可以肯定，通过优化应用程序来对数据库系统进行优化能获得更大的收益。

   对应用程序的优化通常可分为两个方面: 源代码的优化和SQL语句的优化。由于涉及到对程序逻辑的改变，源代码的优化在时间成本和风险上代价很高(尤其是对正在使用中的系统进行优化) 。另一方面，源代码的优化对数据库系统性能的提升收效有限，因为应用程序对数据库的操作最终要表现为SQL语句对数据库的操作。

        对SQL语句进行优化有以下一些直接原因：

1. SQL语句是对数据库(数据) 进行操作的惟一途径，应用程序的执行最终要归结为SQL语句的执行，SQL语句的效率对数据库系统的性能起到了决定性的作用。

2. SQL语句消耗了70%～90%的数据库资源。

3. SQL语句独立于程序设计逻辑，对SQL语句进行优化不会影响程序逻辑，相对于对程序源代码的优化，对SQL语句的优化在时间成本和风险上的代价都很低。

4. SQL语句可以有不同的写法，不同的写法在性能上的差异可能很大。

5. SQL语句易学，难精通。SQL语句的性能往往同实际运行系统的数据库结构、记录数量等有关，不存在普遍适用的规律来提升性能。

二. 优化数据库的思想：

1、关键字段建立索引。

2、使用存储过程，它使SQL变得更加灵活和高效。

3、备份数据库和清除垃圾数据。

4、SQL语句语法的优化。

5、清理删除日志。

三．SQL语句优化的原则：

    不要以为只有SELECT语句是查询。实际上，带有任何WHERE条件的 DML(INSERT、UPDATE、DELETE)语句中都包含查询要求，在后面的文章中，当说到查询时，不一定只是指SELECT语句，也有可能指 DML语句中的查询部分。

    我们知道，SQL语句同其它语言(如C语言)的语句不一样，它是非过程化(non-procedural)的语句，即当你要取数据时，不需要告诉数据库通 过何种途径去取数据，如到底是通过索引取数据，还是应该将表中的每行数据都取出来，然后再通过一一比较的方式取数据(即全表扫描)。

    为了实现一个查询，内核必须为每个查询定制一个查询策略，或为取出符合条件的数据生成一个执行计划(execution plan)。典型的，对于同一个查询，可能有几个执行计划都符合要求，都能得到符合条件的数据。例如，参与连接的表可以有多种不同的连接方法，这取决于连 接条件和优化器采用的连接方法。为了在多个执行计划中选择最优的执行计划，优化器必须使用一些实际的指标来衡量每个执行计划使用的资源(I/0次数、 CPU等)，这些资源也就是我们所说的代价(cost)。如果一个执行计划使用的资源多，我们就说使用执行计划的代价大。以执行计划的代价大小作为衡量标 准，优化器选择代价最小的执行计划作为真正执行该查询的执行计划，并抛弃其它的执行计划。

    3.1 选择最有效率的表名顺序

    在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.

基础表(Driving Table)是指被最先访问的表(通常以全表扫描的方式被访问).
例如:
表 TAB1 16,384 条记录
表 TAB2 1 条记录
选择TAB2作为基础表 (最好的方法)
select count(*) from tab1,tab2 执行时间0.96秒 oracle测试标准
选择TAB2作为基础表 (不佳的方法)
select count(*) from tab2,tab1 执行时间26.09秒
如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.
例如:
EMP表描述了LOCATION表和CATEGORY表的交集.
SELECT *
FROM LOCATION L ,
CATEGORY C,
EMP E
WHERE E.EMP_NO BETWEEN 1000 AND 2000
AND E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN

将比下列SQL更有效率
SELECT *
FROM EMP E ,
LOCATION L ,
CATEGORY C
WHERE E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
AND E.EMP_NO BETWEEN 1000 AND 2000
3.3 WHERE子句中的连接顺序

采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。同时在链接的表中能过滤的就应该先进行过滤。

例如:
(低效,执行时间156.3秒)
SELECT …
FROM EMP E
WHERE SAL > 50000
AND JOB = 'MANAGER'
AND 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO);
(高效,执行时间10.6秒)
SELECT …
FROM EMP E
WHERE 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO)
AND SAL > 50000
AND JOB = 'MANAGER';

3.2  SELECT子句中避免使用 ' * '

当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 '*' 是一个方便的方法.但这是一个非常低效的方法. 实际上,在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.

3.3 尽量多使用COMMIT

    只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:
COMMIT所释放的资源:
a. 回滚段上用于恢复数据的信息.
b. 被程序语句获得的锁
c. redo log buffer 中的空间
3.4 计算记录条数

和一般的观点相反, count(*) 比count(1)稍快 , 当然如果可以通过索引检索,对索引列的计数仍旧是最快的. 例如 COUNT(EMPNO)

3.5 用Where子句替换HAVING子句

避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.

例如:
低效:
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；

HAVING 中的条件一般用于对一些集合函数的比较,如COUNT() 等等. 除此而外,一般的条件应该写在WHERE子句中

3.6通过内部函数提高SQL效率

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

3.8用EXISTS替代IN

3.9用NOT EXISTS替代NOT IN

3.10 用表连接替换EXISTS

通常来说 , 采用表连接的方式比EXISTS更有效率
SELECT ENAME
FROM EMP E
WHERE EXISTS (SELECT 'X'
FROM DEPT
WHERE DEPT_NO = E.DEPT_NO
AND DEPT_CAT = 'A');
(更高效)
SELECT ENAME
FROM DEPT D,EMP E
WHERE E.DEPT_NO = D.DEPT_NO
AND DEPT_CAT = 'A' ;

3.11用索引提高效率

索引是表的一个概念部分,用来提高检索数据的效率.

虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.

3.12避免在索引列上使用计算

WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描

举例:
低效：
SELECT …
FROM DEPT
WHERE SAL * 12 > 25000;
高效:
SELECT …
FROM DEPT
WHERE SAL > 25000/12;
非常实用的规则，请务必牢记

3.13 自动选择索引
如果表中有两个以上（包括两个）索引，其中有一个唯一性索引，而其他是非唯一性．
在这种情况下，ORACLE将使用唯一性索引而完全忽略非唯一性索引．
举例:
SELECT ENAME
FROM EMP
WHERE EMPNO = 2326
AND DEPTNO = 20 ;
这里，只有EMPNO上的索引是唯一性的，所以EMPNO索引将用来检索记录．
TABLE ACCESS BY ROWID ON EMP
INDEX UNIQUE SCAN ON EMP_NO_IDX

3.14 避免在索引列上使用NOT
通常，　我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的
影响.

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

3.16 用UNION替换OR (适用于索引列)
通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低.
在下面的例子中, LOC_ID 和REGION上都建有索引.
高效:
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10
UNION
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE REGION = "MELBOURNE"
低效:
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10 OR REGION = "MELBOURNE"
如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.

3.17 用IN来替换OR

低效:
SELECT….
FROM LOCATION
WHERE LOC_ID = 10
OR LOC_ID = 20
OR LOC_ID = 30
高效
SELECT…
FROM LOCATION
WHERE LOC_IN IN (10,20,30);
3.18 避免在索引列上使用IS NULL和IS NOT NULL
避免在索引中使用任何可以为空的列，

3.19 总是使用索引的第一个列

如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引.

3.20 用UNION-ALL 替换UNION ( 如果有可能的话)
当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序.
如果用UNION ALL替代UNION, 这样排序就不是必要了. 效率就会因此得到提高.

UNION 将对结果集合排序,这个操作会使用到SORT_AREA_SIZE这块内存

union:如果查询出来的结果中有重复记录，那么就去重 ，英文称之为"distinct"
union all:就显示所有的符合条件的记录，重复也保留

3.21  ORDER BY 子句只在两种严格的条件下使用索引.
ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.
ORDER BY中所有的列必须定义为非空.

3.22 避免改变索引列的类型.
当比较不同数据类型的数据时,自动对列进行简单的类型转换.
假设 EMPNO是一个数值类型的索引列.
SELECT …
FROM EMP
WHERE EMPNO = '123'
实际上,经过ORACLE类型转换, 语句转化为:
SELECT …
FROM EMP
WHERE EMPNO = TO_NUMBER('123')
幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变.
现在,假设EMP_TYPE是一个字符类型的索引列.
SELECT …
FROM EMP
WHERE EMP_TYPE = 123
这个语句被ORACLE转换为:
SELECT …
FROM EMP
WHERE TO_NUMBER(EMP_TYPE)=123
因为内部发生的类型转换, 这个索引将不会被用到!为了避免ORACLE对你的SQL进行隐式的类型转换, 最好把类型转换用显式表现出来. 注意当字符和数值比较时, ORACLE会优先转换字符类型到数字类型.
3.23 需要当心的WHERE子句
某些SELECT 语句中的WHERE子句不使用索引. 这里有一些例子.
在下面的例子里, '!=' 将不使用索引. 记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中. '+'是数学函数. 就象其他数学函数那样, 停用了索引

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

 

------------------------------SQL优化 2-----------------------------------------------------------------------------------------------------------

1． 索引的使用：
（1）.当插入的数据为数据表中的记录数量的10%以上，首先需要删除该表的索引来提高数据的插入效率，当数据插入后，再建立索引。
（2）.避免在索引列上使用函数或计算，在where子句中，如果索引是函数的一部分，优化器将不再使用索引而使用全表扫描。如：
低效：select * from dept where sal*12 >2500;
高效：select * from dept where sal>2500/12;
(3).避免在索引列上使用not和 “!=”，索引只能告诉什么存在于表中，而不能告诉什么不存在于表中，当数据库遇到not 和 “!=”时，就会停止使用索引而去执行全表扫描。
(4).索引列上>=代替>
  低效：select * from emp where deptno > 3
  高效：select * from emp where deptno >=4
两者的区别在于，前者dbms将直接跳到第一个deptno等于4的记录，而后者将首先定位到deptno等于3的记录并且向前扫描到第一个deptno大于3的。
(5).非要对一个使用函数的列启用索引，基于函数的索引是一个较好的方案。
2. 游标的使用：
当在海量的数据表中进行数据的删除、更新、插入操作时，用游标处理的效率是最慢的，但是游标又是必不可少的，所以正确使用游标十分重要：
(1). 在数据抽取的源表中使用时间戳，这样每天的维表数据维护只针对更新日期为最新时间的数据来进行，大大减少需要维护的数据记录数。
(2). 在insert和update维表时都加上一个条件来过滤维表中已经存在的记录，例如：
insert into dim_customer select * from ods_customer where ods_customer.code not exists (dim_customer.code)
  ods_customer为数据源表。dim_customer为维表。
(3). 使用显式的游标，因为隐式的游标将会执行两次操作，第一次检索记录，第二次检查too many rows这个exception,而显式游标不执行第二次操作。
3． 据抽取和上载时的sql优化：
(1). Where 子句中的连接顺序：
oracle采用自下而上的顺序解析where子句，根据这个原理，表之间的连接必须写在其他where条件之前，那些可以过滤掉大量记录的条件必须写在where子句的末尾。如：
低效：select * from emp e where sal>5000 and job = ‘manager’ and 25<(select count (*) from emp where mgr=e.empno);
高效：select * from emp e where 25<(select count(*) from emp where mgr=e.empno) and sal>5000 and job=’manager’;
(2). 删除全表时，用truncate 替代 delete,同时注意truncate只能在删除全表时适用，因为truncate是ddl而不是dml。
(3). 尽量多使用commit
只要有可能就在程序中对每个delete,insert,update操作尽量多使用commit,这样系统性能会因为commit所释放的资源而大大提高。
(4). 用exists替代in ，可以提高查询的效率。
(5). 用not exists 替代 not in
(6). 优化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 having job=’president’ or job=’manager’ group by job;
(7). 有条件的使用union-all 替代 union：这样做排序就不必要了，效率会提高3到5倍。
(8). 分离表和索引
  总是将你的表和索引建立在不同的表空间内，决不要将不属于oracle内部系统的对象存放到system表空间内。同时确保数据表空间和索引表空间置于不同的硬盘控制卡控制的硬盘上。

---与SQL优化(包括内存空间)有关一些INIT参数
OPTIMIZER_MODE
SORT_AREA
SHARED_POOL_SZIE

---一些不使用索引的情况
1，NOT IN
2，NOT BETWEEN
3，LIKE（第一个字符非%号除外，如name like '李%'）
4，<>
5，IS NULL/IS NOT NULL
6，查询的字段加函数
7，在8I中，多字段的组合索引（A，B，C），select * from ** wher B='33',则索引也不会用。（按前缀式规则使用索引除外，如A='33' and B='33' || A='33' || A='33' and C='33'）
注：9I除外

---查询语句比较优化的写法：
1，加HINT，改变其执行路径
2，可能使用exsit的地方就尽量不用IN,可以使用not exiist的地方，尽量不要用not in
3，两个表进行JION时，大表放在前面，JION字段建索引
4，尽量用其它写法，取代NOT IN，如a,b表同结构，数据量很大，则代替select * from a where a.c not in (select c from b )
的语句有
a)select a.* from a, b where a.c = b.c + and b.c is null（据说速度比原写法提高30倍）
b)select * from a minus select a.* from a,b where a.c=b.c (速度其次)
c)select * from a where not exist(select a.* from a,b where a.c=b.c) (也不错)

---根据Tom的经验，count(*) 和count某列在Oracle8以上没有区别
根据我以前的经验，在有主键，数据量为100000左右的表中使用COUNT（*）比COUNT（主键）要那么快一点点，大概5秒左右。

----like不是都不能使用索引
有一种情况可以使用，在NAME上建索引，使用name like '李%'可以用到索引，即LIKE的值中%不要放在最前面

----字段加函数可以考虑使用Function Based Index来代替普通索引
这个我听说过，但不知道哪里有中文文档，我想速成。还有，如果这个函数是我自定义的，且比较复杂，包括了从多个表中的计算。Function Based Index是否还有效。

--请指出动态SQL的dbms_sql和execute immediate的区别的来源？或者以自己实例证明。没有见过这样的说法。谢谢
我会在后面贴上文档，其实dbms_sql与execute immediate各有优点，关键看你的SQL语句的目标。

---两表进行Join，应该是小的表放在前面效率更加好一点？
这也是我的经验，一大，一小两个表，在SELECT中好像表的顺序没有太多影响，但JION条件中，大表放在前面有优势。
如：select big.*,smal.* from big smal where big.col=smal.col
据我的试验PLAN，在JION的两个表中，ORACLE只能用到一边表的索引（一般是前面的表，如BIG），而SMAL则进行全表扫描，所以将大表放在前有好处。

——附，两种动态SQL的区别：

Advantages of Native Dynamic SQL
Native dynamic SQL provides the following advantages over the DBMS_SQL package:

Ease of Use
Native dynamic SQL is much simpler to use than the DBMS_SQL package. Because native dynamic SQL is integrated with SQL, you can use it in the same way that you currently use static SQL within PL/SQL code. In addition, native dynamic SQL code is typically more compact and readable than equivalent code that uses the DBMS_SQL package.

The DBMS_SQL package is not as easy to use as native dynamic SQL. There are many procedures and functions that must be used in a strict sequence. Typically, performing simple operations requires a large amount of code when you use the DBMS_SQL package. You can avoid this complexity by using native dynamic SQL instead.

Performance Improvements
The performance of native dynamic SQL in PL/SQL is comparable to the performance of static SQL because the PL/SQL interpreter has built-in support for native dynamic SQL. Therefore, the performance of programs that use native dynamic SQL is much better than that of programs that use the DBMS_SQL package. Typically, native dynamic SQL statements perform 1.5 to 3 times better than equivalent statements that use the DBMS_SQL package. Of course, your performance gains may vary depending on your application.

The DBMS_SQL package is based on a procedural API and, as a result, incurs high procedure call and data copy overhead. For example, every time you bind a variable, the DBMS_SQL package copies the PL/SQL bind variable into its space for later use during execution. Similarly, every time you execute a fetch, first the data is copied into the space managed by the DBMS_SQL package and then the fetched data is copied, one column at a time, into the appropriate PL/SQL variables, resulting in substantial overhead resulting from data copying. In contrast, native dynamic SQL bundles the statement preparation, binding, and execution steps into a single operation, which minimizes the data copying and procedure call overhead and improves performance.

此外EXECUTE IMMEDIATE 的使用，也有优化，如下：
Performance Tip
When using either native dynamic SQL or the DBMS_SQL package, you can improve performance by using bind variables, because using bind variables allows Oracle to share a single cursor for multiple SQL statements.

For example, the following native dynamic SQL code does not use bind variables:

CREATE OR REPLACE PROCEDURE del_dept (
my_deptno dept.deptno%TYPE) IS
BEGIN
EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = ' || to_char (my_deptno);
END;
/

For each distinct my_deptno variable, a new cursor is created, which can cause resource contention and poor performance. Instead, bind my_deptno as a bind variable, as in the following example:

CREATE OR REPLACE PROCEDURE del_dept (
my_deptno dept.deptno%TYPE) IS
BEGIN
EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :1' USING my_deptno;
END;
/

5，动态SQL中，尽量多用execute immediate,而少用DBMS_SQL,前者综合效率优于后者

6，对于很复杂的查询语句，可以建立临时表进行缓冲（关于临时表的解释与使用，还希望同行告诉我在哪里有。。。）

7，COUNT（*）与COUNT（某列）一样进行全表扫描Fast Full Index Scan，速度差不多

8，经常同时存取多列，或经常使用GROUP BY的SQL语句，最好对表的GROUP字段建立组合索引。组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。

9，对于字段取值单一（如性别字段只有男与女），而经常在性别上做查询，则建立位图索引。
注：BITMAP INDEX通常用于DSS,如果你的系统是OLTP,DML操作将LOCK整个BITMAP SEGMENT,因此只在DSS下 考虑BITMAP INDEX

。。。。。。。。。。。。。。。
还有很多，希望各位补充与修正。

---ORACLE爱好者最常用的东西就是SQL了，而它的优化又是一门比较综合复杂的东东，希望ITPUT下一期的杂志能出版一个关于SQL优化的专题，我想一定是很多人都希望的事情。

SQL语句：
是对数据库(数据)进行操作的惟一途径；
消耗了70%~90%的数据库资源；独立于程序设计逻辑，相对于对程序源代码的优化，对SQL语句的优化在时间成本和风险上的代价都很低；
可以有不同的写法；易学，难精通。

SQL优化：
固定的SQL书写习惯，相同的查询尽量保持相同，存储过程的效率较高。
应该编写与其格式一致的语句，包括字母的大小写、标点符号、换行的位置等都要一致

ORACLE优化器：
在任何可能的时候都会对表达式进行评估，并且把特定的语法结构转换成等价的结构，这么做的原因是
要么结果表达式能够比源表达式具有更快的速度
要么源表达式只是结果表达式的一个等价语义结构
不同的SQL结构有时具有同样的操作（例如：= ANY (subquery) and IN (subquery)），ORACLE会把他们映射到一个单一的语义结构。

1 常量优化：
常量的计算是在语句被优化时一次性完成，而不是在每次执行时。下面是检索月薪大于2000的的表达式：
sal > 24000/12
sal > 2000
sal*12 > 24000
如果SQL语句包括第一种情况，优化器会简单地把它转变成第二种。
优化器不会简化跨越比较符的表达式，例如第三条语句，鉴于此，应尽量写用常量跟字段比较检索的表达式，而不要将字段置于表达式当中。否则没有办法优化，比如如果sal上有索引，第一和第二就可以使用，第三就难以使用。

2 操作符优化：
优化器把使用LIKE操作符和一个没有通配符的表达式组成的检索表达式转换为一个“=”操作符表达式。
例如：优化器会把表达式ename LIKE 'SMITH'转换为ename = 'SMITH'
优化器只能转换涉及到可变长数据类型的表达式，前一个例子中，如果ENAME字段的类型是CHAR(10)， 那么优化器将不做任何转换。
一般来讲LIKE比较难以优化。

其中：
~~ IN 操作符优化：
优化器把使用IN比较符的检索表达式替换为等价的使用“=”和“OR”操作符的检索表达式。
例如，优化器会把表达式ename IN ('SMITH','KING','JONES')替换为
ename = 'SMITH' OR ename = 'KING' OR ename = 'JONES‘

~~ ANY和SOME 操作符优化:
优化器将跟随值列表的ANY和SOME检索条件用等价的同等操作符和“OR”组成的表达式替换。
例如，优化器将如下所示的第一条语句用第二条语句替换：
sal > ANY (:first_sal, :second_sal)
sal > :first_sal OR

sal > :second_sal
优化器将跟随子查询的ANY和SOME检索条件转换成由“EXISTS”和一个相应的子查询组成的检索表达式。
例如，优化器将如下所示的第一条语句用第二条语句替换：
x > ANY (SELECT sal FROM emp WHERE job = 'ANALYST')
EXISTS (SELECT sal FROM emp WHERE job = 'ANALYST' AND x > sal)

~~ ALL操作符优化:
优化器将跟随值列表的ALL操作符用等价的“=”和“AND”组成的表达式替换。例如：
sal > ALL (:first_sal, :second_sal)表达式会被替换为：
sal > :first_sal AND sal > :second_sal
对于跟随子查询的ALL表达式，优化器用ANY和另外一个合适的比较符组成的表达式替换。例如
x > ALL (SELECT sal FROM emp WHERE deptno = 10) 替换为：
NOT (x <= ANY (SELECT sal FROM emp WHERE deptno = 10))
接下来优化器会把第二个表达式适用ANY表达式的转换规则转换为下面的表达式：
NOT EXISTS (SELECT sal FROM emp WHERE deptno = 10 AND x <= sal)

~~ BETWEEN 操作符优化:
优化器总是用“>=”和“<=”比较符来等价的代替BETWEEN操作符。
例如：优化器会把表达式sal BETWEEN 2000 AND 3000用sal >= 2000 AND sal <= 3000来代替。

~~ NOT 操作符优化:
优化器总是试图简化检索条件以消除“NOT”逻辑操作符的影响，这将涉及到“NOT”操作符的消除以及代以相应的比较运算符。
例如，优化器将下面的第一条语句用第二条语句代替：
NOT deptno = (SELECT deptno FROM emp WHERE ename = 'TAYLOR')
deptno <> (SELECT deptno FROM emp WHERE ename = 'TAYLOR')
通常情况下一个含有NOT操作符的语句有很多不同的写法，优化器的转换原则是使“NOT”操作符后边的子句尽可能的简单，即使可能会使结果表达式包含了更多的“NOT”操作符。
例如，优化器将如下所示的第一条语句用第二条语句代替：
NOT (sal < 1000 OR comm IS NULL)
NOT sal < 1000 AND comm IS NOT NULL sal >= 1000 AND comm IS NOT NULL

如何编写高效的SQL:
当然要考虑sql常量的优化和操作符的优化啦，另外，还需要：

1 合理的索引设计：
例：表record有620000行，试看在不同的索引下，下面几个SQL的运行情况：
语句A
SELECT count(*) FROM record
WHERE date >'19991201' and date < '19991214‘ and amount >2000

语句B
SELECT count(*) FROM record
WHERE date >'19990901' and place IN ('BJ','SH')

语句C
SELECT date,sum(amount) FROM record
group by date
1 在date上建有一个非聚集索引
A：(25秒)
B：(27秒)
C：(55秒)
分析：
date上有大量的重复值，在非聚集索引下，数据在物理上随机存放在数据页上，在范围查找时，必须执行一次表扫描才能找到这一范围内的全部行。
2 在date上的一个聚集索引
A：（14秒）
B：（14秒）
C：（28秒）
分析：
在聚集索引下，数据在物理上按顺序在数据页上，重复值也排列在一起，因而在范围查找时，可以先找到这个范围的起末点，且只在这个范围内扫描数据页，避免了大范围扫描，提高了查询速度。
3 在place，date，amount上的组合索引
A：（26秒）
C：（27秒）
B：（< 1秒）
分析：
这是一个不很合理的组合索引，因为它的前导列是place，第一和第二条

SQL没有引用place，因此也没有利用上索引；第三个SQL使用了place，且引用的所有列都包含在组合索引中，形成了索引覆盖，所以它的速度是非常快的。
4 在date，place，amount上的组合索引
A： (< 1秒)
B：（< 1秒）
C：（11秒）
分析：
这是一个合理的组合索引。它将date作为前导列，使每个SQL都可以利用索引，并且在第一和第三个SQL中形成了索引覆盖，因而性能达到了最优。

总结1
缺省情况下建立的索引是非聚集索引，但有时它并不是最佳的；合理的索引设计要建立在对各种查询的分析和预测上。一般来说：
有大量重复值、且经常有范围查询（between, >,< ，>=,< =）和order by、group by发生的列，考虑建立聚集索引；
经 常同时存取多列，且每列都含有重复值可考虑建立组合索引；在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引。比如在雇员 表的“性别”列上只有“男”与“女”两个不同值，因此就无必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度。
组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。

2 避免使用不兼容的数据类型：
例如float和INt、char和varchar、bINary和varbINary是不兼容的。数据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。例如:
SELECT name FROM employee WHERE salary ＞ 60000
在这条语句中,如salary字段是money型的,则优化器很难对其进行优化,因为60000是个整型数。我们应当在编程时将整型转化成为钱币型,而不要等到运行时转化。

3 IS NULL 与IS NOT NULL：
不 能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排 除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。任何在WHERE子句中使用is null或is not null的语句优化器是不允 许使用索引的。

4 IN和EXISTS：
EXISTS要远比IN的效率高。里面关系到full table scan和range scan。几乎将所有的IN操作符子查询改写为使用EXISTS的子查询。
例子：
语句1
SELECT dname, deptno FROM dept
WHERE deptno NOT IN
(SELECT deptno FROM emp);
语句2
SELECT dname, deptno FROM dept
WHERE NOT EXISTS
(SELECT deptno FROM emp
WHERE dept.deptno = emp.deptno);
明显的，2要比1的执行性能好很多
因为1中对emp进行了full table scan,这是很浪费时间的操作。而且1中没有用到emp的INdex，
因为没有WHERE子句。而2中的语句对emp进行的是range scan。

5 IN、OR子句常会使用工作表，使索引失效：
如果不产生大量重复值，可以考虑把子句拆开。拆开的子句中应该包含索引。

6 避免或简化排序：
应当简化或避免对大型表进行重复的排序。当能够利用索引自动以适当的次序产生输出时，优化器就避免了排序的步骤。以下是一些影响因素：
索引中不包括一个或几个待排序的列；
group by或order by子句中列的次序与索引的次序不一样；
排序的列来自不同的表。
为了避免不必要的排序，就要正确地增建索引，合理地合并数据库表（尽管有时可能影响表的规范化，但相对于效率的提高是值得的）。如果排序不可避免，那么应当试图简化它，如缩小排序的列的范围等。

7 消除对大型表行

数据的顺序存取：
在 嵌套查询中，对表的顺序存取对查询效率可能产生致命的影响。比如采用顺序存取策略，一个嵌套3层的查询，如果每层都查询1000行，那么这个查询就要查询 10亿行数据。避免这种情况的主要方法就是对连接的列进行索引。例如，两个表：学生表（学号、姓名、年龄??）和选课表（学号、课程号、成绩）。如果两个 表要做连接，就要在“学号”这个连接字段上建立索引。
还可以使用并集来避免顺序存取。尽管在所有的检查列上都有索引，但某些形式的WHERE子句强迫优化器使用顺序存取。下面的查询将强迫对orders表执行顺序操作：
SELECT ＊ FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008
虽然在customer_num和order_num上建有索引，但是在上面的语句中优化器还是使用顺序存取路径扫描整个表。因为这个语句要检索的是分离的行的集合，所以应该改为如下语句：
SELECT ＊ FROM orders WHERE customer_num=104 AND order_num>1001
UNION
SELECT ＊ FROM orders WHERE order_num=1008
这样就能利用索引路径处理查询。

8 避免相关子查询：
一个列的标签同时在主查询和WHERE子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。

9 避免困难的正规表达式：
MATCHES和LIKE关键字支持通配符匹配，技术上叫正规表达式。但这种匹配特别耗费时间。例如：SELECT ＊ FROM customer WHERE zipcode LIKE “98_ _ _”
即使在zipcode字段上建立了索引，在这种情况下也还是采用顺序扫描的方式。如果把语句改为SELECT ＊ FROM customer WHERE zipcode >“98000”，在执行查询时就会利用索引来查询，显然会大大提高速度。
另外，还要避免非开始的子串。例如语句：SELECT ＊ FROM customer WHERE zipcode[2，3] >“80”，在WHERE子句中采用了非开始子串，因而这个语句也不会使用索引。

10 不充份的连接条件：
例：表card有7896行，在card_no上有一个非聚集索引，表account有191122行，在account_no上有一个非聚集索引，试看在不同的表连接条件下，两个SQL的执行情况：
SELECT sum(a.amount) FROM account a,card b WHERE a.card_no = b.card_no
（20秒）
将SQL改为：
SELECT sum(a.amount) FROM account a,card b WHERE a.card_no = b.card_no and a.account_no=b.account_no
（< 1秒）
分析：
在第一个连接条件下，最佳查询方案是将account作外层表，card作内层表，利用card上的索引，其I/O次数可由以下公式估算为：
外层表account上的22541页+（外层表account的191122行*内层表card上对应外层表第一行所要查找的3页）=595907次I/O
在第二个连接条件下，最佳查询方案是将card作外层表，account作内层表，利用account上的索引，其I/O次数可由以下公式估算为：
外层表card上的1944页+（外层表card的7896行*内层表account上对应外层表每一行所要查找的4页）= 33528次I/O
可见，只有充份的连接条件，真正的最佳方案才会被执行。
多表操作在被实际执行前，查询优化器会根据连接条件，列出几组可能的连接方案并从中找出系统开销最小的最佳方案。连接条件要充份考虑带有索引的表、行数多的表；内外表的选择可由公式：外层表中的匹配行数*内层表中每一次查找的次数确定，乘积最小为最佳方案。
不可优化的WHERE子句
例1
下列SQL条件语句中的列都建有恰当的索引，但执行速度却非常慢：
SELECT * FROM record WHERE substrINg(card_no,1,4)='5378'
(13秒)
SELECT * FROM record WHERE amount/30< 1000
（11秒）
SELECT

* FROM record WHERE convert(char(10),date,112)='19991201'
（10秒）
分析：
WHERE子句中对列的任何操作结果都是在SQL运行时逐列计算得到的，因此它不得不进行表搜索，而没有使用该列上面的索引；如果这些结果在查询编译时就能得到，那么就可以被SQL优化器优化，使用索引，避免表搜索，因此将SQL重写成下面这样：
SELECT * FROM record WHERE card_no like '5378%'
（< 1秒）
SELECT * FROM record WHERE amount< 1000*30
（< 1秒）
SELECT * FROM record WHERE date= '1999/12/01'
（< 1秒）

11 存储过程中，采用临时表优化查询：
例
1．从parven表中按vendor_num的次序读数据：
SELECT part_num，vendor_num，price FROM parven ORDER BY vendor_num
INTO temp pv_by_vn
这个语句顺序读parven（50页），写一个临时表（50页），并排序。假定排序的开销为200页，总共是300页。
2．把临时表和vendor表连接，把结果输出到一个临时表，并按part_num排序：
SELECT pv_by_vn，＊ vendor.vendor_num FROM pv_by_vn，vendor
WHERE pv_by_vn.vendor_num=vendor.vendor_num
ORDER BY pv_by_vn.part_num
INTO TMP pvvn_by_pn
DROP TABLE pv_by_vn
这 个查询读取pv_by_vn(50页)，它通过索引存取vendor表1.5万次，但由于按vendor_num次序排列，实际上只是通过索引顺序地读 vendor表（40＋2=42页），输出的表每页约95行，共160页。写并存取这些页引发5＊160=800次的读写，索引共读写892页。
3．把输出和part连接得到最后的结果：
SELECT pvvn_by_pn.＊，part.part_desc FROM pvvn_by_pn，part
WHERE pvvn_by_pn.part_num=part.part_num
DROP TABLE pvvn_by_pn
这样，查询顺序地读pvvn_by_pn(160页)，通过索引读part表1.5万次，由于建有索引，所以实际上进行1772次磁盘读写，优化比例为30∶1。