五种提高 SQL 性能的方法(3)
作者:Johnny Papa 来源:csdn 发布时间:2008-05-16 10:40:00
避免 LEFT JOIN 和 NULL
当然,有很多时候您需要执行 LEFT JOIN 和使用 NULL 值。但是,它们并不适用于所有情况。改变 SQL 查询的构建方式可能会产生将一个花几分钟运行的报告缩短到只花几秒钟这样的天壤之别的效果。有时,必须在查询中调整数据的形态,使之适应应用程序所要求的显示方式。虽然 TABLE 数据类型会减少大量占用资源的情况,但在查询中还有许多区域可以进行优化。SQL 的一个有价值的常用功能是 LEFT JOIN。它可以用于检索第一个表中的所有行、第二个表中所有匹配的行、以及第二个表中与第一个表不匹配的所有行。例如,如果希望返回每个客户及其定单,使用 LEFT JOIN 则可以显示有定单和没有定单的客户。
此工具可能会被过度使用。LEFT JOIN 消耗的资源非常之多,因为它们包含与 NULL(不存在)数据匹配的数据。在某些情况下,这是不可避免的,但是代价可能非常高。LEFT JOIN 比 INNER JOIN 消耗资源更多,所以如果您可以重新编写查询以使得该查询不使用任何 LEFT JOIN,则会得到非常可观的回报(请参阅图 1 中的图)。
图 1 查询
加快使用 LEFT JOIN 的查询速度的一项技术涉及创建一个 TABLE 数据类型,插入第一个表(LEFT JOIN 左侧的表)中的所有行,然后使用第二个表中的值更新 TABLE 数据类型。此技术是一个两步的过程,但与标准的 LEFT JOIN 相比,可以节省大量时间。一个很好的规则是尝试各种不同的技术并记录每种技术所需的时间,直到获得用于您的应用程序的执行性能最佳的查询。
测试查询的速度时,有必要多次运行此查询,然后取一个平均值。因为查询(或存储过程)可能会存储在 SQL Server 内存中的过程缓存中,因此第一次尝试耗费的时间好像稍长一些,而所有后续尝试耗费的时间都较短。另外,运行您的查询时,可能正在针对相同的表运行其他查询。当其他查询锁定和解锁这些表时,可能会导致您的查询要排队等待。例如,如果您进行查询时某人正在更新此表中的数据,则在更新提交时您的查询可能需要耗费更长时间来执行。
避免使用 LEFT JOIN 时速度降低的最简单方法是尽可能多地围绕它们设计数据库。例如,假设某一产品可能具有类别也可能没有类别。如果 Products 表存储了其类别的 ID,而没有用于某个特定产品的类别,则您可以在字段中存储 NULL 值。然后您必须执行 LEFT JOIN 来获取所有产品及其类别。您可以创建一个值为“No Category”的类别,从而指定外键关系不允许 NULL 值。通过执行上述操作,现在您就可以使用 INNER JOIN 检索所有产品及其类别了。虽然这看起来好像是一个带有多余数据的变通方法,但可能是一个很有价值的技术,因为它可以消除 SQL 批处理语句中消耗资源较多的 LEFT JOIN。在数据库中全部使用此概念可以为您节省大量的处理时间。请记住,对于您的用户而言,即使几秒钟的时间也非常重要,因为当您有许多用户正在访问同一个联机数据库应用程序时,这几秒钟实际上的意义会非常重大
灵活使用笛卡尔乘积
对于此技巧,我将进行非常详细的介绍,并提倡在某些情况下使用笛卡尔乘积。出于某些原因,笛卡尔乘积 (CROSS JOIN) 遭到了很多谴责,开发人员通常会被警告根本就不要使用它们。在许多情况下,它们消耗的资源太多,从而无法高效使用。但是像 SQL 中的任何工具一样,如果正确使用,它们也会很有价值。例如,如果您想运行一个返回每月数据(即使某一特定月份客户没有定单也要返回)的查询,您就可以很方便地使用笛卡尔乘积。 图 2 中的 SQL 就执行了上述操作。
虽然这看起来好像没什么神奇的,但是请考虑一下,如果您从客户到定单(这些定单按月份进行分组并对销售额进行小计)进行了标准的 INNER JOIN,则只会获得客户有定单的月份。因此,对于客户未订购任何产品的月份,您不会获得 0 值。如果您想为每个客户都绘制一个图,以显示每个月和该月销售额,则可能希望此图包括月销售额为 0 的月份,以便直观标识出这些月份。如果使用 图 2 中的 SQL,数据则会跳过销售额为 0 * 份,因为在定单表中对于零销售额不会包含任何行(假设您只存储发生的事件)。
图 3 中的代码虽然较长,但是可以达到获取所有销售数据(甚至包括没有销售额的月份)的目标。首先,它会提取去年所有月份的列表,然后将它们放入第一个 TABLE 数据类型表 (@tblMonths) 中。下一步,此代码会获取在该时间段内有销售额的所有客户公司的名称列表,然后将它们放入另一个 TABLE 数据类型表 (@tblCus-tomers) 中。这两个表存储了创建结果集所必需的所有基本数据,但实际销售数量除外。 第一个表中列出了所有月份(12 行),第二个表中列出了这个时间段内有销售额的所有客户(对于我是 81 个)。并非每个客户在过去 12 个月中的每个月都购买了产品,所以,执行 INNER JOIN 或 LEFT JOIN 不会返回每个月的每个客户。这些操作只会返回购买产品的客户和月份。
笛卡尔乘积则可以返回所有月份的所有客户。笛卡尔乘积基本上是将第一个表与第二个表相乘,生成一个行集合,其中包含第一个表中的行数与第二个表中的行数相乘的结果。因此,笛卡尔乘积会向表 @tblFinal 返回 972 行。最后的步骤是使用此日期范围内每个客户的月销售额总计更新 @tblFinal 表,以及选择最终的行集。
如果由于笛卡尔乘积占用的资源可能会很多,而不需要真正的笛卡尔乘积,则可以谨慎地使用 CROSS JOIN。例如,如果对产品和类别执行了 CROSS JOIN,然后使用 WHERE 子句、DISTINCT 或 GROUP BY 来筛选出大多数行,那么使用 INNER JOIN 会获得同样的结果,而且效率高得多。如果需要为所有的可能性都返回数据(例如在您希望使用每月销售日期填充一个图表时),则笛卡尔乘积可能会非常有帮助。但是,您不应该将它们用于其他用途,因为在大多数方案中 INNER JOIN 的效率要高得多。
拾遗补零
这里介绍其他一些可帮助提高 SQL 查询效率的常用技术。假设您将按区域对所有销售人员进行分组并将他们的销售额进行小计,但是您只想要那些数据库中标记为处于活动状态的销售人员。您可以按区域对销售人员分组,并使用 HAVING 子句消除那些未处于活动状态的销售人员,也可以在 WHERE 子句中执行此操作。在 WHERE 子句中执行此操作会减少需要分组的行数,所以比在 HAVING 子句中执行此操作效率更高。HAVING 子句中基于行的条件的筛选会强制查询对那些在 WHERE 子句中会被去除的数据进行分组。
另一个提高效率的技巧是使用 DISTINCT 关键字查找数据行的单独报表,来代替使用 GROUP BY 子句。在这种情况下,使用 DISTINCT 关键字的 SQL 效率更高。请在需要计算聚合函数(SUM、COUNT、MAX 等)的情况下再使用 GROUP BY。另外,如果您的查询总是自己返回一个唯一的行,则不要使用 DISTINCT 关键字。在这种情况下,DISTINCT 关键字只会增加系统开销。
您已经看到了,有大量技术都可用于优化查询和实现特定的业务规则,技巧就是进行一些尝试,然后比较它们的性能。最重要的是要测试、测试、再测试。在此专栏的将来各期内容中,我将继续深入讲述 SQL Server 概念,包括数据库设计、好的索引实践以及 SQL Server 安全范例。
猜你喜欢
- 字符串的IndexOf()方法搜索在该字符串上是否出现了作为参数传递的字符串,如果找到字符串,则返回字符的起始位置 (0表示第一个字符,1表
- 1. 用Dreamweaver 4.0制作闪动的Flash按钮选择菜单Insert→Interactive Images→Flash But
- 快照复制是在数据库之间对数据以及数据库对象进行复制并进行同步,以确保多个数据库之间一致性的一个法宝。简单的说,快照复制就是实现把一个数据库服
- 是在客户端确认还是在服务器端确认? <SCRIPT LANGUAGE="VBSc
- 年前接到QCon的邀请,颇感意外。在我的印象里,QCon大会是后端开发工程师和架构师的技术大会。后来去QCon大会的官网搜索了下,发现原来Q
- win7 +Navicat Lite 9+ VMware7在VMware中安装openSUSE11.x mysql5 Navicat Lit
- 一直很想做这个效果,原理是监听鼠标滚轮事件;可将此效果继续发散到其他应用上,如图片缩放,页面缩放等。演示:<!DOCTYPE html
- 如何提高ASP的效率?通过修改注册表来提高asp的执行效率 改的第一个地方:HKEY_LOCAL_MAC
- 问题:导入数据时是否需要禁用索引?解答:在使用load data导入数据时,如果导入的表是MyISAM的,对于空表,MySQL数据库会把所有
- 站长们是不是还在为空间不支持域名绑定到子目录而发愁呢?买了个便宜也不错的空间,用的还满意,准备再开几个网站,却发现空间程序太落后,无法支持域
- Opera所属:挪威Opera Software ASA公司渲染引擎:自家的PrestoOpera起初是一款挪威Oper
- 以下测试用于去除任何字符串中连线的分隔符 代码如下:--去除字符串中连续的分隔符 declare @str nvarchar(200) de
- 可以说,互联网就是由一个个链接进行信息流通的。没有连接的网站如同一潭死水,毫无生气。当一个访客进入你的网站,在浏览的过程中,如果你的内容不引
- 函数可以参考:<% '注册论坛用户,参数说明 'username 用户登录名称 
- 调用bianli()函数,这里是遍历F:盘,bianli("F:\")'这里设置文件夹路径,你可以改为其它文件夹
- 为某国税局开发一综合 * ,需要建立一个庞大的国税系统通讯录数据库,从各省、市到每名具体工作人员,项目较多,该如何设计各表呢?数据库的表设计
- 瞬间设计是什么?良好的用户体验,全在于那些完美的瞬间。在第一个瞬间,假设当一位用户从购物搜索结果页面跳转到某个店铺的时候,他此刻可能是想看看
- think-queue是ThinkPHP官方提供的一个消息队列服务,是专门支持队列服务的扩展包。think-queue消息队列适用于大并发或
- 也许还有朋友不太清楚DOMContentLoaded这个事件。简单的说,这个事件就是要在大多数情况下去替代window.onload事件,因
- 什么是冗余字段?在设计数据库时,某一字段属于一个表,但它又同时出现在另一个或多个表,且完全等同于它在其本来所属表的意义表示,那么这个字段就是