为什么加了索引sql性能提高好几倍?

在我们日常开发中,针对mysql的sql优化,最简单、最常用的方式就是为查询条件加索引。那么为什么加个索引,sql的性能就能有明显的提升呢?这是因为针对没有索引的sql语句,查询是在磁盘中全表扫描过滤的,我们知道计算机中磁盘性能是最低的,相对于CPU和内存来说,慢的可不是一点半点。那么索引中是怎么查询过滤的呢?索引中其实是采用了B+树的方式,在内存中过滤完数据之后,才到磁盘中获取具体的数据,相对于在磁盘中过滤来说,性能当然会提高很多。我们来看一下具体的索引过滤方式。

mysql索引

mysql中的不同存储引擎的存储方式是不同的,例如,在InnoDB引擎中使用的是聚簇索引,myISAM使用的是非聚簇索引。关于聚簇索引和非聚簇索引的区别我们后面会说到,这里主要说一下InnoDB中的索引。InnoDB索引类型有很多,大致分为两大类,一种是主键索引,一种是普通索引。之所以分为这两种,是因为他们在查询过滤的过程中不太一样。其他的索引后面会讲到。

普通索引

如上图所示,我们看到在普通索引中,索引是以B+树的形式存储的,先大概介绍下B+树。B+树的每个节点可以存储多个元素,所有的中间节点元素都同时存在于子节点,在子节点元素中是最大(或最小)元素(如图中的2就是这样)。B+树中的每个元素不保存数据指针,只用来索引,所有数据指针都保存在叶子节点,同时叶子节点是以链表的形式存储的。在上图中我们可以看出B+树范围查询,只需要查询到范围初始最小节点的位置,然后在链表中遍历到最大节点的位置,即可拿到范围数据。具体B+树流程在这不做详解,感兴趣的可以自行看下资料。

所以使用B+树的优点在于,所有的中间节点都是存储的索引,只有叶子节点存储的是数据的指针,所以,所以磁盘页中可以存储更多的节点元素,也就是说可以让树的高度变得更低,来减少IO的次数。另外因为B+树每次都要查询到叶子节点来获取指针,所以B+树是稳定查找。


我们了解完B+树之后,可以看到普通索引中的存储方式跟B+树中不同的是,它的叶子节点中存储的是主键id。拿到主键之后需要再去主键的B+树中获取到具体的数据信息,这个过程我们称之为回表。从这里我们也可以看出,主键索引和普通索引的区别就在于不需要回表。如上图。

为什么要遵守最左前缀原则

经过上面的分析之后,我们就不难理解为什么要准守最左前缀原则了。比如在联合索引的情况下,有name 、age 、sex三个字段的联合索引,为了大家好理解,我简单画了下图。


在上图中,因为我们是name、age、sex的组合索引,所以每一个name、age、sex是一个节点。这个时候的排序方式就变成了从name开始排序,name排序完成的情况下再依次讲age、sex进行排序。比如是 2 、8 、5为一个组合,5 、 3 、2为一个组合,3 、 6 、5为一个组合,这几个组合是怎么排序的呢?他们的排序顺序为 2 、8 、5 --> 3 、6 、5 --> 5 、3 、2。这个时候我们发现其实它是首先按照第一个字段排序的,在保证第一个字段有序的情况下,再依次对后面的字段进行排序。

也就是说在创建联合索引(a,b,c)的时候,只用(a,c)查的话,只能用到a的索引,无法用到c的索引,因为联合索引的B+树,首先是根据a来排序的,也就是说a是绝对有序的,b,c基本上是无序的。

总结

我们先解答开篇,之所以加了索引之后sql语句的效率会提高,是因为索引的底层数据结构是B+树。B+树的中间节点都是存储的索引,只有叶子节点存储的是数据的指针(普通索引是存储的id),所以,所以磁盘页中可以存储更多的节点元素,也就是说可以让树的高度变得更低,来减少IO的次数。

另外我们还知道了innodb使用的是聚簇索引,myISAM使用的是非聚簇索引。

聚簇索引又分为主键索引和辅助索引,主键索引的B+树中的叶子节点是直接存储的数据。辅助索引的B+树叶子节点中存储的是主键id。非聚簇索引B+树的叶子节点中存储的是数据的指针。

还有就是遵守最左前缀原则是因为,B+树中是从最左边开始排序的,保证最左有序的情况下再依次对后面进行排序。所以无论什么情况下最左都是有序的,要知道,B+树本身就是需要有序才能成立,无序的数据是没办法获取的哦!