一.唯一索引

唯一索引字面上理解就是在索引上增加唯一约束，不允许出现索引值相同的行，目前只有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 INDEX

test=#

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 101

test=#

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 INDEX

Time: 31407.356 ms

test=#

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 INDEX

Time: 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 row

s=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 | 64 kB

(1 row)

Time: 0.950 ms

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

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

最新文章

  1. angularjs学习使用分享
  2. C#调用天气查询服务
  3. JavaScript 中 onload 事件绑定多个方法
  4. xamarin uwp数字证书公钥私钥
  5. 怎样操作WebAPI接口(显示数据)
  6. sass1:
  7. unity3d中Find的用法
  8. hdu 3032 Nim or not Nim? 博弈论
  9. POJ 2488 A Knight's Journey
  10. ViewPager切换动画PageTransformer使用
  11. 给一个int型整数，如何将这个整数的奇偶位互换
  12. VS2008+Qt 项目目录
  13. HDU 3874 Necklace
  14. 在阿里云ECS（CentOS6.5）上安装ftp
  15. Problem E: 可变长数组
  16. python新手---学习第一天
  17. The Best Books on Game Dev
  18. [转][SerialPort]测试用例
  19. 前端“黑话”polyfill
  20. （转）每天进步一点点——五分钟理解一致性哈希算法(consistent hashing)

热门文章

  1. 启动hadoop 2.6遇到的datanode启动不了
  2. VS2008兼容安装
  3. javascript -- addEventListener()和removeEventListener
  4. linux -- Ubuntu下安装和配置Apache2
  5. linux -- Ubuntu Server 安装图形界面
  6. 利用PHPExcel导出Excel相关设置
  7. Java基础--常用IO流使用实例
  8. Json学习一（基础概念知识学习）
  9. 用ADO操作数据库的方法步骤
  10. html 页面模块的常用命名