TSQL不使用索引 [英] TSQL not using indexes

问题描述

这是一个测试服务器，用临时表来说明问题。
假装表@userdata在userid
上有索引，表@users在id上有索引

This is a test senario, made with temporary tables to illustrate the problem. Pretend table @userdata has index on userid and table @users has index on id

为什么第一个select无法使用索引，I假设它在1个子选择中比在2个子选择中表现更好？

Why is the first select unable to use index, I assumed it would perform better in 1 subselect than in 2 subselects?

版本 - Microsoft SQL Server 2008 R2（RTM）
兼容级别 - SQL Server 2000. / p>

Version - Microsoft SQL Server 2008 R2 (RTM) Compatibility level - SQL Server 2000.

-- test tables
DECLARE @userdata TABLE(info VARCHAR(50), userid INT)
DECLARE @users    TABLE(id INT, username VARCHAR(20), superuser BIT)

-- test data
INSERT @users    VALUES(1, 'superuser', 1)
INSERT @users    VALUES(2, 'testuser1', 0)
INSERT @users    VALUES(3, 'testuser2', 0)
INSERT @userdata VALUES('secret information', 1)
INSERT @userdata VALUES('testuser1''s data', 2)
INSERT @userdata VALUES('testuser2''s data', 3)
INSERT @userdata VALUES('testuser2''s data',3)

DECLARE @username VARCHAR(50)
SET @username = 'superuser'
--SET @username = 'testuser1'


--The superuser can read all data
--The testusers can only read own data

-- This sql can't use indexes and is very slow
SELECT *
FROM @userdata d
WHERE EXISTS 
(SELECT 1 FROM @users u
WHERE u.username = @username AND u.superuser = 1 OR 
u.id = d.userid AND u.username = @username)

-- This sql uses indexes and performs well
SELECT *
FROM @userdata d
WHERE EXISTS 
(SELECT 1 FROM @users u
WHERE u.username = @username AND u.superuser = 1)
OR EXISTS (SELECT 1 FROM @users u
WHERE u.ID = d.userid 
AND u.username = @username)

推荐答案

我认为或可能会导致一些麻烦查询分析器拿出一个很好的查询计划。
这不是你问题的真正答案，而是另一种做这个查询的方法。除了你已有的索引，我建议在@ users.username上建一个索引。

I think that or can cause some trouble for the query analyzer to come up with a good query plan. This is not really an answer to your question but an alternative way of doing this query. Apart from the index you already have I would suggest an index on @users.username.

if exists(select * from @users where username = @username and superuser = 1)
begin
  select *
  from @userdata
end
else
begin
  select d.*
  from @userdata as d
    inner join @users as u
      on d.userid = u.id
  where u.username = @username
end

这篇关于TSQL不使用索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！