索引 sql优化

索引简介

1)、索引相当于目录
2)、索引是通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率。
3)、索引的创建要适度，多了会影响增删改的效率，少了会影响查询的效率，索引最好创建在取值分散的列上，避免对同一张表创建过多的索引
4)、索引的使用对用户来说是透明的，由系统来决定什么时候使用索引。
5)、Oracle支持多种类型的索引，可以按列的多少、索引值是否唯一和索引数据的组织形式对索引进行分类，以满足各种表和查询条件的要求。

总结：创建索引就是对某些特定列中的数据排序，生成独立的索引表。在某列上创建索引后，如果该列出现在查询条件中，Oracle会自动的引用该索引，

先从索引表中查询出符合条件记录的ROWID，由于ROWID是记录的物理地址，因此可以根据ROWID快速的定位到具体的记录，表中的数据非常多时，引用索引带来的查询效率非常可观。

CREATE INDEX命令语法:

CREATE [unique] INDEX [user.]indexName ON [user.]tableName

(column [ASC | DESC] [,column [ASC |DESC] ] ... )
[CLUSTER [scheam.]cluster] [INITRANS n] [MAXTRANS n] [PCTFREE n]
[STORAGE storage] [TABLESPACE tablespace] [NO SORT] Advanced

其中：
   column 基表中的列名，一个索引最多有16列，long列、long raw列不能建索引列
   DESC、ASC 缺省为ASC即升序排序
   CLUSTER 指定一个聚簇（Hash cluster不能建索引）
   INITRANS、MAXTRANS 指定初始和最大事务入口数
   Tablespace 表空间名
   STORAGE 存储参数，同create table 中的storage.
   PCTFREE 索引数据块空闲空间的百分比(不能指定pctused)
   NOSORT 不（能）排序（存储时就已按升序，所以指出不再排序）

删除索引：DROP INDEX[schema.]indexname

索引对应的表：1、查看索引信息  all_indexs2、查看索引信息及引用的列all_ind_columns

3、查看函数索引信息 all_ind_expressions

索引分类

--Unique唯一索引 值唯一，但允许存在null，主键默认存在唯一索引，但列不能为null
--Non-Unique：非唯一索引，其索引值可以重复，允许为NULL。默认情况下，Oracle创建的

索引是非唯一索引
--Reverse Key：反向关键字索引。通过在创建索引时指定“REVERSE”关键字，可以创建反向关键字索引，被索引的每个数据列中的数据都是反向存储的，但仍然保持原来数据列的次序
--位图索引: (对列值范围少，如性别政治面貌适用，而不是默认的B树索引)
--函数索引: 当需要经常访问一些函数或表达式时，可以将其存储在索引中，当下次访问时，由于该值已经计算出来了，因此，可以大大提高那些在WHERE子句中包含该函数或表达式的查询操作的速度；函数索引既可以使用B树索引，也可以使用位图索引。

CREATE INDEX IDX_JOB_LOWER ON EMP(LOWER(JOB)); 

用法：SELECT * FROM EMP WHERE LOWER(ename)LIKE ‘JOH%’;

管理索引的原则

1)．小表不需要建立索引。
2)．对于大表而言，如果经常查询的记录数目少于表中总记录数目的10%时，可以创建索引。这个比例并不绝对，它与全表扫描速度成反比。
3)．对于大部分列值不重复的列可建立索引。
4)．对于基数大的列，适合建立B树索引，而对于基数小的列适合建立位图索引。
5)．对于列中有许多空值，但经常查询所有的非空值记录的列，应该建立索引。
6)．LONG和LONG RAW列不能创建索引。
7)．经常进行连接查询的列上应该创建索引。经常在某表的一个字段进行Order By 则也经过进行索引
8)．在使用CREATE INDEX语句创建查询时，将最常查询的列放在其他列前面。
9)．维护索引需要开销，特别是对表进行插入和删除操作时，因此要限制表中索引的数量。对于主要用于读的表，则索引多就有好处，但是，一个表如果经常被更改，则索引应少点。
10)．在表中插入数据后创建索引。如果在装载数据之前创建了索引，那么当插入每行时，Oracle都必须更改每个索引。

Sql 优化:

当Oracle数据库拿到SQL语句时，其会根据查询优化器分析该语句，并根据分析结果生成查询执行计划。也就是说，数据库是执行的查询计划，而不是Sql语句。

查询优化器有rule-based-optimizer(基于规则的查询优化器) 和Cost-Based-optimizer(基于成本的查询优化器)。其中基于规则的查询优化器在10g版本中消失。

对于规则查询，其最后查询的是全表扫描。而CBO则会根据统计信息进行最后的选择。

1、先执行From ->Where->Group By->Order By

2、执行From 字句是从右往左进行执行。因此必须选择记录条数最少的表放在右边。

3、对于Where子句其执行顺序是从后向前执行、因此可以过滤最大数量记录的条件必须写在Where子句的末尾，而对于多表之间的连接，则写在之前。

4. SELECT子句中避免使用(*)ORACLE在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间

5、索引失效的情况:

　① Not Null/Null 如果某列建立索引,当进行Select * from emp where depto is not null/is null。 则会是索引失效。

　②索引列上不要使用函数,SELECT Col FROM tbl WHERE substr(name ,1 ,3 ) = 'ABC'

或者SELECT Col FROM tbl WHERE name LIKE '%ABC%'

而  SELECT Col FROM tbl WHEREname LIKE 'ABC%' 会使用索引。

　③索引列上不能进行计算SELECT Col FROM tbl WHERE col / 10 > 10 则会使索引失效，应该改成

SELECT Col FROM tbl WHERE col > 10 * 10

　④索引列上不要使用NOT （ != 、 <> ）如:SELECT Col FROM tbl WHERE col ! = 10

应该改成：SELECT Col FROM tbl WHERE col > 10 OR col < 10 。

6、用UNION替换OR(适用于索引列)

　 union:是将两个查询的结果集进行追加在一起，它不会引起列的变化。由于是追加操作，需要两个结果集的列数应该是相关的，

并且相应列的数据类型也应该相当的。union 返回两个结果集，同时将两个结果集重复的项进行消除。如果不进行消除，用UNOIN ALL.

通常情况下, 用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, 那就需要返回记录最少的索引列写在最前面.

7. 用EXISTS替代IN、用NOT EXISTS替代NOT IN

在许多基于基础表的查询中, 为了满足一个条件, 往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率.

在子查询中, NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下, NOT IN都是最低效的(因为它对子查询中的表执行了一个全表遍历).

为了避免使用NOT IN, 我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.

例子：

高效: SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT 'X' FROM DEPT WHEREDEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’)

低效: SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC= 'MELB')