通过建立索引提高数据库查询速度的原理

2008-06-24 15:13
索引是提高数据查询最有效的方法，也是最难全面掌握的技术，因为正确的索引可能使效率提高10000倍，而无效的索引可能是浪费了数据库空间，甚至大大降低查询性能。   
    
      
    
    索引的管理成本   
    
    1、       存储索引的磁盘空间   
    
    2、       执行数据修改操作（INSERT、UPDATE、DELETE）产生的索引维护   
    
    3、       在数据处理时回需额外的回退空间。   
    
      
    
    实际数据修改测试：   
    
    一个表有字段A、B、C，同时进行插入10000行记录测试   
    
    在没有建索引时平均完成时间是2.9秒   
    
    在对A字段建索引后平均完成时间是6.7秒   
    
    在对A字段和B字段建索引后平均完成时间是10.3秒   
    
    在对A字段、B字段和C字段都建索引后平均完成时间是11.7秒   
    
    从以上测试结果可以明显看出索引对数据修改产生的影响   
    
      
    
    索引按存储方法分类   
    
    B*树索引   
    
    B*树索引是最常用的索引，其存储结构类似书的索引结构，有分支和叶两种类型的存储数据块，分支块相当于书的大目录，叶块相当于索引到的具体的书页。一般索引及唯一约束索引都使用B*树索引。   
    
    位图索引   
    
    位图索引储存主要用来节省空间，减少DB2对数据块的访问，它采用位图偏移方式来与表的行ID号对应，采用位图索引一般是重复值太多的表字段。位图索引在 实际密集型OLTP（数据事务处理）中用得比较少，因为OLTP会对表进行大量的删除、修改、新建操作，ORACLE每次进行操作都会对要操作的数据块加 锁，所以多人操作很容易产生数据块锁等待甚至死锁现象。在OLAP（数据分析处理）中应用位图有优势，因为OLAP中大部分是对数据库的查询操作，而且一 般采用数据仓库技术，所以大量数据采用位图索引节省空间比较明显。   
    
      
    
    索引按功能分类   
    
    唯一索引   
    
    唯一索引有两个作用，一个是数据约束，一个是数据索引，其中数据约束主要用来保证数据的完整性，唯一索引产生的索引记录中每一条记录都对应一个唯一的ROWID。   
    
      
    
    主关键字索引   
    
    主关键字索引产生的索引同唯一索引，只不过它是在数据库建立主关键字时系统自动建立的。   
    
    一般索引   
    
    一般索引不产生数据约束作用，其功能主要是对字段建立索引表，以提高数据查询速度。   
    
      
    
      
    
    索引按索引对象分类   
    
      
    
    单列索引（表单个字段的索引）   
    
    多列索引（表多个字段的索引）   
    
    函数索引（对字段进行函数运算的索引）   
    
    建立函数索引的方法：   
    
    create     index     收费日期索引     on     GC_DFSS(trunc(sk_rq))   
    
    create     index     完全客户编号索引     on     yhzl(qc_bh||kh_bh)   
    
    在对函数进行了索引后，如果当前会话要引用应设置当前会话的query_rewrite_enabled为TRUE。   
    
    alter     session     set     query_rewrite_enabled=true   
    
    注：如果对用户函数进行索引的话，那用户函数应加上     deterministic参数，意思是函数在输入值固定的情况下返回值也固定。例：   
    
    create     or     replace     function     trunc_add（input_date     date）return     date     deterministic   
    
    as     
    
    begin   
    
            return     trunc(input_date+1);   
    
    end     trunc_add;   
    
      
    
    应用索引的扫描分类   
    
    INDEX     UNIQUE     SCAN（按索引唯一值扫描）   
    
    select     *     from     zl_yhjbqk     where     hbs_bh='5420016000'   
    
    INDEX     RANGE     SCAN（按索引值范围扫描）   
    
    select     *     from     zl_yhjbqk     where     hbs_bh>'5420016000'   
    
    select     *     from     zl_yhjbqk     where     qc_bh>'7001'   
    
    INDEX     FAST     FULL     SCAN（按索引值快速全部扫描）   
    
    select     hbs_bh     from     zl_yhjbqk     order     by     hbs_bh   
    
    select     count(*)     from     zl_yhjbqk   
    
    select     qc_bh     from     zl_yhjbqk     group     by     qc_bh   
    
      
    
    什么情况下应该建立索引   
    
    表的主关键字   
    
    自动建立唯一索引   
    
    如zl_yhjbqk（用户基本情况）中的hbs_bh（户标识编号）   
    
    表的字段唯一约束   
    
    ORACLE利用索引来保证数据的完整性   
    
    如lc_hj（流程环节）中的lc_bh+hj_sx（流程编号+环节顺序）   
    
    直接条件查询的字段   
    
    在SQL中用于条件约束的字段   
    
    如zl_yhjbqk（用户基本情况）中的qc_bh（区册编号）   
    
    select     *     from     zl_yhjbqk     where     qc_bh=’7001’   
    
    查询中与其它表关联的字段   
    
    字段常常建立了外键关系   
    
    如zl_ydcf（用电成份）中的jldb_bh（计量点表编号）   
    
    select     *     from     zl_ydcf     a,zl_yhdb     b     where     a.jldb_bh=b.jldb_bh     and     b.jldb_bh=’540100214511’   
    
      
    
    查询中排序的字段   
    
    排序的字段如果通过索引去访问那将大大提高排序速度   
    
    select     *     from     zl_yhjbqk     order     by     qc_bh（建立qc_bh索引）   
    
    select     *     from     zl_yhjbqk     where     qc_bh='7001'     order     by     cb_sx（建立qc_bh+cb_sx索引，注：只是一个索引，其中包括qc_bh和cb_sx字段）   
    
    查询中统计或分组统计的字段   
    
    select     max(hbs_bh)     from     zl_yhjbqk   
    
    select     qc_bh,count(*)     from     zl_yhjbqk     group     by     qc_bh   
    
      
    
    什么情况下应不建或少建索引   
    
    表记录太少   
    
    如果一个表只有5条记录，采用索引去访问记录的话，那首先需访问索引表，再通过索引表访问数据表，一般索引表与数据表不在同一个数据块，这种情况下 ORACLE至少要往返读取数据块两次。而不用索引的情况下ORACLE会将所有的数据一次读出，处理速度显然会比用索引快。   
    
    如表zl_sybm（使用部门）一般只有几条记录，除了主关键字外对任何一个字段建索引都不会产生性能优化，实际上如果对这个表进行了统计分析后ORACLE也不会用你建的索引，而是自动执行全表访问。如：   
    
    select     *     from     zl_sybm     where     sydw_bh='5401'（对sydw_bh建立索引不会产生性能优化）   
    
      
    
    经常插入、删除、修改的表   
    
    对一些经常处理的业务表应在查询允许的情况下尽量减少索引，如zl_yhbm，gc_dfss，gc_dfys，gc_fpdy等业务表。   
    
      
    
    数据重复且分布平均的表字段   
    
    假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。   
    
      
    
    经常和主字段一块查询但主字段索引值比较多的表字段   
    
    如gc_dfss（电费实收）表经常按收费序号、户标识编号、抄表日期、电费发生年月、操作标志来具体查询某一笔收款的情况，如果将所有的字段都建在一个 索引里那将会增加数据的修改、插入、删除时间，从实际上分析一笔收款如果按收费序号索引就已经将记录减少到只有几条，如果再按后面的几个字段索引查询将对 性能不产生太大的影响。   
    
      
    
    如何只通过索引返回结果   
    
    一个索引一般包括单个或多个字段，如果能不访问表直接应用索引就返回结果那将大大提高数据库查询的性能。对比以下三个SQL，其中对表zl_yhjbqk的hbs_bh和qc_bh字段建立了索引：   
    
    1     select     hbs_bh,qc_bh,xh_bz     from     zl_yhjbqk     where     qc_bh=’7001’   
    
      
    
      
    
    执行路径:   
    
    SELECT     STATEMENT,     GOAL     =     CHOOSE                     11       265     5565   
    
      TABLE     ACCESS     BY     INDEX     ROWID           DLYX           ZL_YHJBQK         11       265     5565   
    
        INDEX     RANGE     SCAN       DLYX           区册索引           1         265     
    
    平均执行时间（0.078秒）   
    
    2     select     hbs_bh,qc_bh     from     zl_yhjbqk     where     qc_bh=’7001’   
    
    执行路径:   
    
    SELECT     STATEMENT,     GOAL     =     CHOOSE                     11       265     3710   
    
      TABLE     ACCESS     BY     INDEX     ROWID           DLYX           ZL_YHJBQK         11       265     3710   
    
        INDEX     RANGE     SCAN       DLYX           区册索引           1         265     
    
    平均执行时间（0.078秒）   
    
    3     select     qc_bh     from     zl_yhjbqk     where     qc_bh=’7001’   
    
    执行路径:   
    
    SELECT     STATEMENT,     GOAL     =     CHOOSE                     1         265     1060   
    
      INDEX     RANGE     SCAN         DLYX           区册索引           1         265     1060   
    
    平均执行时间（0.062秒）   
    
      
    
    从执行结果可以看出第三条SQL的效率最高。执行路径可以看出第1、2条SQL都多执行了TABLE     ACCESS     BY     INDEX     ROWID（通过ROWID访问表）     这个步骤，因为返回的结果列中包括当前使用索引(qc_bh)中未索引的列（hbs_bh,xh_bz），而第3条SQL直接通过QC_BH返回了结果， 这就是通过索引直接返回结果的方法。   
    
      
    
    如何重建索引   
    
    alter     index     表电量结果表主键     rebuild   
    
      
    
    如何快速新建大数据量表的索引   
    
    如果一个表的记录达到100万以上的话，要对其中一个字段建索引可能要花很长的时间，甚至导致服务器数据库死机，因为在建索引的时候ORACLE要将索引 字段所有的内容取出并进行全面排序，数据量大的话可能导致服务器排序内存不足而引用磁盘交换空间进行，这将严重影响服务器数据库的工作。解决方法是增大数 据库启动初始化中的排序内存参数，如果要进行大量的索引修改可以设置10M以上的排序内存（ORACLE缺省大小为64K），在索引建立完成后应将参数修 改回来，因为在实际OLTP数据库应用中一般不会用到这么大的排序内存。