学习

 

数据库查询优化小结：

在这周的学习过程，主要是学习怎么通过写SQL来提高我们查询的效率，在数据库数据量很大的情况下可以大大缩短我们的查询时间。

1.对数据库查询进行优化，应尽量避免全表扫描，首先应考虑在where 及order by 涉及的列上建立索引。

2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

　　select id from t where num is null

　　可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

　　select id from t where num=0

3.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

　　select id from t where num=10 or num=20

　　可以这样查询：

　　select id from t where num=10

　　union all

　　select id from t where num=20

5. in 和 not in 也要慎用，否则会导致全表扫描，如：

　　select id from t where num in(1，2，3)

　　对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

问题描述：查询条件非常复杂时，怎么提高效率？http://blog.csdn.net/feixianxxx/article/details/5271305

*例子：select  *  from  table1  where  id  in  (1,3,6,10,...,8000)  and  type  =  1

*id值可能有数千个之多，这样查询起来非常慢，把id设为主键也很慢，请问有没有什么方法可以快一点？

*解决方法：拆分条件

--------------------*/

--例如:

    select * from tb where id in(1,2,3,4,........) and name='best'

--这里的括号里面的数字有将近千个之多的时候,你就可以考虑这样

--1.将括号的条件做成变量

declare @str varchar(4000) set @str='1,2,3,4,5.......'

--2.然后将@s拆分后插入临时表,拆分一个字符串：

create  table  #t( 

id  varchar(10)) 

declare  @i  int 

declare  @len  int 

set  @i  =  1 

while  @i  <  len(@str+',') 

begin 

insert  #t  select  substring(@str+',',@i,charindex(',',@str+',',@i)-@i) 

set  @i  =  charindex(',',@str+',',@i)+1 

end 

--3利用临时表和原表进行连接取值

select  k.*  from  tb k 

inner  join  #t p  on  p.id  =  k.id 

 where   name='best'

 substring用法：

public String substring(int beginIndex)返回一个新的字符串，它是此字符串的一个子字符串。该子字符串始于指定索引处的字符，一直到此字符串末尾。这个函数返回第一个参数中从第二个参数指定的位置开始、第三个参数指定的长度的子字符串

如：以下函数调用返回“234”： substring("12345",2,3) 

 CHARINDEX（expression1,expression2,[start_location]), 返回字符串中指定表达式的起始位置。

例如：

USE AdventureWorks

SELECT  CHARINDEX('bicycle', DocumentSummary)

FROM Production.Document

WHERE DocumentID = 3;

返回结果为48。

SELECT CHARINDEX('bicycle1', DocumentSummary, 5)

FROM Production.Document

WHERE DocumentID = 3;

返回结果为0

6.下面的查询也将导致全表扫描：

　　select  id  from t where name like '%abc%'

若要提高效率，可以考虑全文检索。

7.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

　　select id from t where num=@num

　　可以改为强制查询使用索引：

　　select id from t with(index(索引名)) where num=@num

8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

　　select id from t where num/2=100

　　应改为：

　　select id from t where num=100*2

9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

　　select id from t where substring(name，1，3)='abc'--name以abc开头的id

select id from t where datediff(day，createdate，'2005-11-30')=0--‘2005-11-30’生成的id