索引及其作用

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

        全表扫描要查找某个特定的行，必须从头开始一一查看表中的每一行，与查询条件作对比，返回满足条件的记录，当表中有很多行时，查询效率非常低。

建立索引目的：

提高查询速度
保证列值的唯一性
查询优化依靠索引起作用
提高ORDER BY，GROUP BY的执行速度
2．索引的分类
1）普通索引

        普通索引是最基本的索引类型，它没有唯一性之类的限制。创建普通引的关键字是INDEX .

2）唯一性索引

        唯一性索引和前面的普通索引基本相同，但有一个区别：唯一性索引的索引列的所有值都只能出现一次，即必须是唯一的。创建唯一性索引的关键字UNIQUE .

3）主键

        主键是一种唯一性索引，它必须指定为 PRIMARY KEY 。主键一般在创建表时指定，也可以通过修改表的方式加入键，但是每个表只能有一个主键。

4）聚簇索引

        聚簇索引的索引顺序就是数据存储的物理顺序，这样能保证索引值相近的元组所存储的物理位置也相近。一个表只能有一个聚簇索引。

5）全文索引

         MySQL 支持全文检索和全文索引。在 MySQL 中，全文索引的索引类型为 FULLTEXT 。

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

3．索引的使用
 使用索引可以提高系统的性能，加快数据检索的速度，但是使用索引要付出一定的代价。

(1）增加存储空间：索引需要占用磁盘空间。

(2）降低更新表中数据的速度：当更新表中数据时，系统会自动更新索引列的数据，这可能需要重新组织一个索引。

4．建立索引的建议

(1）查询中很少涉及的列、重复值比较多的列不要建立索引。

(2）数据量较小的表最好不要建立索引。

(3）限制表中索引的数量。

(4）在表中插入数据后创建索引。四

(5）如果 char 列或 varchar 列字符数很多，可视具体情况选取前 N 个字符值进行索引。

二、索引操作
1、创建索引
1.1在已有的表上创建（CREATE INDEX）

        index_name:指定所建立的索引名称，一个表可建多个索引，每个索引名称必须是唯一的

        tbl_name:指定需要建立索引的表名

        UNIQUE:指定所创建的索引是唯一性索引

        col_name:指定要创建索引的列名

        length:指定使用列的前length个字符创建索引

        ASC|DESC:升序（ASC），降序（DESC）

#【例1】 在stusys数据库中student表的sname列上，创建一个普通索引I_studentSname。

 #【例2】 在stusys数据库中course表的cno列上，创建一个索引I_courseCno，要求按课程号cno字段值前2个字符降序排列。

 #【例3】 在stusys数据库中student表的tc列(降序)和sname列(升序)，创建一个组合索引I_studentTcSname。

 1.2在已有的表上创建（ALTER TABLE）

#【例4】在stusys数据库teacher表的tname列，创建一个唯一性索引I_teacherTname，并按降序排列。

 

1.3在创建表同时创建索引（CREATE TABLE）

#【例5】在stusys数据库中，创建新表score1表，主键为sno和cno，同时在grade列上创建普通索引。

 

2、查看表上建立的索引

#【例6】查看例7.16所创建的score1表的索引。

 

3、删除索引

3.1 使用DROP INDEX

#【例7】删除已建索引I_studentTcSname。

 

3.2 使用 ALTER TABLE

#【例8】删除已建索引I_teacherTname。