MySQL - 索引类型详解

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

MySQL支持多种类型的索引，主要包括以下几种：

1. B-Tree索引：B-Tree是最常见的索引类型，可以用于全部比较操作（包括=、<>、>、<、>=、<=、BETWEEN和LIKE）。它适合处理全值匹配和范围查询。大部分MySQL引擎默认的索引类型即为B-Tree索引。

2. Hash索引：Hash索引基于哈希表实现，只包含哈希值和行指针，因此大小通常小于B-Tree索引。它非常快，但只能用于等值查询（使用=或<>操作符），不能用于范围查询。记住，仅有MEMORY存储引擎支持Hash索引。

3. R-Tree索引：R-Tree索引（又名空间索引）被用于地理数据存储，而且只有MyISAM、InnoDB和NDB引擎支持。它支持对二维对象做精确查找和近似查找。

4. FULLTEXT索引：FULLTEXT索引用于全文搜索。只有Char、Varchar、Text列可以创建全文索引，并且目前只有MyISAM、InnoDB和Mroonga存储引擎支持FULLTEXT索引。

5. Clustered索引：聚簇索引，又称为主键索引，是一种将数据行与键值紧密相连的索引。如果一个表有聚簇索引，那么该表的数据行实际上是按照索引键来排序存储的。InnoDB存储引擎的表会自动创建主键聚簇索引。

6. Secondary索引：副索引或者非聚簇索引，在一个表中可以有多个，而且不需要和数据存放在一起。它包含索引字段值和对应行数据地址的指针。

这些索引类型在使用时的具体选择取决于您的数据以及您需要进行的查询类型。

1. 索引的分类

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

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

1.1 普通索引

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

【这是最基本的索引，它没有任何限制。也被称为非唯一索引。】

创建普通索引的方式：

• 创建索引：

create index index_name on table_name (column_name);

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

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

alter table table_name add index index_name (column_name);

• 创建表的时候直接指定

create table mytable
(
    id       int         not null,
    username varchar(16) not null,
    index index_name (column_name)
);

• 删除索引的语法

drop index index_name on table_name; 

1.2 唯一索引

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

【唯一索引不允许索引列有重复值。如果是组合索引，则列值的组合必须是唯一的。】

创建唯一索引的方式：

• 创建索引

create unique index index_name on table_name (column_name);

• 修改表结构

alter table table_name add unique index_name (column_name);

• 创建表的时候直接指定

create table table_name
(
    id       int         not null,
    username varchar(16) not null,
    unique index_name (column_name)
);  

1.3 主键索引

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

【这是特殊的唯一索引，不允许有空值。一个表只能有一个主键索引。】

创建主键索引的方式：

• 创建索引

create table table_name
(
    id       int         not null,
    username varchar(16) not null,
    primary key (column_name)
);

• 修改索引

alter table table_name add primary key (column_name);

1.4 组合索引

组合索引也称为复合索引或多列索引。相对于单列索引来说，组合索引是将原表的多个列共同组成一个索引。

多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。

但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。

【组合索引是多个列作为一个索引键，用于处理查询条件中涉及多列的情况。】

• 创建索引

create index index_name on table_name (column_name1, column_name2);

1.5 全文索引

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

【在全文索引中，存储引擎会对所有数据进行分析，找出每个单词的位置信息，并且在查询时利用这些信息进行快速查找，常用于全文搜索。注意，目前仅MyISAM、InnoDB和Mroonga存储引擎支持FULLTEXT索引。】

• 创建索引

create fulltext index index_name on table_name (column_name);

• 修改索引

alter table table_name add fulltext index_name (column_name);

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

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

全文索引使用注意事项：

•  全文索引必须在字符串、文本字段上建立。
• 全文索引字段值必须在最小字符和最大字符之间的才会有效。（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);

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）的索引，那么只需要修改原来的索引即可