组合索引

发布于 2021-01-25 15:37:36   阅读量 55  点赞 0  

 当WHERE子句存在多个条件查询的时,若只对各条件字段使用单列索引,则检索时只会走某个字段上的索引(若有),因为 MySQL 只会选择它认为最有效率的单列索引,并建立中间结果集,再在中间结果集上进行后续字段的筛选,即做一个全表扫描。而在中间结果集上是无法利用索引进行检索的。

 因此,为了提高条件组合时的检索,可以使用组合索引。


组合索引的创建

 在建立索引时,传入一个字段列表代替单字段即可,具体见《MySQL 索引与索引优化》。


组合索引的原理

 当建立了组合索引(col1, col2, col3)时,实际上等价于(并不是真的)建立了:

  • (col1, col2, col3)

  • (col1, col2)

  • (col1)

 这是依据“最左前缀”原则建立的索引组合。即以最左边的字段开头的连续组合。

 组合索引的 底层逻辑 是:依据col1排序,当col1相同时再根据col2进行排序,……,依次类推,建立 Btree。故这样的 Btree 可以用于检索col1条件、col1 and col2col1 and col2 and col3的组合条件。

 这样一个索引可以用在多种组合条件场景,只要符合最左匹配原则,具体的生效规则为:索引顺序从前往后依次生效,如果中间某个索引没有使用,那么断点之前的索引部分起作用,断点后面的索引不起作用。

// 若建立了组合索引 (col1, col2, col3)
WHERE col1_condition AND col2_condition AND col3_condition;     // 三个索引顺序使用中间无断点,全部生效
WHERE col1_condition AND col3_condition;    // col2 是断点,col1 走了索引,col3 没有
WHERE col2_condition AND col3_condition;    // col1 是断点,这种组合条件下,组合索引不发挥效果
WHERE col2_condition AND col3_condition AND col1_condition;     // col1、col2、col3 都走索引,与组合条件的书写顺序无关
WHERE col1_condition AND col2_condition AND col3_condition AND col4_condition;      // col1、col2、col3 的检索都走索引,然后基于 col4 做全表扫描

</body>

最左原则

 当建立一个组合索引时,该组合索引依据最左优先原则在逻辑上存在多个索引。

 当进行组合条件的检索时,与组合索引的匹配遵循最左匹配原则。


建立组合索引的建议

  • 将维度更高的字段放在组合条件与索引的前面。


Last Modified : 2021-02-03 10:43:05