mssql聚集索引问题请教

stephenchern 2014-03-04 10:14:15
mssql聚集索引问题
例如
原来表结构
t_demo
demo1,demo2字段，原来按照demo1聚集排列
后面我把demo1聚集排列删掉 换demo2聚集排列
结构打开表看 实际数据物理排列 没有改变
是我哪里设置错了？ 还是其他什么问题
设置方法是 打开表设计 找到键值 将里面的聚集勾选去掉
新建为demo2的聚集
mssql版本是2008r2
还请教大家 谢谢。。。。
...全文
256 22 打赏 收藏 转发到动态 举报
写回复
用AI写文章
22 条回复
切换为时间正序
请发表友善的回复…
发表回复
lg314 2014-03-19
  • 打赏
  • 举报
 回复
引用 10 楼 stephenchern 的回复:
纠结的问题 截图给大家看看 sp_help查出来的信息： 数据查询出来物理排序：
聚集索引 (CompanyID, ShopID, CardID) 非聚集索引主键 (CardID) 其实这时候可以想想，聚集的键的是已经包含所有数据了, 所以说明非聚集索引叶级数据里面已经包含所有的数据了。这时候扫描聚集索引和非聚集索引都无所谓了，这时候数据库让主键权重高一些也很合理的。当然这时候你给这个数据表再添加一个column不在聚集索引的键值里面的话就又不一样了。
stephenchern 2014-03-19
  • 打赏
  • 举报
 回复
总算知道为什么了 感谢楼上二位 的确 再加一列 就显示出我要的结果了 很好。。。。
专注or全面 2014-03-18
  • 打赏
  • 举报
 回复
终于弄明白了，9点跑完步回来，还木洗澡
专注or全面 2014-03-18
  • 打赏
  • 举报
 回复
终于弄懂了 这个事执行计划引起的 虽然是 select * from t_card，这里不去分析这个那个索引了 数据库查询引擎默认有一种最高效的查询方式（或者是表扫描，或者是索引扫描，或者是聚集索引扫描） 我这里不去分析具体哪种查询方式效率高 你就set statistics profile on 去查看select * from t_card的执行计划就知道了 如果要按照聚集索引的顺序查询 就强制走索引 比如聚集索引的名字是index_1 那么你就 select * from t_card with(index(index_1)) 这样查询出来的就是预期的排序了 当 select * from t_card 因为走了索引扫描（这里先不讨论为啥走索引扫描，就是因为索引的关系，他认为走索引扫描效率高吧） 因此，默认查询出来的排序就非预期的排序方式了
专注or全面 2014-03-18
  • 打赏
  • 举报
 回复 



--我也只是找到这中现象发生的情况，置于具体原因，也不得而知
--下面演示一下，是在什么情况下发生这种现象的
--后边有时间会查阅一下相关资料，有答案之后回来回复说明的

--我在本机演示一下，
--一边演示脚本一边解释测试目的


/*********第一种情况，列上指定主键**********************/

drop table [t_card]

--第一次，建表，并且制定id为主键
CREATE TABLE [dbo].[t_card](
	[id] [char](36) NOT NULL primary key,
	[companyId] [int] NULL,
	[shopid] [int] NULL
) ON [PRIMARY]


--插入测试数据
insert into t_card values (NEWID(),1,1)
insert into t_card values (NEWID(),1,2)
insert into t_card values (NEWID(),2,1)
insert into t_card values (NEWID(),2,2)
insert into t_card values (NEWID(),3,1)
insert into t_card values (NEWID(),3,2)

--执行查询
select * from t_card;

--制定主键的时候，默认在主键上建立聚集索引
--根据查询结果，很容易看出来是按照id列排序的

/*
id                                   companyId   shopid
------------------------------------ ----------- -----------
1EE06752-F0C9-48A7-A701-E9E36A0597F3 2           1
2D0F9EF7-A491-461E-A2E0-361D8BB5F3F9 2           2
51A6A413-1F85-4A54-AB15-71CB578781A3 3           2
7660BD62-73B1-4A68-8E4B-718AB33A40B2 1           1
C5C65857-0B70-4867-9CB3-2FDA56B67EB6 3           1
F6283875-3223-4255-8848-1BB27B6E539B 1           2

(6 行受影响)
*/

--对于默认主键生成的聚集索引，我们用脚本删除的时候，提示错误

drop index PK__t_card__3213E83F6754599E on t_card

--不允许对索引 't_card.PK__t_card__3213E83F6754599E' 显式地使用 DROP INDEX。
--该索引正用于 PRIMARY KEY 约束的强制执行。




--那么我就在SSMS中“右键”菜单功能删除这个聚集索引
--然后建立一个聚集索引(模仿你聚集键)

create unique clustered index index_1 on t_card(companyid,shopid,id)

--命令已成功完成。

--顺便再插入一条数据
insert into t_card values (NEWID(),2,4)


--再次查询，可以看到按照预期的索引排序了

select * from t_card;

/*
id                                   companyId   shopid
------------------------------------ ----------- -----------
7660BD62-73B1-4A68-8E4B-718AB33A40B2 1           1
F6283875-3223-4255-8848-1BB27B6E539B 1           2
1EE06752-F0C9-48A7-A701-E9E36A0597F3 2           1
2D0F9EF7-A491-461E-A2E0-361D8BB5F3F9 2           2
EE6655B0-CDCA-4D00-8325-33730C28CB7C 2           6
C5C65857-0B70-4867-9CB3-2FDA56B67EB6 3           1
51A6A413-1F85-4A54-AB15-71CB578781A3 3           2

(7 行受影响)

*/




/********第二种情况，列上指定主键，非聚集索引*************/


drop table [t_card]

CREATE TABLE [dbo].[t_card](
	[id] [char](36)   primary key NONCLUSTERED ,
	[companyId] [int] NULL,
	[shopid] [int] NULL
) ON [PRIMARY]

--插入数据时，特意调了一下插入的顺序
insert into t_card values (NEWID(),1,1)
insert into t_card values (NEWID(),1,2)
insert into t_card values (NEWID(),3,1)
insert into t_card values (NEWID(),3,2)
insert into t_card values (NEWID(),2,1)
insert into t_card values (NEWID(),2,2)

select * from [t_card]

--很明显，默认顺序是无序的，没有按照哪个列排序
/*
id                                   companyId   shopid
------------------------------------ ----------- -----------
F7484505-3E0C-42E8-80D7-9160726EEDEE 1           1
B153F47D-4FE5-4435-8131-D1D14ED512B1 1           2
C3F2571C-1613-4ADC-A4C3-9E3089C3E384 3           1
34102E5C-C486-494D-96E4-807F3C1E01DC 3           2
E47C8EEF-D905-4A97-9051-029B34BC667D 2           1
CBBF8A87-72ED-4FB6-BD4C-880ABD50CAAC 2           2

(6 行受影响)

*/

--建立聚集索引
create unique clustered index index_1 on t_card(companyid,shopid,id)


select * from [t_card]

--问题就出在这里，
/*
*********************************************************
*建表时，指定了主键，且指定主键为非聚集索引
*然后在其他列上建立了聚集索引，默认排序竟然是按照主键来排序的
*再怎么重建索引，也没有按照预期的列排序
**********************************************************
*/


/*
id                                   companyId   shopid
------------------------------------ ----------- -----------
218A084F-B30A-4708-8920-F014C0521463 1           2
7B6BB287-B8D8-40D2-A986-CA515172A84B 1           1
AF462D7C-AA87-4249-9849-FE6BD2BFE032 3           1
B1C6062C-FBF0-44DE-9A6F-27F091EE537E 2           2
D582665C-7F42-4C91-AF2A-3FBECBA216E1 2           1
DAB65C80-3978-45FB-B0FB-000178158A27 3           2

(6 行受影响)


*/


/********第三种情况，列上没有任何约束，自定义聚集或者非聚集索引*************/
--这个就不测试了，都是预期的排序情况
stephenchern 2014-03-18
  • 打赏
  • 举报
 回复

按照楼上的意思 我索引重建下 顺序还是没变过来啊
新的这个索引 是按照companyid shopid cardid聚集排序的。。。
stephenchern 2014-03-18
  • 打赏
  • 举报
 回复
的确 索引要重建下 就说得过去了。。。
stephenchern 2014-03-07
  • 打赏
  • 举报
 回复
继续请教。。。。
专注or全面 2014-03-07
  • 打赏
  • 举报
 回复
你重建一下索引试试 好像也试出来楼主这种情况了，确实有点诡异！ 好像顺序是：创建表，指定主键（默认建立了聚集索引），删除聚集索引，新建一个聚集索引， select * from table 顺序还是原来聚集索引的顺寻，好像是新建索引（create clustered ）的时候，没有把表中的数据重新排序 然后重建聚集索引，就好了 alter index index_tcard on t_card rebuild
专注or全面 2014-03-07
  • 打赏
  • 举报
 回复
引用 10 楼 stephenchern 的回复:
纠结的问题 截图给大家看看 sp_help查出来的信息： 数据查询出来物理排序：
从你的默认排序来看，你的聚集索引还在cardid上，不是明摆着么？
专注or全面 2014-03-07
  • 打赏
  • 举报
 回复 



--测试了一下，好像木有问题啊



select @@VERSION

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86) 
	Apr  2 2010 15:53:02 
	Copyright (c) Microsoft Corporation
	Enterprise Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)


(1 行受影响)


create table t_card
(
	id char(36),
	companyId int,
	shopid int
)

create clustered index index_tcard on t_card(id)



insert into t_card values (NEWID(),1,1)
insert into t_card values (NEWID(),1,2)
insert into t_card values (NEWID(),2,1)
insert into t_card values (NEWID(),2,2)

select * from t_card

id                                   companyId   shopid
------------------------------------ ----------- -----------
3A517FA4-7CE8-44EC-BA00-E03461C8552B 1           2
3D0AF66E-5251-4063-9AED-421ACB519EDD 1           1
5C0F844A-F0EA-4A71-B7DA-8109727F3060 2           1
F4EEDFF7-DFD6-4F32-A7B9-1B1BABC4A150 2           2

(4 行受影响)


drop index index_tcard on t_card

create clustered index index_tcard on t_card(companyId,shopid,id)


select * from t_card

id                                   companyId   shopid
------------------------------------ ----------- -----------
3D0AF66E-5251-4063-9AED-421ACB519EDD 1           1
3A517FA4-7CE8-44EC-BA00-E03461C8552B 1           2
5C0F844A-F0EA-4A71-B7DA-8109727F3060 2           1
F4EEDFF7-DFD6-4F32-A7B9-1B1BABC4A150 2           2

(4 行受影响)
stephenchern 2014-03-05
  • 打赏
  • 举报
 回复
还请大家帮忙看看 奇怪的问题 tks
stephenchern 2014-03-05
  • 打赏
  • 举报
 回复
纠结的问题 截图给大家看看
sp_help查出来的信息：

数据查询出来物理排序：
發糞塗牆 2014-03-05
  • 打赏
  • 举报
 回复
跟版本没关系，你可以考虑先把表上的非聚集索引禁用甚至保存定义然后删掉，看看是否非聚集索引的影响
stephenchern 2014-03-05
  • 打赏
  • 举报
 回复
排序当然不一样啦。。 用管理器打开表查看顺序 和 用selec语句打开 结果 都是一样的 没有因为聚集列改了 而改变物理排序。 奇怪了 不知道怎么回事 我mssq是开发版的 会不会是因为这个问题？？？
發糞塗牆 2014-03-04
  • 打赏
  • 举报
 回复
结构打开表看 实际数据物理排列 没有改变 这个怎么理解？
LongRui888 2014-03-04
  • 打赏
  • 举报
 回复
你的表就聚集索引，还有其他的非聚集索引吗
--小F-- 2014-03-04
  • 打赏
  • 举报
 回复
更改聚集索引 需要重新建立其他的索引。
专注or全面 2014-03-04
  • 打赏
  • 举报
 回复
关键是你的那两个字段排序是不是一样的？ 比如你 select * from table order by column1 跟 select * from table order by column2 结果顺序是一样的，那就理应不变了。
發糞塗牆 2014-03-04
  • 打赏
  • 举报
 回复
另外你说的是用SSMS打开表，我觉得你可以使用SELECT * FROM TB来看看顺序
加载更多回复（2）
mysql的填充因子_聚集索引和填充因子fill factor的设置，减少死锁 | 学步园
为什么聚集索引很容易引起死锁？请高手解答我的困惑。楼主windljf(精神领袖)2004-12-08 08:49:23 在 MS-SQL Server / 基础类 提问我的数据库很容易死锁，我后来发现是聚集索引引起的，我的索引填充因子是90，后来我把聚集索引去掉，问题就解决了，但是我不明白聚集索引为什么会引起死锁？？请教高手 问题点数：100、回复次数：8Top1 楼good2speed(Good...
MySQL索引（详细，1万字长文）
本文知识点较多，篇幅较长，请耐心学习 MySQL索引 索引类型 索引可以提升查询速度，会影响where查询，以及order by排序。MySQL索引类型如下： 从索引存储结构划分：B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引 从应用层次划分：普通索引、唯一索引、主键索引、复合索引 从索引键值类型划分：主键索引、辅助索引（二级索引） 从数据存储和索引键值逻辑关系划分：聚集索引（聚簇索引）、非聚集索引（非聚簇索引） 普通索引 这是最基本的索引类型，基于普通字段建立的索引，没.
mysql主键索引、非主键索引、联合索引、覆盖索引、最左匹配、索引下推
mysql主键索引、非主键索引、联合索引、覆盖索引、最左匹配、索引下推
为什么我用了索引结果还是全表扫描？建议用FORCE INDEX强制使用索引吗？
Q：rt 根据时间范围来捞数据，但时间跨度大，查询的结果占比过多，就出现了问题。 A： 索引不起作用，当where里面是范围的时候有时会不起作用，要查询的范围过大，数据占总数据的20%左右，优化器就会选择全表扫描（聚集索引）；虽然可以使用FORCE INDEX使用强制索引，但请教过DBA同学后，DBA同学并不建议用，实际看了会后说是优化不大，所以最终的解决方案是，把时间分片，分割成范围较...
MySQL 索引详解（并包括了如何分析SQL语句的问题）（强烈推荐）
from: http://my.oschina.net/leejun2005/blog/134932?fromerr=520CpC61 前段时间参加了Mysql索引与sql调优培训，自己线下摸索实践学习了下，这里总结几点分享给大家。顺便巩固下自己所学:) 一、InnoDB主键设计 众所周知，InnoDB是clustered-index table，因此对于InnoDB而
MS-SQL Server

34,590

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章