MYSQL索引【学习总结】

目录

什么是索引？

索引底层数据结构

Hash表

B树

1、B树的定义

2、B树的检索、插入和删除

B+树

1、B+树的定义

2、B+树与B树的差异

3、B+树的检索插入和删除

B+树与B树

B+树相较于B树优点

MyISAM引擎和InnoDB引擎

索引类型

主键索引

二级索引（辅助索引）

聚集索引与非聚集索引

聚集索引

非聚集索引

覆盖索引

联合索引（组合索引、复合索引）

最左前缀匹配原则

索引下推

创建索引的注意事项

使用索引的一些建议

索引失效的场景

引用

---

什么是索引？

索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树， B+树和 Hash。

优点：

• 使用索引可以大大加快 数据的检索速度（大大减少检索的数据量）, 这也是创建索引的最主要的原因。
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

缺点：

• 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
• 索引需要使用物理文件存储，也会耗费一定空间。

大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。

索引提高查询效率原理

索引之所以能提高查询效率，主要是因为它减少了数据库的IO次数。

操作系统对于磁盘IO做了一定的优化。根据局部预读性原理，一个地址的数据被访问到，那么相邻的数据也很快会被访问到。所以一般IO操作的时候是一页一页的形式来读取的，32位操作系统一页就是32bit，即4K（64位操作系统就是8K）。

由于使用了B+树来实现索引，所以在查询的时候很轻易得知应该从何处查找，可以减少传统的逐行扫描而频繁IO造成的效率低下。

索引底层数据结构

Hash表

哈希表是键值对的集合，通过键(key)即可快速取出对应的值(value)，因此哈希表可以快速检索数据(接近 O(1))。

通过哈希算法通过key快速取出value

hash = hashfunc(key)
index = hash % array_size

存在哈希冲突，通常情况下，我们常用的解决办法是 链地址法。链地址法就是将哈希冲突数据存放在链表中。就比如 JDK1.8 之前 HashMap 就是通过链地址法来解决哈希冲突的。不过，JDK1.8 以后HashMap为了减少链表过长的时候搜索时间过长引入了红黑树。（为了减少 Hash 冲突的发生，一个好的哈希函数应该“均匀地”将数据分布在整个可能的哈希值集合中。）

为什么MySQL不使用Hash表作为索引的数据结构呢？

1、Hash冲突问题

2、Hash索引不支持顺序和范围查询（最大缺点）

B树

B树又称B-树，B的意思为平衡 Balance

1、B树的定义

一颗m阶的B树满足如下条件：

• 每个节点最多只有m个子节点。
• 除根节点外，每个非叶子节点具有至少有 m/2（向下取整）个子节点。
• 非叶子节点的根节点至少有两个子节点。
• 有k颗子树的非叶节点有k-1个键，键按照递增顺序排列。（非叶子节点的那一节点有k-1个键，他就有k棵子树）
• 叶节点都在同一层中。

B树的阶：

B树中一个节点的子节点数目的最大值，用m表示，假如最大值为4，则为4阶。

B树的出现是为了弥补不同的存储级别之间的访问速度上的巨大差异，实现高效的 I/O。

平衡二叉树的查找效率是非常高的，并可以通过降低树的深度来提高查找的效率。

但是当数据量非常大，树的存储的元素数量是有限的，这样会导致二叉查找树结构由于树的深度过大而造成磁盘I/O读写过于频繁，进而导致查询效率低下。另外数据量过大会导致内存空间不够容纳平衡二叉树所有结点的情况。B树是解决这个问题的很好的结构。

2、B树的检索、插入和删除

1）检索：

根据要查找的关键码key，在根节点的关键码集合中进行顺序或二分法检索，若key = ki，则检索成功；
否则，key一定在某 ki 和 ki+1 之间，用一个指针在所指节点继续查找，重复上述检索过程，直到检索成功；或指针为空，则检索失败。
整个检索过程中访外次数与B树的高度成正比。

2）插入：

针对m阶高度h的B树，插入一个元素时，首先在B树中是否存在，如果不存在，即在叶子结点处结束，然后在叶子结点中插入该新的元素。

• 若该节点元素个数小于m-1，直接插入；
• 若该节点元素个数等于m-1，引起节点分裂；以该节点中间元素为分界，取中间元素（偶数个数，中间两个随机选取）插入到父节点中；
• 重复上面动作，直到所有节点符合B树的规则；最坏的情况一直分裂到根节点，生成新的根节点，高度增加1。

3）删除：

首先查找B树中需删除的元素,如果该元素在B树中存在，则将该元素在其结点中进行删除；删除该元素后，首先判断该元素是否有左右孩子结点，如果有，则上移孩子结点中的某相近元素(“左孩子最右边的节点”或“右孩子最左边的节点”)到父节点中，然后是移动之后的情况；如果没有，直接删除。

• 某结点中元素数目小于（m/2）-1,(m/2)向上取整，则需要看其某相邻兄弟结点是否丰满；
• 如果丰满（结点中元素个数大于(m/2)-1），则向父节点借一个元素来满足条件；
• 如果其相邻兄弟都不丰满，即其结点数目等于(m/2)-1，则该结点与其相邻的某一兄弟结点进行“合并”成一个结点；

B+树

B树的变种

1、B+树的定义

一颗m阶的B+树满足如下条件：

• 每个节点最多只有m个子节点。
• 除根节点外，每个非叶子节点具有至少有 m/2（向下取整）个子节点。
• 非叶子节点的根节点至少有两个子节点。
• 有k颗子树的非叶节点有k个键，键按照递增顺序排列。（非叶子节点的那一节点有k个键，他就有k棵子树）
• 叶节点都在同一层中。

说明：
每个叶节点中至少包含 m/2（向下取整）个关键码，所有主文件记录的索引项都存放在B+树的叶节点中。所有内部节点都看成是索引的索引。节点中仅包含它的各个子节点中最大（或最小）关键码的分界值以及指向子节点的指针。

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

2、B+树与B树的差异

B+树	B树
有m颗子树的节点中含有 m 个关键码	有m颗子树的节点中含有 m-1 个关键码
所有的叶子结点中包含了完整的索引信息，包括指向含有这些关键字记录的指针，中间节点每个元素不保存数据，只用来索引	B树中非叶子节点的关键码与叶子结点的关键码均不重复，它们共同构成全部的索引信息
所有的非叶子节点可以看成是高层索引， 结点中仅含有其子树根结点中最大（或最小）关键字   （所有关键码都存放在叶节点中，上层的非叶节点的关键码是其子树中最小（或最大）关键码的复写）	B 树的非叶子节点包含需要查找的有效信息

3、B+树的检索插入和删除

1）检索
在B+树中检索关键码key的方法与B树的检索方式相似，但若在内部节点中找到检索的关键码时，检索并不会结束，要继续找到B+树的叶子结点为止。

2）插入
与B树的插入操作相似，总是插到叶子结点上。当叶节点中原关键码的个数等于m时，该节点分裂成两个节点，分别使关键码的个数为 (m+1)/2 （向上取整）和 (m+1)/2 （向下取整）。

3）删除
仅在叶节点删除关键码。若因为删除操作使得节点中关键码数少于 m/2（向下取整）时，则需要调整或者和兄弟节点合并。合并的过程和B树类似，区别是父节点中作为分界的关键码不放入合并后的节点中。

B+树与B树

B+树相较于B树优点

1）B+树的磁盘读写代价更低

B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了；

2）B+树查询效率更加稳定

由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当；

3）B+树便于范围查询（最重要的原因，范围查找是数据库的常态）

B树在提高了IO性能的同时并没有解决元素遍历效率低下的问题，正是为了解决这个问题，B+树应用而生。B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作或者说效率太低。

补充：B树的范围查找用的是中序遍历，而B+树用的是在链表上遍历。

MyISAM引擎和InnoDB引擎

在 MySQL 中，MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是，两者的实现方式不太一样。

MyISAM 引擎中，B+Tree 叶节点的 data 域存放的是数据记录的地址。在索引检索的时候，首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。

InnoDB 引擎中，其数据文件本身就是索引文件。相比 MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”，而其余的索引都作为辅助索引，辅助索引的 data 域存储相应记录主键的值而不是地址，这也是和 MyISAM 不同的地方。在根据主索引搜索时，直接找到 key 所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

索引类型

主键索引

数据表主键就是主键索引，一张数据表有只能有一个主键，并且主键不能为 null，不能重复。

对于使用自增主键和UUID作为主键，这两种情况下，UUID的效率相较于主键自增更加低下。自增主键表示了主键的添加顺序，所以在磁盘上通常相邻的行就是物理上相邻的；而UUID因为生成的数据不确定性，访问的时候要频繁移动磁盘块，所以效率相较更低。

二级索引（辅助索引）

二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。

唯一索引，普通索引，前缀索引等索引属于二级索引。

1. 唯一索引(Unique Key) ：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
2. 普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
3. 前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。
4. 全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

聚集索引与非聚集索引

聚集索引

聚集索引即索引结构和数据一起存放的索引，主键索引属于聚集索引。因为一个数据表的数据行的物理顺序唯一，所以一个数据表只能有一个聚簇索引。

在 MySQL 中，InnoDB 引擎的表的 .ibd文件就包含了该表的索引和数据，对于 InnoDB 引擎表来说，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

聚集索引的优点：查询速度非常快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。

聚集索引的缺点：

1. 依赖于有序的数据 ：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
2. 更新代价大 ： 如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚集索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的。

非聚集索引

非聚集索引即索引结构和数据分开存放的索引。

二级索引属于非聚集索引。

非聚集索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

非聚集索引的优点：更新代价比聚集索引要小 。非聚集索引的更新代价就没有聚集索引那么大了，非聚集索引的叶子节点是不存放数据的

非聚集索引的缺点：

1. 跟聚集索引一样，非聚集索引也依赖于有序的数据
2. 可能会二次查询(回表)：这应该是非聚集索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

MYSQL表文件：

InnoDB    .ibd文件包含索引和数据

MYISAM   .MYD文件包含表的数据 

                 .MYD文件包含表的索引

注意：非聚集索引不一定需要回表查询，比如，查询的数据正好建立了索引且被查的刚好是key

MYISAM的主键索引的叶子结点存放的是指针，所以它确实需要回表，但是如果查的就是主键，那查到返回就行了，这种情况称之为覆盖索引。

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。

覆盖索引是一种数据查询方式,不是索引类型。

我们知道在 InnoDB 存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢。覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！ 

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了， 而无需回表查询。

如主键索引，如果一条 SQL 需要查询主键，那么正好根据主键索引就可以查到主键。

再如普通索引，如果一条 SQL 需要查询 name，name 字段正好有索引， 那么直接根据这个索引就可以查到数据，也无需回表。

联合索引（组合索引、复合索引）

使用表中的多个字段创建索引。

最左前缀匹配原则

指在使用联合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询，如>、<、between和以%开头的like查询等条件，才会停止匹配。 

所以，我们在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据。

使用例子来理解，如下图。

假如：构建一个(a,b)的联合索引，那么它在数据库底层的索引树是下列这样的：

可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。所以b = 2这种查询条件没有办法利用索引，因为联合索引首先是按a排序的，b是无序的。

同时我们还可以发现在a值相等的情况下，b值又是按顺序排列的，但是这种顺序是相对的。所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引。例如a = 1 and b = 2 a,b字段都可以使用索引，因为在a值确定的情况下b是相对有序的，而a>1and b=2，a字段可以匹配上索引，但b值不可以，因为a的值是一个范围，在这个范围中b是无序的。

最左匹配原则适用场景：

假如建立联合索引(a,b,c)

1、全职匹配查询

select * from table_name where a = '1' and b = '2' and c = '3' 
select * from table_name where b = '2' and a = '1' and c = '3' 
select * from table_name where c = '3' and b = '2' and a = '1' 

2、匹配左边的列时

select * from table_name where a = '1' 
select * from table_name where a = '1' and b = '2'  
select * from table_name where a = '1' and b = '2' and c = '3'

都是从最左边开始连续匹配，用到了索引，没有从左边开始的，最后查询没有用到索引，而是全表扫描。如果不连续（查询a和c），就只用到了a列的索引，b列和c列的没有用到。

3、匹配列前缀

如果列是字符型的话它的比较规则是先比较字符串的第一个字符，第一个字符小的哪个字符串就比较小，如果两个字符串第一个字符相通，那就再比较第二个字符，第二个字符比较小的那个字符串就比较小，依次类推，比较字符串。

select * from table_name where a like 'As%'; //前缀都是排好序的，走索引查询
select * from table_name where  a like '%As' //全表查询
select * from table_name where  a like '%As%'//全表查询

4、匹配范围值

select * from table_name where  a > 1 and a < 3

可以对最左边的列进行范围查询，但是多个列同时进行范围查询时，只有对索引最左边的那个列进行范围查找才用到B+树索引，也就是只有a用到索引。

5、精准匹配某一列并范围匹配另外一列

select * from table_name where  a = 1 and b > 3;

如果左边的列是精确查找的，右边的列可以进行范围查找

6、排序

一般情况下，我们只能把记录加载到内存中，再用一些排序算法，比如快速排序，归并排序等在内存中对这些记录进行排序，有时候查询的结果集太大不能在内存中进行排序的话，还可能暂时借助磁盘空间存放中间结果，排序操作完成后再把排好序的结果返回客户端。

Mysql中把这种再内存中或磁盘上进行排序的方式统称为文件排序。文件排序非常慢，但如果order子句用到了索引列，就有可能省去文件排序的步骤

select * from table_name order by a,b,c limit 10;

这是因为B+树索引本身就是按照上述规则排序的，所以可以直接从索引中提取数据，然后进行回表操作取出该索引中不包含的列就好了

order by的子句后面的顺序也必须按照索引列的顺序给出，如果顺序颠倒则没有用到索引。没有颠倒但不全则为用到部分索引。

假如联合索引左边列为常量，后边的列排序可以用到索引

select * from table_name where a =1 order by b,c limit 10;

索引下推

索引下推是 MySQL 5.6 版本中提供的一项索引优化功能，可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。

创建索引的注意事项

1.选择合适的字段创建索引：

• 不为 NULL 的字段 ：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
• 被频繁查询的字段 ：我们创建索引的字段应该是查询操作非常频繁的字段。
• 被作为条件查询的字段 ：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
• 频繁需要排序的字段 ：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
• 被经常频繁用于连接的字段 ：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

2.被频繁更新的字段应该慎重建立索引。

虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

3.尽可能的考虑建立联合索引而不是单列索引。

因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

4.注意避免冗余索引 。

冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

5.考虑在字符串类型的字段上使用前缀索引代替普通索引。

前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。

使用索引的一些建议

• 对于中到大型表索引都是非常有效的，但是特大型表的话维护开销会很大，不适合建索引
• 避免 where 子句中对字段施加函数，这会造成无法命中索引。
• 在使用 InnoDB 时使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键。
• 删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗 MySQL 5.7 可以通过查询 sys 库的 schema_unused_indexes 视图来查询哪些索引从未被使用
• 在使用 limit offset 查询缓慢时，可以借助索引来提高性能

索引失效的场景

• 没有查询条件，或者在查询条件上没有建立索引或者使用引导列

• 索引字段中使用了运算或者函数，或者使用is null、is not null、!=、<>

• or语句中前后字段没有同时都为索引

• 数据类型出现隐式转化， 例如字符串比较没有使用单引号

• 查询结果大于全表的30%（会走全表扫描）

• 复合索引中没有遵循最左前缀原则

• 使用非后置Like通配符，如Like ”%02“不会生效，%只有写在最后面才会索引生效

引用

MySQL之详解索引

MYSQL索引详解

B树和B+树详解

【大话Mysql面试】-如何通俗易懂的了解Mysql的索引最左前缀匹配原则