一篇文章搞懂索引

                                                       

一、什么是索引？

索引（在MySQL中也叫做“键（key）”）是存储引擎用于快速找到记录的一种数据结构。在MySQL中，存储引擎首先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。

二、索引的优点

1. 大大减少了服务器需要扫描的数据量
2. 帮助服务器避免排序和临时表
3. 将随机IO变成顺序IO

三、索引的用处

1. 快速查找匹配WHERE字句的行
2. 从consideration中消除行，如果可以在多个索引之间进行选择，MySQL通常会使用找到最少行的索引
3. 如果表具有多列索引，则优化器可以使用索引的任何最左前缀来查找行
4. 当有表连接的时候，从其他表检索行数据
5. 查找特定索引列的min和max值
6. 如果排序或分组时在可用索引的最左前缀上完成的，则对表进行排列和分组
7. 在某些情况下，可以优化查询以检索值，而无需查询数据行

四、索引的分类

1、主键索引：主键索引树上挂着索引，其他索引上面挂着主键的值，如果不用主键去检索数据的话，就可能发生回表的现象

2、唯一索引：通过确保表中没有两个数据行具有完全相同的键值来帮助维护数据完整性。

为包含数据的现有表创建唯一索引时，会检查组成索引键的列或表达式中的值是否唯一，如果该表包含具有重复值的行，那么索引创建过程会失败。

主键索引和唯一索引的区别和关联：主键索引是唯一索引，主键不能为null，唯一索引包括主键索引，唯一索引列可以为null，当一个可以为null的列被创建唯一索引的时候，只能有一行值为null

3、普通索引：普通索引的唯一作用是加快对数据的访问速度。普通索引允许被创建普通索引的列有重复值。

4、全文索引：全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文搜索和其他几类索引的匹配放肆完全不一样。它有许多需要注意的细节，如停用词、词干和复数、布尔搜索等。全文索引更类似于搜索引擎做的事情，而不是简单的where条件匹配。

在相同的列上同事创建全文索引和基于值的B-Tree索引不会有冲突，全文索引适用于MATCH AGAINST操作，而不是普通的where条件操作。

5、组合索引：例如（name，age），这样的组合索引，会使用最左匹配的方式使用组合索引，如果左边的索引没有匹配上，那么就不会用到索引，这就是最左前缀原则。

当包含多个列作为索引时，需要注意的是正确的顺序依赖于该索引的查询，同时需要考虑如何更好的满足排序和分组的需要。

6、哈希索引：基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有列的索引列计算一个哈希码（hash code），哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

在MySQL中，只有memory引擎显式支持哈希索引。这也是memory引擎表的默认索引类型，memory引擎同时也支持B-Tree索引。memory引擎是支持非唯一哈希索引的，这在数据库世界里面是比较与众不同的。如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。

因为索引自身之需要存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。然而，哈希索引页有它的限制：

1)、哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能的影响并不明显。

2)、哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。

3)、哈希索引页不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如，在数据列（A,B）上建立哈希索引，如果查询只有数据列A，则无法使用该索引。

4)、哈希索引只支持等值比较查询，包括=、IN()、<=>(注意<>和<=>是不同的操作)。也不支持任何的范围查询，例如 where price>100

5）、访问哈希索引的数据非常快，除非有很多哈希冲突（不同的索引列值却有相同的哈希值）。当出现哈希冲突的时候，存储引擎必须便利链表中的所有的行指针，逐行进行比较，直到找到所有符合条件的行。

如果哈希冲突很多的话，一些索引维护操作的代价也会很高。例如，如果在某个选择性很低（哈希冲突很多）的列上建立哈希索引，那么当从表中删除一行时，存储引擎需要遍历对应的哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。

7、聚簇索引：不是单独的索引类型，而是一种数据存储方式，指的是数据行和相邻的键值紧凑的存储在一起。

InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

InnoDB的存储文件有两个：frm是表定义文件，ibd是数据文件

当表有聚簇索引时，它的数据行实际上存放在索引的叶子页（leaf page）中。术语“聚簇”表示数据行和相邻的键值紧凑的存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引（不过，覆盖索引可以模拟锁哥聚簇索引的情况）

优点：

1. 可以把相关数据保存在一起
2. 数据访问更快，因为索引和数据保存在同一个树中，数据和索引放在一起，直接通过索引获取数据
3. 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

缺点：

1. 聚簇索引最大限度的提高了IO密集型应用的性能，如果数据全部在内存，那么聚簇索引就没有那么大优势
2. 插入速度严重依赖于插入顺序，按照主键的插入是最快的方式。
3. 更新聚簇索引的代价很高，因为会强制将每个被更新的行移动到新的位置
4. 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题
5. 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
6. 二级索引（非聚簇索引）可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。

非聚簇索引：数据文件和索引文件分开存放

MyISAM的索引结构就是非聚簇索引，索引文件只存放地址，数据文件存在

MyISAM存储文件有3个：frm是表定义文件，MYD是数据文件，MYI是索引文件

• 1. 覆盖索引：如果通过非主键查询的数据中包含有主键，那么通过该索引就不需要主键索引树上去重新检索数据了，也就不用进行回表操作，回表过程没有了，就是覆盖索引。
  2. 空间数据索引：MyISAM表支持空间索引，可以用作地理数据存储。和B-Tree索引不同，这类索引无需前缀查询。空间索引会从所有的维度来索引数据。查询时，可以有效地使用任意维度来组合查询。必须使用MySQL的GIS相关函数如MBRCONTAINS（）等来维护数据。MySQL的GIS支持并不完善，所以大部分人都不会使用者个特性。开源关系数据库系统中对GIS的解决方案做得比较好的是PostgreSQL的PostGIS。
  3. 其他索引类型：例如TokuDB使用分形树索引（fractal tree index），这是一类新开发的数据结构，既有B-Tree的很多优点，也避免了B-Tree的一些缺点。

五、面试常用的技术名称

1. 回表：当通过非主键索引去查询的数据中不包含主键，这个时候就会先去查询出主键的值，再通过主键的值回到主键索引值，去查询对应主键的数据，这个现象就叫做回表

2. 覆盖索引：如果通过非主键索引查询的数据包含主键，那么就不需要再去主键索引树上面重新检索数据了，也就不需要进行回表操作，回表过程没有了，就是覆盖索引

回表和覆盖索引的区别：

1. 回表的时候，查询的数据中不包含主键，需要查出主键，再去查一次
2. 覆盖索引，查询的数据中包含主键。不需要通过主键再查一次
3. 覆盖索引只需要一次IO操作，回表需要两次IO操作

4. 

3、最左匹配：针对组合索引，从最左面开始匹配

4、索引下推：

1. 如果没有使用索引下推的情况下，使用非主键索引（普通索引or二级索引）进行查询，存储引擎通过索引检索数据，然后返回给MySQL服务器，服务器再进行判断是否符合条件
2. 使用索引下推的时候，MySQL会将这部分判断条件传递给存储引擎，然后存储引擎通过判断是否符合MySQL服务器传递条件，只有符合条件的数据才会将数据检索处理传递给MySQL服务器
3. 查看执行计划时发现extra一栏中有using index condition信息，说明使用了索引下推

六、索引的数据结构

B+Tree

索引为什么使用B+树结构？

hash的结构

缺点：

1. 利用hash存储的话，需要将所有 的数据文件添加到内存，比较耗费内存空间
2. 如果所有的查询都是等值查询，那么hash确实很快，但在实际工作中，范围查找的数据更多，而不是等值查询，因此hash就不适合了

链表结构

缺点：空间开销大，占用空间多，存储密度小，必须通过指针来访问，不能进行排序操作，查询比较慢

二叉树索引结构

BST树：

二叉搜索树（Binary Search Tree，简写BST），又称为二叉排序树，属于树的一种，通过二叉树将数据组织起来，树的每个节点都包含了键值key、数据值data、左子节点指针、右子节点指针。其中键值key是最核心的部分，它的值决定了树的组织形状；数据值data是该节点对应的数据

特点：

• 左右子树也分别是二叉搜索树
• 左子树的所有节点的key值都小于它的根节点的key值
• 右子树的所有节点的key值都大于它的根节点的key值
• 二叉搜索树可以为一颗空树
• 一般来说，树中的每个节点的key值都不相等，但根据需要也可以相同的key值插入树中

缺点：容易产生出现一个子树太深，查询太慢

平衡二叉树 AVL树

AVL树，也称平衡二叉树，AVL是其发明者姓名简写。AVL树属于树的一种，而且它也是一棵二叉搜索树，不同的是他通过一定的机制保证二叉搜索树的平衡，平衡的二叉搜索树的查询效率更高。

特点：

• AVL树是一棵二叉搜索树
• AVL树的左右子节点也是AVL树
• AVL树拥有二叉搜索树的所有基本特点
• 每个节点的左右子节点的高度之差的绝对值最多为1，即平衡因子的范围为[-1,1]

缺点：存在旋转的过程，增加了IO开销

红黑树（Red-black）

是一种自平衡二叉查找树，1972年由Rudolf Bayer发明，它与AVL树类似，都在插入和删除操作时通过旋转操作保持二叉查找树的平衡，以便能获得搞笑的查找性能，它可以在O（logn）时间内做查找，插入和删除等操作。红黑树是2-3-4树的一种等同，但有些红黑树设定只能左边是红薯，这种情况是2-3树的一种等同了。对于AVL树来说，红黑树牺牲了部分平衡性以换取插入/删除操作时少量的旋转操作，整体来说性能更优于AVL树。

特点：

1. 节点是红色或黑色
2. 根节点是黑色
3. 每个叶结点（NIL节点）是黑色的
4. 每个红色节点的两个子节点都为黑色，（从每个叶子到根的所有路径上不能有两个连续的红色节点）
5. 从任一节点到其叶子的所有路径都包含相同数目的黑色节点
6. 最长路径不超过最短路径的2倍

缺点：存在旋转染色的过程，增加IO开销

无论是二叉树、AVL树还是红黑树，都会因为树的深度过深，增加IO次数，影响数据读取的效率

BTree索引结构

特点：

1. 、所有键值分布在整颗树上
2. 、搜索有可能在非叶子节点结束，在关键字全集内做一次查询，性能逼近二分查找
3. 、每个节点最多拥有m个字树
4. 、根节点至少有两个字树
5. 、分支节点至少拥有m/2颗字树（除根节点和叶子节点都是子节点）
6. 、所有叶子节点都在同一层，每个节点最有可以有m-1个key，并且以升序排列

缺点：指针和数据存放在一起，一次IO查询的数据量太少

每个节点占用一个磁盘块，一个节点上有两个升序排序的关键字和三个指向字树根节点的指针，指针存储的是子节点所在磁盘块的地址，两个关键词划分成的三个范围域对应三个指针指向的字树的数据的范围域，以根节点为例，关键字为16和34，P1指针指向的字树的数据范围为小于16，P2指针指向的字树的数据范围为16-34，P3指针指向的字树的数据范围为大于34.

查找关键字过程：

1. 根据根节点找到磁盘块1，读入内存（磁盘I/O第一次操作）
2. 比较关键字28在区间（16,34），找到磁盘块1的指针P2
3. 根据P2指针找到磁盘块3，读入内存，（磁盘I/O第二次操作）
4. 比较关键字28在区间（25,31），找到磁盘块3的指针P2
5. 根据P2指针找到磁盘块8，读入内存，（磁盘I/O第三次操作）
6. 在磁盘块8中的关键字列表中找到关键字28

缺点：

1. 每个节点都有key，同时也包含data，而每个页存储空间是有限的，如果data比较大的话，会导致每个节点存储的key数量变小
2. 当存储的数据量很大的时候，会导致深度较大，增大查询磁盘io次数，进而影响查询性能。

B+Tree结构

B+Tree是在BTree的基础上做的一种优化，变化如下：

1. B+Tree每个节点可以包含更多的节点，这样做的原因有两个，第一个原因是为了降低树的高度，第二个原因是将数据范围变为多个区间，区间越多，数据检索越快
2. 非叶子节点存储key，叶子节点存储key和数据
3. 叶子节点两两指针相互连接（符合磁盘的预读特性），顺序查询性能更高

注意：在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构，因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

七、索引的匹配方式

先建一个表

create table staffs(

    id int primary key auto_increment,

    name varchar(24) not null default '' comment '姓名',

    age int not null default 0 comment '年龄',

    pos varchar(20) not null default '' comment '职位',

    add_time timestamp not null default current_timestamp comment '入职时间'

  ) charset utf8 comment '员工记录表';

建一个组合索引

alter table staffs add index idx_nap(name, age, pos);

新建一张表

create TABLE people(

last_name varchar(50) not null,

first_name varchar(50) not null,

dob date not null,

gender enum('m','f') not null,

key(last_name,first_name,dob));

1、全值匹配：和索引中的所有列进行匹配

explain select * from staffs where name=”july” and age=25 and pos=”dev”;

2、匹配最左前缀：只匹配前面的几列

explain select * from staffs where name=”july” and age=25;

expain select * from staffs where name=”july”;

3、匹配列前缀：可以匹配某一列的值的开头部分

explain select * from staffs where name like “J%”;

explain select * from staffs where name like “%J”;

4、范围匹配：可以查找某一范围的数据

explain select * from staffs where name>”Mary”;

5、精确匹配某一列并范围匹配另外一列：可以查询第一列的全部和第二列的部分

explain select * from staffs where name=”July” and age>25;

6、只访问索引的查询：查询的时候只访问索引，不需要访问数据行，本质上就是覆盖索引

explain select name,age,pos from name=”July” and age=25 and pos=”dev”;

因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的 ORDER BY操作（按照顺序查找）。一般来说，如果B-Tree可以按照某种方式查找到值，那么也可以按照这种方式用于排序。所以，如果ORDER BY字句满足前面列出的集中查询类型，则这个索引也可以满足对应的排序需求