本文主要作為優(yōu)化查詢性能的一些知識儲備，感覺知識點(diǎn)有些散，不知道起啥名字好，獨(dú)立成文又沒有達(dá)到把每個點(diǎn)都說透徹那樣的高度，且就當(dāng)做創(chuàng)建合理索引的一個楔子把。本文對實(shí)際應(yīng)用沒有太大的指導(dǎo)意義，但可以加深我們對SQL Server理解，夯實(shí)我們的基本功，就像小說里面的武功一樣，沒有足夠的內(nèi)功基礎(chǔ)，給你再好的秘籍你也成不了武林高手。
序言
寫這篇文章時表示鴨梨很大，主要是對SQL Server的認(rèn)識很有限，遠(yuǎn)遠(yuǎn)不足把這個話題說清楚，不過還是鼓起勇氣寫出來，也算作自己對索引認(rèn)識的一個總結(jié)。索引這潭水太深了，應(yīng)用場景不同，所建立的索引在有些情況下運(yùn)行良好，有些情況下可能完全無效。而對于索引理解、認(rèn)識層次不同，怎樣建才比較合理的也是眾說分云，用萬金油的說法就是沒有最合理的只有最合適的。一般來說最懂數(shù)據(jù)庫的當(dāng)屬DBA，不過DBA卻不懂業(yè)務(wù)，一般除了一些核心業(yè)務(wù)模塊表建立及索引維護(hù)有DBA完成外，大多數(shù)情況下索引、SQL維護(hù)都是有開發(fā)人員完成的，因此我們通常認(rèn)為的索引建立、優(yōu)化都是有開發(fā)人員完成的（并不是我無視DBA，事實(shí)就是這個情況），可能有人疑惑了，DBA不維護(hù)索引、不寫SQL那他們都干嘛吃去了，這個俺還真不知道，不過DBA很忙、很累我是知道的，估計、可能、大概會做以下事情：數(shù)據(jù)庫并發(fā)、分發(fā)復(fù)制、性能監(jiān)控、數(shù)據(jù)遷移、備份、日常維護(hù)、索引、SQL調(diào)優(yōu)等等，DBA童鞋別噴我，我是真的不知道，DBA童鞋們可以留言告訴我下哈。
下面簡單說一些暫時想到的對使用索引影響較大的幾個注意點(diǎn)
頁是SQL Server存儲數(shù)據(jù)的基本單位，大小為8KB。
請一定要記住，頁是SQL Server進(jìn)行數(shù)據(jù)讀寫的最小I/O單位，而不是行。SQL Server中一個頁大小為8KB，每8個頁形成一個區(qū)，每頁8KB，其中頁頭占用96個字節(jié)，頁尾的行指示器占用2個字節(jié)，還有幾個保留字節(jié)，也就是一個頁8192個字節(jié)，能用了存儲數(shù)據(jù)的實(shí)際約8000個字節(jié)，8000個字節(jié)一般可以存儲很多行數(shù)據(jù)。即便SQL Server只訪問一行數(shù)據(jù)，它也要把整個頁加載到緩存并從緩存讀取數(shù)據(jù)，我們通常所說的開銷主要就是I/O開銷，這點(diǎn)不少人都沒有清醒的認(rèn)知。
認(rèn)識窄索引
很多書籍和文章都寫過索引要使用窄索引，窄索引是個什么東東呢，大白話就是把索引建在字節(jié)長度比較小的列上，比如int占用4個字節(jié)，bigint占用8個字節(jié)，char（20）占用20個字節(jié)nchar（20）最占用40個字節(jié)，那么int 相對于bigint來說就是窄了（占用字節(jié)數(shù)更少），bigint比char（20）也要窄，char（20）和nchar（20）相比要窄（nchar（20）每個字符占用2個字節(jié)）。
明白了啥是窄索引我們來說下為什么要使用窄索引，我們知道數(shù)據(jù)存儲和讀取的最小單位是頁，一個頁8K大小，當(dāng)使用比較窄的列做索引列時，每個頁能存儲的數(shù)據(jù)就更多，以int和bigint為例，一個8K的頁大約能存儲8*1024/4（int 4個字節(jié)）=2048（實(shí)際值要比這個數(shù)字小）條數(shù)據(jù)，使用bigint大約能存儲8*1024/8（bigint為8個字節(jié)）=1024（實(shí)際值要比這個數(shù)字小）條數(shù)據(jù)，也就是說索引列的長度也小，每個頁能存儲的數(shù)據(jù)也就越多，反過來說就是存儲索引所需要的頁數(shù)也就越少，頁數(shù)少了進(jìn)行索引查找時需要檢索的頁自然也就少了，檢索頁數(shù)少了IO開銷也就隨之減少，查詢效率自然也就高了。
認(rèn)識索引的二叉樹級數(shù)
SQL Server中所有的索引都是平衡二叉樹結(jié)構(gòu)，平衡樹的意思是所有葉子節(jié)點(diǎn)到根節(jié)點(diǎn)的距離都相同，SQL Server進(jìn)行索引查找時總是從索引的根節(jié)點(diǎn)開始，并從根跳到下一級的相應(yīng)頁，并繼續(xù)從一個級別跳到下一個級別，直到達(dá)把可以查找鍵的葉子頁。所有葉級節(jié)點(diǎn)到底跟的距離都是相同的，這意味著一次查找操作在葉讀取上的成本正好是頁的級數(shù)。索引級數(shù)大多為2級到4級，2級索引大約包含幾千行，3級索引大約4 000 000行，4級索引能容納約4 000 000 000 行，這點(diǎn)上聚集索引和非聚集索引上是一樣的。一般來說對于小表來說索引通常只有2級，對于大表通常包含3級或4級。
索引級數(shù)也就意味著一次索引查找的最小開銷，比如我們建立一個User表
CREATE TABLE Users
(
UserID INT IDENTITY,
UserName nvarchar(50),
Age INT,
Gender BIT,
CreateTime DateTime
) --在UserID列上創(chuàng)建聚集索引 IX_UserID
CREATE UNIQUE CLUSTERED INDEX IX_UserID ON dbo.Users(UserID)
--插入示例數(shù)據(jù)
insert into Users(UserName,Age,Gender,CreateTime)
select N'Bob',20,1,'2012-5-1'
union all
select N'Jack',23,0,'2012-5-2'
union all
select N'Robert',28,1,'2012-5-3'
union all
select N'Janet',40,0,'2012-5-9'
union all
select N'Michael',22,1,'2012-5-2'
union all
select N'Laura',16,1,'2012-5-1'
union all
select N'Anne',36,1,'2012-5-7' |
我們執(zhí)行查詢
| SELECT * FROM dbo.Users WHERE UserID=1 |
可以看到輸出信息為
(1 行受影響)
表 'Users'。掃描計數(shù) 0，邏輯讀取 2 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。 |
當(dāng)表數(shù)據(jù)量增多至幾千條時，執(zhí)行上述查詢邏輯讀依然為2，當(dāng)數(shù)據(jù)量到達(dá)百萬級別時邏輯讀會變成3，當(dāng)?shù)竭_(dá)千萬、億級別時，邏輯讀就會變成4，有興趣的童鞋可以親自去試驗下。
認(rèn)識書簽查找的開銷
當(dāng)使用非聚集索引時，若查詢條件沒有實(shí)現(xiàn)索引覆蓋就會形成書簽查找，那么一次書簽查找的開銷是多少呢？答案是不一定，一般為1到4次邏輯讀，對于堆表（無聚集索引的表）來說，一次書簽查找只需要一次邏輯讀，對于非堆表（有聚集索引的表）來說一次書簽查找的邏輯讀次數(shù)為聚集索引的級數(shù)，在索引結(jié)構(gòu)我們簡單說了下大多數(shù)聚集索引的級數(shù)為2-4級，也就是說對于非堆表來說一次邏輯讀的開銷為2-4次邏輯讀，下面我們做具體測試
創(chuàng)建非聚集索引 IX_UserName
| CREATE INDEX IX_UserName ON dbo.Users(UserName) |
執(zhí)行查詢
| SELECT UserID,UserName FROM dbo.Users WITH(INDEX(IX_UserName))WHERE UserName =('Bob') |
由于我們的查詢實(shí)現(xiàn)了索引覆蓋，沒有書簽查找，獲取一條數(shù)據(jù)需要2次邏輯讀
(1 行受影響)
表 'Users'。掃描計數(shù) 1，邏輯讀取 2 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。 (1 行受影響) |
修改查詢，返回列中增加CreateTime，這樣就無法實(shí)現(xiàn)索引覆蓋
| SELECT UserID,UserName,CreateTime FROM dbo.Users WITH(INDEX(IX_UserName))WHERE UserName =('Bob') |
可以看到這時發(fā)生了書簽查找，同樣返回一條數(shù)據(jù)，邏輯讀達(dá)到了4次（索引查找2次，書簽查找2次），由于我們的表Users為非堆表，故一次書簽查找需要2次（聚集索引IS_UserID的級數(shù)為2）邏輯讀
(1 行受影響)
表 'Users'。掃描計數(shù) 1，邏輯讀取 4 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。 (1 行受影響) |
然后我們繼續(xù)測試堆表的書簽查找開銷，刪除表Users的聚集索引IX_UserID，使其變?yōu)槎驯?/p>
| DROP INDEX IX_UserID ON dbo.Users |
再次執(zhí)行我們的查詢，可以看到邏輯讀變成了3次（索引查找2次，書簽查找1次），想想為什么堆表的書簽查找次數(shù)少呢？
| SELECT UserID,UserName,CreateTime FROM dbo.Users WITH(INDEX(IX_UserName))WHERE UserName =('Bob') |
(1 行受影響)
表 'Users'。掃描計數(shù) 1，邏輯讀取 3 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。 (1 行受影響) |
使用索引時的隨機(jī)讀
嗯，關(guān)于隨機(jī)讀我現(xiàn)在也有些迷糊，只是大概的知道發(fā)生隨即讀時即便需要的數(shù)據(jù)就存在同一頁上，也會發(fā)生多次讀取，而不是一次拿到整頁數(shù)據(jù)后進(jìn)行篩選。當(dāng)進(jìn)行where in查找或發(fā)生書簽查找時，一定會使用隨機(jī)讀
首先我們看看聚集索引的隨即讀表現(xiàn)
--創(chuàng)建聚集索引IX_UserID
CREATE UNIQUE CLUSTERED INDEX IX_UserID ON dbo.Users(UserID) |
執(zhí)行如下查詢，可以發(fā)現(xiàn)在聚集索引上面使用where in時不管有沒有找到記錄都會進(jìn)行聚集索引查找，而且查找次數(shù)固定為where in里面的條件數(shù)*索引級數(shù)，不知道為什么明明索引掃描有著更高的效率，查詢優(yōu)化器還是選擇聚集索引查找，有知道的朋友還請告知下哈
--這個聚集索引掃描，返回記錄7條，邏輯讀2次
SELECT * FROM dbo.Users
--這個聚集索引查找，返回記錄3條，邏輯讀2次
SELECT * FROM dbo.Users WHERE UserID<=2
--這個聚集索引查找，返回記錄3條，邏輯讀6次
SELECT * FROM dbo.Users WHERE UserID IN(1,2)
--這個聚集索引查找，返回記錄0條，邏輯讀6次
SELECT * FROM dbo.Users WHERE UserID IN(10,20,22) |
(7 行受影響)
表 'Users'。掃描計數(shù) 1，邏輯讀取 2 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。 (1 行受影響) (3 行受影響)
表 'Users'。掃描計數(shù) 1，邏輯讀取 2 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。 (1 行受影響) (3 行受影響)
表 'Users'。掃描計數(shù) 3，邏輯讀取 6 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。 (1 行受影響) (0 行受影響)
表 'Users'。掃描計數(shù) 3，邏輯讀取 6 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。 (1 行受影響) |
我們再來看下非聚集索引的隨機(jī)讀，當(dāng)然我這里為了演示特意使用了索引提示，實(shí)際應(yīng)用中沒事千萬別加索引提示，當(dāng)使用非聚集索引時查詢優(yōu)化器發(fā)現(xiàn)使用索引后效率更低時會放棄索引轉(zhuǎn)為使用表掃描，我們這個例子中若去掉索引提示的話使用表掃描僅需要2次邏輯讀就可以完成查詢，這里僅僅是為了演示
--非聚集索引查找，返回記錄2,邏輯讀2
SELECT UserID,UserName FROM dbo.Users WITH(INDEX(IX_UserName))WHERE UserName LIKE 'J%'
--非聚集索引查找，返回記錄2，邏輯讀4
SELECT UserID,UserName FROM dbo.Users WITH(INDEX(IX_UserName))WHERE UserName IN('Bob','Jack') --非聚集索引查找+書簽查找，返回記錄2,邏輯讀6
SELECT UserID,UserName,CreateTime FROM dbo.Users WITH(INDEX(IX_UserName))WHERE UserName LIKE 'J%' --非聚集索引查找+書簽查找，返回記錄0，邏輯讀2(索引查找2*1+書簽查找2*0）
SELECT UserID,UserName,CreateTime FROM dbo.Users WITH(INDEX(IX_UserName))WHERE UserName IN('Rabbit')
--非聚集索引查找+書簽查找，返回記錄1，邏輯讀4(索引查找2*1+書簽查找2*1）
SELECT UserID,UserName,CreateTime FROM dbo.Users WITH(INDEX(IX_UserName))WHERE UserName IN('Bob')
--非聚集索引查找+書簽查找，返回記錄2，邏輯讀8(索引查找2*2+書簽查找2*2）
SELECT UserID,UserName,CreateTime FROM dbo.Users WITH(INDEX(IX_UserName))WHERE UserName IN('Bob','Jack') |
(2 行受影響)
表 'Users'。掃描計數(shù) 1，邏輯讀取 2 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。 (1 行受影響) (2 行受影響)
表 'Users'。掃描計數(shù) 2，邏輯讀取 4 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。 (1 行受影響) (2 行受影響)
表 'Users'。掃描計數(shù) 1，邏輯讀取 6 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。 (1 行受影響) (0 行受影響)
表 'Worktable'。掃描計數(shù) 0，邏輯讀取 0 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。
表 'Users'。掃描計數(shù) 1，邏輯讀取 2 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。 (1 行受影響) (1 行受影響)
表 'Users'。掃描計數(shù) 1，邏輯讀取 4 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。 (1 行受影響) (2 行受影響)
表 'Users'。掃描計數(shù) 2，邏輯讀取 8 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。 (1 行受影響) |
總結(jié)：
認(rèn)識書索引的查找開銷、書簽查找的開銷、隨機(jī)讀的影響對我們具體創(chuàng)建索引和編寫SQL有著積極的影響，畢竟對查詢的的認(rèn)識更加清楚了，我們在寫索引和SQL時候才有更有針對性，最起碼我們又知道了一個盡量不適用where in的理由，為什么要盡量規(guī)避書簽查找，聚集索引查找不見得就一定高效，順便留個問題：聚集索引掃描和非聚集索引掃描哪個有著更高的效率，什么情況下會發(fā)生非聚集索引掃描？
嗯，暫且寫到這里，還是腦子里的墨水太少了，寫點(diǎn)東西就感覺腦子里空蕩蕩的......
原文出自:http://www.cnblogs.com/lzrabbit/archive/2012/06/11/2517963.html