仓酷云

标题: MSSQL网页设计聚簇索引与非聚簇索引的区分和SQL Se... [打印本页]

作者: 山那边是海 时间: 2015-1-16 22:23
标题: MSSQL网页设计聚簇索引与非聚簇索引的区分和SQL Se...
在需要把许多新记录插入某个数据表的场合，DELAY_KEY_WRITE选项的作用将非常明显。另外，索引还会在硬盘上占用相当大的空间。因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。server|区分|索引|优化在《数据库道理》内里，对聚簇索引的注释是:聚簇索引的按次就是数据的物理存储按次，而对非聚簇索引的注释是:索引按次与数据物理分列按次有关。正式由于云云，以是一个表最多只能有一个聚簇索引。
不外这个界说太笼统了。在SQLServer中，索引是经由过程二叉树的数据布局来形貌的，我们能够这么了解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍旧是索引节点，只不外有一个指针指向对应的数据块。以下图：

非聚簇索引

聚簇索引

聚簇索引与非聚簇索引的实质区分究竟是甚么？甚么时分用聚簇索引，甚么时分用非聚簇索引？
这是一个很庞大的成绩，很难用一言半语说分明。我在这里从SQLServer索引优化查询的角度复杂谈谈(假如对这方面感乐趣的话，能够读一读微软出书的《MicrosoftSQLServer2000数据库编程》第3单位的数据布局引论和第6、13、14单位)。

1、索引块与数据块的区分
人人都晓得，索引能够进步检索效力，由于它的二叉树布局和占用空间小，以是会见速率块。让我们来算一道数学题：假如表中的一笔记录在磁盘上占用1000字节的话，我们对个中10字节的一个字段创建索引，那末该纪录对应的索引块的巨细只要10字节。我们晓得，SQLServer的最小空间分派单位是“页（Page）”，一个页在磁盘上占用8K空间，那末这一个页能够存储上述纪录8条，但能够存储索引800条。如今我们要从一个有8000笔记录的表中检索切合某个前提的纪录，假如没有索引的话，我们大概必要遍历8000条×1000字节/8K字节=1000个页面才干够找到了局。假如在检索字段上有上述索引的话，那末我们能够在8000条×10字节/8K字节=10个页面中就检索到满意前提的索引块，然后依据索引块上的指针一一找到了局数据块，如许IO会见量要少的多。

2、索引优化手艺
是否是有索引就必定检索的快呢？谜底是不是。有些时分用索引还不如不必索引快。好比说我们要检索上述表中的一切纪录，假如不必索引，必要会见8000条×1000字节/8K字节=1000个页面，假如利用索引的话，起首检索索引，会见8000条×10字节/8K字节=10个页面失掉索引检索了局，再依据索引检索了局往对应数据页面，因为是检索一切数据，以是必要再会见8000条×1000字节/8K字节=1000个页面将全体数据读掏出来，一共会见了1010个页面，这明显不如不必索引快。
SQLServer外部有一套完全的数据检索优化手艺，在上述情形下，SQLServer的查询企图（SearchPlan）会主动利用表扫描的体例检索数据而不会利用任何索引。那末SQLServer是怎样晓得甚么时分用索引，甚么时分不必索引的呢？SQLServer除一样平常保护数据信息外，还保护着数据统计信息，下图是数据库属性页面的一个截图：

从图中我们能够看到，SQLServer主动保护统计信息，这些统计信息包含数据密度信息和数据散布信息，这些信息匡助SQLServer决意怎样制订查询企图和查询是是不是利用索引和利用甚么样的索引（这里就不再注释它们究竟怎样匡助SQLServer创建查询企图的了）。我们仍是来做个实行。创建一张表：tabTest(ID,unqValue，intValue)，个中ID是整形主动编号主索引，unqValue是uniqueidentifier范例，在下面创建一般索引，intValue是整形，不创建索引。之以是挂上一个没有索引的intValue字段，就是避免SQLServer利用索引掩盖查询优化手艺，如许实行就起不到感化了。向表中录进10000条随机纪录，代码以下：
CREATETABLE[dbo].[tabTest](
[ID][int]IDENTITY(1,1)NOTNULL,
[unqValue][uniqueidentifier]NOTNULL,
[intValue][int]NOTNULL
)ON[PRIMARY]
GO

ALTERTABLE[dbo].[tabTest]WITHNOCHECKADD
CONSTRAINT[PK_tabTest]PRIMARYKEYCLUSTERED
(
[ID]
)ON[PRIMARY]
GO

ALTERTABLE[dbo].[tabTest]ADD
CONSTRAINT[DF_tabTest_unqValue]DEFAULT(newid())FOR[unqValue]
GO

CREATEINDEX[IX_tabTest_unqValue]ON[dbo].[tabTest]([unqValue])ON[PRIMARY]
GO

declare@iint
declare@vint

set@i=0
while@i<10000
begin
set@v=rand()*1000
insertintotabTest([intValue])values(@v)
set@i=@i+1
end

然后我们实行两个查询并检察实行企图，如图：（在查询剖析器的查询菜单中能够翻开查询企图，同时图上第一个查询的GUID是我从数据库中找的，人人做实行的时分能够依据本人数据库中的值来定）：

从图中能够看出，在第一个查询中，SQLServer利用了IX_tabTest_unqValue索引，依据箭头偏向，盘算机先在索引局限内找，找到后，利用BookmarkLookup将索引节点映照到数据节点上，最初给出SELECT了局。在第二个查询中，体系间接遍历表给出了局，不外它利用了聚簇索引，为何呢？不要忘了，聚簇索引的页节点就是数据节点！如许利用聚簇索引会更快一些（不受数据删除、更新留下的存储朴陋的影响，间接遍历数据是要跳过这些朴陋的）。
上面，我们在SQLServer中将ID字段的聚簇索引变动为非聚簇索引，然后再实行select*fromtabTest，这回我们看到的实行企图酿成了：

SQLServer没有利用任何索引，而是间接实行了TableScan，由于只要如许，检索效力才是最高的。

3、聚簇索引与非聚簇索引的实质区分
如今能够会商聚簇索引与非聚簇索引的实质区分了。正如本文最后面的两个图所示，聚簇索引的叶节点就是数据节点，而非聚簇索引的页节点仍旧是索引检束，并保存一个链接指向对应数据块。
仍是经由过程一道数学题来看看它们的区分吧：假定有一8000笔记录的表，表中每笔记录在磁盘上占用1000字节，假如在一个10字节长的字段上创建非聚簇索引主键，必要二叉树节点16000个（这16000个节点中有8000个叶节点，每一个页节点都指向一个数据纪录），如许数据将占用8000条×1000字节/8K字节=1000个页面；索引将占用16000个节点×10字节/8K字节=20个页面，合计1020个页面。
一样一张表，假如我们在对应字段上创建聚簇索引主键，因为聚簇索引的页节点就是数据节点，以是索引节点唯一8000个，占用10个页面，数据仍旧占据1000个页面。
上面我们看看在实行拔出操纵时，非聚簇索引的主键为何比聚簇索引主键要快。主键束缚请求主键不克不及呈现反复，那末SQLServer是怎样晓得不呈现反复的呢？独一的办法就是检索。关于非聚簇索引，只必要检索20个页面中的16000个节点就晓得是不是有反复，由于一切主键键值在这16000个索引节点中都包括了。但关于聚簇索引，索引节点仅仅包括了8000其中间节点，至于会不会呈现反复必需检索别的1000个页数据节点才晓得，那末相称于检索10+1000=1010个页面才晓得是不是有反复。以是聚簇索引主键的拔出速率要比非聚簇索引主键的拔出速率慢良多。
让我们再来看看数据检索的效力，假如对上述两表举行检索，在利用索引的情形下（有些时分SQLServer实行企图会选择不利用索引，不外我们这里临时假定必定利用索引），关于聚簇索引检索，我们大概会会见10个索引页面外加1000个数据页面失掉了局（实践情形要比这个好），而关于非聚簇索引，体系会从20个页面中找到切合前提的节点，再映照到1000个数据页面上（这也是最糟的情形），对照一下，一个会见了1010个页面而另外一个会见了1020个页面，可见检索效力差别并非很年夜。以是不论非聚簇索引也好仍是聚簇索引也好，都合适排序，聚簇索引仅仅比非聚簇索引快一点。

结语
好了，写了半天，手都累了。关于聚簇索引与非聚簇索引效力成绩的实行就不做了，感乐趣的话能够本人利用查询剖析器对查询企图举行剖析。SQLServer是一个很庞大的体系，特别是索引和查询优化手艺，Oracle就更庞大了。懂得索引和查询面前的事变不是甚么好事，它能够匡助我们更加深入的懂得我们的体系。
支持大型的数据库。可以处理拥有上千万条记录的大型数据库。

作者: 若天明 时间: 2015-1-19 10:09
很多书籍啊，不过个人认为看书太慢，还不如自己学。多做实际的东西，就会遇到很多问题，网上搜下解决问题。不断重复这个过程，在配合sql的F1功能。

作者: 乐观 时间: 2015-1-28 06:06
可能有的朋友会抱怨集成的orderby，其实如果使用ranking函数,Orderby是少不了的。如果担心Orderby会影响效率，可以为orderby的字段建立聚集索引，查询计划会忽略orderby操作（因为本来就是排序的嘛）。

作者: 山那边是海 时间: 2015-2-5 18:57
现在是在考虑:如果写到服务器端,我一下搞他个10个存储过程导过去,那久之服务器不就成垃圾箱了吗?即便优化了我的中间层.

作者: 灵魂腐蚀 时间: 2015-2-13 06:51
大侠们有推荐的书籍和学习方法写下吧。

作者: 愤怒的大鸟 时间: 2015-3-3 18:45
如果处理少量数据,比如几百条记录的数据,我不知道这两种情况哪个效率更高,如果处理大量数据呢?比如有表中有20万条记录.

作者: 飘飘悠悠 时间: 2015-3-11 12:44
还不是性能有问题！否则面向对象的数据库早就实现了！建议使用CLR的地方一般是和应用的复杂程度或操作系统环境有很高的耦合度的场景。如你想构建复杂的算法，并且用到了大量的指针和高级数据模型。

作者: 爱飞 时间: 2015-3-18 18:53
其中最有名的应该是row_number了。这个终于解决了用临时表生成序列号的历史，而且SQLServer2005的row_number比Oracle的更先进。因为它把Orderby集成到了一起，不用像Oracle那样还要用子查询进行封装。

作者: 深爱那片海 时间: 2015-3-26 14:21
不好!如果出了错;不好调试;不好处理!其实web开发将代码分为3层:web层;业务逻辑层和数据访问层;一般对数据库的操作都在数据访问层来做;这样便于调试和维护!而且将来如果是换了数据库的话;你只需要改数据层的代码;其他层的基本可以不变!要是你在jsp中直接调用sql数据库;那么如果换了数据库呢?岂不都要改?如果报了异常呢?怎么做异常处理?

欢迎光临仓酷云 (http://ckuyun.com/)