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