索引的技巧和使用

概论 大家都知道，索引调整是数据库性能优化中最重要的手段之一。很多时候，在一个数据量大的表中建立一个合适的索引，性能往往可以提高几十倍到几百倍.下面，说一下我对索引的了解，数据库采用的是DB2。另外，索引知识太多了，下面只是一些有关索引大概描述，另外，附件里有一关于索引介绍的文档。最后，建议不清楚索引结构的朋友google一下索引的结构，对索引树(B+)了解一下 为什么要使用索引 大家想想，假如没有索引，我们要查询一个包含了几百万甚至几千万数据的库表示，结果会怎样呢？大家可能都知道，一定是非常耗时。那你知道为什么耗时吗？主要有两个原因： 一. 存入数据库中的数据被存入到数据页中，而表里的所有数据页就形成了堆，并 且堆里的数据时杂乱无章的，所以在堆中找某个或某批数据时非常耗时的. 二. 即使在第一行找到了要查询的数据，数据库系统还是要从头到尾对指定表要扫描一 次。这不是画蛇添足吗？ 索引的优点和缺点 是的，使用索引也许可以解决上面问题。这样看来，索引确实很神奇啊，但是别忘了，索引是把双刃剑,用的好，可以提高查询性能，用的不好，哼哼！不仅浪费存储资源，而且进行某些SQL操作时，反而更慢。下面，列举下索引的优点和缺点： 索引优点：1.唯一索引保证了数据库中每行记录的唯一性； 2.加快了数据的检索速度 索引缺点：1.占用一定的物理空间； 2.在进行插入、删除和修改操作时，也要维护对应 表上的索引，从而降低了数据维护速度 什么时候使用索引 既然使用索引存在优点，但也存在一定的缺点，那我们在使用索引的时候就必须要明白应该不应该使用索引。要知道应不应该，那就要对表里数据量的大小，以及指定谓词的变化率有比较清楚的了解。在数据量大时，下面是两种索引常使用的场景. 一. 当数据量比较大时，对那些频繁查询的的SQL语句后的谓词使用索引。当然，指 定谓词的变化率一定要大。例如： Java代码 // 数据量大，为了提高查询速度，可针对谓词lastName字段建立以索引 select * from CUSTOMER where lastName = ‘xiaoxin’ //这种形式也行 select * from CUSTOMER where lastName like ‘xiao%’ 二. 存在着某些针对某些谓词进行排序的SQL语句，如果要加快SQL执行速度，我们可 以针对该谓词建立索引（索引时针对键值排序的）。一般要执行排序操作的SQL语 句中可能包含的关键字有：orderBy、distinct、union、group by 等。例： Java代码 //可针对谓词lastName字段建立以索引 select distinct(lastName) from CUSTOMER //order by 可针对谓词lastName字段建立以索引 select * from CUSTOMER order by lastName //group by 可针对谓词lastName字段建立以索引 select firstName,lastName from CUSTOMER group by lastName ..... 索引的种类介绍 1. 唯一索引 通过唯一索引(包括主键)来保证表中没有两个数据行具有相同的键值，从而来维护数 据的完整性.注意，它和主键的关系：主键一定是唯一索引，而唯一索引不一定是主键。因为主键是不允许有null值存在的，而唯一索引的键值是可以为null的 2. 组合索引 前面说了,索引是把双刃剑，索引多了，会占用一定的物理内存。所以，在SQL中有多个谓词时，是否要针对每个谓词都建立一个索引呢？不一定，下面介绍一种可能会更好的解决方法——建立组合索引。 组合索引：针对多个字段建立一个索引，使用时注意如果没使用到索引前导列，则数据库会一次扫描该索引的叶节点，而不会使用根节点页和合中间节点页(B+树知识),也就是说,该索引基本无效。例如： Java代码 //SQL1 Select * from CUSTOMER where firstName=’xiaoxin’ and lastName=’xiaoxin’ and age=’20’ //SQL2 Select age from CUSTOMER where firstName=’xiaoxin’ and lastName=’xiaoxin’ //为了加快SQL1和SQL2的执行速度，可针对fristName、lastName、age建立以组合索引 // 这样，索引union_index的前导列是谓词 firstName create index union_index on CUSTOMER(firstName,lastName,age) //查询时，索引union_index会加快查询效率 Select * from CUSTOMER where firstName=’xiaoxin’ and lastName=’xiaoxin’ and age=’20’ //查询时，索引union_index还是会加快查询效率 Select * from CUSTOMER where firstName=’xiaoxin’ and lastName=’xiaoxin’ //查询时，索引union_index基本无效，基本不会加快查询效率 Select * from CUSTOMER where lastName=’xiaoxin’ and age=’20’ 3.带关键字INCLUDE的索引 组合索引有节省一定的空间好处，但索引维护的开销还是稍微有点大的。在某些情况下，为了减少索引维护的开销，而且又不降低查询效率。我们不妨试试带关键字INCLUDE的索引。例： Java代码 //SQL3 Select age from CUSTOMER where firstName=’xiaoxin’ and lastName=’xiaoxin’ //为了加快SQL3的执行速度，可针对fristName、lastName、age建立以组合索引 create index union_index on CUSTOMER(firstName,lastName,age) //为了减少索引维护开销，我们可以以如下方式建立索引 create index union_index on CUSTOMER(firstName,lastName) include(age) 使用INCLUDE后，谓词age列的数据将存在索引树的叶子节点，但并不存在索引的关键字中。这样，执行SQL3时，根本不需要检索数据页(存储数据库数据的页),直接从叶节点可获得。而且由于age不作为索引的键值处理，则避免了一些针对age排序的操作，从而减少了索引维护的开销。 4. 双向索引 听其名，知其意。双向索引，顾名思义就是指可以对索引树结构向左或向右扫描的索引。这种索引用在哪些场合呢？看下面例子： Java代码 //SQL4 Select max(firstName) from CUSTOMER //index1 Create index index1 on CUSTOMER(firstName) //index2 Create index index2 on CUSTOMER(firstName desc) //index3 Create index index3 on CUSTOMER(firstName desc) ALLOW REVERSE SCANS 大家可能会认为既然是查询firstName的最大值，而我又在firstName上建立一个索引(假如是index1，没有建index2和index3),优化器应该知道从索引中直接去寻找最大值。实际情况并非如此，因为你创建索引时并没有指定排序属性，则DB2会扫描整个索引树的叶子节点，取得所有值后，然后取出最大值。 如果建立了索引index2,则DB2不需要扫描整个索引树叶子节点，因为第一个节点便是最大的。 当然，其实是有索引Index3则可以双向扫描，但针对SQL4没多大意义，反而在数据更新时维护成本还会高一些。