MySQL - 索引类型详解_mysql四种索引类型

通俗的来说，索引就相当于一个字典的目录，为了方便查找书中的内容，通过对内容建立索引形成目录，索引是一个文件，它是要占据物理内存的。
官方的来说索引它是一种数据结构，数据库索引是数据库管理系统中一个排序的数据结构，以协助快速查询，更新数据表中的数据。

1. 索引的分类

MySQL目前主要有以下几种索引类型：

1. 普通索引
2. 唯一索引
3. 主键索引
4. 组合索引
5. 全文索引

1.1 普通索引

普通索引是 MySQL 中最基本的索引类型，它没有任何限制，唯一任务就是加快系统对数据的访问速度。
普通索引允许在定义索引的列中插入重复值和空值。 创建普通索引时，通常使用的关键字是 INDEX 或 KEY。

创建普通索引的方式：

• 创建索引：

create index index_name on table_name (column_name);
1

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

• 修改表结构（添加索引）

alter table table_name add index index_name (column_name);
1

• 创建表的时候直接指定

create table mytable
(
    id       int         not null,
    username varchar(16) not null,
    index index_name (column_name)
);
1
2
3
4
5
6

• 删除索引的语法

drop index index_name on table_name; 
1

1.2 唯一索引

唯一索引与普通索引类似，不同的是创建唯一性索引的目的不是为了提高访问速度，而是为了避免数据出现重复。
唯一索引列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一。
创建唯一索引通常使用 UNIQUE 关键字。

创建唯一索引的方式：

• 创建索引

create unique index index_name on table_name (column_name);
1

• 修改表结构

alter table table_name add unique index_name (column_name);
1

• 创建表的时候直接指定

create table table_name
(
    id       int         not null,
    username varchar(16) not null,
    unique index_name (column_name)
);  
1
2
3
4
5
6

1.3 主键索引

主键索引就是专门为主键字段创建的索引，也属于索引的一种。
主键索引是一种特殊的唯一索引，不允许值重复或者值为空。
创建主键索引通常使用 PRIMARY KEY 关键字。不能使用 CREATE INDEX 语句创建主键索引。

创建主键索引的方式：

• 创建索引

create table table_name
(
    id       int         not null,
    username varchar(16) not null,
    primary key (column_name)
);
1
2
3
4
5
6

• 修改索引

alter table table_name add primary key (column_name);
1

1.4 组合索引

组合索引也称为复合索引或多列索引。相对于单列索引来说，组合索引是将原表的多个列共同组成一个索引。多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。

• 创建索引

create index index_name on table_name (column_name1, column_name2);
1

1.5 全文索引

全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。
全文索引允许在索引列中插入重复值和空值。
不过对于大容量的数据表，生成全文索引非常消耗时间和硬盘空间。
创建全文索引使用 FULLTEXT 关键字。

• 创建索引

create fulltext index index_name on table_name (column_name);
1

• 修改索引

alter table table_name add fulltext index_name (column_name);
1

和常用的like模糊查询不同，全文索引有自己的语法格式，使用 match 和 against 关键字，比如：

select * from user where match(name) against('aaa');
1

全文索引使用注意事项：

• 全文索引必须在字符串、文本字段上建立。
• 全文索引字段值必须在最小字符和最大字符之间的才会有效。（innodb：3-84；myisam：4-84）
• 全文索引字段值要进行切词处理，按syntax字符进行切割，例如b+aaa，切分成b和aaa
• 全文索引匹配查询，默认使用的是等值匹配，例如a匹配a，不会匹配ab,ac。如果想匹配可以在布尔模式下搜索a*

select * from user where match(name) against('a*' in boolean mode);
1

2. MySQL索引的优缺点

2.1 优点

（1）通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
（2）可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
（3）可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
（4）在使用分组和排序子句进行数据检索时，同时可以显著减少查询中分组和排序的时间。

2.2 缺点

（1）创建索引和维护索引需要时间成本，这个成本随着数据量的增加而加大。
（2）创建索引和维护索引需要空间成本，每一条索引都要占据数据库的物理存储空间，数据量越大，占用及空间也越大（数据表占据的是数据库的数据空间）
（3）会降低表的增删改的效率，因为每次增删改索引需要进行动态维护，导致时间边长。

3. 索引的使用场景

3.1 选择唯一性索引

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

3.2 经常需要排序，分组，联合操作的字段建立索引

经常需要 order by，group by ，distinct ， union 等操作的字段，排序操作会浪费很多时间，为其建立索引可以有效地避免排序操作。

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

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

3.4 限制索引的数目

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

3.5 尽量使用数据量少的索引

索引的值越长查询的速度会越慢。例如一个char（100）类型的字段进行全文检索需要的时间比char（10）类型的字段需要的时间更多。

3.6 删除不再使用或很少使用的索引

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

3.7 尽量的扩展索引，不要新建索引

比如表中已经有a 的索引，现在要加（a，b）的索引，那么只需要修改原来的索引即可。