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

[newdigitime]

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

注意是select 1
因为很多资料说 条件中有or时,往往会使索引无效.
如果上面的name以及mail字段都建了索引.
上面的SQL命令能不能利用上索引呢?

[dla001]

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

即使有index，用or有时会有出现index失效的情况。
将or拆开成 union all 会防止此现象。

[guguda2008]

[Quote=引用楼主 newdigitime 的回复:]
SQL code

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


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

[冷箫轻笛]

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

or会导致索引失效
所以，应该用不上索引

[guguda2008]

[Quote=引用 3 楼 coolingpipe 的回复:]

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

or会导致索引失效
所以，应该用不上索引
[/Quote]
这个我是测试过的，等我写个测试语句你跑一下就明了了

[SQL_Hhy]

or 可以用到索引

[SQL77]

楼主不测试一下?

[claro]

鼓掌！

[guguda2008]

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)
*/

[Mr_Nice]

[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]

路过，学习...

[xiaoxiangqing]

不要用or,用or是不会用到索引

[feilniu]

--创建表
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，数据库引擎也会根据查询计划决定是否使用索引。上面的第一个查询即用了索引。

[zhangweiit]

开始我也不是很确定，查阅了各种资料，
可以说，or 语句，也是可以用到索引的
只要两个字段都有建索引

关于从or 转换成union的问题，其实我们可以不用自己转换的
oracle 是会自动优化，不懂sqlserver 是不是这样,但我相信05,08这两个版本，一定会做不少优化工作