【.com快速翻译】除非您遵循本文中的提示，否则很容易编写导致查询变慢或锁定数据库的数据库代码。由于数据库世界还相对不成熟，每个平台上的SQL开发人员都在苦苦挣扎，一遍又一遍地犯同样的错误。当然，数据库供应商正在取得一些进展，并继续努力解决更大的问题。无论SQL开发人员是在SQLServer、Oracle、DB2、Sybase、MySQL还是任何其他关系数据库平台上编写代码，并发性、资源管理、空间管理和速度仍然困扰着他们。部分问题在于没有灵丹妙药；对于几乎所有***做法，我都可以说出至少一个例外。通常，开发人员会找到他们喜欢的方法而不会费心研究其他方法。这可能是缺乏教育的表现，或者开发人员没有意识到他们做错了什么。也许查询在一组本地测试数据上运行顺利，但在生产级系统上，性能并不令人满意。我不希望SQL开发人员成为管理员，但他们必须在编写代码时考虑生产级环境。如果他们在开发初期不这样做，DBA会让他们稍后再做，用户就会受到影响。我们说，调优数据库既是一门科学，也是一门艺术，并且有充分的理由，很少有全面适用的硬性规定。您在一个系统上解决的问题在另一个系统上不是问题，反之亦然。当谈到调优查询时，没有正确答案，但这并不意味着您应该放弃它。以下是您可以遵循的17条原则，您可以期待获得丰硕的成果。不使用UPDATE而不是CASE的问题很常见，但很难发现，并且经常被许多开发人员忽视，因为如此自然地使用UPDATE似乎合乎逻辑。以这个场景为例：你向一个临时表中插入数据，并且需要它在另一个值存在时显示某个值。也许您从Customer表中获取记录并希望将订单超过100,000美元的客户标记为“首选”。因此，您将数据插入表中，运行UPDATE语句，并将订单价值超过100,000美元的任何客户的CustomerRank列设置为“Preferred”。问题是，UPDATE语句被记录下来，这意味着每次写入表都会完成两次。解决方法：在SQL查询中使用内联CASE语句，检查每行的订单金额条件，并在写入表之前设置“Preferred”标志，处理性能得到显着提升。不盲目复用代码的问题也很普遍，很容易复制别人写的代码，因为你知道它会得到你需要的数据。问题是，它经常会获取太多你不需要的数据，而开发人员很少对其进行精简，所以你最终会得到大量数据。这通常表现为WHERE子句中的额外外部连接或额外条件。如果根据您的确切要求精简重用代码，则可以大大提高性能。需要多少列，提取多少列这个问题和第二个问题类似，但是它是列特有的。使用SELECT*对所有查询进行编码而不是一一列出列很容易。问题还在于它获取了太多不需要的数据，这是我见过无数次的错误。开发人员对具有120列和数百万行的表执行SELECT*查询，但***仅使用了其中的三或五列。因此，您处理的数据比您实际需要的多得多，查询返回结果真是奇迹。您不仅处理了太多不需要的数据，而且还在抢夺其他进程的资源。不要查询两次（double-dip）这是我看到很多人犯的另一个错误：写入存储过程，从具有数亿行的表中获取数据。一位开发人员想要提取有关居住在加利福尼亚且年收入超过40,000美元的客户的信息。因此，他查询居住在加利福尼亚州的客户并将结果放入一个临时表中。然后查询年收入大于40,000美元的客户，并将这些结果放入另一个临时表中。***他加入这两个表得到最终的结果。你在开玩笑吧？这应该通过一个查询来完成，而不是为一个非常大的表查询两次。别傻了：尝试只查询一次大表，您会发现存储过程执行得更快。一个稍微不同的场景是当一个过程中的几个步骤需要大表的一个子集时，导致每次都要查询大表。为避免此问题，只需查询此子集，将其保存在别处，然后将后续步骤指向此较小的数据集。知道何时使用临时表解决起来有点棘手，但它是有回报的。临时表可以在很多情况下使用，比如防止大表被查询两次。也可以使用临时表，大大降低连接大表所需的处理能力。如果一定要join一个表到一个大表，而大表是有条件的，只需要将大表中需要的部分数据提取到临时表中，然后与临时表join即可，可以提高查询性能。如果存储过程中有多个查询需要对同一个表执行类似的连接，它也会有很大帮助。预暂存数据是我最喜欢的主题之一，因为它是一种经常被忽视的旧方法。如果您有一个报表或存储过程（或它们的集合）要对大型表执行类似的连接操作，那么通过提前连接表并将它们持久存储在表中来预暂存数据可能会很好对你有帮助。现在可以针对这个预安排的表运行报告，避免大型连接。您不会总是能够使用此方法，但是当您这样做时，您会发现它绝对是节省服务器资源的好方法。注意：许多开发人员通过关注查询本身来避免此连接问题，从连接创建只读视图，这样他们就不必一遍又一遍地键入连接条件。但这种方法的问题是您仍然必须为每个需要它的报告运行查询。如果pre-stage数据，只需要运行一次连接（例如，报告前10分钟），其他人可以避免大连接。你不知道我有多喜欢这个技巧，在大多数环境中，一些公用表一直都在连接，所以没有理由不能先预留它们。批量删除和更新这是另一个经常被忽视的技巧，如果操作不当，从大表中删除或更新大量数据可能是一场噩梦。问题是，这两个语句作为单个事务运行。如果你需要敲定它们，或者系统在执行它们时遇到问题，系统不得不回滚整个事务，这需要很长时间。这些操作还会在持续时间内阻塞其他事务，这实际上给系统带来了瓶颈。解决方法是小批量删除或更新。这从几个方面解决了这个问题：无论事务终止的原因是什么，它只有少量的行要回滚，所以数据库恢复联机的速度要快得多。当小批量事务提交到磁盘时，其他事务可以进来做一些工作，从而大大提高并发性。还有，很多开发者一直固执地认为这些删除和更新操作必须在同一天完成。情况并非总是如此，尤其是在您提交申请时。如果你需要延长操作，你可以这样做，小批量有助于实现这一点；如果您需要更长的时间来执行这些密集型操作，请不要降低系统速度。使用临时表提高游标性能尽可能避免使用游标。游标不仅有速度问题，速度问题本身就是很多操作的大问题，还会导致你的操作长时间阻塞其他操作，大大降低系统的并发性。但是游标的使用总是不可避免的，当无法避免游标时，可以通过对临时表进行游标操作来代替游标带来的性能问题。例如，考虑一个查找表并根据某些比较更新一些列的游标。您可以将该数据放入临时表中，并与临时表而不是实时表进行比较。然后，您可以对锁定时间非常短的小得多的活动表运行单个UPDATE语句。进行此类数据修改可以大大提高并发性。***我会说你根本不需要使用游标，总会有一个基于集合的解决方法。不嵌套视图Views可能比较方便，但是在使用视图的时候要小心。虽然视图有助于屏蔽用户的大型查询和标准化数据访问，但您很容易发现自己陷入了这个陷阱：视图A调用视图B，视图B调用视图C，视图C调用视图D，后者称为嵌套视图。这会导致严重的性能问题，尤其是在两个方面：返回的数据很可能比您需要的多得多。查询优化器将放弃并返回一个错误的查询计划。我有一些喜欢嵌套视图的客户，这个几乎所有数据都有一个视图，因为它有两个重要的连接。问题是视图返回一个包含2MB文档的列，有些文档甚至更大。该客户端几乎在每个运行的查询中为每一行推送至少2MB的数据。自然，查询性能很差。没有一个查询实际使用该列！当然，柱子深埋七景，难寻。从视图中删除文档列后，***查询从2.5小时缩短到10分钟。我逐层展开嵌套视图（带有一些不必要的连接和列）并编写了一个普通查询，这也花费了不到1秒的时间。使用表值函数这是我一直以来最喜欢的技巧之一，因为这是只有专家才知道的秘密。当在查询的SELECT列表中使用标量函数时，会为结果集中的每一行调用该函数，这会显着降低大型查询的性能。但是，可以将标量函数转换为表值函数，然后在查询中使用CROSSAPPLY，可以大大提高性能。这个绝妙的技巧可以显着提高性能。使用分区避免移动大数据并不是每个人都可以利用SQLServerEnterprise中依赖分区的这一技巧，但对于那些可以的人来说，这是一个很好的技巧。大多数人没有意识到SQLServer中的所有表都是分区的。如果愿意，您可以将表拆分为多个分区，但即使是简单的表从创建之时起也会进行分区。但是，它们是作为单个分区创建的。如果您正在运行SQLServerEnterprise，您已经可以轻松利用分区表。这意味着您可以使用SWITCH等分区功能来归档从仓库加载的大量数据。举个实际的例子，去年遇到这样一个客户：客户需要将当天的表数据复制到归档表中；这样如果加载失败，公司可以快速使用当天的表进行恢复。由于种种原因，不可能每次都改表名，所以公司每天在加载前往archive表中插入数据，然后从active表中删除当天的数据。刚开始还算顺利，但一年下来，每张表抄1.5小时，每天好几张，问题越来越严重。解决方案是放弃INSERT和DELETE进程并使用SWITCH命令。SWITCH命令允许公司避免所有写入，因为它将页面分配给存档表。这只是改变了元数据。SWITCH平均运行两三秒。如果当前加载失败，可以通过SWITCH将数据切换回原表。如果一定要用ORM，就用存储过程ORM是我经常炮轰的对象之一。简而言之，不要使用ORM（对象关系映射器）。ORM生成世界上最糟糕的代码，几乎是我遇到的所有性能问题的罪魁祸首。ORM代码生成器不太可能像知道自己在做什么的人那样编写好SQL。但是，如果您使用ORM，请编写您自己的存储过程并让ORM调用存储过程，而不是编写您自己的查询。我知道使用ORM的所有原因，而且我知道开发人员和管理人员喜欢ORM，因为它们有助于将产品快速推向市场。但是如果你看看查询对数据库做了什么，它是非常昂贵的。存储过程有很多优点，首先，您通过网络传输的数据要少得多。如果您有一个长查询，整个查询可能需要通过网络进行三到四次往返才能到达数据库服务器。这不包括服务器将查询重新放在一起并运行它所花费的时间；还要考虑这一点：查询可能每秒运行几次或数百次。使用存储过程可以大大减少传输的流量，因为存储过程调用总是短得多。此外，存储过程更容易在Profiler或其他软件中进行跟踪。存储过程是数据库中的实际对象，这意味着获取存储过程的性能统计信息比临时查询要容易得多，因此更容易发现性能问题和查明异常。此外，存储过程参数化更加一致，这意味着您更有可能重用执行计划，甚至处理缓存问题，这对于临时查询来说很难查明。使用存储过程，可以更轻松地处理边缘情况，甚至可以添加审计或更改锁定行为。存储过程可以处理困扰即席查询的许多任务。几年前，我妻子整理了一个两页的EntityFramework查询，运行需要25分钟。她简化了复杂性并将这个大型查询重写为SELECTCOUNT(*)fromT1，这不是开玩笑。主要就这些，我知道很多.NET程序员认为业务逻辑不适合放在数据库中，这是非常错误的。如果把业务逻辑放在应用的前端，所有的数据都必须传输只是为了比较，性能不是很好。我有一个客户，他将所有逻辑都保存在数据库之外，并在前端处理所有事情。公司将数千行数据发送到前端，以便应用业务逻辑并显示所需的数据。这个过程花了40分钟，我把存储过程放在后端，让前端调用；页面在三秒钟内加载。当然，有时逻辑在前端是合适的，有时在数据库是合适的，但ORM总是让我生气。不要在同一批次中对许多表执行大型操作这似乎很明显，但事实并非如此。我将使用另一个实例，因为它更具说明性。我有一个严重阻塞的系统，许多操作停滞不前。发现每天运行几次的删除例程在显式事务中删除了14个表的数据。在一个事务中处理所有14个表意味着锁定每个表，直到所有删除完成。解决方案是将每个表删除分解成一个单独的事务，使每个删除事务只锁定一个表。这会释放其他表，解除阻塞，并允许其他操作继续进行。您应该始终将此类大交易分解为单独的小交易以防止阻塞。不要使用触发器这个与前一个大致相同，但仍然值得一提。触发器的问题：无论你想让触发器做什么，它都会在与原始操作相同的事务中完成。如果您编写一个触发器，在更新Orders表中的一行时将数据插入另一个表，则两个表都将被锁定，直到触发器完成执行。如果需要在更新后向另一个表中插入数据，将更新和插入放入存储过程中，并在单独的事务中执行。如果需要回滚，不用锁住两个表也很容易回滚。一如既往，保持事务简短并且一次不要锁定多个资源。不要聚集在GUID这么多年之后，我不敢相信我们仍然在为这个问题而苦苦挣扎。但我仍然每年至少遇到两次GUID集群。一个GUID（GloballyUniqueIdentifier）是一个16字节的随机生成的数字。按此列对表中的数据进行排序比使用稳定增加的值（例如DATE或IDENTITY）更快地对表进行分段。几年前我做了一个基准测试，我将一堆数据插入到一个具有聚集GUID的表中，并将相同的数据插入到另一个具有IDENTITY列的表中。GUID表碎片非常严重，仅15分钟后，性能下降了数千个百分点。5小时后，IDENTITY表的性能仅下降了几个百分点，这不仅适用于GUID，适用于任何易变列。如果您只需要检查数据是否存在，请不要计算行数。您经常需要检查数据是否存在于表中，并根据检查结果执行某些操作。我经常看到人们做SELECTCOUNT(*)FROMdbo.T1来检查数据是否存在：SET@CT=(SELECTCOUNT(*)FROMdbo.T1);If@CT>0BEGIN
