PostgreSQL中的Btree索引有什么作用-創(chuàng)新互聯(lián)

本篇內(nèi)容主要講解“PostgreSQL中的Btree索引有什么作用”，感興趣的朋友不妨來看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來帶大家學(xué)習(xí)“PostgreSQL中的Btree索引有什么作用”吧!

創(chuàng)新互聯(lián)公司總部坐落于成都市區(qū)，致力網(wǎng)站建設(shè)服務(wù)有網(wǎng)站建設(shè)、成都網(wǎng)站建設(shè)、網(wǎng)絡(luò)營(yíng)銷策劃、網(wǎng)頁設(shè)計(jì)、網(wǎng)站維護(hù)、公眾號(hào)搭建、小程序開發(fā)、軟件開發(fā)等為企業(yè)提供一整套的信息化建設(shè)解決方案。創(chuàng)造真正意義上的網(wǎng)站建設(shè)，為互聯(lián)網(wǎng)品牌在互動(dòng)行銷領(lǐng)域創(chuàng)造價(jià)值而不懈努力！

結(jié)構(gòu)
Btree是常見的數(shù)據(jù)結(jié)構(gòu),有以下特性:
1.Btree是平衡樹,以root節(jié)點(diǎn)為分界,左右兩邊的中間節(jié)點(diǎn)數(shù)目一樣,也就是說查詢?nèi)我庖粋€(gè)值,時(shí)間都是一樣的
2.Btree有多個(gè)分支,每個(gè)page(8KB)可以有數(shù)百個(gè)TIDs,也就是說Btree只需要不多的幾個(gè)層次就可以支持行數(shù)巨大的表
3.索引中的數(shù)據(jù)Page之間和Page內(nèi)部都是有序的,相同層次的Page通過雙向鏈表彼此連接

NULLs
PostgreSQL在創(chuàng)建索引時(shí)會(huì)存儲(chǔ)NULLs,因此條件為IS NULL和IS NOT NULL時(shí)可以支持索引掃描.

testdb=# insert into t_null select x,'c1'||x from generate_series(1,10000) as x;
INSERT 0 10000
testdb=# insert into t_null values(null,null);
INSERT 0 1
testdb=# 
testdb=# create index idx_t_null_id on t_null(id);
CREATE INDEX
testdb=# analyze t_null;
ANALYZE
testdb=# 
testdb=# explain verbose select * from t_null where id is null;
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Index Scan using idx_t_null_id on public.t_null  (cost=0.29..8.30 rows=1 width=10)
   Output: id, c1
   Index Cond: (t_null.id IS NULL)
(3 rows)
testdb=# explain verbose select * from t_null where id is not null;
                             QUERY PLAN                             
--------------------------------------------------------------------
 Seq Scan on public.t_null  (cost=0.00..155.01 rows=10000 width=10)
   Output: id, c1
   Filter: (t_null.id IS NOT NULL)
(3 rows)
testdb=# 
testdb=# truncate t_null;
TRUNCATE TABLE
testdb=# insert into t_null select null,null from generate_series(1,10000);
INSERT 0 10000
testdb=# insert into t_null values(1,'1');
INSERT 0 1
testdb=# analyze t_null;
ANALYZE
testdb=# 
testdb=# explain verbose select * from t_null where id is null;
                            QUERY PLAN                             
-------------------------------------------------------------------
 Seq Scan on public.t_null  (cost=0.00..135.01 rows=10000 width=6)
   Output: id, c1
   Filter: (t_null.id IS NULL)
(3 rows)
testdb=# explain verbose select * from t_null where id is not null;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Index Scan using idx_t_null_id on public.t_null  (cost=0.29..8.30 rows=1 width=6)
   Output: id, c1
   Index Cond: (t_null.id IS NOT NULL)
(3 rows)
testdb=#

NULLs可以保存在Index的最前面,也可以保存在最后面,可通過FIRST/LAST關(guān)鍵字指定,這對(duì)排序會(huì)有所影響.

testdb=# create table t_null_sort(id int,c1 varchar(20));
CREATE TABLE
testdb=# 
testdb=# insert into t_null_sort select x,'c1'||x from generate_series(1,10000) as x;
INSERT 0 10000
testdb=# insert into t_null_sort values(null,null);
INSERT 0 1
testdb=# 
testdb=# create index idx_t_null_id_first on t_null_sort(id nulls first);
CREATE INDEX
testdb=# create index idx_t_null_id_last on t_null_sort(id nulls last);
CREATE INDEX
testdb=# 
testdb=# analyze t_null_sort;
ANALYZE
testdb=# 
testdb=# explain verbose select * from t_null_sort order by id nulls first;
                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 Index Scan using idx_t_null_id_first on public.t_null_sort  (cost=0.29..328.30 rows=10001 width=10)
   Output: id, c1
(2 rows)
testdb=# explain verbose select * from t_null_sort order by id nulls last;
                                             QUERY PLAN                                             
----------------------------------------------------------------------------------------------------
 Index Scan using idx_t_null_id_last on public.t_null_sort  (cost=0.29..328.30 rows=10001 width=10)
   Output: id, c1
(2 rows)
testdb=# 
testdb=#

INCLUDE
創(chuàng)建索引時(shí),通過使用INCLUDE可以把非索引字段加入到該索引中,在通過索引掃描時(shí)如投影列只包含索引列和INCLUDE列,那么可以通過INDEX ONLY SCAN掃描Fetch數(shù)據(jù).

testdb=# create table t_include(id int,c1 varchar(20),c2 varchar(20),c3 varchar(20));
CREATE TABLE
testdb=# 
testdb=# insert into t_include(id,c1,c2) select x,'c1'||x,'c2'||x from generate_series(1,10000) as x;
INSERT 0 10000
testdb=# 
testdb=# create index idx_t_include_id on t_include(id) include (c1);
CREATE INDEX
testdb=# 
testdb=# analyze t_include;
ANALYZE
testdb=# explain verbose select id,c1 from t_include;
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Seq Scan on public.t_include  (cost=0.00..163.00 rows=10000 width=10)
   Output: id, c1
(2 rows)
testdb=# 
testdb=# explain verbose select id,c1 from t_include where id = 1;
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Index Only Scan using idx_t_include_id on public.t_include  (cost=0.29..8.30 rows=1 width=10)
   Output: id, c1
   Index Cond: (t_include.id = 1)
(3 rows)
testdb=#

New Data Type
創(chuàng)建類型complex以及數(shù)據(jù)表

testdb=# create type complex as (re float, im float);
CREATE TYPE
testdb=# create table numbers(x complex);
CREATE TABLE
testdb=# insert into numbers values ((0.0, 10.0)), ((1.0, 3.0)), ((1.0, 1.0));
INSERT 0 3
testdb=# select * from numbers order by x;
   x    
--------
 (0,10)
 (1,1)
 (1,3)
(3 rows)

創(chuàng)建比較函數(shù)

testdb=# 
testdb=# create function modulus(a complex) returns float as $$
testdb$#     select sqrt(a.re*a.re + a.im*a.im);
testdb$# $$ immutable language sql;
CREATE FUNCTION
testdb=# 
testdb=# create function complex_lt(a complex, b complex) returns boolean as $$
testdb$#     select modulus(a) < modulus(b);
testdb$# $$ immutable language sql;
CREATE FUNCTION
testdb=# 
testdb=# create function complex_le(a complex, b complex) returns boolean as $$
testdb$#     select modulus(a) <= modulus(b);
testdb$# $$ immutable language sql;
CREATE FUNCTION
testdb=# 
testdb=# create function complex_eq(a complex, b complex) returns boolean as $$
testdb$#     select modulus(a) = modulus(b);
testdb$# $$ immutable language sql;
CREATE FUNCTION
testdb=# 
testdb=# create function complex_ge(a complex, b complex) returns boolean as $$
testdb$#     select modulus(a) >= modulus(b);
testdb$# $$ immutable language sql;
CREATE FUNCTION
testdb=# 
testdb=# create function complex_gt(a complex, b complex) returns boolean as $$
testdb$#     select modulus(a) > modulus(b);
testdb$# $$ immutable language sql;
CREATE FUNCTION

創(chuàng)建operator

testdb=# create operator <(leftarg=complex, rightarg=complex, procedure=complex_lt);
CREATE OPERATOR
testdb=# 
testdb=# create operator <=(leftarg=complex, rightarg=complex, procedure=complex_le);
arg=complex, rightarg=complex, procedure=complex_gt);
CREATE OPERATOR
testdb=# 
testdb=# create operator =(leftarg=complex, rightarg=complex, procedure=complex_eq);
CREATE OPERATOR
testdb=# 
testdb=# create operator >=(leftarg=complex, rightarg=complex, procedure=complex_ge);
CREATE OPERATOR
testdb=# 
testdb=# create operator >(leftarg=complex, rightarg=complex, procedure=complex_gt);
CREATE OPERATOR
testdb=#

現(xiàn)在可以對(duì)complex進(jìn)行比較了:

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

創(chuàng)建比較函數(shù)和opc,在創(chuàng)建opc的時(shí)候,pg會(huì)自動(dòng)創(chuàng)建同名的opf

testdb=# create function complex_cmp(a complex, b complex) returns integer as $$
testdb$#     select case when modulus(a) < modulus(b) then -1
testdb$#                 when modulus(a) > modulus(b) then 1 
testdb$#                 else 0
testdb$#            end;
testdb$# $$ language sql;
CREATE FUNCTION
testdb=# create operator class complex_ops
testdb-# default for type complex
testdb-# using btree as
testdb-#     operator 1 <,
testdb-#     operator 2 <=,
testdb-#     operator 3 =,
testdb-#     operator 4 >=,
testdb-#     operator 5 >,
testdb-#     function 1 complex_cmp(complex,complex);
CREATE OPERATOR CLASS
testdb=# select * from pg_opfamily where opfname = 'complex_ops';
  oid   | opfmethod |   opfname   | opfnamespace | opfowner 
--------+-----------+-------------+--------------+----------
 106585 |       403 | complex_ops |         2200 |       10
(1 row)

現(xiàn)在可以創(chuàng)建數(shù)據(jù)類型為complex的Btree索引

testdb=# select amp.amprocnum,
testdb-#        amp.amproc,
testdb-#        amp.amproclefttype::regtype,
testdb-#        amp.amprocrighttype::regtype
testdb-# from   pg_opfamily opf,
testdb-#        pg_am am,
testdb-#        pg_amproc amp
testdb-# where  opf.opfname = 'complex_ops'
testdb-# and    opf.opfmethod = am.oid
testdb-# and    am.amname = 'btree'
testdb-# and    amp.amprocfamily = opf.oid;
 amprocnum |   amproc    | amproclefttype | amprocrighttype 
-----------+-------------+----------------+-----------------
         1 | complex_cmp | complex        | complex
(1 row)
testdb=# create index idx_numbers_x on numbers(x);
CREATE INDEX
testdb=# analyze numbers;
ANALYZE
testdb=# explain select * from numbers order by x;
                          QUERY PLAN                          
--------------------------------------------------------------
 Sort  (cost=1.14..1.15 rows=6 width=37)
   Sort Key: x
   ->  Seq Scan on numbers  (cost=0.00..1.06 rows=6 width=37)
(3 rows)
testdb=# set enable_seqscan=off;
SET
testdb=# explain select * from numbers order by x;
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Index Only Scan using idx_numbers_x on numbers  (cost=0.13..12.22 rows=6 width=37)
(1 row)

到此，相信大家對(duì)“PostgreSQL中的Btree索引有什么作用”有了更深的了解，不妨來實(shí)際操作一番吧！這里是創(chuàng)新互聯(lián)網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！

當(dāng)前文章：PostgreSQL中的Btree索引有什么作用-創(chuàng)新互聯(lián)
文章地址：http://www.muchs.cn/article16/ipedg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供Google、面包屑導(dǎo)航、自適應(yīng)網(wǎng)站、建站公司、虛擬主機(jī)、ChatGPT

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)

成都網(wǎng)頁設(shè)計(jì)公司