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

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

 

例子1

优化前：

 

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

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

 

优化后：

 

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

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秒多

 

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

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

 

优化后：查询速度14毫秒

 

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

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