MySQL索引分析扩展理解

MySQL索引分析扩展理解

看了很多网上的一些资料，感觉MySQL的索引知识还是很多的，这一篇文章就用来自己对MySQL索引的其他理解和以前模糊的地方的一些总结。

1、MySQL的常见索引模型

有序数组

• 我们在查找数据的时候，就可以通过IDCard这一列，在数据库表中进行二分查找，二分查找的时间复杂度为O(logn)，这是非常快的。
• 由于数据是有序存放的，所以支持范围查找，只要找到起始值然后往后扫描判断，就可以检索出范围内的值。
• 但是使用有序数组的情况，如果是插入或者删除数据，就会非常的麻烦。可以想象，插入数据需要将后半部分数据往后挪动一个位置，

哈希表

我们指定一个列为索引，然后将这个列的值作为key，将数据放到哈希表其中一个bucket（桶位）中，如下所示：

我们可能会好奇，如何在文件中组织哈希表呢？

• 其实主要的问题是在文件中怎么表示指针，指针是指向一个地址的，在文件中这个地址是由数据在文件中的偏移量表示，弄清楚这个以后，我们来看一下哈希表组织索引的特点
• 通过IDCard通过哈希算法计算出一个特定值，然后存储地址，这样在你找数据的时候直接可以通过IDCard去找了，当然也会有这种情况，就是两个元素选中了相同的空间，我们通常会引出一个链表去存储。
• 哈希表是无序的，所以我们根据哈希表的特性可以得到无法利用索引进行范围查询，只能利用索引来进行等值查询。

二叉搜索树

对于平衡二叉树来说，查找的时间复杂度为O(logn)，所以适合查找，并且二叉搜索树是有序的，所以也支持范围查找。二叉搜索树的相关总结稍后会更新。

• 二叉搜索树是存在于磁盘中，每次我们都要从磁盘中读取出相应的节点，然而二叉搜索树的节点在文件中是随机存放的，所以可能读取一个节点就需要一个磁盘IO，恰恰二叉搜索树都会比较高，如果一颗一百万个元素的平衡二叉树就有十几层的高度了，也就是大部分情况下检索一次数据就需要十几次磁盘IO，这个代价是相当高的吗，所以这就是二叉搜索树不会被用来做索引的原因。
• 其实用二叉搜索树作为索引，从功能来说已经非常优秀了，我们可以改进它，其实就是降低树的高度以减少检索数据的时候磁盘IO的次数，这就可以使用多路搜索树，B-树和B+树应用而生，当每个节点的分支多了，这棵树的高度自然就降低了，每次需要读取的磁盘IO也就减少了。

对于多路搜索树（B-树、B+树）作为索引结构的内容可以参考我的这篇博客：B-树、B+树详解

看完了上面的，会知道MySQL默认使用的是B+树数据结构作为底层的索引结构

那么我们知道了单列索引的存储结构，下面就来了解一下MySQL建立复合索引的时候底层的存储结构和查询的时候如何使用索引的呢？

2、复合（联合）索引的索引结构

假设建立了复合索引（b，c，d），在索引树树种的样子如图，在比较的过程中，先判断b在判断c然后是d

首先，表T1有字段a，b，c，d，e，其中a是主键（聚簇索引），除了e为varchar类型其余均为int类型，并创建了一个复合索引idx_t1_bcd（a，b，c，d），然后b、c、d三列作为复合索引，在B+树上的结构正如上图所示。复合索引的所有索引列都出现在索引树上，并依次比较三列值得大小。上图树高只有两层不容易理解，下面是假设得表数据以及我对其复合索引在B+树上得结构图得改进。PS：基于InnoDB引擎。

T1表中得数据如下：

我们先看T1表，它得主键我们定为的是整形自增的（PS：至于为什么是整型自增请参考我的这篇文章：MySQL常见面试题之存储引擎 ，InnoDB会使用主键（聚簇索引）B+树在磁盘中维护索引和数据文件，然后我们创建了一个复合索引（b，c，d）也会生成一个索引树，同样是B+树的结构，只不过复合索引是一个辅助索引，内部存储的不是数据文件，而是复合索引该行的主键值（为了回表查询数据），至于不了解什么是辅助索引和回表查询的在上面的链接中也有详细叙述。

下面我们结合上面两张图来介绍一下复合索引的存储结构：

对于复合索引来说只不过是比单列索引多了几列，而这些索引列全部都出现在索引树上。对于复合索引，存储引擎会首先根据第一个索引列进行排序，如上图中我们可以单看第一个索引列，如：1 1 5 12 13...它是单调递增的（可以看出是有序的），如果第一列相等以后则再根据第二列排序，依次类推就构成了上图中的索引树。

3、复合索引的查找方式

我们以这条SQL语句（可以用到索引）为例来讲解：select * from T1 where b=12 and c=14 and c=13

InnoDB存储引擎首先从根节点（一般常驻于内存）开始查找，第一个索引的第一个索引列为1，12大于1，第二个索引列的第一个索引列为56，12小于56，于是从这两个索引的中间读到下一个节点的磁盘文件地址，从磁盘上Load这个节点，通常伴随着一次磁盘IO操作，然后将索引文件加载到内存中，存储引擎再去内存里去查找。当Load叶子节点的第二个节点的时候又是一次磁盘IO，比较第一个元素，b=12，c=14，d=3完全复合，于是找到该索引下的data元素即ID值，再从主键索引树上找到最终数据行。

4、最左前缀匹配原则（面试内容）

了解了上面这些，我们就可以去好好看看MySQL索引的最左前缀匹配原则了。

什么是最左前缀匹配原则：

在MySQL建立复合索引的时候会遵守最左前缀匹配原则，即最左优先，在检索数据的时候从复合索引的最左边开始匹配。带头大哥不能丢，中间兄弟不能断。

explain关键字：主要是用来查询解析计划（因为MySQL内部执行过程跟DBA写的sql语句的执行顺序可能不一样，类可以使用explain来查看MySQL的执行过程）

那接下来主要了解下explain执行计划中的type字段和key_len字段

• type：代表查询所使用索引的级别，下面给出各种查询级别，按照从最佳到最坏类型排序：（重点看ref，range，index）即可

  • system：表中只有一行记录（等于系统表），这是const类型的特例，很少出现，因为当表中数据量较少的时候不建议使用索引。
  • const：表示通过一次索引就找到，const用于比较primary key或者unique索引。因为只需要匹配一行数据，所以很快，如果将主键置于where列表中，MySQL就能将该查询转换为一个const
  • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或者唯一性索引。
  • ref：非唯一性索引扫描，返回匹配某个单独值得所有行，本质上也是一种索引访问，它活泛匹配某个值的行，然而它可能会找到多个复合条件的行，所以它应该属于查找和扫描的混合体，主要出现于单值索引和复合索引中。
  • range：只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是where语句出现了between、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始某个点，结束于另一个点，不用扫描全部索引。
  • index：Full Index Scan（全索引扫描），index与All的区别为index类型只遍历索引树，这通常为All块，注意：在InnoDB中，主键对应的索引文件本身就是其数据文件。辅助索引的索引文件通常比数据文件小。
  • ALL：Full Table Scan，遍历磁盘中的数据文件以找到匹配的行。

• key_len：显示MySQL实际使用的索引的长度，如果索引是NULL（即没有使用索引），则长度为NULL，如果不是NULL，则为使用索引的长度。通过计算此字段就可以知道使用了哪个索引。
  这里推荐一篇博文：MySQL key_len 大小的计算

示例：

首先创建一个表：

create table staffs1(
	id int(10) primary key,
    uid int(10),
    name char(10),
    age int(10),
    key uid_name_age_index(uid,name,age)
)engine=InnoDB default charset=utf8

该表中对uid列，name列，age列建立了一个复合索引uid_name_age_index，实际上相当于建立了三个索引（uid），(uid_name)，（uid_name_age）

下面介绍一下可能会使用到该索引的几种情况：

1、全值匹配查询的时候
通过观察上面的结果图可知，where后面的查询条件复合索引中的列全部出现的时候，不论怎么样的调换索引列的位置，在查询的时候都会使用到复合索引。

可能有同学会疑惑，为什么底下两个的搜索条件明明没有按照复合索引从左到右进行匹配，却也使用了复合索引？

这是因为MySQL中有查询优化器explain，所以sql语句中字段的顺序不需要和复合索引定义的字段顺序相同，查询优化器会判断并纠正这条sql语句以什么样的顺序执行效率最高，最后才能生成真正的执行计划，索引，不论以何种顺序都会使用到复合索引（前提是索引列全部出现在where条件后），另外通过计算key_len的值也可以得出使用了复合索引，（计算过程看上面推荐的博客即可明白怎么计算）。

该搜索是遵循最左匹配原则的，通过key字段可知，在搜索过程中使用到了联合索引，且使用的是复合索引中的uid索引，因为key_len中字段值为5，而uid索引字段为int且可为null，所以长度正好也为5。

由于uid到name是从左边到右边依次往右边匹配，这两个字段中的值都是有序的，所以也遵循最左匹配原则，通过key字段可知，在搜索过程中使用到了复合索引，但使用的是复合索引中的（uid_name）索引，因为 key_len字段值为 36,我们计算一下：uid是int且为null所以是5，name是定长的char(10)且可以为null，而因为使用的是utf8编码，所以是10*3+1=31，所以一共是36。，这里使用双列索引进行查询运用到了索引下推的原则，下面会讲解什么是索引下推？

那如果不是依次匹配的呢？
通过key字段可以知道，在搜索过程中也使用到了复合索引，但使用的是复合索引中的（uid）索引，从key_len字段也知道，因为索引树是按照uid创建的，但是age相对uid来说是无序的，只有uid是有序的，只有uid是有序的，所以它只能使用复合索引中的uid索引。

对于复合索引的最左前缀原则就介绍到这里，还有一些索引失效的情况，这里只做一些总结，不再举例子。

• 带头大哥不能丢，要不然无法使用索引
• 在索引列上做任何操作（计算 函数（自动or手动）类型转换）会导致索引失效
• 索引列中范围条件右边的列会失效（注意：范围本身这个列不失效）
• 使用不等于（!= 或者<>）的时候会导致索引失效（注意：在MySQL8.0以上版本划为了range，不再失效）
• is null 或者 is not null 也会导致索引失效
• like以通配符开头的也会导致索引失效

这里再介绍一下使用复合索引Extra字段出现了Using Index中使用了覆盖索引的情况

5、覆盖索引

对于覆盖索引，有三种理解：

• 解释一：就是select的数据列只用从索引中就能够获取，不必再回表查询数据，换句话说查询列要被所用的索引覆盖。
• 解释二：索引就是高效找到行的一个方法，当能通过检索索引就可以读取获取想要的数据，那就不需要再到聚簇索引中去获取行数据了。如果一个索引包含了（或者覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引
• 解释三：是非聚簇索引的一种形式，它包括在查询里面的select、join和where子句用到的所有列（即建立索引的字段正好是覆盖查询语句，select子句与查询条件where子句中所涉及的字段，也即索引包含了查询正在查找的所有数据）。

不是所有的类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B+树索引做覆盖索引。

举个例子，来看看使用覆盖索引的情况：

Extra：Using index表示没有回表查询数据的过程，实现了覆盖索引。

总结：覆盖索引的优化及限制

覆盖优化是一种非常强大的工具，能大大的提高查询性能，只需要读取索引而不需要读取数据，有以下几个有点：

• 索引项通常比数据记录要小，所以MySQL访问更少的数据。
• 索引都按值得大小存储，相对于随机访问记录，需要更少的IO。
• 数据引擎能更好的缓存索引，比如MyISAM值缓存索引。
• 覆盖索引对InnoDB尤其有用，因为InnoDB使用聚簇索引组织数据，如果二级索引包含查询所需要的数据，那么就不需要再聚簇索引中查找行数据了。

限制：

• 覆盖索引也并不使用于任意的索引类型，索引必须存储列的值
• Hash和Full-Text索引不存储值，因此MySQL只能使用B+树来作为索引结构
• 如果要使用覆盖索引，一定要注意select列表值取出需要的列，不可以select * ，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。
  聊完了这么多，再看看最后的索引下推原则：
  
  分析一下这个语句的执行过程，这个语句再搜索索引树的时候，只能得到第一条满足uid=1的记录，然后找到该记录的主键id值去聚簇索引中去得到行数据，然后去判断其他条件是否满足。

在MySQL5.6之前，只能从uid=1的对应的主键值开始一个个回表查询，到聚簇索引上找出数据行，在对比字段值。

分析一下这个语句的执行过程，这个语句再搜索索引树的时候，只能得到第一条满足uid=1的记录，然后找到该记录的主键id值去聚簇索引中去得到行数据，然后去判断其他条件是否满足。

在MySQL5.6之前，只能从uid=1的对应的主键值开始一个个回表查询，到聚簇索引上找出数据行，在对比字段值。

而MySQL5.6引入的索引下推优化原则，可以直接在索引遍历的过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表的次数。