这样的查询能不能利用上索引?

newdigitime 2010-07-01 08:01:46

select 1 from table where name=@name or mail=@mail

注意是select 1
因为很多资料说 条件中有or时,往往会使索引无效.
如果上面的name以及mail字段都建了索引.
上面的SQL命令能不能利用上索引呢?
...全文
117 点赞 收藏 12
写回复
12 条回复
zhangweiit 2010年07月02日
开始我也不是很确定,查阅了各种资料,
可以说,or 语句,也是可以用到索引的
只要两个字段都有建索引

关于从or 转换成union的问题,其实我们可以不用自己转换的
oracle 是会自动优化,不懂sqlserver 是不是这样,但我相信05,08这两个版本,一定会做不少优化工作
回复 点赞
feilniu 2010年07月02日

--创建表
CREATE TABLE tblUser(
UserID int NOT NULL PRIMARY KEY CLUSTERED,
UserName nvarchar(20) NOT NULL,
Birthday datetime NOT NULL)
--生成测试数据
INSERT INTO tblUser
SELECT UserID = 1000000 + n,
UserName = 'U' + CAST(1000000 + n AS varchar(10)),
Birthday = DATEADD(day,ABS(CHECKSUM(NEWID()))%18262,'19500101')
FROM dbo.Nums
WHERE n <= 100000
--创建非聚集索引
CREATE INDEX IX_tblUser_UserName ON tblUser(UserName)
CREATE INDEX IX_tblUser_Birthday ON tblUser(Birthday)

--查询测试

--(20264 行受影响)
--查询开销:22%
SELECT * FROM tblUser WHERE UserName LIKE 'U10001%' OR Birthday BETWEEN '19800101' AND '19900101'

--(20264 行受影响)
--查询开销:40%
SELECT * FROM tblUser WHERE UserName LIKE 'U10001%'
UNION
SELECT * FROM tblUser WHERE Birthday BETWEEN '19800101' AND '19900101'

--(20286 行受影响) 数据存在重复
--查询开销:38%
SELECT * FROM tblUser WHERE UserName LIKE 'U10001%'
UNION ALL
SELECT * FROM tblUser WHERE Birthday BETWEEN '19800101' AND '19900101'


通常情况下,直接用OR比UNION效率高。UNION ALL固然比UNION效率略高些,但在这种情况下结果是不对的,因为没有去重。
即使查询条件中有OR,数据库引擎也会根据查询计划决定是否使用索引。上面的第一个查询即用了索引。
回复 点赞
xiaoxiangqing 2010年07月02日
不要用or,用or是不会用到索引
回复 点赞
Mr_Nice 2010年07月02日
[Quote=引用 8 楼 guguda2008 的回复:]

SQL code
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(COL1 INT,COL2 INT,COL3 INT)
INSERT INTO TB
SELECT
CHECKSUM(NEWID()),CHECKSUM(NEWID()),CHECKSUM(NEWID())
FROM master..spt_val……
[/Quote]

路过,学习...
回复 点赞
guguda2008 2010年07月02日
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(COL1 INT,COL2 INT,COL3 INT)
INSERT INTO TB
SELECT
CHECKSUM(NEWID()),CHECKSUM(NEWID()),CHECKSUM(NEWID())
FROM master..spt_values T1
where type='P'

CREATE NONCLUSTERED INDEX INX_TB_COL1 ON TB(COL1)
CREATE NONCLUSTERED INDEX INX_TB_COL2 ON TB(COL2)

SET SHOWPLAN_TEXT ON
GO
SELECT * FROM TB WHERE COL1='1500690911' OR COL2='-1927829915'
/*
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
|--Stream Aggregate(GROUP BY:([Bmk1000]))
| |--Merge Join(Concatenation)
| |--Index Seek(OBJECT:([tempdb].[dbo].[TB].[INX_TB_COL1]), SEEK:([tempdb].[dbo].[TB].[COL1]=(1500690911)) ORDERED FORWARD)
| |--Index Seek(OBJECT:([tempdb].[dbo].[TB].[INX_TB_COL2]), SEEK:([tempdb].[dbo].[TB].[COL2]=(-1927829915)) ORDERED FORWARD)
|--RID Lookup(OBJECT:([tempdb].[dbo].[TB]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
*/
回复 点赞
claro 2010年07月02日
鼓掌!
回复 点赞
SQL77 2010年07月02日
楼主不测试一下?
回复 点赞
SQL_Hhy 2010年07月02日
or 可以用到索引
回复 点赞
guguda2008 2010年07月02日
[Quote=引用 3 楼 coolingpipe 的回复:]

跟楼上的观点相反
个人认为一个查询中只能用到一个索引
所以name和mail上是否都有索引跟 这个查询是否能用到索引 是两回事

or会导致索引失效
所以,应该用不上索引
[/Quote]
这个我是测试过的,等我写个测试语句你跑一下就明了了
回复 点赞
冷箫轻笛 2010年07月02日
跟楼上的观点相反
个人认为一个查询中只能用到一个索引
所以name和mail上是否都有索引跟 这个查询是否能用到索引 是两回事

or会导致索引失效
所以,应该用不上索引
回复 点赞
guguda2008 2010年07月02日
[Quote=引用楼主 newdigitime 的回复:]
SQL code

select 1 from table where name=@name or mail=@mail


注意是select 1
因为很多资料说 条件中有or时,往往会使索引无效.
如果上面的name以及mail字段都建了索引.
上面的SQL命令能不能利用上索引呢?
[/Quote]
如果name和mail上分别有一个索引,可以用到。
回复 点赞
dla001 2010年07月02日

select 1 from table where [name]=@name
union all
select 1 from table where [mail]=@mail

即使有index,用or有时会有出现index失效的情况。
将or拆开成 union all 会防止此现象。
回复 点赞
发动态
发帖子
应用实例
创建于2007-09-28

1.1w+

社区成员

6.8w+

社区内容

MS-SQL Server 应用实例
社区公告
暂无公告