B-TREE索引是最常用的索引,适合等值查询、范围查询、索引排序、多列条件、条件包含表达式等等场景。

操作符

操作符 示例
< select * from test where id <1
<= select * from test where id <=1
= select * from test where id =1
>= select * from test where id >=1
> select * from test where id >1
between and select * from test where id between 1 and 10
in select * from test where id in (1,2,3)
like select * from test where id like ‘abc%’

多列索引

多列索引用于定义在表的多个列上的索引,最多可以指定32个列。

  • 表结构
    create table a(id int,name varchar(10));
  • 查询语句
    select * from test where id=1 and name='a1’;         
  • 创建多列索引
    create index ON test(id,name);
  • 查看执行计划
    postgres=# explain select * from test where id=1 and name='a1';
                                       QUERY PLAN
    --------------------------------------------------------------------------------
     Index Only Scan using a_id_name_idx on test  (cost=0.42..8.44 rows=1 width=10)
       Index Cond: ((id = 1) AND (name = 'a1'::text))
    (2 rows)

以上示例中在条件没有包含ID的情况下不会走索引,示例如下。

postgres=# explain select * from test where name='a1';
                       QUERY PLAN
--------------------------------------------------------
 Seq Scan on test  (cost=0.00..1791.00 rows=1 width=10)
   Filter: ((name)::text = 'a1'::text)
(2 rows)

表达式索引

表达式索引用于索引的列不是物理表的一个列,是对表的一个列或者多列进行计算的函数或者表达式。

  • 表结构
    create table a(id int,name varchar(10));
  • 查询语句
    select * from test where lower(name)='a1’;
  • 创建表达式索引
    create index ON test (lower(name));
  • 查看执行计划
    postgres=# explain select * from test where lower(name)='a1';
                                       QUERY PLAN
    --------------------------------------------------------------------------------
     Bitmap Heap Scan on test  (cost=12.17..571.91 rows=500 width=10)
       Recheck Cond: (lower((name)::text) = 'a1'::text)
       ->  Bitmap Index Scan on test_lower_idx  (cost=0.00..12.04 rows=500 width=0)
             Index Cond: (lower((name)::text) = 'a1'::text)
    (4 rows)

索引表达式的维护代价较为昂贵,在每一行被插入或更新时都得为它重新计算相应的表达式。

部分索引

当一个部分索引是建立在表的一个子集上,而该子集由一个条件表达式定义,索引中只包含符合谓词的表行的项,则可以使用部分索引。

  • 表结构
    create table a(id int,name varchar(10));
  • 查询语句
    select * from test where name='a1';
    select * from test where name='a2';
  • 创建部分索引
    create index ON test(name) where name='a1';
  • 执行计划
    postgres=# explain select * from test where name='a1';
                                    QUERY PLAN
    ---------------------------------------------------------------------------
     Index Scan using test_name_idx on test  (cost=0.12..8.14 rows=1 width=10)
    (1 row)
    
    postgres=# explain select * from test where name='a2';
                           QUERY PLAN
    --------------------------------------------------------
     Seq Scan on test  (cost=0.00..1791.00 rows=1 width=10)
       Filter: ((name)::text = 'a2'::text)
    (2 rows)

索引排序

索引除了简单查找返回行之外,还可以按照指定顺序返回不需要独立的排序步骤。

  • 表结构
    create table a(id int,name varchar(10));
  • 查询语句
    select * from test order by name desc;
  • 创建索引前计划
    postgres=# explain select * from test order by name desc;
                                QUERY PLAN
    -------------------------------------------------------------------
     Sort  (cost=9845.82..10095.82 rows=100000 width=10)
       Sort Key: name DESC
       ->  Seq Scan on test  (cost=0.00..1541.00 rows=100000 width=10)
    (3 rows)
  • 创建索引
    create index ON test (name desc);
  • 查看执行计划
    postgres=# explain select * from test order by name desc;
                                        QUERY PLAN
    -----------------------------------------------------------------------------------
     Index Scan using test_name_idx on test  (cost=0.29..3666.46 rows=100000 width=10)
    (1 row)

默认情况下,B-TREE索引将它的项以升序方式存储,并将空值放在最后。您可以在创建B-TREE索引时通过ASC、DESC、NULLS FIRST和NULLS LAST选项来改变索引的排序。

只使用索引扫描和覆盖索引

只查询索引相关字段,可以通过索引直接返回数据,无需访问具体的数据文件。

  • 示例一
    • 表结构
      create table a(id int,name varchar(10));
    • 查询语句
      select name from test where name=‘a1’;
    • 没有索引时的执行计划
      postgres=# explain select name from test where name='a1';
                            QUERY PLAN
      -------------------------------------------------------
       Seq Scan on test  (cost=0.00..1791.00 rows=1 width=6)
         Filter: ((name)::text = 'a1'::text)
      (3 rows)
    • 创建索引
      create index ON test (name);
    • 有索引时的执行计划
      postgres=# explain select name from test where name='a1';
                                        QUERY PLAN
      -------------------------------------------------------------------------------
       Index Only Scan using test_name_idx on test  (cost=0.29..8.31 rows=1 width=6)
         Index Cond: (name = 'a1'::text)
      (2 rows)
  • 示例二
    • 表结构(与示例一相同)
      create table a(id int,name varchar(10));
    • 查询语句
      select * from test where name='a1’;
    • 没有索引时的执行计划
      postgres=# explain select * from test where name='a1';
                             QUERY PLAN
      --------------------------------------------------------
       Seq Scan on test  (cost=0.00..1791.00 rows=1 width=10)
         Filter: ((name)::text = 'a1'::text)
      (2 rows)
    • 创建覆盖索引
      create index ON test (name) include(id);
    • 有索引时的执行计划
      postgres=# explain select * from test where name='a1';
                                          QUERY PLAN
      -----------------------------------------------------------------------------------
       Index Only Scan using test_name_id_idx on test  (cost=0.42..8.44 rows=1 width=10)
         Index Cond: (name = 'a1'::text)
      (2 rows)

查询语句必须只引用存储在该索引中的列,才能使用覆盖索引,即只需要扫描索引,不需要去扫描表中数据就可以得到相应的结果。

索引页面类型

索引页面将简单介绍索引的内部架构,上述的索引功能都是基于内部架构实现。PolarDB的B-TREE索引页面分几个类型:

  • meta page
  • root page
  • branch page
  • leaf page

其中meta page类型和root page类型是必须有的,meta page需要一页来存储,表示指向root page的page id。随着记录数的增加,一个root page可能存不下所有的heap item,就会需要leaf page类型、branch page类型或多层的branch page类型。

BTREE索引