继续：非聚集索引行在存储索引键时确实是排序了的，用事实说话，理论+实践

Question

本帖最后由 x_wy46 于 2013-09-21 08:17:09 编辑

Answer 1

因为走非聚集索引查找的话，需要遍历一个非聚集索引，通过书签查找找到具体的数据，等于多遍历了一遍非聚集索引，依次循环下去，等于是多了N-1遍表扫描（有聚集索引的话就是聚集索引扫描），N是表中数据的条数
而第二句直接表扫描，然后排序，所以比起来第一句IO要小的多哪来的理论,不理解?感觉没有说服力.
当你新建索引的时候索引键已经自做了排序,非聚集索引是链式查找,你上面两条语句用select *,选择的字段一样多,也没有where条件,我认为遍历都是一次,速度可以说相当,毫无差别.速度的快慢与select字段存在关系,与where条件也有关系.遍历次数一般与in,not in,exists,not exists,charindex()等有关,而你上面根本没有这些函数,哪天说说法"多了N-1次遍历"?你得到的结论:第一句IO要小的多,我觉得也没有什么说服力.就遍历一次,IO应该是一样多.

Answer 2

笔误，这里指的是“非聚集索引扫描”
对比的是这两句sql，前提是表上没有聚集索引
select * from TestNoClusteredIndexSort with (index(index_col1));
select * from TestNoClusteredIndexSort order by col1
可能表达方式有问题，，么有把问题说清楚。
关于IO的话，测试一下，结果是很明显的。

Answer 3

对比的是这两句sql，前提是表上没有聚集索引
那就是意味着有非聚集索引，是吧？两句话都走了非聚集索引去找数据，结果都是全部找出数据，但第二句话还要排序，应该是第二句系统开销还要多

Answer 4

这里的前提是表上没有聚集索引，
然后是这样的，比如有100条数据，有一个name列，name列上有非聚集索引，
假如select* from tableName的话，
直接做一个表扫描，然后sort排序假如select* from tableName with (index(index_name));的话
执行步骤是这样的，
做非聚集索引查找，
找到第一个Name索引键，去做一个表扫描，取出来全部数据，
找到第二个Name索引键，去做一个表扫描，取出来全部数据，
……以至于出现我所说的“非聚集索引扫描”这种现象（当然正常情况下是不会出现“非聚集索引扫描”的，这里就是强制查询走非聚集索引的结果）“非聚集索引扫描”扫描的结果就是不断的对表做“表扫描动作”
你看下图，我全部select出来也不过是“逻辑读取 95 ”
而强制走“非聚集索引扫描”的逻辑读达到了上万次

Answer 5

这里的前提是表上没有聚集索引，
然后是这样的，比如有100条数据，有一个name列，name列上有非聚集索引，
假如select* from tableName的话，
直接做一个表扫描，然后sort排序假如select* from tableName with (index(index_name));的话
执行步骤是这样的，
做非聚集索引查找，
找到第一个Name索引键，去做一个表扫描，取出来全部数据，
找到第二个Name索引键，去做一个表扫描，取出来全部数据，
……以至于出现我所说的“非聚集索引扫描”这种现象（当然正常情况下是不会出现“非聚集索引扫描”的，这里就是强制查询走非聚集索引的结果）“非聚集索引扫描”扫描的结果就是不断的对表做“表扫描动作”
你看下图，我全部select出来也不过是“逻辑读取 95 ”
而强制走“非聚集索引扫描”的逻辑读达到了上万次
再强调一下
这里不是为了论证非聚集索引扫描的性能问题什么的，
最终的目的是为了说明，非聚集键在存储时是有序的

Answer 6

慧聚集索引在存储时本来就是有序的，我上一贴不是回给你了吗。。GUID因为有序所以分页存储，不然肯定堆在一页里了啊

Answer 7

非常佩服你的专研精神，得想你学习，更可贵的是那种想到就去做，去实践的精神。我完全赞同你的观点，我认为非聚集索引，绝对是有序的，索引索引，就是有序才叫索引的哈，不管是聚集索引还是非聚集索引，都是有序的，只是聚集索引是把表的数据按照某个字段进行排序，这个索引中不仅是这个字段，而是其他所有字段也存放在里面。而非聚集索引，其实也是一样的，是按照索引字段进行排序，只不过里面没有放其他字段，只放了一个，我们可以称之为书签号，就是用于访问在非聚集索引中没有的字段时，进行书签查找的。另外，你说第一句IO小的多，我也完全赞同，因为当进行表扫描时(不管是聚集索引的表，还是堆表)，
效率要高的多，因为你扫描的数据页都是紧紧的挨着，都是顺序IO，效率很高。而下面的需要进行书签查找，哪为什么书签查找效率更低呢？
其实非聚集索引扫描，本质上到是和表扫描，区别不大，速度挺快的，比如扫描了一个页的数据，里面存储的就是（COL1、书签号），我们把它称为索引记录，当然啦，一个页，可以存储很多的索引记录，但是接下来问题大了，col1的值是排序的，比如，一个页中存放的都是col1为100的索引记录，但是100个索引记录对应了100个书签号，也就对应到表中100条记录，这100条记录可能分布在100个数据页上，于是乎就得访问100个数据页。接下来扫描到了下一个非聚集索引页，假设又是100条索引记录，而这100条记录又分布在100个数据页上，而这100个数据页，在物理上不连续，比如页号是100,200,500,390，……，这就导致SQL serve必须进行大量的随机IO访问，那么效率必然下降。所以这里的本质区别就变成了随机IO和顺序IO之间的差别了，对索引的扫描，不管是聚集还是非聚集，基本上都是顺序的（当然啦，如果是对索引的seek查找，那就是随机IO了），可是书签查找就是随机IO了，而随机IO的效率远低于顺序IO，这才是导致效率差异的本质原因。 晚了，睡了，呵呵