如何使用B-Tree索引

有多种类型的索引,它们各有各的性能特点,常见的索引包括:

  • B-Tree 索引
  • Hash 索引
  • 空间R-Tree索引
  • 全文索引

B-Tree索引是最常见的索引,当我们讨论索引而没有指明是哪种类型的索引的时候,一般就是指B-Tree索引。

B-Tree Index

B-Tree索引可以很好地用于单行、范围或者前缀扫描,他们只有在查找使用了索引的最左前缀(Leftmost Prefix)的时候才有用。

P.s.

  1. 暂时不讨论非前导索引(non-leading index parts)
  2. 当然完全前缀匹配更有效率(complete prefix)

对于以下类型的索引,可以使用B-Tree索引:

  • 匹配全部索引列
  • 匹配最左前缀
  • 匹配列前缀
  • 匹配范围值
  • 精确匹配一部分(前一部分),并匹配某个范围内的另一部分
  • 覆盖索引,B-Tree索引通常能支持只访问索引的查询

此外,由于树的节点是排好序的,可以用于查找,那么也可以用同样的方式排序,所以也可以用于ORDER BY。

B-Tree索引存在一些限制:

  • 如果查找不从索引列的最左边开始,索引就无法使用;同样,不能查找字符串结尾
  • 不能跳过索引中的列
  • 不能使用任何在第一个范围条件右边的列作为条件

因此B-Tree的列顺序非常重要,上述使用规则都和列顺序有关。对于实际的应用,一般要根据具体的需求,创建不同列和不同列顺序的索引。

举例如下: 假设有索引index (A, B, C)

  • 使用索引:

    • A>5 AND A<10 - 最左前缀匹配
    • A=5 AND B>6 - 最左前缀匹配
    • A=5 AND B=6 AND C=7 - 全列匹配
    • A=5 AND B IN (2,3) AND C>5 - 最左前缀匹配,填坑
  • 不能使用索引:

    • B>5 - 没有包含最左前缀
    • B=6 AND C=7 - 没有包含最左前缀
  • 使用部分索引:

    • A>5 AND B=2 - 使用索引A列
    • A=5 AND B>6 AND C=2 - 使用索引的A和B列

使用索引对结果进行排序,需要索引的顺序和ORDER BY子句中的顺序一致,并且所有列的升降序一致(ASC/DESC)。如果查询连接了多个表,只有在ORDER BY的列引用的是第一个表才可以(需要按序JOIN)。

  • 使用索引排序:

    • ORDER BY A - 最左前缀匹配
    • WHERE A=5 ORDER BY B,C - 最左前缀匹配
    • WHERE A=5 ORDER BY B DESC - 最左前缀匹配
    • WHERE A>5 ORDER BY A,B - 最左前缀匹配
  • 不能使用索引排序:

    • WHERE A=5 ORDER BY B DESC,C ASC - 升降序不一致
    • WHERE A=5 ORDER BY B,D - D不在索引中
    • WHERE A=5 ORDER BY C - 没有包含最左前缀
    • WHERE A>5 ORDER BY B,C - 第一列是范围条件,无法使用BC排序
    • WHERE A=5 AND B IN(1, 2) ORDER BY C - B也是范围条件,无法用C排序

flacro

Read more posts by this author.