–当表中有大量记录时

--前提代码:
--创建数据库
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(相当于页码)快速找到表中对应的记录。

–当表中有大量记录时。–注意创建索引也需要时间和计算量,一般只用于经常需要访问的列。

–当表中有大量记录时。–聚集索引:物理存储顺序与逻辑顺序一致,线性结构(一条直线)。
–当表中有大量记录时。–非聚集索引:物理存储顺序与逻辑顺序不一致:树状(多条分支)。

–其他资料:

–额,还是不太懂。。

 

You may also like...

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图