作者 l zyz1992来源 l Hollis(ID:hollischuang)索引,可能让好很多人望而生畏,毕竟每次面试时候 MySQL 的索引一定是必问内容,哪怕先撇开面试,就在平常的开发中,对于 SQL 的优化也而是重中之重。可以毫不夸张的说,系统中 SQL 的好坏,是能直接决定你系统的快慢的。但是在优化之前大家是否想过一个问题?那就是:我们优化的原则是什么?优化SQL的理论基础是什么?虽然说实践出真知,但是我更相信理论是支撑实践的基础,因为我们不可能毫无目的的去盲目的实践,因为这样往往事倍功半。所以说了这么多只想告诉大家,在真正的开始索引优化之前,我们需要彻底搞明白索引的原理。这样再谈优化你将觉得更丝滑~
从B树的结构图中可以看到每个节点中不仅包含数据的 key 值,还有 data 值。而每页的存储空间是有限的,如果 data 比较大,会导致每个节点的 key 存储的较少,当数据量较大的时候,同样会导致B树很深,从而增加了磁盘 IO 的次数,进而影响查询效率。好了,说到这里,常见的索引的种类也说完了,上面的内容仅仅是作为一个铺垫,下面我们正式开始 MySQL 的 B+ 树。
在插入数据的时候,MySQL 首先会根据 name 进行排序,如果 name 一样,就根据联合索引中的 age 去排序,如果还一样,那么就会根据 主键 字段去排序。插入的原理就是这样子的。此时每个数据页中的记录存放的实际是索引字段和主键字段,而其他字段是不存的(为什么不存放?一样的数据到处存放很浪费空间的,也没必要,所以才会有下面的索引优化),至于查找,原理和过程跟聚簇索引一样,这里就不再赘述,但是,下面说的内容却是至关重要的:假设现在执行这样的SQL:
SELECT name FROM student WHERE name='wx'
那么此时的查询是完美的,使用到了索引且不需要回表7.回表是这样子的,现在要根据 name 查找到该条记录,且查询的字段(即 select 后面的查询字段)也仅仅有 name(只要是在 name,age,id 这三个字段中都可以)这个时候是能够直接获取到最终的记录的换句话说,因为联合索引中的记录也仅仅有 name,age,id,所以在查询的如果也仅仅查询这三个字段,那么在该B+树中就能够查询到想要的结果了。那现在假设查询的 SQL 是这样子的(我们假设 student 中还有除了name,age,id 其他的字段 )SELECT * FROM student WHERE name='wx'那这下子就完蛋了,因为你现在虽然根据 name 很快的定位到了该条记录,但是因为 name+age 不是聚簇索引,此时的 B+ 树的数据页中存放的仅仅是自己关联的索引和主键索引字段,并不会存其他的字段,所以这个时候其他的属性值是获取不到的,这时候该怎么办?这种情况下,MySQL 就需要进行回表查询了。此时 MySQL 就会根据定位到的某条记录中的 id 再次进行聚簇索引查找,也就是说会根据 id 去维护 id 的那么 B+ 树中查找。因为聚簇索引中数据页记录的是一条记录的完整的记录,这个过程就叫回表。再强调下回表的含义:根据非主键索引查询到的结果并没有查找的字段值,此时就需要再次根据主键从聚簇索引的根节点开始查找,这样再次查找到的记录才是完成的。最后,让我一起看下 MySQL 对于非主键索引的维护过程:对于非主键索引(一般都是联合索引),在维护 B+ 树的时候,会根据联合索引的字段依次去判断,假设联合索引为:name + address + age,那么 MySQL 在维护该索引的 B+ 树的时候,首先会根据 name 进行排序,name 相同的话会根据第二个 address 排序,如果 address 也一样,那么就会根据 age 去排序,如果 age 也一样,那么就会根据主键字段值去排序,且对于非主键索引,MySQL 在维护 B+ 树的时候,仅仅是维护索引字段和主键字段。