索引在数据库中的应用分析
索引是提高数据查询最有效的方法,也是最难全面掌握的技术,因为正确的索引可能使效率提高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*树索引。
位图索引
位图索引储存主要用来节省空间,减少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_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=’<chmetcnv w:st="on" tcsc="0" numbertype="1" negative="False" hasspace="False" sourcevalue="7001" unitname="’">7001’</chmetcnv>
查询中与其它表关联的字段
字段常常建立了外键关系
如zl_ydcf(用电成份)中的jldb_bh(计量点表编号)
select * from zl_ydcf a,zl_yhdb b where a.jldb_bh=b.jldb_bh and b.jldb_bh=’<chmetcnv w:st="on" tcsc="0" numbertype="1" negative="False" hasspace="False" sourcevalue="540100214511" unitname="’">540100214511’</chmetcnv>
查询中排序的字段
排序的字段如果通过索引去访问那将大大提高排序速度
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=’<chmetcnv w:st="on" tcsc="0" numbertype="1" negative="False" hasspace="False" sourcevalue="7001" unitname="’">7001’</chmetcnv>
执行路径:
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=’<chmetcnv w:st="on" tcsc="0" numbertype="1" negative="False" hasspace="False" sourcevalue="7001" unitname="’">7001’</chmetcnv>
执行路径:
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=’<chmetcnv w:st="on" tcsc="0" numbertype="1" negative="False" hasspace="False" sourcevalue="7001" unitname="’">7001’</chmetcnv>
执行路径:
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要将索引字段所有的内容取出并进行全面排序,数据量大的话可能导致服务器排序内存不足而引用磁盘交换空间进行,这将严重影响服务器数据库的工作。解决方法是增大数据库启动初始化中的排序内存参数,如果要进行大量的索引修改可以设置<chmetcnv w:st="on" tcsc="0" numbertype="1" negative="False" hasspace="False" sourcevalue="10" unitname="m"><span lang="EN-US">10M</span></chmetcnv>以上的排序内存(ORACLE缺省大小为64K),在索引建立完成后应将参数修改回来,因为在实际OLTP数据库应用中一般不会用到这么大的排序内存。
叶正盛
2003-08-15
分享到:
相关推荐
4. 数据库设计:在创建Oracle数据库时,需要进行需求分析、概念设计(ER图)、逻辑设计和物理设计。良好的设计能提高性能,减少数据冗余,确保数据一致性。 5. 表空间与数据文件:Oracle数据库使用表空间来组织数据...
这份“ORACLE各平台安装配置原创文档”详细介绍了在不同操作系统上安装和配置Oracle数据库的过程,对于IT专业人员来说,是一份非常有价值的参考资料。以下是根据标题和描述提取的关键知识点: 1. **Oracle数据库...
最后,"超大型ORACLE数据库应用系统的设计--Oracle专区--IT胖子2.htm"和"超大型ORACLE数据库应用系统的设计--Oracle专区--IT胖子1.htm"可能探讨了在处理大规模数据时,如何优化架构、负载均衡、分区策略等,这对于...
主流数据库包括Oracle、MySQL、SQL Server、PostgreSQL等,每种数据库都有其特定的优势和适用场景,Oracle在企业级应用中表现出色。 3. **Oracle认证** Oracle Certified Professional (OCP) 是Oracle提供的专业...
总结以上内容,Oracle数据库的分区技术是数据库管理中的高级技巧,通过合理应用分区技术,数据库管理员可以有效地提升数据库性能、简化数据维护工作以及优化查询效率。从Oracle 8.0到最新版本,分区技术不断进化,...
综上所述,数据库调研报告应涵盖对数据库基础知识的理解、发展历程的阐述、优化策略的探讨以及应用场景的分析。通过网络调研和图书资料获取信息,撰写一篇800-1000字的报告,可以深入探讨数据库技术的现状及其在现代...
1. **Oracle概述**:介绍Oracle公司的历史、Oracle数据库的版本发展以及其在业界的地位。解释什么是关系型数据库,以及Oracle数据库与其他数据库系统(如MySQL、SQL Server等)的区别。 2. **Oracle架构**:详述...
在Oracle数据库中,创建表通常涉及到`CREATE TABLE`语句,可能还会包括数据类型定义、约束条件(如主键、外键、唯一性等)、索引以及其他数据库对象(如视图、存储过程等)的创建。 Oracle数据库是关系型数据库管理...
根据Evans Data Corporation的多客户调查数据,MySQL在过去两年中在开发者数据库使用方面的市场份额增长了25%,显示了其强大的市场吸引力。 【PostgreSQL 概述】 PostgreSQL,通常简称为Postgres,是一种开源的...
### MySQL 数据库基础知识点 #### 一、数据库简介与数据保存方式 ...通过以上内容的介绍,我们对 MySQL 数据库的基础知识有了一个全面的理解,这将有助于我们在实际开发过程中更加高效地管理和使用数据库资源。
在本篇作业中,我们将深入探讨Oracle数据库系统管理和维护的相关知识,主要涵盖SQL语句的编写、表的创建与约束、用户及权限管理、索引与视图的创建以及数据库安全策略的设计。以下是对每个任务的详细解释: 1. 创建...
在本作业中,我们将深入探讨Oracle数据库系统的管理和维护,包括SQL语句的编写、表的创建、外键约束、用户管理、权限分配、索引创建、视图设计以及数据库安全策略的规划。以下是对各项任务的详细解释: 1. 创建...
Oracle SQL Handler,是专为Oracle数据库开发人员及操作人员精心打造的一款Oracle开发工具(客户端工具)。国产原创,精品奉献,无序列号限制,仅凭使用满意度随意赞助就可永久使用! Oracle SQL Handler 特点...
3. **Java**:这是一种广泛应用的编程语言,尤其在服务器端开发中。在这个项目中,Java被用作后端语言,处理小程序的请求和数据处理。 4. **Spring Boot**:这是Spring Framework的一个扩展,简化了Java Web应用的...
在这个过程中,学生需要使用现有的数据建模工具和数据库管理系统软件,例如SQL Server或Oracle,来规范地设计并实现一个小型数据库应用系统。设计的目标主要包括三个方面: 1. 掌握关系数据库的设计方法:这涉及到...
在课程设计中,学生需要学会如何使用Oracle创建数据库,定义表,设置索引,以及管理用户权限。 4. **数据库安全性**:配置用户权限是确保数据安全的重要步骤。学生需要了解如何创建用户,分配不同级别的访问权限,...
物理结构设计阶段关注如何在特定数据库管理系统(如SQL Server、Oracle等)中实现这些关系模型,包括表的创建、索引的设计以及存储优化。数据库实施则包括创建数据库、定义表结构和编写针对管理员、考勤、会议记录、...
GoldenGate 同步无主键无唯一索引表的问题及解决方案 在数据同步中,无主键无唯一索引表的同步问题是常见的困扰, GoldenGate...在实际应用中,需要根据具体情况选择合适的解决方案,以确保数据同步的正确性和可靠性。
通过这些上机练习,我们可以逐步掌握数据库设计的技巧,并理解如何在实际项目中应用。记住,优秀的数据库设计不仅仅是初期工作,还需要随着业务的发展不断调整和完善。尊重并保护知识产权,是我们作为IT从业者的基本...
数据库课程设计旨在让学生在掌握了《数据库系统原理》的基础理论后,将理论知识应用于实践,通过设计和开发一个小型的数据库管理信息系统,提升学生的实际操作能力和问题解决能力。设计过程中,学生需要学会如何分析...