建立索引提高数据库查询速度

Advertisement




索引是提高数据查询最有效的方法,也是最难全面掌握的技术,因为正确的索引可能使效率提高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数据库应用中一般不会用到这么大的排序内存。

Similar Posts:

  • 如何提高数据库查询速度 (摘录) -转http://www.cnblogs.com/yzwdli/archive/2008/03/09/1097216.html

    1.用程序中, 保证在实现功能的基础上,尽量减少对数据库的访问次数: 通过搜索参数,尽量减少对表的访问行数,最小化结果集,从而减轻网络负担: 能够分开的操作尽量分开处理,提高每次的响应速度: 在数据窗口使用SQL时,尽量把使用的索引放在选择的首列: 算法的结构尽量简单: 在查询时,不要过多地使用通配符如SELECT * FROM T1语句,要用到几列就选择几列如: SELECT COL1,COL2 FROM T1: 在可能的情况下尽量限制尽量结果集行数如: SELECT TOP 300 COL1

  • (转)处理上百万条的数据库如何提高处理查询速度

    处理上百万条的数据库如何提高处理查询速度 1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引. 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t where num is null 可以在num上设置默认值0,确保表中num列没有null值,然后这样查询: select id from t where num=0 3.应尽量避免在 where

  • 转:SQL Server数据库查询速度慢的原因

    SQL Server数据库查询速度慢的原因有很多,常见的有以下几种: 1.没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷) 2.I/O吞吐量小,形成了瓶颈效应. 3.没有创建计算列导致查询不优化. 4.内存不足 5.网络速度慢 6.查询出的数据量过大(可以采用多次查询,其他的方法降低数据量) 7.锁或者死锁(这也是查询慢最常见的问题,是程序设计的缺陷) 8.sp_lock,sp_who,活动的用户查看,原因是读写竞争资源. 9.返回了不必要的行和列 10.查询语句不好,没有优

  • 6条策略提高mysql查询速度 潇湘博客

    6条策略提高mysql查询速度 1,表设计一定要优化,冗余数据最少,少用连接查询.如果在实际应用中,使用了极其复杂的连接,子查询,则数据表的设计得要重新考虑了. 2,尽量用char而不是varchar,因为固定长度得string用起来更快.在当今硬盘容量越来越大的情况下,牺牲点存储空间而换得查询速度得提升是值得的. 3,通过简化权限来提高查询速度.如果一个查询之前要执行很多权限验证,则查询速度会慢下来,不妨试着在mysql中用root登录与用你新建的有权限控制的用户登录的速度,就可以看出来了,r

  • 处理上百万条的数据库如何提高处理查询速度

    1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引. 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t where num is null 可以在num上设置默认值0,确保表中num列没有null值,然后这样查询: select id from t where num=0 3.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放

  • [转载]php 处理上百万条的数据库如何提高处理查询速度

    转载自http://www.jb51.net/article/22063.htm -------------------------------------------------------------------------------------------------------------------------------------- 1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引. 2.应尽量避免在 where 子句中对

  • 决策等于一切 SQL Server 索引 提高数据库的查询性能,但是性能的提高取决于数据库的实现

    管理层的决策 通过有效的使用索引,可以在微软的SQL Server系统中实现很好的查询功能,但是使用索引的效率取决于几种不同的实现决策.在索引的性能平衡方面,要做出正确的数据库管理决策意味着需要在良 好的性能和困境中抉择. 在微软的SQL Server系统中通过有效的使用索引可以提高数据库的查询性能,但是性能的提高取决于数据库的实现. 索引的定义 索引是数据库的工具,通过使用索引,在数据库中获取数据的时候,就可以不用扫描数据库中的所有数据记录,这样能够提高系统获取数据的性能.使用索引可以改变数据

  • 【Anychat音视频开发】数据库查询速度慢的原因

    查询速度慢的原因很多,常见如下几种: 1.没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷) 2.I/O吞吐量小,形成了瓶颈效应. 3.没有创建计算列导致查询不优化. 4.内存不足 5.网络速度慢 6.查询出的数据量过大(可以采用多次查询,其他的方法降低数据量) 7.锁或者死锁(这也是查询慢最常见的问题,是程序设计的缺陷) 8.sp_lock,sp_who,活动的用户查看,原因是读写竞争资源. 9.返回了不必要的行和列 10.查询语句不好,没有优化 可以通过如下方法来优化查询

  • 关于mysql处理百万级以上的数据时如何提高其查询速度的方法

    最近一段时间由于工作需要,开始关注针对Mysql数据库的select查询语句的相关优化方法. 由于在参与的实际项目中发现当mysql表的数据量达到百万级时,普通SQL查询效率呈直线下降,而且如果where中的查询条件较多时,其查询速度简直无法容忍.曾经测试对一个包含400多万条记录(有索引)的表执行一条条件查询,其查询时间竟然高达40几秒,相信这么高的查询延时,任何用户都会抓狂.因此如何提高sql语句查询效率,显得十分重要.以下是网上流传比较广泛的30种SQL查询语句优化方法: 1.应尽量避免在

  • (转)数据库查询速度慢的原因

    ●查询速度慢的原因很多,常见如下几种: 1.没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷) 2.I/O吞吐量小,形成了瓶颈效应. 3.没有创建计算列导致查询不优化. 4.内存不足 5.网络速度慢 6.查询出的数据量过大(可以采用多次查询,其他的方法降低数据量) 7.锁或者死锁(这也是查询慢最常见的问题,是程序设计的缺陷) 8.sp_lock,sp_who,活动的用户查看,原因是读写竞争资源. 9.返回了不必要的行和列 10.查询语句不好,没有优化 ●可以通过如下方法来优化查

Tags: