赵丽红的空间

我们一直在努力....

数据库设计规范(三)

标签: 数据库 数据库设计 数据库优化 索引

1.1.       数据库设计技巧

1.1.1.     分类拆分数据量大的表

对于经常使用的表(如某些参数表或代码对照表),由于其使用频率很高,要尽量减少表中的记录数量。例如,通过业务类别将数据拆分,则可以大大提高查询效率。

1.1.2.     索引设计

对于大的数据库表,合理的索引能够提高整个数据库的操作效率。
在索引设计中,索引字段应挑选重复值较少的字段;在对建有复合索引的字段进行检索时,应注意按照复合索引字段建立的顺序进行。
例如,如果对一个5万多条记录的订单表以日期和订单号为序建立复合索引,由于在该表中日期的重复值接近整个表的记录数,用订单号进行查询所用的时间接近3秒;而如果以订单号为索引字段建立索引进行相同的查询,所用时间不到1秒。因此在大型数据库设计中,只有进行合理的索引字段选择,才能有效提高整个数据库的操作效率。

1.1.3.     数据操作的优化

在大型数据库中,如何提高数据操作效率值得关注。例如,每在数据库流水表中增加一笔业务,就必须从流水控制表中取出流水号,并将其流水号的数值加一。正常情况下,单笔操作的反应速度尚属正常,但当用它进行批量业务处理时,速度会明显减慢。
经过分析发现,每次对流水控制表中的流水号数值加一时都要锁定该表,而该表却是整个系统操作的核心,有可能在操作时被其他进程锁定,因而使整个事务操作速度变慢。
对这一问题的解决的办法是,根据批量业务的总笔数批量申请流水号,并对流水控制表进行一次更新,即可提高批量业务处理的速度。
另一个例子是对插表的优化。对于大批量的业务处理,如果在插入数据库表时用普通的insert语句,速度会很慢。其原因在于,每次插表都要进行一次I/O操作,花费较长的时间。改进后,可以用put语句等缓冲区形式等满页后再进行I/O操作,从而提高效率。
对大的数据库表进行删除时,一般会直接用delete语句,这个语句虽然可以进行小表操作,但对大表却会因带来大事务而导致删除速度很慢甚至失败。解决的方法是去掉事务,但更有效的办法是先进行drop操作再进行重建。

1.1.4.     数据库参数的调整

数据库参数的调整是一个经验不断积累的过程,应由有经验的系统管理员完成。如记录锁的数目太少会造成锁表的失败;逻辑日志的文件数目太少会造成插入大表失败等,这些问题都应根据实际情况进行必要的调整。

1.1.5.     必要的工具

在整个数据库的开发与设计过程中,可以先开发一些小的应用工具,如自动生成库表的头文件、插入数据的初始化、数据插入的函数封装、错误跟踪或自动显示等,以此提高数据库的设计与开发效率。

1.1.6.     避免长事务

对单个大表的删除或插入操作会带来大事务,解决的办法是对参数进行调整,也可以在插入时对文件进行分割。对于一个由一系列小事务顺序操作共同构成的长事务,可以由一系列操作完成整个事务,但其缺点是有可能因整个事务太大而使不能完成,或者,由于偶然的意外而使事务重做所需的时间太长。较好的解决方法是,把整个事务分解成几个较小的事务,再由应用程序控制整个系统的流程。这样,如果其中某个事务不成功,则只需重做该事务,因而既可节约时间,又可避免长事务。

1.1.7.     避免或简化排序

应当尽量简化或避免对大型表进行重复的排序。当能够利用索引自动以适当的次序产生输出时,优化器就避免了排序这个步骤。为了避免不必要的排序,就要正确地增建索引,合理地合并数据库表(尽管有时可能影响表的规范化,但相对于效率的提高是值得的)。如果排序不可避免,那么应当试图简化它,如缩小排序的列的范围等。

1.1.8.     消除对大型表行数据的顺序存取

在嵌套查询中,表的顺序存取对查询效率可能产生致命的影响。
比如采用顺序存取策略,一个嵌套3层的查询,如果每层都查询1000行,那么这个查询就要查询10亿行数据。避免这种情况的主要方法就是对连接的列进行索引。例如,两个表:学生表(学号、姓名、年龄……)和选课表(学号、课程号、成绩)。如果两个表要做连接,就要在“学号”这个连接字段上建立索引。
我们有时可以使用并集来避免顺序存取。尽管也许在所有的检查列上都有索引,但某些形式的where子句会强迫优化器使用顺序存取,这一点也应注意。
下面的查询将强迫对orders表执行顺序操作: SELECT*FROMordersWHERE (customer_num=104 ANDorder_num>1001)ORorder_num=1008
虽然在customer_num和order_num上建有索引,但是在上面的语句中优化器还是使用顺序存取路径扫描整个表。因为这个语句要检索的是分离的行的集合,所以应该改为如下语句,就能利用索引路径处理查询。
SELECT*FROMordersWHEREcustomer_num=104 ANDorder_num>1001
UNION
SELECT*FROMordersWHEREorder_num=1008

1.1.9.     避免相关子查询

如果一个列同时在主查询和where子句中出现,很可能当主查询中的列值改变之后,子查询必须重新查询一次。而且查询嵌套层次越多,效率越低,因此应当尽量避免子查询。如果子查询不可避免,那么要在子查询中过滤掉尽可能多的行。
 

1.1.10.避免困难的正规表达式

MATCHES和LIKE关键字支持通配符匹配,技术上叫正规表达式。但这种匹配特别耗费时间。例如:SELECT*FROMcustomerWHEREzipcodeLIKE'98_ _ _'
即使在zipcode字段上建立了索引,在这种情况下也还是采用顺序扫描的方式。如果把语句改为SELECT*FROMcustomerWHEREzipcode>'98000',在执行查询时就会利用索引来查询,显然会大大提高速度。

1.1.11.使用临时表加速查询

 
把表的一个子集进行排序并创建临时表,有时能加速查询。它有助于避免多重排序操作,而且在其他方面还能简化优化器的工作。临时表中的行要比主表中的行少,而且物理顺序就是所要求的顺序,减少了磁盘i/o,所以查询工作量可以得到大幅减少。但要注意,临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下,注意不要丢失数据。

1.1.12.适当超前

计算机技术发展日新月异,数据库的设计必须具有一定前瞻性,不但要满足当前的应用要求,还要考虑未来的业务发展,同时必须有利于扩展或增加应用系统的处理功能。

    评分: 请先登录再投票,同一篇博客一月只能投票一次!
    无人投票

相关博客:


评论


发表评论

关注此文的人们还关注