postgreSQL 的索引

索引概述

       索引可以帮助我们更快的查找和获取指定的行数据，是增强数据库性能的常用手段，但是如果数据库索引使用不合理反而会降低数据库性能，所以我们需要了解索引的相关原理和使用规范。

创建索引语句为：CREATE INDEX test1_id_index ON test1 (id);
删除索引语句为：DROP INDEX test1_id_index;

       索引被创建以后，当向表中插入更新数据时，会自动被触发更新索引，所以在表上创建非必须的索引会导致更多的开销，意味着插入数据会变慢。更新、删除和join索引列也会优化查询，使查询更快。
       给已有大量数据的表添加索引会需要一段长的时间。但是在创建索引的时间内，查询还是可以执行的（多线程），但是插入、更新和删除会被阻塞。

索引的种类

postgreSQL的索引有如下几类：B-tree、Hash、 GiST、SP-GiST、GIN、BRIN。每个索引都有特定的算法来匹配不同的类型数据的查询。

B-tree 索引可以应用于相等和值范围的查询，例如：<,<=,=,>=,>, betwee, in, is null, is not null也适用 like 'foo' , ~ '^foo' ,like 'foo%'，但不适用like ‘%foo’。B-tree也支持排序，虽然他有些情况下不比简单的扫描和排序快，但通常也是有用的。B-tree适合大多数数据类型，也是CREATE INDEX 命令默认创建使用的索引。

GIST索引适用于像like '%foo';的模糊查询，如create extension if not exists pg_trgm; CREATE INDEX trgm_idx ON test_trgm USING GIST (t gist_trgm_ops);

BRIN 索引是Block Range Index的简称，适用块的区域搜索，即物理位置是相邻的。如设备上报的事件，按事件顺序排列并插入，查询一段时间内的事件。这一段时间内的订单存储的物理位置是相邻的。如果物理位置不相邻，可以使用如下命令整理CLUSTER [VERBOSE] table_name [ USING index_name ]。