本篇内容主要讲解“PostgreSQL中的Btree索引有什么作用”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“PostgreSQL中的Btree索引有什么作用”吧!

结构
Btree是常见的数据结构,有以下特性:
1.Btree是平衡树,以root节点为分界,左右两边的中间节点数目一样,也就是说查询任意一个值,时间都是一样的
2.Btree有多个分支,每个page(8KB)可以有数百个TIDs,也就是说Btree只需要不多的几个层次就可以支持行数巨大的表
3.索引中的数据Page之间和Page内部都是有序的,相同层次的Page通过双向链表彼此连接

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

testdb=#insertintot_nullselectx,'c1'||xfromgenerate_series(1,10000)asx;INSERT010000testdb=#insertintot_nullvalues(null,null);INSERT01testdb=#testdb=#createindexidx_t_null_idont_null(id);CREATEINDEXtestdb=#analyzet_null;ANALYZEtestdb=#testdb=#explainverboseselect*fromt_nullwhereidisnull;QUERYPLAN------------------------------------------------------------------------------------IndexScanusingidx_t_null_idonpublic.t_null(cost=0.29..8.30rows=1width=10)Output:id,c1IndexCond:(t_null.idISNULL)(3rows)testdb=#explainverboseselect*fromt_nullwhereidisnotnull;QUERYPLAN--------------------------------------------------------------------SeqScanonpublic.t_null(cost=0.00..155.01rows=10000width=10)Output:id,c1Filter:(t_null.idISNOTNULL)(3rows)testdb=#testdb=#truncatet_null;TRUNCATETABLEtestdb=#insertintot_nullselectnull,nullfromgenerate_series(1,10000);INSERT010000testdb=#insertintot_nullvalues(1,'1');INSERT01testdb=#analyzet_null;ANALYZEtestdb=#testdb=#explainverboseselect*fromt_nullwhereidisnull;QUERYPLAN-------------------------------------------------------------------SeqScanonpublic.t_null(cost=0.00..135.01rows=10000width=6)Output:id,c1Filter:(t_null.idISNULL)(3rows)testdb=#explainverboseselect*fromt_nullwhereidisnotnull;QUERYPLAN-----------------------------------------------------------------------------------IndexScanusingidx_t_null_idonpublic.t_null(cost=0.29..8.30rows=1width=6)Output:id,c1IndexCond:(t_null.idISNOTNULL)(3rows)testdb=#

NULLs可以保存在Index的最前面,也可以保存在最后面,可通过FIRST/LAST关键字指定,这对排序会有所影响.

testdb=#createtablet_null_sort(idint,c1varchar(20));CREATETABLEtestdb=#testdb=#insertintot_null_sortselectx,'c1'||xfromgenerate_series(1,10000)asx;INSERT010000testdb=#insertintot_null_sortvalues(null,null);INSERT01testdb=#testdb=#createindexidx_t_null_id_firstont_null_sort(idnullsfirst);CREATEINDEXtestdb=#createindexidx_t_null_id_lastont_null_sort(idnullslast);CREATEINDEXtestdb=#testdb=#analyzet_null_sort;ANALYZEtestdb=#testdb=#explainverboseselect*fromt_null_sortorderbyidnullsfirst;QUERYPLAN-----------------------------------------------------------------------------------------------------IndexScanusingidx_t_null_id_firstonpublic.t_null_sort(cost=0.29..328.30rows=10001width=10)Output:id,c1(2rows)testdb=#explainverboseselect*fromt_null_sortorderbyidnullslast;QUERYPLAN----------------------------------------------------------------------------------------------------IndexScanusingidx_t_null_id_lastonpublic.t_null_sort(cost=0.29..328.30rows=10001width=10)Output:id,c1(2rows)testdb=#testdb=#

INCLUDE
创建索引时,通过使用INCLUDE可以把非索引字段加入到该索引中,在通过索引扫描时如投影列只包含索引列和INCLUDE列,那么可以通过INDEX ONLY SCAN扫描Fetch数据.

testdb=#createtablet_include(idint,c1varchar(20),c2varchar(20),c3varchar(20));CREATETABLEtestdb=#testdb=#insertintot_include(id,c1,c2)selectx,'c1'||x,'c2'||xfromgenerate_series(1,10000)asx;INSERT010000testdb=#testdb=#createindexidx_t_include_idont_include(id)include(c1);CREATEINDEXtestdb=#testdb=#analyzet_include;ANALYZEtestdb=#explainverboseselectid,c1fromt_include;QUERYPLAN-----------------------------------------------------------------------SeqScanonpublic.t_include(cost=0.00..163.00rows=10000width=10)Output:id,c1(2rows)testdb=#testdb=#explainverboseselectid,c1fromt_includewhereid=1;QUERYPLAN-----------------------------------------------------------------------------------------------IndexOnlyScanusingidx_t_include_idonpublic.t_include(cost=0.29..8.30rows=1width=10)Output:id,c1IndexCond:(t_include.id=1)(3rows)testdb=#

New Data Type
创建类型complex以及数据表

testdb=#createtypecomplexas(refloat,imfloat);CREATETYPEtestdb=#createtablenumbers(xcomplex);CREATETABLEtestdb=#insertintonumbersvalues((0.0,10.0)),((1.0,3.0)),((1.0,1.0));INSERT03testdb=#select*fromnumbersorderbyx;x--------(0,10)(1,1)(1,3)(3rows)

创建比较函数

testdb=#testdb=#createfunctionmodulus(acomplex)returnsfloatas$$testdb$#selectsqrt(a.re*a.re+a.im*a.im);testdb$#$$immutablelanguagesql;CREATEFUNCTIONtestdb=#testdb=#createfunctioncomplex_lt(acomplex,bcomplex)returnsbooleanas$$testdb$#selectmodulus(a)<modulus(b);testdb$#$$immutablelanguagesql;CREATEFUNCTIONtestdb=#testdb=#createfunctioncomplex_le(acomplex,bcomplex)returnsbooleanas$$testdb$#selectmodulus(a)<=modulus(b);testdb$#$$immutablelanguagesql;CREATEFUNCTIONtestdb=#testdb=#createfunctioncomplex_eq(acomplex,bcomplex)returnsbooleanas$$testdb$#selectmodulus(a)=modulus(b);testdb$#$$immutablelanguagesql;CREATEFUNCTIONtestdb=#testdb=#createfunctioncomplex_ge(acomplex,bcomplex)returnsbooleanas$$testdb$#selectmodulus(a)>=modulus(b);testdb$#$$immutablelanguagesql;CREATEFUNCTIONtestdb=#testdb=#createfunctioncomplex_gt(acomplex,bcomplex)returnsbooleanas$$testdb$#selectmodulus(a)>modulus(b);testdb$#$$immutablelanguagesql;CREATEFUNCTION

创建operator

testdb=#createoperator<(leftarg=complex,rightarg=complex,procedure=complex_lt);CREATEOPERATORtestdb=#testdb=#createoperator<=(leftarg=complex,rightarg=complex,procedure=complex_le);arg=complex,rightarg=complex,procedure=complex_gt);CREATEOPERATORtestdb=#testdb=#createoperator=(leftarg=complex,rightarg=complex,procedure=complex_eq);CREATEOPERATORtestdb=#testdb=#createoperator>=(leftarg=complex,rightarg=complex,procedure=complex_ge);CREATEOPERATORtestdb=#testdb=#createoperator>(leftarg=complex,rightarg=complex,procedure=complex_gt);CREATEOPERATORtestdb=#

现在可以对complex进行比较了:

testdb=#select(1.0,1.0)::complex<(1.0,3.0)::complex;?column?----------t(1row)

创建比较函数和opc,在创建opc的时候,pg会自动创建同名的opf

testdb=#createfunctioncomplex_cmp(acomplex,bcomplex)returnsintegeras$$testdb$#selectcasewhenmodulus(a)<modulus(b)then-1testdb$#whenmodulus(a)>modulus(b)then1testdb$#else0testdb$#end;testdb$#$$languagesql;CREATEFUNCTIONtestdb=#createoperatorclasscomplex_opstestdb-#defaultfortypecomplextestdb-#usingbtreeastestdb-#operator1<,testdb-#operator2<=,testdb-#operator3=,testdb-#operator4>=,testdb-#operator5>,testdb-#function1complex_cmp(complex,complex);CREATEOPERATORCLASStestdb=#select*frompg_opfamilywhereopfname='complex_ops';oid|opfmethod|opfname|opfnamespace|opfowner--------+-----------+-------------+--------------+----------106585|403|complex_ops|2200|10(1row)

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

testdb=#selectamp.amprocnum,testdb-#amp.amproc,testdb-#amp.amproclefttype::regtype,testdb-#amp.amprocrighttype::regtypetestdb-#frompg_opfamilyopf,testdb-#pg_amam,testdb-#pg_amprocamptestdb-#whereopf.opfname='complex_ops'testdb-#andopf.opfmethod=am.oidtestdb-#andam.amname='btree'testdb-#andamp.amprocfamily=opf.oid;amprocnum|amproc|amproclefttype|amprocrighttype-----------+-------------+----------------+-----------------1|complex_cmp|complex|complex(1row)testdb=#createindexidx_numbers_xonnumbers(x);CREATEINDEXtestdb=#analyzenumbers;ANALYZEtestdb=#explainselect*fromnumbersorderbyx;QUERYPLAN--------------------------------------------------------------Sort(cost=1.14..1.15rows=6width=37)SortKey:x->SeqScanonnumbers(cost=0.00..1.06rows=6width=37)(3rows)testdb=#setenable_seqscan=off;SETtestdb=#explainselect*fromnumbersorderbyx;QUERYPLAN------------------------------------------------------------------------------------IndexOnlyScanusingidx_numbers_xonnumbers(cost=0.13..12.22rows=6width=37)(1row)

到此，相信大家对“PostgreSQL中的Btree索引有什么作用”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！