為什麼我在Sql Server上創建的索引用不上？

12-29

水平低問了這樣一個弱智問題，請大家不要見怪。
這是測試示例，首先創建表和索引
CREATE TABLE T1 ( Id int NOT NULL primary key, I1 int NOT NULL, I2 int NOT NULL, I3 int NOT NULL, )
CREATE INDEX Index_T1_I2 ON T1 (I2 ASC)

現在執行索引對應欄位的查詢語句
select*from T1 where I2=0
但是創建的索引在查詢的時候並不會用上，為什麼會這樣？要怎樣創建索引，執行上述Sql語句的時候才會用上？

這個問題並不弱智，需要對查詢優化器比較了解才能理解為什麼沒有用索引，而查詢優化器是資料庫里非常複雜的部分。它的目標是接受用戶的SQL語句，生成一個最好的查詢計劃。

不過這個問題還是比較容易解釋的，查詢優化器在選擇查詢計劃的時候，會對不同查詢計划進行評估（通過一個cost function），選擇它認為執行最快的查詢計劃。

你這個查詢可以有兩種查詢計劃，一個是表掃描(table scan)，一個是利用索引(B+tree)

至於這兩個計劃哪個快，需要看具體的數據是怎樣的，並不是索引總會比表掃描快，舉個例子：

如果你那個表T1有100萬行記錄，其中50萬行的Ｉ２都是1，selectivity為50%，這時明顯用表掃描快，因為用表掃描，掃描全部100萬行記錄就可以了。而如果用B+tree，如果底層存儲是索引組織表，由於你的Ｉ2是非聚簇索引，需要現在Ｉ２的B+tree中找Ｉ２為1的記錄的主鍵Id，然後再去Id的B+tree中找這個tuple，才能完成一次查找，然後由於非聚簇B+樹索引中Ｉ２為1的元素所在葉子節點通過指針連接起來了，後面的不需要再在Ｉ２的B+tree的根節點出發，直接通過鏈表可以獲得，但仍然需要50萬次的Id的B+tree的查找，顯然效率很差。雖然如果底層存儲為堆表，只需要在Ｉ２的B+tree中找就可以了，但仍需要通過指針到底層存儲，去獲取整個tuple，效率扔不如表掃描。

但如果你那個表中只有1行的Ｉ２為1，selectivity幾乎為0，那麼只需要一次索引掃描就可以了，而用表掃描仍需要掃表整個表的所有數據，因此此時用索引更好。

根據經驗來看，selectivity為10%是臨界點（使用普通硬碟時），低於10%用索引更優，高於10%用表掃描更優，不過實際上要根據硬體進行調整，比如你用的是普通硬碟還是SSD。

你可以看一下你查詢結果的數量，然後和總行數比較一下，比值應該是比較大的。

select count(*) from T1 where I2=1;

select count(*) from T1;

手邊沒有環境，你試試select的時候指定欄位列表。另外是不是數據量比較小導致的？

首先我不建議有事沒事建個索引。優化查詢的手段有很多，建索引不一定有效。

從Oracle的角度來說，當一個表上全表掃描比走索引快的時候，就用全表掃描。

一個例子:

有一個庫使用的機械硬碟，資料庫看執行計劃，使用了索引。

後來這個庫遷移到一台SSD上，資料庫改用了全表掃描。

10G後使用的CBD進行SQL解釋

之前使用的RBO進行解釋

區別是CBO是先進行計算，預估一個資源消耗值，然後生成執行計劃。

而RBO是根據規則，一條一條的進行執行計劃。

嘖嘖，輪帶逛帶歪了@vczh

很簡單，加索引不代表會用索引，資料庫走執行計劃是算cost算出來的，而且依據是統計信息，統計信息是抽樣獲得的，估計t2=0的數據根據統計信息算出來佔全表的數量很大，所以走索引cost還不如走全表scan～

還有，按你的索引建立 sqlserver用select *的話，即使走的是t2的索引，仍然需要look up聚集索引，因為sqlseserver的非聚集索引默認只含索引值，其他值不包括，必須從聚集索引獲得～所以，sqlserver或出現聚集索引和非聚集索引發生死鎖的怪事～

ps：

sqlserver和mysql的b+樹是在數據也上的～

postgresql是單獨的一份～

資料庫理論很重要，不同產品的實現也需要理解～

1樓主沒說數據大小，就現象來看，我認為是數據太少原因.

多插入點數據，重新生成統計信息試試

2就是你select * 用i2找到以後，還要書籤查找，反而不去直接聚集快了

強制指定使用i2，然後看查詢計劃，是不是使用了書籤查找

看一下I2各種值的佔比

最高票的答主回答的很全了。

索引使用B+TREE，當數據量夠大時，搜索會使用index。

全表搜索使用的是內存，index本身很大，絕大部分時間是以索引文件形式存在。

I/O讀取比內存消耗資源更大。

當數據量足夠，來舉個栗子

id為索引

這個栗子其實並不能完全說明，變數不唯一（線上庫），盡量選擇了在穩定時期來跑著兩個語句。

資料庫會選擇最優查詢計劃來進行任務，這個栗子在半夜時結果可能完全相反。

6000+數據，量小，理論上應該是用全表查詢，事實結果卻是index比全表更快。因為這個時間資料庫還在跑著不同的任務，可能在幾秒前這個表剛剛被查詢過。

餓的腦子不夠用，表述不夠清楚。湊合看吧。。。

可以考慮下用條件索引。