Sql千万数据执行测试

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

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

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

SqlServer索引及优化详解 http://blog.csdn.net/china2010pan/article/details/6953498

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

（*）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树（二叉树）建立逻辑索引表，增删改不营销，查询效率比聚集索引低。索引详解：http://blog.csdn.net/donglynn/article/details/49618255

（*）误区：聚集索引并不一定是唯一索引，由于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