论文部分内容阅读
[摘要]从数据库设计人员的角度出发讨论SQL Server数据库性能优化的问题，非常详细地从数据库的逻辑设计到物理设计，再到数据库查询三个层次详细的描述数据库设计中性能优化的实现。
[关键词]数据库设计 SQL Server 优化
中图分类号：TP3文献标识码：A文章编号：1671－7597（2009）0120079－01
数据库系统是企业整个管理信息系统的核心和基础，它的任务就是把系统中大量的数据按一定模型组织起来，以便及时、准确地提供给用户使用。
设计一个数据库应用系统似乎并不难，但是要想使系统达到最优化的性能并不是一件容易的事。在开发工具、数据库设计、应用程序的结构、查询设计、接口选择等方面有多种选择。总体来看，要提高数据库的应用系统运行效率，必须从数据库系统级优化、数据库设计级优化、程序实现级优化这三个层次上同时下工夫。本文主要以SQL Server为例，从后台数据库的设计角度讨论数据库应用程序性能优化技巧。数据库性能的优化主要从以下几个方面入手。
一、逻辑数据库设计的规范化问题
合理运用规范化设计逻辑数据库。规范化是数据库设计的基本指导原则。我们知道，从最基本的1NF到2NF、3NF是从不同的层次描述了关系中存在的函数依赖关系，那么我们在设计数据库的时候范式的级别到底怎么来把握呢？范式级别越高逻辑数据库就越好吗？下面我们针对这两个问题进行论述。
数据库的目的是进行数据处理的，它是为应用系统服务的。一个应用系统是为了解决现实中生产、生活需要的，所以，我们首先必须明白的一个问题就是数据库的设计必须紧密围绕实际的应用需求，应用需求包括功能需求，性能需求，甚至用户的操作需求。功能需求解决的是系统需要那些数据；而性能需求和用户的操作需求涉及的就是这些数据如何科学、合理的组织的问题。所以数据库的逻辑设计必须要解决好这两个问题。由于我们讨论的是数据库逻辑设计的优化问题，那么后者就是我们要重点讨论的。
数据库系统性能要求是什么是决定数据库设计规范化层次的一个重要依据。系统的需求分析阶段我们必须仔细分析系统的性能要求。特别是数据库操作性能要求。
另外，数据库的逻辑设计必须考虑到用户的操作要求。也就是业务处理的流程要求和程序界面设计要求。我们知道，数据库的设计是软件系统需求分析完成以后进行的，而程序界面设计是在数据库设计之后完成的。那么数据库的设计过程必须在充分基于需求分析的基础上、同时充分考虑后续程序界面的设计来完成。如果完全脱离用户的操作要求而从纯粹追求高级别规范化角度去设计数据库是不合理的。这就是“功能第一、性能第二”的原则。一般来讲对于事务型数据库来说满足3NF的关系既能满足用户的应用需求，也能满足规范化普遍要求。但是针对具体的关系还得进一步具体分析。
二、物理数据库生成策略
数据库优化的目标无非是避免磁盘I/O瓶颈、减少CPU利用率和减少资源竞争，提高数据库的读写效率。基于硬件的物理数据库的性能自然离不开高性能的硬件支持，特别是需要大容量内存和一个好的磁盘I/O子系统，智能型SCSI-2磁盘控制器或磁盘组控制器是不错的选择。但是从软件(DBMS)角度，SQL SERVER数据库的实施过程中以下准则是我们必须遵循的：
1．与每个表列相关的数据类型应该反映数据所需的最小存储空间，特别是对于被索引的列更是如此。比如能使用smallint类型就不要用integer类型，这样索引字段可以被更快地读取，而且可以在1个数据页上放置更多的数据行，因而也就减少了I/O操作。
2．尽可能地把数据库的所有基本表分布到多个不同的磁盘系统。尤其是系统采用了多个智能型磁盘控制器和数据分离技术的情况下，这样做可以明显的提高数据的操作性能。
3．把一个频繁使用的大表分割开，并放在2个单独的智能型磁盘控制器的数据库设备上，这样也可以提高性能。因为有多个磁头在查找，所以数据分离也能提高性能。
4．把文本或图像列的数据存放在1个单独的物理设备上可以提高性能。1个专用的智能型的控制器能进一步提高性能。
5．合理定义主键与外键，合理定义索引。在基本表设计中，表的主键、外键、索引设计占有非常重要的地位，但系统设计人员往往只注重于满足用户要求，而没有从系统优化的高度来认识和重视它们。实际上，它们与系统的运行性能密切相关。现在从系统数据库优化角度讨论主键和索引的问题：
（1）主键(Primary Key)：主键被用于复杂的SQL语句时，频繁地在数据访问中被用到。一个表只有一个主键。设计主键时短主键最佳（小于25bytes），主键的长短影响索引的大小，索引的大小影响索引页的大小，从而影响磁盘I/O。如果使用复合主键，要求主键列不能太多，复合主键使得Join操作复杂化、也增加了外键表的大小。
（2）索引(Index)：利用索引优化系统性能是显而易见的，索引的主要优点是对所有常用于查询中的Where子句的列和所有用于排序的列创建索引，可以避免整表扫描或访问，在不改变表的物理结构的情况下，直接访问特定的数据列，这样减少数据存取时间；利用索引可以优化或排除耗时的分类操作。索引码越小，定位就越直接，索引性能越好。在进行Insert、Delete和Update操作时，必须对索引进行维护，所以定期更新索引非常必要。当然索引也要付出代价，一是增加存储空间开销，二是建立索引也要花费时间。所以一个基本表建立索引的多少、索引码的选择（除聚集索引外）就非常关键。
三、查询优化技巧讨论
查询是数据库的一个最为重要的操作，查询的优化对整个数据库系统的性能来讲最为关键。一个性能优良的数据库系统必然在查询优化方面做得很优秀。
Microsoft SQL Server数据库内核用1个基于费用的查询优化器自动优化向SQL提交的数据查询操作。数据操作查询是指支持SQL关键字WHERE或HAVING的查询，如Selece、Delete和Update。基于费用的查询优化器根据统计信息产生子句的费用估算。
了解优化器数据处理过程的简单方法是检测ShowPlan命令的输出结果。如果用基于字符的工具（例如ISQL），可以通过键入Show ShowPlan ON来得到SHOWPLAN命令的输出。如果使用图形化查询，比如SQL Enterprise Manager中的查询工具或LSQL/W，可以设定配置选项来提供这一信息。
SQL Server的优化通过3个阶段完成：查询分析、索引选择、合并选择。
（一）查询分析
在查询分析阶段，SQL Server优化器查看每一个由正规查询树代表的子句，并判断它是否能被优化。SQL Server一般会尽量优化那些限制扫描的子句。如含有SQL不等关系符“<>”的子句。因为“<>”是1个排斥性的操作符，而不是1个包括性的操作符，所在扫描整个表之前无法确定子句的选择范围会有多大。当1个关系型查询中含有不可优化的子句时，执行计划用表扫描来访问查询的这个部分，对于查询树中可优化的SQL Server子句，则由优化器执行索引选择。
（二）索引选择
对于每个可优化的子句，优化器都查看数据库系统表，以确定是否有相关的索引能用于访问数据。只有当索引中的列的1个前缀与查询子句中的列完全匹配时，这个索引才被认为是有用的。因为索引是根据列的顺序构造的，所以要求匹配是精确的匹配。对于分簇索引，原来的数据也是根据索引列顺序排序的。想用索引的次要列访问数据，就像想在电话本中查找所有姓为某个姓氏的条目一样，排序基本上没有什么用，因为你还是得查看每一行以确定它是否符合条件。如果1个子句有可用的索引，那么优化器就会为它确定选择性。
（三）合并选择
当索引选择结束，并且所有的子句都有了一个基于它们的访问计划的处理费用时，优化器开始执行合并选择。合并选择被用来找出一个用于合并子句访问计划的有效顺序。为了做到这一点，优化器比较子句的不同排序，然后选出从物理磁盘I/O的角度看处理费用最低的合并计划。因为子句组合的数量会随着查询的复杂度极快地增长，SQL Server查询优化器使用树剪枝技术来尽量减少这些比较所带来的开支。当这个合并选择阶段结束时，SQL Server查询优化器已经生成了1个基于费用的查询执行计划，这个计划充分利用了可用的索引，并以最小的系统开支和良好的执行性能访问原来的数据。
从以上查询优化的3个阶段不难看出，设计出物理I/O和逻辑I/O最少的方案并掌握好处理器时间和I/O时间的平衡，是高效查询设计的主要目标。也就是说，希望设计出这样的查询：充分利用索引、磁盘读写最少、最高效地利用了内存和CPU资源。
经验告诉我们，在优化查询时，必须做到：
1．尽可能少的记录行；
2．避免排序或尽可能少的排序，若要做大量数据排序，最好将相关数据放在临时表中操作；用简单的键（列）排序，如整型或短字符串排序；
3．避免表内的相关子查询，多表查询尽可能使用连接查询；
4．避免在Where子句中使用复杂的表达式或非起始的子字符串、用长字符串连接；
5．在Where子句中多使用“与”(And)连接，少使用“或”(Or)连接；
6．利用临时数据库。在查询多表、有多个连接、查询复杂、数据要过滤时，可以建临时表（索引）以减少I/O，但缺点是增加了空间开销；
7．除非每个列都有索引支持，否则在有连接的查询时分别找出两个动态索引，放在工作表中重新排序。
参考文献：
[1]郑阿奇，《SQL Server实用教程》.
[2]王珊、萨师瑄，《数据库系统概论》.
作者简介：
梁铭，讲师，十堰职业技术学院计算机工程系，主研领域：数据库应用技术。
[关键词]数据库设计 SQL Server 优化
中图分类号：TP3文献标识码：A文章编号：1671－7597（2009）0120079－01
数据库系统是企业整个管理信息系统的核心和基础，它的任务就是把系统中大量的数据按一定模型组织起来，以便及时、准确地提供给用户使用。
设计一个数据库应用系统似乎并不难，但是要想使系统达到最优化的性能并不是一件容易的事。在开发工具、数据库设计、应用程序的结构、查询设计、接口选择等方面有多种选择。总体来看，要提高数据库的应用系统运行效率，必须从数据库系统级优化、数据库设计级优化、程序实现级优化这三个层次上同时下工夫。本文主要以SQL Server为例，从后台数据库的设计角度讨论数据库应用程序性能优化技巧。数据库性能的优化主要从以下几个方面入手。
一、逻辑数据库设计的规范化问题
合理运用规范化设计逻辑数据库。规范化是数据库设计的基本指导原则。我们知道，从最基本的1NF到2NF、3NF是从不同的层次描述了关系中存在的函数依赖关系，那么我们在设计数据库的时候范式的级别到底怎么来把握呢？范式级别越高逻辑数据库就越好吗？下面我们针对这两个问题进行论述。
数据库的目的是进行数据处理的，它是为应用系统服务的。一个应用系统是为了解决现实中生产、生活需要的，所以，我们首先必须明白的一个问题就是数据库的设计必须紧密围绕实际的应用需求，应用需求包括功能需求，性能需求，甚至用户的操作需求。功能需求解决的是系统需要那些数据；而性能需求和用户的操作需求涉及的就是这些数据如何科学、合理的组织的问题。所以数据库的逻辑设计必须要解决好这两个问题。由于我们讨论的是数据库逻辑设计的优化问题，那么后者就是我们要重点讨论的。
数据库系统性能要求是什么是决定数据库设计规范化层次的一个重要依据。系统的需求分析阶段我们必须仔细分析系统的性能要求。特别是数据库操作性能要求。
另外，数据库的逻辑设计必须考虑到用户的操作要求。也就是业务处理的流程要求和程序界面设计要求。我们知道，数据库的设计是软件系统需求分析完成以后进行的，而程序界面设计是在数据库设计之后完成的。那么数据库的设计过程必须在充分基于需求分析的基础上、同时充分考虑后续程序界面的设计来完成。如果完全脱离用户的操作要求而从纯粹追求高级别规范化角度去设计数据库是不合理的。这就是“功能第一、性能第二”的原则。一般来讲对于事务型数据库来说满足3NF的关系既能满足用户的应用需求，也能满足规范化普遍要求。但是针对具体的关系还得进一步具体分析。
二、物理数据库生成策略
数据库优化的目标无非是避免磁盘I/O瓶颈、减少CPU利用率和减少资源竞争，提高数据库的读写效率。基于硬件的物理数据库的性能自然离不开高性能的硬件支持，特别是需要大容量内存和一个好的磁盘I/O子系统，智能型SCSI-2磁盘控制器或磁盘组控制器是不错的选择。但是从软件(DBMS)角度，SQL SERVER数据库的实施过程中以下准则是我们必须遵循的：
1．与每个表列相关的数据类型应该反映数据所需的最小存储空间，特别是对于被索引的列更是如此。比如能使用smallint类型就不要用integer类型，这样索引字段可以被更快地读取，而且可以在1个数据页上放置更多的数据行，因而也就减少了I/O操作。
2．尽可能地把数据库的所有基本表分布到多个不同的磁盘系统。尤其是系统采用了多个智能型磁盘控制器和数据分离技术的情况下，这样做可以明显的提高数据的操作性能。
3．把一个频繁使用的大表分割开，并放在2个单独的智能型磁盘控制器的数据库设备上，这样也可以提高性能。因为有多个磁头在查找，所以数据分离也能提高性能。
4．把文本或图像列的数据存放在1个单独的物理设备上可以提高性能。1个专用的智能型的控制器能进一步提高性能。
5．合理定义主键与外键，合理定义索引。在基本表设计中，表的主键、外键、索引设计占有非常重要的地位，但系统设计人员往往只注重于满足用户要求，而没有从系统优化的高度来认识和重视它们。实际上，它们与系统的运行性能密切相关。现在从系统数据库优化角度讨论主键和索引的问题：
（1）主键(Primary Key)：主键被用于复杂的SQL语句时，频繁地在数据访问中被用到。一个表只有一个主键。设计主键时短主键最佳（小于25bytes），主键的长短影响索引的大小，索引的大小影响索引页的大小，从而影响磁盘I/O。如果使用复合主键，要求主键列不能太多，复合主键使得Join操作复杂化、也增加了外键表的大小。
（2）索引(Index)：利用索引优化系统性能是显而易见的，索引的主要优点是对所有常用于查询中的Where子句的列和所有用于排序的列创建索引，可以避免整表扫描或访问，在不改变表的物理结构的情况下，直接访问特定的数据列，这样减少数据存取时间；利用索引可以优化或排除耗时的分类操作。索引码越小，定位就越直接，索引性能越好。在进行Insert、Delete和Update操作时，必须对索引进行维护，所以定期更新索引非常必要。当然索引也要付出代价，一是增加存储空间开销，二是建立索引也要花费时间。所以一个基本表建立索引的多少、索引码的选择（除聚集索引外）就非常关键。
三、查询优化技巧讨论
查询是数据库的一个最为重要的操作，查询的优化对整个数据库系统的性能来讲最为关键。一个性能优良的数据库系统必然在查询优化方面做得很优秀。
Microsoft SQL Server数据库内核用1个基于费用的查询优化器自动优化向SQL提交的数据查询操作。数据操作查询是指支持SQL关键字WHERE或HAVING的查询，如Selece、Delete和Update。基于费用的查询优化器根据统计信息产生子句的费用估算。
了解优化器数据处理过程的简单方法是检测ShowPlan命令的输出结果。如果用基于字符的工具（例如ISQL），可以通过键入Show ShowPlan ON来得到SHOWPLAN命令的输出。如果使用图形化查询，比如SQL Enterprise Manager中的查询工具或LSQL/W，可以设定配置选项来提供这一信息。
SQL Server的优化通过3个阶段完成：查询分析、索引选择、合并选择。
（一）查询分析
在查询分析阶段，SQL Server优化器查看每一个由正规查询树代表的子句，并判断它是否能被优化。SQL Server一般会尽量优化那些限制扫描的子句。如含有SQL不等关系符“<>”的子句。因为“<>”是1个排斥性的操作符，而不是1个包括性的操作符，所在扫描整个表之前无法确定子句的选择范围会有多大。当1个关系型查询中含有不可优化的子句时，执行计划用表扫描来访问查询的这个部分，对于查询树中可优化的SQL Server子句，则由优化器执行索引选择。
（二）索引选择
对于每个可优化的子句，优化器都查看数据库系统表，以确定是否有相关的索引能用于访问数据。只有当索引中的列的1个前缀与查询子句中的列完全匹配时，这个索引才被认为是有用的。因为索引是根据列的顺序构造的，所以要求匹配是精确的匹配。对于分簇索引，原来的数据也是根据索引列顺序排序的。想用索引的次要列访问数据，就像想在电话本中查找所有姓为某个姓氏的条目一样，排序基本上没有什么用，因为你还是得查看每一行以确定它是否符合条件。如果1个子句有可用的索引，那么优化器就会为它确定选择性。
（三）合并选择
当索引选择结束，并且所有的子句都有了一个基于它们的访问计划的处理费用时，优化器开始执行合并选择。合并选择被用来找出一个用于合并子句访问计划的有效顺序。为了做到这一点，优化器比较子句的不同排序，然后选出从物理磁盘I/O的角度看处理费用最低的合并计划。因为子句组合的数量会随着查询的复杂度极快地增长，SQL Server查询优化器使用树剪枝技术来尽量减少这些比较所带来的开支。当这个合并选择阶段结束时，SQL Server查询优化器已经生成了1个基于费用的查询执行计划，这个计划充分利用了可用的索引，并以最小的系统开支和良好的执行性能访问原来的数据。
从以上查询优化的3个阶段不难看出，设计出物理I/O和逻辑I/O最少的方案并掌握好处理器时间和I/O时间的平衡，是高效查询设计的主要目标。也就是说，希望设计出这样的查询：充分利用索引、磁盘读写最少、最高效地利用了内存和CPU资源。
经验告诉我们，在优化查询时，必须做到：
1．尽可能少的记录行；
2．避免排序或尽可能少的排序，若要做大量数据排序，最好将相关数据放在临时表中操作；用简单的键（列）排序，如整型或短字符串排序；
3．避免表内的相关子查询，多表查询尽可能使用连接查询；
4．避免在Where子句中使用复杂的表达式或非起始的子字符串、用长字符串连接；
5．在Where子句中多使用“与”(And)连接，少使用“或”(Or)连接；
6．利用临时数据库。在查询多表、有多个连接、查询复杂、数据要过滤时，可以建临时表（索引）以减少I/O，但缺点是增加了空间开销；
7．除非每个列都有索引支持，否则在有连接的查询时分别找出两个动态索引，放在工作表中重新排序。
参考文献：
[1]郑阿奇，《SQL Server实用教程》.
[2]王珊、萨师瑄，《数据库系统概论》.
作者简介：
梁铭，讲师，十堰职业技术学院计算机工程系，主研领域：数据库应用技术。