为啥我写个三表关联查询语句查询个数据要5秒之久??

aaa907638015 2017-09-26 04:36:14
select 
A.*
from
(
select
row_number() over (order by CreateTime desc) RowNumber,
*
from
(
select
A.*,
B.LotteryType,
B.State,
C.Account as UserAccount,
C.NickName as UserNickName
from
BettingUsers as A
inner join
Bettings as B
on
A.BettingID = B.ID
inner join
Users as C
on
A.UserID = C.ID
where
B.IsChipped = 1
) as A)
as A
where
A.RowNumber between 1 and 30


哪位大神能帮忙优化下吗?

BettingUsers 投注用户表(即每个投注单可以有多个用户投注)
Bettings 投注单表
Users 用户表

主键是ID,自增字段,没别的索引字段
...全文
1022 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
吉普赛的歌 2017-12-27
  • 打赏
  • 举报
回复
--先创建索引
CREATE INDEX ix_BettingUsers_CreateTime ON BettingUsers(CreateTime)

--执行稍简化后的SQL
SELECT A.*
FROM   (
			SELECT 
					ROW_NUMBER() OVER(ORDER BY CreateTime DESC) RowNumber,
					A.*,
					B.LotteryType,
					B.State,
					C.Account   AS UserAccount,
					C.NickName  AS UserNickName
			FROM   BettingUsers AS A
					INNER JOIN Bettings AS B
						ON  A.BettingID = B.ID
					INNER JOIN Users AS C
						ON  A.UserID = C.ID
			WHERE  B.IsChipped = 1
       ) AS A
WHERE  A.RowNumber BETWEEN 1 AND 30
吉普赛的歌 2017-12-27
  • 打赏
  • 举报
回复
引用 10 楼 z10843087 的回复:
很早以前的帖子了哦
上当了
OwenZeng_DBA 2017-12-27
  • 打赏
  • 举报
回复
引用 9 楼 yenange 的回复:
--先创建索引
CREATE INDEX ix_BettingUsers_CreateTime ON BettingUsers(CreateTime)

--执行稍简化后的SQL
SELECT A.*
FROM   (
			SELECT 
					ROW_NUMBER() OVER(ORDER BY CreateTime DESC) RowNumber,
					A.*,
					B.LotteryType,
					B.State,
					C.Account   AS UserAccount,
					C.NickName  AS UserNickName
			FROM   BettingUsers AS A
					INNER JOIN Bettings AS B
						ON  A.BettingID = B.ID
					INNER JOIN Users AS C
						ON  A.UserID = C.ID
			WHERE  B.IsChipped = 1
       ) AS A
WHERE  A.RowNumber BETWEEN 1 AND 30
很早以前的帖子了哦
二月十六 2017-09-26
  • 打赏
  • 举报
回复
引用 6 楼 aaa907638015 的回复:
引用 5 楼 sinat_28984567 的回复:
给CreateTime 加一个索引
试过,好像没啥屌用,,。
加上索引之后把执行计划贴上来
aaa907638015 2017-09-26
  • 打赏
  • 举报
回复
引用 5 楼 sinat_28984567 的回复:
给CreateTime 加一个索引
试过,好像没啥屌用,,。
二月十六 2017-09-26
  • 打赏
  • 举报
回复
给CreateTime 加一个索引
二月十六 2017-09-26
  • 打赏
  • 举报
回复
这样试试:
 SELECT Identifier ,			--主键
        ROW_NUMBER() OVER ( ORDER BY Identifier DESC ) AS RowIndex
 INTO   #tb1_1
 FROM   BettingUsers AS A
        INNER JOIN Bettings AS B ON A.BettingID = B.ID
        INNER JOIN Users AS C ON A.UserID = C.ID
 WHERE  B.IsChipped = 1


 SELECT Identifier
 INTO   #tb1_2
 FROM   #tb1_1 AS t
 WHERE  t.RowIndex BETWEEN 1 AND 30

 SELECT A.* ,
        B.LotteryType ,
        B.State ,
        C.Account AS UserAccount ,
        C.NickName AS UserNickName
 FROM   BettingUsers AS A
        INNER JOIN Bettings AS B ON A.BettingID = B.ID
        INNER JOIN Users AS C ON A.UserID = C.ID
 WHERE  B.IsChipped = 1
        AND Identifier IN ( SELECT  *					--主键在临时表中
                            FROM    #tb1_2 )
 ORDER BY Identifier DESC

 DROP TABLE #tb1_1 
 DROP TABLE #tb1_2
另外表关联字段加上索引;然后再贴出加上索引之后的执行计划看看。
繁花尽流年 2017-09-26
  • 打赏
  • 举报
回复
select top 30 A.*, B.LotteryType, B.State, C.Account as UserAccount, C.NickName as UserNickName from BettingUsers as A inner join Bettings as B on A.BettingID = B.ID inner join Users as C on A.UserID = C.ID where B.IsChipped = 1 order by a.CreateTime desc
aaa907638015 2017-09-26
  • 打赏
  • 举报
回复
引用 3 楼 sinat_28984567 的回复:
这样试试:
 SELECT Identifier ,			--主键
        ROW_NUMBER() OVER ( ORDER BY Identifier DESC ) AS RowIndex
 INTO   #tb1_1
 FROM   BettingUsers AS A
        INNER JOIN Bettings AS B ON A.BettingID = B.ID
        INNER JOIN Users AS C ON A.UserID = C.ID
 WHERE  B.IsChipped = 1


 SELECT Identifier
 INTO   #tb1_2
 FROM   #tb1_1 AS t
 WHERE  t.RowIndex BETWEEN 1 AND 30

 SELECT A.* ,
        B.LotteryType ,
        B.State ,
        C.Account AS UserAccount ,
        C.NickName AS UserNickName
 FROM   BettingUsers AS A
        INNER JOIN Bettings AS B ON A.BettingID = B.ID
        INNER JOIN Users AS C ON A.UserID = C.ID
 WHERE  B.IsChipped = 1
        AND Identifier IN ( SELECT  *					--主键在临时表中
                            FROM    #tb1_2 )
 ORDER BY Identifier DESC

 DROP TABLE #tb1_1 
 DROP TABLE #tb1_2
另外表关联字段加上索引;然后再贴出加上索引之后的执行计划看看。
发现问题在于这个order by CreateTime desc上面,但是又不得不用这个CreateTime排序。。。不知道何解了,,你的SQL试了下,时间在2秒左右,而且排序是根据ID来排的,改成CreateTime之后时间就一样了。 我这里数据量大概在60W左右。
aaa907638015 2017-09-26
  • 打赏
  • 举报
回复
自己拆分了下
select 
	top 10
	A.*, 
	B.LotteryType,
	B.State, 
	C.Account as UserAccount,
	C.NickName as UserNickName
from 
	BettingUsers as A 
inner join 
	Bettings as B 
on 
	A.BettingID = B.ID 
inner join 
	Users as C 
on 
	A.UserID = C.ID 
where 
	B.IsChipped = 1
上面这个SQL秒出结果,但是下面这个加了排序的就要5秒钟了,求解。。
select 
	top 10
	A.*, 
	B.LotteryType,
	B.State, 
	C.Account as UserAccount,
	C.NickName as UserNickName
from 
	BettingUsers as A 
inner join 
	Bettings as B 
on 
	A.BettingID = B.ID 
inner join 
	Users as C 
on 
	A.UserID = C.ID 
where 
	B.IsChipped = 1
order by A.CreateTime desc

11,848

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 非技术版
社区管理员
  • 非技术版社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧