sqlserver的索引建立

標籤：int   索引   遇到   serve   物理   並且   主鍵   維護   根據   

隨著系統資料的增多，一些查詢逐漸層慢，這時候我們可以根據sqlserver的執行計畫，查看sql的開銷，然後根據開銷建立索引。

索引有叢集索引與非叢集索引。

叢集索引：叢集索引在儲存上是按照順序儲存的，就像字典裡的漢字。

非叢集索引：實體儲存體不連續，但邏輯上是連續的，因為單獨維護著資料的儲存位置與資料的關係。

首先寫入100000資料

DECLARE @i INT,    @num int    SET @i=0   SET @num=100000    WHILE @i<=@num    BEGIN     IF NOT EXISTS(SELECT * FROM  dbo.meter_manage WHERE meter_id=@i)     INSERT INTO dbo.meter_manage             ( meter_id ,               meter_no ,               meter_name              )     VALUES             ( @i , -- meter_id - int               ‘asdasd‘+CONVERT(VARCHAR(20),@i), -- meter_no - varchar(500)               ‘asdsf‘++CONVERT(VARCHAR(20),@i)  -- meter_name - varchar(500)             );             SET @i=@i+1;    END      go

 

 

 

非叢集索引的建立：

create NONCLUSTERED INDEX index1 ON meter_manage(meter_no)  

效果：

select * from meter_manage where meter_no=‘asdasd2‘

建立非叢集索引之前，耗時23毫秒左右

建立非叢集索引之後，瞬間完成

 經常使用多條件陳述式查詢時，我們可建立複合索引。

select * from meter_manage where meter_no=‘asdasd2‘ and meter_name=‘asdsf2‘

未建立非叢集索引，耗時30毫秒：

在meter_no欄位建立單索引，耗時3毫秒：

create NONCLUSTERED INDEX index1 ON meter_manage(meter_no)  

條件查詢位置更換：

select * from meter_manage where meter_name=‘asdsf2‘ and  meter_no=‘asdasd2‘ 

查詢速度沒變，同樣3毫秒。

我們同時在另一個欄位meter_name上也建立一個非叢集索引：

create NONCLUSTERED INDEX index2 ON meter_manage(meter_name)  

發現兩個非叢集索引的時間與一個叢集索引的時間沒有太大變化，查看執行計畫，只命中了index1索引：

 

分析：

我們來想象一下當資料庫有N個索引並且查詢中分別都要用上他們的情況：
查詢最佳化工具（用大白話說就是產生執行計畫的那個東西）需要進行N次主二叉樹尋找[這裡主二叉樹的意思是最外層的索引節點]，此處的尋找流程大概如下：
查出第一條column1主二叉樹等於1的值，然後去第二條column2主二叉樹查出foo的值並且當前行的coumn1必須等於1，最後去column主二叉樹尋找bar的值並且column1必須等於1和column2必須等於foo。
如果這樣的流程被查詢最佳化工具執行一遍，就算不死也半條命了，查詢最佳化工具可等不及把以上計劃都執行一遍，貪婪演算法（最近鄰居演算法）可不允許這種情況的發生，所以當遇到以下語句的時候，資料庫只要用到第一個篩選列的索引（column1），就會直接去進行表掃描了。

select count(1) from table1 where column1 = 1 and column2 = ‘foo‘ and column3 = ‘bar‘

所以與其說是資料庫只支援一條查詢語句只使用一個索引，倒不如說N條獨立索引同時在一條語句使用的消耗比只使用一個索引還要慢。
所以如上條的情況，最佳推薦是使用index(column1,column2,column3） 這種聯合索引，此聯合索引可以把b+tree結構的優勢發揮得淋漓盡致：
一條主二叉樹（column=1），查詢到column=1節點後基於當前節點進行二級二叉樹column2=foo的查詢，在二級二叉樹查詢到column2=foo後，去三級二叉樹column3=bar尋找。

結論：兩個單獨索引通常資料庫只能使用其中一個

建立複合索引：

create index idx1 on meter_manage(meter_no,meter_name) 

瞬間完成，發現多條件下適合建立複合索引。

條件位置改變一下

select * from meter_manage where meter_name=‘asdsf2‘ and  meter_no=‘asdasd2‘ 

同樣瞬間完成。查看執行計畫命中了idx1

我們去掉二個條件：

select * from meter_manage where meter_no=‘asdasd2‘

同樣瞬間完成，也命中了索引  idx1

我們去掉第一個條件：

select * from meter_manage where meter_name=‘asdsf2‘

耗時27毫秒，與不加索引沒什麼區別，查看執行計畫，發現雖然命中了idx1

 

但是類型卻是Index Scan，與之前的Index Seek不同

 區別：

[Table Scan] 表掃描（最慢），對錶記錄逐行進行檢查

[Clustered Index Scan] 叢集索引掃描（較慢），按叢集索引對記錄逐行進行檢查

[Index Scan] 索引掃描（普通），根據索引濾出部分資料在進行逐行檢查

[Index Seek] 索引尋找（較快），根據索引定位記錄所在位置再取出記錄

[Clustered Index Seek] 叢集索引尋找（最快），直接根據叢集索引擷取記錄

因此，欄位上同時存在叢集索引與非叢集索引，這種情況下只會命中叢集索引，因為叢集索引最快，例如：主鍵上建立非叢集索引

create NONCLUSTERED INDEX index3 ON meter_manage(meter_id)  

瞬間完成，執行計畫：

 

sqlserver的索引建立