摘要：我們通過建立”idx_isMan“索引後，就會構建右半圖的B樹結構，其中索引記錄會存放兩個值，一個是索引值isMan和一個聚集索引值ID，如果你不相信的話，可以通過DBCC Page去探索"idx_isMan"的索引頁，你也可以通過DBCC SHOW_STATISTICS 去查看，如圖：。通過上面的三個圖，大概可以看到， 10w條數據用了697數據頁，其中聚集索引有521個，非聚集索引爲176個，這也說明了上面的”聚集索引掃描“走遍了它自己所有的數據頁來才撈出數據， 同時還發現這兩個索引都有一個共同特徵就是，只有一個根節點(indexLevel=1）和無數個（indexLevel=0）葉子節點，然後我腦子裏面就有一幅圖出來了。

我們在學sqlserver的時候，大多教科書和前輩們都說狀態少的字段不要建索引，由此帶來的開銷還不如不建索引，但是這句話有多少人真的知道，或者說有多少人真的對此有比較深刻的理解，而不是聽別人道聽途說。。。這樣記得快，忘記的也不慢。。。這篇我來分析一下這句話到底有幾個意思。

一：現象

首先我們還是用測試數據來發現問題，我先建立一個Person，有5個字段，建表sql如下：

DROP TABLE dbo.Person

CREATE TABLE Person(ID INT PRIMARY KEY IDENTITY,NAME VARCHAR(900),Age INT,Email VARCHAR(20),isMan INT )

-- 在isMan字段創建非聚集索引（0：女 1：男）
CREATE INDEX idx_isMan ON dbo.Person(isMan)

DECLARE @ch AS INT=0

WHILE @ch<=100000
BEGIN
    INSERT INTO dbo.Person(NAME,Age,Email,isMan)
    VALUES
    (
      REPLICATE(CHAR(@ch),50),
      @ch,
      CAST(CAST(RAND()*1000000000 AS INT) AS VARCHAR(10))+'qq.com',
      @ch%2
    )
    SET @ch=@ch+1
END

通過上面的sql發現ID爲聚集索引，isMan爲非聚集索引，isMan也就是兩種狀態（0,1），並且插入10w條記錄，截圖如下：

sql都做完了，接下來要做的事情就是查詢下：isMan=1的記錄，如下圖：

我靠。。。我明明是在isMan上做數據檢索的，怎麼就變成 “聚集索引掃描”了？？？這sqlserver什麼意思嘛，居然不走我的“idx_isMan”索引，卻走他的“聚集索引(PK Person 3214EC276EF57B66)”。。。。同時也看到上面的”邏輯讀取”爲521。。。說明在內存中走了521個數據頁。但是我不服呀。。。我一定要讓執行計劃走我的索引。。。辦法就是強制指定。。。如下圖。

看到上面的圖，你是不是已經瘋了。。。我才撈5w的數據，你給我走了10w多次數據頁。。。這麼說1條記錄要走兩個數據頁。。。而掃描聚集索引才走521個數據頁，相差200倍。。。難怪執行計劃打死也不走“idx_isMan”這條索引。。。

二：分析原因

現在很生氣，整個人都不好了，爲什麼會這樣？？？爲了找出問題，我們還得看數據頁。

DBCC TRACEON(3604,2588)
DBCC IND(Ctrip,Person,-1)

通過上面的三個圖，大概可以看到， 10w條數據用了697數據頁，其中聚集索引有521個，非聚集索引爲176個，這也說明了上面的”聚集索引掃描“走遍了它自己所有的數據頁來才撈出數據， 同時還發現這兩個索引都有一個共同特徵就是，只有一個根節點(indexLevel=1）和無數個（indexLevel=0）葉子節點，然後我腦子裏面就有一幅圖出來了。。。

上面就是我構思出來的圖，這個專業一點的名字叫做書籤查找。。。我們通過建立”idx_isMan“索引後，就會構建右半圖的B樹結構，其中索引記錄會存放兩個值，一個是索引值isMan和一個聚集索引值ID，如果你不相信的話，可以通過DBCC Page去探索"idx_isMan"的索引頁，你也可以通過DBCC SHOW_STATISTICS 去查看，如圖：

然後引擎通過“idx_isMan“掃描後，拿到了key值，但是非常可惜，我是select * 的，所以必須還要噴出記錄中的Name，Emai等l字段，但是”index_isMan"中並沒有保存這幾個字段，所以必須通過key去”聚集索引“的B樹中去找。。。最後通過”聚集索引“的B樹找到了目標記錄，這也就是所謂的執行計劃中的”鍵查找“，然後噴出”Name，Email“等字段。。。。 問題就在這裏。。。因爲我這樣來回的蹦躂蹦躂。。。造成了找出完整的一個記錄，需要蹦躂2-3次數據頁。 。。具體的尋找記錄，可參考圖中的”紫色線條“，最後也就造成了10w多次蹦躂。。。

三：啓示

那這個例子給我們什麼啓示呢？？？仔細想想你就知道。。。使用非聚集索引，千萬不要撈取過多的數據。。。因爲過多的數據會造成在多個B樹中來回的蹦躂。。。想要做到撈取數據較少，就必須在高唯一性的字段上建立索引，這樣的話在非聚集索引B樹中符合的數據相對較少，也就減少了我蹦躂到”主鍵索引“的B樹次數。。。這樣的話來回蹦躂的次數遠遠比”聚集索引“掃描來的實惠，對不對。。。

四：結論

必須在唯一性較高的字段上建立非聚集索引。

相關文章