SQL Server 2008数据查询的优化方法研究
摘要
    随着数据存储需求的日益增长，对关系数据的管理和访问就成为数据库技术必须解决的问题。本文主要论述关系数据库查询优化技术，并从它的优化技术进行深入探讨，对系统实现做了一定的论述，并进行了部分的程序实现。
关键词：数据库  查询系统  优化
引言   
SQLServer是是由微软公司开发的基于Windows操作系统的关系型数据库管理系统，它是一个全面的、集成的、端到端的数据解 决方案，为企业中的用户提供了一个安全、可靠和高效的平台用于企业数据管理和商业智能应用。目前，许多中小型企业的数据库应用系统都是用 SQLServer作为后台数据库管理系统设计开发的。设计一个应用系统并不难，但是要想使系统达到最优化的性能并不是一件容易的事。根据多年的实践，由 于初期的数据库中表的记录数比较少，性能不会有太大问题，但数据积累到一定程度，达到数百万甚至上千万条，全面
扫描一次往往需要数十分钟，甚至数小时。 20％的代码用去了80％的时间，这是程序设计中的一个著名定律，在数据库应用程序中也同样如此。如果用比全表扫描更好的查询策略，往往可以使查询时间降 为几分钟。而且我们知道，目前数据库系统应用中，查询操作占了绝大多数，查询优化成为数据库性能优化最为重要的手段之一。   
影响查询效率的因素    
SQLServer处理查询计划的过程是这样的：在做完查询语句的词法、语法检查之后，将语句提交给SQLServer的查询优化器，查询优化器 通过检查索引的存在性、有效性和基于列的统计数据来决定如何处理扫描、检索和连接，并生成若干执行计划，然后通过分析执行开销来评估每个执行计划，从中选 出开销最小的执行计划,由预编译模块对语句进行处理并生成查询规划，然后在合适的时间提交给系统处理执行，最后将执行结果返回给用户。所 以，SQLServer中影响查询效率的因素主要有以下几种：
1．没有索引或者没有用到索引。索引是数据库中重要的数据结构，使用索引的目的是避免全 表扫描，减少磁盘I/O，以加快查询速度。
2．没有创建计算列导致查询不优化。
数据库优化sql语句
  　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*2

select * from record where substring(card_no,1,4)=’5378’
应改为:
select * from record where card_no like ‘5378%’

select member_number, first_name, last_name  from members
where datediff(yy,datofbirth,getdate()) > 21
应改为:
select member_number, first_name, last_name  from members
where 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_no
select 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) or
order_num=1008
解决办法可以使用并集来避免顺序存取：
select ＊ from orders where customer_num=104 and order_num>1001
union
select ＊ from orders where order_num=1008
这样就能利用索引路径处理查询。

9、 避免困难的正规表达式
      like关键字支持通配符匹配，技术上叫正规表达式。但这种匹配特别耗费时间。例如：select * from customer where zipcode like “98_ _ _”
即使在zipcode字段上建立了索引，在这种情况下也还是采用顺序扫描的方式。如
果把语句改为select ＊ from customer where zipcode >“98000”，在执行查询
时就会利用索引来查询，显然会大大提高速度。
10、 使用视图加速查询
把表的一个子集进行排序并创建视图，有时能加速查询。它有助于避免多重排序
操作，而且在其他方面还能简化优化器的工作。例如：
select cust.name，rcvbles.balance，……other columns
from cust，rcvbles
where cust.customer_id = rcvlbes.customer_id
and rcvblls.balance>0
and cust.postcode>“98000”
order by cust.name
如果这个查询要被执行多次而不止一次，可以把所有未付款的客户出来放在一个
视图中，并按客户的名字进行排序：
create view dbo.v_cust_rcvlbes
as
select cust.name，rcvbles.balance，……other columns
from cust，rcvbles
where cust.customer_id = rcvlbes.customer_id
and rcvblls.balance>0
order by cust.name

然后以下面的方式在视图中查询：
select ＊ from  v_cust_rcvlbes
where postcode>“98000”
视图中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘
i/o，所以查询工作量可以得到大幅减少。

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

12、 distinct的就不用group by
      select orderid  from details where unitprice > 10 group by orderid
      可改为：
      select distinct orderid from details where unitprice > 10
     

13、  部分利用索引
      1.select employeeid, firstname, lastname
from names
where dept = prod or city = orlando or division = food

      2.select employeeid, firstname, lastname from names where dept = prod
union all
select employeeid, firstname, lastname from names where city = orlando
union all
select employeeid, firstname, lastname from names where division = food
如果dept 列建有索引则查询2可以部分利用索引，查询1则不能。

14、  能用union  all就不要用union
union  all不执行select distinct函数，这样就会减少很多不必要的资源

15、  不要写一些不做任何事的查询
如：select col1 from t1 where 1=0
    select col1 from t1 where col1=1 and col1=2
这类死码不会返回任何结果集，但是会消耗系统资源。

16、  尽量不要用select into语句。
select inot 语句会导致表锁定，阻止其他用户访问该表。

17、 必要时强制查询优化器使用某个索引
    select * from t1 where nextprocess = 1 and processid in (8,32,45)
改成：
select * from t1 (index = ix_processid) where nextprocess = 1 and processid in (8,32,45)
则查询优化器将会强行利用索引ix_processid 执行查询。
   
18、  虽然update、delete语句的写法基本固定，但是还是对update语句给出以下建议：
a) 尽量不要修改主键字段。
b) 当修改varchar型字段时，尽量使用相同长度内容的值代替。
c) 尽量最小化对于含有update触发器的表的update操作。
d) 避免update将要复制到其他数据库的列。
e) 避免update建有很多索引的列。
f) 避免update在where子句条件中的列。
总结
本文通过分析影响SQLSERVER数据查询效率的因素，提出了优化查询的方法，这些方法实施简便易行，在系统的开发中采用，能整体提高应用系统的性能。
参考文献
【1】王珊、萨师煊  《数据库系统概论》 高等教育出版社
【2】刘奎、付青、张权 《SQL Server 2008 从入门到精通》 化学工业出版社
【3】William R。Stanek(著) 贾洪峰(译)  《SQL Server 2008 管理员必备指南》清华大学出版社

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。