- 浏览: 1435231 次
- 性别:
- 来自: 广州
文章分类
- 全部博客 (409)
- Java (48)
- Spring (29)
- struts2 (17)
- hibernate (4)
- 设计模式 (24)
- jbpm (1)
- JavaScript (5)
- 统计报表 (7)
- ExtJS_3.0 (35)
- struts1 (1)
- 分析设计 (3)
- Flex3 (24)
- UML (2)
- 数据库 (18)
- PowerDesigner (2)
- 应用服务器 (3)
- WebService (5)
- ActiveMQ_5.3.2 (6)
- Java通信技术 (11)
- GWT (6)
- OSGi (15)
- android (11)
- liferay6.0.6 (13)
- jquery (13)
- Linux (3)
- java.util.concurrent (16)
- guava (9)
- 开发模式 (1)
- 大数据 (2)
- 互联网金融 (4)
- treegrid-3.0 (7)
- 分布式 (8)
- GO语言 (4)
- maven (1)
- 缓存技术 (6)
- 其他 (2)
- 前端页面 (1)
- heasy (1)
- spring cloud(F版) (21)
- springboot (12)
- springmvc (5)
- mybatis (3)
- dubbo (1)
- 物联网 (0)
最新评论
-
raymond.chen:
谢谢您的分享
使用Ngrok解决通过外网访问内网web应用 -
wangyudong:
速度有点慢,不过在也找到了一个开源的holer,配置一个key ...
使用Ngrok解决通过外网访问内网web应用 -
a1006458222:
...
Axis2的部署和应用 -
偷师来了:
不好意思 这样的博客我觉得就灭有必要分享出来了 命令大家都会看 ...
Consul框架介绍 -
lliiqiang:
怎么直接删除文件夹啊?固定的几个文件可以删除,不固定的呢?需要 ...
Flex AIR —— 文件读写
本文首先讨论了基于第三范式的数据库表的基本设计,着重论述了建立主键和索引的策略和方案,然后从数据库表的扩展设计和库表对象的放置等角度概述了数据库管理系统的优化方案。
关键词: 优化(Optimizing) 第三范式(3NF) 冗余数据(Redundant Data) 索引(Index) 数据分割(Data Partitioning) 对象放置(Object Placement)
1 引言
数据库优化的目标无非是避免磁盘I/O瓶颈、减少CPU利用率和减少资源竞争。为了便于读者阅读和理解,笔者参阅了Sybase、Informix和Oracle等大型数据库系统参考资料,基于多年的工程实践经验,从基本表设计、扩展设计和数据库表对象放置等角度进行讨论,着重讨论了如何避免磁盘I/O瓶颈和减少资源竞争,相信读者会一目了然。
2 基于第三范式的基本表设计
在基于表驱动的信息管理系统(MIS)中,基本表的设计规范是第三范式(3NF)。第三范式的基本特征是非主键属性只依赖于主键属性。基于第三范式的数据库表设计具有很多优点:一是消除了冗余数据,节省了磁盘存储空间;二是有良好的数据完整性限制,即基于主外键的参照完整限制和基于主键的实体完整性限制,这使得数据容易维护,也容易移植和更新;三是数据的可逆性好,在做连接(Join)查询或者合并表时不遗漏、也不重复;四是因消除了冗余数据(冗余列),在查询(Select)时每个数据页存的数据行就多,这样就有效地减少了逻辑I/O,每个Cash存的页面就多,也减少物理I/O;五是对大多数事务(Transaction)而言,运行性能好;六是物理设计(Physical Design)的机动性较大,能满足日益增长的用户需求。
在基本表设计中,表的主键、外键、索引设计占有非常重要的地位,但系统设计人员往往只注重于满足用户要求,而没有从系统优化的高度来认识和重视它们。实际上,它们与系统的运行性能密切相关。现在从系统数据库优化角度讨论这些基本概念及其重要意义:
(1)主键(Primary Key):主键被用于复杂的SQL语句时,频繁地在数据访问中被用到。一个表只有一个主键。主键应该有固定值(不能为Null或缺省值,要有相对稳定性),不含代码信息,易访问。把常用(众所周知)的列作为主键才有意义。短主键最佳(小于25bytes),主键的长短影响索引的大小,索引的大小影响索引页的大小,从而影响磁盘I/O。主键分为自然主键和人为主键。自然主键由实体的属性构成,自然主键可以是复合性的,在形成复合主键时,主键列不能太多,复合主键使得Join*作复杂化、也增加了外键表的大小。人为主键是,在没有合适的自然属性键、或自然属性复杂或灵敏度高时,人为形成的。人为主键一般是整型值(满足最小化要求),没有实际意义,也略微增加了表的大小;但减少了把它作为外键的表的大小。
(2)外键(Foreign Key):外键的作用是建立关系型数据库中表之间的关系(参照完整性),主键只能从独立的实体迁移到非独立的实体,成为后者的一个属性,被称为外键。
(3)索引(Index):利用索引优化系统性能是显而易见的,对所有常用于查询中的Where子句的列和所有用于排序的列创建索引,可以避免整表扫描或访问,在不改变表的物理结构的情况下,直接访问特定的数据列,这样减少数据存取时间;利用索引可以优化或排除耗时的分类*作;把数据分散到不同的页面上,就分散了插入的数据;主键自动建立了唯一索引,因此唯一索引也能确保数据的唯一性(即实体完整性);索引码越小,定位就越直接;新建的索引效能最好,因此定期更新索引非常必要。索引也有代价:有空间开销,建立它也要花费时间,在进行Insert、Delete和Update*作时,也有维护代价。索引有两种:聚族索引和非聚族索引。一个表只能有一个聚族索引,可有多个非聚族索引。使用聚族索引查询数据要比使用非聚族索引快。在建索引前,应利用数据库系统函数估算索引的大小。
① 聚族索引(Clustered Index):聚族索引的数据页按物理有序储存,占用空间小。选择策略是,被用于Where子句的列:包括范围查询、模糊查询或高度重复的列(连续磁盘扫描);被用于连接Join*作的列;被用于Order by和Group by子句的列。聚族索引不利于插入*作,另外没有必要用主键建聚族索引。
② 非聚族索引(Nonclustered Index):与聚族索引相比,占用空间大,而且效率低。选择策略是,被用于Where子句的列:包括范围查询、模糊查询(在没有聚族索引时)、主键或外键列、点(指针类)或小范围(返回的结果域小于整表数据的20%)查询;被用于连接Join*作的列、主键列(范围查询);被用于Order by和Group by子句的列;需要被覆盖的列。对只读表建多个非聚族索引有利。索引也有其弊端,一是创建索引要耗费时间,二是索引要占有大量磁盘空间,三是增加了维护代价(在修改带索引的数据列时索引会减缓修改速度)。那么,在哪种情况下不建索引呢?对于小表(数据小于5页)、小到中表(不直接访问单行数据或结果集不用排序)、单值域(返回值密集)、索引列值太长(大于20bitys)、容易变化的列、高度重复的列、Null值列,对没有被用于Where子语句和Join查询的列都不能建索引。另外,对主要用于数据录入的,尽可能少建索引。当然,也要防止建立无效索引,当Where语句中多于5个条件时,维护索引的开销大于索引的效益,这时,建立临时表存储有关数据更有效。
批量导入数据时的注意事项:在实际应用中,大批量的计算(如电信话单计费)用C语言程序做,这种基于主外键关系数据计算而得的批量数据(文本文件),可利用系统的自身功能函数(如Sybase的BCP命令)快速批量导入,在导入数据库表时,可先删除相应库表的索引,这有利于加快导入速度,减少导入时间。在导入后再重建索引以便优化查询。
(4)锁:锁是并行处理的重要机制,能保持数据并发的一致性,即按事务进行处理;系统利用锁,保证数据完整性。因此,我们避免不了死锁,但在设计时可以充分考虑如何避免长事务,减少排它锁时间,减少在事务中与用户的交互,杜绝让用户控制事务的长短;要避免批量数据同时执行,尤其是耗时并用到相同的数据表。锁的征用:一个表同时只能有一个排它锁,一个用户用时,其它用户在等待。若用户数增加,则Server的性能下降,出现“假死”现象。如何避免死锁呢?从页级锁到行级锁,减少了锁征用;给小表增加无效记录,从页级锁到行级锁没有影响,若在同一页内竞争有影响,可选择合适的聚族索引把数据分配到不同的页面;创建冗余表;保持事务简短;同一批处理应该没有网络交互。
(5)查询优化规则:在访问数据库表的数据(Access Data)时,要尽可能避免排序(Sort)、连接(Join)和相关子查询*作。经验告诉我们,在优化查询时,必须做到:
① 尽可能少的行;
② 避免排序或为尽可能少的行排序,若要做大量数据排序,最好将相关数据放在临时表中*作;用简单的键(列)排序,如整型或短字符串排序;
③ 避免表内的相关子查询;
④ 避免在Where子句中使用复杂的表达式或非起始的子字符串、用长字符串连接;
⑤ 在Where子句中多使用“与”(And)连接,少使用“或”(Or)连接;
⑥ 利用临时数据库。在查询多表、有多个连接、查询复杂、数据要过滤时,可以建临时表(索引)以减少I/O。但缺点是增加了空间开销。
除非每个列都有索引支持,否则在有连接的查询时分别找出两个动态索引,放在工作表中重新排序。
3 基本表扩展设计
基于第三范式设计的库表虽然有其优越性(见本文第一部分),然而在实际应用中有时不利于系统运行性能的优化:如需要部分数据时而要扫描整表,许多过程同时竞争同一数据,反复用相同行计算相同的结果,过程从多表获取数据时引发大量的连接*作,当数据来源于多表时的连接*作;这都消耗了磁盘I/O和CPU时间。
尤其在遇到下列情形时,我们要对基本表进行扩展设计:许多过程要频繁访问一个表、子集数据访问、重复计算和冗余数据,有时用户要求一些过程优先或低的响应时间。
如何避免这些不利因素呢?根据访问的频繁程度对相关表进行分割处理、存储冗余数据、存储衍生列、合并相关表处理,这些都是克服这些不利因素和优化系统运行的有效途径。
3.1 分割表或储存冗余数据
分割表分为水平分割表和垂直分割表两种。分割表增加了维护数据完整性的代价。
水平分割表:一种是当多个过程频繁访问数据表的不同行时,水平分割表,并消除新表中的冗余数据列;若个别过程要访问整个数据,则要用连接*作,这也无妨分割表;典型案例是电信话单按月分割存放。另一种是当主要过程要重复访问部分行时,最好将被重复访问的这些行单独形成子集表(冗余储存),这在不考虑磁盘空间开销时显得十分重要;但在分割表以后,增加了维护难度,要用触发器立即更新、或存储过程或应用代码批量更新,这也会增加额外的磁盘I/O开销。
垂直分割表(不破坏第三范式),一种是当多个过程频繁访问表的不同列时,可将表垂直分成几个表,减少磁盘I/O(每行的数据列少,每页存的数据行就多,相应占用的页就少),更新时不必考虑锁,没有冗余数据。缺点是要在插入或删除数据时要考虑数据的完整性,用存储过程维护。另一种是当主要过程反复访问部分列时,最好将这部分被频繁访问的列数据单独存为一个子集表(冗余储存),这在不考虑磁盘空间开销时显得十分重要;但这增加了重叠列的维护难度,要用触发器立即更新、或存储过程或应用代码批量更新,这也会增加额外的磁盘I/O开销。垂直分割表可以达到最大化利用Cache的目的。
总之,为主要过程分割表的方法适用于:各个过程需要表的不联结的子集,各个过程需要表的子集,访问频率高的主要过程不需要整表。在主要的、频繁访问的主表需要表的子集而其它主要频繁访问的过程需要整表时则产生冗余子集表。
注意,在分割表以后,要考虑重新建立索引。
3.2 存储衍生数据
对一些要做大量重复性计算的过程而言,若重复计算过程得到的结果相同(源列数据稳定,因此计算结果也不变),或计算牵扯多行数据需额外的磁盘I/O开销,或计算复杂需要大量的CPU时间,就考虑存储计算结果(冗余储存)。现予以分类说明:
若在一行内重复计算,就在表内增加列存储结果。但若参与计算的列被更新时,必须要用触发器更新这个新列。
若对表按类进行重复计算,就增加新表(一般而言,存放类和结果两列就可以了)存储相关结果。但若参与计算的列被更新时,就必须要用触发器立即更新、或存储过程或应用代码批量更新这个新表。
若对多行进行重复性计算(如排名次),就在表内增加列存储结果。但若参与计算的列被更新时,必须要用触发器或存储过程更新这个新列。
总之,存储冗余数据有利于加快访问速度;但违反了第三范式,这会增加维护数据完整性的代价,必须用触发器立即更新、或存储过程或应用代码批量更新,以维护数据的完整性。
3.3 消除昂贵结合
对于频繁同时访问多表的一些主要过程,考虑在主表内存储冗余数据,即存储冗余列或衍生列(它不依赖于主键),但破坏了第三范式,也增加了维护难度。在源表的相关列发生变化时,必须要用触发器或存储过程更新这个冗余列。当主要过程总同时访问两个表时可以合并表,这样可以减少磁盘I/O*作,但破坏了第三范式,也增加了维护难度。对父子表和1:1关系表合并方法不同:合并父子表后,产生冗余表;合并1:1关系表后,在表内产生冗余数据。
4 数据库对象的放置策略
数据库对象的放置策略是均匀地把数据分布在系统的磁盘中,平衡I/O访问,避免I/O瓶颈。
⑴ 访问分散到不同的磁盘,即使用户数据尽可能跨越多个设备,多个I/O运转,避免I/O竞争,克服访问瓶颈;分别放置随机访问和连续访问数据。
⑵ 分离系统数据库I/O和应用数据库I/O。把系统审计表和临时库表放在不忙的磁盘上。
⑶ 把事务日志放在单独的磁盘上,减少磁盘I/O开销,这还有利于在障碍后恢复,提高了系统的安全性。
⑷ 把频繁访问的“活性”表放在不同的磁盘上;把频繁用的表、频繁做Join*作的表分别放在单独的磁盘上,甚至把把频繁访问的表的字段放在不同的磁盘上,把访问分散到不同的磁盘上,避免I/O争夺;
⑸ 利用段分离频繁访问的表及其索引(非聚族的)、分离文本和图像数据。段的目的是平衡I/O,避免瓶颈,增加吞吐量,实现并行扫描,提高并发度,最大化磁盘的吞吐量。利用逻辑段功能,分别放置“活性”表及其非聚族索引以平衡I/O。当然最好利用系统的默认段。另外,利用段可以使备份和恢复数据更加灵活,使系统授权更加灵活。
发表评论
-
Oracle的表分区
2019-11-25 14:11 397当表中的数据量不断增大,查询数据的速度就会变慢,应用程序的 ... -
Oracle使用汇总
2015-12-31 16:51 8001、使用CLOB字段作为过滤条件 select ... -
mysql的安装和配置
2015-11-28 15:44 859安装配置步骤如下: 1、下载mysql-5.6. ... -
oracle lead分析函数
2015-11-23 21:17 1733Lag和Lead分析函数可以在同一次查询中取出同一字段的前 ... -
Oracle存储过程
2015-11-23 13:58 766new_time(sysdate,'GMT',' ... -
Oracle11g安装过程遇到的问题
2015-10-17 21:58 1860Oracle 11g安装成功后,在DOS窗口输入 emctl ... -
在oracle中使用scheduler的范例
2013-04-16 17:09 2119范例源码: -- 创建新表 create table ... -
在Oracle中使用job的范例
2013-04-16 15:17 1563范例源码: -- 创建新 ... -
数据库理论知识
2010-06-10 14:40 20601、范式 第一范式(1NF): 字段 ... -
Sqlserver多表更新
2010-04-16 20:56 14911、sql update dbo.new_user set ... -
Oracle触发器的使用范例
2010-03-18 13:56 830create or replace trigger TG_MS ... -
常用数据库连接配置
2009-12-16 13:37 1737Oracle 10G oracle.jdbc.dri ... -
Sqlserver存储过程及游标的使用
2009-04-23 16:34 1866SET ANSI_NULLS ON GO SET QUOT ... -
SqlServer触发器的应用
2009-03-04 11:13 2184更新事务类似于在删除之后执行插入;首先旧行被复制到 delet ... -
Oracle包的使用
2008-11-19 16:37 5116一、包规范 1、report包 CREATE OR R ... -
Oracle游标(Cursor)的应用(一)
2008-07-01 10:17 1863功能:将某个入库批次中号码数量等于40的札拆分成两札 d ... -
Oracle多表关联update字段值
2008-07-01 10:02 39851、sql更新: UPDATE NUM_ACTIVE_INT ...
相关推荐
【数据库优化设计方案】 数据库优化是提升系统性能的关键环节,旨在避免磁盘I/O瓶颈、减少CPU利用率和降低资源竞争。本文将深入探讨基于第三范式的基本表设计、扩展设计以及数据库表对象放置等方面,旨在为实际开发...
ORACLE数据库优化设计方案(全版) ORACLE数据库优化设计方案(全版) ORACLE数据库优化设计方案(全版)
### 大型Oracle数据库优化设计方案解析 在当今数据驱动的时代,高效、稳定且优化的数据库系统对于企业运营至关重要。尤其对于大型Oracle数据库,其优化设计不仅关乎数据处理速度,更直接影响到业务连续性和用户体验...
### ORACLE数据库优化设计方案 #### 一、数据库优化自由结构OFA(Optimal Flexible Architecture) 在Oracle数据库的设计中,逻辑配置对于性能具有重要的影响。Oracle推荐了一种优化结构——OFA(Optimal Flexible ...
"大型ORACLE数据库优化设计方案" 本文主要从大型数据库ORACLE环境四个不同级别的调整分析入手,分析ORACLE的系统结构和工作机理,从九个不同方面较全面地总结了ORACLE数据库的优化调整方案。 一、数据库优化自由...
"遗传算法和实时数据库规则结合的数据库查询优化方案设计" 本文旨在结合遗传算法和实时数据库规则,设计一个数据库查询优化方案。数据库的建立是实现各种类型事务查询的关键,而现有的查询处理系统无法满足实时...
本文将深入探讨SQL数据库优化方案,包括查询优化、索引优化、存储优化和架构优化等策略,旨在帮助你提升数据库的运行效率。 一、查询优化 查询优化是数据库优化的基础,主要涉及SQL语句的编写。合理的SQL语句可以...
本文档主要阐述了四个级别的数据库调整和九个方面的优化设计方案。 首先,数据库优化涉及四个层次的调整:操作系统级、RDBMS级、数据库设计级和SQL级。操作系统级优化包括对硬件平台的调整,确保其能有效支持数据库...
【大型ORACLE数据库优化设计方案】 Oracle数据库作为一款广泛使用的大型企业级数据库系统,其性能优化是确保系统稳定高效运行的关键。本方案主要针对ORACLE数据库的优化策略进行深入探讨,涵盖了从操作系统层面到...
Oracle数据库优化设计方案是一个复杂而全面的过程,涉及到数据库的多个层面,包括硬件配置、数据库结构、内存管理、数据规范与反规范、索引设计以及并行处理等。以下是对这些方面的详细解析: 1. **科学配置逻辑...
《ORACLE数据库优化设计方案的研究》这篇论文主要探讨了如何针对ORACLE数据库进行性能优化,以提升系统的整体效能。文章从数据库的系统结构出发,提出了多个关键优化策略,涵盖了硬件平台、数据库管理系统、数据库...
数据库优化存储设计方案 数据库优化存储设计方案是指通过对数据库的设计、优化和调整,提高数据库的存储和查询性能,减少磁盘 I/O 瓶颈、降低 CPU 利用率和减少资源竞争。该方案涵盖了基于第三范式的基本表设计、...
数据库性能优化是IT领域中的一个核心议题,尤其对于...文件“数据库性能优化方案.htm”和“数据库性能优化方案.files”可能包含更具体的实例和工具使用指南,学习和实践这些资料将有助于提升数据库管理与优化的能力。
文章介绍了分布式遥感影像数据库的架构设计、数据库技术、SOAP/XML关键技术、辅助元数据库、遥感影像数据库优化设计等知识点。 首先,本文介绍了分布式遥感影像数据库的架构设计,包括前台展现层和后台支持层。前台...
【数据库优化专项方案】 数据库优化是提升系统性能的关键环节,主要目标是提高数据查询速度、减少资源消耗,以及确保系统的稳定性和可扩展性。本文主要针对SQL语句设计和操作符优化两个方面展开讨论。 首先,高效...
大型数据库ORACLE数据库的优化设计方案 (1).pdf