索引的运用

索引整理

 

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代表索引是降序的。

 

 

索引管理

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

创建索引要慎重，因其是一个非常大的对象，只有在经常查询的列上，创建索引才能够发挥作用，不要创建在数据量比较小的表上，也不要创建在很少访问的表上。创建索引有利有弊，利是通过索引访问数据，其速度非常快，能够大大节约用户访问系统的速度；弊是创建索引会影响数据的插入、删除、修改的操作，尤其是在表的数据量非常大的时候，利弊的权衡需要仔细考虑。不过在多数情况下，索引带来的数据检索速度的好处要远远大于弊端。

 

1)修改索引

关键字是ALTER INDEX

a.禁用索引

当进行数据的大批量导入或者修改的时候，可以先将索引禁用，这样可以提高对表数据的DML操作。

/*禁用在学生表格上的idx_student_cardno索引*/

ALTER    INDEX    idx_student_cardno

ON   student

DISABLE

GO

 

b.启用索引

当大批量数据修改或导入以后，索引需要重新启用。

/*启用在学生表格上的idx_student_cardno索引*/

ALTER     INDEX    idx_student_cardno

ON  student

REBUILD

GO

 

(2)索引碎片

就像操作系统经常需要进行碎片的整理一样，数据库的操作也会出现很多碎片，尤其是对表中的数据进行频繁添加、修改、删除的动作，更会大大增加碎片的数量。索引是建立在表的数据基础上，由于频繁地对表数据的修改，必然会造成索引碎片的产生。

/*使用DBCC语句来整理碎片*/

DBCC   SHOWCONTIG      (stduent)

GO

 

如果索引碎片非常多，可选择以下方法来减少

a.删除然后重新创建聚集索引：创建聚集索引将重新组织，使数据页填满。这种方法的缺点是，索引在删除/重新创建周期内，为脱机状态，并且该操作是一个整体，不可中断。如果中断，则不能重新创建索引。

b.对索引的页级按逻辑顺序重新排序：使用ALTER INDEX…REORGANIZE，对索引的页逻辑顺序重新排序。由于此操作是联机的，因此语句运行时索引可用。此外，中断该操作不会丢失已完成的工作。这种方法的缺点是在重新组织数据方面，没有聚集索引的删除/重新创建操作有效。

c.联机重新生成索引：使用REBUILD和ALTER INDEX。

 

全文索引

前面介绍的索引，都是建立在数字字段或者字符串类型(一般选择长度比较短的)上的，一般的，是不会在存储地址信息或者人员简介等类字段上，因为长度比较大。而全文索引，就是针对这种大文本建立的索引解决方法，可以快速地定位，提取数据。

对大量非结构化的文本数据进行查询时，使用全文搜索获得的性能优势，会得到充分的表现。对数百万行文本数据执行的LIKE查询，可能需要花费几分钟才能返回结果；但对同样的数据，全文查询只需更小的时间，具体取决于返回的行数。

创建全文索引需要执行两个步骤：

(1)创建全文目录来存储全文索引

(2)创建全文索引

 

/*创建名为school_cagalog的目录*/

USE   school

GO

--在当前数据库中启用全文索引，如果不起用这个属性，则不能创建全文目录

EXECUTE  SP_FULLTEXT_DATABASE   enable

GO

CREATE FULLTEXT CATALOG(目录) school_catalog

ON   FILEGROUP  students

GO

 

/*在表student上添加address列，全文索引用在存储大的信息上*/

USE   school

GO

ALTER   TABLE    student

ADD   address   varchar(200)

GO

 

为表格中的数据输入信息……

 

/*在数据库中使用全文索引*/

SELECT  id AS ‘学生编号’,

        name AS ‘学生姓名’

        address AS ‘学生地址’

FROM student

WHERE   FREETEXT(address,’西城区’)

GO

 

慎重使用/删除索引

索引本身是比较大的对象，在表列上创建索引以后，将大大影响表的数据添加、删除、修改速度。在表上创建索引要仔细考虑。

a.对数据量小的表不要创建索引，这样非但不会提高，反而会降低查询访问的速度。

b.创建索引的表列，经常要被访问，不能在不经常访问的表上创建索引。

c.不要过多地创建非聚集索引，这样会影响访问的速度。

d.要经常地对索引进行重建。

e.在大批量数据导入或数据修改的时候，要先将索引禁用，操作完后，再将索引重新启用。

f.如果表的数据量非常巨大(GB以上的数据量)，这个时候即使创建了索引，进行了最好的优化，也不会将查询速度提高多少。

 

删除索引语法：

DROP INDEX index_name

ON <object>

实际上，在大批量数据操作的时候，数据库管理员通常会将索引删除，然后重新建立。