关于主键和索引对记录的排序问题

jlqin717 2010-04-22 09:54:03

在sql server2k中，创建一个department表，有两个字段：系号，char,3,不空；系名，char,6,不空；并设置系号为主键。在其中输入如下记录：001，数学；002，计算机；003，化学。此时若改变主键的排序规则，即为升序或降序，能够按照系号正常排序。但再对系名建立UNIQUE非聚集索引以后，则记录的排序规则将按照系名进行，而不是按照主键的定义规则进行排序。因为主键系号是一个聚集索引，而结果显示的是按照非聚集索引系名进行排序，不知这是什么原因！！！
请帮忙！！！

...全文

264 23 打赏收藏转发到动态举报

写回复

用AI写文章

23 条回复

切换为时间正序

请发表友善的回复…

发表回复

libinlink 2010-04-26

打赏
举报

还是order by 吧。
楼主已经很了解聚集索引和非聚集索引啦。

有些东西微乳不对外公开，所以原因不是很容易搞懂。

obuntu 2010-04-26

打赏
举报

[Quote=引用 20 楼 jlqin717 的回复:]

感谢大家的关注，依然期待更好的解释！！！
[/Quote]

http://blog.csdn.net/obuntu/archive/2010/04/22/5515800.aspx

做的一个探索。可能不是问题答案，可以参考下。

jlqin717 2010-04-26

打赏
举报

感谢大家的关注，依然期待更好的解释！！！

999朵玫瑰 2010-04-26

打赏
举报

学习了

obuntu 2010-04-22

打赏
举报

[Quote=引用 16 楼 xys_777 的回复:]

我也试了，两列时时出现这样问题，三列就没问题了，也是很迷惑
[/Quote]

google了半天。发现很多是这样解释的。
当有聚集索引和非聚集索引同时存在时，因为非聚集索引所占用的page比较少，sqlserver会认为将使用更少的I/O，所以就自动选择了非聚集索引。

如果有三列时，通过非聚集索引找到另外列的代价比较大，所以就选择了聚集索引。

如果你把另外一列也添加到非聚集索引，你仍会发现，它选择的是非聚集索引，而不是聚集索引了。

misterliwei 2010-04-22

打赏
举报

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/05/20/without-order-by-there-is-no-default-sort-order.aspx

永生天地 2010-04-22

打赏
举报

我也试了，两列时时出现这样问题，三列就没问题了，也是很迷惑

obuntu 2010-04-22

打赏
举报

搞不懂，sql server为什么把扫描unique的索引放在优先的位置。。

有bol说明没呢？

htl258_Tony 2010-04-22

打赏
举报

[Quote=引用 13 楼 garnett_kg 的回复:]
引用楼主 jlqin717 的回复:
在sql server2k中，创建一个department表，有两个字段：系号，char,3,不空；系名，char,6,不空；并设置系号为主键。在其中输入如下记录：001，数学；002，计算机；003，化学。此时若改变主键的排序规则，即为升序或降序，能够按照系号正常排序。但再对系名建立UNIQUE非聚集索引以后，则记录的排序规则将按照系名进行，而不是按照主键……
[/Quote]

--测试数据

IF NOT OBJECT_ID('[tb]') IS NULL

    DROP TABLE [tb]

GO

CREATE TABLE tb(a int primary key,b int,c int)

INSERT tb SELECT 2,1,2

INSERT tb SELECT 1,2,3

INSERT tb SELECT 3,3,4



SELECT * FROM TB 

/*

a           b           c

----------- ----------- -----------

1           2           3

2           1           2

3           3           4



(3 行受影响)

*/

CREATE INDEX IDX_tb_b ON tb(b)

SELECT * FROM TB 

/*

a           b           c

----------- ----------- -----------

1           2           3

2           1           2

3           3           4



(3 行受影响)

*/

真是这样，只有两个字段，引擎更喜欢扫描聚集索引，有三个以上字段，从计划看，引擎先扫描的是聚集索引。

Garnett_KG 2010-04-22

打赏
举报

[Quote=引用楼主 jlqin717 的回复:]
在sql server2k中，创建一个department表，有两个字段：系号，char,3,不空；系名，char,6,不空；并设置系号为主键。在其中输入如下记录：001，数学；002，计算机；003，化学。此时若改变主键的排序规则，即为升序或降序，能够按照系号正常排序。但再对系名建立UNIQUE非聚集索引以后，则记录的排序规则将按照系名进行，而不是按照主键的定义规则进行排序。因为主键系号是一个聚……
[/Quote]

有可能的,引擎更喜欢扫描Uniqueu的索引。你的表恰好只有2个字段，所以便选择系名咯。

你要是加多一个字段，结果便是你想的那样按系号扫描了。
SELECT * FROM department

htl258_Tony 2010-04-22

打赏
举报

看执行计划也是先扫描非聚集索引

htl258_Tony 2010-04-22

打赏
举报

--测试数据

IF NOT OBJECT_ID('[tb]') IS NULL

	DROP TABLE [tb]

GO

CREATE TABLE tb(a int primary key,b int)

INSERT tb SELECT 2,1

INSERT tb SELECT 1,2

INSERT tb SELECT 3,3



SELECT * FROM TB 

/*

a           b

----------- -----------

1           2

2           1

3           3



(3 行受影响)

*/

CREATE INDEX IDX_tb_b ON tb(b)

SELECT * FROM TB 

/*

a           b

----------- -----------

2           1

1           2

3           3



(3 行受影响)

*/

还真是这种情况

永生天地 2010-04-22

打赏
举报

表和索引是分别存储的

obuntu 2010-04-22

打赏
举报

[Quote=引用 7 楼 htl258 的回复:]

引用楼主 jlqin717 的回复:
在sql server2k中，创建一个department表，有两个字段：系号，char,3,不空；系名，char,6,不空；并设置系号为主键。在其中输入如下记录：001，数学；002，计算机；003，化学。此时若改变主键的排序规则，即为升序或降序，能够按照系号正常排序。但再对系名建立UNIQUE非聚集索引以后，则记录的排序规则将按照系名进行，而不是按照主……
[/Quote]

我做了下实验，并不是这样的。

情况确实是像楼主说的，不会选择聚集索引，而是选择了非聚集索引。。

期待高手解答下。

--小F-- 2010-04-22

打赏
举报

[Quote=引用楼主 jlqin717 的回复:]
在sql server2k中，创建一个department表，有两个字段：系号，char,3,不空；系名，char,6,不空；并设置系号为主键。在其中输入如下记录：001，数学；002，计算机；003，化学。此时若改变主键的排序规则，即为升序或降序，能够按照系号正常排序。但再对系名建立UNIQUE非聚集索引以后，则记录的排序规则将按照系名进行，而不是按照主键的定义规则进行排序。因为主键系号是一个聚……
[/Quote]

设计主键了不是直接就有聚集索引了吗? 奇怪

htl258_Tony 2010-04-22

打赏
举报

[Quote=引用楼主 jlqin717 的回复:]
在sql server2k中，创建一个department表，有两个字段：系号，char,3,不空；系名，char,6,不空；并设置系号为主键。在其中输入如下记录：001，数学；002，计算机；003，化学。此时若改变主键的排序规则，即为升序或降序，能够按照系号正常排序。但再对系名建立UNIQUE非聚集索引以后，则记录的排序规则将按照系名进行，而不是按照主键的定义规则进行排序。因为主键系号是一个聚……
[/Quote]
系号设置了主键，系统会自动为其加上聚集索引，系名再加个普通索引，如果查询时没有进行任何排序，默认还是按主键顺序排的。

水族杰纶 2010-04-22

打赏
举报

-- 索引影响查询结果顺序

--测试数据

CREATE TABLE tb(a int,b int,c int)

CREATE CLUSTERED INDEX IDX_tb_a ON tb(a)

CREATE INDEX IDX_tb_b ON tb(b)

CREATE INDEX IDX_tb_c ON tb(c)

INSERT tb SELECT 1,3,2

INSERT tb SELECT 2,2,1

INSERT tb SELECT 3,1,3



--指定使用字段a上的索引

SELECT * FROM tb WITH(INDEX=IDX_tb_a)

/*--结果

a           b            c

---------------- ------------------ ----------- 

1           3           2

2           2           1

3           1           3

--*/



--指定使用字段b上的索引

SELECT * FROM tb WITH(INDEX=IDX_tb_b)

/*--结果

a           b           c 

---------------- ------------------ ----------- 

3           1           3

2           2           1

1           3           2

--*/



--指定使用字段c上的索引

SELECT * FROM tb WITH(INDEX=IDX_tb_c)

/*--结果

a           b           c

---------------- ------------------ ----------- 

2           2           1

1           3           2

3           1           3

--*/