多字段索引
更新時間(jian) 2025-02-14 10:21:45
最(zui)近更新時間: 2025-02-14 10:21:45
分(fen)享文章
本文(wen)為(wei)您介(jie)紹(shao)如(ru)何使用多字段索引。
teledb=# create table t_mul_idx (f1 int,f2 int,f3 int,f4 int);
CREATE TABLE
teledb=# create index t_mul_idx_idx on t_mul_idx(f1,f2,f3);
CREATE INDEX多字段使用(yong)注意(yi)事項：
or 查詢條件(jian) bitmap scan 最多(duo)支持兩個不同字(zi)段(duan)條件(jian)。
teledb=# insert into t_mul_idx select t,t,t,t from generate_series(1,1000000) as t; INSERT 0 1000000 teledb=# analyze ; ANALYZE teledb=# explain select * from t_mul_idx where f1=1 or f2=2 ; QUERY PLAN ------------------------------------------------------------------------------------------- Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0) Node/s: dn001, dn002 -> Bitmap Heap Scan on t_mul_idx (cost=7617.08..7621.07 rows=2 width=16) Recheck Cond: ((f1 = 1) OR (f2 = 2)) -> BitmapOr (cost=7617.08..7617.08 rows=2 width=0) -> Bitmap Index Scan on t_mul_idx_idx (cost=0.00..2.43 rows=1 width=0) Index Cond: (f1 = 1) -> Bitmap Index Scan on t_mul_idx_idx (cost=0.00..7614.65 rows=1 width=0) Index Cond: (f2 = 2) (9 rows) Time: 5.134 ms teledb=# explain select * from t_mul_idx where f1=1 or f2=2 or f1=3 ; QUERY PLAN ------------------------------------------------------------------------------------------- Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0) Node/s: dn001, dn002 -> Bitmap Heap Scan on t_mul_idx (cost=7619.51..7625.49 rows=3 width=16) Recheck Cond: ((f1 = 1) OR (f2 = 2) OR (f1 = 3)) -> BitmapOr (cost=7619.51..7619.51 rows=3 width=0) -> Bitmap Index Scan on t_mul_idx_idx (cost=0.00..2.43 rows=1 width=0) Index Cond: (f1 = 1) -> Bitmap Index Scan on t_mul_idx_idx (cost=0.00..7614.65 rows=1 width=0) Index Cond: (f2 = 2) -> Bitmap Index Scan on t_mul_idx_idx (cost=0.00..2.43 rows=1 width=0) Index Cond: (f1 = 3) (11 rows) Time: 5.048 ms teledb=# explain select * from t_mul_idx where f1=1 or f2=2 or f3=3 ; QUERY PLAN -------------------------------------------------------------------- Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0) Node/s: dn001, dn002 -> Seq Scan on t_mul_idx (cost=0.00..12966.87 rows=3 width=16) Filter: ((f1 = 1) OR (f2 = 2) OR (f3 = 3)) (4 rows) Time: 3.153 ms如果返回字段全(quan)部在索(suo)(suo)引文件中，則只需要掃描索(suo)(suo)引，IO 開銷會更(geng)少(shao)。
teledb=# explain select f1,f2,f3 from t_mul_idx where f1=1 ; QUERY PLAN ------------------------------------------------------------------------------------------- Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0) Node/s: dn01 -> Index Only Scan using t_mul_idx_idx on t_mul_idx (cost=0.42..8.44 rows=1 width=12) Index Cond: (f1 = 1) (4 rows) Time: 2.630 ms更新性(xing)能(neng)比(bi)單字段多(duo)索引(yin)文件要(yao)好(hao)
多字段
teledb=# truncate table t_mul_idx; TRUNCATE TABLE Time: 44.930 ms teledb=# insert into t_mul_idx select t,t,t,t from generate_series(1,1000000) as t; INSERT 0 1000000 Time: 1985.901 ms (00:01.986)單字(zi)段
teledb=# create table t_simple_idx (f1 int,f2 int,f3 int,f4 int); CREATE TABLE teledb=# create index t_simple_idx_idx on t_simple_idx(f1); CREATE INDEX teledb=# create index t_simple_idx_idx1 on t_simple_idx(f2); CREATE INDEX teledb=# create index t_simple_idx_idx2 on t_simple_idx(f3); CREATE INDEX teledb=# insert into t_simple_idx select t,t,t,t from generate_series(1,1000000) as t; INSERT 0 1000000 Time: 4186.401 ms (00:04.186)多字段索引走非第一(yi)字段查詢(xun)時性能比(bi)獨立(li)的單字段差(cha)。
多(duo)字段
teledb=# select * from t_mul_idx where f1=1; f1 | f2 | f3 | f4 ----+----+----+---- 1 | 1 | 1 | 1 (1 row) Time: 1.034 ms teledb=# select * from t_mul_idx where f2=1; f1 | f2 | f3 | f4 ----+----+----+---- 1 | 1 | 1 | 1 (1 row) Time: 37.072 ms teledb=# select * from t_mul_idx where f3=1; f1 | f2 | f3 | f4 ----+----+----+---- 1 | 1 | 1 | 1 (1 row) Time: 36.229 ms單字段
teledb=# select * from t_simple_idx where f1=1; f1 | f2 | f3 | f4 ----+----+----+---- 1 | 1 | 1 | 1 (1 row) Time: 2.610 ms teledb=# select * from t_simple_idx where f2=1; f1 | f2 | f3 | f4 ----+----+----+---- 1 | 1 | 1 | 1 (1 row) Time: 3.266 ms teledb=# select * from t_simple_idx where f3=1; f1 | f2 | f3 | f4 ----+----+----+---- 1 | 1 | 1 | 1 (1 row) Time: 1.316 ms
