数据库性能调优技术系列文章(2)--索引优化

一、概述

　　随着数据库在各个领域的使用不断增长，越来越多的应用提出了高性能的要求。数据库性能调优是知识密集型的学科，需要综合考虑各种复杂的因素：数据库缓冲区的大小、索引的创建、语句改写等等。总之，数据库性能调优的目的在于使系统运行得更快。

　　调优需要有广泛的知识，这使得它既简单又复杂。

　　说调优简单，是因为调优者不必纠缠于复杂的公式和规则。许多学术界和业界的研究者都在尝试将调优和查询处理建立在数学基础之上。

　　称调优复杂，是因为如果要完全理解常识所依赖的原理，还需要对应用、数据库管理系统、操作系统以及硬件有广泛而深刻的理解。

　　数据库调优技术可以在不同的数据库系统中使用。如果需要调优数据库系统，最好掌握如下知识：1)查询处理、并发控制以及数据库恢复的知识;2)一些调优的基本原则。

　　这里主要描述索引调优。

　　二、索引调优

　　索引是建立在表上的一种数据组织，它能提高访问表中一条或多条记录的特定查询效率。因此，适当的索引调优是很重要的。

　　对于索引调优存在如下的几个误区：

　　误区1：索引创建得越多越好?

　　实际上：创建的索引可能建立后从来未使用。索引的创建也是需要代价的，对于删除、某些更新、插入操作，对于每个索引都要进行相应的删除、更新、插入操作。从而导致删除、某些更新、插入操作的效率变低。

　　误区2：对于一个单表的查询，可以索引1进行过滤再使用索引2进行过滤?

　　实际上：假设查询语句如下select * from t1 where c1=1 andc2=2，c1列和c2列上分别建有索引ic1、ic2。先使用ic1(或ic2)进行过滤，产生的结果集是临时数据，不再具有索引，所以不可使用ic2(或ic1)进行再次过滤。

　　索引优化的基本原则：

　　1.将索引和数据存放到不同的文件组

　　没有将表数据和索引数据存储到不同的文件组，而不加区别地将它们存储到同一文件组。这样，不但会造成I/O竞争，也为数据库的维护工作带来不变。

　　2.组合索引的使用

　　假设存在组合索引it1c1c2(c1,c2)，查询语句select * from t1where c1=1 and c2=2能够使用该索引。查询语句select * from t1 wherec1=1也能够使用该索引。但是，查询语句select * from t1 wherec2=2不能够使用该索引，因为没有组合索引的引导列，即，要想使用c2列进行查找，必需出现c1等于某值。

　　根据where条件的不同，归纳如下：

　　1) c1=1 and c2=2：使用索引it1c1c2进行等值查找。

　　2) c1=1 andc2>2：使用索引it1c1c2进行范围查找，可以有两种方法。

　　方法1，使用通过索引键(1,2)在B树中命中一条记录，然后向后扫描找出第一条符合条件的记录，从此记录往后的每一条记录都是符合条件的。这种方法的弊端在于：如果c1=1and c2=2对应的记录数很多，会产生很多无效的扫描。

　　方法2，如果c2对应的int型数据，可以使用索引键(1,3)在B树中命中一条记录，从此记录往后的每一条记录都是符合条件的。

　　本文中的例子均采用方法1。

　　3)c1>1 andc2=2：因为索引的第一个列不是等于号的，索引即使后面出现了c2=2，也不能将c2=2应用于索引查找。这里，通过索引键(1,-∞)在B树中命中一条记录，向后扫描找出第一条符合c1>1的记录，此后的每一条记录判断是否符合c2=2，如果符合则输出，否则过滤掉。这里我们称c2=2没有参与到索引运算中去。这种情况在实际应用中经常出现。

　　4)c1>1：通过索引键(1,- ∞)在B树中命中一条记录，以此向后扫描找出第一条符合c1>1的记录，此后的每条记录都是符合条件的。

　　3.唯一索引与非唯一索引的差异

　　假设索引int1c1(c1)是唯一索引，对于查询语句select c1 from t1wherec1=1，数据库使用索引键(1)命中B树中一条记录，命中之后直接返回该记录(因为是唯一索引，所以最多只能有一条c1=1的记录)。

　　假设索引it1c2(c2)是非唯一索引，对于查询语句select c2 from t2wherec2=2，数据库使用索引键(2)命中B树中一条记录，返回该记录，并继续向后扫描，如果该记录是满足c=2，返回该记录，继续扫描，直到遇到第一条不符合条件c2=2的记录。

　　于是，我们可以得知，对于不存在重复值的列，创建唯一索引优于创建非唯一索引。

　　4.非聚集索引的作用

　　每张表只可能一个聚集索引，聚集索引用来组织真实数据。语句“createtable employee (id int cluster primary key,name varchar(20),addrvarchar(20))”。表employee的数据用id来组织。如果要查找id=1000的员工记录，只要用索引键(1000)命中该聚集索引。但是，对于要查找name=’张三’的员工记录就不能使用该索引了，需要进行全表扫描，对于每一条记录判断是否满足name=’张三’，这样会导致查询效率非常低。

　　要使用聚集索引，必需提供id，我们只能提供name，于是需要引入一个辅助结构实现name到id的转换，这就是非聚集索引的作用。该非聚集索引的键是name，值是id。于是语句“select* from employee wherename=’张三’”的执行流程是：通过键(’张三’)命中非聚集索引，得到对应的id值3(假设’张三’对应的id为3)，然后用键(3)命中聚集索引，得到相应的记录。

　　5.是不是使用非聚集索引的查询都需要进行聚集的查询?

　　不是的，虽然在上一点中查询转换为聚集索引的查找，有时候可以只需要使用非聚集索引。

　　创建表并创建相应的索引：create table t1(c1 int,c2 int,c3int);create index it1c2c3 on t1(c2,c3)。查询语句为：select c3 fromt1 where c2=1。

　　因为索引it1c2c3(c2,c3)覆盖查询语句中的列(c2,c3)。所以，该查询语句的执行流程为：通过索引键(1,-∞)命中索引it1c2c3，对于该记录直接返回c3对应的值，继续向后扫描，如果索引记录中c1还是等于1，那么输出c3，以此类推，直到出现第一条c1不等于1的索引记录,结束查询。

　　6.创建索引的规则

　　创建索引首先要考虑的是列的可选择性。比较一下列中唯一键的数量和表中记录的行数，就可以判断该列的可选择性。如果该列的“唯一键的数量/表中记录行数”的比值越接近于1，则该列的可选择行越高。在可选择性高的列上进行查询，返回的数据就较少，比较适合索引查询。相反，比如性别列上只有两个值，可选择行就很小，不适合索引查询。

 

Oracle 索引 详解

一．索引介绍

 1.1 索引的创建语法： 

CREATE UNIUQE | BITMAP INDEX <schema>.<index_name>

      ON <schema>.<table_name>

           (<column_name> | <expression> ASC | DESC,
            <column_name> | <expression> ASC | DESC,...)
     TABLESPACE <tablespace_name>
     STORAGE <storage_settings>
     LOGGING | NOLOGGING
    COMPUTE STATISTICS
     NOCOMPRESS | COMPRESS<nn>
     NOSORT | REVERSE
     PARTITION | GLOBAL PARTITION<partition_setting>

 

相关说明

1） UNIQUE | BITMAP：指定UNIQUE为唯一值索引，BITMAP为位图索引，省略为B-Tree索引。
2）<column_name> | <expression> ASC | DESC：可以对多列进行联合索引，当为expression时即“基于函数的索引”
3）TABLESPACE：指定存放索引的表空间(索引和原表不在一个表空间时效率更高)
4）STORAGE：可进一步设置表空间的存储参数
5）LOGGING | NOLOGGING：是否对索引产生重做日志(对大表尽量使用NOLOGGING来减少占用空间并提高效率)
6）COMPUTE STATISTICS：创建新索引时收集统计信息
7）NOCOMPRESS | COMPRESS<nn>：是否使用“键压缩”(使用键压缩可以删除一个键列中出现的重复值)
8）NOSORT | REVERSE：NOSORT表示与表中相同的顺序创建索引，REVERSE表示相反顺序存储索引值
9）PARTITION | NOPARTITION：可以在分区表和未分区表上对创建的索引进行分区

1.2 索引特点： 

第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。 

第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。 

第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。 

第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。 

第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。 

 

 

1.3 索引不足：

第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。 

第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。 

第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。 

 

1.4 应该建索引列的特点：

1）在经常需要搜索的列上，可以加快搜索的速度； 

2）在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构； 

3）在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度； 

4）在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的； 

5）在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间； 

6）在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。 

1.5 不应该建索引列的特点：

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。 

第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。 

第三，对于那些定义为blob数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。 

第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。 

1.6 限制索引
限制索引是一些没有经验的开发人员经常犯的错误之一。在SQL中有很多陷阱会使一些索引无法使用。下面讨论一些常见的问题：
   1.6.1  使用不等于操作符（<>、!=）      
   下面的查询即使在cust_rating列有一个索引，查询语句仍然执行一次全表扫描。     
   select cust_Id,cust_name from customers where  cust_rating <> 'aa';        
把上面的语句改成如下的查询语句，这样，在采用基于规则的优化器而不是基于代价的优化器（更智能）时，将会使用索引。        
  select cust_Id,cust_name from customers where cust_rating < 'aa' or cust_rating > 'aa';
  特别注意：通过把不等于操作符改成OR条件，就可以使用索引，以避免全表扫描。
   1.6.2 使用IS NULL 或IS NOT NULL
   使用IS NULL 或IS NOT NULL同样会限制索引的使用。因为NULL值并没有被定义。在SQL语句中使用NULL会有很多的麻烦。因此建议开发人员在建表时，把需要索引的列设成 NOT NULL。如果被索引的列在某些行中存在NULL值，就不会使用这个索引（除非索引是一个位图索引，关于位图索引在稍后在详细讨论）。
   1.6.3 使用函数
   如果不使用基于函数的索引，那么在SQL语句的WHERE子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。 下面的查询不会使用索引（只要它不是基于函数的索引）
 select empno,ename,deptno from emp  where  trunc(hiredate)='01-MAY-81';
   把上面的语句改成下面的语句，这样就可以通过索引进行查找。
select empno,ename,deptno from emp where  hiredate<(to_date('01-MAY-81')+0.9999);

  1.6.4 比较不匹配的数据类型       
也是比较难于发现的性能问题之一。 注意下面查询的例子，account_number是一个VARCHAR2类型,在account_number字段上有索引。

下面的语句将执行全表扫描：

 select bank_name,address,city,state,zip from banks where account_number = 990354;
  Oracle可以自动把where子句变成to_number(account_number)=990354，这样就限制了索引的使用,改成下面的查询就可以使用索引：
 select bank_name,address,city,state,zip from banks where account_number ='990354';

特别注意：不匹配的数据类型之间比较会让Oracle自动限制索引的使用,即便对这个查询执行Explain Plan也不能让您明白为什么做了一次“全表扫描”。

1.7 查询索引
查询DBA_INDEXES视图可得到表中所有索引的列表，注意只能通过USER_INDEXES的方法来检索模式(schema)的索引。访问USER_IND_COLUMNS视图可得到一个给定表中被索引的特定列。

1.8 组合索引
当某个索引包含有多个已索引的列时，称这个索引为组合（concatented）索引。在 Oracle9i引入跳跃式扫描的索引访问方法之前，查询只能在有限条件下使用该索引。比如：表emp有一个组合索引键，该索引包含了empno、 ename和deptno。在Oracle9i之前除非在where之句中对第一列（empno）指定一个值，否则就不能使用这个索引键进行一次范围扫描。
   特别注意：在Oracle9i之前，只有在使用到索引的前导索引时才可以使用组合索引！

1.8 组合索引
当某个索引包含有多个已索引的列时，称这个索引为组合（concatented）索引。在 Oracle9i引入跳跃式扫描的索引访问方法之前，查询只能在有限条件下使用该索引。比如：表emp有一个组合索引键，该索引包含了empno、 ename和deptno。在Oracle9i之前除非在where之句中对第一列（empno）指定一个值，否则就不能使用这个索引键进行一次范围扫描。
   特别注意：在Oracle9i之前，只有在使用到索引的前导索引时才可以使用组合索引！
 

1.9 ORACLE ROWID
通过每个行的ROWID，索引Oracle提供了访问单行数据的能力。ROWID其实就是直接指向单独行的线路图。如果想检查重复值或是其他对ROWID本身的引用，可以在任何表中使用和指定rowid列

1.10 选择性
   使用USER_INDEXES视图，该视图中显示了一个distinct_keys列。比较一下唯一键的数量和表中的行数，就可以判断索引的选择性。选择性越高，索引返回的数据就越少。

1.11 群集因子(Clustering Factor)
  Clustering Factor位于USER_INDEXES视图中。该列反映了数据相对于已建索引的列是否显得有序。如果Clustering Factor列的值接近于索引中的树叶块(leaf block)的数目，表中的数据就越有序。如果它的值接近于表中的行数，则表中的数据就不是很有序。

1.12 二元高度(Binary height)
  索引的二元高度对把ROWID返回给用户进程时所要求的I/O量起到关键作用。在对一个索引进行分析后，可以通过查询DBA_INDEXES的B- level列查看它的二元高度。二元高度主要随着表的大小以及被索引的列中值的范围的狭窄程度而变化。索引上如果有大量被删除的行，它的二元高度也会增加。更新索引列也类似于删除操作，因为它增加了已删除键的数目。重建索引可能会降低二元高度。

1.13 快速全局扫描
  从Oracle7.3后就可以使用快速全局扫描(Fast Full Scan)这个选项。这个选项允许Oracle执行一个全局索引扫描操作。快速全局扫描读取B-树索引上所有树叶块。初始化文件中的 DB_FILE_MULTIBLOCK_READ_COUNT参数可以控制同时被读取的块的数目。

1.14 跳跃式扫描
  从Oracle9i开始，索引跳跃式扫描特性可以允许优化器使用组合索引，即便索引的前导列没有出现在WHERE子句中。索引跳跃式扫描比全索引扫描要快的多。

下面的比较他们的区别：
SQL> set timing on

SQL> create index TT_index on TT(teamid,areacode);

索引已创建。

已用时间:  00: 02: 03.93

SQL> select count(areacode) from tt;

COUNT(AREACODE)

---------------

 7230369

已用时间:  00: 00: 08.31

SQL> select /*+ index(tt TT_index )*/ count(areacode) from tt;

COUNT(AREACODE)

---------------

7230369

已用时间:  00: 00: 07.37

 

1.15 索引的类型
B-树索引    位图索引   HASH索引     索引编排表  

反转键索引 基于函数的索引  分区索引   本地和全局索引

http://blog.csdn.net/tianlesoftware/article/details/5347098对索引类型做了具体分析！