MySQL-索引

索引是一种特殊的数据库结构，可以用来快速查询数据库中的特定记录。索引是提高数据库性能的重要方式。MySQL中，所有的数据类型都可以被索引。MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

索引简介

索引由数据库表中一列或多列组合而成，其作用是提高对表中数据的查询速度。

索引的含义和特点

索引是创建在表上的，是对数据库中一列或多列的值进行排序的一种结构。索引可以提高查询的速度。索引有明显的优点，也有不可

避免的缺点。

索引的优势是：可以提高检索数据的速度，这是创建索引的最主要的原因；对于有依赖关系的子表和父表之间的联合查询时，可以提高查询速度；使用分组和排序进行数据查询时，同样可以显著节省查询中分组和排序的时间。

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

索引的分类

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

普通索引

在创建普通索引时，不附加任何限制条件。这类索引可以创建在任何数据类型上，其值是否唯一和非空由字段本身的完整性约束条件决定。建立索引以后，查询时可以通过该索引进行查询。

唯一性索引

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

全文索引

使用FULLTEXT参数可以设置索引为全文索引。全文索引只能创建在char、Varchar或text类型的字段上。查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。

单列索引

在表中的单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。

多列索引

多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。例如，在表中的id、name和sex字段上建立一个多列索引，那么，只有查询条件使用了id字段时该索引才会被使用。

空间索引

使用spatial参数可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。空间数据类型包括geometry、point、linestring、polygon等。目前只有myisam存储引擎支持空间检索，这类索引很少会用到。

索引的设计原则

为了使索引的使用效率更高，在创建索引的时候必须考虑在哪些字段上创建索引和创建什么类型的索引。设计原则如下：

1.选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。

2.为经常需要排序、分组和联合操作的字段建立索引

经常需要排序、分组和联合操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效的避免排序操作。

3.为常作为查询条件的字段建立索引

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

4.限制索引的数目

索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

5.尽量使用数据量少的索引

如果索引的值很长，那么查询的速度会受到影响。例如，对一个char（100）类型的字段进行全文检索需要的时间肯定要比char（10）类型的字段需要的时间要多

6.尽量使用前缀来索引

如果索引字段的值很长最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

7.删除不在使用或者很少使用的索引。

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再被需要。数据库管理员应当定期找出这些索引 ，将他们删除，从而减少索引对更新操作的影响。

创建索引

创建索引是指在某个表的一列或多列上建立一个索引，以便提高对表的访问速度。创建索引有3种方式，这3种方式分别是创建表的时候创建索引、在已经存在的表上创建索引和使用alter table 语句来创建索引。

创建表的时候创建索引

创建表的时候可以直接创建索引，这种方式最简单、方便。其基本形式如下：

create table 表名（属性名 数据类型[完整性约束条件],属性名 数据类型[完整性约束条件]，

1.创建普通索引

2.创建唯一性索引

3.创建全文索引

4.创建单列索引

5.创建多列索引

6.创建空间索引）

在已经存在的表上创建索引

在已经存在的表上，可以直接为表上的一个或几个字段创建索引。基本形式如下：

create [unique|fulltext|spatial] index 索引名 on 表名（属性名 [长度][asc|desc]）；

1.创建普通索引

2.创建唯一性索引

3.创建全文索引

4.创建单列索引

5.创建多列索引

6.创建空间索引

用alter table语句来创建索引

在已经存在的表上，可以通过alter table 语句直接为表上的一个或几个字段创建索引。其基本形式如下：

alter table 表名 add [unique |fullText |spatial] index 索引名（属性名[（长度）][asc|desc]）;

其中的参数与上面的两种方式的参数是一样的。

1.创建普通索引

2.创建唯一性索引

3.创建全文索引

4.创建单列索引

5.创建多列索引

6.创建空间索引

删除索引

删除索引是指将表中已经存在的索引删除掉。一些不在使用的索引会降低表的更新速度，影响数据库的性能。对于这样的索引，应该将其删除。

对于已经存在的索引，可以通过drop 语句来删除索引。基本形式如下：

drop index 索引名 on 表名