风水与数据库设计

寻找恰当的定位

数据用户的定位是数据库设计中一个要考虑的基本问题。在线事务处理(OLTP)应用程序通常执行很多插入、更新和删除操作，而分析程序(包括OLAP程序)则主要执行SELECT查询。换句话说，OLTP程序改变数据；分析程序读取数据。这两种操作从更本上总是相互对立的。

这种对立在SQL Server中明显的表现是关于锁的争用问题。OLTP程序必须使用独占锁以维护数据一致性，然而，这些锁中断了到分析程序的数据流。过去5年中对SQL SerVer引擎的许多改进都集中在减少独占锁存在的时间的问题上，它们施加和释放的速度以及它们锁定的粒度。将页级锁定改变为行级锁定背后的想法是可以通过将锁分散到更多的独立单元上以减少对数据整体上的争用。这种想法是否有效不仅依赖于相对并发用户数量来说的数据库尺寸，而且于这些应用程序如何操作数据。

举个例子，观察一下如果你将Northwind数据库用来支持NothwindTrader的电子商务Web站点时可能会发生什么。正如你所预期的那样，大多数的记录将存在于Order Details表中。给定Orders表和Order Details表的关系，对于Orders表中的每条记录，在Order Details表中都存在几条记录与其相对应。Order Details表中的每行是22个字节，因此如果你使用页级锁定，系统可以支持6个(2155行／368行每页)用户同时进行要求独占锁的更改。类似地，当数百计的用户可以从表中读取记录时，一个共享锁可能阻止同样多的用户更改他们的订单。随着Order Details表中记录数的增加，潜在的并发用户数量成比例地增长，这是因为两个用户要求同一个页面的可能性在减少。这样一来，一个表的增长实际上能够改善数据的流动。

行级锁定相对于页级锁定来说具有显著的改进，因为锁定只影响表中更小的一部分(例如，一个锁只影响一行而不是一页中的所有368行)。然而，争用的级别仍然会随着用户数量的增长而增长。行级锁定只是增加了并发用户的数量，因为锁的粒度更小而且更多的人可以持有对单独行的锁定而发生冲突的几率很小。然而，当一个表的行数向着接近页面尺寸增长时，由于其锁定粒度接近页级锁定，行级锁定会丧失它的一些优点。因此，在采用行级锁定的系统中，频繁使用的表应该在每一页中包含尽可能多的行。

此外，服务器自身也可能成为数据流动潜在的障碍。随着用户数量的增加，数据缓冲区中对空间的要求也会相应增加。硬盘子系统的负载也会随着SQL Server查找特定的记录而增加。如果Northwind Trader没有规律地从Order Details表中存取行，查找这些行并将它们装载到内存的时间将会线性增长直到硬盘子系统满负载。但硬盘子系统超负载时，查找时间会由于数据从硬盘到内存传输的延迟而呈对数级的增长。在这种情况下，服务器硬件条件的制约可能会对即使是最好的数据库设计产生限制。

Products表中包含一列用于保存当前存货水平的数据这一事实可以说明另一种类型的问题。需要关注的是Products几乎是数据库所支持每种操作中的都要涉及的部分。某个人在浏览想要购买的物品而执行的SELECT查询会阻止其他人下订单时对存货水平的更新。订单被终止同样会对系统中的每个人造成影响，而且，某个人如果运行耗时的销售报告也会阻止大量的客户完成他们的订单。

实时的存货管理是一个存在很长时间的问题了，可以有许多不同的解决方案。考虑当一个客户在网站上将10O瓶名贵葡萄酒放进购物车时会发生些什么。如果程序通过减少Product表中的数量来预留这些瓶效，其他想要购买这种酒的客户可能会发现存货少于他们所想要的数量而到其他地方去购买。如果程序将商品加到购物车中而并不在库存中预留，客户购买的瓶数可能会比Northwind Trader现存的数量更多。这个商业决策的确有点困难。

这样一来，商业决策经常也会影响到数据库设计。在这种情况下，如果Northwind Traders选择通过减少Products表UnitsInStock列中的数据来预留存货，执行UPDATE语句所需要的独占锁会阻塞每个想要购买此种商品的客户的数据流。如果多个客户在同时要预留多种商品，你将面临死锁的危险，这将使所有相关人员的数据流停止。这种锁定问题的产生是由于Products表是众多操作的中心；解决方法是消除单个的故障点。一种途径是允许脏读(dirty reads)——读操作忽略独占锁。对于Products表来说，让客户看到不一致的数据不是太大的问题，因为大多数列中包含的是诸如产品名称和供应商名称这类的静态数据。只有当用户查看存货水平时，你需要小心。执行改变存货水平的UPDATE语句期间所加的独占锁以先来先服务的原则串行化对行的访问，但允许脏读将会使客户所看到的存货水平比实际的要大。如果太多的人试图订购太多数量的同一种商品，存货水平可能变成负数。虽然许多程序用负存货来表示有多少商品需要延期交货，但诸如名贵葡萄酒这类的商品不能被记录。在大多数情况下，你可以使用一个UPDATE触发器检查负存货数量来解决这种情况。如何处理这种类型的问题是一个商业上的决策，而且由于它会影响数据表中值的含义，进而也会影响到你的整个数据库设计。

理解你的数据用户的定位是很重要的。了解有多少人将会同时使用数据库，他们将执行什么类型的操作、执行的频度以及在什么数据上执行将会有助于你在数据库设计中最大化数据的流动。通常，不好的设计是由于对这些因素的忽略或是错误猜测；因此，它们应该成为你开始进行数据库设计过程中需要考虑的第一件事。

寻找适当的平衡

虽然用户将会对你的数据库设计产生最大的影响，但硬件的选择也将对最终的成败与否产生显著的影响。低速的硬件会使不良的设计更为恶化，而高速的硬件则可以掩盖很多问题。不幸的是，许多程序员依赖于高速的硬件而免于费尽心思地考虑其应用程序所使用的数据库。

举个例子，我的一个客户，一个提供旅馆服务的网站，最初将其所有的顾客和动态的Web内容都保存在一个Joint Engine Technology(JET)数据库中，ASP应用程序通过ADO对其进行访问。由于复杂的委托结构以及用户Web页的所有内容都保存在其中，这个数据库中存在大量复杂的表间交叉关系。但用户数量非常迅速地从1000增长到5000时，这个客户将数据库转移到一台运行速度是原来的两倍的单CPU服务器上。这台服务器一直工作到用户数量达到10000；此后，这个客户将数据库转移到一台带有6个磁盘的RAID5阵列的4 CPU Compaq服务器上。数据库只有20MB，但由于糟糕的设计造成了用户之间的相互影响。JET所使用的不完善的锁机制也是一个问题。将数据库转移到更快的机器上仅仅意味着每个操作结束的更快些，每个用户使用更少的物理时间。这种方法就像公路上一条新的车道可以缓解高峰时间的交通拥塞。

当用户数量增加到l5000时，我的客户被迫将数据库转移到SQLServer上并建立了适合于SQL Server的素引。我们保持这种数据库设计直到用户数目增加到60000。在这个点上，应用程序可以处理大约1000个并发的用户更新客户端的Web页——这个比例低得无法接受。因为数据库已经运行在明显没有超负荷运行的四路CPU的系统上，将数据库转移到更快的服务器上不会有什么帮助。即使是转移到多服务器上也不会有什么作用，这是因为此时数据流动的障碍是数据库本身，而不再是硬件。唯一的选择是改变数据库的设计。从这次经历中我吸取的教训是尽管硬件可以缓解由于不良的数据库设计而引发的问题，但修改即使是已经建好的数据库最终也会比增加新的内存、磁盘或是CPU更经济。

一个普遍存在的错误观念是完全的规范化是数据库设计的最终目标。规范化有益于保证数据的完整性以及最小化数据冗余，但如果过度使用，它可能会产生阻碍数据从数据库服务器流动的负面影响。

SELECT a.CompanyName,a.Address,a.City,a.Region,a.PostalCode,
    b.ShipName,b.ShipAddress,b.ShipCity,b.ShipRegion,
    b.ShipPostalCode,d.ProductName,c.Quantity,c.UnitPrice,
    (c.UnitPrice*c.Quantity*(1.0-c.Discount))AS Subtotal,
    e.CompanyName AS ShipperName
FROM Customers AS a INNER JOIN orders AS b ON
    (a.customerid=b.customerid)
INNER JOIN [ORDER Details] AS c 
    0N(b.OrderID=C.0rderID)
INNER JOIN Products AS d 
    0N(c.ProductID=d.ProductlD)
INNER JOIN Shippers AS e 
    0N(b.ShipVia=e.ShipperlD)
WHERE a.CustomerID='VINET'

回到Northwind Traders电子商务网站的例子，清单1中的查询展示了一种用于在Web页面上为一个客户显示出他所发出的所有订单的可选方法。作为一个规则，在任何时候将一张表从多重SELECT中移除时，你可以改善查询的性能。在这个例子中，Order Details表中的ProductID列可以使查询从Products表中检索产品名称。如果你假定产品名称不会改变而且ProductID不会与另外一种产品相关联，你可以通过在Order Details表中增加一列ProductName来改善数据库的设计。这个增加可以免去将Products表连接到查询中，当Products和Orders Details表增大时，这种设计的改善会体现得越来越明显。

这种改进的不足是由于为每个订单增加了一个40字符的产品名称而使得Order Details表比原始设计明显增大。考虑现今硬盘的尺寸，存储额外的数据不是什么问题；然而，新增的列会要求更多的数据通过磁盘的I／O缓冲区，这可能会导致整体吞吐量的降低。此外，Order Details表的每行都要求数据缓存中更多的RAM，这意味着其他内容可能会受到排挤。这种情况是一个很好的例子，说明了你在优化性能时必须权衡行事。作为一种普遍的规则，在你的最初设计中争取实现完全的规范化，因为规范化减少了给数据库增加冗余以及造成数据不一致性的几率。当你完成设计时，需要寻求规范化和性能之间的平衡。需要关注的地方是那些含有多连接、聚集函数和GROUP BY子句的查询，交叉子查询以及外键引用等。每个这样的查询都可能是耗时的而且使用大量的数据缓存并在tempdb中生成临时表。

发现障碍

正如前面我们所看到的，页或行的用户比例以及RAM数量相对于数据库尺寸的比例，是两个影响数据流动的因素。应用程序中向表插入行的方式同样会改变出入表的数据流。例如，SQL Servr会将插入到一张没有聚集索引(clustered index)的表中的行放到分配给这张表的最后一页。因此，应用于INSERT操作的锁将集中在表的末端。SQL Servr 6.5有一种称为插入行级锁定(Insert Row—level Locking)的专用模式，这种模式在本质上使SQL Server具有了应用于INSERT操作的独占锁。SQLServer 2000和7.0具有对所有行的行级锁定，所以后两者都使在最后一页集中执行插入操作所引发的问题达到最小。然而，在新行无法装入一个现存页面的时候，所有的版本都必须使用页级的锁定。现存的页和新分配的页在SQL Server调整其内容时都有页级锁。当这些独占锁存在的时候，涉及到这些页的操作都无法执行，包括新的插入操作。这当然会造成短暂的数据流中断。

OLTP应用程序通常使用作为高级别INSERT操作目标的若干表，而且这些表经常会有一大部分用户要同时使用它们。如果你假定这些表在某时刻有少量行被删除，那么大部分插入的行会存在于两页中：表中的最后一页和被分配用来处理溢出的一页。再加上SQL Servr的存储引擎趋向于同时在一块硬盘上进行页面分配，INSERT操作可以很快地使系统遭到破坏。此外，随着数目的增长，用户花在等待操作完成上的时间也会相应增长，这种增长通常是非线性的。

顺流而动

作为一种普遍的规则，将风水的概念应用于数据库设计中就转化为寻找数据流动的障碍。这些障碍可能是由于作为数据库设计重点的表的拥塞、行的争用、硬盘子系统的超负荷使用、在大表上执行查询造成的在数据缓存中的页面轮换以及持有锁时间过长或多次读取数据的低效查询等诸多因素所引发的。通畅数据流的方式包括将用于分析的数据从用于是务处理的数据库中复制到一个单独的数据库中、对设计进行逆规范化以减少过度的连接、对表进行拆分以使主要用于读取的列与经常会被修改的列存在于不同的表中、增加服务器的可用资源以及在存储过程中放置用户自定义的事务处理等等。

一种解决方案是使INSERT操作尽可能快地发生并将它们平均地分布到表的页面上。使用聚集索引的一个好处是插入是按照索引的顺序分散在表中；因此，找到正确的索引顺序可以平衡好的性能和不好的性能之间的差异。例如，如果Orders表在OrderDate列上使用了聚集索引，新的订单会集中在表的末尾，这会引发一个潜在的问题。如果你改为使用CustomerID列，每个顾客的订单将集中在一个区域，顾客之间的交叠也会达到最小。而且，一个顾客同时发出两份订单或是两个CustomerID相临的顾客同时订货是不大可能的。

当然，分析程序引入的共享锁进一步加剧了数据流的问题，因为共享锁的存在阻止了独占锁的施加。幸运的是，共享锁的存在期较短，而且分析程序不经常读取最近插入的数据。然而，在使用频率较高的表中删除行的0LTP程序将会由于分析程序对新插入行所在区域进行锁定而发生冲突。在OLTP程序和OLAP程序使用同一张表的情况下，你需要知道你的应用程序执行的是哪个操作。

对表中的大部分记录进行扫描的查询，如大所数分析程序中查询，是需要进行改善的候选者。对于查找一张表的多连接要起进行表扫描，查询性能会随着表的增长而成比例下降。对于包含有在一张表上进行扫描的多语句事务，SQL Server保持锁的时间长度会按照表增长的比例而增长。减少表扫描的典型方法是创建索引，但要记住INSERT和UP-DATE操作会强制对连接到表中的每个索引的更改。随着索引数目的增长和表的增长，更改操作的性能会下降。如果你结合了事务处理和报告程序使用同一个数据库，那么考虑将用于报告的数据复制到另外的表或数据库中，因为通常用于生成报告的查询不会从那些有助于事务处理的索引中受益。

对于简单SELECT查询所隐含的事务处理，共享锁可能不会造成问题，因为SQL Server每读完一行就会将其释放。然而，用户自定义的事务可能会是一个问题，因为SQL Server直到整个事务提交后才会将其持有的锁释放。影响到表中大部分记录的UPDATE语句是一个特殊的问题，因为SQL Server必须保持独占锁直到事务的结束。使用HOLDLOCK提示的SELECT查询也会产生同样的影响。因此，用户自定义的事务应该尽可能地短。这种延迟也是不要从客户端应用程序开始事务的一个重要原因。如果可能的话，通过一个存储过程执行所有的用户自定义事务；如果你必须从客户端应用程序启动事务，要确保程序在事务开始后不会延迟处理过程。

简单地说，寻找一些方法，使数据请求分布在尽可能广的区域上。对于数据库设计，这意味着对数据库中那些只有少量的表与其他表相关联的设计图应该持一种怀疑态度，特别是在OLTP程序中。对于数据存储，这意味着使用聚集索引分散数据以使你可以减少多用户要求相临的行或页的可能性。对于硬件而言，这意味着要确保RAM在数据库尺寸中占较大的比例，文件分布在多个物理硬盘上，以及硬盘控制器有足够的能力来处理磁盘I/O。对于应用程序而言，这意味着尽可能地共享如数据库连接这样的资源并使对于诸如锁和事务这样的公共资源的使用达到最小。对于用户来说，这意味着理解他们需要对数据做些什么以便你可以使数据库的设计适应他们的需要而不是一些不切实际的假想。意识到数据通过你的服务器流动的本质并使用一些用于扩宽数据通路的常识性技术，你将会避免很多长久以来一直存在的普遍问题。

对于诸如锁和事务这样的公共资源的使用达到最小。对于用户来说，这意味着理解他们需要对数据做些什么以便你可以使数据库的设计适应他们的需要而不是一些不切实际的假想。意识到数据通过你的服务器流动的本质并使用一些用于扩宽数据通路的常识性技术，你将会避免很多长久以来一直存在的普遍问题。