如何使用B-TREE索引_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

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 test(id int,name varchar(10));

查询语句

SELECT * FROM test WHERE id=1 AND name='a1';

创建多列索引
```
CREATE INDEX ON test(id,name);
```

查看执行计划

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的情况下不会走索引，示例如下。

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 test(id int,name varchar(10));

查询语句

SELECT * FROM test WHERE lower(name)='a1';

创建表达式索引
```
CREATE INDEX ON test (lower(name));
```

查看执行计划

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 test(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';

执行计划

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)

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 test(id int,name varchar(10));

查询语句
```
SELECT * FROM test ORDER BY name DESC;
```

创建索引前计划

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);
```

查看执行计划

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 test(id int,name varchar(10));

查询语句
```
SELECT name FROM test WHERE name='a1';
```

没有索引时的执行计划

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);
```

有索引时的执行计划

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 test(id int,name varchar(10));

查询语句
```
SELECT * FROM test WHERE name='a1';
```

没有索引时的执行计划

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);

有索引时的执行计划

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索引