MYSQL之索引（基本操作及底层原理）

       MYSQL中很重要的一个知识点，也是面试的常考点：索引。本文将主要从索引的概念，优缺点，分类，基本操作以及底层原理五个方面为大家进行介绍，至于索引的优化问题将在下一篇文章中进行介绍。

什么是索引

      索引是创建在表上的，是对数据库表中的一列或多列的值进行排序的一种结构。索引可以提高查询速度，通过索引，查询数据时可以不必读完记录的所有信息，只是查询索引列，从而有效提高数据库系统的性能。

索引的优缺点

• 优点

   索引的优点在于可以提高检索数据的速度：对于有依赖关系的子表和父表之间的联合查询，可以提高查询速度；使用分组和排序子句进行数据查询时，同样可以显著节省查询中分组和排序的时间。

• 缺点

索引的缺点是创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加：索引需要占用物理空间，每一个索引要占用一定的物理空间；增加，删除和修改数据时，要动态维护索引，造成索引的维护速度降低了。

       因此，在创建索引时，要综合考虑索引的优缺点。索引可以提高查询效率但是影响插入记录的速度（向有索引的表中插入记录，数据库会按照索引进行排序，从而降低插入记录的速度），这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后，再创建索引。

索引的分类

• 普通索引

 在创建普通索引时，不需要附加任何限制条件。可以创建在任何数据类型中；

• 唯一性索引

使用UNIQUE参数可以将索引设置为唯一性索引。创建唯一性索引时，限制该索引的值必须是唯一的。通过唯一性索引，可以更快地确定某条记录。主键就是一种特殊的唯一性索引。 

• 全文索引

使用FULLTEXT参数可以将索引设置为全文索引。全文索引只能创建在char,varchar或text类型的字段上。查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。全文索引只有MyISAM存储引擎支持。在默认情况下，全文索引的搜索方式不区分大小写，但索引的列使用二进制排序后，可以执行区分大小写的全文索引。 

• 单列索引

在表中的单个字段上创建索引。单列索引可以是普通索引，全文索引，唯一性索引，只要满足该索引对应一个字段。 

• 多列索引

在表中的多个字段创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询，但，只有查询条件使用了这些字段中的第一个字段是，索引才会被使用。

• 空间索引

 使用SPATIAL参数将索引设置为空间索引，只能建立在空间数据上。只有MyISAM存储引擎支持空间索引且索引的字段不能为空值，较少使用。 

索引的创建与删除

1、在创建表是设置索引

create table table_name (

属性名 数据类型 [完整性约束条件],

属性名 数据类型 [完整性约束条件],

......

属性名 数据类型 [完整性约束条件],

[UNIQUE | FULLTEXT | SPATIAL ] index | key  [别名] (属性名)

);

2、创建表后设置索引

create [unique | fulltext | spatial] index 索引名(idx_id) on 表名(属性名);

alter table 表名 add [unique | fulltext | spatial] index 索引名(属性名);

3、删除索引

drop index 索引名 on 表名；

4、索引的执行过程

       索引的执行过程可以通过explain语句查看，举例进行说明。  

      创建一个Student表，表里字段分别为：Student(SID,Sname,Sage,Ssex)

--SID 学生编号,Sname 学生姓名,Sage 年龄,Ssex 学生性别

插入基础数据后查看表中信息：