仔细听!mysql深入浅出索引。

475 阅读6分钟

前言

纸上得来终觉浅,绝知此事要躬行。

描述

目前自己进入到林晓斌大佬的mysql实战45讲,相信很多人都看过,刚好看到04-05深入浅出索引,认为很重要,于是就做下笔记,大家也可以学下的同时我也做个笔记。

面试常见问题

  • 索引的常见模型
  • InnoDB 的索引模型
  • 回表
  • 是索引覆盖
  • 最左前缀原则
  • 索引下推

索引

百度百科解释

在关系数据库中,索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构,它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录,可以根据目录中的页码快速找到所需的内容。

通俗理解

我们上小学老师教我们用的新华字典,如果你要单独查一个字“周”,你是一页一页翻快还是直接查目录的z呢,答案肯定是查目录,那么索引跟这个也是这个意思。

索引的常见模型

哈希表

哈希表是一种以键 - 值(key-value)存储数据的结构,我们只要输入待查找的值即 key,就可以找到其对应的值即 Value。哈希的思路很简单,把值放在数组里,用一个哈希函数把 key 换算成一个确定的位置,然后把 value 放在数组的这个位置。 不可避免地,多个 key 值经过哈希函数的换算,会出现同一个值的情况。处理这种情况的一种方法是,拉出一个链表.

image.png

概述

如上述图中,它查询的规则是,打个比方,他先通过ID_card_n2算出来N,然后从N的链表中算出来对应的值,从链表中循环,注意,他不是有序的,所以,我们得出哈希表的结论。

哈希表作为索引的结论

哈希表只适合做单个等于的场景,并不适合区间的查找。

有序数组

有序数组是一种特殊的数组,里面的元素,按一定的顺序排列,我们这里假设由小到大排列

概述

有序数组可以弥补哈希表在区间上的不足,有序数组在等值查询和范围查询场景中的性能就都非常优秀,但它也并不是完美的,在更新数据和插入数据的成本太高。

有序数组作为索引的结论

有序数组索引只适用于静态存储引擎,有序数组适合的场景是在区间上的查找,并不适合在插入和更新频繁的场景使用。

搜索树

概述

即二叉查找树

image.png

特点

二叉搜索树的特点是:每个节点的左儿子小于父节点,父节点又小于右儿子。这样如果你要查ID_card_n2 的话,按照图中的搜索顺序就是按照 UserA -> UserC -> UserF -> User2 这个路径得到。这个时间复杂度是 O(log(N))。当然为了维持 O(log(N)) 的查询复杂度,你就需要保持这棵树是平衡二叉树。为了做这个保证,更新的时间复杂度也是 O(log(N)),索引不止存在内存中,还要写到磁盘上。

你可以想象一下一棵 100 万节点的平衡二叉树,树高 20。一次查询可能需要访问 20 个数据块。在机械硬盘时代,从磁盘随机读一个数据块需要 10 ms 左右的寻址时间。也就是说,对于一个 100 万行的表,如果使用二叉树来存储,单独访问一个行可能需要 20 个 10 ms 的时间,这个查询可真够慢的

结论

因为树高的关系,越高可能会影响效率,所以二叉查找树也并不适合

InnoDB 的索引模型

InnoDB采用的是B+树,在 InnoDB 中,表都是根据主键顺序以索引的形式存放的,这种存储方式的表称为索引组织表。B+ 树能够很好地配合磁盘的读写特性,减少单次查询的磁盘访问次数,大家可以百度看下B+树的原理,这里不做概述了。

回表

概述

回到主键索引树的过程,我们称为回表。

如何避免回表过程

文章中说的是索引覆盖,那么索引覆盖怎么使用呢。

索引覆盖

如果执行的语句是 select ID from T where k between 3 and 5,这时只需要查 ID 的值,而ID 的值已经在 k 索引树上了,因此可以直接提供查询结果,不需要回表。也就是说,在这个查询里面,索引 k 已经“覆盖了”我们的查询需求,我们称为覆盖索引。由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。

我对索引覆盖的理解

这里我认为是k本来就加了个索引的情况下,你的ID是主键索引,也就是说索引覆盖需要有ID。因为索引覆盖整个就是对回表的减少。

最左前缀原则

使用的话,打个比方一个联合索引(a,b,c) 当我查询的时候,从最左边开始命中,比如说a,ab,abc这样命中。

联合主键索引

其实是跟联合索引是一个道理,大家可以去看看联合索引,命中规则也是遵循最左前缀原则

索引下推

在 MySQL 5.6 之前,只能从 ID3 开始一个个回表。到主键索引上找出数据行,再对比字段值。而 MySQL 5.6 引入的索引下推优化(index condition pushdown), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。

image.png

image.png

老师的概述

图三第一张图,图四第二张图每一个虚线箭头表示回表一次.图 3 中,在 (name,age) 索引里面我特意去掉了 age 的值,这个过程 InnoDB 并不会去看 age的值,只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此,需要回表 4次。

图 4 跟图 3 的区别是,InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10,对于不等于 10 的记录,直接判断并跳过。在我们的这个例子中,只需要对 ID4、ID5 这两条记录回表取数据判断,就只需要回表 2 次。

老师的理解

在 MySQL 5.6 之前,只能从 ID3 开始一个个回表。到主键索引上找出数据行,再对比字段值。而 MySQL 5.6 引入的索引下推优化(index condition pushdown), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。

结尾

mysql实战总共是45讲,我会写几篇也说不定,加油吧,如果有错误和不正的可以指出来,大伙可以给我点点赞。