复合索引使用疑问

[lcw321321]

create table tb(a int,b int)

create index ix_tb on tb(a,b)

select * from tb where a=@a 
--会使用索引
select * from tb where b=@b 
--由于不包含该索引中的第一列所以不会使用索引

select * from tb where a=@a or b=@b
--会使用索引,why? 迷惑为什么这个语句也能使用到索引,or b=@b 应该是等同于下一句:
select * from  tb where a=@a --会用到索引
union 
select * from  tb where  b=@b --不会用到索引

[Garnett_KG]

mm,这个头像是你吗？

[永生天地]

[Quote=引用 8 楼 lcw321321 的回复:]
引用 4 楼 sql77 的回复:
select * from tb where a=@a or b=@b
--会使用索引,why? 迷惑为什么这个语句也能使用到索引,or b=@b 应该是等同于下一句: select * from tb where a=@a --会用到索引 union select * from tb where b=@b --不会用到索引

谁说的等同……

我也……
[/Quote]

为什么OR就不能用索引呢？索引就像书的目录，为什么OR就不能用。。。

[lcw321321]

[Quote=引用 12 楼 xiaoliaoyun 的回复:]
这个应该是Index Seek

SQL code

select * from tb where a=@a



这个应该是Index Scan

SQL code

select * from tb where a=@a or b=@b



Seek和Scan是不同的.而且b也在索引里面,Scan索引也可以得到b,索引优化器选择index scan也不是不行……
[/Quote]
顶这个。一般来说索引查找比索引扫描效率会高很多。
也就是说就算 select * from tb where b=@b 也是索引扫描？等下我测试下。

[lcw321321]

[Quote=引用 13 楼 sql_server_central 的回复:]
引用 9 楼 lcw321321 的回复:
引用 3 楼 ldslove 的回复:
这个查询优化器来决定的。。。除非人为指定

嗯，这个我也赞同，我是指一般情况下，比如说：数据均匀分布，索引碎片很低，索引数据大部分为唯一值等。这样的话，查询优化器就会向我们指定的方向走了。

可以根据需要强制让他使用索引

SQL code
with index()
[/Quote]

强制使用索引，有时候只会带来更大的性能问题。

[lcw321321]

[Quote=引用 4 楼 sql77 的回复:]
select * from tb where a=@a or b=@b
--会使用索引,why? 迷惑为什么这个语句也能使用到索引,or b=@b 应该是等同于下一句:
select * from tb where a=@a --会用到索引
union
select * from tb where b=@b --不会用到索引

谁说的等同……
[/Quote]
不等同的话，区别在哪里呢？其实我今天进来就是等这个。哈哈

[guguda2008]

[Quote=引用 18 楼 lcw321321 的回复:]

引用 4 楼 sql77 的回复:
select * from tb where a=@a or b=@b
--会使用索引,why? 迷惑为什么这个语句也能使用到索引,or b=@b 应该是等同于下一句:
select * from tb where a=@a --会用到索引
union
select * from tb where b=@b --不会用到索引

谁说的等同……

……
[/Quote]
一个是扫一遍，一个是扫两遍，就是这里不同。结果集是一样的

[SQL77]

[Quote=引用 19 楼 guguda2008 的回复:]
引用 18 楼 lcw321321 的回复:

引用 4 楼 sql77 的回复:
select * from tb where a=@a or b=@b
--会使用索引,why? 迷惑为什么这个语句也能使用到索引,or b=@b 应该是等同于下一句:
select * from tb where a=@a --会用到索引
union
select * from tb where b……
[/Quote]

[lcw321321]

[Quote=引用 19 楼 guguda2008 的回复:]
引用 18 楼 lcw321321 的回复:

引用 4 楼 sql77 的回复:
select * from tb where a=@a or b=@b
--会使用索引,why? 迷惑为什么这个语句也能使用到索引,or b=@b 应该是等同于下一句:
select * from tb where a=@a --会用到索引
union
select * from tb where b……
[/Quote]

应该是一个是扫描一遍索引，一个是查找一遍索引+扫描一遍索引，对不？

[xiaoliaoyun]

[Quote=引用 18 楼 lcw321321 的回复:]
不等同的话，区别在哪里呢？其实我今天进来就是等这个。哈哈
[/Quote]

两个结果是一样的,UNION会分成2个分别查询,还会有一个去处重复值的步骤

[lcw321321]

[Quote=引用 22 楼 xiaoliaoyun 的回复:]
引用 18 楼 lcw321321 的回复:
不等同的话，区别在哪里呢？其实我今天进来就是等这个。哈哈


两个结果是一样的,UNION会分成2个分别查询,还会有一个去处重复值的步骤
[/Quote]
嗯，其实如果结果集大的话，这个去重复值的过程也比较耗CPU的。

[lcw321321]

经过测试
create table tb(a int,b int)

create index ix_tb on tb(a,b)

select * from tb where b=@b

--由于不包含该索引中的第一列所以不会使用索引
--通过执行计划查看，上句为错误的，实际上能够用到索引，只不过是索引扫描而不是索引查找而已，索引SCAN 虽然也不理想，不过在很多情况下比全表扫描也要好的多。谢谢各位。

[youyou2404]

bangding

[ok1234567]

复合索引主要用于：
1、查询结果的索引覆盖（返回的字段全在索引中）
2、固定模式的条件查询
如果不是以上情形，通常不采用复合索引

[ShenLiang2025]

--Ⅰ以下为引用部分
例如，如果将索引定义为 LastName、FirstName，则该索引在搜索条件为 WHERE LastName = 'Smith' 或 WHERE LastName = Smith AND FirstName LIKE 'J%' 时将很有用。不过，查询优化器不会将此索引用于基于 FirstName (WHERE FirstName = 'Jane') 而搜索的查询。

--Ⅱ解释
①
当你建立一个复合索引(以LastName为Key Column),即是
CREATE INDEX IX_LastName_FirstName ON tbl_TestCSDN(LastName,FirstName)
时MS SQL会自动产生基于字段LastName的统计信息(Statistics).尽管是复合索引,MS SQL仅仅存放
主字段,复合索引的第一个字段的统计信息.所以复合索引的第一个字段选取很重要.

②
索引的选取和Statistics关系很大.这个直方图似的统计信息会展现当前表的数据的稠密和分布情况.


③
同样你可以考虑覆盖索引中的INCLUDE方式.
CREATE INDEX IX_LastName_FirstName ON tbl_TestCSDN(LastName)INCLUDE(FirstName)

④  ... ...

[lcw321321]

SQL 2008 联机帮助--》索引--》设计索引：

如果索引包含多个列，则应考虑列的顺序。用于等于 (=)、大于 (>)、小于 (<) 或 BETWEEN 搜索条件的 WHERE 子句或者参与联接的列应该放在最前面。其他列应该基于其非重复级别进行排序，就是说，从最不重复的列到最重复的列。

例如，如果将索引定义为 LastName、FirstName，则该索引在搜索条件为 WHERE LastName = 'Smith' 或 WHERE LastName = Smith AND FirstName LIKE 'J%' 时将很有用。不过，查询优化器不会将此索引用于基于 FirstName (WHERE FirstName = 'Jane') 而搜索的查询。

上面这句话感觉有些含糊，经过本地测试，基于 FirstName (WHERE FirstName = 'Jane') 是有索引扫描而不是索引查找，如果一行的数据占BIT比较大，而索引又比较小而且唯一性非常高，那么就算是索引扫描也比全表扫描快很多。

[kakav]

楼主，
select * from tb where a=@a or b=@b --不会用到索引的
select * from tb where a=@a and b=@b --才会用到索引的

[rodger_yanglq]

复合索引的话create index ix_tb on tb(a,b)
where只有包含a和b列或者只包含A列内容是才会应用索引。

[lcw321321]

[Quote=引用 29 楼 kakav 的回复:]
楼主，
select * from tb where a=@a or b=@b --不会用到索引的
select * from tb where a=@a and b=@b --才会用到索引的
[/Quote]
select * from tb where a=@a or b=@b --是可以利用到索引的，不过是SCAN而不是SEEK

[ShenLiang2025]

--Ⅰ：理论相关
关于Index Scan和Index Seek
其中Index Scan和Table Scan类似。也即是一行行的扫描,不过这里对有序的数据页和索引项扫描.
Index Scan和Table Scan常常发生在没有过滤条件的查询里或则没有合适的的Search Argument(SARG)
Index Scan比Table Scan相对快 是以为Index Scan是对一个索引项对应的数据项列表作为一个逻辑读
然而Table Scan则是对每条数据做一个逻辑读.

而Index Seek则是对范围进行搜索

CREATE TABLE Test_CSDN
(id int,
v_code varchar(32),
v_name varchar(32),
v_detail varcahr(32))

CREATE CLUSTERE INDEX Test_CSDN_v_code ON Test_CSDN( v_code )

如果查询如下：

①SELECT * FROM Test_CSDN WHERE v_code = 'S0001'

②SELECT * FROM Test_CSDN WHERE v_code LIKE 'S%' ...

MS SQL多半会选择Clustered Index Seek。原因是Test_CSDN_v_code维护了Test_CSDN的物理顺序,
而且这里的过滤条件恰是聚集索引字段。

Seek比Scan快的原因是Seek限定了查询索引项的范围,
比如查询②,其实等效于 SELECT * FROM Test_CSDN WHERE v_code  > = 'S' AND v_code < 't'

Test_CSDN_v_code会首先在索引项(Index key + Page number)中找到S开头的v_code对应的page number,
然后根据改number 到数据页去查找数据.

因此相对Clustered Index Scan而言,Clustered Index Seek范围要小.当然也要看SARG的过滤程度了.

同理可以分析非聚集Index.

--Ⅱ关于复合索引的测试相关

--打开执行计划文本格式
SET STATISTICS PROFILE ON
GO

SELECT COUNT(*) FROM T1 --1000

SELECT * FROM T1 WHERE 1 = 0
----
a           b           x

CREATE INDEX T1_a_b ON T1 (a,b)

点击对象浏览器(Object Explorer)
点看T1表下的Statistics
	Column Statistics: _WA_Sys_00000001_0425A276
	Index Statistics: T1_a_b

--查询分析

SELECT * FROM T1 WHERE a = 12

--因未建立聚集索引,故这里是RID Lookup,显然这里是Index Seek
/*
SELECT * FROM [T1] WHERE [a]=@1
  |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
       |--Index Seek(OBJECT:([TestingShen].[dbo].[T1].[T1_a_b]), SEEK:([TestingShen].[dbo].[T1].[a]=(12)) ORDERED FORWARD)
       |--RID Lookup(OBJECT:([TestingShen].[dbo].[T1]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
*/

SELECT * FROM T1 WHERE b = 20 

--因Statistic仅仅建立在字段a上所以不能像上面那样定位b的范围 故为Index Scan
/*
SELECT * FROM [T1] WHERE [b]=@1
  |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
       |--Index Scan(OBJECT:([TestingShen].[dbo].[T1].[T1_a_b]),  WHERE:([TestingShen].[dbo].[T1].[b]=(20)))
       |--RID Lookup(OBJECT:([TestingShen].[dbo].[T1]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
*/

--同理可以分析

--OR因为将查询的范围扩大,故不可以在a的范围内限定数据
SELECT * FROM T1 WHERE b = 20 OR a = 12
--
/*
SELECT * FROM T1 WHERE b = 20 OR a = 12
  |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
       |--Index Scan(OBJECT:([TestingShen].[dbo].[T1].[T1_a_b]),  WHERE:([TestingShen].[dbo].[T1].[b]=(20) OR [TestingShen].[dbo].[T1].[a]=(12)))
       |--RID Lookup(OBJECT:([TestingShen].[dbo].[T1]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
*/

--AND可以将查询的范围缩小,因索引字段为a和b 而且statistic会为a而建立,所以这里也是Index Seek
SELECT * FROM T1 WHERE b = 20 AND a = 12
--
/*
SELECT * FROM [T1] WHERE [b]=@1 AND [a]=@2
  |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
       |--Index Seek(OBJECT:([TestingShen].[dbo].[T1].[T1_a_b]), SEEK:([TestingShen].[dbo].[T1].[a]=(12) AND [TestingShen].[dbo].[T1].[b]=(20)) ORDERED FORWARD)
       |--RID Lookup(OBJECT:([TestingShen].[dbo].[T1]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
*/

[kakav]

[Quote=引用 31 楼 lcw321321 的回复:]

引用 29 楼 kakav 的回复:
楼主，
select * from tb where a=@a or b=@b --不会用到索引的
select * from tb where a=@a and b=@b --才会用到索引的

select * from tb where a=@a or b=@b --是可以利用到索引的，不过是SCAN而不是SEEK
[/Quote]

楼主的意思就是seek算利用 scan不算。