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

1樓：強哥

關於你這種情況，可能是因為表中資料量太小，資料庫判斷走索引，還不如走全表掃瞄快。

也有可能是索引建立後，表中的資料發生了比較大的變化，表的統計資訊已經有很大的變化，但統計資訊並沒有重新收集。

這種情況，重新收集下表的統計資訊就可以了。

當然，索引建立後，也可能會失效，需要將索引重建一下。

2樓：黃贇

本質上，是優化器在比較回讀與全表掃瞄的成本。

回讀，需要從索引頁儲存結構的本質說起。

最底層的索引資料頁除了儲存索引值以外，還儲存了主表的 RowId 或者聚集索引表索引值。每當查詢的字段不在索引包含列中時，需要依據 RowId 或者聚集索引表索引值重回主表去讀一遍資料頁，這個資料頁上有查詢所用的字段。所以SELECT * 一下就要讀兩個資料庫結構，而事實上我們卻不需要讀所有字段，此時就浪費了資源。

很多時候，我們會有個誤區，Seek 一定比 Scan 效率高，其實不盡然。如果查詢需要的資料分布佔據了 80% 的表資料量，那麼 Seek 更慢，因為那層回讀在此時就是多餘的。Seek 既有 Clustered Index Seek 也有 Non-Clustered Index Seek, 分別針對 Clustered Index Table 和 Non-Clustered Index 而言。

Clustered Index Seek 比起 Non-Clustered Index Seek 的好處在於，它包含了各個字段，不需要再一次的回讀。但有個限制，每張表僅可有乙個 Clustered Index.

回讀，更專業一點來講，叫做 Bookmark Lookup. 碰到回讀這種情況，優化器會做出兩種選擇，一是真正地去回讀，二是全表掃瞄原表。判斷依據在於查詢所要結果集，是否占有大量原表資料。

沒錯，這裡肯定有乙個閾值，超過這個閾值，優化器會選擇全表掃瞄，而摒棄用 Bookmark Lookup, 因為 Bookmark lookup 是採用 random 讀，耗時較長。

如何才能用得上你的索引？

去掉 SELECT * , 改成 SELECT （索引用到的那些字段） FROM t1 便可。即使不是 Seek 操作，Index Scan 操作也能接受。

3樓：大江

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

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

ps：sqlserver和mysql的b+樹是在資料也上的～postgresql是單獨的乙份～

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

4樓：

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

索引使用B+TREE，當資料量夠大時，搜尋會使用index。

全表搜尋使用的是記憶體，index本身很大，絕大部分時間是以索引檔案形式存在。

I/O讀取比記憶體消耗資源更大。

當資料量足夠，來舉個栗子

id為索引

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

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

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

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

5樓：

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

乙個例子:

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

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

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

之前使用的RBO進行解釋

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

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

嘖嘖，輪帶逛帶歪了@vczh

6樓：李晨曦

這個問題並不弱智，需要對查詢優化器比較了解才能理解為什麼沒有用索引，而查詢優化器是資料庫裡非常複雜的部分。它的目標是接受使用者的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;

7樓：yunanw

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

多插入點資料，重新生成統計資訊試試

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

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

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

為什麼我在quora上看到的回答幾乎都是印度人的？

我為什麼在感情上是個雙標狗？

為什麼我經常看到許嵩在知乎上上熱榜？

其他用戶還看了：