Mysql Index 索引原理

434 阅读8分钟

前言

在日常工作中经常需要接触到mysql数据库,为了可以更好的使用mysql数据库,并开始研究其深入一点的知识,能够让自己在工作的环境中应用好mysql数据库,优化其sql,了解其锁的原理、事务、mvvc版本控制的原理。在之后的文章中都会将其一一总结。

在此文章中,我会从索引出发,探讨一下为什么mysql命中index之后的效率可以高那么多。以及我们应该如何使用mysql的index,在使用的时候需要注意一些什么。

搜索算法

为了可以更好的理解b+树原理,在此需要先讲解一下其算法。循循渐进的去理解b+树如何做搜索。

  • 二分查找 这个算法能让我们快速理解树的数据结构树如何进行查找的,假设我们有

    1,2,3,4,5,6,7,8,9,10 十个数字,我想找到5这个数字的次数是多少? 那肯定就是找5次嘛。因为是顺序查找。那什么是二分查找呢?

二分查找是指每次的查找都从数据的中间部分开始划分,把整体数据1分为2 比如有10个数子。我们就从下标为5的开始查找

1,2,3,4,5,6,7,8,9,10
        ^

这样的话,我们运气非常好 只需要一次查询就能找到这个数字了。那如果是查找9个数字呢?

1,2,3,4,5,6,7,8,9,10
        ^
1,2,3,4,5,6,7,8,9,10
              ^
1,2,3,4,5,6,7,8,9,10
                ^

这样我们只需要需要3次就能找到9这个数字了。

数据结构

对于普通的树而言,只需要达到一个节点其下方只有左右2个叶子节点则可,但却没有任何的限制让你去放叶子节点。也就是说,你可以放成这些样子:

1       |    1     |    3
 \      |   / \    |   / \
  2     |  5   3   |  1   2
   \    |       \  |       
    3   |        2 |        

那这种就是最普通的树结构,在此不展开详细的探讨这个数据结构的优缺点。应该大家都说看得出这种随意性比较高的数据结构会带来很多的麻烦以及规范的问题。当树的层数越高,那么搜索的io就越高,时间也就需要越久。

平衡二叉树

然后我就衍生出了其他的树,例如这一个 平衡二叉树。平衡二叉树有几个规则是需要满足的:

  • 左子树的值总是小于根节点
  • 右子树的值总是大于根节点 image.png 平衡二叉树的搜索结果树肯定要比顺序查找要快的。 如何同样是10个节点。那么顺序查找次数则为:

(1+2+3+...10)/10 = 5.5次

那如果是二叉树的搜索结果是多少呢?

(4+4+4+3+3+3+3+2+2+1)/10 = 2.9次

很明显,在平衡二叉树的搜索下,效率是提高了将近1倍的速度。那么平衡二叉树的性能都那么好了? 那直接用他作为mysql的数据结构不就可以了? 为什么还有b+树的出现呢?

我们再想深一层次,平衡二叉树到底有什么缺点?

  • 插入的代价高 想想我们之前说的一个原理。平衡二叉树必须要保重他们的左右节点都要小于大于根节点。那问题就来了,我每次插入的时候我就必须要对树做一个旋转才能够保证这一个特性吖。因此每一次的插入操作其实都是 寻找+旋转的操作 这就导致了维护一刻平衡二叉搜索树的成本非常大。

image.gif 在上图中我们可以看到每个节点的插入 其实都是一次搜索+旋转子树。当树的高度越高的时候,也就意味着我们维护这一颗树的代价越大。因此才会有我们今天的主角,b+树的出现

b+树

到了我们今天要讲到主角,b+树, b+树于平衡二叉树都是非常经典的数据结构,b+树也是由b树的索引顺序访问演化而来。

b+树的定义为:

  • 非叶子节点只存放其键值
  • 叶子节点存放数据

而b+树的设计就是为了磁盘或者其他可直接存取的设备而专门设计的一种数据结构,b+树的所有节点都是按照键值的大小排好顺序存放在同一层的节点上 image.png 毕竟这一次的主要目的还是讲解索引,因此在这里埋一个点。下一次会着重分享 b+树的数据结构、插入、删除节点的操作以及当叶子节点和中间节点都满了的情况下,innodb如何进行拆分和申请更多的页

image.gif 上图所示就是当叶子节点数为3的时候。b+树的插入和删除操作

b+树索引种类

聚集索引

在innodb中,每一个表都会拥有一个索引,而聚集索引就是按照表的主键去构造出来的一颗b+树,同时这些叶子节点就是这张表的数据。

因为数据页只可以按照一颗b+树进行排序,因此每张表页就只有一个聚集索引。而大部分情况下查询优化其都会优先选择聚集索引主要查找,因为聚集索引能够直接找到叶子节点上的数据。无需再进行二次回表

聚集索引有以下几种情况会被创建:

  • 表中有定义主键,innodb会采用该主键作为聚集索引
  • 没有主键,innodb会选一个非空的唯一索引作为聚集索引
  • 以上都没有, innodb则会创建一个隐式的聚集索引(_rowid)

在此需要注意一个点。我们所说的数据存储是顺序的意思是指,在逻辑上是有序的,并不是在实际物理存储上的有序,因此数据之间都是用双向链表进行关联。

image.png

辅助索引

辅助索引指是非聚集索引,也就是说除了集聚索引意外的索引,都可以称之为辅助索引。辅助索引的叶子节点并不包含行的数据,而是存放着主键的键值,每张表是可以存放多个辅助索引的,因此使用辅助索引来查找数据的话应对的就需要进行两步操作。

  • 第一步通过辅助索引找到主键索引
  • 第二步通过主键索引找到叶子节点的数据 这就是我们一直说的回表查询的意思

image.png 上方就是我们辅助索引的数据结构。在这边讲到了辅助索引后,则需要更深入的带出联合索引以及索引覆盖的意思

联合索引

我们可以从辅助索引的图中看到存储数据的时候,我们都是排好顺序作为存储的。假设我们有索引(a,b) 则上方都是按照(1,1),(1,2),(2,1),(2,4),(3,1)的顺序进行存放,以加快查询的速度。

当你使用查询语句

select * from table where a=? and b=?

则可以用到(a,b)索引进行加速查询,也正是因为有最左原则存在,因此where条件的顺序必须跟索引的顺序一致才能命中索引。 也就是说当你使用

select * from table where b=?

的时候则无法使用到(a,b)索引,因为在b+树上b的数据(1,2,1,4,1)很明显就不是按照顺序排序的。

覆盖索引

索引覆盖的原理很简单,主要是讲从辅助索引就可以直接获取到数据,而不需要回表查询。覆盖索引的好处就是它不会包含整个记录所有信息,因此它的大小要比聚集索引要小很多,在查找的时候 也能大大的降低其io操作。

接下来2个例子就能让你了解覆盖索引的意思,我们先创建一张表

create table buy_log(
    userid INT UNSIGNED NOT NULL,
    buy_date DATE
)

然后我们随机插入一些数据,并且添加2个辅助索引

alert table buy_log add key (userid);
alert table buy_log add key (userid, buy_date);

接下来,我们做一个查询假设我们查 userid < 2的所有数据

select * from buy_log where userid < 2;

这条语句应该会用到哪个索引?

image.png 没错,就是会选择到 (userid, buy_date)的索引, 为什么呢?我们刚刚不说过覆盖索引只会包含其中的一部分数据,而不是全部记录的数据。因此在这里 select * 正好满足了 这一条覆盖索引所拥有的数据。

那么再来看一个?

select userid from buy_log where userid < 2;

这一次,我们只需要userid这个值

image.png 很明显。这一次查询优化器选择的是(userid)这个索引,因为这个索引就能覆盖到userid了。

思考

  • 为什么在重复值高(比如性别)的字段里不要创建索引?
  • 为什么不要随便就添加一个索引?

答案

  1. 我们刚才有说过索引的创建,其本质都是b+树,当你在性别这列创建索引时,其b+树的数据结构就直接指向了聚集索引的主键,导致每一条数据都是需要回表查询,而且还需要额外维护此索引
  2. 就如刚才所说。过多的索引就导致我们每一次的插入删除都要维护所有的b+树

补充

关于 哈希索引 全文索引不在此文章详细介绍