本篇內(nèi)容主要講解“PostgreSQL中的Btree索引有什么作用”,感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷,實用性強。下面就讓小編來帶大家學(xué)習(xí)“PostgreSQL中的Btree索引有什么作用”吧!
創(chuàng)新互聯(lián)公司總部坐落于成都市區(qū),致力網(wǎng)站建設(shè)服務(wù)有網(wǎng)站建設(shè)、成都網(wǎng)站建設(shè)、網(wǎng)絡(luò)營銷策劃、網(wǎng)頁設(shè)計、網(wǎng)站維護、公眾號搭建、小程序開發(fā)、軟件開發(fā)等為企業(yè)提供一整套的信息化建設(shè)解決方案。創(chuàng)造真正意義上的網(wǎng)站建設(shè),為互聯(lián)網(wǎng)品牌在互動行銷領(lǐng)域創(chuàng)造價值而不懈努力!結(jié)構(gòu)
Btree是常見的數(shù)據(jù)結(jié)構(gòu),有以下特性:
1.Btree是平衡樹,以root節(jié)點為分界,左右兩邊的中間節(jié)點數(shù)目一樣,也就是說查詢?nèi)我庖粋€值,時間都是一樣的
2.Btree有多個分支,每個page(8KB)可以有數(shù)百個TIDs,也就是說Btree只需要不多的幾個層次就可以支持行數(shù)巨大的表
3.索引中的數(shù)據(jù)Page之間和Page內(nèi)部都是有序的,相同層次的Page通過雙向鏈表彼此連接
NULLs
PostgreSQL在創(chuàng)建索引時會存儲NULLs,因此條件為IS NULL和IS NOT NULL時可以支持索引掃描.
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)鍵字指定,這對排序會有所影響.
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)建索引時,通過使用INCLUDE可以把非索引字段加入到該索引中,在通過索引掃描時如投影列只包含索引列和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)在可以對complex進行比較了:
testdb=# select (1.0,1.0)::complex < (1.0,3.0)::complex; ?column? ---------- t (1 row)
創(chuàng)建比較函數(shù)和opc,在創(chuàng)建opc的時候,pg會自動創(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)
到此,相信大家對“PostgreSQL中的Btree索引有什么作用”有了更深的了解,不妨來實際操作一番吧!這里是創(chuàng)新互聯(lián)網(wǎng)站,更多相關(guān)內(nèi)容可以進入相關(guān)頻道進行查詢,關(guān)注我們,繼續(xù)學(xué)習(xí)!
當前文章:PostgreSQL中的Btree索引有什么作用-創(chuàng)新互聯(lián)
文章地址:http://bm7419.com/article16/ipedg.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供Google、面包屑導(dǎo)航、自適應(yīng)網(wǎng)站、建站公司、虛擬主機、ChatGPT
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時需注明來源: 創(chuàng)新互聯(lián)
猜你還喜歡下面的內(nèi)容