SQL Server 索引基础知识(2)----聚集索引,非聚集索引

SQL Server 索引基础知识(2)----聚集索引,非聚集索引

作者:郭红俊

时间:January 02, 2008

由于需要给同事培训数据库的索引知识,就收集整理了这个系列的博客。发表在这里,也是对索引知识的一个总结回顾吧。通过总结,我发现自己以前很多很模糊的概念都清晰了很多。

不论是 聚集索引,还是非聚集索引,都是用 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 慢的情况,例子的脚本如下:


  
  
  1. USE CREDIT
  2. go
  3. -- These samples use the Credit database. You can download and restore the
  4. -- credit database from here:
  5. -- http://www.sqlskills.com/resources/conferences/CreditBackup80.zip
  6. -- NOTE: This is a SQL Server 2000 backup and MANY examples will work on
  7. -- SQL Server 2000 in addition to SQL Server 2005.
  8. -------------------------------------------------------------------------------
  9. -- (1) Create two tables which are copies of charge:
  10. -------------------------------------------------------------------------------
  11. -- Create the HEAP
  12. SELECT * INTO ChargeHeap FROM Charge
  13. go
  14. -- Create the CL Table
  15. SELECT * INTO ChargeCL FROM Charge
  16. go
  17. CREATE CLUSTERED INDEX ChargeCL_CLInd ON ChargeCL (member_no, charge_no)
  18. go
  19. -------------------------------------------------------------------------------
  20. -- (2) Add the same non-clustered indexes to BOTH of these tables:
  21. -------------------------------------------------------------------------------
  22. -- Create the NC index on the HEAP
  23. CREATE INDEX ChargeHeap_NCInd ON ChargeHeap (Charge_no)
  24. go
  25. -- Create the NC index on the CL Table
  26. CREATE INDEX ChargeCL_NCInd ON ChargeCL (Charge_no)
  27. go
  28. -------------------------------------------------------------------------------
  29. -- (3) Begin to query these tables and see what kind of access and I/O returns
  30. -------------------------------------------------------------------------------
  31. -- Get ready for a bit of analysis:
  32. SET STATISTICS IO ON
  33. -- Turn Graphical Showplan ON (Ctrl+K)
  34. -- First, a point query (also, see how a bookmark lookup looks in 2005)
  35. SELECT * FROM ChargeHeap WHERE Charge_no = 12345
  36. go
  37. SELECT * FROM ChargeCL WHERE Charge_no = 12345
  38. go
  39. -- What if our query is less selective?
  40. -- 1000 is .0625% of our data... (1,600,000 million rows)
  41. SELECT * FROM ChargeHeap WHERE Charge_no < 1000
  42. go
  43. SELECT * FROM ChargeCL WHERE Charge_no < 1000
  44. go
  45. -- What if our query is less selective?
  46. -- 16000 is 1% of our data... (1,600,000 million rows)
  47. SELECT * FROM ChargeHeap WHERE Charge_no < 16000
  48. go
  49. SELECT * FROM ChargeCL WHERE Charge_no < 16000
  50. go
  51. -------------------------------------------------------------------------------
  52. -- (4) What's the EXACT percentage where the bookmark lookup isn't worth it?
  53. -------------------------------------------------------------------------------
  54. -- What happens here: Table Scan or Bookmark lookup?
  55. SELECT * FROM ChargeHeap WHERE Charge_no < 4000
  56. go
  57. SELECT * FROM ChargeCL WHERE Charge_no < 4000
  58. go
  59. -- What happens here: Table Scan or Bookmark lookup?
  60. SELECT * FROM ChargeHeap WHERE Charge_no < 3000
  61. go
  62. SELECT * FROM ChargeCL WHERE Charge_no < 3000
  63. go
  64. -- And - you can narrow it down by trying the middle ground:
  65. -- What happens here: Table Scan or Bookmark lookup?
  66. SELECT * FROM ChargeHeap WHERE Charge_no < 3500
  67. go
  68. SELECT * FROM ChargeCL WHERE Charge_no < 3500
  69. go
  70. -- And again:
  71. SELECT * FROM ChargeHeap WHERE Charge_no < 3250
  72. go
  73. SELECT * FROM ChargeCL WHERE Charge_no < 3250
  74. go
  75. -- And again:
  76. SELECT * FROM ChargeHeap WHERE Charge_no < 3375
  77. go
  78. SELECT * FROM ChargeCL WHERE Charge_no < 3375
  79. go
  80. -- Don't worry, I won't make you go through it all :)
  81. -- For the Heap Table (in THIS case), the cutoff is: 0.21%
  82. SELECT * FROM ChargeHeap WHERE Charge_no < 3383
  83. go
  84. SELECT * FROM ChargeHeap WHERE Charge_no < 3384
  85. go
  86. -- For the Clustered Table (in THIS case), the cut-off is: 0.21%
  87. SELECT * FROM ChargeCL WHERE Charge_no < 3438
  88. SELECT * FROM ChargeCL WHERE Charge_no < 3439
  89. go

这个例子也就是 吴家震 在 Teched 2007 上的那个演示例子。

小结:

这篇博客只是简单的用几个图表来介绍索引的实现方法: B+数, 聚集索引,非聚集索引,Bookmark Lookup 的信息而已。

参考资料:

表组织和索引组织
http://technet.microsoft.com/zh-cn/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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值