我把mysql索引的裤子给扒了

介绍

提到互联网企业最青睐的关系型数据库,那必然是mysql。而提到mysql性能优化,索引是不得不说的一个话题。索引是什么?何时使用索引?索引怎样提高数据库性能?且听我娓娓道来。

什么是索引

一句话总结:索引是排好序的快速查找数据结构,它以一种特定的方式有条理地组织起mysql数据库中的数据,对于查找和排序的性能有着显著作用。

举个栗子

select * from tableA where field = x

当我们使用上面这条SQL语句查找我们想要的数据。未建立索引时,mysql为了从茫茫数据中找到那条"天选之子"记录,需要在整张表上奋力狂奔,速度实在是缓慢。 而建立索引之后,mysql只需要在索引建好的数据结构上有序查找,速度极大提升。

有的朋友可能会问了:

我从来不建索引,select语句一直都是秒出啊,你说的这个性能我完全感受不到呀???

听到这话的博主顿时露出了坏笑:

兄dei,你的数据量也太少了, 要不要我让你试一试不建索引的千万级亿万级数据表,来让你体验一下mysql的毒打,嗯?

说了这么多,原来索引这货听起来好像很牛逼的样子,那它到底是个啥玩意呢?

mysql的索引类型主要有两种:

  1. B+Tree索引
  2. Hash索引

mysql绝大多数情况都选择建立B+Tree索引,所以这里只介绍前者。让我们一起揭开索引的神秘面纱:B+Tree。

B树?B数?傻傻分不清楚

在熟悉B+树之前,我们先要有B数。咳咳,是先来熟悉B树,而在B树之前,我们都学过很多的树型数据结构:什么二叉树,二叉平衡树,红黑树......各种乱七八糟的树。 为什么他们都不适合用来作索引,最终都输给了B+树呢?

众所周知,线性查找是非常慢的,毕竟一个一个往后找,用术语讲就是O(n)的时间复杂度。而线性排序后的二分查找将效率提升到了O(logn),对于一个平衡的二叉树而言,从根节点向下搜寻, 其本质也就是二分查找,虽然时间效率显著提高,但是缺点也比较明显:首先,插入节点的时候必须保持树的平衡,因此维护起这样一棵树,要消耗的资源也比较多;另外, 由于每个节点只有两个分叉,随着数据的不断添加,二叉树的高度也会快速升高,升高会发生什么呢?从根节点每向下搜索一次,意味着一次IO,树高了,IO次数多了,时间自然就变得很长。

说完二叉树再来说红黑树,红黑树本质也是二叉树,但它也经过了一些优化。它不像平衡二叉树那样追求绝对的平衡了,它只追求大致的平衡(它是一棵空树或它的左右两个子树的高度差的绝对值不超过1, 并且左右两个子树都是一棵平衡二叉树),既减少了维护树的代价,又让时间复杂度维持在O(logn)

既然二叉不够好使,那么为啥不能搞一个高效率多叉呢? 德国计算机学家鲁道夫·拜尔一拍自己脑门儿,想出了一个好点子! B树就被发明了。果然心中有B数,自然好点子出,咳咳我怎么单押了。

总的来说,B树是一种平衡的多叉树,相同的数据量,B树会比前面的二叉类的树更"胖",同时高度更矮。这就意味着从根向下搜索的io次数减少,速度加快。但有个缺点,就是和平衡二叉树一样, 中间节点也可以保存数据,搜索可能会在中间节点命中,这就造成每次搜索时间有概率不一致,另外,在执行范围型搜索时,效率就嗝屁了,比如下面这条SQL语句:

select fieldA from tableA where fieldB>10

那么,就轮到B+树来终结这一切了! B+树又在B树上做出了改进:中间节点不存放数据了,只存放指向儿子节点的指针,所有的数据都放在叶子节点中,并且,所有叶子节点在同一个高度上。叶子节点又 按存储的特征值从小到大用链表串起来了。

比如我们建一张表,只有id和name两个字段,主键是id,表名就叫test,并且插入5条数据:

test表

注意:mysql内部会自动为主键建立一个索引,用来组织数据库中的数据。如下图,(不绝对一样,但大致相同),叶子节点里存放的是主键值和这一行记录的其他数据,对应于我们这个表,由于除主键外只有name一个字段,那么就只存了name的值。

主键字段索引树

然后我们再在name字段建立索引

alter table `test` add index i_name ( `name` ) 

那么mysql在内部就会维护这样一棵树(不绝对一样,但大致相同),树的叶子节点存放的是name值和对应的主键值。:

name字段索引树

这样的话,当我们执行:

select * from test where name='张三'

mysql就可以走索引查询,而不用全表扫描一遍了。(但由于这条语句写的是select *,这个过程里面还会有隐藏一步,就是从name的索引树拿到name对应的id主键后,mysql还要到主键索引里面去搜寻,这一步叫回表,有一定开销,这也就是为什么我们要尽量少用select *,而要用覆盖索引,后面的文章再说)。

说到这里肯定有朋友会惊呼: 妙啊!~~(没有也请配合一下),但上面只是最简单的一种情况,有关索引更复杂的情况我后面还会再写.

最终B+树达到了什么效果呢?搜索只会有一种情况,就是一定搜索到叶子节点才命中,那么时间效率就全统一了,又由于叶子节点用链表串起来了,所以就算执行范围select语句,效率也依然坚挺!

现在知道mysql为什么用B+树来作索引了吧,了解B+树的具体细节可以参考这篇文章: B+树总结

讲完了索引是啥,是干啥的,后面当然就是实操中怎么在mysql索引调优了。

请移步:调优操作(一)调优操作(二)

评论

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×