sql server 处理百万数据查询

如何得到SQL精确的执行时间

方法一:
declare @d datetime
set @d=getdate()
-->查询语句
select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())
 
方法二:
SET STATISTICS TIME ON
SELECT * FROM SYSOBJECTS  -->你的查询
SET STATISTICS TIME OFF

set statistics time on的理解：http://www.studyofnet.com/news/568.html

SqlServer索引及优化详解 

（*）当拥有千万级数据时，修改字段默认值会花费巨量的时间。修改字段类型不费时间，添加字段并设置默认值也不费时间。

（*）union和or执行结果一样，但是，当搜索条件中搜索字段不同时，union效率比or要高；当搜索条件中搜索字段一样时，效率一样差。union默认会去除重复记录，union all会显示全部记录

（*）搜索同一条记录，搜索聚集索引耗时0ms，搜索无索引的字符串字段耗时420ms（如果此字符串为纯数字，耗时246ms），

（*）distinct会导致全表扫描，group by 会优先扫描索引

（*）sum函数注意溢出，需要使用sum(cast(id as bigint))转换成8字节的bigint

（*）order by 根据索引排序很快，非索引字段效率低下；其中非聚集索引排序效率略微高于聚集索引

（*）建立复合索引后，查询条件中一定要包含聚集索引或非聚集索引，否则无效。A：聚集 B：非聚集  A+B+C：复合，如果where只有C，则无效，AC或者BC或者ABC都有效

（*）聚集索引有大小排序，物理存储顺序和逻辑顺序一致，增删改操作系统消耗大，查询快速。非聚集索引，不改变物理存储，根据B树（二叉树）建立逻辑索引表，增删改不营销，查询效率比聚集索引低。索引详解：

（*）误区：聚集索引并不一定是唯一索引，由于SQL SERVER将主键默认定义为聚集索引，事实上，索引是否唯一与是否聚集是不相关。但一般建立在唯一索引上，可以提高等值查询效率

（*）可以在同一个字段上同时设置聚集索引和非聚集索引，例如id字段，但是对于查询效率没有任何的提升，当两个索引同时设置在同一个字段，那么耗时和只有聚集索引一样

（*）建立索引的目的就是帮助查询,如果查寻用不到则索引就没有必要建立,另外如果数据表过大(5w以上)则有些字段(字符型长度超过(40))不适合作为索引,另外如果表是经常需要更新的也不适合做索引 

环境：

分页方案;

/*
 分页方案一(达到10万页,200万条时，耗时300ms)：
 select top 20 id,username,password from A_User 
 where id> 
 (select max (id) from (select top 2000000 id from A_User order by id) as T )     
 order by id 

 分页方案二（达到6.5万页，130万条时，耗时300ms；提取指定的id、password、username反而耗时更长）：
 select top 20 * from A_User where id not in(select top 1300000 id from A_User )

 分页方案三（达到6.5万页，130万条时，耗时300ms）：
 SELECT TOP 20 * FROM A_User a WHERE not exists 
 (select id,password,username from (select top 1300000 id,password,username from A_User order by id) b where b.id=a.id ) 
 order by id 

 分页方案四（达到4.25万页，85万条时，耗时300ms）：
 SELECT TOP 20 * FROM 
(SELECT TOP 850000 id,password,username FROM A_User ORDER BY id DESC) f 
 ORDER BY f.id ASC分页方案五（达到3.7万页，74万条时，耗时300ms）：
 SELECT TOP 20 * 
 FROM(SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM A_User) as A  
 WHERE RowNumber > 20*(37000-1)

两表联动分页：

//方案一，效率很低。
select top 20 tt.id from (select n.id,n.title,u.username from A_New n join A_User u on n.userid=u.id) tt where tt.id>
 (select MAX(t.id) from 
(select top 20 n.id from A_New n join A_User u on n.userid=u.id) 
 as t)//方案二，效率高，和主表单独查询时间基本一样
select t2.id,t2.username,r.* from
 (select top 20 id,username,roleid from A_User where id> 
 (select max (id) from (select top 1500000 id from A_User order by id) as T )     
 order by id) as t2
 left join A_Role r
 on t2.roleid=r.id

两表联动耗时

1、（65207条记录，耗时505ms。A_User中数据少，对应A_New中数据多）：

select t1.* from
 (select top 3 * from A_User) as t1
 left join A_New n
 on t1.id=n.userid

结果字段

2、（65207条记录，耗时827ms。）：

select t1.*,u.username from
 (select top 65207 * from A_New) as t1
 left join A_User u
 on t1.userid=u.id

3、效率最低的方式（65207条记录，耗时16252ms）

select top 65207 n.* from A_New n,A_User u where n.userid=u.id

三表联动分页：

方案一(首先提取出主表中当前页的所有数据，然后使用left join去连接其他表)：

select t2.id,t2.username from
 (select top 20 id,username,roleid,classid from A_User where id>(select MAX(id) from (select top 2000000 id from A_User order by id) as t) order by id) as t2
 left join A_Role r on t2.roleid=r.id
 left join A_Class c on t2.classid=c.id
 */
1、select * from A_User where id in(9999999)

第一次查询花费11秒，之后的查询，无论数值变为多少，花费时间为0

2、搜索数值的速度远远快于搜索字符的速度

select top 200 id,addtime,roleid from A_User order by id desc//用时0
select top 200 id,username,[password],addtime,token,roleid from A_User order by id desc//用时46ms
select id,username,[password],addtime,token,roleid from A_User where id>10560000//用时43ms