數據庫如何決定使用哪個索引

DDL

create table t
(
   id int,
   id1 int
)

create index Example_Index
 On t(id,id1)

create index Example1_Index
 On t(id1,id)

---

DML

insert into t(id, id1)values(1, 100)
insert into t(id, id1)values(1, 101)
insert into t(id, id1)values(2, 103)
insert into t(id, id1)values(1, 104)
insert into t(id, id1)values(3, 105)
insert into t(id, id1)values(1, 106)
insert into t(id, id1)values(2, 107)
insert into t(id, id1)values(3, 108)

---

Sql 查詢- select * from t Where id = 107 – 使用Example_Index

Sql 查詢- select * from t Where id1 = 107– 使用Example1_Index

Sql 查詢- select * from t – 使用Example1_Index

困惑- 系統如何決定使用Example1_Index

以下是詳細資訊…

使用了最新創建的索引或正在使用具有更高索引 ID 的索引。這顯示在下面的查詢中，但這只是基於以下查詢的觀察結果，可能不是一致的行為，因為我無法從 MS 中找到與此相關的任何文件。執行下面的查詢，然後通過交換兩個索引創建語句來執行相同的查詢。

但是只要優化器選擇最好的索引它就不應該打擾你。在這種情況下，兩個索引是相同的。

drop table t
go
create table t 
( 
   id int, 
   id1 int 
) 


create index Example_Index 
 On t(id,id1) 

create index Example1_Index 
 On t(id1,id) 
go
insert into t(id, id1)values(1, 100) 
insert into t(id, id1)values(1, 101) 
insert into t(id, id1)values(2, 103) 
insert into t(id, id1)values(1, 104) 
insert into t(id, id1)values(3, 105) 
insert into t(id, id1)values(1, 106) 
insert into t(id, id1)values(2, 107) 
insert into t(id, id1)values(3, 108) 
go
select * from sys.dm_db_index_physical_stats(9,object_id('t'),null,null,'DETAILED')
go
dbcc freeproccache
go
select * from t Where id = 107 -- uses Example_Index

select * from t Where id1 = 107 -- uses Example1_Index

select * from t -- uses Example1_Index
go
dbcc freeproccache
go
select * from t Where id = 107 -- uses Example_Index

select * from t -- uses still Example1_Index even though that index is not used at all.However, this index id is 3 higher than example_index.
go
--now try the same thing but change the order of the index and you will see example_index is used.

引用自：https://dba.stackexchange.com/questions/21183