求一个最优化的SQL语句

[wsine]

两个表A和B，
A表：
username pwd
小李 aaa
小王 bbb
小沈 ccc
.......

B表：
username score
小刘 80
小罗 95
小王 99
小杨 89
小李 90
....

现在是要：从A表取出记录，满足score大于80的条件。

由于数据量较大，怎么写SQL能最优化？

[xiaoxiangqing]

1.B表score建聚集索引 2.A表，B表username建索引

[u010685567]

厉害啊。人才

[u010685567]

呃，如果B表符合条件的比例不高的话，我觉得这个事分两步做比较好，第一步先把B表里面大于80的都找出来放到临时表里面，然后直接jion这个临时表就行了

[u010787907]

啊啊十大啊啊实打实大大四大阿大声道啊

[qingye2008]

居然没有ID,到处都是varchar关联,注定就是个悲剧.

[u010434451]

都是高手啊.！

[BuleRiver]

个人感觉可能3L的效率更高一些。

[HAPPENS]

对#25的内容修改了下，结果如下：

CREATE TABLE [dbo].[A]
(
[ID] [int] IDENTITY(1, 1) NOT NULL,
[username] [varchar](50) NULL,
[pwd] [nchar](10) NULL
)

GO

DECLARE @I INT
SET @I = 1;
INSERT INTO [dbo].[A]
VALUES
(
'NAME',
'PWS'
)
WHILE @I <= 20
BEGIN
INSERT INTO [dbo].[A]
SELECT [username],
[pwd]
FROM [dbo].[A]

SET @I = @I + 1;
END
GO

UPDATE [dbo].[A]
SET [username] = [username] + CAST([ID] AS VARCHAR)
GO


ALTER TABLE A ADD CONSTRAINT pk_id PRIMARY KEY NONCLUSTERED(id)
CREATE CLUSTERED INDEX idx_username ON A(username)

CREATE TABLE [dbo].[B](
[ID] [int] NOT NULL,
[username] [varchar](50) NULL,
[score] [nchar](10) NULL
) ON [PRIMARY]

GO

INSERT INTO [dbo].[B] SELECT id,
[username],
CASE WHEN ID%2=1
THEN 81 ELSE 79 END FROM [dbo].[A]
GO


ALTER TABLE B ADD CONSTRAINT pk_id PRIMARY KEY NONCLUSTERED(ID)
CREATE CLUSTERED INDEX idx_b_score ON B(score)
CREATE NONCLUSTERED INDEX idx_b_username ON B(username)

SELECT * FROM A WHERE NOT EXISTS(SELECT 1 FROM B WHERE A.username=b.username AND B.score <81)
SELECT * FROM A WHERE EXISTS ( SELECT 1 FROM b WHERE a.username = b.username AND b.score > 80 )
SELECT [dbo].[A] .[username],[pwd] FROM [dbo].[A]
INNER JOIN [dbo].[B] ON [dbo].[A].username=[dbo].[B].username AND [dbo].[B].score >80




分别是：28%，28%，44%

[HAPPENS]

主要是索引不同。

图不清，再截一张。



请25楼来点评下。

[sighshadow]

[无聊找乐]

select a.username, a.pwd where a.username = b.username and b.score > 80 这就行了 b.score建个索引

[魔者侠心之明星级程序员]

你好，请问需要评点什么？ 你这个图跟25楼的第一张图一样的。这个时候1，2种写法的效率高过第3种。

[YHL27]

顶一个哦。。。

[u010820252]

厉害啊，真的很厉害

[a1006292239]

看看 怎么样

[铁歌]

表结构主键使用INT型号，做exists或inner join关联时不要使用username而要使用int主键关联，开销会降低，楼上所述的indexed view还是管用的，相当于已经保存的大表关联结果再利用了

[球球之家/carver]

过来围观。。。。。

[编程的微小小]

好东西，我一般都是顶完了再看！

[liaicheng12]

感觉各种牛人和版主穿插其中，我就学习膜拜加打酱油

[maokyou]

引用 25 楼 SQL_Beginner 的回复:

多谢两位斑竹啦 先准备一下测试数据， 由于小数据看不成什么区分，所以两个表都建100W条数据。 USE [tempdb] GO CREATE TABLE [dbo].[A]( [ID] [int] IDENTITY(1,1) NOT NULL, [username] [varchar](50) NULL, [pwd] [nchar](10) NULL ) ON [PRIMARY] GO CREATE CLUSTERED INDEX [ClusteredIndex-20130520-133729] ON [dbo].[A] ( [username] ASC ) DECLARE @I INT SET @I=1; INSERT INTO [dbo].[A] VALUES('NAME','PWS') WHILE @I<=20 BEGIN INSERT INTO [dbo].[A] Select [username],[pwd] From [dbo].[A] SET @I=@I+1; END GO UPDATE [dbo].[A] SET [username]=[username] + CAST([ID] AS VARCHAR) GO -- CREATE TABLE [dbo].[B]( [ID] [int] NOT NULL, [username] [varchar](50) NULL, [score] [nchar](10) NULL ) ON [PRIMARY] GO CREATE CLUSTERED INDEX [ClusteredIndex-20130520-125704] ON [dbo].[B] ([username] ASC) GO CREATE NONCLUSTERED INDEX [NonClusteredIndex-20130520-125845] ON [dbo].[B] ([score] ASC) GO INSERT INTO [dbo].[B] SELECT id, [username], CASE WHEN ID%2=1 THEN 81 ELSE 79 END FROM [dbo].[A] GO ALTER TABLE [dbo].[A] DROP COLUMN [ID] GO ALTER TABLE [dbo].[B] DROP COLUMN [ID] GO --CREATE VIEW INDEXEDV1 WITH SCHEMABINDING AS --SELECT [dbo].[A] .[username],[pwd] FROM [dbo].[A] --INNER JOIN [dbo].[B] ON [dbo].[A].username=[dbo].[B].username AND [dbo].[B].score >80 --GO --CREATE UNIQUE CLUSTERED INDEX VDiscountInd ON INDEXEDV1 ([username]) 然后我们对比一下三种方式的cost ， 1,SELECT * FROM A WHERE NOT EXISTS(SELECT 1 FROM B WHERE A.username=b.username AND B.score <81) 2,SELECT * FROM A WHERE EXISTS ( SELECT 1 FROM b WHERE a.username = b.username AND b.score > 80 ) 3,SELECT [dbo].[A] .[username],[pwd] FROM [dbo].[A] INNER JOIN [dbo].[B] ON [dbo].[A].username=[dbo].[B].username AND [dbo].[B].score >80 看一下COST对比大概是30%，30%，40% 然后我们建INDEX VIEW， CREATE VIEW INDEXEDV1 WITH SCHEMABINDING AS SELECT [dbo].[A] .[username],[pwd] FROM [dbo].[A] INNER JOIN [dbo].[B] ON [dbo].[A].username=[dbo].[B].username AND [dbo].[B].score >80 GO CREATE UNIQUE CLUSTERED INDEX VDiscountInd ON INDEXEDV1 ([username]) Go 再次运行上面的语句，再看一下COST 现在的比如是47%,47%,7% 因为第三条 INNER JOIN 已经走了indexed view ，它的计划是clustered indexed scan，因为它正好包含了你需要的全部数据，所还有什么比clustered indexed scan更快的计划吗？没有啦。 缺点就是跟索引一样需要维护，另外一个缺点是值固定了。 那假如我查询满足score大于90的条件呢？ 这个需要需要把indexed view改得更加通用一点了 比如说可以这样建 CREATE VIEW INDEXEDV1 WITH SCHEMABINDING AS SELECT [dbo].[A] .[username],[pwd] ，[dbo].[B].score FROM [dbo].[A] INNER JOIN [dbo].[B] ON [dbo].[A].username=[dbo].[B].username 然后你可以在INDEXEDV1 再对dbo].[B].score建索引，至于能否用到就要看[dbo].[B].username的statistics, 主要好处就是实现链接固话，避免了表之间在运行的时候再做费事的join 操作的耗时。 另外，没有用indexed view之前，我发现用exists或者not exists，inner join 的cost，如果是HASH JOIN大概在20多，如果是LOOP JOIN 大概，是100多。特别计划会是Hash join，，这个时候相当的吃CPU。 加了INDEXED VIEW 后inner join的COST会将一个数量级。 就先这样了，好累。

表示这位大神已经属于深入研究SQL Server了!如果说大神是海底的大白鲨，我就是海面的沙丁鱼了，呵呵呵……