地球人都知道,InnoDB中索引结构为B+树,这是综合了B+树寻址次数少且查询耗时较稳定等优点决定的。
那B+树中存储的是什么东西呢?根据索引树就能直接找到对应的数据行吗?要说明这一点,就要详细地聊一下B+树的储存内容。
- 回表
实际上,InnoDB除了维护了一个索引B+树之外,还维护了一个主键B+树。索引B+树节点上存放的数据实际上是主键的值,而主键B+树上存放的才是真正的数据行本身(因为InnoDB是聚集索引,所以直接存储了数据行,如果是MyIsam非聚集索引,这里储存的只是数据行的一个地址,还需要去磁盘中获取数据行。这点具体可以看一下聚集索引与非聚集索引的区别)
那么这点弄清楚了,就可以分析一下,如果一条指令通过索引(同时不是主键)来检索数据,会发生什么呢?
例如在一个居民表中,有以下字段,其中id是主键,并且建立了name索引。
id | name | age |
---|---|---|
1 | xxx | 19 |
当执行语句:
SELECT * FROM MyTable WHERE name=xxx;
首先MySQL分析到该次查询是通过name索引来检索的,所以就会去检索索引B+树。
而刚才我们也说了,索引树上存的实际上是主键,所以不出意外的话,我们能从索引树上获取到id=1这个主键。
于是这时如果想查询出整行数据,就要发生 回表:拿着id=1这个主键,再去主键树上进行查询,进而查询出真正的一条数据行。这个查询语句才算执行完成了。
回顾一下,在这个查询过程了,我们一共进行了两次B+树的检索,其中后面一次就是回表。就像我们猜测的一样,回标操作必然会导致查询时间的增加,开销的增大。
如果此时使用覆盖索引就可以避免回表操作,那啥是覆盖索引?考虑这个SQL语句:
SELECT id FROM MyTable WHERE name=xxx;
那这样执行流程是怎么样?我们发现,检索索引树的时候,直接就把id给查询出来,符合我们的预期结果了,不需要再进行一次回表。那这种操作就叫做覆盖索引,实际中也经常使用覆盖索引来进行性能的优化。
- 联合索引
基于刚说完的覆盖索引,我们再考虑一个问题,当在一个需要进行”根据name查询出age“操作很频繁的场景之下,按照我们刚才所建立的主键和索引,我们不难判断这个操作:
SELECT age FROM MyTable WHERE name=xxx;
或者这个操作
SELECT 一个其他属性 FROM MyTable WHERE name=xxx AND age=10;
每次执行时都需要进行回表操作(因为索引树中并没有age相关的数据),这很明显是不太合理的。那咋办?把主键声明为age,利用覆盖索引避免回表操作?别傻了,不同的人可能会有相同的age,这怎么能作为主键呢?那这时,我们可以定义联合索引了。
取{name,age}作为一个联合索引,那么每次查询的时候,因为索引下推(会检索索引的所有值,只有都符合条件的时候,才会对该索引进行回表),MySQL除了查出name之外,还能查出age的值,这样我们就能从索引树直接获得age字段,而避免了一次回表操作。当然这样做也有缺点,联合索引因为比较复杂,通常来说维护起来也会产生比单个索引更大的开销,这个就要和实际情况相结合来进行取舍了。
- 最左前缀原则
回表的问题可算是解决了,建立一个联合索引{name,age}就可以。那问题又来了:
我不一定是通过name和age来检索数据啊,我们可能直接通过name来检索,那这时咋办,同时建立一个{name,age}联合索引来满足联合查询,和一个{name}单独索引来满足单独查询?是不是太繁琐了?
MySQL也考虑到了这个问题,所以MySQL有一个最左前缀原则。
拿{name,age}来举例,该索引中的最左边是name字段,根据最左前缀原则,该索引既可以用于name和age组合查询,也可以用于name的单独查询。那么这就好办了,只需要维护{name,age}就可以了,无需再多建立一个name索引。当然如果还需要一个单独的age索引,因为它不是在联合索引的最左边,所以还是需要额外建立age索引的,否则会进行全表扫描