论文部分内容阅读
摘要:后台数据库系统的性能优劣决定了软件系统的整体运行效率。为了提高系统的运行效率,需要对数据库系统进行必要的调整和优化。分析了和比较了不同方案对数据库性能的影响。提出了数据库优化的一般性的准则,并结合实际项目的测试数据证明之。
关键词:数据库设计;性能优化;T-SQL优化
中图分类号:TP311文献标识码:A 文章编号:1009-3044(2009)34-9837-02
Research of Optimizing Performance of SQL Server 2008
CHEN Su-rong, ZHU Xiao-hui
(College of Computer Science and Technology, Nantong University, Nantong 226019, China)
Abstract: The efficiency of the software depend on the performance of database. In order to improve the system performance, we should adjust and optimize the database. The article analyze the impact of different features for the database performance optimizing and propose general rules of performance optimizing for database and make several test on it.
Key words: database design; performance optimizing; T-SQL optimizing
对管理信息系统而言,对后台数据库数据的存取速度往往决定了整个系统的运行效率[1],尤其是对一个要处理上百万、上千万数据的大型数据库系统而言更是如此。本文从数据库设计、T-SQL编写、应用程序优化,及硬件配置等方面,分析了SQL Server 2008数据库性能优化问题,并给出优化的一般性准则。
1 数据库设计
数据库设计包括逻辑设计和物理设计两个部分[2]。数据库逻辑设计又包含业务需求和数据建模。优秀的数据库逻辑设计方案可以有效去除冗余数据、提高系统数据吞吐率、保证数据完整性,并能清楚地表达数据元素间的关系。数据库物理设计包括将逻辑设计映射到物理媒体,利用可用的硬件和软件功能来提高系统对数据库的访问速度。
1.1 逻辑设计
数据库设计过程包括:现实世界→需求分析→概念设计→逻辑设计→物理设计[5]。
概念设计是利用数据模型进行概念数据库的模式设计,一般用E-R图来进行描述。逻辑设计是把概念设计得到的概念数据库模式变为逻辑数据模式,常用到函数依赖、范式、关系分解等多种技术。好的逻辑设计可以为优化数据库性能打下良好的基础。在SQL Server 2008中进行逻辑设计时一般应遵循如下几个原则:
1) 所有逻辑表结构应尽量满足第一、第二、第三范式[5],从而有效减少数据冗余,提高查询性能并减少因数据冗余导致的数据不一致性错误。
2) 特殊情况下可以突破前三范式的规范化要求,采用空间换取时间的办法,利用适当的冗余数据来提高数据库的查询速度[6]。例如笔者在开发一个销售系统时,销售表中已有“销售单价”、“返利单价”、“销售数量”三个字段,通过这三个字段可以自动计算出“销售总额”及“返利总额”两个信息,但在进行逻辑设计时特意增加了“销售总额”、“返利总额”两个冗余字段,以方便进行高效率的汇总统计以及提升按销售总额或返利总额的排序效率。同时采用SQL2008中的触发器技术来根据前面三个字段的数据自动生成这“销售总额”和“返利总额”两个数据,保证了数据的一致性和完整性。
3) 充分利用SQL2008数据库的现有的规则功能来自动维护数据库的一致性和完整性。避免未满足完整性规则的“脏数据”进入数据库。常用的技术有:
a) 多使用Check约束,确保数据的有效性;b) 给字段设置Default和Not Null 约束,保证字段值的确定性;c) 建立Foreign Key约束,确保外键参照完整性[7];d) 建Primary Key约束,确保纪录唯一性。
4) 建立合理的索引,索引是数据库中重要的数据结构,对需要排序和查找的列上建立适当的索引,可以大大减少数据库对表进行物理读取的次数,提高运行效率。从另一方面来说,尽管索引可以快速获取数据,但它们也同时减慢数据的update和delete操作并需要更多的额外空间来存放索引表,因此,必须设计出合适的索引。一般来说建立索引应注意以下几点:
a) 对于查询中很少涉及的列或重复值比较多的列,不要建立索引;b) 数据量较小的表一般无需建立索引;c) 在经常进行连接,但没有指定为外键的列上建立索引;d) 经常出现在where子句、order by子句或group by子句后面的字段上建立索引。
对同时出现在where子句、order by子句或group by子句的多个字段上创建复合索引,并且复合索引的次序相同于子句中这些字段的排列次序。
1.2 物理设计
1) 把数据库事务日志文件和主文件存放到不同的物理磁盘,提高数据存取时I/O并发性[13]。
2) 当某些表中数据量特别大,而且访问非常频繁时,可以考虑采用将这些表放置在不同的物理磁盘上以提高读写时的磁盘的并发性。
3) 对数据库进行索引优化,利用索引可以避免表扫描,并减少因查询而造成的I/O开销,从而提高系统性能.可以用SQL Server2008中查询分析器来对索引进行优化和调整。
2 T-SQL编写
经过优化的T-SQL语句可以极大提高数据的存取效率,以下是常用的优化技巧。
1) 对于SELECT语句必须显式定义所有需要返回的列,避免使用星号。通过明确指定需要返回的数据列可以大大减少数据库返回的数据量,减少对磁盘的I/O操作,提高性能。
2) 在WHERE子句中避免使用导致表扫描的语句,比如: OR, <>, != , ! ,<, >, IS NULL, NOT,NOT IN, NOT LIKE 和LIKE等,因为这些操作很难利用已有索引[11]。
3) 避免使用NOT IN,可以采用IN,EXISTS,NOT EXISTS和LEFT JOIN 加空值判断。
4) 如果WHERE条件语句有多个AND条件,请确保至少有一个列有索引,如果没有,可以建立多列复合索引。
5) 尽可能避免在WHERE条件语句中使用函数计算。
对一些复杂的多个SQL语句,优先考虑使用存储过程,并避免在事务中进行赋值和复杂计算。存储过程是被预先编译好并被放在数据库内的,因此可以有效减少编译语句所花的时间。 同时,编译好的存储过程会进入缓存,所以对于经常执行的存储过程,除了第一次执行外,后续执行的速度会有明显提高。存储过程也可以更好的利用服务器内存,尤其对处理中间数据量不大的情况,存储过程中可以利用存放在内存的表变量来保存临时数据集。
3 应用程序优化
1) 应用程序应尽量避免在循环语句中编写对数据库存取的代码,应改用WHERE条件子句一次性从数据库中获取所有纪录的方式来替换。在分布式应用环境中,前台系统与后台数据库系统间一般采用断开式的连接请求的形式,即前台系统向数据库发出连接请求→数据库响应请求→前台系统发送SQL语句→后台数据库执行SQL语句→断开数据库连接→执行结束。因此,在循环中进行数据库存取将导致数据库频繁的进行建立连接、断开连接的操作而影响整个系统的性能。
2) 尽可能在应用程序中完成各种计算,从而减少在数据库级别的运行和操作。
3) 在应用程序中把需要进行多个SQL语句进行联合查询,并且中间会产生较大数据量的操作整合到存储过程中,这样可以大大减少网络的数据流量,提高系统运行效率[14]。
4 硬件配置
对于大型的数据库系统,数据库服务器的硬件配置对数据库系统的运行效率有着巨大的影响,硬件配置的升级往往能带来运行效率的大幅提升。
1) 选用高I/O性能的磁盘驱动器。数据库中的数据存放在物理磁盘上,数据库系统需要频繁对物理磁盘进行读取并向用户返回数据,因此磁盘的I/O性能对数据库的效率具有很大影响,尤其是在大量并发访问的情况下,影响尤其明显[6]。
2) 优先考虑使用多CPU或多核CPU的服务器系统。多个CPU或多核CPU可以并发执行多个数据库操作线程,因此可以大大提高数据库的并发能力。
3) 增加服务器内存,大容量的内存可以把整个数据库全部加载到内存中,从而大大减少对物理磁盘的I/O请求,因此可以大大加快对数据库的查询速度。笔者做过一个测试,在一台双核2.2GHz的CPU、1GB内存、7200转/分磁盘的机器上有一个产品数据库。共有50万个产品,执行一个分页存储过程获取最后20条产品纪录的时间是7秒。给机器增加了1G内存后,获取最后20条纪录的时间只有1秒。主要原因就是系统把整个产品表都缓存到了内存,查询速度大大加快。
5 结束语
总之,数据库性能的优化是一个整体工程,应考虑到方方面面。每一个小的调整,都可能会对系统性能产生很大影响。另外,影响关系数据库性能的因素错综复杂,因此优化策略必须根据实际情况进行不断调整和测试。以上列举了影响数据库系统性能的一些共性问题。在实际应用中,必须根据实际情况进行适当的折衷和平衡。
参考文献:
[1] 车争,夏巨谌,胡国安. 基于SQL Server 的数据库与应用程序的优化[J]. 计算机辅助工程,2002,(4):14-18.
[2] 刘云生. 现代数据库技术[M].北京:国防工业出版社,2001.
[3] Molina H G, Salem K. Main Memory Database Systems: An Overview[J]. IEEE Transactions on Knowledge and Data Engineering,1992, 4(6): 509-516.
[4] 李春葆,曾平. 数据库原理与应用[M]. 北京:清华大学出版社,2005.
[5] 萨师煊,王珊.数据库系统概论[M].3版.北京:高等教育出版社,2000.
[6] 钱文波,谢金宝.SQLSerVer数据库性能优化技术[J].微型机与应用,1999,18(3):7-9,22.
[7] 赵颖,沈金龙.基于SQLSerVer的应用程序优化[J].电子工程师,20(X),26(5):7-9.
[8] Scott W. Ambler ,Mapping Objects To Relational Databases[M] . AmbySoft2 Inc. ,Februrayr 1999.
[9] 尹大成,周津,朱明.电信网管数据模型三种数据库实现性能测试报告[P].中兴通讯技术报告.20031041.
[10] Johnny Olsson ,WM2data ,Allan R. Lassen ,Ramb ll. Experiences from Ob2ject2relational Programming in Oracle8,http:∥www.cit.dk/COT/reports/reports/Case4/062v1.4/cot2420621.4.pdf.
[11] 尹萍,SQL Server 数据库性能优化[J].计算机应用与软件,2005,(3):52-54.
[12] ROB P, CORONEL C. DataBase System Design, Realization and Management[M]. 北京:清华大学出版社,2005.
[13] 王宏志, 李建中, 骆吉洲, 等. 海量关系数据库的压缩存储与查询策略[EB/OL]. http://dev.csdn.net/develop/article/82/82593.shtm,2005.
[14] 车争,夏巨谌. 基于SQL Server 的数据库与应用程序的优化[J].计算机辅助工程, 2002,(4):14-18.
关键词:数据库设计;性能优化;T-SQL优化
中图分类号:TP311文献标识码:A 文章编号:1009-3044(2009)34-9837-02
Research of Optimizing Performance of SQL Server 2008
CHEN Su-rong, ZHU Xiao-hui
(College of Computer Science and Technology, Nantong University, Nantong 226019, China)
Abstract: The efficiency of the software depend on the performance of database. In order to improve the system performance, we should adjust and optimize the database. The article analyze the impact of different features for the database performance optimizing and propose general rules of performance optimizing for database and make several test on it.
Key words: database design; performance optimizing; T-SQL optimizing
对管理信息系统而言,对后台数据库数据的存取速度往往决定了整个系统的运行效率[1],尤其是对一个要处理上百万、上千万数据的大型数据库系统而言更是如此。本文从数据库设计、T-SQL编写、应用程序优化,及硬件配置等方面,分析了SQL Server 2008数据库性能优化问题,并给出优化的一般性准则。
1 数据库设计
数据库设计包括逻辑设计和物理设计两个部分[2]。数据库逻辑设计又包含业务需求和数据建模。优秀的数据库逻辑设计方案可以有效去除冗余数据、提高系统数据吞吐率、保证数据完整性,并能清楚地表达数据元素间的关系。数据库物理设计包括将逻辑设计映射到物理媒体,利用可用的硬件和软件功能来提高系统对数据库的访问速度。
1.1 逻辑设计
数据库设计过程包括:现实世界→需求分析→概念设计→逻辑设计→物理设计[5]。
概念设计是利用数据模型进行概念数据库的模式设计,一般用E-R图来进行描述。逻辑设计是把概念设计得到的概念数据库模式变为逻辑数据模式,常用到函数依赖、范式、关系分解等多种技术。好的逻辑设计可以为优化数据库性能打下良好的基础。在SQL Server 2008中进行逻辑设计时一般应遵循如下几个原则:
1) 所有逻辑表结构应尽量满足第一、第二、第三范式[5],从而有效减少数据冗余,提高查询性能并减少因数据冗余导致的数据不一致性错误。
2) 特殊情况下可以突破前三范式的规范化要求,采用空间换取时间的办法,利用适当的冗余数据来提高数据库的查询速度[6]。例如笔者在开发一个销售系统时,销售表中已有“销售单价”、“返利单价”、“销售数量”三个字段,通过这三个字段可以自动计算出“销售总额”及“返利总额”两个信息,但在进行逻辑设计时特意增加了“销售总额”、“返利总额”两个冗余字段,以方便进行高效率的汇总统计以及提升按销售总额或返利总额的排序效率。同时采用SQL2008中的触发器技术来根据前面三个字段的数据自动生成这“销售总额”和“返利总额”两个数据,保证了数据的一致性和完整性。
3) 充分利用SQL2008数据库的现有的规则功能来自动维护数据库的一致性和完整性。避免未满足完整性规则的“脏数据”进入数据库。常用的技术有:
a) 多使用Check约束,确保数据的有效性;b) 给字段设置Default和Not Null 约束,保证字段值的确定性;c) 建立Foreign Key约束,确保外键参照完整性[7];d) 建Primary Key约束,确保纪录唯一性。
4) 建立合理的索引,索引是数据库中重要的数据结构,对需要排序和查找的列上建立适当的索引,可以大大减少数据库对表进行物理读取的次数,提高运行效率。从另一方面来说,尽管索引可以快速获取数据,但它们也同时减慢数据的update和delete操作并需要更多的额外空间来存放索引表,因此,必须设计出合适的索引。一般来说建立索引应注意以下几点:
a) 对于查询中很少涉及的列或重复值比较多的列,不要建立索引;b) 数据量较小的表一般无需建立索引;c) 在经常进行连接,但没有指定为外键的列上建立索引;d) 经常出现在where子句、order by子句或group by子句后面的字段上建立索引。
对同时出现在where子句、order by子句或group by子句的多个字段上创建复合索引,并且复合索引的次序相同于子句中这些字段的排列次序。
1.2 物理设计
1) 把数据库事务日志文件和主文件存放到不同的物理磁盘,提高数据存取时I/O并发性[13]。
2) 当某些表中数据量特别大,而且访问非常频繁时,可以考虑采用将这些表放置在不同的物理磁盘上以提高读写时的磁盘的并发性。
3) 对数据库进行索引优化,利用索引可以避免表扫描,并减少因查询而造成的I/O开销,从而提高系统性能.可以用SQL Server2008中查询分析器来对索引进行优化和调整。
2 T-SQL编写
经过优化的T-SQL语句可以极大提高数据的存取效率,以下是常用的优化技巧。
1) 对于SELECT语句必须显式定义所有需要返回的列,避免使用星号。通过明确指定需要返回的数据列可以大大减少数据库返回的数据量,减少对磁盘的I/O操作,提高性能。
2) 在WHERE子句中避免使用导致表扫描的语句,比如: OR, <>, != , ! ,<, >, IS NULL, NOT,NOT IN, NOT LIKE 和LIKE等,因为这些操作很难利用已有索引[11]。
3) 避免使用NOT IN,可以采用IN,EXISTS,NOT EXISTS和LEFT JOIN 加空值判断。
4) 如果WHERE条件语句有多个AND条件,请确保至少有一个列有索引,如果没有,可以建立多列复合索引。
5) 尽可能避免在WHERE条件语句中使用函数计算。
对一些复杂的多个SQL语句,优先考虑使用存储过程,并避免在事务中进行赋值和复杂计算。存储过程是被预先编译好并被放在数据库内的,因此可以有效减少编译语句所花的时间。 同时,编译好的存储过程会进入缓存,所以对于经常执行的存储过程,除了第一次执行外,后续执行的速度会有明显提高。存储过程也可以更好的利用服务器内存,尤其对处理中间数据量不大的情况,存储过程中可以利用存放在内存的表变量来保存临时数据集。
3 应用程序优化
1) 应用程序应尽量避免在循环语句中编写对数据库存取的代码,应改用WHERE条件子句一次性从数据库中获取所有纪录的方式来替换。在分布式应用环境中,前台系统与后台数据库系统间一般采用断开式的连接请求的形式,即前台系统向数据库发出连接请求→数据库响应请求→前台系统发送SQL语句→后台数据库执行SQL语句→断开数据库连接→执行结束。因此,在循环中进行数据库存取将导致数据库频繁的进行建立连接、断开连接的操作而影响整个系统的性能。
2) 尽可能在应用程序中完成各种计算,从而减少在数据库级别的运行和操作。
3) 在应用程序中把需要进行多个SQL语句进行联合查询,并且中间会产生较大数据量的操作整合到存储过程中,这样可以大大减少网络的数据流量,提高系统运行效率[14]。
4 硬件配置
对于大型的数据库系统,数据库服务器的硬件配置对数据库系统的运行效率有着巨大的影响,硬件配置的升级往往能带来运行效率的大幅提升。
1) 选用高I/O性能的磁盘驱动器。数据库中的数据存放在物理磁盘上,数据库系统需要频繁对物理磁盘进行读取并向用户返回数据,因此磁盘的I/O性能对数据库的效率具有很大影响,尤其是在大量并发访问的情况下,影响尤其明显[6]。
2) 优先考虑使用多CPU或多核CPU的服务器系统。多个CPU或多核CPU可以并发执行多个数据库操作线程,因此可以大大提高数据库的并发能力。
3) 增加服务器内存,大容量的内存可以把整个数据库全部加载到内存中,从而大大减少对物理磁盘的I/O请求,因此可以大大加快对数据库的查询速度。笔者做过一个测试,在一台双核2.2GHz的CPU、1GB内存、7200转/分磁盘的机器上有一个产品数据库。共有50万个产品,执行一个分页存储过程获取最后20条产品纪录的时间是7秒。给机器增加了1G内存后,获取最后20条纪录的时间只有1秒。主要原因就是系统把整个产品表都缓存到了内存,查询速度大大加快。
5 结束语
总之,数据库性能的优化是一个整体工程,应考虑到方方面面。每一个小的调整,都可能会对系统性能产生很大影响。另外,影响关系数据库性能的因素错综复杂,因此优化策略必须根据实际情况进行不断调整和测试。以上列举了影响数据库系统性能的一些共性问题。在实际应用中,必须根据实际情况进行适当的折衷和平衡。
参考文献:
[1] 车争,夏巨谌,胡国安. 基于SQL Server 的数据库与应用程序的优化[J]. 计算机辅助工程,2002,(4):14-18.
[2] 刘云生. 现代数据库技术[M].北京:国防工业出版社,2001.
[3] Molina H G, Salem K. Main Memory Database Systems: An Overview[J]. IEEE Transactions on Knowledge and Data Engineering,1992, 4(6): 509-516.
[4] 李春葆,曾平. 数据库原理与应用[M]. 北京:清华大学出版社,2005.
[5] 萨师煊,王珊.数据库系统概论[M].3版.北京:高等教育出版社,2000.
[6] 钱文波,谢金宝.SQLSerVer数据库性能优化技术[J].微型机与应用,1999,18(3):7-9,22.
[7] 赵颖,沈金龙.基于SQLSerVer的应用程序优化[J].电子工程师,20(X),26(5):7-9.
[8] Scott W. Ambler ,Mapping Objects To Relational Databases[M] . AmbySoft2 Inc. ,Februrayr 1999.
[9] 尹大成,周津,朱明.电信网管数据模型三种数据库实现性能测试报告[P].中兴通讯技术报告.20031041.
[10] Johnny Olsson ,WM2data ,Allan R. Lassen ,Ramb ll. Experiences from Ob2ject2relational Programming in Oracle8,http:∥www.cit.dk/COT/reports/reports/Case4/062v1.4/cot2420621.4.pdf.
[11] 尹萍,SQL Server 数据库性能优化[J].计算机应用与软件,2005,(3):52-54.
[12] ROB P, CORONEL C. DataBase System Design, Realization and Management[M]. 北京:清华大学出版社,2005.
[13] 王宏志, 李建中, 骆吉洲, 等. 海量关系数据库的压缩存储与查询策略[EB/OL]. http://dev.csdn.net/develop/article/82/82593.shtm,2005.
[14] 车争,夏巨谌. 基于SQL Server 的数据库与应用程序的优化[J].计算机辅助工程, 2002,(4):14-18.