4.2 索引及其操作

        对数据库中的表进行查询操作时有两种搜索扫描方式，一种是全表扫描，另一种就是使用表上建立的索引进行扫描。

        全表扫描要查找某个特定的行，必须从头开始一一查看表中的每一行，与查询条件做对比，返回满足条件的记录，当表中有很多行时，查询效率非常低；
索引是按数据表中一列或多个列进行索引排序，并为其建立指向数据表记录所在位置的指针。   

        使用索引可以提高系统的性能，加快数据检索的速度。但是使用索引要付出一定的代价。增加存储空间，降低更新表中数据的速度。

索引的分类：

1)普通索引
       最基本的索引类型，没有唯一性之类的限制。创建普通索引的关键字是INDEX。
2)唯一性索引
       和普通索引基本相同，但唯一性索引的索引列的所有值都只能出现一次，即必须是唯一的。创建唯一性索引的关键字是UNIQUE。
3)主键
       是一种唯一性索引，必须指定为primary key。一般在创建表时指定，也可以通过修改表的方式加入主键。每个表只能有一个主键。
4)聚簇索引
       聚簇索引的索引顺序就是数据存储的物理顺序，保证索引值相近的元组所存储的物理位置也相近。一个表只能有一个聚簇索引。
5)全文索引
        MySQL支持全文检索和全文索引。在MysQL中，全文索引的索引类型为fulltext。

索引可以建立在一列上，称为单列索引，一个表可以建立多个单列索引。索引也可以建立在多个列上，称为组合索引、复合索引或多列索引。

4.2.1 创建索引

三种方法：

在已有的表上创建索引可用create index语句和alter table语句；
在创建表的同时创建索引可用create table语句。

1. 使用create index语句创建索引

语法格式：create [ unique ] INDEX index_name
                        on table_name ( col_name [ ( length ) ] [ asc | desc ]，... )

其中，length为可选项，用于指定使用列的前length个字符创建索引。

【例4.12】在数据库study中学生表的姓名列上创建一个普通索引 Ⅰ_studentSname。

create index I_studentSname on 学生(姓名);

【4.13】在数据库study中课程表的课程号列上创建一个普通索引Ⅰ_courseCno，要求按课程号字段值降序排列。

create index I_courseCno on 课程(课程号 desc);

【4.14】在数据库study中选课表的成绩列（降序）和学号列（升序）创建一个组合索引Ⅰ_courseGradeSno。

create index I_courseGradeSno on 选课(成绩 desc,学号);

排序时先按成绩列降序排序。若成绩列值相同，再按学号列升序排序。 

2.使用alter table语句创建索引

语法格式：
alter TABLE tab_name
add [ unique | fulltext ] [ index | key ] [ index_name ] (col_name [ ( length ) ] [ asc | desc ]，...)  

【例4.15】在数据库study中教师表的tname列创建一个唯一索引Ⅰ_teacherTname。

alter table 教师
	add unique index I_teacherTname(tname desc);

这里的“tname desc”是指按照汉语拼音对应的英文字母顺序排列；相应的，如果是英文，按照英文字母顺序进行排列。

3. 使用create table语句创建索引

可在创建表的同时创建索引。

语法格式：create TABLE tab_name [ col_name data_type ]
                        [ constraint index_name ] [ unique | fulltext ]
                        [ index | key ] [ index_name ] (col_name [ ( length ) ] [ asc | desc ]，...)  

【例4.16】在数据库study中创建新表选课1表，主键为学号和课程号，同时在成绩列上创建普通索引。

create table 选课1 (
	学号 char(6) not null,
	课程号 char(4) not null,
	成绩 tinyint,
	primary key(学号,课程号),
	index(成绩));

 

4.2.2 查看表上建立的索引

语法格式：show { index | indexes | keys } { from | in } tb1_name [ { from | in } db_name ]

【例4.17】查看4.16所创建的选课1表的索引。

show index from 选课1;

从以上代码可以看出，在选课1表上建立了3个索引，2个主键索引，索引名称是primary。索引建立在学号和课程号列上，1个普通索引，索引名称是grade，索引建立在grade列上。

4.2.3 删除索引

1. 使用drop index语句。

语法格式：drop INDEX index_name on table_name

【例4.18】删除已建的索引Ⅰ_courseGradeSno。

drop index I_courseGradeSno on 选课;

该语句执行后，选课表上的索引被删除，对选课表无影响，也不影响该表上其他索引。

2. 使用alter table语句删除索引。

语法格式：alter  table tb1_name drop INDEX index_name

【例4.19】删除已建的索引Ⅰ_teacherTname。

alter table 教师
	drop index I_teacherTname;