SQL Sever创建和管理索引

Summ
336 阅读4分钟

索引的概念：

实例：图书馆的图书索引。
索引是一个重要的、常用的数据库对象。
索引要建立在表上。表上的索引就像书的目录一样。
使用索引可以大大提高数据库的检索速度。
---索引表是排序的，可采用二分查找等快速定位算法。
---索引表还可以驻留在内存，进一步提高速度。
缺点：增加系统开销。

  • 索引是一个文件：索引文件/索引表，需要占据空间存储。
  • 更新表时索引文件也同时更新(DBMS自动更新)索引文件，以保持索引文件与数据文件的一致性。

根据索引对数据表中记录的影响，索引分为两种类型：

聚集索引（clustered index）
非聚集索引（nonclustered index）

聚集索引

特点：
数据文件中的记录按照索引键指定的顺序排序，使得具有相同索引键值的记录在物理上聚集在一起。
一个表只能建立一个聚集索引。
建立聚集索引后，插入的记录会按一定的顺序放在指定的位置。 例如插入的一条记录的编号列为8，则会插入在编号列为和9的中间。
而如果是非聚集索引或没有建立索引，则会插到表的最后一行之后。
聚集索引适用于

  1. 大量非重复值的列；
  2. 经常被连续访问的列；
  3. 经常使用连接或 GROUP BY 子句查询访问的列。
    聚集索引不适用于
    频繁更改的列。

非聚集索引

特点：
不会影响表中记录的实际存储顺序。
一个表可以有多个非聚集索引。
非聚集索引不能像聚集索引那样利用数据表本身的顺序查找记录。
为了查找给定键值的记录，必须在非聚集索引中为每个键值建立一个索引项：

  • 索引项的第一列是索引键值，
  • 索引项的第二列是与键值相对应的记录指针。 说明：
    1）非聚集索引与数据行的存放顺序无关
    2）非聚集索引作为表的附加信息
    3）非聚集索引有利于单行查询，不利于范围查询

非聚集索引与聚集索引的重要区别

  • 数据记录不按非聚集索引键的顺序排序和存储。
  • 数据与索引分开存储。
  • 在非聚集索引中仅仅包含索引值和指向数据行的指针。

唯一索引

特点：
不允许表中任何两行具有相同索引值。
主键索引是唯一索引的特定类型。
在为表定义主键时将自动创建主键索引。
如果聚集索引不是唯一索引，SQL Server将自动添加在内部生成的值以使重复的键唯一，从而生成唯一索引，但用户看不到这个值。

索引何时创建

取决于对列的查询方式。
以下查询推荐创建索引：

  • 精确匹配查询。如：Where 列名=值
  • 特定范围查询。如Where 列名 between … and …
  • 连接操作查询。如：Where 表1.列名=表2.列名
  • 未进行排序但要求排序的查询
  • 以特定字符串开始的LIKE查询。如：like ‘ab%’
  • 有外键的两个表之间的匹配行的查询

创建索引

CREATE 
[UNIQUE]  [CLUSTERED] | [NONCLUSTERED]
INDEX  index_name 
ON { table | view } (column [ASC | DESC] [,…n] )

CLUSTERED：聚集索引。数据的物理存储位置按索引排列； NONCLUSTERED：非聚集索引。数据的物理存储不一定按索引排列。
缺省为NONCLUSTERED(非聚集)
PRIMARY KEY约束默认为聚集索引；UNIQUE约束默认为非聚集索引。
[ASC | DESC] ：默认升序。

创建聚集索引

例：为Student表按“姓名” 列升序建立一个聚集索引Stusname 。

CREATE CLUSTERED  INDEX  Stusname ON  Student(姓名)；

为Student表按学号升序建聚集索引Stusno

CREATE CLUSTERED INDEX  Stusno ON Student(Sno)；

一个表只能建立一个聚集索引。
当一个表建立主键后，系统会自动建立主键索引。
当一个表建立主键后，就不能再为该表建立聚集索引了。只能建立非聚集索引。

创建非聚集索引

为student表建立以性别(降序)、姓名(升序)为索引关键字的非聚集索引。

create nonclustered  index 学生_性别_姓名 on student(性别 desc,姓名 asc)

创建唯一索引

为一列定义唯一约束(创建表时)后，系统自动为该列建立唯一索引和唯一键。

为SC表按学号升序、课程号降序建唯一索引SCno

CREATE UNIQUE INDEX  SCno ON SC(Sno ASC,Cno DESC);

删除索引

DROP INDEX <表名>. <索引名>[,…n]；

例：删除Student表的Stusname索引。

DROP INDEX Student.Stusname；

重命名索引

利用系统提供的存储过程sp_rename可以对索引进行重命名

例：将student表中的索引idx_name更名为idx_stu_name。

Exec sp_rename 'student.idx_name’, 'idx_stu_name'

使用索引

检索时可指明要使用(引用)的索引。
省略时使用聚集的唯一索引。

select 学号，姓名 from xsda
with (index ( IDX_总学分))  /*with(index(IDX_总学分))两对括号都不能少！ */
where总学分=50

与下面的结果相同

select 学号，姓名 from xsda
where 总学分=50
avatar
文章
阅读
粉丝