在什么是索引这篇文章中介绍了索引的常见的数据结构,其对应数据结构的图是以主键索引来讲解的。本文我们一起看下联合使用在B+树的结构是怎么样的?
联合索引是什么
对多个字段同时建立的索引(有顺序,ABC,CBA是完全不同的两种联合索引);
假设联合索引为age,name,school组合的
其在B+树的结构如下:
联合索引的叶子节点中data数据存储的是主键id;如果要查询语句中查询的是表中所有的数据,则先通过联合索引树找到主键ID,然后再根据主键索引树找到对应data数据;
联合索引中一个节点中会按照索引从左到右的索引构建一个节点,这样也就可以和SQL优化中常说的最左前缀匹配原则进行关联;
最左前缀匹配原则
最左前缀匹配原则和联合索引的索引树结构和检索方式是有关的。在上面联合索引树,最底层的叶子节点先按照age从左右到右递增排列,name和school列无序的;当age列相同时,相同的节点中会按照name从左到右递增排列,school无序的;当name列相同时,则就按照school从左到右递增排列;
所以我们在写查询语句的时候,一般最好按照索引的最左前缀匹配原则编写sql;
如stu表中有索引(age,name,school),查询语句如下:
select * from stu where age = 23 and name = 'E' and school = '交大';
这条SQL语句会走索引树进行检索,会使用到age,name,school这个字段组合的索引;
select * from stu where age = 23 and school = '交大';
这条SQL语句也会走索引树进行检索,但是只会使用到age字段,MySQL在检索B+树时,先检索age,找打值为23的节点,但是name字段无法走索引检索,school字段则也没有使用到;从而只使用age字段,将age等于23的数据都取出在一个个和school字段值进行对比,筛选需要的数据。
覆盖索引
上面说到联合索引叶子节点中的data存储的是主键id,当sql语句使用到联合索引是按照索引树检索找到符合条件的叶子节点中data的主键id,然后根据主键id去检索主键索引树,找到对应的全部数据;那如果我们在查询的时候不需要将所有的数据都返回,我们就可以使用到覆盖索引;
覆盖索引是一种很常用的优化手段。因为在使用辅助索引检索时候,我们只能拿到叶子节点中data数据即主键,相当于获取数据还需要再根据主键查询主键索引树再获取到数据。当我们查询的字段是联合索引(辅助索引)中的字段,则不需要进行回表,直接从索引树上进行返回就可以啦;
select age,name,school from stu where age = 23 and name = 'E' and school = '交大';
这条sql语句执行的时候就直接在联合索引树中直接取出符合条件的数据,无需通过回表。