PostgreSQL中Btree索引的作用是什么

结构
Btree是一种常见的数据结构，具有以下特点：< br/>1.Btree是平衡树，以根节点为分界线，左右两边的中间节点数量相同，也就是说查询任意值的时间是相同的
2.Btree有多个分支，每个页（8KB）可以有数百个TID，这意味着Btree只需要几个级别就可以支持巨大行的表
3. Page之间和Page内索引中的数据都是有序的，同一级别的页面通过双向链表相互连接

NULLs
PostgreSQL在创建索引时会存储NULLX。因此，当条件为IS NULL和IS NOT NULL时，可以支持索引扫描。

testdb=# insert into t_null select x,'c1'||x fromgenerate_series(1,10000) as x; INSERT 0 10000testdb =# 插入 t_null 值(null,null);INSERT 0 1testdb=# testdb=# 在 t_null(id) 上创建索引 idx_t_null_id;CREATE INDEXtestdb=# 分析 t_null;ANALYZEtestdb=# testdb=# 解释详细 select * from t_null，其中 id 为 null； ------------------------------------- 在 public.t_null 上使用 idx_t_null_id 进行索引扫描（成本= 0.29. .8.30 rows=1 width=10) 输出：id, c1 索引条件：(t_null.id IS NULL)(3 rows)testdb=# 解释详细 select * from t_null where id is not null;;--------------------- 对 public.t_null 进行序列扫描 (cost=0.00..155.01 rows=10000 width=10) 输出：id, c1 过滤器：(t_null .id IS NOT NULL)(3 行)testdb=# testdb=# truncate t_null;TRUNCATE TABLEtestdb=# 插入 t_null select null,null fromgenerate_series(1,10000);INSERT 0 10000testdb=# 插入 t_null 值(1, '1');INSERT 0 1testdb=# 分析 t_null;ANALYZEtestdb=# testdb=# 解释详细 select * from t_null 其中 id 为 null; ----------------------- public.t_null 上的顺序扫描（成本=0.00..135.01 行=10000 宽度=6） 输出：id, c1 过滤器： (t_null.id IS NULL)(3 rows)testdb=# 解释详细 select * from t_null where id is not null;sp;PLAN                                                                                                                           ------------------------ --------------- 在 public.t_null 上使用 idx_t_null_id 进行索引扫描（成本=0.29.. 8.30 rows=1 width=6) 输出：id, c1 索引条件：(t_null.id IS NOT NULL)(3 rows)testdb=#

NULL可以保存在前面索引，也可以保存在最后，可以通过FIRST/LAST关键字指定，这会对排序产生影响。

testdb=# create table t_null_sort(id int,c1 varchar (20));CREATE TABLEtestdb=# testdb =# 插入 t_null_sort select x,'c1'||x fromgenerate_series(1,10000) as x;INSERT 0 10000testdb=# 插入 t_null_sort 值(null,null);INSERT 0 1testdb=# testdb=# 在 t_null_sort 上创建索引 idx_t_null_id_first（id 为空）；CREATE INDEXtestdb=# 在 t_null_sort 上创建索引 idx_t_null_id_last（id 为空最后）；CREATE INDEXtestdb=# testdb= #analyze t_null_sort；ANALYZEtestdb =# testdb=# 解释详细 select * from t_null_sort 首先按 id null 排序； ——————————————————— ------------------------------------------------- - - 使用 idx_t_null_id_fir 进行索引扫描st on public.t_null_sort (cost=0.29..328.30 rows=10001 width=10) 输出：id, c1(2 rows)testdb=# 解释详细 select * from t_null_sort order by id nulls last;查询计划 ------------------------------------------------ -------------------------------------------------- -- 在 public.t_null_sort 上使用 idx_t_null_id_last 进行索引扫描 (cost=0.29..328.30 rows=10001 width=10) 输出：id, c1(2 rows)testdb=# testdb=#

INCLUDE
创建索引时，可以使用INCLUDE将非索引字段添加到索引中。扫描索引时，如果投影列只包含索引列和INCLUDE列，可以通过INDEX ONLY SCAN扫描Fetch。数据。

testdb=# 创建表 t_include(id int,c1 varchar(20),c2 varchar(20),c3 varchar(20));CREATE TABLEtestdb=# testdb=# insert into t_include(id ,c1,c2) ​​select x,'c1'||x,'c2'||x fromgenerate_series(1,10000) as x;INSERT 0 10000testdb=# testdb=# 在 t_include(id) incl 上创建索引 idx_t_include_idude (c1 );CREATE INDEXtestdb=# testdb=# 分析 t_include;ANALYZEtestdb=# 解释详细选择 id,c1 from t_include; ----------------------------------------------------------- 顺序扫描开启public.t_include (cost= 0.00..163.00 rows=10000 width=10) 输出： id, c1(2 rows)testdb=# testdb=# suggest verbose select id,c1 from t_include where id = 1;p; --------------------------------------------------仅索引使用 idx_t_include_id 对 public.t_include 进行扫描 (cost=0.29..8.30 rows=1 width=10) 输出：id, c1 索引条件：(t_include.id = 1)(3 rows)testdb=#

testdb=# create type complex as (re float, im float);CREATE TYPEtestdb=# create table Numbers (xcomplex);CREATE TABLEtestdb=# 插入数字值((0.0, 10.0)), ((1.0, 3.0)), ((1.0, 1.0));INSERT 0 3testdb=# select * from Numbers order by X; x - ------- (0,10) (1,1) (1,3)(3行)

创建比较函数

testdb=# testdb= # 创建函数modulus(a complex) 返回浮点数为 $$testdb$# select sqrt(a.re*a.re + a.im*a.im);testdb$# $$ 不可变语言 sql;CREATE FUNCTIONtestdb=# testdb=# create函数complex_lt(acomplex,bcomplex)返回布尔值$$testdb$#选择模数(a)<模数(b);testdb$#$$不可变语言sql;CREATE FUNCTIONtestdb=#testdb=#创建函数complex_le(acomplex , b 复数) 返回布尔值 $$testdb$# 选择模数(a) <= 模数(b);testdb$# $$ 不可变语言 sql;CREATE FUNCTIONtestdb=# testdb=# 创建函数 complex_eq(a 复数, b 复数)将布尔值返回为 $$testdb$# select modulus(a) = modulus(b);testdb$# $$ 不可变语言 sql;CREATE FUNCTIONtestdb=# testdb=# create function complex_ge(a Complex, b Complex) 将布尔值返回为 $$ testdb$# select modulus(a) >= modulus(b);testdb$# $$ 不可变语言 sql;CREATE FUNCTIONtestdb=# testdb=# create function complex_gt(a 复数, b 复数) 返回布尔值作为 $$testdb$# select模数(a) > 模数(b);testdb$# $$ 不可变的 language sql;CREATE FUNCTION

创建运算符

testdb=# 创建运算符 <(leftarg=complex, rightarg=complex, procedure=complex_lt);CREATE OPERATORtestdb=# testdb=# 创建运算符<=(leftarg=complex, rightarg=complex, procedure=complex_le);arg=complex, rightarg=complex, procedure =complex_gt);CREATE OPERATORtestdb=# testdb=# 创建操作符 =(leftarg=complex, rightarg=complex, procedure= complex_eq);CREATE OPERATORtestdb=# testdb=# 创建运算符 >=(leftarg=complex, rightarg=complex, procedure=complex_ge);CREATE OPERATORtestdb=# testdb=# 创建运算符 >(leftarg=complex, rightarg=complex, procedure=complex_gt );CREATE OPERATORtestdb=#

现在可以比较复数了：

testdb=# select (1.0,1.0)::complex < (1.0,3.0)::complex; ？柱子？ ---------- t(1 row)

创建比较函数和opc。创建opc时，pg会自动创建一个同名的opf

testdb=# create function complex_cmp(a complex, b complex ) returns integer as $$testdb$# 当 modulus(a) < modulus(b) then -1testdb$# 当 modulus(a) > modulus(b) then 1 testdb$# else 0testd b$#             end;testdb$# $$ language sql; CREATE FUNCTIONtestdb=# 创建运算符类complex_opstestdb-# 类型的默认值complextestdb-# 使用btree astestdb-# 运算符 1 <,testdb-# 运算符 2 <=,testdb-# 运算符 3 =,testdb-# 运算符 4 >=,testdb- # 操作符 5 >,testdb-# 函数 1 complex_cmp(complex,complex);创建操作符类testdb=# select * from pg_opfamily where opfname = 'complex_ops';类 |操作方法 |操作名称 | opf 命名空间 |所有者 --------+--------- --+-------------+---------------- +---------- 106585 | 403 | 403复杂操作 | 2200 | 2200 10（1行）

您现在可以创建数据类型为complex的Btree索引

testdb=# select amp.amprocnum,testdb-# amp.amproc,testdb-# amp .amproclefttype::regtype,testdb-# amp.amprocrighttype::regtypetestdb-# 来自 pg_opfamily opf,testdb-# pg_am am,testdb-# pg_amproc amptestdb-# 其中 opf.opfname ='complex_ops'testdb-# 和 opf.opfmethod = am.oidtestdb-# 和 am.amname = 'btree'testdb-# 和 amp.amprocfamily = opf.oid;氨普罗努 |安普洛克| amprocleft 类型 | amprocrighttype -----------+-------- -------+----------------+-------- ----------- 1 |复杂_cmp |复杂|复杂(1行)testdb=#在数字(x)上创建索引idx_numbers_x;CREATE INDEXtestdb=#分析数字;ANALYZEtestdb=#解释select * fromnumbers order by x;​​​​​​​​​​​​​​ ​​​​​​​​​​---------------------------------------- ------- 排序（成本= 1.14..1.15 行=6 宽度=37） 排序键：x -> 顺序扫描数字（成本=0.00..1.06 行=6 宽度=37）（3 行） testdb=# set enable_seqscan=off;SETtestdb=# 解释 select * from Numbers order by x;计划  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  - - - -------------------------- 仅索引使用 idx_numbers_x 对数字进行扫描（成本=0.13..12.22 行=6 宽度=37）（1 row )

至此，相信大家对“Bt的作用是什么”有了更深入的了解PostgreSQL中的ree索引》，不妨实践一下吧！这是网站。更多相关内容，可以进入相关频道咨询，关注我们，继续学习！