sql大数据多条件查询索引优化

此优化的前提可以称之为最近流行的头条人物“许三多”，总数据多，查询条件多，返回列多

优化前分页查询内部select列为需要的全部列，优化后内部select只返回ID主键，外部查询关联原数据表，然后查出所需要的列

例子1

优化前：

[sql] view plain copy print?

1. select t.* from (  
2.         select r.* ,row_number() over(order by r.id desc) row from tab(nolock) r   
3.         where 1=1 and r.IsDelete=0 and r.Status>0 and r.PlatformID=1 and r.CreateUser=100000       
4. ) as t  where row between 1 and 10  

select t.* from (
        select r.* ,row_number() over(order by r.id desc) row from tab(nolock) r 
        where 1=1 and r.IsDelete=0 and r.Status>0 and r.PlatformID=1 and r.CreateUser=100000 	
) as t  where row between 1 and 10      

优化后：

1. select r.* from (  
2.         select r.ID ,row_number() over(order by r.id desc) row from tab(nolock) r   
4. ) as t join tab r on r.id=t.id where row between 1 and 10  

select r.* from (
        select r.ID ,row_number() over(order by r.id desc) row from tab(nolock) r 
        where 1=1 and r.IsDelete=0 and r.Status>0 and r.PlatformID=1 and r.CreateUser=100000 	
) as t join tab r on r.id=t.id where row between 1 and 10
      

最近又有一个例子

例子2

优化前：tablA数据量1千多万，tablB数据量几百万，查询速度11秒多

1. select * from (  
2.         select d.LessonPlanID,d.ResUrl,p.createID,p.CreateTime,row_number() over(order by d.id desc) row  
3.         from tablA(nolock) d   
4.         join tablB(nolock) p on p.id=d.lessonplanid  
5.         where p.createID in(109486,103295,103298,109347,130346,181382,330312)  
6.     ) t where t.row between 1 and 20   

select * from (
	    select d.LessonPlanID,d.ResUrl,p.createID,p.CreateTime,row_number() over(order by d.id desc) row
	    from tablA(nolock) d 
		join tablB(nolock) p on p.id=d.lessonplanid
	    where p.createID in(109486,103295,103298,109347,130346,181382,330312)
	) t where t.row between 1 and 20       

1. select d.LessonPlanID,d.ResUrl,p.createID,p.CreateTime from (  
2.         select  d.id,row_number() over(order by d.id desc) row  
6. ) t join tablA(nolock) d on d.id=t.id   join tablB(nolock) p on p.id=d.lessonplanid  
7.  where t.row between 1 and 20   

select d.LessonPlanID,d.ResUrl,p.createID,p.CreateTime from (
	    select  d.id,row_number() over(order by d.id desc) row
	    from tablA(nolock) d 
		join tablB(nolock) p on p.id=d.lessonplanid
	    where p.createID in(109486,103295,103298,109347,130346,181382,330312)
) t join tablA(nolock) d on d.id=t.id	join tablB(nolock) p on p.id=d.lessonplanid
 where t.row between 1 and 20