多表关联如何优化

[wgybb]

我现在有四张表分别如下：
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这个，效率上有区别吗？

[JiangHongTao]

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

感觉分别建索引好。

[dawugui]

语句没什么优化的.

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

[-狙击手-]

复合索引 ，个人观点

[zbc1009]

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

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

[qqhmitzk]

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

[BoyHaXin]

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

[wgybb]

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

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

[wgybb]

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

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