多字段索引
更新時間 2025-02-14 10:21:50
最近更新時間: 2025-02-14 10:21:50
分享文章
本文為您介紹如何使用多字段索引。
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多字段使用注意事項：
or 查詢條件 bitmap scan 最多支持兩個不同字段條件。
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如果返回字段全部在索引文件中，則只需要掃描索引，IO 開銷會更少。
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更新性能比單字段多索引文件要好。
多字段
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)單字段
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)
多字段索引走非第一字段查詢時性能比獨立的單字段差。
多字段
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
