先說(shuō)說(shuō)這些誤區(qū)。所謂“誤區(qū)”，有一些是新手很容易犯的錯(cuò)誤或者很容易忽略的問(wèn)題，另外一些，則是像“耗子吃了鹽會(huì)變成蝙蝠”一樣，讓我們從小就認(rèn)為是正確的事情。如下：

1、? 表上不管用得著用不著，都加個(gè)聚集索引。

我們知道，表以?xún)煞N方式組織物理存儲(chǔ)：有聚集索引的“聚集表”；沒(méi)有聚集索引的“堆”。在聚集表中，數(shù)據(jù)行按照聚集索引的順序存儲(chǔ)（這也是為啥一張表最多只能有一個(gè)聚集索引的原因）；堆中，數(shù)據(jù)行的存儲(chǔ)可以認(rèn)為是不確定的。

在偶《寫(xiě)有效率的 SQL 查詢(xún)（ II ）》中曾經(jīng)介紹過(guò) DB 引擎如何在聚集表中通過(guò)非聚集索引查找目標(biāo)數(shù)據(jù)：從非聚集索引樹(shù)根開(kāi)始 seek ，查找到目標(biāo)索引行，然后通過(guò)索引行上存儲(chǔ)的聚集索引鍵值，爬聚集索引樹(shù)，并最終通過(guò)聚集索引行上的指針拿到目標(biāo)數(shù)據(jù)。

但是堆上的非聚集索引存儲(chǔ)的不是聚集索引鍵值，它存儲(chǔ)的是指向目標(biāo)行的指針。也就是說(shuō)，如果在同樣的表是堆，通過(guò)非聚集索引 seek 數(shù)據(jù)將省掉爬聚集索引樹(shù)的損耗，而可以直接通過(guò)非聚集索引行上的行指針直接拿到目標(biāo)數(shù)據(jù)。也就是說(shuō)，在某些情況下，使用堆可以提高系統(tǒng)效率。

這個(gè)“某些情況”，就是你的需求，你的系統(tǒng)行為。一般情況下，所有人對(duì)要在什么樣的字段上創(chuàng)建聚集索引都非常了解；但是不是所有的人都對(duì)應(yīng)該在什么樣的系統(tǒng)行為下，不創(chuàng)建聚集索引了解。假設(shè)你的表中有字段 col1, col2,col3,col4 等等， col1 、 col2 的分布密度很低。你觀察了系統(tǒng)行為，發(fā)現(xiàn)一半的查詢(xún)是 XXXX where col1 = YYYY ，另一半的查詢(xún)是 XXXX where col2 = YYYY 。這種情況下，使用堆就是更好的選擇。

2、? primary key 就是聚集索引。

primary key 上是得有索引，但是這個(gè)索引可不見(jiàn)得一定得是聚集索引。盡管語(yǔ)句

create table testPK

(

??? ?????? id int identity ( 1 , 1 ) primary key ,

??? ?????? fname varchar ( 64 )

)

會(huì)在 id 列上創(chuàng)建聚集索引。當(dāng)然，一般主鍵都是聚集索引，但也僅僅是“一般”而已。個(gè)人感覺(jué)，聚集索引的唯一目標(biāo)就是數(shù)據(jù)檢索，它應(yīng)該建在什么字段上，完全由系統(tǒng)行為決定。“一般主鍵都是聚集索引”也僅僅是因?yàn)槎鄶?shù)情況下， primary key 字段上建所有更有益于效率而已。

create table testPK

(

??? ?????? id int identity ( 1 , 1 ) primary key nonclustered ,

??? ?????? fname varchar ( 64 )

)

可以創(chuàng)建primary key為非聚集索引

3、? Log 類(lèi)的表，有事沒(méi)事加個(gè)自增的 Id 列。

這事相信干過(guò)的人很多，哈，而且一般還會(huì)順手在這個(gè) Id 列上加上個(gè) primary key 的約束，聚集索引也就被無(wú)意識(shí)的建上了。就像一個(gè)記錄用戶(hù)活動(dòng)的日志表，一般會(huì)有這么幾個(gè)典型字段： Id 、 LogTime 、 UserId 。實(shí)際上對(duì)這種表的查詢(xún)，大多集中在 LogTime 和 UserId 上， Id 完全沒(méi)有實(shí)際意義。你的客服系統(tǒng)查找的，可能僅僅是某個(gè)用戶(hù)的操作記錄（一般按時(shí)間排序的），或者你的報(bào)表系統(tǒng)要生成每天的用戶(hù)操作統(tǒng)計(jì)。想想看，如果干脆砍了 Id 列，并直接把聚集索引創(chuàng)建在 LogTime 上多爽。

4、? 是個(gè)表就給加個(gè) primary key 約束

就像 3 中的例子， primary 完全沒(méi)必要。呵呵，這條看著簡(jiǎn)單是簡(jiǎn)單了，犯這錯(cuò)誤的人，那也不比 3 少。

5、? 在 where 條件里對(duì)同一個(gè)表中的列做運(yùn)算或比較，以為創(chuàng)建某種類(lèi)型的索引可以提高效率。（這種情況下，任何索引都無(wú)法提升性能。解決辦法見(jiàn)偶前面的“寫(xiě)有效率的 sql 查詢(xún)”）

見(jiàn)過(guò)了無(wú)數(shù)的這種寫(xiě)法。最常見(jiàn)的，如：一張用戶(hù)表里有用戶(hù)注冊(cè)時(shí)間（ t1,YYYYDD ），有退訂時(shí)間（ t2,YYYYDD ），現(xiàn)在讓你獲取存活時(shí)間大于 3 天的用戶(hù)總數(shù)：很多人一不注意，就整一個(gè) select count(*) from Users where t1 – t2 > 3 出來(lái)。而且常常會(huì)臆測(cè)在 t1 、 t2 上建個(gè)涵蓋索引（或者分別在 t1 、 t2 上建索引）會(huì)讓性能提升。

6、? 在表上創(chuàng)建了 col1 、 col2 順序的涵蓋索引（聚集的或非聚集的），但是 where 條件里就一個(gè) col2 > XXX 。這種情況下，就不如分別在 col1 、 col2 上創(chuàng)建索引。

?

以上的誤區(qū)，都是在工作中常常犯或遇到的，沒(méi)遇到的肯定還有，歡迎各位安達(dá)補(bǔ)充：）

?

OK ，接下來(lái)我們說(shuō)說(shuō)“涵蓋索引”和 include 索引。

所謂的涵蓋索引，就是傳統(tǒng)方式在多個(gè)列上創(chuàng)建的索引。“ inlude 索引”是 SQL2k5 提供的新功能，允許添加非鍵列到非聚集索引的葉節(jié)點(diǎn)上。

創(chuàng)建涵蓋索引：

create index ix_tb_col1_col2 on tb

(

??? ??? col1 ,

??? ??? col2

)

創(chuàng)建 include 索引：

create index ix_tb_col1 on tb

(

??? ??? col1

) include ( col2 , col3 , col4 )

???????? 涵蓋索引和 include 索引的區(qū)別在于，涵蓋索引的所有列都是鍵列，索引行的物理存儲(chǔ)順序就是 col1 、 col2 的順序，這也是誤區(qū) 6 之所以稱(chēng)為誤區(qū)的原因。涵蓋索引可以是聚集索引，也可以是非聚集索引。

???????? include 索引 include 的列并不影響索引行的物理存儲(chǔ)順序，它們作為一個(gè)掛件“掛在”索引行上。掛著這些掛件的作用在于，諸如 select col2, col3, col4 from tb where col1 = XXX 只需要 seek 一把非聚集索引 ix_tb1_col1 就 OK 了，拿到索引行就拿到了需要的所有數(shù)據(jù)。掛件們是要占用索引行空間的，我們知道，索引字段寬度要盡可能窄是選擇索引的一項(xiàng)基本原則（這項(xiàng)原則背后的原理是盡可能讓索引樹(shù)深度小），所以并不是 include 的字段越多越好，這得跟你的系統(tǒng)行為有一個(gè)平衡。

???????? 從上面敘述可以看到，涵蓋索引實(shí)際上是 include 索引的加強(qiáng)版。也就是說(shuō)，你的 where 條件里除了涵蓋索引的第一個(gè)索引列之外還有其他索引列的比較，創(chuàng)建涵蓋索引要比 include 索引高效一點(diǎn)點(diǎn)。同樣，維護(hù)涵蓋索引的消耗也會(huì)多少高于 Include 索引。

???????? 聚集索引的索引行直接包含了數(shù)據(jù)行指針，也就是說(shuō)，通過(guò)聚集索引行，可以直接拿到其他所有列的數(shù)據(jù)，從某種意義上說(shuō)，聚集索引就是最大的 include 索引，這也是 include 索引只能是非聚集索引的原因所在。

???????? OK ，給你一條 SQL 語(yǔ)句：

select col1, col2, col3, col4 from tb where col5 > XXX and col6 > yyy

你既可以在上面創(chuàng)建涵蓋索引 col5 、 col6 ，又可以創(chuàng)建 include 索引（ col5/col6 ） include(col1 、 col2 、 col3 、 col4) 。選擇如何創(chuàng)建，就要看你的表各字段寬度、系統(tǒng)行為了。在此不再贅述。

?

最后講講如何拿到在文中頻頻提到的系統(tǒng)行為統(tǒng)計(jì)信息。這東西說(shuō)白了就是各種 SQL 的執(zhí)行次數(shù)、邏輯 IO 、物理 IO 、執(zhí)行消耗 CPU 時(shí)間等等等等。想想看，假如你拿了一份系統(tǒng)中所有 SQL 的文本、執(zhí)行總次數(shù)、邏輯 IO 占用總 IO 比例、物理 IO 占用總 IO 比例、平均邏輯 IO 、平均物理 IO 等等等等，你八成能夠指出系統(tǒng)瓶頸所在，老板和伙計(jì)們的眼光也會(huì)會(huì)極大的滿(mǎn)足你小小的虛榮心，哈。這些東西就在動(dòng)態(tài)視圖 sys.dm_exec_query_stats 里面，自個(gè)翻翻聯(lián)機(jī)文檔吧：）

拿到系統(tǒng)行為統(tǒng)計(jì)信息之后，你終于調(diào)整了索引，于是系統(tǒng)明顯 nb 了。如果你要看看它變得有多 nb ，可以關(guān)注動(dòng)態(tài)視圖 sys.dm_db_index_usage_stats ，這個(gè)也就不多說(shuō)了。

?

最后，多讀聯(lián)機(jī)文檔，多做嘗試，盡力不用工具而手寫(xiě) SQL 才是硬道理。

=====================

關(guān)于表上是不是都需要一個(gè)聚集索引,各位安達(dá)展開(kāi)了劇烈討論.摘錄部分到這里:
from RicCC:
描述的確不足，是否選擇聚集索引不是這么簡(jiǎn)單
1. heap表的查詢(xún)，除了table scan和covering index之外，都需要bookmark lookup，covering index的使用是有限的，剩下的都是成本很高的操作。除非對(duì)這個(gè)表的查詢(xún)很少。
2. heap的數(shù)據(jù)頁(yè)之間沒(méi)有l(wèi)ink，順序讀取數(shù)據(jù)性能低，I/O開(kāi)銷(xiāo)大。除非每次都用unique index seek。
3. heap每個(gè)insert數(shù)據(jù)都是在末尾，并發(fā)的insert阻塞問(wèn)題比較大。因?yàn)閕nsert位置一次只能有一個(gè)任務(wù)加排它鎖。可以用clustered改善。
4. delete多時(shí)，heap比clustered更浪費(fèi)磁盤(pán)空間，碎片更嚴(yán)重，并且沒(méi)有正常的方式消除heap數(shù)據(jù)頁(yè)的碎片，只能建clustered或者drop table重建。

目前為止我基本沒(méi)有發(fā)現(xiàn)充足的證據(jù)使用heap.
============
index seek跟unique index seek不一樣，例如你要找8.1-8.9號(hào)的log，執(zhí)行計(jì)劃里面只會(huì)看到一個(gè)index seek，它seek的是第一條數(shù)據(jù)，從第一條數(shù)據(jù)到最后一條用的是scan，并且heap肯定要用到rid/index lookup，假如要取的是1.1-8.9，rid/index lookup的成本很可能導(dǎo)致sql server放棄index而使用table scan

綜合考慮，使用heap的范圍實(shí)在是太狹窄，clustered index怎樣建倒很有文章，需要極為認(rèn)真的對(duì)待.
============
index是unique的，index條件都給出來(lái)了并且全部是=，每次seek操作輸出都只有一條記錄，就是unique index seek，oracle是有這個(gè)操作的
如果不是unique index seek，就一定會(huì)有range index scan。sql server heap表的range index scan需要在IAM跟數(shù)據(jù)頁(yè)間切換，效率不好，clustered index就是用于改善這種狀況，并且充分利用磁盤(pán)設(shè)備讀取連續(xù)數(shù)據(jù)的優(yōu)化措施
========================================================================

Me:到目前為止,我找到的最有理由使用堆的地方是一張每天產(chǎn)生kw級(jí)記錄的日志表,這張表上的查詢(xún)主要以查詢(xún)指定Id的用戶(hù)在某段時(shí)間內(nèi)的記錄.

寫(xiě)有效率的SQL查詢(xún)（III）