如何让你的SQL运行得更快.docx

1、如何让你的SQL运行得更快案例学习：如何让你的SQL运行得更快作者: , 出处:博客,责任编辑: jinpu, 2006-11-24 08:00人们在使用SQL时往往会陷入一个误区，即太关注于所得的结果是否正确，而忽略了不同的实现方法之间可能存在的性能差异，这种性能差异在大型的或是复杂的数据库环境中(如联机事务处理OLTP或决策支持系统DSS)中表现得尤为明显。笔者在工作实践中发现，不良的SQL往往来自于不恰当的索引设计、不充份的连接条件和不可优化的where子句。在对它们进行适当的优化后，其运行速度有了明显地提高!下面我将从这三个方面分别进行总结: 人们在使用SQL时往往会陷入一个误区，即太

2、关注于所得的结果是否正确，而忽略了不同的实现方法之间可能存在的性能差异，这种性能差异在大型的或是复杂的数据库环境中(如联机事务处理OLTP或决策支持系统DSS)中表现得尤为明显。笔者在工作实践中发现，不良的SQL往往来自于不恰当的索引设计、不充份的连接条件和不可优化的where子句。在对它们进行适当的优化后，其运行速度有了明显地提高!下面我将从这三个方面分别进行总结:为了更直观地说明问题，所有实例中的SQL运行时间均经过测试，不超过1秒的均表示为(19991201anddate2000(25秒)selectdate,sum(amount)fromrecordgroupbydate(55秒)se

3、lectcount(*)fromrecordwheredate19990901andplacein(BJ,SH)(27秒)分析:date上有大量的重复值，在非群集索引下，数据在物理上随机存放在数据页上，在范围查找时，必须执行一次表扫描才能找到这一范围内的全部行。2.在date上的一个群集索引selectcount(*)fromrecordwheredate19991201anddate2000（14秒）selectdate,sum(amount)fromrecordgroupbydate（28秒）selectcount(*)fromrecordwheredate19990901andplace

4、in(BJ,SH)（14秒）分析:在群集索引下，数据在物理上按顺序在数据页上，重复值也排列在一起，因而在范围查找时，可以先找到这个范围的起末点，且只在这个范围内扫描数据页，避免了大范围扫描，提高了查询速度。3.在place，date，amount上的组合索引selectcount(*)fromrecordwheredate19991201anddate2000（26秒）selectdate,sum(amount)fromrecordgroupbydate（27秒）selectcount(*)fromrecordwheredate19990901andplacein(BJ,SH)（1999120

5、1anddate2000(19990901andplacein(BJ,SH)（,=, =)和order by、group by发生的列，可考虑建立群集索引;.经常同时存取多列，且每列都含有重复值可考虑建立组合索引;.组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。二、不充份的连接条件:例:表card有7896行，在card_no上有一个非聚集索引，表account有191122行，在account_no上有一个非聚集索引，试看在不同的表连接条件下，两个SQL的执行情况:selectsum(a.amount)fromaccounta,cardbwherea.card_no=b

6、.card_no（20秒）将SQL改为:selectsum(a.amount)fromaccounta,cardbwherea.card_no=b.card_noanda.account_no=b.account_no（1秒）分析:在第一个连接条件下，最佳查询方案是将account作外层表，card作内层表，利用card上的索引，其I/O次数可由以下公式估算为:外层表account上的22541页+(外层表account的191122行*内层表card上对应外层表第一行所要查找的3页)=595907次I/O 在第二个连接条件下，最佳查询方案是将card作外层表，account作内层表，利用ac

7、count上的索引，其I/O次数可由以下公式估算为:外层表card上的1944页+(外层表card的7896行*内层表account上对应外层表每一行所要查找的4页)= 33528次I/O可见，只有充份的连接条件，真正的最佳方案才会被执行。总结:1.多表操作在被实际执行前，查询优化器会根据连接条件，列出几组可能的连接方案并从中找出系统开销最小的最佳方案。连接条件要充份考虑带有索引的表、行数多的表;内外表的选择可由公式:外层表中的匹配行数*内层表中每一次查找的次数确定，乘积最小为最佳方案。2.查看执行方案的方法- 用set showplanon，打开showplan选项，就可以看到连接顺序、使用

8、何种索引的信息;想看更详细的信息，需用sa角色执行dbcc(3604,310,302)。三、不可优化的where子句1.例:下列SQL条件语句中的列都建有恰当的索引，但执行速度却非常慢:select*fromrecordwheresubstring(card_no,1,4)=5378(13秒)select*fromrecordwhereamount/301000（11秒）select*fromrecordwhereconvert(char(10),date,112)=19991201（10秒）分析:where子句中对列的任何操作结果都是在SQL运行时逐列计算得到的，因此它不得不进行表搜索，而没

9、有使用该列上面的索引;如果这些结果在查询编译时就能得到，那么就可以被SQL优化器优化，使用索引，避免表搜索，因此将SQL重写成下面这样:select*fromrecordwherecard_nolike5378%（1秒）select*fromrecordwhereamount1000*30（1秒）select*fromrecordwheredate=1999/12/01（1001) OR order_num=1008虽然在customer_num和order_num上建有索引，但是在上面的语句中优化器还是使用顺序存取路径扫描整个表。因为这个语句要检索的是分离的行的集合，所以应该改为如下语句:S

10、ELECTFROMordersWHEREcustomer_num=104ANDorder_num1001UNIONSELECTFROMordersWHEREorder_num=1008这样就能利用索引路径处理查询。4.避免相关子查询一个列的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。5.避免困难的正规表达式MATCHES和LIKE关键字支持通配符匹配，技术上叫正规表达式。但这种匹配特别耗费时间。例如:SELECT * FRO

11、M customer WHERE zipcode LIKE “98_ _ _”即使在zipcode字段上建立了索引，在这种情况下也还是采用顺序扫描的方式。如果把语句改为SELECT * FROM customer WHERE zipcode “98000”，在执行查询时就会利用索引来查询，显然会大大提高速度。另外，还要避免非开始的子串。例如语句:SELECT * FROM customer WHERE zipcode2，3 “80”，在where子句中采用了非开始子串，因而这个语句也不会使用索引。6.使用临时表加速查询把表的一个子集进行排序并创建临时表，有时能加速查询。它有助于避免多重排序操作

12、，而且在其他方面还能简化优化器的工作。例如:SELECTcust.name，rcvbles.balance，othercolumnsFROMcust，rcvblesWHEREcust.customer_id=rcvlbes.customer_idANDrcvblls.balance0ANDcust.postcode“98000”ORDERBYcust.name如果这个查询要被执行多次而不止一次，可以把所有未付款的客户找出来放在一个临时文件中，并按客户的名字进行排序:SELECTcust.name，rcvbles.balance，othercolumnsFROMcust，rcvblesWHERE

13、cust.customer_id=rcvlbes.customer_idANDrcvblls.balance0ORDERBYcust.nameINTOTEMPcust_with_balance然后以下面的方式在临时表中查询:SELECTFROMcust_with_balanceWHEREpostcode“98000”临时表中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘I/O，所以查询工作量可以得到大幅减少。注意:临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下，注意不要丢失数据。7.用排序来取代非顺序存取非顺序磁盘存取是最慢的操作，表现在磁盘存取臂的来回移动。SQ

14、L语句隐藏了这一情况，使得我们在写应用程序时很容易写出要求存取大量非顺序页的查询。有些时候，用数据库的排序能力来替代非顺序的存取能改进查询。3.优化 tempdb 性能对 tempdb 数据库的物理位置和数据库选项设置的一般建议包括:使 tempdb 数据库得以按需自动扩展。这确保在执行完成前不终止查询，该查询所生成的存储在 tempdb 数据库内的中间结果集比预期大得多。将 tempdb 数据库文件的初始大小设置为合理的大小，以避免当需要更多空间时文件自动扩展。如果 tempdb 数据库扩展得过于频繁，性能会受不良影响。将文件增长增量百分比设置为合理的大小，以避免 tempdb 数据库文件按

15、太小的值增长。如果文件增长幅度与写入 tempdb 数据库的数据量相比太小，则 tempdb 数据库可能需要始终扩展，因而将妨害性能。将 tempdb 数据库放在快速 I/O 子系统上以确保好的性能。在多个磁盘上条带化 tempdb 数据库以获得更好的性能。将 tempdb 数据库放在除用户数据库所使用的磁盘之外的磁盘上。有关更多信息，请参见扩充数据库。4.优化服务器:使用内存配置选项优化服务器性能Microsoft SQL Server 2000 的内存管理组件消除了对 SQL Server 可用的内存进行手工管理的需要。SQL Server 在启动时根据操作系统和其它应用程序当前正在使用的

16、内存量，动态确定应分配的内存量。当计算机和SQL Server 上的负荷更改时，分配的内存也随之更改。有关更多信息，请参见内存构架。下列服务器配置选项可用于配置内存使用并影响服务器性能:min server memorymax server memorymax worker threadsindex create memorymin memory per querymin server memory 服务器配置选项可用于确保 SQL Server 在达到该值后不会释放内存。可以基于 SQL Server 的大小及活动将该配置选项设置为特定的值。如果选择设置此选项，必须为操作系统和其他程序留出足

17、够的内存。如果操作系统没有足够的内存，会向 SQL Server 请求内存，从而导致影响 SQL Server 性能。max server memory 服务器配置选项可用于:在 SQL Server 启动及运行时，指定 SQL Server 可以分配的最大内存量。如果知道有多个应用程序与 SQL Server 同时运行，而且想保障这些应用程序有足够的内存运行，可以将该配置选项设置为特定的值。如果这些其它应用程序(如 Web 服务器或电子邮件服务器)只根据需要请求内存，则 SQL Server 将根据需要给它们释放内存，因此不要设置 max server memory 服务器配置选项。然而，应

18、用程序通常在启动时不假选择地使用可用内存，而如果需要更多内存也不请求。如果有这种行为方式的应用程序与 SQL Server 同时运行在相同的计算机上，则将 max server memory 服务器配置选项设置为特定的值，以保障应用程序所需的内存不由 SQL Server 分配出。不要将 min server memory 和 max server memory 服务器配置选项设置为相同的值，这样做会使分配给 SQL Server 的内存量固定。动态内存分配可以随时间提供最佳的总体性能。有关更多信息，请参见服务器内存选项。max worker threads 服务器配置选项可用于指定为用户连接

19、到 SQL Server 提供支持的线程数。255 这一默认设置对一些配置可能稍微偏高，这要具体取决于并发用户数。由于每个工作线程都已分配，因此即使线程没有正在使用(因为并发连接比分配的工作线程少)，可由其它操作(如高速缓冲存储器)更好地利用的内存资源也可能是未使用的。一般情况下，应将该配置值设置为并发连接数，但不能超过 32727。并发连接与用户登录连接不同。SQL Server 实例的工作线程池只需要足够大，以便为同时正在该实例中执行批处理的用户连接提供服务。如果增加工作线程的数量超过默认值，会降低服务器性能。有关更多信息，请参见max worker threads 选项。说明 当 SQL

20、 Server 运行在 Microsoft Windows 98 上时，最大工作线程服务器配置选项不起作用。index create memory 服务器配置选项控制创建索引时排序操作所使用的内存量。在生产系统上创建索引通常是不常执行的任务，通常调度为在非峰值时间执行的作业。因此，不常创建索引且在非峰值时间时，增加该值可提高索引创建的性能。不过，最好将 min memory per query 配置选项保持在一个较低的值，这样即使所有请求的内存都不可用，索引创建作业仍能开始。有关更多信息，请参见 index create memory 选项。min memory per query 服务器配置选项可用于指定分配给查询执行的最小内存量。当系统内有许多查询并发执行时，增大 min memory per query 的值有助于提高消耗大量内存的查询(如大型排序和哈希操作)的性能。不过，不要将 min memory per query 服务器配置选项设置得太高，尤其是在很忙的系统上，因为