pgsql索引详解

一、mysql索引

MySQL索引跟pgsql索引类似，都是为了加速查询而创建的。MySQL索引有主键索引、唯一索引、普通索引、全文索引、空间索引，其中最常用的是B-Tree索引，而MyISAM存储引擎还有Hash索引。

二、pgsql索引行超出btree长度

一般情况下，我们创建索引时指定列和索引类型即可。但当列长度过长时，如果直接创建索引，则在查询时会出现“行行超过btree最大长度”的错误。这时我们就需要使用函数对列进行截断处理。例如：

CREATE INDEX idx_temp ON table1 (substring(col1, 1, 10));

三、pgsql索引的内存

索引被存储在内存中，索引越小，查询效率越高。当我们使用“EXPLAIN ANALYZE”来分析查询计划时，可以看到索引占用的内存大小。如果内存不足，可以通过调整“shared_buffers”参数来加大索引占用的内存空间。例如：

shared_buffers = 1024MB

四、pgsql索引创建

我们可以使用CREATE INDEX命令来创建索引。参数包括表名、列名和索引类型等。例如：

CREATE INDEX idx_name ON table_name (column_name);

五、pgsql索引类型

pgsql支持多种索引类型，包括B-Tree、哈希表、GiST和SP-GiST等。B-Tree是pgsq最常用的索引类型，哈希表可以在精确匹配时效率更高，GiST可以在多维空间中使用，而SP-GiST则是空间分割的基础。下面是创建不同类型索引的示例：

--创建B-Tree索引
CREATE INDEX idx_name ON table_name USING btree (column_name);

--创建哈希表索引
CREATE INDEX idx_name ON table_name USING hash (column_name);

--创建GiST索引
CREATE INDEX idx_name ON table_name USING gist (column_name);

--创建SP-GiST索引
CREATE INDEX idx_name ON table_name USING spgist (column_name);

六、pgsql索引原理

B-Tree索引是在数据结构中利用了平衡树的思想。它的原理是将数据按照索引列的值进行排序，然后将排序后的数据按照一定的规则划分成多个块（也称之为节点），并生成多层树状结构，每个节点都包含了自己范围内的元素。这整个过程就是使用B-Tree算法生成索引的过程。简单来说，就是把数据存到一棵树里，然后根据每个节点的取值范围，在树的各个层级中查找。

七、pgsql索引结构

B-Tree索引结构由根节点、中间节点和叶子节点三部分组成。每个节点都存储一定数量的键和指针。中间节点以及根节点都存储键和指针，用于连接下一层节点。叶子节点存储实际数据和行指针。下面是一个B-Tree索引结构的图示：

    +------+---+---+---+---+
    |      |   |   |   |   |
    |   K  | P | P | P | P |
    |      |   |   |   |   |
    +------+---+---+---+---+
     /  | |  \  /   \  | | \
   /  |  | |   /     \ | |  \
  /  |   \ /  /       \ \ /   \
L  K  P  K  P  K       K P  K   R

（其中，K代表索引键，P代表指针）

八、pgsql索引优化

优化索引可以在查询效率上有所提升。例如，在查询字段少但需要返回的数据比较多时，我们可以使用索引包含需要返回的数据。如果涉及到多表查询时，将复杂查询拆解成多个简单查询，使用索引连接返回的数据。如果使用索引查询以后还需要对数据进行排序、分组等操作，那么我们可以考虑新建索引，或者在现有的索引基础上新增字段。

九、pgsql索引分区

当数据量过大时，我们可以使用索引分区来对数据进行划分，这样不仅可以加快查询速度，还可以在维护数据时提高效率。例如：

CREATE TABLE table_name (
    id SERIAL PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    data TEXT NOT NULL
) PARTITION BY RANGE(created_at);

--创建分区表
CREATE TABLE table_name_2019_01 PARTITION OF table_name
FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2019-02-01 00:00:00');

--创建索引
CREATE INDEX ON table_name (created_at);

十、pgsql索引含null选取

当我们在查询含有null值的列时，通常会使用“is null”或“is not null”来进行筛选，而一般情况下，数据库并不会将null值存储在B-Tree索引中。如果将含有null值的列添加到B-Tree索引中，大量的null值会大大影响查询效率。因此，我们在添加索引时，应该确定索引不含有null值。例如：

CREATE INDEX idx_name ON table_name (column_name) WHERE column_name IS NOT NULL;

原创文章，作者：小蓝，如若转载，请注明出处：https://www.506064.com/n/301635.html