MySql 索引

本篇主要介绍MySql中索引的相关内容

目录

一、什么是索引

索引简介

索引的语法

 二、索引结构

常见索引结构

Hash索引

B+树索引

三、索引的分类

按类型划分

按存储形式划分 

回表查询 

---

一、什么是索引

索引简介

在我们学习英语时，经常会遇到不认识的单词，此时我们就需要去字典里查这个单词的意思，并且在查字典时，我们会根据字典的目录去查询，如果没有目录，我们就需要一页页查询，这样可能查个一天都查不到。MySql查数据的时候，也就和这种没有目录去查字典一样，需要一个一个去查，那MySql有没有一个像目录一样的功能呢？答案是有的，那就是索引。 

索引，表示一种数据结构，他能将数据库表中的数据以 这种数据结构组织起来，从而大大提高查询的性能。

例如，我们这里有一张表，表中的数据如下

此时，在执行下面这条Sql时

select * from stu where id = 8; 

如果没有索引，则需要查询五次（全表查询）。

如果有索引，索引结构如下（B+树） 

此时我们只要 查询一次就能找到id = 8的数据了，性能大大提升。但由于索引的加入，会使得在进行添加数据，删除数据，修改数据时需要对索引也进行同步修改，因此，索引会降低修改数据的性能。因此在创建索引时，应考虑当前表是查询的操作占比大，还是修改数据的操作占比大后再来创建索引。

索引的语法

创建索引的语法如下：

create [索引类型] index 索引名 on 表名（字段名）

查看索引：

show index from table_name 

删除索引

drop index  index _name on table_name 

 二、索引结构

前面我们说过，索引会将数据以某种数据结构组织起来，这种数据结构到底是什么呢，我们来了解一下。

常见索引结构

在MySql中，常见的索引结构有一下四种：

• B+Tree索引：最常见的索引结构，很多引擎都使用这种索引结构。
• R-Tree索引： 空间索引，是MyISAM引擎中的一个特殊索引，主要用于一些地理数据类型。
• hash索引：有hash表来实现，只能进行个别查询，不能进行范围查询。
• full-text：建立倒排索引，能够快速匹配文档

不同存储引擎对于这些索引结构的支持情况有所不同，具体如下 

下面我们来具体了解一下Hash索引和B+树索引。

Hash索引

Hash索引在内部维护了一个Hash表，数据通过Hash算法获得一个数值，并根据该值存到Hash表对应的槽位上，如果当前槽位发送哈希冲突（多个数据通过Hash算法得到的值相同），通过链表的方式来解决。

如果要查询某个数据，直接根据查询的字段值进行hash运算后的结果就能很快的找到所查询数据 。但Hash索引，存在一定问题，主要为下面几点：

• 当哈希冲突时会使用链表，如果冲突的次数过多，就会导致链表长度过长，从而影响查询效率
• 哈希索引每次只能查到一个数据，且数据前后没有顺序关系，因此不能进行范围查询

B+树索引

在介绍B+树索引前，我们先来了解一下以二叉搜索树和B树作为索引结构的情况 。

以二叉搜索树构建的索引结构如下：

从图中可以看出使用二叉搜索树做为索引结构，我们查询速度会得到很大程度的提升，我们每次最多进行树的高度次IO（每比较一次节点值大小都得与磁盘IO一次）就能查到目标数据了，但图中的二叉树是一颗平衡二叉树，这是一种比较理想的状态，通常情况下，并不会达到这种平衡的效果，更多的是不平衡的状态，例如：

 

 如果数据按照顺序进行存储，还会出现下面这种链表的情况

这样树的高度都是比较大的，从而导致磁盘IO（查数据）的次数依然很多，优化效果并不是很好。

因此为了降低树的高度，我们可以采取b树来作为索引结构，使用B树作为索引结构的情况如下：

可以发现由于B树每一个节点能存储多个数据，从而使得树的高度要比二叉搜索树要低很多。从而与磁盘进行IO的次数也就更少，查询的速度也就更快了。在B树中，叶子节点和非叶子节点都会存储当前数据的全量数据，这样就会使得空间占用比较大，并且每次IO的数据量也会比较大，因此，B树通常也不会用作索引结构。

最后我们再来看一下使用B+树的情况：

从图中可以发现，B+树的叶子节点中也包含了非叶子节点的值，并且叶子节点之间用单向链表进行连接，这样我们就不需要在非叶子节点中保存全量数据，只需要保存加了索引的字段的值用来进行大小比较即可，全量数据保存在叶子节点中，并且通过非叶子节点之间的链表，我们也能更快速的进行范围查询。在每次查询数据时，我们都需要查树的高度次才能获取到对应的全量数据，从而让我们的查询的时间更均衡，因此大多数存储引擎都使用B+树来作为 索引结构。

三、索引的分类

按类型划分

在MySql中，对于索引的类型进行了分类，主要可以分为以下四种：

• 主键索引：对设置为主键的字段创建的索引，只要设置了主键就会自动创建该索引，默认只有一个，索引的关键字为primary
• 唯一索引：设置了该索引的字段值不能重复，可以有多个，关键字为unique
• 常规索引：对普通字段设置的索引，可以有多个，没有关键字
• 全文索引：该索引在比对时不是比较字段的值，而是查找全文的关键字，可以有多个，关键字为FullText

按存储形式划分 

根据存储方式的不同，索引又可以分为聚集索引和二级索引，像前面那种将一整行的全量数据都保存在叶子节点，非叶子节点只存某一个字段值的索引就称为聚集索引，聚集索引只能有一个，默认为主键索引，如果没有主键索引，则为第一个唯一索引（按字段的顺序的第一个）。而二级索引则是无论在叶子节点还是非叶子节点都不存储全量数据，叶子节点保存字段值，非叶子节点则保存字段值和字段所在行的主键id（没有主键则为第一个唯一索引的字段值）。

 

 

回表查询 

由于聚集索引叶子节点包含整行数据，因此在查到叶子节点就可以直接返回数据了，而二级索引查到叶子节点时，只能查到索引的字段值和主键，因此要获取全量数据，还需要根据叶子节点中的主键去聚集索引中查。这个根据叶子节点中包含的主键去聚集索引查全量数据的过程就被为回表查询。证实因为回表查询的存在，使得二级索引在查询全量数据时的效率要比聚集索引低。