这样总结MySQL索引分类才好

文章简介

本文将大致介绍索引的类型、InnoDB的索引分类、如何创建索引、使用索引的注意事项等几个方面记录索引。由于侧重点的不同，本文不会全面介绍索引的知识点，例如二叉树、平衡二叉树、B Tree和B + Tree等，后面会单独针对这几种数据结构，进行深度的分享。导图连接(点击底部阅读原文，就可以打开文档连接了。)

问题引入

详细很多程序员在面试的时候，都会被问到这样一个问题“MySQL中的索引都有哪些”？或许你很自信的啪啪啪答了一大堆，什么唯一索引、普通索引、主键索引、联合索引等等索引名称，你还以为自己答得非常完美。此时，你看面试官脸上带着一脸嫌弃的样子，你心里瞬间跌入了万丈深渊似的。

不是因为你没有答对，而是因为你回答的方式不对。该文将梳理如何回答该问题。

文章提纲

下面的截图也是文章的大致题纲，写作的思路也是围绕该题纲进行。

索引定义

什么是索引，想必大家都很熟悉了。使用的字典目录这个案例也是非常之多了。这里个人罗列一下自己对为什么使用索引多一个定义吧(不一定准确，属于个人理解)。

索引是利用数据结构的特点，实现一种为了快速检索数据的存储方式。

存储引擎

本文的话题是索引，为什么会提出存储引擎这个东西呢？因为不同的存储引擎支持的索引也不同。下面罗列出不同的存储引擎之间的区别：

hash索引是不需要我们手动设置的，InnoDB存储引擎的表会自动根据使用情况，调整为hash索引。其他的索引类型，使用的少，暂时也没过多的了解，后期针对该文更新时弥补上。

索引分类

索引从数据结构上主要分为下面四种索引类型。其中B+Tree索引使用情况也是最多的。后面文章重点也是总结该索引类型。

1. B+Tree 类型索引
2. hash索引
3. 空间索引
4. 全文索引

B+Tree索引

B+Tree是一种多路搜索的数据结构类型。它就像一棵树一样，有根节点、叶子结点、枝干等元素。一般在提交到索引的时候，都会提及到二叉树、平衡二叉树、B Tree这几种数据结构，至于为什么会提及呢？主要是B+Tree是根据这几种数据结构演变过来的，因为前面的数据结构存在这种种不足。

这里简单提及一下几种数据结构，后面单独出一篇文章来总结这几种数据结构。

索引分类

从 B+Tree数据结构分类，InnoDB中的索引类型分为聚集索引和非聚集索引。聚集索引包含了主键索引，而非聚集索引包含了唯一索引、联合索引、前缀索引、复合索引、普通索引。

hash索引

hah索引是一种等值类型的数据类型。通过将键值进行hash计算，检索时通过过相同的hash方式进行等值查找的一种存储策略方式。

这种索引类型在等值上检索快(不需要像B+tree进行逐级查找，只需要进行一次的hash计算，就等定位到数据，检索快。)，但是也存在诸多不足之处。例如：

1. 不能进行范围检索。
2. 不能进行大小比较。
3. 不能进行排序检索。

使用该索引也不需要手动去定义，InnoDB存储引擎会根据表的使用情况，自动生成hash索引，不能通过人为的干预生成hash索引。

定义索引

主键索引

定义

由一个或者多个字段组成的索引列，该索引列是唯一的、自增的、不能为null的。

语法

alter table table_name add primary key(column_name)

唯一索引

定义

表中的当前列的值不能重复，但可以为null。

语法

alter table table_name add unique(column_name)

前缀索引

定义

为值的前几个字符创建索引的一种策略。

语法

alter table add key(column_name(inde_length))

该类索引适用于text，blob，varchar等字符类型，但是建议减少该索引类型的使用。

优缺点

1. 便于快速检索数据。
2. 不能使用在order by情况中。
3. 不能使用在group by的情况中。
4. 不能使用在覆盖索引的情况中。
5. 创建的索引长度，最好是根据column_name对应的长度来确定。

普通索引

语法

alter table table_name add index index_name(column_name)

联合索引

定义

是表中两个或者两个以上的索引组成的一个新索引。

语法

create index index_name on table_name(index_name_1,index_name_2)

优缺点

1. 减少数据检索范围。
2. 必须遵循前缀索引原则，则索引会生效。

覆盖索引

定义

一个索引包含(覆盖)所有查询字段的值。

优点

1. 减少回表查询查询额外的字段值。为什么覆盖索引会检索快呢？
2. 索引检索的数据量始终是小于数据表的数据量。
3. 索引是按照顺序检索的，避免了直接检索表的随机IO读取。
4. 减少系统层面的调用，部分存储引擎读取数据还需要调用系统层面。
5. 在InnoDB的聚集索引中，可以减少二次索引的开销。

查看索引

show语法

mysql root@127.0.0.1:demo> show index from `user`\G;
2 rows in set
Time: 0.003s
***************************[ 1. row ]***************************
Table         | user
Non_unique    | 0
Key_name      | PRIMARY
Seq_in_index  | 1
Column_name   | id
Collation     | A
Cardinality   | 7
Sub_part      | <null>
Packed        | <null>
Null          |
Index_type    | BTREE
Comment       |
Index_comment |
***************************[ 2. row ]***************************
Table         | user
Non_unique    | 0
Key_name      | idx_mobiel
Seq_in_index  | 1
Column_name   | mobile
Collation     | A
Cardinality   | 7
Sub_part      | <null>
Packed        | <null>
Null          | YES
Index_type    | BTREE
Comment       |
Index_comment |

explain语法

explain语句可以分析出SQL语句是否使用了索引、检索的类型等情况。

mysql root@127.0.0.1:demo> explain select * from `user` where id = 1\G;
1 row in set
Time: 0.003s
***************************[ 1. row ]***************************
id            | 1
select_type   | SIMPLE
table         | user
partitions    | <null>
type          | const
possible_keys | PRIMARY
key           | PRIMARY
key_len       | 4
ref           | const
rows          | 1
filtered      | 100.0
Extra         | <null>

注意事项

创建索引

1. 选择性低的字段不要创建字段。
2. 很少查询的列不要创建索引。
3. 大数据类型的字段不要创建索引。
4. 尽量避免列不要使用null，尽可能的设置为not null。尽可能使用空值来代替这种情况。

使用索引

1. 通过索引扫描的行记录数数超过全表的30%，优化器不会走索引，而且会变成全表扫描。
2. 联合索引中，第一个查询条件不是最左索引列不会走索引。
3. 模糊查询条件列最左以"%"开始的。
4. 两个单列索引，一个用于检索，一个用于排序。这种情况是只能使用一个索引，因为SQL查询语句中最多只能使用一个索引，推荐使用联合索引来替代。
5. 查询列上面使用了聚合函数，也不会走索引。