postgresql----唯一索引，表达式索引，部分索引

⼀.唯⼀索引

唯⼀索引字⾯上理解就是在索引上增加唯⼀约束，不允许出现索引值相同的⾏，⽬前只有Btree索引可以声明唯⼀索引，唯⼀键会⾃动创建唯⼀索引。测试表：

test=# create table tbl_unique_index(a int, b int);CREATE TABLE

⽰例1.创建唯⼀索引，相等数据只允许插⼊⼀⾏，NULL除外，因为NULL不等于NULL。

test=# create unique index idx_unq_tbl_unique_index_a_b on tbl_unique_index using btree (a,b);CREATE INDEX

test=# \\d tbl_unique_index Table \"public.tbl_unique_index\" Column | Type | Modifiers --------+---------+----------- a | integer | b | integer | Indexes:

\"idx_unq_tbl_unique_index_a_b\" UNIQUE, btree (a, b)

test=# insert into tbl_unique_index values (1,1);INSERT 0 1

test=# insert into tbl_unique_index values (1,1);

ERROR: duplicate key value violates unique constraint \"idx_unq_tbl_unique_index_a_b\"DETAIL: Key (a, b)=(1, 1) already exists.test=# insert into tbl_unique_index values (1);INSERT 0 1

test=# insert into tbl_unique_index values (1);INSERT 0 1

test=# insert into tbl_unique_index values (1);INSERT 0 1

⽰例2.唯⼀键会⾃动创建唯⼀索引

test=# truncate table tbl_unique_index ;TRUNCATE TABLE

test=# alter table tbl_unique_index add constraint pk_tbl_unique_index_a primary key(a);ALTER TABLE

test=# alter table tbl_unique_index add constraint uk_tbl_unique_index_b unique(b);ALTER TABLE

test=# \\d tbl_unique_index Table \"public.tbl_unique_index\" Column | Type | Modifiers --------+---------+----------- a | integer | not null b | integer | Indexes:

\"pk_tbl_unique_index_a\" PRIMARY KEY, btree (a) \"idx_unq_tbl_unique_index_a_b\" UNIQUE, btree (a, b)

\"uk_tbl_unique_index_b\" UNIQUE CONSTRAINT, btree (b)

⼆.表达式索引

除针对表的字段直接创建索引外，还可以对字段进⾏某种运算之后的结果创建索引。测试表

test=# create table tbl_expression(a varchar(32), b varchar(32));CREATE TABLE

test=# insert into tbl_expression select concat('test',x),concat('you',x) from generate_series(1,10000) x;INSERT 0 10000

如果此时分别在a和b字段上各创建⼀个Btree索引，分别使⽤a和b字段查询时会进⾏索引扫描。

test=# create index idx_tbl_expression_a on tbl_expression using btree (a);CREATE INDEX

test=# create index idx_tbl_expression_b on tbl_expression using btree (b);CREATE INDEXtest=#

test=# explain analyze select * from tbl_expression where a = 'TEST';

QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------- Index Scan using idx_tbl_expression_a on tbl_expression (cost=0.29..8.30 rows=1 width=15) (actual time=0.130..0.130 rows=0 loops=1) Index Cond: ((a)::text = 'TEST'::text) Planning time: 0.667 ms Execution time: 0.168 ms(4 rows)

test=# explain analyze select * from tbl_expression where b = 'you';

QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------- Index Scan using idx_tbl_expression_b on tbl_expression (cost=0.29..8.30 rows=1 width=15) (actual time=0.171..0.171 rows=0 loops=1) Index Cond: ((b)::text = 'you'::text) Planning time: 0.126 ms Execution time: 0.206 ms(4 rows)

但是下⾯的两种查询⽅式是不会进⾏索引扫描的

select * from tbl_expression where upper(a) = 'TEST';select * from tbl_expression where (a || ' ' ||b) = 'test you';

test=# explain analyze select * from tbl_expression where upper(a) = 'TEST';

QUERY PLAN ------------------------------------------------------------------------------------------------------------ Seq Scan on tbl_expression (cost=0.00..166.00 rows=50 width=15) (actual time=5.957..5.957 rows=0 loops=1) Filter: (upper((a)::text) = 'TEST'::text) Rows Removed by Filter: 10000 Planning time: 0.140 ms Execution time: 6.014 ms(5 rows)

test=#

test=# explain analyze select * from tbl_expression where (a || ' ' ||b) = 'test you'; QUERY PLAN ------------------------------------------------------------------------------------------------------------ Seq Scan on tbl_expression (cost=0.00..191.00 rows=50 width=15) (actual time=7.851..7.851 rows=0 loops=1) Filter: ((((a)::text || ' '::text) || (b)::text) = 'test you'::text) Rows Removed by Filter: 10000 Planning time: 0.114 ms Execution time: 7.883 ms(5 rows)

此时就可以使⽤表达式创建索引来解决此类全表扫描问题。

test=# explain analyze select * from tbl_expression where upper(a) = 'TEST';

QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Bitmap Heap Scan on tbl_expression (cost=4.67..21.42 rows=50 width=15) (actual time=0.133..0.133 rows=0 loops=1)

Recheck Cond: (upper((a)::text) = 'TEST'::text)

-> Bitmap Index Scan on idx_tbl_expression_upper_a (cost=0.00..4.66 rows=50 width=0) (actual time=0.129..0.129 rows=0 loops=1) Index Cond: (upper((a)::text) = 'TEST'::text) Planning time: 0.565 ms Execution time: 0.175 ms(6 rows)

test=# create index idx_tbl_expression_a_b on tbl_expression ((a||' '||b));CREATE INDEX

test=# explain analyze select * from tbl_expression where (a || ' ' ||b) = 'test you';

QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on tbl_expression (cost=4.67..21.55 rows=50 width=15) (actual time=0.130..0.130 rows=0 loops=1) Recheck Cond: ((((a)::text || ' '::text) || (b)::text) = 'test you'::text)

-> Bitmap Index Scan on idx_tbl_expression_a_b (cost=0.00..4.66 rows=50 width=0) (actual time=0.128..0.128 rows=0 loops=1) Index Cond: ((((a)::text || ' '::text) || (b)::text) = 'test you'::text) Planning time: 0.582 ms Execution time: 0.187 ms(6 rows)

但是还是需要根据实际业务情况仔细评估后决定采⽤何种索引，因为并不是索引越多越好。三.部分索引

只在⾃⼰感兴趣的那部分数据上创建索引，⽽不是对每⼀⾏数据都创建索引，此种⽅式创建索引就需要使⽤WHERE条件了。

创建两个完全相同的表⽐较部分索引和全索引的区别。测试表

test=# create table tbl_partial_index(id bigint,alarm_time timestamp without time zone,level varchar(12),alarm_desc varchar(100));CREATE TABLE

test=# create table tbl_partial_index1(id bigint,alarm_time timestamp without time zone,level varchar(12),alarm_desc varchar(100));CREATE TABLE

写⼊完全相同的数据

test=# insert into tbl_partial_index(id,alarm_time,level,alarm_desc)

select generate_series(1,9000000),clock_timestamp()::timestamp without time zone,'green','正常';INSERT 0 9000000

test=# insert into tbl_partial_index(id,alarm_time,level,alarm_desc)

select generate_series(9000000,9000100),clock_timestamp()::timestamp without time zone,'red','攻击';INSERT 0 101test=# test=#

test=# insert into tbl_partial_index1(id,alarm_time,level,alarm_desc)

select generate_series(1,9000000),clock_timestamp()::timestamp without time zone,'green','正常';INSERT 0 9000000

test=# insert into tbl_partial_index1(id,alarm_time,level,alarm_desc)

select generate_series(9000000,9000100),clock_timestamp()::timestamp without time zone,'red','攻击';INSERT 0 101

⽰例1.在tbl_partial_index表字段level上创建索引

test=# create index idx_tbl_partial_index_level on tbl_partial_index using btree (level);CREATE INDEXTime: 31407.356 mstest=#

test=# explain analyze select * from tbl_partial_index where level = 'red';

QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using idx_tbl_partial_index_level on tbl_partial_index (cost=0.43..4.45 rows=1 width=29) (actual time=0.069..0.087 rows=101 loops=1)

Index Cond: ((level)::text = 'red'::text)

Planning time: 0.268 ms Execution time: 0.124 ms(4 rows)Time: 23.460 ms

test=# select relname,pg_size_pretty(pg_relation_size(oid)) from pg_class where relname='idx_tbl_partial_index_level'; relname | pg_size_pretty -----------------------------+---------------- idx_tbl_partial_index_level | 191 MB(1 row)

Time: 71.799 ms

⽰例2.在tbl_partial_index1表字段level等于red的⾏上创建索引

test=# create index idx_tbl_partial_index1_level on tbl_partial_index1(level) where level = 'red';CREATE INDEXTime: 5558.905 ms

test=# explain analyze select * from tbl_partial_index1 where level = 'red';

QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using idx_tbl_partial_index1_level on tbl_partial_index1 (cost=0.14..4.16 rows=1 width=29) (actual time=0.051..0.082 rows=101 loops=1)

Planning time: 18.922 ms Execution time: 0.136 ms(3 rows)Time: 19.929 ms

test=# select relname,pg_size_pretty(pg_relation_size(oid)) from pg_class where relname='idx_tbl_partial_index1_level'; relname | pg_size_pretty ------------------------------+---------------- idx_tbl_partial_index1_level | kB(1 row)Time: 0.950 ms

⽐较上⾯两个⽰例的结果可知，全表索引在耗时和⼤⼩⽅⾯要⽐部分索引消耗更多的资源，查询'red'的数据排除环境影响基本相同，数据量更⼤，'red'占⽐更⼩时性能可能会有明显差异，但是查询⾮'red'数据时全表索引会有明显的性能优势，因为部分索引并没有'green'数据的索引，⾛的是全表扫描。

综上，根据数据的使⽤⽅式创建不同的索引在性能上是有明显差异的。