SQL数据库建表前期优化

关 于数据库优化方面的文章很多，但是有的写的似是而非，有的不切实际，对一个数据库来说，只能做到更优，不可能最优，并且由于实际需求不同，优化方案还是有 所差异，根据实际需要关心的方面（速度、存储空间、可维护性、可拓展性）来优化数据库，而这些方面往往又是相互矛盾的，下面结合网上的一些看法和自己的一 些观点做个总结。

　　一个系统的性能的提高，不单单是试运行或者维护阶段的性能调优，也不单单是开发阶段的事情，而是在整个软件生命周期都需要注意。所以我希望按照软件生命周期的不同阶段来总结数据库性能优化相关的注意事项。

　　一、 分析阶段

　　一般来说，在系统分析阶段往往有太多需要关注的地方，系统各种功能性、可用性、可靠性、安全性需求往往吸引了我们大部分的注意力，但是，我们必须注意，性能是很重要的非功能性需求，必须根据系统的特点确定其实时性需求、响应时间的需求、硬件的配置等。最好能有各种需求的量化的指标。

　　另一方面，在分析阶段应该根据各种需求区分出系统的类型，大的方面，区分是OLTP(联机事务处理系统)和OLAP(联机分析处理系统)。

　　二、 设计阶段

　　设计阶段可以说是以后系统性能的关键阶段，在这个阶段，有一个关系到以后几乎所有性能调优的过程—数据库设计。

　　在数据库设计完成后，可以进行初步的索引设计，好的索引设计可以指导编码阶段写出高效率的代码，为整个系统的性能打下良好的基础。

　　以下是性能要求设计阶段需要注意的：

　　1、数据库逻辑设计的规范化

　　数据库逻辑设计的规范化就是我们一般所说的范式，我们可以这样来简单理解范式：

　　第1规范：没有重复的组或多值的列，这是数据库设计的最低要求。

第2规范: 每个非关键字段必须依赖于主关键字，不能依赖于一个组合式主关键字的某些组成部分。消除部分依赖，大部分情况下，数据库设计都应该达到第二范式。

　　第3规范: 一个非关键字段不能依赖于另一个非关键字段。消除传递依赖，达到第三范式应该是系统中大部分表的要求，除非一些特殊作用的表。

　　更高的范式要求这里就不再作介绍了，个人认为，如果全部达到第二范式，大部分达到第三范式，系统会产生较少的列和较多的表，因而减少了数据冗余，也利于性能的提高。

　　2、合理的冗余

　　完全按照规范化设计的系统几乎是不可能的，除非系统特别的小，在规范化设计后，有计划地加入冗余是必要的。

　　冗余可以是冗余数据库、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作用。

　　冗余可以是为了编程方便而增加，也可以是为了性能的提高而增加。从性能角度来说，冗余数据库可以分散数据库压力，冗余表可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。

　　3、主键的设计

　　主键是必要的，SQL SERVER的主键同时是一个唯一索引，而且在实际应用中，我们往往选择最小的键组合作为主键，所以主键往往适合作为表的聚集索引。聚集索引对查询的影响是比较大的，这个在下面索引的叙述。

　　在有多个键的表，主键的选择也比较重要，一般选择总的长度小的键，小的键的比较速度快，同时小的键可以使主键的B树结构的层次更少。

　　主键的选择还要注意组合主键的字段次序，对于组合主键来说，不同的字段次序的主键的性能差别可能会很大，一般应该选择重复率低、单独或者组合查询可能性大的字段放在前面。

　　4、外键的设计

外键作为数据库对象，很多人认为麻烦而不用，实际上，外键在大部分情况下是很有用的，理由是：

　　外键是最高效的一致性维护方法，数据库的一致性要求，依次可以用外键、CHECK约束、规则约束、触发器、客户端程序，一般认为，离数据越近的方法效率越高。

　　谨慎使用级联删除和级联更新，级联删除和级联更新作为SQL SERVER 2000当年的新功能，在2005作 了保留，应该有其可用之处。我这里说的谨慎，是因为级联删除和级联更新有些突破了传统的关于外键的定义，功能有点太过强大，使用前必须确定自己已经把握好 其功能范围，否则，级联删除和级联更新可能让你的数据莫名其妙的被修改或者丢失。从性能看级联删除和级联更新是比其他方法更高效的方法。

　　5、字段的设计

　　字段是数据库最基本的单位，其设计对性能的影响是很大的。需要注意如下：

　　A、数据类型尽量用数字型，数字型的比较比字符型的快很多。

　　B、数据类型尽量小，这里的尽量小是指在满足可以预见的未来需求的前提下的。

　　C、 尽量不要允许NULL，除非必要，可以用NOT NULL+DEFAULT代替。

　　D、少用TEXT和IMAGE，二进制字段的读写是比较慢的，而且，读取的方法也不多，大部分情况下最好不用。

　　E、自增字段要慎用，不利于数据迁移。

　　6、数据库物理存储和环境的设计

　　在设计阶段，可以对数据库的物理存储、操作系统环境、网络环境进行必要的设计，使得我们的系统在将来能适应比较多的用户并发和比较大的数据量。

　　这里需要注意文件组的作用，适用文件组可以有效把I/O操作分散到不同的物理硬盘，提高并发能力。

　　7、系统设计

　　整个系统的设计特别是系统结构设计对性能是有很大影响的，对于一般的OLTP系统，可以选择C/S结构、三层的C/S结构等，不同的系统结构其性能的关键也有所不同。

系统设计阶段应该归纳一些业务逻辑放在数据库编程实现，数据库编程包括数据库存储过程、触发器和函数。用数据库编程实现业务逻辑的好处是减少网络流量并可更充分利用数据库的预编译和缓存功能。

　　8、索引的设计

　　在设计阶段，可以根据功能和性能的需求进行初步的索引设计，这里需要根据预计的数据量和查询来设计索引，可能与将来实际使用的时候会有所区别。

　　关于索引的选择，应改主意：

　　A、根据数据量决定哪些表需要增加索引，数据量小的可以只有主键。

　　B、根据使用频率决定哪些字段需要建立索引，选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候选字段。

　　C、把经常一起出现的字段组合在一起，组成组合索引，组合索引的字段顺序与主键一样，也需要把最常用的字段放在前面，把重复率低的字段放在前面。

　　D、一个表不要加太多索引，因为索引影响插入和更新的速度。

-------------------------------------------------------------------------------------------------------------------

刚开始用SQL Server的时候，我没有用显示执行计划来对查询进行分析。 我曾经一直认为我递交的SQL查询都是最优的，而忽略了查询性能究竟如何，从而对“执行计划”重视不够。在我职业初期，我只要能获取数据就很开心，而不去 考虑数据是如何返回的，“执行计划”对我的查询作了什么工作。我以为SQL Server会自己去处理查询的性能问题的。作为一个刚进入IT行业或者刚学到新技术的软件工程师，在编写代码前不太可能有时间去学习其实必须掌握的知 识。也许这是因为IT行业竞争太激烈的缘故。

随着时间的流逝，数据库容量慢慢变大了。终于某天，客户对应用系统的查询性能感到不满意了。他面带怒容来找我，抱怨 由于查询太慢，使得他需要花更多的时间来处理公务。最初，我建议客户升级其系统资源，例如作为临时解决方案，增加硬盘容量。虽然硬盘价格现在很便宜了，但 是客户还是要求我提供一个永久性的解决方案，检查和好好调试查询语句，来替代那种无休止地升级资源的临时方案。因为客户的满意度对IT行业来说是十分重要 的，因此我不得不考虑他的个人建议。我答应他一定会检查和调整我的代码。

如何入手呢?

在刚进入IT行业时，我知道SQL Server的基础只是。说实话，向客户承诺检查系统的时候，我还没有一点入手的头绪。不过我相信我可以通过GOOGL和BOL来获取相应的信息。

我阅读了一些关于SQL Server的书籍，BOL，以及在网上搜索的信息。于是我知道了“显示执行计划”的概念。可以在查询管理器中将该选项的开关设置为ON。“显示执行计划”是一个图形化工具，可以帮助开发者和DBA分析，优化查询，从而改善性能。

“显示执行计划”中不同的任务具有不同的图标。本文中我主要对“Table Scan”、“Index Scan”、“Index Seek”、“Cluster Index Scan”以及“Clustered Index Seek”感兴趣。也许在以后，可以对别的任务进行另外介绍。

时间以F1方程式的速度开始流逝，我觉得该是我全面理解“Table Scan”、“Index Scan”、“Index Seek”、“Clustered Index Scan”、和“Clustered Index Seek”如何工作的时候了。

我准备开始分析并优化我的查询。在分析之前，我想到了一些问题。

 

• MS-SQL Server什么时候使用"Table Scan"?
• MS-SQL Server什么时候使用"Index Scan"?
• MS-SQL Server什么时候使用"Index Seek"?
• MS-SQL Server什么时候使用"Clustered Index Scan"?
• MS-SQL Server什么时候使用"Clustered Index Seek"?

我主要关注SQL Server是根据什么来使用“执行计划”分析查询的。在经过一段时间学习后，我了解了一些相关知识。这些知识应该对开发和DBA新手有帮助。于是我决定写这篇文章，共享我的知识以帮助别人来理解“执行计划”。

如果你喜欢，可以慢慢读完，也可以在SQL Server上，模拟我下面做的实验。

开始入手

为了解释“显示执行计划”中的“Table Scan”、“Index Scan”、“Index Seek”、“Clustered Index Scan”和“Clustered Index Seek”，先创建新表，并添加一些示例数据进去。下面是创建新表的脚本：

Create Table PerformanceIssue
(
PRID UniqueIdentifier NOT NULL,
PRCode Int NOT NULL,
PRDesc Varchar (100) NOT NULL
)
ON [PRIMARY]

表创建后需要添加一些数据。使用下面的脚本添加100,000条记录进去。脚本执行时间可能比较长，请耐心等待其执行完毕。

Declare @Loop Int
Declare @PRID UniqueIdentifier
Declare @ PRDesc Varchar (100)

Set @Loop = 1
Set @ PRDesc = ''

WHILE @Loop <= 100000
BEGIN
Set @PRID = NewID()
Set @PRDesc = ' PerformanceIssue - ' + Convert( Varchar(10),@Loop )
Insert Into PerformanceIssue Values (@PRID, @Loop, @PRDesc)
Set @Loop = @Loop + 1
END

脚本成功执行后，数据就添加进去了。

用下面语句来看一下表的内容：

Select PRID, PRCode, PRDesc
From PerformanceIssue
GO

由于记录较长，因此这里就不列出查询结果了。

正如我前面讲到，我想解释何时会有“Table Scan”、“Index Scan”、“Index Seek”、“Clustered Index Scan”和“Clustered Index Seek”。上述哪个会改善性能呢？

当SQL Server返回数据时，我们想知道SQL Server采取何种扫描机制来协助获取数据。首先看一下“Table Scan”。我们想了解什么时候“Table Scan”会产生。

选择“显示执行计划”或者使用热键“Alt + Q”来激活“显示执行计划”，当然也可以用快捷键“Ctrl+K”。

看一下执行下面查询后的“执行计划”结果。

Select PRID, PRCode, PRDesc
From PerformanceIssue
GO

上面的“执行计划”中，SQL Server用到了“Table Scan”。我问自己为什么会有“Table Scan”，SQL Server是根据什么来使用该方法的。难道是因为我想获取所有100,000条记录吗？于是我换了一个角度进行思考，如果来避免查询中出现“Table Scan”呢？此时我对SQL Server的扫描机制还不是很清楚，那么该如何优化查询呢？下面的SELECT查询中仅选择两列：[PRID, PRCode]。

Select PRID, PRCode
From PerformanceIssue
GO

查询执行后，执行计划和第一个查询一样。于是将查询改变为只检索一个字段 [PRID]。

Select PRID
From PerformanceIssue

GO

查询执行后，执行计划仍然和第一个查询的相同。对“Estimated row size”属性不需要太大关注。意思我立刻决定只获取一条记录，看看执行计划会如何。查询语句如下：

Select PRID, PRCode, PRDesc
From PerformanceIssue
Where PRID = 'D386C151-5F74-4C2A-B527-86FEF9712955'
-- PRID GUID value might be differ in your machine

GO

执行完成后，执行计划显示：

查询仍然使用了“Table Scan”方法来显示数据。

那么，我需要想其它办法来避免“Table Scan”。首先我想到应该给表加上索引。于是我在PRID字段上创建非聚集索引。添加了索引后是否就能避免“Table Scan”？下面我们开始讨论关于“Index Scan”和“Index Seek”的主题。

Index Scan 和 Index Seek

首先在PRID字段上创建非聚集索引。

CREATE UNIQUE NONCLUSTERED INDEX UNC_PRID
ON PerformanceIssue (PRID)
GO

本文假定读者已经知道非聚集索引如何工作的知识。了解非聚集索引更详细的信息，请阅读BOL相关主题，也可参看 http://www.sql-server-performance.com/gv_index_data_structures.asp。下面我们详细讲述“Index Scan”是如何工作的。

执行下面语句并查看执行计划的结果。

Select PRID, PRCode, PRDesc
From PerformanceIssue
GO

奇怪了，“Table Scan”仍然用到了。为什么SQL Server没有用到那个非聚集索引？于是继续优化查询语句，选择检索两个字段 [PRID, PRCode] 。

Select PRID, PRCode From PerformanceIssue
GO

执行结果是和上一个查询结果一摸一样。于是修改查询为只检索一个字段 [PRID] 。

Select PRID
From PerformanceIssue
GO

执行计划结果如下：

“Index Scan”在查询中被用到了，这很好。很自然，接下来的问题就是“Index Scan”什么时候会被用到。字段PRID上有一个索引，查询语句中选中的字段为PRID。执行查询的时候，SQL Server扫描索引页，因此用到了“Index Scan”方法。前面的查询中选择了有索引的和没有索引的字段，SQL Server无法使用“Index Scan”。当查询中只选择有索引的字段时，SQL Server就使用了“Index Scan”。我不清楚SQL Server底层到底是如何判断的，不过通过这些试验，我认为当查询中只选择有索引的字段时，SQL Server就使用“Index Scan”方法。

下面看“Index Seek”方法何时产生。当我看到“Seek”这个词时，第一反应就是条件查询这个主意。

我尝试三种不同的带WHERE语法的查询语句，以找出那种会用“Index Seek”。第一种语句如下：

Select PRID, PRCode, PRDesc
From PerformanceIssue
Where PRCode = 8
GO

结果显示，执行计划使用了“Table Scan”。

第二种语句如下：

Select PRID, PRCode, PRDesc
From PerformanceIssue
Where PRDesc = ' PerformanceIssue - 8'
GO

执行计划仍然使用“Table Scan”方法。

第三种查询语句如下：

Select PRID, PRCode, PRDesc
From PerformanceIssue
Where PRID = 'D386C151-5F74-4C2A-B527-86FEF9712955'

-- PRID GUID value might be differ in your machine
GO

查询用到了“Index Seek”和“Bookmark Lookup”方法。用到“Index Seek”是因为WHERE后面使用带索引的字段PRID来进行过滤。“Bookmark Lookup”方法被用到是因为查询中选择了没有索引的字段。如果去掉这两个没有索引的字段，那么“Bookmark Lookup”方法就可以去掉。当然如果只返回PRID字段，那么该查询就没什么意义了，因为WHERE语句后面已经给出PRID具体取值了。

我认为“Index Seek”在性能改善上比“Index Scan”和“Table Scan”要好，这主要表现在下面几个方面：

1. “Index Seek”不需要对表和索引页进行扫描；而“Table Scan”和“Index Scan”需要。
2. “Index Seek”利用“WHERE”来过滤获取的数据，这样比用“Index Scan”和“Table Scan”快很多。

当我完成这些测试后，我同事问我一个很有意思的问题：SQL Server什么时候使用“Clustered Index Scan”和“Clustered Index Seek”？下面对“Clustered Index Scan”和“Clustered Index Seek”进行实验。

我决定在PRCode上建一个聚集索引来测试“Clustered Index Scan”和“Clustered Index Seek”。

Clustered Index Scan & Clustered Index Seek

下面的脚本删除PRID字段上的索引，并在PRCode字段上创建聚集索引。

Drop Index PerformanceIssue.UNC_PRID
GO
CREATE UNIQUE CLUSTERED INDEX UC_PRCode
ON PerformanceIssue( PRCode)
GO
-------------
Clustered index has been created successfully.
Index has been created.

关于聚集索引的基础知识请查阅联机帮助的相关主题或者 http://www.sql-server-performance.com/gv_index_data_structures.asp。下面我们将重点放在“Clustered Index Scan”和“Clustered Index Seek”如何被使用上。

执行下面查询语句：

Select PRID, PRCode, PRDesc
From PerformanceIssue
GO

查询执行后，可以看到执行计划中用到了“Clustered Index Scan”。

下面用三种不同的WHERE方式来试验何时SQL Server会用到“Clustered Index Seek”。第一种形式如下：

Select PRID, PRCode, PRDesc
From PerformanceIssue
Where PRDesc = ' PerformanceIssue - 8'
GO

查询执行后，可以看到执行计划中用到了“Clustered Index Scan”。

第二种形式如下：

Select PRID, PRCode, PRDesc
From PerformanceIssue
Where PRID = 'D386C151-5F74-4C2A-B527-86FEF9712955'

-- PRID GUID value might be differ in your machine
GO

查询执行后，发现执行计划中用到的仍然是“Clustered Index Scan”。

第三种形式：

Select PRID, PRCode, PRDesc
From PerformanceIssue
Where PRCode = 8
GO

这次执行计划用到了“Clustered Index Seek”。

当在WHERE后用到PRCode字段的时候，“Clustered Index Seek”被用到。执行计划对聚集索引表检索的时候，因为在选取的字段中，包括没有索引的字段，所以不用用到“Bookmark Lookup”方法。

我个人认为，从改善性能角度考虑，“Clustered Index Seek”比“Clustered Index Scan”和“Index Seek”要好。

1. “Clustered Index Seek”不需要扫描整个聚集索引页。
2. 和“Index Scan”相比，对于检索选择的字段包含那些没有索引的字段时，“Clustered Index Seek”不会有“Bookmark Lookup”方法出现。

通过这些试验，我对执行计划的应用积累了实际经验。我知道哪种扫描机制可以提高性能，从而是的客户满意。