磁盘空间使用关乎SQL Server性能

lusspan

当谈到影响SQL Server性能的组分时，你很可能会想到CPU的使用率、内存和查询过程所占用的I/O资源。不过，还有另外一个值得注意的影响因素，那就是磁盘空间的使用。在以前，磁盘空间的成本非常高，所以大部分人都会竭尽所能地节约磁盘空间的使用。相信大家对千年虫问题还记忆犹新，问题的产生正是源于日期是以八位字符(例如2000年11月7日的格式为11/07/00)形式存储的。老牌的计算机程序员在存储每个日期时都只存储年份的后两位数字，而省去了前两位。如今，磁盘驱动器的价格一落千丈，所以大家也就不用花大量时间来想方设法为减少磁盘空间的使用量而优化数据库设计了。不过，还是请大家记住，你每浪费一个字节的数据库磁盘空间，就会对应用程序执行性能造成一次冲击。本文将为你一一细数磁盘空间的使用是如何影响数据库性能的。
　　磁盘空间使用对性能的影响

　　要讨论磁盘空间使用对性能的影响，我们首先要讲述一下从磁盘驱动器读写数据到底要耗费些什么。你每次从 SQL Server读取某块数据，都需要从磁盘检索信息。检索的过程启动了磁盘的I/O操作。SQL Server中的数据都是存储在一系列不同的物理页中。每个物理页可用空间大小为8060字节(除去存放系统信息的部分)。对于每个物理页里的数据，SQL Server都需要进行一次I/O操作来检索该数据，也就是说磁盘的读取和写入数据操作是在页级执行的。

　　为了更深入了解I/O操作是如何能够影响到数据库性能的，我们来做个简单的计算题，如果我们要从一个SQL Server表中检索一千万条记录，每条记录的长度为300字节，需要进行多少次I/O操作呢?这意味着每页可以存储26条不同的记录，所有的一千万条记录数据就需要384615个数据页来存储，这仅仅是存储原始数据，还没有把索引所占的空间大小计算在内。也就是说，在这存储了一千万条记录的表中查找一条记录就需要执行384615次I/O操作。

　　现在假设我为每条记录节省了2字节的数据空间，这样每条记录的长度就是298字节。这样的话每个SQL Server页就可以存储27条记录。就是省了这两个字节，你每执行一次 I/O操作都可以多检索一条记录。当你读取整个一千万条记录的表时，可以少执行14244次 I/O操作。每条记录节省两字节的空间却能够省去大量的 I/O操作。

　　所以，每当你把存储到SQL Server表的一个记录减少少量字节时，你都能为改善数据库查询性能做出贡献。当表足够大时，你就能明显看到性能的提升。所以，你应当尽量减少记录的大小，这样就能最大限度地增加存储在每个数据页的记录数量。

　　而当你尽量减少存储数据的磁盘空间时，节约下来的不仅仅是 I/O操作。记住每个页面在被读取的时候，首先要存储在缓冲池里。所以，记录长度越短，在一个缓冲池页中能够存储的记录就越多。所以节约了用来存储数据的磁盘空间，也就节约了读取数据所必须的内存容量。

　　利用合适的数据类型以便尽量减少磁盘空间的使用

　　当你为某特定的字段选择数据类型时，你必须确保你选定的数据类型是合适的。大家在具体操作时很容易选错数据类型，从而浪费了磁盘空间。因此，你必须小心谨慎，并确保你选择的数据类型满足了数据需求并最大限度减少了存储每个字段所需要的磁盘空间大小。下面我们来逐一审视不同的数据类型及其对磁盘空间的需求。

　　首先，我们来看看Unicode数据类型。Unicode数据类型包括NVARCHAR、NCHAR和NTEXT数据类型。Unicode数据类型需要两个字节来存储每个字符。而非Unicode数据类型(如 VARCHAR、CHAR和TEXT)存储一个字符只需要一个字节。非Unicode数据类型智能存储256个不同的字符。而使用Unicode数据类型，你可以存储多达65536种不同的双字节模式。由于使用非Unicode数据类型时能够用来存储的特定字符数量受到限制，所以用来代表某一特定字符的十六进制数随着代码页的不同而不相。当你使用Unicode数据类型时，代表字符的十六进制数在不同的代码页都通用。Unicode数据类型往往适合于国际化应用程序。如果你的应用程序不需要提供国际化支持，而你的应用程序能够用单字节的256字符组合表现出来的话，你就应该考虑使用VARCHAR、CHAR和TEXT 等非Unicode数据类型。使用非Unicode数据类型，每个基于字符的字段可以只使用一半磁盘空间。如果你存储了大量的字符数据，纳闷你使用非Unicode数据类型所节省下来的磁盘空间就相当客观了。有人做过相关试验，把使用Unicode数据类型的数据库改为全部使用非Unicode数据类型，发现能够节省40%的磁盘空间。如此大幅度的磁盘空间节省率比使用Unicode数据类型更能改善性能。而这种性能的提升正是源于增加了能够存储在单个SQL Server页面的记录数量。

　　存储字符型数据时，还有一些需要注意的地方，那就是CHAR和VARCHAR数据类型的使用。CHAR数据类型是一种固定格式长度的数据类型。当你定义一个字段为CHAR(20)，那么不管你存储的数据是否有20个字节，它都要占用20个字节的空间。也就是说，如果你只为某定义为CHAR(20)的字段赋予“abc”的值，那么SQL Server将它存储成“abc”后面带上17个空格的形式。而VARCHAR字段的长度则是可变的。所以当你为定义为VARCHAR(20)的字段赋予“abc”的值时，只占用了5字节的存储空间，其中2字节是用来记录数据长度的，另外才是用来存储“abc”值的。如果需要赋值的字符型字段很少，那么使用VARCHAR 数据类型存储这些字段能减少磁盘空间的使用量。

　　下面让我们来看看整数数据类型应该如何存储。整数类型又可以分为四种不同的数据类型：TINYINT、SMALLINT、INT 和 BIGINT。每一种类型所占用的存储空间不同。TINYINT占用1字节的存储空间，支持的值范围为0到255。SMALLINT占用2字节存储空间，可以表示的值范围为-32768到32767。INT数据类型占用4字节存储空间，取值范围从-2147483648到2147483647。而 BIGINT 占用8字节的存储空间，能够存储从-9223372036854775808到9223372036854775807的值。

lusspan

当你要定义某字段来存储一个整数值时，你需要考虑你的应用程序需要的所有可能出现的值。如果你的字段只需要表示一个10以下的集，取值范围从0到9，那你就应该选择使用TINYINT字段。 如果你选择其他的整数数据类型就会大量浪费磁盘存储空间。

　　我们经常碰到带有ID字段的表，这一字段作为自动增长的identity字段来定义。当创建identify字段，要确定数据类型时，你需要考虑该表最多能达到多少行。如果少于10000行，那么你应该选择SMALLINT数据类型，而不用INT类型，这样可以节省大量磁盘空间。人们往往习惯性地把所有的identity字段都定义为INT数据类型，甚至就算表最多只有几行的时候也这样做。当你为identity字段定义数据类型时，一定要选择合适的整数数据类型。

　　还有一种数据类型可以用来节省磁盘空间，那就是BIT数据类型。BIT数据类型只能用来存储1或0的值。BIT数据类型最适合拿来存储TRUE/FALSE条件值。由于存储的最小单位是一个字节，所以单个字节可以存储八种不同的BIT字段。如果你需要记录TRUE/FALSE或 0/1类型的条件值时，最好选择BIT数据类型，而不要选择其他的整数数据类型。当你的表中只有单个BIT数据类型字段时，你可以把它定义为BIT，虽然TINYINT占用相同的磁盘存储空间，这是因为单个BIT数据类型只要求1字节的空间。当你在一个表中有很多BIT数据类型字段时，BIT就比TINYINT更能节省磁盘空间了。

　　DATETIME是另外一个常常被误用而导致磁盘空间浪费的数据类型。这里有两种不同的日期数据类型：SMALLDATETIME和DATETIME。就像其他的数据类型一样，这两种不同的数据类型对存储字节数的要求也不相同。SMALLDATETIME数据类型要求2字节存储空间，存储值的日期范围从1900年1月1日开始到2079年6月6日，而时间部分只能精确到分钟。DATETIME数据类型存储赋值时占用4字节存储空间，支持的日期范围从1753年1月1日到9999年12月31日，而时间部分可以精确到3毫秒。如果你的应用程序只需要存储当前的日期或不久的将来的日期(不超过2079年)，而且你不需要时间部分，那么使用SMALLDATETIME字段数据类型才是正确的选择。假设程序并不需要把时间精确到分钟以下，那绝大部分处理日期的应用程序都可以利用SMALLDATETIME数据类型来节省空间。当你的应用程序需要存储秒以下的时间，或者存储的日期早于1900年或远于2079年，那就必须使用 DATETIME数据类型了。

　　有时候，人们利用UNIQUEIDENTIFER数据类型来存储ID字段。这种数据类型产生一个GUID(全局唯一标志符)，并占用16字节的存储空间。当你要为某独特列创建唯一值时，这是最佳选择。使用这种数据类型的一个普遍原因是，当你进行复制时，UNIQUEIDENTIFIER字段在两个数据库中都是唯一的。这里提到这种数据类型是因为如果你把这种数据类型用在非复制环境中，就会浪费磁盘空间，在非复制环境中应该使用某种整数数据类型来代替UNIQUEIDENTIFIER，以便节省磁盘空间。

　　有一些应用程序使用MONEY和SMALLMONEY数据类型来存储货币数量值。这两种数据类型也占用的磁盘空间大小也不一样。SMALLMONEY只占用4字节存储空间，存储值范围从214748.3538 到214748.3647，而MONEY数据类型需要占用8字节空间，存储值范围从-922337203685477.5808到922337203685477.5807。如果你的应用软件存储的货币交易额一般不超过200000，那么你可以选择SMALLMONEY数据类型，这样记录每次交易额都可以节省4字节的空间。

　　二进制数据可以用两种不同的数据类型来存储：BINARY和VARBINARY。这两种数据类型的关系类似于CHAR和VARCHAR的关系。也就是说，BINARY数据类型是固定长度的，而VARBINARY的长度是可变的。就像VARCHAR数据类型一样，VARBINARY数据类型存储长度除了数据的实际长度外还增加了2字节，以便SQL Server确定VARBINARY数据的实际长度。如果你想要长度可变的二进制字符串，那VARBINARY数据类型无疑会为你节省大量磁盘空间。

　　结论

　　使用了不合适的数据类型，会让SQL Server动用额外的磁盘空间来存储给定的值，这样不仅浪费磁盘空间，而且会引发性能问题。无论什么时候，你都应该挑选能满足给定字段的算有可能组合而使用的少磁盘空间又是最少的数据类型。