SQL语法整理——索引

什么是索引

数据库中表的索引与日常生活中所使用的书或者字典的索引是相似的。索引可以极大地提高查询的速度。

?

索引的分类

索引包含由表或视图中的一列或多列生成的键.

根据索引的使用方式、数据的存储方式可以将索引分为4类：

???????????????????? 惟一性索引、

???????????????????? 主键索引、

???????????????????? 聚集索引、

???????????????????? 非聚集索引。

?(1)索引列

索引是创建在表的列上，可以在一个列上，也可以在多个列上，当某一个列数值具有相同的数值，多列的索引就能将其区分开。如果经常搜索两个列或者多个列的时候，索引也是有帮助的。

确定索引的有效性包括以下内容：

a.检查查询中的WHERE和JOIN字句，在任何一个字句中，包含每一列都是索引可以选择的。

b.试验新的索引，检查其对运行查询性能的影响。

c.考虑表中已经创建的索引数据量，不要在一张表上创建大量的索引。索引是一个比较大的对象，会降低查询的速度。

d.检查表中创建的索引，尽量避免包含重复的列，尤其是在多列中。

e.尽量在数值型的列上创建索引，数值的比较、查询速度都要高于字符串。

<经常在student表上，基于学号与姓名作为查询条件查询信息，则在这两列上创建多列的索引，将能大大提高访问的速度。>

?

?

(2)聚集索引

聚集索引的含义是：表中的各行的物理顺序与索引的逻辑顺序是相同的，每张表中只有一个，因为一旦创建成功，其数据的物理顺序就固定了。

聚集索引根据数据行的键值在表或视图中排序和存储这些数据行。索引定义中包含聚集索引列。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序排序。

只有当表包含聚集索引时，数据行才按排序顺序存储。如果表具有聚集索引，则称为聚集表。如果表没有聚集索引，则其数据行存储在一个称为堆的无序结构中。

?

?

创建聚集索引的语法：

CREATE CLUSTERED(集群) INDEX index_name

ON {table_name|view_name} (column[ASC|DESC][,…n])

?

/*在result表id列上创建聚集索引*/

CREATE?? CLUSTERED?? INDEX??? idx_result_id

ON?? result(student_id?? ASC)

GO

（result只能创建一个聚集索引，表中的物理顺序与索引中的物理顺序都已固定，当再次创建一个聚集索引时就会报错。如果要在另一个列上创建聚集索引，那么只能先将前面创建的聚集索引删除。）

?

<大家都使用过字典，排序方法是按着拼音，所以当查询索引（目录）一旦定下来，整个字典中所有字所在的页数就确定了。这种拼音查字方法决定的索引就是聚集索引。>

?

?

(3)非聚集索引

非聚集索引不影响表中数据的物理位置，是建立在表中聚集索引的基础之上的。一张表上只能创建一个聚集索引，但是现在却可以创建多个非聚集索引。

/*在result表subject列上创建非聚集索引*/

/*创建名称为idx_result_subject索引*/

CREATE??? NONCLUSTERED? INDEX?? idx_result_subject

ON??? result(subject? ASC)

GO

?

/*使用SP_HELPINDEX命令查看在表result上创建的索引*/

EXECUTE?? SP_HELPINDEX???? result

GO

?

<最普通的，也是最重要的查字法就是：拼音查字法。大多数字典都是以拼音查字法为基准，这就对应着数据库中的聚集索引。除了拼音查字法以外，还有一些特殊的，例如：部首查字法、笔画查字法等。这些查字法，是建立在字典中字所在位置都确定的基础上的，是建立在拼音查字法的基础上的。这些查字法在数据库中就对应着非聚集索引。

一张表上只能创建一个聚集索引，但是现在却可以创建多个非聚集索引。就象一个字典，在拼音查字法定下了字所在的位置后，可以制定笔画、部首查字法来适应不同的查询需要。>

?

(4)惟一性索引

惟一性索引类似于UNIQUE约束，不允许两行具有相同的索引值。如果现有的数据中存在重复的数值，则数据库是不允许创建惟一性索引的。当更新数据的时候，使得表中的数值重复的话，数据引擎也会拒绝这样的修改。

在创建惟一性约束(UNIQUE)的时候，实际上系统也为这张表列创建了一个相应的惟一性索引,其作用与主键约束相同,既起到约束的作用又同时起到索引的作用。

/*在student表cardno列上创建惟一性索引*/

CREATE???? UNIQUE?? NONCLUSTERED?? INDEX?? idx_student_cardno

ON?? student(cardno?? ASC)

ON?? students??????????????? ---将索引存储在students文件组上

GO

创建成功！

?

/*创建名称为idx_student_teacher_id索引*/

CREATE?? UNIQUE???? NONCLUSTERED?? INDEX??? idx_student_teacher_id

ON??? student(teacher_id???? ASC)

ON???? students??????????????? --将索引存储在students文件组上

GO

创建失败！

?

<因为cardno列上的数值是惟一的，所以创建成功。但teacher-id列上的数值不是惟一的，所以无法创建惟一性的索引。>

?

?

(5)主键索引

主键,通常是一列或者多个列的组合,惟一地标识表中的数据行.在关系数据库中为表定义了一个主键以后，相应地就会创建一个索引,是跟随主键而创建的,主键索引实际上是惟一性索引的特殊例子,要求被创建索引的列满足主键的要求—— 不能为空、数值惟一。当在查询中使用了索引，允许快速地访问数据。

当创建主键约束的时候，系统会自动创建名称相同的聚集索引，一旦创建了主键，就不能再在这张表上创建聚集索引了。

?

?

总结：

CREATE [UNIQUE][CLUSTERED][NONCLUSTERED] INDEX index_name

ON <object>(column? [ASC|DESC][,…n])

UNIQUE:代表的是惟一索引。

CLUSTERED：代表的是聚集索引。

NONCLUSTERED：代表的是非聚集索引。

index_name：代表的是索引的名称。

<object>:可以是表也可以是视图。

column：是要创建的索引引用的列。

ASC代表索引是升序的，DESC代表索引是降序的。

?

?

索引管理

索引创建以后，需要数据库管理员经常维护、管理。由于表在创建索引以后，会影响数据的插入、修改、删除的速度，一般的情况下，会牺牲一点消耗，以换取高速的查询。但是，当大批量数据导入到数据库的时候，就要考虑性能问题，这个时候为了能够节约时间，数据库管理员会把索引删除或禁用，当数据导入完毕，再重新创建索引或者将索引启用。