从范式优化到检索优化数据库优化技术的分析与探讨

从范式优化到检索优化数据库优化技术的分析与探讨

随着数据库技术的发展，计算商业数据分析越来越受到重视。但由于传统的基于文件的数据存储和检索方法不灵活且难于管理,因而数据库优化设计成为人们关注的题。如何有效地组织和处理大型数据库的海量数据,使得人们能方便、准确、快捷地完成数据的存取操作,成为对数据库的建设及使用需要解决的问题。数据库的数据存取主要有四个不同的调整级别,第一级调整是操作系统级包括硬件平台,第二级调整是DBMS级的调整,第三级是数据库设计级的调整,最后一个调整级是SQL级。通常依此四级调整级别对数据库进行调整、优化,数据库的整体性能会得到很大的改善。所以,数据库的开发过程中,程序员能够在数据库设计阶段,能够根据数据库实际使用需要,采用适当的范式优化对基本表进行合理的设计和调整,同时在数据库应用程序开发时,注重提高查询语句的执行效率,将会在很大程度提高数据库性能。1建立数据库表的必要性在数据库开发过程中,需要利用范式对基本表进行规范化。一般情况下,基本表的设计要求达到第三范式,第三范式的特征是在消除函数依赖的基础上消除传递依赖,从而使非主键的其他属性只依赖于主键属性。第三范式的使用消除了由于数据冗余带来的插入、更新和删除操作产生的不可预期的副作用,同时保证了良好的的参照完整限制和实体完整性限制,使得数据容易维护。将规范化作为数据库开发过程的完善工具时,首先利用E-R图建立概念数据模型,并使用转换规则生成基本表,然后利用范式分析各个表,最终保证基本表及其字段之间的关系满足第三范式。基于第三范式设计的数据库表虽然有其优越性,但是,满足第三范式的数据库设计,往往不一定是最好的设计。作为一项设计标准,第三范式“消除冗余以避免更改异常”偏重于数据库的更改功能。在数据库设计中,当分解表来符合范式要求时,表的数量会增加,虽然数据库越容易更改,但查询的难度会随之增加。如果数据库在实际应用中主要用于查询,那么过多追求避免更改异常不见得是一个恰到好处的设计目标。另外,加入更多的表容易导致过程从多表获取数据时引发大量的连接操作,占用额外的磁盘I/O操作和处理逻辑,因此减慢了系统速度。所以,在许多过程要频繁访问一个表、子集数据访问、重复计算和冗余数据的情况下,需要对基本表进行结构优化和性能调整。1.1水平分割表或垂直分割表当一个表包含过大数据量,而主要过程反复访问一个表中的部分行或者部分列而不需要访问整个表时,可以水平分割表或者垂直分割表,可以将被频繁访问的列数据单独存为一个子集表。这些操作都是在不用考虑磁盘的开销的情况下,同时分割表也增加了维护数据完整性的代价。1.2加相应列计算对一些要做大量重复性计算的过程而言,可以考虑增加相应列存储计算结果。如在库存表中每个仓库的库存总量由产品数量相加所得,而库存总量又频繁被查询,则有必要将库存总量作为一个单独列加入到表中。1.3增加了实体代码的存储规范化规则要求独立存储外键,以表示1:M关联。如果外键表示代码,而用户需要同时得到代码以及相应的名称,则可以将相应列和代码存储在一起,减少一些连接操作。用户如订单报表的输出需要订单号、产品名、订单金额等字段,而订单号和产品名在不同表中,查询时需要对这两个表进行连接查询,为了提高查询速度,可以在订单表中增加产品名字段。1.4增加订单总量字段到订单总量表的连接储存派生数据可以减少表的连接操作,省去检索数据以计算派生数据的麻烦。如订单总量频繁要求被输出,而计算订单总量的两个数据订单数量和产品单价分别在订单表和产品表中,那么可以增加订单总量字段到订单总量表中,从而避免了两次连接操作。虽然范式的级别越高,关系模式越不会出现冗余和更新异常等不合理情况,但是关系模式的数目在不断的分解过程中将会不可避免的逐渐增加,必然会导致连接操作的增多,而连接操作是一个开销非常大的运算,过多的关系连接必然加重数据库系统的负担,影响系统效率,所以说模式的分解切不可一味追求其规范化程度而忽略了实际的使用方便性,在应用当中我们需要结合实际情况酌情考虑,尽可能使二者达到一个比较均衡的状态。2高效执行策略查询是数据库系统中使用最频繁、最基本的操作。对于一个给定的查询,通常会有许多种可能的执行策略,查询优化就是从众多策略中找出高效执行策略的处理过程,是DBMS实现的关键技术,对系统性能有很大影响。一些大规模的系统,数据量非常大,全表扫描无疑耗费大量时间,查询操作所基于的SELECT语句在SQL语句中是代价最大的语句,由程序员提交的SQL语句是系统优化的基础,如果能够采用比全表扫描更好的查询策略,则可以使查询时间大大降低,提高查询效率。2.1索引设置位置合理的使用索引可以加快数据检索速度,从而大大提高查询效率,其使用原则如下:1)在经常进行搜索的列上、主键列上以及经常进行连接但是没有指定为外键的列上建立索引。2)在频繁进行排序或分组(即进行groupby或orderby操作)的列上,以及经常需要根据范围进行搜索的列上建立索引。3)对于那些只有很少数据值的列不应该建立索引,如性别列。取值很少的列结果集的数据行占了表中数据行的很大比例,建立索引并不能提高查询效率。4)对于数据类型为text、image等这类数据量大取值通常很少的数据不必要建立索引。5)经常执行大量插入和删除的表不应当包含很多索引。6)避免在组合列上使用索引,大多数优化组件可在同一个表上使用多个索引,相比之下组合列上的索引不够灵活。2.2文复合文1)SQL当中尽量不采用性能比较低的IN操作符,强烈避免使用NOTIN操作符。相应的语句操作符可以由EXISTS或NOTEXISTS代替。2)ISNULL或ISNOTNULL操作会导致全盘扫描而放弃使用索引,如果并非严格要求为NULL值,在使用时应修改为和某一个缺省值进行比较。如:aisnotnull改为a>0或a>’’(空值)3)UNION操作符在进行表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果,在没有重复的记录的情况下可以使用只是简单的将两个结果合并后就返回的UNIONALL代替。4)避免使用SELECT*语句,尽量明确查询字段,不要返回用不到的任何字段。5)尽量避免在可以使用索引的列上使用函数,函数将排除使用索引的可能性。6)如果列上的数据类型与相关常量值不匹配,将发生隐式转换,如year=‘2011’条件将导致year列隐性转化为字符数据类型,从而使year列不能使用索引。2.3前后组合的连接WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响,如语句一:SELECTSnameWHEREItem=’彩电’ANDShop.S#=SC.S#语句二:SELECTSnameWHEREShop.S#=SC.S#ANDItem=’彩电’语句一先连接后做选择,执行时间需要152.5秒,而语句二先选择后做连接,执行时间需要7.5秒,所以在可以的情况下,先做选择后做连接,而不是先做连接后做选择。同样,可以最大程度过滤掉大数量记录的条件要写在where句子的末尾。如需要查询库存量大于1000的饮料产品,在实际数据中库存量大于1000的产品占到80%,而饮料产品占到产品总量的5%,所以应将饮料产品这一条件放在where句子的末尾。2.4基础表的形成在FROM后面列表的顺序会对SQL语句的执行性能产生影响,FROM最后面的表将会最先被处理,在有多个查询表的情况下,应该选择记录条数最少的表作为基础表。如果有3个以上的表需要连接查询,则选择交叉表作为基础表,以避免由于表的顺序不对而产生的十分耗服务器资源的数据交叉。3对于数据库的特征,要求变化的方案由此可见,在设计数据库时,最初的概念模型设计阶段要遵守第三范式,在具体数据库物理设计阶段要根据数据库使用实际情况,适当降低范式要求,增加冗余,在数据库工作效率和空间开销上