MySQL - 索引

索引概述：

索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

假如我们要执行一条SQL语句为： select * from user where age = 18;

在无索引情况下，就需要从第一行开始扫描，一直扫描到最后一行，我们称之为 全表扫描，性能很低。

如果我们对一张数据表建立了索引，就能高效的获取数据，无需做全表扫描，性能会大大提高。

索引的优缺点：

优势	劣势
提高数据检索的效率，降低 数据库的IO成本	索引列也是要占用空间的
通过索引列对数据进行排序，降低 数据排序的成本，降低CPU的消耗	索引大大提高了查询效率，同时却也 降低了更新表的速度，如对表进行 INSERT、UPDATA、DELETE时，效率降低。

索引结构：

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种：

上述是MySQL中所支持的所有的索引结构，接下来，我们再来看看不同的存储引擎对于索引结构的支持 情况。

B+Tree:

B+Tree是B-Tree的变种，我们以一棵最大度数（max-degree）为4（4阶）的B+Tree为例，来看一下其结构示意图：

我们可以看到，两部分：

①绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。

②红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。

B+Tree与B-Tree相比，主要有以下3点区别：

①所有的数据都会出现在叶子节点。

②叶子节点形成一个单向链表。

③非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。

---

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高了区间访问的性能，利于排序。

Hash:

MySQL中除了支持B+Tree索引，还支持一种索引类型---Hash索引。

哈希表索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。

举个栗子：

id是主键，我们要对name字段创建一个hash索引的数据结构。怎么做呢？

首先，先需要算出这张表当中每一行数据的hash值，接下来再拿到name字段的所有值，针对name字段的所有值通过它内部的hash函数去计算每一个name值应该落在哪一个hash表的槽位上，这些001 ，002，...都是hash表中对应的槽位，经过hash算法算出来的键值，它的hash值是多少就会落在对应的槽位上。

比如金庸进行hash运算，算出它的槽位值是005，在这个槽位中就会存储金庸这个key以及金庸那条记录对应的hash值。

如果两个（或多个）键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。

hash索引特点：

A. Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，...）

B. 无法利用索引完成排序操作

C. 查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索 引

存储引擎支持：

在MySQL中，支持hash索引的是Memory存储引擎。 而InnoDB中具有自适应hash功能，hash索引是 InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的。

面试题：

为什么InnoDB存储引擎选择使用B+tree索引结构？

①相比普通的二叉搜索树：当我们向二叉树顺序插入数据时，它会形成一个链表，层级高，  查询性能极低，而B+tree层级更少，搜索效率高。

②对于B-tree:无论是叶子节点还是非叶子节点，都会存储数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低。而MySQL索引数据结构对经典的B+tree进行了优化，在原B+tree的基础上，增加了一个指向相邻叶子节点的链表指针，提高了区间访问的性能，有利于排序。

③对于hash索引：hash索引只支持等值匹配，无法进行范围查询以及排序。

---

索引分类：

主要分为4种：

在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。

聚集索引和二级索引：

而在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

聚集索引选取规则：

如果存在主键，主键索引就是聚集索引。

如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。

如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

聚集索引和二级索引的具体结构如下：

聚集索引的叶子节点下挂的是这一行的数据。

二级索引的叶子节点下挂的是该字段值对应的主键值。

接下来，我们来分析一下，当我们执行如下的SQL语句时，具体的查找过程是什么样子的。

具体过程如下:

①. 由于是根据name字段进行查询，所以先根据name='Arm'到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。

②. 由于查询返回的数据是 * ，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最 终找到10对应的行row。

③. 最终拿到这一行的数据，直接返回即可。

回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取 数据的方式，就称之为回表查询。

思考题：

InnoDB主键索引的B+tree高度为多高呢?

假设:

一行数据大小为1k，一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空 间，主键即使为bigint，占用字节数为8。

高度为2：

n * 8 + (n + 1) * 6 = 16*1024 ,     n代表主键个数

算出n约为 1170

1171* 16 = 18736

也就是说，如果树的高度为2，则可以存储 18000 多条记录。

高度为3：

1171 * 1171 * 16 = 21939856

也就是说，如果树的高度为3，则可以存储 2200w 左右的记录。

---

索引语法：

1）创建索引

create   [ unique | fulltext]   index   index_name   on   table_name (index_col_name,  ...);

2)查看索引

show     index      from      table_name;

3)删除索引

drop      index      index_name      on      table_name;