postgres mysql 索引_Postgres数据库之索引

索引是对数据库中一列或几列的数据按照特定的数据结构进行排序保存的一种方式。使用索引可以加快数据库查询或排序时的速度。如果不使用索引那么查询数据时就会进行全表扫描也就是每条数据读取一遍看是不是要找的值，而使用索引可以快速找到要找的数据，不用扫描所有数据。

PostgreSQL 拥有众多开放特性，例如 :

1、开放的数据类型接口，使得PG支持超级丰富的数据类型，除了传统数据库支持的类型，还支持GIS，JSON，RANGE，IP，ISBN，图像特征值，化学，DNA等等扩展的类型，用户还可以根据实际业务扩展更多的类型。

2、开放的操作符接口，使得PG不仅仅支持常见的类型操作符，还支持扩展的操作符，例如 距离符，逻辑并、交、差符号，图像相似符号，几何计算符号等等扩展的符号，用户还可以根据实际业务扩展更多的操作符。

3、开放的外部数据源接口，使得PG支持丰富的外部数据源，例如可以通过FDW读写MySQL, redis, mongo, oracle, sqlserver, hive, www, hbase, ldap, 等等只要你能想到的数据源都可以通过FDW接口读写。

4、开放的语言接口，使得PG支持几乎地球上所有的编程语言作为数据库的函数、存储过程语言，例如plpython , plperl , pljava , plR , plCUDA , plshell等等。用户可以通过language handler扩展PG的语言支持。

5、开放的索引接口，使得PG支持非常丰富的索引方法，例如btree , hash , gin , gist , sp-gist , brin , bloom , rum , zombodb , bitmap (greenplum extend)，用户可以根据不同的数据类型，以及查询的场景，选择不同的索引。

6、PG内部还支持BitmapAnd, BitmapOr的优化方法，可以合并多个索引的扫描操作，从而提升多个索引数据访问的效率。

索引的优点：

可以大大提高数据查询的速度

在实现数据的参考完整性方面，可以加快表和表之间的连接

在分组和排序时，可以减少分组和排序的时间

当然，索引也有缺点，所以我们不能在表中随意的创建索引

索引的缺点

创建和维护索引需要耗费时间，数据量越大耗费时间也越长

索引需要占用额外的磁盘空间，如果数据量很大又有大量索引，那么索引文件大小增加很快

对表中数据改动的时候，索引需要动态维护，降低了数据操作的速度

索引类型

PostgreSQL提供的索引类型有：B-tree、Hash、GiST和GIN。大多数情况下，我们使用比较常用的B-tree索引。

B-tree索引适合处理那么些能够按照顺序存储的数据，它适合用于比较操作时经常用到的字段。

Hash索引只能处理简单的等于比较。当一个字段涉及到使用“=”操作符进行比较时查询规划器会考虑使用Hash索引。

GiST索引不只是一种索引，还是一种架构，可以实现很多不同的索引策略。所以，GiST索引可以使用的特定操作符类型高度依赖于索引策略(操作符类)。

GIN索引是反转索引，它可以处理包含多个键的值(比如数组)。和GiST索引类似，GIN支持用户定义的索引策略，GIN索引可以使用的特定操作符类型根据索引策略不同而不同。

创建、删除索引

create index idx_name on table_name(column_name); #创建B-tree索引

create index idx_name on table_name using hash (column_name); #创建Hash索引

drop index idx_name; #删除索引

索引的设计原则

1、索引并不是越多越好，索引太多会影响insert,delete,update的性能。

2、对于经常查询的字段应该建立索引

3、避免对经常更新的表进行过多的索引

4、数据量很小的表最好不要使用索引，由于数据量少，扫描表的时间可能比查询索引时间要短，索引没有效果

5、不要在不同值少的列上建立索引，例如 表示性别的字段一般只有 ‘男’和‘女’两个不同值，如果建立索引会严重降低更新速度。

6、在频繁进行排序或分组的列上建立索引时，如果待排序的列有多个，可在这些列上建立联合索引。

例子 :

btree

b-tree适合所有的数据类型，支持排序，支持大于、小于、等于、大于或等于、小于或等于的搜索。

postgres=# create table t_btree(id int, info text);CREATE TABLEpostgres=# insert into t_btree select generate_series(1,10000), md5(random()::text) ;INSERT 0 10000postgres=# create index idx_t_btree_1 ont_btree using btree (id);CREATE INDEXpostgres=# explain (analyze,verbose,timing,costs,buffers) select * from t_btree where id=1;

QUERYPLAN

-------------------------------------------------------------------------------------------------------------------------------

Index Scan using idx_t_btree_1 on public.t_btree (cost=0.29..3.30 rows=1 width=37) (actual time=0.027..0.027 rows=1 loops=1)

Output: id, infoIndex Cond: (t_btree.id = 1)

Buffers: shared hit=1 read=2Planning time:0.292ms

Execution time:0.050ms

(6 rows)

hash

hash索引存储的是被索引字段VALUE的哈希值，只支持等值查询。

postgres=# create table t_hash (id int, info text);CREATE TABLEpostgres=# insert into t_hash select generate_series(1,100), repeat(md5(random()::text),10000);INSERT 0 100

--使用b-tree索引会报错，因为长度超过了1/3的索引页大小

postgres=# create index idx_t_hash_1 ont_hash using btree (info);

ERROR:index row size 3720 exceeds maximum 2712 for index"idx_t_hash_1"

HINT:Values larger than 1/3 ofa buffer page cannot be indexed.

Consider afunction index of an MD5 hash of the value, or use full textindexing.

postgres=# create index idx_t_hash_1 ont_hash using hash (info);CREATE INDEXpostgres=# set enable_hashjoin=off;SETpostgres=# explain (analyze,verbose,timing,costs,buffers) select * from t_hash where info in (select info from t_hash limit 1);

QUERYPLAN

-------------------------------------------------------------------------------------------------------------------------------------

Nested Loop (cost=0.03..3.07 rows=1 width=22) (actual time=0.859..0.861 rows=1 loops=1)

Output: t_hash.id, t_hash.info

Buffers: shared hit=11

-> HashAggregate (cost=0.03..0.04 rows=1 width=18) (actual time=0.281..0.281 rows=1 loops=1)

Output: t_hash_1.infoGroup Key: t_hash_1.info

Buffers: shared hit=3

-> Limit (cost=0.00..0.02 rows=1 width=18) (actual time=0.012..0.012 rows=1 loops=1)

Output: t_hash_1.info

Buffers: shared hit=1

-> Seq Scan on public.t_hash t_hash_1 (cost=0.00..2.00 rows=100 width=18) (actual time=0.011..0.011 rows=1 loops=1)

Output: t_hash_1.info

Buffers: shared hit=1

-> Index Scan using idx_t_hash_1 on public.t_hash (cost=0.00..3.02 rows=1 width=22) (actual time=0.526..0.527 rows=1 loops=1)

Output: t_hash.id, t_hash.infoIndex Cond: (t_hash.info =t_hash_1.info)

Buffers: shared hit=6Planning time:0.159ms

Execution time:0.898ms

(19 rows)

参考文章 : https://blog.csdn.net/qq_28289405/article/details/80539921 , https://www.jianshu.com/p/161b66eca691