复合索引的底层数据结构
复合索引一定是一颗B+树
这是一张表格,col1 是主建,col2和col3 是普通字段。
主索引 对应的 B+树 结构是这样的:
对col3 建立一个单列索引:
如果对 col3 和 col2 建立 联合索引,那么 B+ 树会是一个什么样子的呢?
首先可以肯定的是,肯定只有一棵树,又因为 最左原则的存在:
先根据col3 排序,在根据 col2 排序。
建索引语句 CREATE INDEX IDX_XXX ON TABLE(COL3, COL2);
为了更好的理解,在例子中的数据中增加了重复数据,
红色框是改动的地方,把col3 改成有重复数据了,然后 还是对 col3 ,col2建立联合索引,那么 B+树 如下:
复合索引在查找的时候,比如要找 Alice,34
这条记录WHERE COL3 = 'Alice' AND COL2 = 34
先根据col3 查找 Alice ,找到了2条记录,再根据col2 查找 34,然后获取到主键 15 ,再根据主键去查找 主索引。
如果 是 WHERE COL2 = 34
,由于只有复合索引 (col3, col2),没有col2 的单列索引,且不符合最左原则,那么查找的时候,就没法根据上面的这棵树来查找 ,只能全表扫描,如果是 WHERE COL3 = Alice
, 就可以用到复合索引。
所以为什么会有最左原则,就是因为 B+树 是根据最左边的字段构建的:CREATE INDEX IDX_XXX ON TABLE(COL3, COL2);
,所以查找时会以最左边的字段COL3
为基础进行查找,而不能跨过左边的字段,去直接查找右边的字段,如果这样做,那么我们建立的复合索引就会失去作用,查找将会变成全表查询。
注意:
联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持(a),(a,b),(a,b,c)
3种组合进行查找,但不支持 (b,c)
进行查找 .当最左侧字段是常量引用时,索引就十分有效。
参考文章:MySQL索引背后的数据结构及算法原理