查看: 6443|回复: 5
如何提高千万条数据分页查询效率
论坛徽章:0
表my_resume数据1000W条
表v_getAllUserLabels数据100条
SQL语句如下:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH OrderedResults AS
( SELECT R.MyUserID,R.Pername,R.Sex,R.Birthday,workedcomnumber,workedyear,workedmonth, R.Hometown,R.Location,R.updateDate,R.photoflag,[PhotoName], R.selfdescription,R.ResumeStatus,R.checkflag, isnull(R.joblocation1,'') as joblocation1,isnull(R.joblocation2,'') as joblocation2,isnull(R.joblocation3,'') as joblocation3, R.JobFunction1,R.JobFunction2,R.JobFunction3, isnull(R.JobSeeking1,'') as JobSeeking1,isnull(R.JobSeeking2,'') as JobSeeking2,isnull(R.JobSeeking3,'') as JobSeeking3, [dbo].[F_GetMyWorkExp](R.MyUserID) as workExp, [dbo].[F_GetMyEducation](R.MyUserID) as schools, ROW_NUMBER() OVER (order by ResID) as RowNumber
FROM wuerbajob.dbo.My_Resume R
WHERE 1=1&&and checkflag=2
and (ResumeStatus=0 or ResumeStatus=2)
and R.updatedate between ' 00:00' and&&' 17:02' )
select * from OrderedResults orderR left outer join weibo.dbo.v_getAllUserLabels userLabel on orderR.myuserid=userLabel.userid where&&orderR.RowNumber between 1 and 10000
单线程环境下 花费时间3分钟,去掉left outer join weibo.dbo.v_getAllUserLabels,效率差不多
已做如下操作:
表my_resume 已根据resID分为10个分区表(测试表明加了分区,只快了1分钟左右)
resID为索引
如何才能提高该语句查询速度?因为要分批一次把1000W条数据查询完.
论坛徽章:11
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH&&OrderedResults
& && &&&AS (SELECT&&R.MyUserID
& && && && && && &, R.Pername
& && && && && && &, R.Sex
& && && && && && &, R.Birthday
& && && && && && &, workedcomnumber
& && && && && && &, workedyear
& && && && && && &, workedmonth
& && && && && && &, R.Hometown
& && && && && && &, R.Location
& && && && && && &, R.updateDate
& && && && && && &, R.photoflag
& && && && && && &, [PhotoName]
& && && && && && &, R.selfdescription
& && && && && && &, R.ResumeStatus
& && && && && && &, R.checkflag
& && && && && && &, ISNULL(R.joblocation1 , '') AS joblocation1
& && && && && && &, ISNULL(R.joblocation2 , '') AS joblocation2
& && && && && && &, ISNULL(R.joblocation3 , '') AS joblocation3
& && && && && && &, R.JobFunction1
& && && && && && &, R.JobFunction2
& && && && && && &, R.JobFunction3
& && && && && && &, ISNULL(R.JobSeeking1 , '') AS JobSeeking1
& && && && && && &, ISNULL(R.JobSeeking2 , '') AS JobSeeking2
& && && && && && &, ISNULL(R.JobSeeking3 , '') AS JobSeeking3
& && && && && && &, [dbo].[F_GetMyWorkExp](R.MyUserID) AS workExp
& && && && && && &, [dbo].[F_GetMyEducation](R.MyUserID) AS schools
& && && && && && &, ROW_NUMBER() OVER (ORDER BY ResID) AS RowNumber
& && && && &FROM& & wuerbajob.dbo.My_Resume R
& && && && &WHERE& &1 = 1
& && && && && && &&&AND checkflag = 2
& && && && && && &&&AND (ResumeStatus = 0
& && && && && && && && & OR ResumeStatus = 2
& && && && && && && && &)
& && && && && && &&&AND R.updatedate BETWEEN ' 00:00'
& && && && && && && && && && && && & AND& &&&' 17:02')
&&SELECT&&*
&&FROM& & OrderedResults orderR
&&LEFT OUTER JOIN weibo.dbo.v_getAllUserLabels userLabel
&&ON& && &orderR.myuserid = userLabel.userid
&&WHERE& &orderR.RowNumber BETWEEN 1 AND 10000
一次查一万条, 谁有耐心都看完啊?
先改成100条吧, 再把执行计划贴上来...
论坛徽章:0
建议先分页主键再关联数据，不要直接拿明细数据来分页WITH&&OrderedResults
& && &&&AS (SELECT&&R.MyUserID
& & & & & & & & & & & & & & & && &, ROW_NUMBER() OVER (ORDER BY ResID) AS RowNumber
& && && && &FROM& & wuerbajob.dbo.My_Resume R
& && && && &WHERE& &1 = 1
& && && && && && &&&AND checkflag = 2
& && && && && && &&&AND ResumeStatus in (0,2)
& && && && && && &&&AND R.updatedate BETWEEN ' 00:00'
& && && && && && && && && && && && & AND& &&&' 17:02')
& && && && && && && && && && && && &
&&SELECT& & & & R.MyUserID
& & & & & & & && &, R.Pername
& & & & & & & && &, R.Sex
& & & & & & & && &, R.Birthday
& & & & & & & && &, workedcomnumber
& & & & & & & && &, workedyear
& & & & & & & && &, workedmonth
& & & & & & & && &, R.Hometown
& & & & & & & && &, R.Location
& & & & & & & && &, R.updateDate
& & & & & & & && &, R.photoflag
& & & & & & & && &, [PhotoName]
& & & & & & & && &, R.selfdescription
& & & & & & & && &, R.ResumeStatus
& & & & & & & && &, R.checkflag
& & & & & & & && &, ISNULL(R.joblocation1 , '') AS joblocation1
& & & & & & & && &, ISNULL(R.joblocation2 , '') AS joblocation2
& & & & & & & && &, ISNULL(R.joblocation3 , '') AS joblocation3
& & & & & & & && &, R.JobFunction1
& & & & & & & && &, R.JobFunction2
& & & & & & & && &, R.JobFunction3
& & & & & & & && &, ISNULL(R.JobSeeking1 , '') AS JobSeeking1
& & & & & & & && &, ISNULL(R.JobSeeking2 , '') AS JobSeeking2
& & & & & & & && &, ISNULL(R.JobSeeking3 , '') AS JobSeeking3
& & & & & & & && &, [dbo].[F_GetMyWorkExp](R.MyUserID) AS workExp
& & & & & & & && &, [dbo].[F_GetMyEducation](R.MyUserID) AS schools
& & & & & & & && &, orderR.RowNumber
& & & & & & & && &, userLabel.*
&&FROM& & OrderedResults orderR
&&inner join wuerbajob.dbo.My_Resume R on R.MyUserID=orderR.MyUserID
&&LEFT OUTER JOIN weibo.dbo.v_getAllUserLabels userLabel ON orderR.myuserid = userLabel.userid
&&WHERE& &orderR.RowNumber BETWEEN 1 AND 10000复制代码
论坛徽章:0
lucky7_2000 发表于
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH&&OrderedResults
恩,这样确实可以提高,关键点在下面这2行
& && && && && && &, [dbo].[F_GetMyWorkExp](R.MyUserID) AS workExp
& && && && && && &, [dbo].[F_GetMyEducation](R.MyUserID) AS schools
这2行的作用是:
把MyWorkExp表中的多行多列的数据转换成一行一列的数据
把MyEducation表中的多行多列的数据转换成一行一列的数据
这个地方最耗查询效率
论坛徽章:7
注意其中的【改进版】：先仅仅对主键进行分页，得到满足页数的所有记录的主键，最后再关联回完整的记录
论坛徽章:5
updatedate 上建立聚集索引，别的查询列上建立非聚集索引，试试
itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有　　　　
　北京市公安局海淀分局网监中心备案编号：10　广播电视节目制作经营许可证：编号（京）字第1149号鍗氬?鍒嗙被锛