SQL语句执行效率.docx

《SQL语句执行效率.docx》由会员分享，可在线阅读，更多相关《SQL语句执行效率.docx（8页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、编号：时间：2021年x月x日书山有路勤为径，学海无涯苦作舟页码：第8页 共8页SQL语句执行效率SQL语句中，IN、EXISTS、NOT IN、NOT EXISTS的效率较低，尤其是后两种语句，当数据量较大时，更常给人一种死机般的感觉。本文提供一种使用连接的方法代替以上的四种语句，可大副提高SQL语句的运行效率。以NOT IN为例，当数据量达到一万时，效率可提高20倍，数据量越大，效率提高的幅度也就越大。1)SELECT语句中的中的效率提高方法 SQL语句如下：CREATE TABLE TAB1(COL1 VARCHAR(20) NOT NULL,COL2 INTEGER，PRIMARY K

2、EY(COL1);CREATE TABLE TAB2(COL1 VARCHAR(20) NOT NULL,PRIMARY KEY(COL1);CREATE TABLE TAB3(COL1 VARCHAR(20) NOT NULL,PRIMARY KEY(COL1);下面，我们将举2个例子来具体说明使用连接替换IN、NOT IN、EXISTS、NOT EXISTS的方法。读取表1中第2列（COL2）数据的总和，且其第1列数据存在于表2的第1列中。1. 使用IN的SQL语句：SELECT SUM(COL2) FROM TAB1 WHERE COL1 IN(SELECT COL1 FROM TAB2

3、)2. 使用EXISTS的SQL语句：SELECT SUM(COL2) FROM TAB1 WHERE EXISTS(SELECT * FROM TAB2 WHERE TAB1.COL1=TAB2.COL1)3. 使用连接的SQL语句：SELECT SUM(A.COL2) FROM TAB1 A,TAB2 B WHERE A.COL1=B.COL1读取表1中第2列（COL2）数据的总和，且其第1列数据不存在于表2的第1列中。1. 使用NOT IN的SQL语句：SELECT SUM(COL2) FROM TAB1 WHERE COL1 NOT IN(SELECT COL1 FROM TAB2)2

4、. 使用NOT EXISTS的SQL语句：SELECT SUM(COL2) FROM TAB1 WHERE NOT EXISTS(SELECT * FROM TAB2 WHERE TAB1.COL1=TAB2.COL1)3. 使用外连接的SQL语句：SELECT SUM(A.COL2) FROM TAB1 A,TAB2 B WHERE A.COL1=B.COL1(+) AND B.COL1 IS NULL2) DELETE语句中的效率提高方法 SQL语句如下： CREATE TABLE TA(CA INT)CREATE TABLE TB(CA INT)CREATE TABLE TC(CA IN

5、T)1 用IN的SQL语句：DELETE FROM TA WHERE TA.CA IN (SELECT CA FROM TB)2 用EXISTS的SQL语句：DELETE FROM TA WHERE EXISTS (SELECT * FROM TB WHERE TB.CA=TA.CA)3 使用连接的SQL语句：DELETE TA FROM TA,TB WHERE TA.CA=TB.CA删除表A中表A存在但表B中不存在的数据1. 使用IN的SQL语句：DELETE FROM TA WHERE TA.CA NOT IN (SELECT CA FROM TB)2. 使用EXISTS的SQL语句：DE

6、LETE FROM TA WHERE NOT EXISTS (SELECT CA FROM TB WHERE TB.CA=TA.CA)3. 使用连接的SQL语句：DELETE TA FROM TA LEFT OUTER JOIN TB ON TA.CA=TB.CA WHERE TB.CA IS NULL 3) UPDATE语句中的效率提高方法更新表A中表A和表B相同的数据1. 使用IN的SQL语句：UPDATE TA SET CA=CA+10000 WHERE CA IN (SELECT CA FROM TB)2. 使用EXISTS的SQL语句：UPDATE TA SET CA=CA+1000

7、0 WHERE EXISTS (SELECT CA FROM TB WHERE TB.CA=TA.CA)3. 使用连接的SQL语句：UPDATE TA SET TA.CA=TA.CA+10000 FROM TA,TB WHERE TA.CA=TB.CA更新表A中表A存在但表B中不存在的数据1. 使用IN的SQL语句：UPDATE TA SET CA=CA+10000 WHERE CA NOT IN (SELECT CA FROM TB)2. 使用EXISTS的SQL语句：UPDATE TA SET CA=CA+10000 WHERE NOT EXISTS (SELECT CA FROM TB

8、WHERE TB.CA=TA.CA)3. 使用连接的SQL语句：UPDATE TA SET TA.CA=TA.CA+10000 FROM TA LEFT OUTER JOIN TB ON TA.CA=TB.CA WHERE TB.CA IS NULL=方法一、尽量使用复杂的SQL来代替简单的一堆 SQL. 同样的事务，一个复杂的SQL完成的效率高于一堆简单SQL完成的效率。有多个查询时，要善于使用JOIN。 oRs=oConn.Execute(SELECT * FROM Books) while not oRs.Eof strSQL = SELECT * FROM Authors WHERE

9、AuthorID=&oRs(AuthorID) oRs2=oConn.Execute(strSQL) Response.write oRs(Title)&oRs2(Name)&q uot;oRs.MoveNext() wend 要比下面的代码慢： strSQL=SELECT Books.Title,Authors.Name FROM Books JOIN Authors ON Authors.AuthorID=Books.AuthorID oRs=oConn.Execute(strSQL) while not oRs.Eof Response.write oRs(Title)&oRs(Name

10、)&qu ot; oRs.MoveNext() wend 方法二、尽量避免使用可更新 Recordset oRs=oConn.Execute(SELECT * FROM Authors WHERE AuthorID=17,3,3)oRs(Name)=DarkManoRs.Update()要比下面的代码慢：strSQL = UPDATE Authors SET Name=DarkMan WHERE AuthorID=17oConn.Execute strSQL方法三、更新数据库时，尽量采用批处 理更新将所有的SQL组成一个大的批处理SQL，并一次运行；这比一个一个地更新数据要有效率得多。这样也更

11、加满足你进行事务处理 的需要：strSQL=strSQL=strSQL&SET XACT_ABORT ONn;strSQL=strSQL&BEGIN TRANSACTIONn;strSQL=strSQL&INSERT INTO Orders(OrdID,CustID,OrdDat) VALUES(9999,1234,GETDATE()n;strSQL=strSQL&INSERT INTO OrderRows(OrdID,OrdRow,Item,Qty) VALUES(9999,01,G4385,5)n;strSQL=strSQL&INSERT INTO OrderRows(OrdID,OrdR

12、ow,Item,Qty) VALUES(9999,02,G4726,1)n;strSQL=strSQL&COMM99v TRANSACTIONn;strSQL=strSQL&SET XACT_ABORT OFFn;oConn.Execute(strSQL);其中，SET XACT_ABORT OFF 语句告诉SQL Server，如果下面的事务处理过程中，如果遇到错误，就取消已经完成的事务。方法四、数据库索引那些将在Where子句中出现的字段，你应该首先考虑建立索引；那些需要排序的字段，也应该在考虑之列 。在MS Access中建立索引的方法：在Access里面选择需要索引的表，点击“设计”，

13、然后设置相应字段的索引.在MS SQL Server中建立索引的方法：在SQL Server管理器中，选择相应的表，然后“设计表”，点击右键，选择“Properties”，选择“indexes/keys”方法五、避免使Text字段太大 当字符串的值大小不固定时，用varchar比用char的效果要好 些。我曾经看到一个例子程序，字段被定义为TEXT(255)，但是他的取值经常只有20个字符。这个数据表有50k个记录，从而使这慢 =- 人们在使用SQL时往往会陷入一个误区，即太关注于所得的结果是否正确，而忽略了不同的实现方法之间可能存在的性能差异，这种性能差异在大型的或是复杂的数据库环境中（如联

14、机事务处理OLTP或决策支持系统DSS）中表现得尤为明显。笔者在工作实践中发现，不良的SQL往往来自于不恰当的索引设计、不充份的连接条件和不可优化的where子句。在对它们进行适当的优化后，其运行速度有了明显地提高！下面我将从这三个方面分别进行总结： - 为了更直观地说明问题，所有实例中的SQL运行时间均经过测试，不超过秒的均表示为（19991201 and date 2000 (25秒)select date,sum(amount) from record group by date(55秒)select count(*) from record where date 19990901 an

15、d place in (BJ,SH) (27秒)- 分析：-date上有大量的重复值，在非群集索引下，数据在物理上随机存放在数据页上，在范围查找时，必须执行一次表扫描才能找到这一范围内的全部行。- 2.在date上的一个群集索引select count(*) from record where date 19991201 and date 2000 （14秒）select date,sum(amount) from record group by date（28秒）select count(*) from record where date 19990901 and place in (BJ,

16、SH)（14秒）- 分析：- 在群集索引下，数据在物理上按顺序在数据页上，重复值也排列在一起，因而在范围查找时，可以先找到这个范围的起末点，且只在这个范围内扫描数据页，避免了大范围扫描，提高了查询速度。- 3.在place，date，amount上的组合索引select count(*) from record where date 19991201 and date 2000 （26秒）select date,sum(amount) from record group by date（27秒）select count(*) from record where date 19990901 an

17、d place in (BJ, SH)（19991201 and date 2000(19990901 and place in (BJ,SH)（,=, =）和order by、group by发生的列，可考虑建立群集索引；- .经常同时存取多列，且每列都含有重复值可考虑建立组合索引；- .组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。二、不充份的连接条件：- 例：表card有7896行，在card_no上有一个非聚集索引，表account有191122行，在 account_no上有一个非聚集索引，试看在不同的表连接条件下，两个SQL的执行情况：select sum(a

18、.amount) from account a,card b where a.card_no = b.card_no（20秒）- 将SQL改为：select sum(a.amount) from account a,card b where a.card_no = b.card_no and a.account_no=b.account_no（ 1秒）- 分析：- 在第一个连接条件下，最佳查询方案是将account作外层表，card作内层表，利用card上的索引，其I/O次数可由以下公式估算为：- 外层表account上的22541页+（外层表account的191122行*内层表card上对

19、应外层表第一行所要查找的3页）=595907次I/O - 在第二个连接条件下，最佳查询方案是将card作外层表，account作内层表，利用account上的索引，其I/O次数可由以下公式估算为：- 外层表card上的1944页+（外层表card的7896行*内层表account上对应外层表每一行所要查找的4页）= 33528次I/O- 可见，只有充份的连接条件，真正的最佳方案才会被执行。- 总结：- 1.多表操作在被实际执行前，查询优化器会根据连接条件，列出几组可能的连接方案并从中找出系统开销最小的最佳方案。连接条件要充份考虑带有索引的表、行数多的表；内外表的选择可由公式：外层表中的匹配行数

20、*内层表中每一次查找的次数确定，乘积最小为最佳方案。- 2.查看执行方案的方法- 用set showplanon，打开showplan选项，就可以看到连接顺序、使用何种索引的信息；想看更详细的信息，需用sa角色执行dbcc(3604,310,302)。三、不可优化的where子句- 1.例：下列SQL条件语句中的列都建有恰当的索引，但执行速度却非常慢：select * from record wheresubstring(card_no,1,4)=5378(13秒)select * from record whereamount/30 1000（11秒）select * from record

21、 whereconvert(char(10),date,112)=19991201（10秒）- 分析：- where子句中对列的任何操作结果都是在SQL运行时逐列计算得到的，因此它不得不进行表搜索，而没有使用该列上面的索引；如果这些结果在查询编译时就能得到，那么就可以被SQL优化器优化，使用索引，避免表搜索，因此将SQL重写成下面这样：select * from record where card_no like5378%（ 1秒）select * from record where amount 1000*30（ 1秒）select * from record where date= 199

22、9/12/01（ 1秒）- 你会发现SQL明显快起来！- 2.例：表stuff有200000行，id_no上有非群集索引，请看下面这个SQL：select count(*) from stuff where id_no in(0,1)（23秒）- 分析：- where条件中的in在逻辑上相当于or，所以语法分析器会将in (0,1)转化为id_no =0 or id_no=1来执行。我们期望它会根据每个or子句分别查找，再将结果相加，这样可以利用id_no上的索引；但实际上（根据showplan）,它却采用了OR策略，即先取出满足每个or子句的行，存入临时数据库的工作表中，再建立唯一索引以去掉

23、重复行，最后从这个临时表中计算结果。因此，实际过程没有利用id_no上索引，并且完成时间还要受tempdb数据库性能的影响。 - 实践证明，表的行数越多，工作表的性能就越差，当stuff有620000行时，执行时间竟达到220秒！还不如将or子句分开：select count(*) from stuff where id_no=0select count(*) from stuff where id_no=1- 得到两个结果，再作一次加法合算。因为每句都使用了索引，执行时间只有3秒，在620000行下，时间也只有4秒。或者，用更好的方法，写一个简单的存储过程：create proc count

24、_stuff asdeclare a intdeclare b intdeclare c intdeclare d char(10)beginselect a=count(*) from stuff where id_no=0select b=count(*) from stuff where id_no=1endselect c=a+bselect d=convert(char(10),c)print d- 直接算出结果，执行时间同上面一样快！- 总结：- 可见，所谓优化即where子句利用了索引，不可优化即发生了表扫描或额外开销。- 1.任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。- 2.in、or子句常会使用工作表，使索引失效；如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索引。- 3.要善于使用存储过程，它使SQL变得更加灵活和高效。- 从以上这些例子可以看出，SQL优化的实质就是在结果正确的前提下，用优化器可以识别的语句，充份利用索引，减少表扫描的I/O次数，尽量避免表搜索的发生。其实SQL的性能优化是一个复杂的过程，上述这些只是在应用层次的一种体现，深入研究还会涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。 第 8 页 共 8 页