SQL Server 索引

参考博客:https://www.cnblogs.com/selene/p/4474721.html

索引的简介:

索引是一种磁盘上的数据结构,建立在表或视图的基础上。使用索引可以使数据的获取更快更高校,也会影响其它的一些性能,比如插入、修改和删除等操作。
索引分为聚集索引和非聚集索引,数据库中的索引类似于一本书的目录,在一本书中通过目录可以快速找到你想要的信息,而不需要读完全书。

索引主要目的是提高了SQL Server系统的性能,加快数据的查询速度与减少系统的响应时间 。

但是索引对于提高查询性能也不是万能的,也不是建立的索引越多就越好。索引建少了,用 WHERE 子句查找数据效率低,不利于查找数据。索引建多了不利于新增、修改和删除等操作,因为做这些操作时,SQL SERVER 除了要更新数据表本身,还要连带立即更新所有的相关索引,而且过多的索引也会浪费硬盘空间。

索引的优点主要有以下几条:

 (1).通过创建唯一索引,可以保证数据库表的每一行数据的唯一性。

 (2).可以大大加快数据的查询速度,这也是创建索引的最主要的原因。

 (3).实现数据的参照完整性,可以速表和表之间的连接。

 (4).在使用分组和排序子句进行查询时,也可以显著减少查询中分组和排序的时间。

同时,增加索引也有不利的一面:

 (1).创建索引和维护索引要耗费时间,并且随着数据量的增加所耗费的时间也会增加。

 (2).索引需要占磁盘空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果有大量的索引,索引文件可能比数据文件更快达到做大文件尺寸。

 (3).当对表中的数据进行增加,删除和修改的时候,索引也要动态地维护,这样就就降低了数据的维护速度。

索引的分类:
索引就类似于中文字典前面的目录,按照拼音或部首都可以很快的定位到所要查找的字。

主键索引:当创建表时指定的主键列,会自动创建主键索引,并且拥有唯一的特性。

唯一索引(UNIQUE)每一行的索引值都是唯一的(创建了唯一约束,系统将自动创建唯一索引)

聚集索引(CLUSTERED)聚集索引就相当于使用字典的拼音查找,因为聚集索引存储记录是物理上连续存在的,即拼音 a 过了后面肯定是 b 一样。数据行的物理顺序与列值的逻辑顺序相同,一个表只能拥有一个聚集索引。

非聚集索引(NONCLUSTERED):非聚集索引就相当于使用字典的部首查找,非聚集索引是逻辑上的连续,物理存储并不连续。该索引中索引的逻辑顺序与磁盘上数据行的物理存储顺序不同,一个表中可以拥有多个非聚集索引。其实按照定义,除了聚集索引以外的索引都是非聚集索引,只是人们想细分一下非聚集索引,分成普通索引,唯一索引,全文索引。

组合索引:指定索引基于多列,一个组合索引中最多可组合16列。组合索引中的所有列必须在同一个表或视图中。

PS:聚集索引一个表只能有一个,而非聚集索引一个表中可以有多个。 

什么情况下使用索引:

  • 定义主键的数据列(sql server默认会给主键一个聚集索引)。
  • 定义有外键的数据列
  • 对于经常查询的数据列
  • 对于需要在指定范围内频繁查询的数据列
  • 经常在where子句中出现的数据列
  • 经常出现在关键字 order by、group by、distinct后面的字段。

什么情况下不要设置索引:

  • 查询中很少涉及的列,重复值比较多的列。
  • text、image、bit数据类型的列
  • 经常存取的列
  • 经常更新操作的表,索引一般不要超过3个、最多不要5个。虽说提高了访问速度,但会影响更新操作。

查看一个表的索引信息:EXEC sp_helpindex '表名'

查看索引的统计信息:DBCC SHOW_STATISTICS ('表名', 索引名称)

聚集索引(CLUSTERED): 默认情况下,生成主键的同时将自动创建一个聚集索引

CREATE CLUSTERED INDEX 索引名 ON 表名 (列名)

--建立聚集索引
create clustered index index_name /*聚集索引名*/
on table_name  /*表名*/
(
   id  /*列名,多个用逗号隔开(id,name)*/
)
with(drop_existing=on); /*如果存在则删除*/

非聚集索引(NONCLUSTERED): 

CREATE NONCLUSTERED INDEX 索引名 ON 表名 (列名)

--建立非聚集索引
create nonclustered index index_name /*聚集索引名*/
on table_name  /*表名*/
(
   id  /*列名,多个用逗号隔开(id,name)*/
)
with(drop_existing=on); /*如果存在则删除*/

管理索引:

--查看一个表的索引信息
exec sp_helpindex 表名
--查看索引的统计信息
DBCC SHOW_STATISTICS ('表名', 索引名称)
--修改索引名称
exec sp_rename '表名.name_index','new_name' 
--删除索引
drop index 表名.new_name
drop index new_name ON 表名
--检查碎片
dbcc showcontig(表名,new_name)
--整理碎片
dbcc indexdefrag(webDB,表名,new_name) 
--更新表中所有索引的统计
update statistics 表名

 

posted @ 2023-03-22 18:47  以德为先  阅读(148)  评论(0编辑  收藏  举报