文档章节

数据库中的索引

凌枫yong
 凌枫yong
发布于 2016/07/17 13:41
字数 3679
阅读 2
收藏 1

数据库中的索引技术

 

索引的设计目标:为了提升数据库的性能.

索引建立的规则

在经常进行连接,但是没有指定为外键的列上建立索引,而不经常连接的字段则由优化器自动生成索引。 
在频繁进行排序或分组(即进行group by或order by操作)的列上建立索引。 
在条件表达式中经常用到的不同值较多的列上建立检索,在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值,因此就无必要建立索引。如果建立索引不但不会提高查询效率,反而会严重降低更新速度。 
如果待排序的列有多个,可以在这些列上建立复合索引(compound index)。

使用索引应注意的事项:

 

(1)在下面两条select语句中:
   select * from table1  where  field1<=10000 and field1>=0;
   select * from table1  where  field1>=0 and field1<=10000;
   如果数据表中的数据field1都>=0,则第一条select语句要比第二条select语句效率高的多,因为第二条select语句的第一个条件耗费了大量的系统资源。
   第一个原则:在where子句中应把最具限制性的条件放在最前面。

(2)在下面的select语句中:
   select * from tab  where  a=… and b=… and c=…;
  若有索引index(a,b,c),则where子句中字段的顺序应和索引中字段顺序一致。
   第二个原则:where子句中字段的顺序应和索引中字段顺序一致。

以下假设在field1上有唯一索引I1,在field2上有非唯一索引I2。
(3) select field3,field4 from tb where field1='sdf'        快
    select * from tb where field1='sdf'      慢,
因为后者在索引扫描后要多一步ROWID表访问。

(4) select field3,field4 from tb where field1>='sdf'        快
select field3,field4 from tb where field1>'sdf'        慢
因为前者可以迅速定位索引。

(5) select field3,field4 from tb where field2 like 'R%'    快
    select field3,field4 from tb where field2 like '%R'    慢,
    因为后者不使用索引。

(6) 使用函数如:
select field3,field4 from tb where upper(field2)='RMN'不使用索引。
如果一个表有两万条记录,建议不使用函数;如果一个表有五万条以上记录,严格禁止使用函数!两万条记录以下没有限制。

(7) 空值不在索引中存储,所以
    select field3,field4 from tb where field2 is[not] null不使用索引。

(8) 不等式如
    select field3,field4 from tb where field2!='TOM'不使用索引。
    相似地,
    select field3,field4 from tb where field2 not in('M','P')不使用索引。

(9) 多列索引,只有当查询中索引首列被用于条件时,索引才能被使用。

(10)  MAX,MIN等函数,如
Select max(field2) from tb使用索引。所以,如果需要对字段取max,min,sum等,应该加索引。
一次只使用一个聚集函数,如:
select “min”=min(field1), “max”=max(field1)  from tb      
不如:select “min”=(select min(field1) from tb) , “max”=(select max(field1) from tb)    

(11) 重复值过多的索引不会被查询优化器使用。而且因为建了索引,修改该字段值时还要修改索引,所以更新该字段的操作比没有索引更慢。

(12) 索引值过大(如在一个char(40)的字段上建索引),会造成大量的I/O开销(甚至会超过表扫描的I/O开销)。因此,尽量使用整数索引。 Sp_estspace可以计算表和索引的开销。

(13) 对于多列索引,order by的顺序必须和索引的字段顺序一致。

(14) 在sybase中,如果order by的字段组成一个簇索引,那么无须做order by。记录的排列顺序是与簇索引一致的。

(15) 多表联结(具体查询方案需要通过测试得到)
    where子句中限定条件尽量使用相关联的字段,且尽量把相关联的字段放在前面。
    select a.field1,b.field2 from a,b where a.field3=b.field3
 1. field3上没有索引的情况下:
    对a作全表扫描,结果排序
    对b作全表扫描,结果排序
    结果合并。
    对于很小的表或巨大的表比较合适。

 2. field3上有索引
    按照表联结的次序,b为驱动表,a为被驱动表
    对b作全表扫描
    对a作索引范围扫描
    如果匹配,通过a的rowid访问

(16) 避免一对多的join。如:
     select tb1.field3,tb1.field4,tb2.field2 from tb1,tb2 where tb1.field2=tb2.field2 and tb1.field2=‘BU1032’ and tb2.field2= ‘aaa’
     不如:
     declare @a varchar(80)
select @a=field2 from tb2 where field2=‘aaa’  
     select tb1.field3,tb1.field4,@a from tb1 where field2= ‘aaa’

(16) 子查询
    用exists/not exists代替in/not in操作
    比较:
    select a.field1 from a where a.field2 in(select b.field1 from b where b.field2=100)
    select a.field1 from a where exists( select 1 from b where a.field2=b.field1 and b.field2=100)

    select field1 from a where field1 not in( select field2 from b)
    select field1 from a where not exists( select 1 from b where b.field2=a.field1)

(17) 主、外键主要用于数据约束,sybase中创建主键时会自动创建索引,外键与索引无关,提高性能必须再建索引。

(18) char类型的字段不建索引比int类型的字段不建索引更糟糕。建索引后性能只稍差一点。

(19)   使用count(*)而不要使用count(column_name),避免使用count(distinct column_name)。

(20) 等号右边尽量不要使用字段名,如:
select * from tb where field1 = field3
 
(21) 避免使用or条件,因为or不使用索引。

 

 

 

 

 

Oracle 中的索引技术

索引的管理成本

    1  存储索引的磁盘空间
    2  执行数据修改操作(INSERTUPDATEDELETE)产生的索引维护
    3  在数据处理时回需额外的回退空间。

    实际数据修改测试:

一个表有字段ABC,同时进行插入10000行记录测试


    在没有建索引时平均完成时间是3左右
    在对A字段建索引后平均完成时间是7左右
    在对A字段和B字段建索引后平均完成时间是10
    在对A字段、B字段和C字段都建索引后平均完成时间是11
    从以上测试结果可以明显看出索引对数据修改产生的影响

 

索引按存储方法分类

    B*树索引
    B*树索引是最常用的索引,其存储结构类似书的索引结构,有分支和叶两种类型的存储数据块,分支块相当于书的大目录,叶块相当于索引到的具体的书页。一般索引及唯一约束索引都使用B*树索引。

    位图索引
    位图索引储存主要用来节省空间,减少ORACLE对数据块的访问,它采用位图偏移方式来与表的行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_enabledTRUE
    alter session set query_rewrite_enabled=true
    注:如果对用户函数进行索引的话,那用户函数应加上 deterministic参数,意思是函数在输入值固定的情况下返回值也固定。例:
    create or replace function trunc_addinput_date datereturn 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_bhcb_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_yhbmgc_dfssgc_dfysgc_fpdy等业务表。

    数据重复且分布平均的表字段
    假如一个表有10万行记录,有一个字段A只有TF两种值,且每个值的分布概率大约为50%,那么对这种表A字段建索引一般不会提高数据库的查询速度。

    经常和主字段一块查询但主字段索引值比较多的表字段
    gc_dfss(电费实收)表经常按收费序号、户标识编号、抄表日期、电费发生年月、操作标志来具体查询某一笔收款的情况,如果将所有的字段都建在一个索引里那将会增加数据的修改、插入、删除时间,从实际上分析一笔收款如果按收费序号索引就已经将记录减少到只有几条,如果再按后面的几个字段索引查询将对性能不产生太大的影响。

如何只通过索引返回结果

一个索引一般包括单个或多个字段,如果能不访问表直接应用索引就返回结果那将大大提高数据库查询的性能。对比以下三个SQL,其中对表zl_yhjbqkhbs_bhqc_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的效率最高。执行路径可以看出第12SQL都多执行了TABLE ACCESS BY INDEX ROWID(通过ROWID访问表) 这个步骤,因为返回的结果列中包括当前使用索引(qc_bh)中未索引的列(hbs_bh,xh_bz),而第3SQL直接通过QC_BH返回了结果,这就是通过索引直接返回结果的方法。

 

如何重建索引

    alter index 表电量结果表主键 rebuild

    如何快速新建大数据量表的索引

    如果一个表的记录达到100万以上的话,要对其中一个字段建索引可能要花很长的时间,甚至导致服务器数据库死机,因为在建索引的时候ORACLE要将索引字段所有的内容取出并进行全面排序,数据量大的话可能导致服务器排序内存不足而引用磁盘交换空间进行,这将严重影响服务器数据库的工作。解决方法是增大数据库启动初始化中的排序内存参数,如果要进行大量的索引修改可以设置10M以上的排序内存(ORACLE缺省大小为64K),在索引建立完成后应将参数修改回来,因为在实际OLTP数据库应用中一般不会用到这么大的排序内存。

本文转载自:http://blog.csdn.net/u011990285/article/details/38701071

共有 人打赏支持
凌枫yong
粉丝 1
博文 65
码字总数 0
作品 0
南昌
私信 提问
Where语句设置不当导致索引失效

虽然说索引在使用上可能有种种限制,但是还是在数据库设计中被充分利用。因为在大部分情况下索引还是被用来提高数据库性能的一个工具。 虽然说索引在使用上可能有种种限制,但是还是在数据库...

疯狂的艺术家
2011/04/01
0
1
数据库系统学习一

学习书《数据库系统教程》第三版 作者:施伯乐 1、什么是索引? a、在关系数据库中,索引是表或视图在数据库中的一种结构,他可以使对应的表的SQL语句执行更快。索引的作用相当于图书的目录,...

小湘西
2015/10/22
55
0
数据库的唯一索引

唯一索引是不允许表中任何两行具有相同索引值的索引。 当现有的数据中存在重复的键值时,大多数数据库不允许把新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新...

博为峰教研组
2016/12/15
4
0
为 MySQL 查询优化选择最佳索引

我们的许多用户、开发者和数据库管理员不断向我们的团队咨询有关 EverSQL 的索引推荐算法。 所以,我们决定写一些这方面的内容。 本教程不会详细介绍该算法的所有内部特性,而是要简单地说明...

oschina
2018/02/05
3.7K
6
Oracle创建索引要做到三个适当

在Oracle数据库中,创建索引虽然比较简单。但是要合理的创建索引则比较困难了。笔者认为,在创建索引时要做到三个适当,即在适当的表上、适当的列上创建适当数量的索引。虽然这可以通过一句话...

超人不会飞
2012/07/28
0
0

没有更多内容

加载失败,请刷新页面

加载更多

C++随笔(四)Nuget打包

首先把自己编译好的包全部准备到一个文件夹 像这样 接下来新建一个文本文档,后缀名叫.nuspec 填写内容 <?xml version="1.0"?><package xmlns="http://schemas.microsoft.com/packaging/201......

Pulsar-V
33分钟前
0
0
再谈使用开源软件搭建数据分析平台

三年前,我写了这篇博客使用开源软件快速搭建数据分析平台, 当时收到了许多的反馈,有50个点赞和300+的收藏。到现在我还能收到一些关于dataplay2的问题。在过去的三年,开源社区和新技术的发...

naughty
今天
3
0
Python3的日期和时间

python 中处理日期时间数据通常使用datetime和time库 因为这两个库中的一些功能有些重复,所以,首先我们来比较一下这两个库的区别,这可以帮助我们在适当的情况下时候合适的库。 在Python文...

编程老陆
今天
2
0
分布式面试整理

并发和并行 并行是两个任务同时进行,而并发呢,则是一会做一个任务一会又切换做另一个任务。 临界区 临界区用来表示一种公共资源或者说是共享数据,可以被多个线程使用,但是每一次,只能有...

群星纪元
今天
3
0
手机通过wifi遥控arduino

手机下载Blinker 从Blinker官网下载手机App,安装到手机。 手机连接WiFi。 点击我的设备右上角的"+"添加设备,选择Arduino -> wifi接入,复制密钥以备后续使用。 点击新建的设备,可以在新界...

davidwbnu
昨天
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部