结合B+树,谈数据库的联合索引

xiaoxiao2021-02-28  36

先给一个问题: 数据库表T有A,B,C三个字段,对其建立联合索引uniq(A,B,C),请问如下查询哪些会用到索引? 1. SELECT * FROM T WHERE A=a AND B=b AND C=c; 2. SELECT * FROM T WHERE A=a AND B=b; 3. SELECT * FROM T WHERE A=a AND C=c; 4. SELECT * FROM T WHERE B=b AND C=c;

大家都知道联合索引有最左原则。也就是说,如果联合索引的第一个列没有在WHERE语句中,或者所查询的列其中并没有在索引中被建立。那么,这个联合索引是无效的。 比如,上面的问题,这个索引可以被用于搜索如下所示的数据列组合: A,B,C A,B A

MySQL不能利用这个索引来搜索没有包含在最左前缀的内容。例如,如果你按照B或C来搜索,就不会使用到这个索引。 如果你搜索给定的A和C的组合,该索引也是不能用于这种组合值的,尽管MySQL可以利用索引来查找匹配的A从而缩小搜索的范围。

那么,为什么是最左原则呢? 就要想想联合查询的结构是怎样的。

首先,先看看B+树的结构图。 (图源张洋的《MySQL索引背后的数据结构及算法原理》一文)

那联合索引呢? 这是一张表格,col1 是主建,col2和col3 是普通字段。 (图源张洋的《MySQL索引背后的数据结构及算法原理》一文) 那么,多列的索引是这样的 (图源张洋的《MySQL索引背后的数据结构及算法原理》一文) 也就是说,联合索引(col1, col2, col3)也是一棵B+树,其非叶子节点存储的是第一个关键字的索引,而叶子节点存储的则是三个关键字col1、col2、col3三个关键字的数据,且按照col1-col2-col3的顺序进行排序。

索引还可以这么画。 如果执行的是,SELECT * FROM T WHERE B=‘Tom’ AND C=4567; 那么无法使用索引,因为索引是用A字段先排序的,如果没有先确定A,直接查找B和C,那么将会是全表查询。

如果执行的是,SELECT * FROM T WHERE A=‘30’ AND B=Demi; 那么,会先找到A字段,再在A等于30的数据中(比如有很多条),找B等于Demi的数据。这样是可以用到索引的。

如果执行的是,SELECT * FROM T WHERE A=‘18’ AND C=1234; 那么,A字段可以索引,而C不能索引。所以可以部分索引,也比全表查询快。

现在,大概了解了什么为什么是最左原则。因为,B+树是按照最左边的字段以此构建的。

PS:感谢贝贝网面试官提出的问题,并给出了建议。欢迎大家指正:)

转载请注明原文地址: https://www.6miu.com/read-1750361.html

最新回复(0)