索引設(shè)計(jì)的建議：

一.檢查where子句和連接條件列

當(dāng)一個查詢提交到sql server時，查詢優(yōu)化器嘗試為查詢中引用的所有表查找最佳的數(shù)據(jù)訪問機(jī)制，

一下是它所進(jìn)行的方式。

1.優(yōu)化器識別Where子句和連接條件中包含的列、

2.接著優(yōu)化器檢查這些列的索引.

3.優(yōu)化器通過從索引上維護(hù)的統(tǒng)計(jì)確定子句的選擇性(也就是返回多少行),評估每個索引的有效性

4.最終,優(yōu)化器根據(jù)前面幾個步驟中收集的信息,評估讀取所限定的行開銷最低的方法.

下面看個例子:? Table_1表內(nèi) 又 30000條數(shù)據(jù)

執(zhí)行查詢:

查看結(jié)果:

邏輯讀取的次數(shù)為 95次.

下面加上where? 條件句, where 的列 是 含有聚集索引的.

結(jié)果:

很明顯, 邏輯讀 次數(shù)減少了兩次.

下面看 where 條件句,where 的列 不含有索引的:

查詢的結(jié)果:

總結(jié):

where 子句列 幫助 優(yōu)化器選擇 一個對 查詢最優(yōu)的索引操作. 這也使用于 兩個表之間的連接條件中使用的列.

優(yōu)化器 查找在 where子句 或連接條件列上的索引,如果可用,考慮使用該索引 來從表中檢索行,查詢優(yōu)化器在執(zhí)行一個查詢時,

考慮where子句或連接條件列上的索引. 因此,在where子句或連接條件中 頻繁使用的列上有 索引將 幫助查詢優(yōu)化器避免基本表的掃描.

注意:? 當(dāng)一個表中的數(shù)據(jù)總量非常小以至于可以 放入一個單獨(dú)的頁面(8kb)時,表掃描可能比索引查找工作 得更好,

二: 使用 窄索引

應(yīng)該避免在索引中使用 寬數(shù)據(jù)類型 的列. 比如 字符串類型(char,varchar,nchar ,nvarchar)的列有時候可能和二進(jìn)制類型一樣大.

窄索引可以再8kb的索引頁面中 容納比 寬索引 更多的行,這將有一下效果:

1.減少 i/o數(shù)量 (讀取更少的8kb頁面)

2.使數(shù)據(jù)庫緩存更有效,因?yàn)?sql server 可以緩存更少的索引頁面,從而減少內(nèi)存中的索引頁面所需的邏輯讀操作.

3.減少數(shù)據(jù)庫的存儲空間.

下面看個例子:

表a

id為主鍵, 所以 a有個聚集索引, 那么id列的數(shù)據(jù)類型是 int,a表有數(shù)據(jù)27行數(shù)據(jù),

說明所有的 索引 總大小為 4*27 byte, 一個索引頁面(8kb)完全可以容得下.

sys.indexes系統(tǒng)表 在每個數(shù)據(jù)庫中保存,包含了數(shù)據(jù)庫中所有索引的基本信息.

DMF? sys.dm_db_index_physical_stats 包含了關(guān)于索引上統(tǒng)計(jì)的更詳細(xì)信息.

下面看sql語句:?

select i.name,i.type_desc,s.page_count,s.record_count,s.index_level from sys.indexes ijoin sys.dm_db_index_physical_stats(DB_ID(N'test'),OBJECT_ID(N'dbo.a'),null,null,'DETAILED') as son i.index_id=s.index_id where i.OBJECT_ID=OBJECT_ID(N'dbo.a') 獲取如下 結(jié)果: .csharpcode, .csharpcode pre { font-size: small; color: rgba(0, 0, 0, 1); font-family: consolas, "Courier New", courier, monospace; background-color: rgba(255, 255, 255, 1) } .csharpcode pre { margin: 0 } .csharpcode .rem { color: rgba(0, 128, 0, 1) } .csharpcode .kwrd { color: rgba(0, 0, 255, 1) } .csharpcode .str { color: rgba(0, 96, 128, 1) } .csharpcode .op { color: rgba(0, 0, 192, 1) } .csharpcode .preproc { color: rgba(204, 102, 51, 1) } .csharpcode .asp { background-color: rgba(255, 255, 0, 1) } .csharpcode .html { color: rgba(128, 0, 0, 1) } .csharpcode .attr { color: rgba(255, 0, 0, 1) } .csharpcode .alt { background-color: rgba(244, 244, 244, 1); width: 100%; margin: 0 } .csharpcode .lnum { color: rgba(96, 96, 96, 1) }

說明 索引頁數(shù) 是1頁, index_leval =0說明是 聚集索引.

下面使用寬索引 的一個例子:

b表結(jié)果如下:? name列有一個? 非聚集索引,索引名字為: IX_b? 由于name 的數(shù)據(jù)類型為 char(500),b內(nèi)數(shù)據(jù)有23行, 說明非聚集索引占用的大小事 23*500byte,那么超出了一個頁面的大小,

按照預(yù)測,name的索引將存在兩個索引頁面, 下面用sql語句來證實(shí)這個結(jié)論.

sql語句:

select i.name,i.type_desc,s.page_count,s.record_count,s.index_level from sys.indexes ijoin sys.dm_db_index_physical_stats(DB_ID(N'test'),OBJECT_ID(N'dbo.b'),null,null,'DETAILED') as son i.index_id=s.index_id where i.OBJECT_ID=OBJECT_ID(N'dbo.b') .csharpcode, .csharpcode pre { font-size: small; color: rgba(0, 0, 0, 1); font-family: consolas, "Courier New", courier, monospace; background-color: rgba(255, 255, 255, 1) } .csharpcode pre { margin: 0 } .csharpcode .rem { color: rgba(0, 128, 0, 1) } .csharpcode .kwrd { color: rgba(0, 0, 255, 1) } .csharpcode .str { color: rgba(0, 96, 128, 1) } .csharpcode .op { color: rgba(0, 0, 192, 1) } .csharpcode .preproc { color: rgba(204, 102, 51, 1) } .csharpcode .asp { background-color: rgba(255, 255, 0, 1) } .csharpcode .html { color: rgba(128, 0, 0, 1) } .csharpcode .attr { color: rgba(255, 0, 0, 1) } .csharpcode .alt { background-color: rgba(244, 244, 244, 1); width: 100%; margin: 0 } .csharpcode .lnum { color: rgba(96, 96, 96, 1) }

查詢結(jié)果如下:

果然,看到 IX_b record_count =23的 行, 它的 page_count是2 ,說明我們之前的猜想是正確的.

這就是寬索引的劣勢.

三:檢查列的唯一性.

在一個具有很小范圍的可能值的列(如性別,只有,f和m)上創(chuàng)建索引,對性能是沒有很大幫助的.

因?yàn)椴樵儍?yōu)化器不能使用索引有效的減少返回的行.?

考慮只有兩個唯一值 的 性別列(F和M).當(dāng)執(zhí)行一個 具有使用 性別列的where子句查詢時,最終從表中得打很大數(shù)量的行,導(dǎo)致開銷很大的表,或聚集索引掃描.

結(jié)論:

所以 使用where子句中的列 具有 大量的唯一行,一限制訪問的行數(shù),是個首選的方案. 應(yīng)該在 這些列上 創(chuàng)建索引,來幫助 查詢優(yōu)化器

訪問小的結(jié)果集.

而且,在創(chuàng)建多個列上的索引時(符合索引),列的順序是有關(guān)系的.在某些情況下,先使用最有選擇性的列將使索引行的列更有效率.

可以使用下面的語句 來判斷 列的選擇性.

test1 自己隨意定義的表select count(distinct EventClass) as 不同的值,count(EventClass) as 多少行,cast(count(distinct EventClass) as decimal) as 選擇性 from test1

結(jié)果如下:

具有最高的唯一值數(shù)量(或選擇性)的列 是 where子句 或連接條件 中引用的索引的 最佳候選.

四:考慮索引類型

`sql server主要有兩種索引，聚集索引和非聚集索引，這兩種類型的索引 都為 B-樹 結(jié)構(gòu)。兩者之間的主要區(qū)別 是

聚集索引的葉子頁面 是表的數(shù)據(jù)頁面，因此數(shù)據(jù)和其指針的順序相同，這意味著 聚集索引就是該表。

下一篇 ，主要講述這兩種索引。

總結(jié)

以上是生活随笔為你收集整理的sql server 2008学习4 设计索引的建议的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。