《锋利的SQL（第2版）》——2.1 SQL Server数据库基础

本节书摘来自异步社区出版社《锋利的SQL（第2版）》一书中的第2章，第2.1节，作者：张洪举 王晓文，更多章节内容可以访问云栖社区“异步社区”公众号查看。

2.1 SQL Server数据库基础

锋利的SQL（第2版）
本节将介绍SQL Server数据库的实现方式，包括实例、数据库、架构、数据库文件存储等内容。

2.1.1　数据库的服务方式

传统的SQL Server数据库服务方式是安装在客户场所内，客户负责所有的事情——硬件、安装软件、处理更新、高可用性和灾难恢复（HADR）、安全，以及其他事项。客户可以在同一个服务器上安装产品的多个实例，可以编写查询与多个数据库交互，也可以在数据库之间切换连接。

当然，目前已经进入了云时代，数据库作为IT基础架构中最重要的部分，与云的结合将变得非常重要。微软支持两种SQL Server云：私有云和公共云。云这个术语用于私有情况应当是有点混乱，主要因为它是在本地托管的，但私有云使用了虚拟化技术。公共云称为Windows Azure SQL Database（以前名为SQL Azure），它是在微软数据中心托管， 硬件、维护、HADR和更新全部由微软负责，但是客户仍旧负责索引和查询优化。

使用公共云，客户可以在云服务器（当然是一个概念性的服务器）上有多个数据库，但一次只能连接到一个数据库，客户不能在数据库之间切换，也不能编写多数据库查询。

此外，SQL Server公共云服务与企业内部部署的SQL Server在功能上会有所差异，某些运行在内部部署上的SQL语句，可能不能运行在SQL Server公共云上。但是，公共云的版本更新和部署进度比企业内部部署的SQL Server快很多，因此，一些SQL功能有可能在它们出现在企业内部部署的SQL Server版本之前已经在SQL Database中实现了。

2.1.2　数据库实例

在企业内部部署的SQL Server中可以安装多个实例，如图2-1所示。在安全、所管理数据和所有其他资源方面，每个实例完全独立于其他实例。

可以将计算机中多个实例中的一个实例作为默认实例，其他实例则必须是命名实例。在安装时要确定是默认实例还是命名实例，确定以后便不能更改。若要连接到默认实例，客户端应用程序可以指定计算机的名称或IP地址。若要连接到命名实例，客户端需要指定计算机的名称或IP地址，后跟一个反斜杠（），再跟上实例名称（这是在安装时指定的）。例如，假设你在一台名为Server1的计算机上安装有两个SQL Server实例，其中一个实例是以默认实例安装，另一个作为命名实例安装，称为Inst1。要连接到默认实例，只需要指定Server1作为服务器名称。但是，若要连接到命名实例，需要指定服务器和实例名称：Server1Inst1。

允许在同一台计算机上安装SQL Server的多个实例会有多种好处。例如，一个数据库服务支持公司为了能够重现客户遇到的问题，就可以安装一个新实例来模拟客户的生产环境，而对其他实例不会产生影响。再如，数据库服务供应商有时候需要保证其客户数据与其他客户数据完全安全分离，就可以采取这种方式解决。

对于云方式的数据库连接，在你购买服务时，服务商会告诉你连接方法。

2.1.3　架构与对象

架构是从SQL Server 2005开始引入的，之前没有架构的概念，只有用户的概念。架构独立于创建它们的数据厍用户而存在，每个对象都属于一个数据库架构（对象包括表、视图、存储过程等），如图2-2所示。你可以把架构看作表、视图、存储过程等对象的容器。

数据库架构是一个独立于数据库用户的非重复命名空间，用户与架构相互独立，删除用户不会删除架构中的对象。将架构与数据库用户分离对管理员和开发人员而言有下列好处。

极大地简化了删除数据库用户的操作。删除数据库用户不需要重命名该用户架构所包含的对象。因而，在删除创建架构所含对象的用户后，不再需要修改和测试显式引用这些对象的应用程序。
多个用户可以共享一个默认架构以进行统一名称解析。
开发人员通过共享默认架构可以将共享对象存储在为特定应用程序专门创建的架构中，而不是DBO架构中。
可以用更大的粒度管理对象的权限。
如果数据库用户具有创建架构的权限，就可以在数据库中创建和更改架构，该用户就是架构的所有者，并且可以将架构的所有者转让给其他用户，或者授予其他用户访问该架构的权限，只有架构的所有者才能在架构中创建对象。也就是说，你可以在架构级别控制权限。例如，可以授予用户对架构的SELECT权限，允许用户从该架构的所有对象中查询数据。引入架构后，就可以实现对架构内的对象统一设置访问权限。所以说，对于确定如何安排架构中的对象，安全是一个很重要的考虑因素。

架构作为一个命名空间——它被用作对象名称的前缀。例如，假设在一个名为Sales的架构中有一个名为Orders的表。架构限定式对象名称（也称为“两部分式对象名称”）是Sales.Orders。引用对象时如果省略了架构名称，SQL Server将通过一个过程来解决架构名称，如检查对象是否在用户的默认架构中，如果不是，则检查是否存在于dbo架构中。微软建议在代码中引用对象时始终使用“两部分式”对象名称。在没有显式指定架构名称的情况下，在判断对象时会有一些不必要的额外支出，既然是不必要的，为什么要支出呢？ 此外，如果多个具有相同名称的对象存在于不同架构中，结果往往是得到一个不同的对象，而不是想要的对象。

2.1.4　数据库文件和文件组

SQL Server将数据库映射为一组操作系统文件。数据和日志信息绝不混合在同一个文件中，而且一个文件只能由一个数据库使用。文件组是文件的命名集合，用于简化数据存放和管理任务（如备份和还原操作）。

1．数据库文件
数据库是由数据文件和事务日志文件构成的。创建数据库时，你可以为每个文件定义各种属性，包括文件名称、位置、初始大小、最大大小和一个自动增长的增量。每个数据库必须至少有一个数据文件和一个日志文件（SQL Server默认的）。数据文件存储对象数据，日志文件存储SQL Server需要维护事务的信息。

数据文件包括主文件、次要文件。主文件中包含着数据库的启动信息。此外，主文件还用于存储数据。每个数据库都有一个主文件。主文件的建议文件扩展名为.mdf。

次要文件包含不能放置在主数据文件中的所有数据。如果主文件足够大，能够包含数据库中的所有数据，则该数据库不需要次要数据文件。有些数据库可能非常大，因此需要多个次要数据文件，也可能在独立的磁盘驱动器上使用次要文件以将数据分散到多个磁盘上。次要文件的建议文件扩展名为.ndf。

在创建数据库时，应当根据数据库中预期的最大数据量，创建尽可能大的数据文件。

事务日志文件包含用于恢复数据库的日志信息，每个数据库必须至少有一个事务日志文件。日志文件最小为512 KB。事务日志的建议文件扩展名为.ldf。

虽然SQL Server可以并行写入多个数据文件，但它只能以连续方式一次写入一个日志文件。因此，与数据文件不同，有多个日志文件不会带来性能提升。如果日志所在的磁盘驱动器空间不足，你可能需要添加日志文件。

2．文件和文件组的填充策略
数据文件被组织在称为“文件组”的逻辑组中。文件组是所创建对象（如表或索引）的目标，对象数据将分散在其所隶属目标文件组的文件中，文件组可以按你要求的方式来控制对象的物理位置。

每个数据库至少有一个PRIMARY文件组，此文件组包含主文件和未放入其他文件组的所有次要文件。可以创建用户定义的文件组，用于将数据文件集合起来，以便于管理、数据分配和放置。你可以决定哪个文件组被标记为默认文件组。如果创建对象时的语句没有显式指定一个不同的目标文件组，则对象将被建立在默认文件组中。

文件组对组内的所有文件都使用按比例填充策略。将数据写入文件组时，数据库引擎会根据文件中的可用空间量将一定比例的数据写入文件组中的每个文件，而不是将所有数据先写满第一个文件，然后再写入下一个文件。例如，如果文件f1有100 MB可用空间，文件f2有200 MB可用空间，则从文件f1中分配一个区，从文件f2中分配两个区，依次类推。这样，两个文件几乎同时填满。

文件组中的所有文件一满，数据库引擎就自动按照循环方式一次扩展一个文件，以容纳更多数据（假定数据库设置为自动增长）。例如，某个文件组由三个文件组成，它们都设置为自动增长。当文件组中所有文件的空间都已用完时，只扩展第一个文件。当第一个文件已满，无法再向文件组中写入更多数据时，将扩展第二个文件。当第二个文件已满，无法再向文件组中写入更多数据时，将扩展第三个文件。当第三个文件已满，无法再向文件组中写入更多数据时，将再次扩展第一个文件，依次类推。

使用文件和文件组可以改善数据库的性能，因为这样允许跨多个磁盘、多个磁盘控制器或RAID（独立磁盘冗余阵列）系统创建数据库。例如，如果计算机上有4个磁盘，那么可以创建一个由3个数据文件和1个日志文件组成的数据库，每个磁盘上放置1个文件。在对数据进行访问时，4个读/写磁头可以同时并行地访问数据。这样可以加快数据库操作的速度。

另外，文件和文件组还允许数据布局，可以在特定的文件组中创建表。这样可以改善性能，因为可以将特定表的所有 I/O 都定向到一个特定的磁盘。例如，可以将最常用的表放在一个文件组的一个文件中，该文件组位于一个磁盘上；而将数据库中其他不常访问的表放在另一个文件组的其他文件中，该文件组位于第二个磁盘上。

3．文件和文件组的设计规则
下列规则适用于文件和文件组。

个文件或文件组不能由多个数据库使用。
一个文件只能是一个文件组的成员。
数据和事务日志信息不能属于同一个文件或文件组。
事务日志文件不能属于任何文件组。
下面是使用文件和文件组时的一些建议。

大多数数据库在只有单个数据文件和单个事务日志文件的情况下性能良好。
如果使用多个文件，应当为附加文件创建第二个文件组，并将其设置为默认文件组。这样，主文件将只包含系统表和对象。
要使性能最大化，应当在尽可能多的不同的可用本地物理磁盘上创建文件或文件组。应当将争夺空间最激烈的对象置于不同的文件组中。
使用文件组将对象放置在特定的物理磁盘上。
将在同一联接查询中使用的不同表置于不同的文件组中。由于采用并行磁盘I/O对联接数据进行搜索，所以性能将得以改善。
将最常访问的表和属于这些表的非聚集索引置于不同的文件组中。如果文件位于不同的物理磁盘上，由于采用并行I/O，所以性能将得以改善。
不要将事务日志文件置于其中已有其他文件和文件组的物理磁盘上。
本文仅用于学习和交流目的，不代表异步社区观点。非商业转载请注明作译者、出处，并保留本文的原始链接。