索引:如何让主键不自动创建聚集索引???

2017-01-06 19:40:04来源:cnblogs.com作者:hao_1234_1234人点击

--前提代码:--创建数据库create database UsersDBTest  --sqlserver这一句话就可以创建数据库,其它按默认值创建。 --创建表  create table UserInfo  (	  ID int identity(1,1) not null, 	  Uname nvarchar(max) not null,	  Pwd nvarchar(max) not null,	  ShowName nvarchar(max) null,  );		--代码创建主键 Alter table UserInfo    add constraint PK_UserInfo  --给主键约束取别名,寓意:UserInfo表的主键约束。取别名的好处是1删除方便2为多个列定义同时约束,即主键组。		Primary Key (ID Asc); --主键约束 ,根据ID升序排序--但是刷新表后发现 索引项多了 PK_UserInfo ---sqlserver自动对主键列创建了聚集索引(补充:主键默认是唯一的所以也是唯一索引)	--相当于 	 Alter table UserInfo    add constraint PK_UserInfo  		Primary Key Clustered (ID Asc); 		--问题:1能不能把建立索引与主键分离,然后单独对添加、修改和删除索引?--百度得:--添加索引:CREATE INDEX   索引名称    ON  表名(列)--删除索引:drop index 索引名称  on  表名--查寻索引: select * from sys.indexes where name='索引名称';  --并没发现直接修改索引的语法,那就先删除再添加吧。 --drop Index PK_UserInfo On UserInfo--报错:不允许对索引 'UserInfo.PK_UserInfo' 显式地使用 DROP INDEX。该索引正用于 PRIMARY KEY 约束的强制执行。 alter table UserInfo drop constraint PK_UserInfo --结果:聚集索引没了,但主键约束也没来。  --单独建立UserInfo表索引列和索引类型 create Index IX_UserInfo On UserInfo(ID); --为了好区分,IX_UserInfo是我为索引取的别名。--结果:不唯一,非聚集索引 Alter table UserInfo    add constraint PK_UserInfo		Primary Key  (ID Asc);--重建主键约束,结果:默认又为主键创建了聚集索引!--删除IX_UserInfo  drop Index IX_UserInfo On UserInfo--为什么对于自己创建的索引,显式删除有效,对系统默认创建索引的就无效。。--问题2:如何取消主键自动创建索引???--方案一-- 1. 首先删除主键, 然后重新创建主键,   Alter table UserInfo drop constraint PK_UserInfo--2重新创建主键的时候, 需要说明本主键是使用 非聚集索引  Alter table UserInfo     add  constraint PK_UserInfo      Primary Key NonClustered (ID);--3为需要聚集索引的列创建聚集索引      Create  Clustered  Index IX_UserInfo_UName on UserInfo (UName);	 --4想加唯一约束,鼠标却删不掉,代码删除	Drop index IX_RoleInfo_UName on UserInfo;    Create Unique Clustered  Index IX_UserInfo_UName on UserInfo (UName);	  --查询索引:刷新表后查看索引项,或者代码查看   select * from sys.indexes where name='IX_UserInfo_UName';    --方案二------设置非主键为聚集索引【4步】--1. 查看所有的索引,默认情况下主键上都会建立聚集索引    sp_helpindex UserInfo    -- 2. --删除主键约束,从而删除主键上的索引约束。 alter table UserInfo drop constraint PK_UserInfo -- 3.--创建聚集索引到其它列 create  Clustered  Index IX_UserInfo_UName on UserInfo (UName); -- 4.—修改原来的主键字段还是为主键,此时会自动建立非聚集索引【一张表聚集索引只能有一个】 alter table UserInfo add primary key (ID)--如果想自定义名称   Alter table UserInfo     add  constraint PK_UserInfo      Primary Key(ID);--最终结论:两种方案大同小异,只能把主键改为非聚集索引,但是会有索引。--不过把聚集索引的位置腾出来了(一张表只能创建一个聚集索引)。

--但是索引本质到底是什么?

--百度百科:

--索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。索引是针对表而建立的,它是由数据页面以外的索引页面组成的,每个索引页面中的行都会含有逻辑指针,以便加速检索物理数据。

--在数据库关系图中,可以在选定表的“索引/键”属性页中创建、编辑或删除每个索引类型。当保存索引所附加到的表,或保存该表所在的关系图时,索引将保存在数据库中。 

--当表中有大量记录时,若要对表进行查询,第一种搜索信息方式是全表搜索,是将所有记录一一取出,和查询条件进行一一对比,然后返回满足条件的记录,这样做会消耗大量数据库系统时间,并造成大量磁盘I/O操作; --第二种就是在表中建立索引,然后在索引中找到符合查询条件的索引值,最后通过保存在索引中的ROWID(相当于页码)快速找到表中对应的记录。

--注意创建索引也需要时间和计算量,一般只用于经常需要访问的列。

--聚集索引:物理存储顺序与逻辑顺序一致,线性结构(一条直线)。
--非聚集索引:物理存储顺序与逻辑顺序不一致:树状(多条分支)。

--其他资料:

-- http://blog.csdn.net/kennyrose/article/details/7532032

-- http://outofmemory.cn/mysql/mysql-index-and-slow-query#6762184-tsina-1-89901-3400cac556b7c64aaad138f696c2c997

--额,还是不太懂。。

 

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台