全部
基础类
应用实例
新技术前沿

多表关联如何优化

wgybb 2008-03-13 06:01:32

我现在有四张表分别如下：
Buy(ID[PK],SmallClassID，shi,qu,Title,IsCheck)
SmallClass(ID[PK],SmallClassName)
shi(ID[PK],shiName)
qu(ID[PK],quName)

进行关联的语句如下：
SELECT Buy.ID,Buy.Title,Buy.SmallClassID,shi.shiName,qu.quName,SmallClass.SmallClassName

FROM (SELECT * FROM Buy WHERE SmallClassID=68 and shi=1 and IsCheck=1) AS

Buy,shi,qu,SmallClass WHERE Buy.shi=shi.ID AND Buy.qu=qu.ID AND

Buy.SmallClassID=SmallClass.ID

Buy表里面有50000多条记录，其它三个表数据量非常小，现在执行起来有CPU为80-100之间，请教各位这

条语句是否能再忧化一些。Buy这个表的涉及有INSERT,UPDATE,DELETE这些操作，有时候只与其中一个表

关联，有时候也可能与两个表进行关联。我想建索引，试问该怎样建才好
1：在Buy表里面分别对shi,qu,SmallclassID建三个索引
2：还是建一个组合索引create index IX_Buys on buy(shi,qu,SmallClass)

或者有更好的办法，请大指点一下，谢谢！

如果我建一个组合索引的话，那么假如当Buy表只与SmallClass表进行关联的时候，这个组合索引能不能

起到作用呢？

还有一点，我上面写的关联语句如果换成INNER JOIN这个，效率上有区别吗？

...全文

258 点赞收藏 8

写回复

8 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

wgybb 2008-03-14

上面的代码是我从存储过程里面摘取出来的，大家看一下，我的分页存储过程写的是否有问题，影响了效率。



CREATE PROCEDURE dbo.getBuyList

@iPageCount int OUTPUT, --总页数

@iPage int, --当前页号

@iPageSize int, --每页记录数



@strTemp nvarchar(200) --搜索条件



as

set nocount on

begin

if @strTemp is null return



--创建临时表 

create table #TempBuy (ID int IDENTITY, --自增字段

Buy_ID int,

Buy_InfoType tinyint,

Buy_Type nvarchar(16),

Buy_Title nvarchar(128),

Buy_Money nvarchar(16),

Buy_RegDate datetime,

Buy_Content text,

Buy_Picture nvarchar(32),

Buy_SmallClassID smallint,

Buy_LinkMan nvarchar(16),

Buy_Tel nvarchar(32),

Buy_shi nvarchar(16),

Buy_qu nvarchar(16),

Buy_SmallClassName nvarchar(16))

--设置临时表的主键

ALTER TABLE #TempBuy WITH NOCHECK ADD 

	CONSTRAINT [PK_#TempBuy] PRIMARY KEY  CLUSTERED 

	(

		[ID]

	)  ON [PRIMARY] 



Declare @strSQL nvarchar(1000)



Set @strSQL = N'insert into #TempBuy(Buy_ID,Buy_InfoType,Buy_Type,Buy_Title,Buy_Money,Buy_RegDate,Buy_Content,Buy_Picture,Buy_SmallClassID,Buy_LinkMan,Buy_Tel,Buy_shi,Buy_qu,Buy_SmallClassName) SELECT Buy.ID,Buy.InfoType,Buy.Type,Buy.Title,Buy.Money,Buy.RegDate,Buy.Content,Buy.Picture,Buy.SmallClassID,Buy.LinkMan,Buy.Tel,shi.shi,qu.qu,SmallClass1.SmallClassName FROM (((Buy INNER JOIN shi ON Buy.shi=shi.ID)) INNER JOIN qu ON Buy.qu=qu.ID) INNER JOIN SmallClass1 ON  Buy.SmallClassID=SmallClass1.ID  WHERE ' + @strTemp + ' order by case infoType when 0 then 0 else 1 end desc,RegDate desc,Buy.ID Desc;'



--向临时表中写入数据

execute sp_executesql @strSQL





--取得记录总数 

declare @iRecordCount int

set @iRecordCount = @@rowcount



--确定总页数

IF @iRecordCount%@iPageSize=0

SET @iPageCount=CEILING(@iRecordCount/@iPageSize)

ELSE

SET @iPageCount=CEILING(@iRecordCount/@iPageSize)+1



--若请求的页号大于总页数，则显示最后一页

IF @iPage > @iPageCount

SELECT @iPage = @iPageCount



--确定当前页的始末记录

DECLARE @iStart int --start record

DECLARE @iEnd int --end record

SELECT @iStart = (@iPage - 1) * @iPageSize

SELECT @iEnd = @iStart + @iPageSize + 1



--取当前页记录 

select * from #TempBuy where [ID]>@iStart and [ID]<@iEnd



--删除临时表

DROP TABLE #TempBuy



--返回记录总数

return @iRecordCount

end

GO

回复赞

wgybb 2008-03-14

CSDN昨天晚上出问题了，导致我发了这么多相同的贴子，我不是有意的。

多谢楼上朋友们的建议，还是分别建索引好了，因为Buy这个表有时候单独与shi表进行关联，也有可能与SmallClass表进行单独的关联，也有可能与其中两个表进行关联。如果我建一个组合索引create index IX_Buys on buy(shi,qu,SmallClass) 那是不是，当Buy表与其中一个表进行关联的时候，这个组合索引就起不到作用了？

回复赞

BoyHaXin 2008-03-14

http://zhenhaxin.blog.hexun.com/17580689_d.html

回复赞

qqhmitzk 2008-03-14

只现应出现的字段
建立索引少用in

回复赞

zbc1009 2008-03-13

建立索引是关键，语句逻辑相同，不好再怎么优化，
如果经常对shi,qu,SmallclassID中的一个做为条件查询，或者关联表，那么分开建索引。即
1：在Buy表里面分别对shi,qu,SmallclassID建三个索引
如果经常是三个一起做为条件，那么建立这个复合索引。
2：还是建一个组合索引create index IX_Buys on buy(shi,qu,SmallClass)

在这个例子里，你可以建立复合索引，索引都是要占空间的，如果服务器空间昂贵，数据量特别大，再想想别的办法。

回复赞

-狙击手- 2008-03-13

复合索引，个人观点

回复赞

dawugui 2008-03-13

语句没什么优化的.

主要是各表要建立主键.
要连接的字段建立索引.

回复赞

JiangHongTao 2008-03-13

SELECT Buy.ID,Buy.Title,Buy.SmallClassID,shi.shiName,qu.quName,SmallClass.SmallClassName

FROM Buy join shi on Buy.shi=shi.ID join qu on Buy.qu=qu.ID 

         join SmallClass on Buy.SmallClassID=SmallClass.ID 

where buy.SmallClassID=68 and buy.shi=1 and buy.IsCheck=1

感觉分别建索引好。

回复赞