Postgre 数据库索引笔记

1.使用索引的优点

通过创建唯一索引，可以保证数据唯一性

提高数据记录的查询速度

加快表与表之间的连接速度

2.使用索引的缺点

使用索引具有以下缺点

需要占据额外的物理存储空间

如果表中数据有变化，则索引也需要同步更新，对数据库性能有一定影响

3.索引分类

pgsql中提供了B-tree，hash，GiST，SP-GIST，GIN,BRIN 等多种索引类型，每种索引使用不同的算法来适应不同类型的查询。在默认情况下，创建的索引类型为B-tree索引。

b-tree索引

b-tree索引使用b-tree数据结构存储索引数据，可用于处理等值查询和范围查询，包括<,<=,>=,>等运算符，以及BETWEEN,IN,IS NULL, IS NOT NULL等条件

hash索引

hash索引基于哈希表实现，只能用于等值查询（不常用）

索引设计原则

1.在经常用于查询的字段上创建索引

2.在经常用于连接的字段上创建索引，加快索引效率

3.在经常需要根据范围进行查询的列上创建索引

4.在经常需要排序的列上创建索引，加快排序

5.不应该在查询中很少的列上创建索引

6.对经常更新的表需要避免建立过多的索引

7.不应该在数据量非常小的表上创建索引

8.不应该在数据取值区分度很小的列上创建索引，如性别字段只有男女两个取值

创建索引语法

lottu=# \h create index Command: CREATE INDEX Description: define a new index Syntax: CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name [ USING method ] ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ) [ WITH ( storage_parameter = value [, ... ] ) ] [ TABLESPACE tablespace_name ] [ WHERE predicate ] 接下来我们以t表为例。

1. 关键字【UNIQUE】 #创建唯一索引；主键就是一种唯一索引

CREATE UNIQUE INDEX ind_t_id_1 on t (id);

2. 关键字【CONCURRENTLY】 # 这是并发创建索引。跟oracle的online创建索引作用是一样的。创建索引过程中；不会阻塞表更新，插入，删除操作。当然创建的时间就会很漫长。

CREATE INDEX CONCURRENTLY ind_t_id_2 on t (id);

3. 关键字【IF NOT EXISTS】 #用该命令是用于确认索引名是否存在。若存在；也不会报错。 CREATE INDEX IF NOT EXISTS ind_t_id_3 on t (id);

4. 关键字【USING】 # 创建哪种类型的索引。 默认是B-tree。 CREATE INDEX ind_t_id_4 on t using btree (id);

5 关键字【[ ASC | DESC ] [ NULLS { FIRST | LAST]】 # 创建索引是采用降序还是升序。 若字段存在null值，是把null值放在前面还是最后：例如采用降序，null放在前面。 CREATE INDEX ind_t_id_5 on t (id desc nulls first)

6. 关键字【WITH ( storage_parameter = value)】 #索引的填充因子设为。例如创建索引的填充因子设为75 CREATE INDEX ind_t_id_6 on t (id) with (fillfactor = 75);

7. 关键字【TABLESPACE】 #是把索引创建在哪个表空间。 CREATE INDEX ind_t_id_7 on t (id) TABLESPACE tsp_lottu;

8. 关键字【WHERE】 #只在自己感兴趣的那部分数据上创建索引，而不是对每一行数据都创建索引，此种方式创建索引就需要使用WHERE条件了。 CREATE INDEX ind_t_id_8 on t (id) WHERE id < 1000;

删除索引

lottu=# \h drop index Command: DROP INDEX Description: remove an index Syntax:

DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

索引修改

lottu=# \h alter index Command: ALTER INDEX Description: change the definition of an index Syntax:

#把索引重新命名

ALTER INDEX [ IF EXISTS ] name RENAME TO new_name

#把索引迁移表空间

ALTER INDEX [ IF EXISTS ] name SET TABLESPACE tablespace_name

#把索引重设置填充因子

ALTER INDEX [ IF EXISTS ] name SET ( storage_parameter = value [, ... ] )

#把索引的填充因子设置为默认值 ALTER INDEX [ IF EXISTS ] name RESET ( storage_parameter [, ... ] )

#把表空间TSP1中索引迁移到新表空间

ALTER INDEX ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ] SET TABLESPACE new_tablespace [ NOWAIT ]
 

--通过pg_stat_user_indexes.idx_scan可检查利用索引进行扫描的次数；这样可以确认那些索引可以清理掉。

select idx_scan from pg_stat_user_indexes where indexrelname = 'ind_t_id';

重建索引

--如果一个表经过频繁更新之后，索引性能不好；需要重建索引。

lottu=# select pg_size_pretty(pg_relation_size('ind_t_id_1')); pg_size_pretty ---------------- 2200 kB (1 row) lottu=# delete from t where id > 1000; DELETE 99000 lottu=# analyze t; ANALYZE lottu=# select pg_size_pretty(pg_relation_size('ind_t_id_1')); pg_size_pretty ---------------- 2200 kB lottu=# insert into t select generate_series(2000,100000),'lottu'; INSERT 0 98001 lottu=# select pg_size_pretty(pg_relation_size('ind_t_id_1')); pg_size_pretty ---------------- 4336 kB (1 row) lottu=# vacuum full t; VACUUM lottu=# select pg_size_pretty(pg_relation_size('ind_t_id_1')); pg_size_pretty ---------------- 2176 kB 重建方法：

1. reindex：reindex不支持并行重建【CONCURRENTLY】;索引会锁表；会进行阻塞。

2. vacuum full; 对表进行重构；索引也会重建；同样也会锁表。

3. 创建一个新索引(索引名不同)；再删除旧索引。