数据库查询优化实验报告_SQLServer2008

SQL Server 2008数据查询的优化方法研究摘要随着数据存储需求的日益增长，对关系数据的管理和访问就成为数据库技术必须解决的问题。

本文主要论述关系数据库查询优化技术，并从它的优化技术进行深入探讨，对系统实现做了一定的论述，并进行了部分的程序实现。

关键词：数据库查询系统优化引言SQLServer是是由微软公司开发的基于Windows操作系统的关系型数据库管理系统，它是一个全面的、集成的、端到端的数据解决方案，为企业中的用户提供了一个安全、可靠和高效的平台用于企业数据管理和商业智能应用。

目前，许多中小型企业的数据库应用系统都是用SQLServer作为后台数据库管理系统设计开发的。

设计一个应用系统并不难，但是要想使系统达到最优化的性能并不是一件容易的事。

根据多年的实践，由于初期的数据库中表的记录数比较少，性能不会有太大问题，但数据积累到一定程度，达到数百万甚至上千万条，全面扫描一次往往需要数十分钟，甚至数小时。

20％的代码用去了80％的时间，这是程序设计中的一个著名定律，在数据库应用程序中也同样如此。

如果用比全表扫描更好的查询策略，往往可以使查询时间降为几分钟。

而且我们知道，目前数据库系统应用中，查询操作占了绝大多数，查询优化成为数据库性能优化最为重要的手段之一。

影响查询效率的因素SQLServer处理查询计划的过程是这样的：在做完查询语句的词法、语法检查之后，将语句提交给SQLServer的查询优化器，查询优化器通过检查索引的存在性、有效性和基于列的统计数据来决定如何处理扫描、检索和连接，并生成若干执行计划，然后通过分析执行开销来评估每个执行计划，从中选出开销最小的执行计划,由预编译模块对语句进行处理并生成查询规划，然后在合适的时间提交给系统处理执行，最后将执行结果返回给用户。

所以，SQLServer中影响查询效率的因素主要有以下几种：1．没有索引或者没有用到索引。

索引是数据库中重要的数据结构，使用索引的目的是避免全表扫描，减少磁盘I/O，以加快查询速度。

2．没有创建计算列导致查询不优化。

3．查询出的数据量过大（可以采用多次查询，其他的方法降低数据量）。

4．返回了不必要的行和列。

5．查询语句不好，没有优化。

其中包括：查询条件中操作符使用是否得当;查询条件中的数据类型是否兼容;对多个表查询时,数据表的次序是否合理;多个选择条件查询时,选择条件的次序是否合理;是否合理安排联接选择运算等。

SQLServer数据查询优化方法1、避免使用不兼容的数据类型。

例如float和int、char和varchar、binary和varbinary 是不兼容的。

数据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。

例如: select name from employee where salary ＞60000在这条语句中,如salary字段是money型的,则优化器很难对其进行优化,因为60000是个整型数。

我们应当在编程时将整型转化成为钱币型,而不要等到运行时转化。

2、尽量避免在where子句中对字段进行函数或表达式操作，这将导致引擎放弃使用索引而进行全表扫描。

如：select * from t1 where f1/2=100应改为:select * from t1 where f1=100*2select * from record where substring(card_no,1,4)=‟5378‟应改为:select * from record where card_n o like …5378%‟select member_number, first_name, last_name from memberswhere datediff(yy,datofbirth,getdate()) > 21应改为:select member_number, first_name, last_name from memberswhere dateofbirth < dateadd(yy,-21,getdate())即：任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。

3、避免使用!=或＜＞、is null或is not null、in ，not in等这样的操作符,因为这会使系统无法使用索引,而只能直接搜索表中的数据。

例如:select id from employee where id != b%优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。

4、尽量使用数字型字段，一部分开发人员和数据库管理人员喜欢把包含数值信息的字段设计为字符型，这会降低查询和连接的性能，并会增加存储开销。

这是因为引擎在处理查询和连接回逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

5、合理使用exists,not exists子句。

如下所示：1.select sum(t1.c1)from t1 where((select count(*)from t2 where t2.c2=t1.c2>0)2.select sum(t1.c1) from t1where exists(select * from t2 where t2.c2=t1.c2)两者产生相同的结果，但是后者的效率显然要高于前者。

因为后者不会产生大量锁定的表扫描或是索引扫描。

如果你想校验表里是否存在某条纪录，不要用count(*)那样效率很低，而且浪费服务器资源。

可以用exists代替。

如：if (select count(*) from table_name where column_name = xxx)可以写成：if exists (select * from table_name where column_name = xxx)6、尽量避免在索引过的字符数据中，使用非打头字母搜索。

这也使得引擎无法利用索引。

见如下例子：select * from t1 where name like …%l%‟select * from t1 where substing(name,2,1)=‟l‟select * from t1 where name like …l%‟即使name字段建有索引，前两个查询依然无法利用索引完成加快操作，引擎不得不对全表所有数据逐条操作来完成任务。

而第三个查询能够使用索引来加快操作。

7、分利用连接条件，在某种情况下，两个表之间可能不只一个的连接条件，这时在where 子句中将连接条件完整的写上，有可能大大提高查询速度。

例：select sum(a.amount) from account a,card b where a.card_no = b.card_noselect sum(a.amount) from account a,card b where a.card_no = b.card_no and a.account_no=b.account_no第二句将比第一句执行快得多。

8、消除对大型表行数据的顺序存取尽管在所有的检查列上都有索引，但某些形式的where子句强迫优化器使用顺序存取。

如：select * from orders where (customer_num=104 and order_num>1001) ororder_num=1008解决办法可以使用并集来避免顺序存取：select ＊from orders where customer_num=104 and order_num>1001unionselect ＊from orders where order_num=1008这样就能利用索引路径处理查询。

9、避免困难的正规表达式like关键字支持通配符匹配，技术上叫正规表达式。

但这种匹配特别耗费时间。

例如：select * from customer where zipcode like “98_ _ _”即使在zipcode字段上建立了索引，在这种情况下也还是采用顺序扫描的方式。

如果把语句改为select ＊from customer where zipcode >“98000”，在执行查询时就会利用索引来查询，显然会大大提高速度。

10、使用视图加速查询把表的一个子集进行排序并创建视图，有时能加速查询。

它有助于避免多重排序操作，而且在其他方面还能简化优化器的工作。

例如：select ，rcvbles.balance，……other columnsfrom cust，rcvbleswhere cust.customer_id = rcvlbes.customer_idand rcvblls.balance>0and cust.postcode>“98000”order by 如果这个查询要被执行多次而不止一次，可以把所有未付款的客户找出来放在一个视图中，并按客户的名字进行排序：create view dbo.v_cust_rcvlbesasselect ，rcvbles.balance，……other columnsfrom cust，rcvbleswhere cust.customer_id = rcvlbes.customer_idand rcvblls.balance>0order by 然后以下面的方式在视图中查询：select ＊from v_cust_rcvlbeswhere postcode>“98000”视图中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘i/o，所以查询工作量可以得到大幅减少。

11、能够用between的就不要用inselect * from t1 where id in (10,11,12,13,14)改成：select * from t1 where id between 10 and 14因为in会使系统无法使用索引,而只能直接搜索表中的数据。

12、 distinct的就不用group byselect orderid from details where unitprice > 10 group by orderid可改为：select distinct orderid from details where unitprice > 1013、部分利用索引1.select employeeid, firstname, lastnamefrom nameswhere dept = prod or city = orlando or division = food2.select employeeid, firstname, lastname from names where dept = prod union allselect employeeid, firstname, lastname from names where city = orlando union allselect employeeid, firstname, lastname from names where division = food如果dept 列建有索引则查询2可以部分利用索引，查询1则不能。