SQL Server 索引基礎知識(2)----聚集索引,非聚集索引_Mssql數據庫教程
推薦:SQL Server 索引基礎知識(4)----主鍵與聚集索引有些人可能對主鍵和聚集索引有所混淆,其實這兩個是不同的概念,下面是一個簡單的描述。不想看繞口文字者,直接看兩者的對比表。尤其是最后一項的比較。 主鍵(PRIMARY KEY ) 來自MSDN的描述: 表通常具有包含唯一標識表中每一行的值的一列或一組列。這樣的
由于需要給同事培訓數據庫的索引知識,就收集整理了這個系列的博客。發表在這里,也是對索引知識的一個總結回顧吧。通過總結,我發現自己以前很多很模糊的概念都清晰了很多。
不論是 聚集索引,還是非聚集索引,都是用B+樹來實現的。我們在了解這兩種索引之前,需要先了解B+樹。如果你對B樹不了解的話,建議參看以下幾篇文章:
BTree,B-Tree,B+Tree,B*Tree都是什么
http://blog.csdn.net/manesking/archive/2007/02/09/1505979.aspx
B+ 樹的結構圖:
B+ 樹的特點:
- 所有關鍵字都出現在葉子結點的鏈表中(稠密索引),且鏈表中的關鍵字恰好是有序的;
- 不可能在非葉子結點命中;
- 非葉子結點相當于是葉子結點的索引(稀疏索引),葉子結點相當于是存儲(關鍵字)數據的數據層;
B+ 樹中增加一個數據,或者刪除一個數據,需要分多種情況處理,比較復雜,這里就不詳述這個內容了。
聚集索引(Clustered Index)
- 聚集索引的葉節點就是實際的數據頁
- 在數據頁中數據按照索引順序存儲
- 行的物理位置和行在索引中的位置是相同的
- 每個表只能有一個聚集索引
- 聚集索引的平均大小大約為表大小的5%左右
下面是兩副簡單描述聚集索引的示意圖:
在聚集索引中執行下面語句的的過程:
select * from table where firstName = 'Ota'
一個比較抽象點的聚集索引圖示:
非聚集索引 (Unclustered Index)
- 非聚集索引的頁,不是數據,而是指向數據頁的頁。
- 若未指定索引類型,則默認為非聚集索引
- 葉節點頁的次序和表的物理存儲次序不同
- 每個表最多可以有249個非聚集索引
- 在非聚集索引創建之前創建聚集索引(否則會引發索引重建)
在非聚集索引中執行下面語句的的過程:
select * from employee where lname = 'Green'
一個比較抽象點的非聚集索引圖示:
什么是 Bookmark Lookup
雖然SQL 2005 中已經不在提 Bookmark Lookup 了(換湯不換藥),但是我們的很多搜索都是用的這樣的搜索過程,如下:
先在非聚集中找,然后再在聚集索引中找。
在 http://www.sqlskills.com/ 提供的一個例子中,就給我們演示了 Bookmark Lookup 比 Table Scan 慢的情況,例子的腳本如下:
USE CREDIT go -- These samples use the Credit database. You can download and restore the -- credit database from here: -- http://www.sqlskills.com/resources/conferences/CreditBackup80.zip -- NOTE: This is a SQL Server 2000 backup and MANY examples will work on -- SQL Server 2000 in addition to SQL Server 2005. ------------------------------------------------------------------------------- -- (1) Create two tables which are copies of charge: ------------------------------------------------------------------------------- -- Create the HEAP SELECT * INTO ChargeHeap FROM Charge go -- Create the CL Table SELECT * INTO ChargeCL FROM Charge go CREATE CLUSTERED INDEX ChargeCL_CLInd ON ChargeCL (member_no, charge_no) go ------------------------------------------------------------------------------- -- (2) Add the same non-clustered indexes to BOTH of these tables: ------------------------------------------------------------------------------- -- Create the NC index on the HEAP CREATE INDEX ChargeHeap_NCInd ON ChargeHeap (Charge_no) go -- Create the NC index on the CL Table CREATE INDEX ChargeCL_NCInd ON ChargeCL (Charge_no) go ------------------------------------------------------------------------------- -- (3) Begin to query these tables and see what kind of access and I/O returns ------------------------------------------------------------------------------- -- Get ready for a bit of analysis: SET STATISTICS IO ON -- Turn Graphical Showplan ON (Ctrl+K) -- First, a point query (also, see how a bookmark lookup looks in 2005) SELECT * FROM ChargeHeap WHERE Charge_no = 12345 go SELECT * FROM ChargeCL WHERE Charge_no = 12345 go -- What if our query is less selective? -- 1000 is .0625% of our data... (1,600,000 million rows) SELECT * FROM ChargeHeap WHERE Charge_no < 1000 go SELECT * FROM ChargeCL WHERE Charge_no < 1000 go -- What if our query is less selective? -- 16000 is 1% of our data... (1,600,000 million rows) SELECT * FROM ChargeHeap WHERE Charge_no < 16000 go SELECT * FROM ChargeCL WHERE Charge_no < 16000 go ------------------------------------------------------------------------------- -- (4) What's the EXACT percentage where the bookmark lookup isn't worth it? ------------------------------------------------------------------------------- -- What happens here: Table Scan or Bookmark lookup? SELECT * FROM ChargeHeap WHERE Charge_no < 4000 go SELECT * FROM ChargeCL WHERE Charge_no < 4000 go -- What happens here: Table Scan or Bookmark lookup? SELECT * FROM ChargeHeap WHERE Charge_no < 3000 go SELECT * FROM ChargeCL WHERE Charge_no < 3000 go -- And - you can narrow it down by trying the middle ground: -- What happens here: Table Scan or Bookmark lookup? SELECT * FROM ChargeHeap WHERE Charge_no < 3500 go SELECT * FROM ChargeCL WHERE Charge_no < 3500 go -- And again: SELECT * FROM ChargeHeap WHERE Charge_no < 3250 go SELECT * FROM ChargeCL WHERE Charge_no < 3250 go -- And again: SELECT * FROM ChargeHeap WHERE Charge_no < 3375 go SELECT * FROM ChargeCL WHERE Charge_no < 3375 go -- Don't worry, I won't make you go through it all :) -- For the Heap Table (in THIS case), the cutoff is: 0.21% SELECT * FROM ChargeHeap WHERE Charge_no < 3383 go SELECT * FROM ChargeHeap WHERE Charge_no < 3384 go -- For the Clustered Table (in THIS case), the cut-off is: 0.21% SELECT * FROM ChargeCL WHERE Charge_no < 3438 SELECT * FROM ChargeCL WHERE Charge_no < 3439 go
這個例子也就是 吳家震 在Teched 2007 上的那個演示例子。
小結:
這篇博客只是簡單的用幾個圖表來介紹索引的實現方法:B+數, 聚集索引,非聚集索引,Bookmark Lookup 的信息而已。
參考資料:
表組織和索引組織
http://technet.microsoft.com/zh-cn/library/ms189051.aspx
http://technet.microsoft.com/en-us/library/ms189051.aspx
How Indexes Work
http://manuals.sybase.com/onlinebooks/group-asarc/asg1200e/aseperf/@Generic__BookTextView/3358
Bookmark Lookup
http://blogs.msdn.com/craigfr/archive/2006/06/30/652639.aspx
Logical and Physical Operators Reference
http://msdn2.microsoft.com/en-us/library/ms191158.aspx
分享:SQL Server 2000 中使用正則表達式這兩天有個需求,需要在數據庫中判斷字符串的格式,于是從網上搜集了一些資料,整理了一下。 下面這個是一個自定義函數,用戶可以調用這個函數判斷指定的字符串是否符合正則表達式的規則. CREATE FUNCTION dbo.find_regular_expression ( @source varchar(50
- sql 語句練習與答案
- 深入C++ string.find()函數的用法總結
- SQL Server中刪除重復數據的幾個方法
- sql刪除重復數據的詳細方法
- SQL SERVER 2000安裝教程圖文詳解
- 使用sql server management studio 2008 無法查看數據庫,提示 無法為該請求檢索數據 錯誤916解決方法
- SQLServer日志清空語句(sql2000,sql2005,sql2008)
- Sql Server 2008完全卸載方法(其他版本類似)
- sql server 2008 不允許保存更改,您所做的更改要求刪除并重新創建以下表
- SQL Server 2008 清空刪除日志文件(瞬間日志變幾M)
- Win7系統安裝MySQL5.5.21圖解教程
- 將DataTable作為存儲過程參數的用法實例詳解
- 相關鏈接:
- 教程說明:
Mssql數據庫教程-SQL Server 索引基礎知識(2)----聚集索引,非聚集索引。