数据库建立索引前后的性能分析

在测试某涉及数据库操作的接口时发现TPS很弱，只有100出头：

    查看哨兵发现一条数据库操作语句很费时间，其执行的SQL是merge into TB_SPRING_USER_BRAND t using (select :1 account_id,  :2 brand_id from dual) t2 on (t.account_id = t2.account_id and t.brand_id = t2.brand_id) when matched then update set status = :3,  update_time = sysdate when not matched then insert (id,  account_id,  brand_id,  status,  create_time,  update_time) values ( to_char(sysdate, 'yyyymmddhh24')||'HE'||seq_health.nextval,  :4,  :5,  :6,  sysdate,  sysdate )，其中有个判断条件on (t.account_id = t2.account_id and t.brand_id = t2.brand_id)，而account_id和brand_id这两个列没有任何索引，导致每次sql执行就只能走全表扫描，会扫描很多内存数据。加了account_id和brand_id 组成的唯一索引后，TPS能提升到1000以上：

    这就是索引的力量了。索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。数据库索引底层是用HashMap实现的，好比是一本书前面的目录，能加快数据库的查询速度。
    例如这样一个查询：select * from table1 where id=44。如果没有索引，必须遍历整个表，直到ID等于44的这一行被找到为止；有了索引之后(必须是在ID这一列上建立的索引)，直接在索引里面找 44（也就是在ID这一列找），就可以得知这一行的位置，也就是在O(1)时间内找到这一行。可见，索引是用来定位的。
　　索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。

    虽然建立索引的目的是加快对表中记录的查找或排序，但是为表设置索引要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。

　　创建索引可以大大提高系统的性能。
　　　　第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
　　　　第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
　　　　第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
　　　　第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
　　　　第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
　　也许会有人要问：增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？因为，增加索引也有许多不利的方面。
　　　　第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
　　　　第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
　　　　第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

    索引是建立在数据库表中的某些列的上面。在创建索引的时候，应该考虑在哪些列上可以创建索引，在哪些列上不能创建索引。一般来说，应该在这些列上创建索引：
　　在经常需要搜索的列上，可以加快搜索的速度；
　　在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
　　在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
　　在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
　　在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
　　同样，对于有些列不应该创建索引。一般来说，不应该创建索引的的这些列具有下列特点：
　　第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
　　第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
　　第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少,不利于使用索引。
　　第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改操作远远多于检索操作时，不应该创建索引。

唯一索引是唯一的，也可以是非唯一的。唯一索引可以确保在定义索引的列中，表的任意两行的值都不相同。非唯一索引没有在列上规定此限制。oracle自动为表的主键列创建唯一索引。可以使用create unique index命令明确的创建唯一索引；

    create   unique   index  item_index  on  tableName(columnname);  

组合索引是在表中的多个列上创建的索引。组合索引中的列的顺序是任意的，不必是表中相邻的列，如过select语句中的where子句引用的组 合索引中的所有列或大多数列，则组合索引可以提高数据的检索速度。创建组合索引时，应该注意定义中使用的列的顺序。通常，最频繁访问的列应放置在列表的最 前面。

     create   index  comp_index  on  tablename(a_column,b_column);  

 

    反向键索引是一种特殊类型的索引，在索引基于有序数的列时非常有用。因此，反向键索引通常建立在一些值连续增长的列上（高基数字段）

    create   index  rev_index  on   table (columnname) reverse;  

    使用noreverse可以将反向键索引重建为标准索引  

    alert index rev_index rebuild noreverse;  

    位图索引适用于低基数列，也就是说不同值的数目比表的行数少的列，如果某个列的值重复了超过一百次，则可以考虑在该列上创建位图索引。位图索引的优点：

   1、对于大批量的查询，可减少相应的时间；

   2、相比其他索引技术，占用空间明显减少；

   3、即使在配置很低的终端硬件上，也能获得显著的性能；

    create bitmap index bit_index on tablename(columnName);    

 像数据库索引这种经典的性能问题，每次代码中有新的SQL语句时，建议先将该语句报备DBA查看是否需要建索引，之后再提测，可以避免不必要的性能测试轮次。

B-Tree索引
场合：非常适合数据重复度低的字段 例如 身份证号码  手机号码  QQ号等字段，常用于主键 唯一约束，一般在在线交易的项目中用到的多些。
原理：一个键值对应一行（rowid）  格式： 【索引头|键值|rowid】
优点：当没有索引的时候，oracle只能全表扫描where qq=40354446 这个条件那么这样是灰常灰常耗时的，当数据量很大的时候简直会让人崩溃，那么有个B-tree索引我们就像翻书目录一样，直接定位rowid立刻就找到了我们想要的数据，实质减少了I/O操作就提高速度，它有一个显著特点查询性能与表中数据量无关，例如 查2万行的数据用了3 consistent get,当查询1200万行的数据时才用了4 consistent gets。
当我们的字段中使用了主键or唯一约束时，不用想直接可以用B-tree索引
缺点：不适合键值重复率较高的字段上使用，例如 第一章 1-500page 第二章 501-1000page

家肯定会疑惑，为什么要用全表扫描而不用B-tree索引呢，这是因为oracle基于成本优化器CBO认为使用全表扫描要比使用B-tree索引性能更好更快，由于我们结果重复率很高，导致有366次一致性读，从cup使用率12%上看也说明了B-tree索引不适合键值重复率较高的列

小结：从以上的测试我们可以了解到，B-tree索引在什么情况下使用跟键值重复率高低有很大关系的，之间没有一个明确的分水岭，只能多测试分析执行计划后来决定。

位图索引   Bitmap index
场合：列的基数很少，可枚举，重复值很多，数据不会被经常更新
原理：一个键值对应很多行（rowid）， 格式：键值  start_rowid   end_rowid  位图
优点：OLAP 例如报表类数据库 重复率高的数据 特定类型的查询例如count、or、and等逻辑操作因为只需要进行位运算即可得到我们需要的结果
缺点：不适合重复率低的字段，还有经常DML操作（insert，update，delete），因为位图索引的锁代价极高，修改一个位图索引段影响整个位图段，例如修改
一个键值，会影响同键值的多行，所以对于OLTP 系统位图索引基本上是不适用的

全文索引 Text index
定义：全文索引就是通过将文字按照某种语言进行词汇拆分，重新将数据组合存储，来达到快速检索的目的
场合：当字段里存储的都是文本时适合用全文索引，常用于搜索文字
优点：全文索引不是按照键值存储的，而是按照分词重组数据，常用于模糊查询Where name like '%leonarding%'效率比全表扫描高很多，适用OLAP系统，
OLTP系统里面用到的并不多。
缺点：全文索引会占用大量空间有时比原表本身占的空间还多，bug较多，维护困难。
实验：全文索引性能优势

小结：从如上实验来看，当我们检索大量文字的时候使用全文索引要比全表扫描快很多了，有弊就有利，由于全文索引会占用大量空间提前预估全文索引大小保留出足够的空间，context类型全文索引不是基于事务的，无法保证索引和数据实时同步，DML完成后，如果在全文索引中查不到键值时，可以通过手工or定时任务来刷新同步，而B-tree、位图都是实时的。
总结：本次实验了B-tree  位图  全文三大索引的性能，同时比较了各自适合场合和用途，还总结了各自的优缺点，由于水平有限有不足之处还请大家指点。

http://www.itpub.net/thread-1700144-1-1.html