SQL优化的中心思想有两点：
- 少做甚至不做。
少做事情，甚至对不必要的事情干脆不做，自然就能使SQL提高效率。
通过使用索引来减少数据扫描就是少做事思想的最典型的情形。
- 集中资源做一件事情。
同样的工作量，相比一个人做，大家一起做自然就快了。并行操作就属于这种情形。
索引就是为了快速查找表中记录所创建的一种特殊数据结构。索引块中包含字段的值和指向对应行的地址——rowid。
下面通过示例来介绍一下为什么索引会提升SQL的执行效率：
1、当执行select * from t1 where id = 10000 这样的语句时，如果id字段上创建了索引，并且id 字段中的唯一值较多时，SQL优化器会判断选择走索引。
2、走索引的话，会代入10000 去索引中匹配，经过根节点、枝干节点，找到叶子节点中对应的id字段值为10000的索引项，读取索引项中行记录的地址信息，即rowid。
3、接下来通过rowid直接读取表中的行记录，如果where 后边还有其他条件，将会对行记录进行校验，并返回最终符合条件的行记录。
如果id 字段上没有索引，那么SQL将如何执行呢？
如果id 字段上没有索引，那么SQL只能选择全表扫描，对表中的所有数据进行过滤，并返回最终符合条件的行记录。 随着表中数据量的增长，全表扫描所消耗的资源将会越来越多，SQL执行时间会越来越长。
由索引结构图中，我们可以得出索引的三大特征，并借助这三大特征对SQL进行优化。
- 高度较低（可快速定位）
通过索引，小表查询与大表查询性能差异很小。
- 存储列值（可优化聚合）
count, sum/avg, max/min, 多列索引避免回表
- 有序（可优化排序）
order by, max/min, union, distinct
怎么理解索引呢？ 想想新华字典中的拼音索引或部首索引就能理解了。
部首索引中会主要记录了两类信息，即部首和部首出现的页数。同样的，数据库中索引也会记录被索引字段的值和该值在表中出现的位置。
当我们查找一个不认识的字的时候，我们使用部首索引就可以快速查找到我们要找的文字，而不用将整个字典从头翻到尾一页一页的找。
新华字典中文字部分可以理解成表，为了快速查找到要找的文字，创建了部首索引。 我们要快速的从表中找到想要的记录，也同样的需要创建索引。
因为在索引中，数据是有序的，使用索引可以快速的定位到我们要查找的内容。然后通过索引中的rowid（可以理解成数据存储的物理位置）， 也就可以直接去表中读取数据了。
创建索引时，一般会在where 条件后的字段上创建索引， 这样就可以通过索引快速查找到相应的记录了。
但是，假如通过查询条件查找到的记录较多，那么，索引效率就不会高。 对于这种情况，就不应该创建索引了。
与索引查找相对应的是全表扫描，下面就将全表扫描和索引查找的概念简单介绍一下：
全表扫描：全表扫描是数据库服务器用来搜寻表的每一条记录(表中的行）的过程，直到所有符合给定条件的记录返回为止。因为只有把全表所有记录从头到尾找一个遍，才能确定满足查询条件的记录全被找到。
全表扫描的一般使用场景：
1、表上没有索引。
2、由于满足查询条件的记录较多，导致使用索引效率也不会高, 这时也会选择全表扫描。
3、对表执行全记录操作，例如没有条件限制的查询表中所有记录，没有条件限制的排序或聚合(group by, sum,count) 操作等。
以上场景中，2 和 3 是无法避免的， 也是不应该避免的。 因为在这种情况下，全表扫描相比索引查询性能会好。
索引扫描：在只需要查询表中少量记录的时候，使用索引扫描效率会高。 因为索引结构的特殊性，通过索引可以快速定位到要查找的记录，而不用将整个表从头找到尾。
索引的真正意义：索引是优化器在制定执行计划时，为了寻找最优化的路径而使用的战略要素。假如表上没有索引，那优化器就只能选择全表扫描的执行计划。
索引虽然可以帮着我们快速查找记录，但是，索引也是有成本的。
当表中插入、删除记录和更新索引字段的记录时，数据库就会自动维护到索引中，这部分开销是不容忽视的。 随着表上索引个数的增多，索引维护开销对性能的影响会越来越明显。 因此，索引不能盲目的创建，只有切实发挥有益作用的索引才值得创建。
正因为如此，我们才强调，创建索引要从全局综合性考虑绝大部分SQL的数据访问需求，创建战略性索引，用尽量少的索引来满足绝大部分SQL的性能需求。
单列索引创建原则：
- 查询条件中的经常使用列
- 列的离散度要高
- 通过索引只查询很少一部分数据，小于5%（这个只是一个大概值）。
- 查询条件不能是 <>。
复合索引创建原则：（重要程度依据序号所示）
1、是否经常被使用？
2、是否经常为列使用“=”比较查询条件？
3、哪个列具有更好的离散度？
4、经常按照何种顺序进行排序？
5、何种列将作为附加性列被添加？
以上是创建索引的原则，也可以说是创建索引的依据。开发人员应当多多在工作中练习体会。
- 写好SQL，不犯低级错误。
SQL书写中常见的低级错误有：
- 对列进行运算（这里的意思是能避免就避免）
- 对列使用函数（这里的意思是能避免就避免）
- 数据类型不一致导致列发生隐式转化
- 查询列表中直接使用 * 查询所有字段，而间接包含了不需要的字段
- 进行不必要的排序
- union 可用 union all 代替
- 使用不必要的distinct
如果我们能避免以上低级错误，就算是写好了SQL。
注意，以上并没有提到多表关联查询时， 表的顺序， 以及查询条件where后的条件的前后顺序。 在oracle现有主流版本中，表顺序、where条件顺序对SQL执行没有任何影响，oracle会自动分析判断使用最高效的执行路径。
除了一些低级错误之外，下面对SQL 书写方面普遍存在的讹传进行一下辟谣：
- 表的连接顺序、表的条件顺序
这种说法在oracle9i之后就过时了，现在已经很难找到oracle8i的数据库了，现在已经不在区分书写顺序了。
- Count(*) 与 count(列) 的性能比较
首先，要注意以上两种写法本身就不等价。 count(*) 是查询表中的记录数，空值也会被统计到；而count(列) 是统计该列值的个数，空值是不被统计的。
其次，性能比较的话，也会因为是否会使用到索引而不同。 如果表上有主键，count(*)、count(1), count(主键列) 的性能是一样的，其执行计划都是选择主键索引进行快速索引全扫描。Count(其他列) 的话，如果对应列上没有索引，列序越靠后，则用时越长。如果有索引，则将不会有什么差异。
- In 与 exists 性能之争
在现有oracle主流版本中，已经没有什么区别了。
- Not in 与 not exists 之争
以上两者性能上并没有明显差异。 但是，鉴于not in ( ) 中存在空值时查询结果为空，推荐使用not exists。
给大家辟谣之后，以后写SQL就不用再顾忌那么多了，是不是轻松许多？ 那是肯定的！
我们创建索引时，不但要着眼于某一条SQL，更要着眼于访问表的绝大部分SQL，根据SQL的占比，执行频次来进行综合的分析考量。
索引是有成本的，索引不是越多越好。
正确的创建索引的理念力，是力求用最少量的索引，满足绝大部分SQL的性能要求。
大家试想一下，在表中数据大幅增长的情况下，如何保证应用性能仍然可控？ 即保证SQL性能不会明显下降。
如果表上没有索引，也就是对数据的访问是全表扫描的方式，那么，随着表中数据量的增长，性能会持续下降，应用的执行时间会增长，用户体验会越来越不好。
可以预见的是，这样的应用程序的生命周期是非常短暂的。
如果SQL使用到索引，那情形就不一样了。 因为对于索引访问方式，即使表中数据增长十倍的情况下，SQL性能也不会有明显下降。这就是索引的优势！！
开发人员将SQL写好后，还需要负责创建适当的索引吗？ 当然！
因为就算你将SQL写好了，如果缺少必要的索引，oracle优化器也不会创建出高效的执行计划。你让oracle将事情做好，最起码的优化手段得提供给他吧？
如何理解访问方式呢？有以下几点：
- 通过哪些列对数据进行访问？
- 各种访问方式的频率如何？
- 访问列的离散程度如何？
理解之后做什么呢？
- 在应用开发阶段，就应该创建好相关索引。
- 综合考量对表的访问方式，创建战略性索引。
由于开发人员对表中数据的多少、数据的分布、数据的访问方式是最了解的，因此，我们可以说，开发人员对SQL进行性能优化，比其他人员具有不可比拟的先天优势！
- 可发现SQL中存在的问题，能进行简单调优。
性能优化一个渐进的过程，这种能力，需要在掌握各种优化技能的基础上，多多练习体会。对开发人员来说，有优化意识，能发现SQL的性能问题，进行简单调优就相当可以了。
在我们看来，意识和技能正是优化的两大法宝。
意识就是善于思考，善于发现，多问几个能不能。例如怎样才能少做事？ 哪些操作是不必要的？ 应用性能还能不能快一点？ 服务器能不能少多一点？ 用户的真实需求是什么？
很多时候，优化更多是意识方面的，无需任何优化技能就能实现的。
技能指掌握性能优化的基本知识，这些是我们优化时的手段和工具，包括索引、表连接、分区表和数据库原理等。只有掌握了这些技能，在遇到问题时，我们才能使用这些手段去解决问题。技能是性能优化时不可或缺的。
这三类集合操作都会去除重复值，会进行排序操作。 对于union，优先考虑是否可用union all代替。
除了性能考虑之外，对于我们开发人员来说，首先要对 union/union all/intersect/minus 集合运算结果有个清楚的认识。
以下是对 union/intersect/minus 集合操作的演示：
SQL> select * from t1 order by id;
ID
----------
1
1
2
2
3
3
4
4
5
5
6
7
8
9
14 rows selected
SQL> select * from t2 order by id;
ID
----------
1
2
3
4
上面，是查询中涉及到的两张表t1 和 t2，下面将对两表进行集合运算。
SQL> select * from t1
2 union
3 select * from t2;
ID
----------
1
2
3
4
5
6
7
8
9
9 rows selected
SQL> select * from t1
2 minus
3 select * from t2;
ID
----------
5
6
7
8
9
SQL> select * from t1
2 intersect
3 select * from t2;
ID
----------
1
2
3
4
从执行结果中，我们注意到union，minus， intersect 集合运算都进行了去除重复值的运算。
SQL> select * from t1
2 union all
3 select * from t2;
ID
----------
1
2
3
4
5
6
7
8
9
1
2
3
4
5
1
2
3
4
18 rows selected
SQL>
以上执行结果， 是否与你预期的结果一样呢？
尽可能将where条件放到SQL最里层，以便在SQL执行之初，就将不符合的数据过滤掉。
SELECT COUNT(*)
FROM (SELECT AGENTCODE,
IDNO,
GREATEST(OFFWORKDATE, NVL(REWORKDATE, DATE '2006-01-01')) AS INDATE,
DECODE(AGENTSTATE,
'0',
LEAD(OFFWORKDATE, 1, ADD_MONTHS(SYSDATE, 12))
OVER(PARTITION BY AGENTCODE ORDER BY DEPARTTIMES),
LEAD(OFFWORKDATE, 1, DATE '2006-01-01')
OVER(PARTITION BY AGENTCODE ORDER BY DEPARTTIMES)) AS OUTDATE
FROM (SELECT A.AGENTCODE,
IDNO,
A.DEPARTTIMES,
A.OFFWORKDATE,
A.REWORKDATE,
DECODE(B.AGENTSTATE, '01', '0', '02', '0', '1') AS AGENTSTATE
FROM LADIMISSION A, LAAGENT B
WHERE A.AGENTCODE = B.AGENTCODE
AND OFFWORKDATE IS NOT NULL
AND B.BRANCHTYPE = '1'
UNION ALL
SELECT AGENTCODE,
IDNO,
0,
JOINDATE,
NULL,
DECODE(AGENTSTATE, '01', '0', '02', '0', '1') AS AGENTSTATE
FROM LAAGENT
WHERE BRANCHTYPE = '1'))
WHERE IDNO = :B3
AND IDNO <> :B2
AND :B1 BETWEEN INDATE AND OUTDATE - 1
分析SQL语句可以发现， WHERE IDNO = :B3 这个条件部分可以直接从SQL的外层放到最里层去。
排序操作将严重影响语句执行效率。排序是资源开销最大的一类操作，所以要坚决去掉没有必要的排序开销，或者借用索引来避免排序。
增加排序，意味着需要做怎样的操作？
首先，对全表数据进行扫描。
其次，对相关字段应用排序算法，计算出排序的结果。 这个过程中，CPU、内存、磁盘三种资源开销都会不小。
涉及排序的操作：
A、创建索引
B、涉及到索引维护的并行插入
C、order by 或者group by (尽可能对索引字段排序)
D、Distinct
E、union/intersect/minus
F、sort-merge join
G、analyze命令(尽可能使用estimate而不是compute)
示例SQL：
select *
from (select a.missionprop1 理赔编号,
getCaseState(a.missionprop1) 案件状态,
a.missionprop3 出险人客户号,
a.missionprop4 出险人姓名,
(select codename
from ldcode
where codetype = 'sex'
and code = a.missionprop5),
to_date(a.missionprop6, 'yyyy-mm-dd') 出险日期,
(select rptdate from llreport where rptno = a.missionprop1),
(select shortname from ldcom where comcode = b.mngcom) 报案机构,
(select username from lduser where usercode = b.operator) 报案记录人员,
a.missionid,
a.submissionid,
a.activityid,
a.missionprop2,
(select case count(1)
when 0 then
''
else
'回退案件'
end
from LLCaseBack
where rgtno = a.missionprop1) 标志,
(case (select isEnpAdded
from LLRegister
where rgtno = (select b.rgtobjno
from llregister b, llcase c
where caseno = a.missionprop1
最低0.47元/天 解锁文章
745
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
