PostgreSQL教程：索引（概念、分类、GIN索引）

落日映苍穹つ 2023-10-16 05:26 60阅读 0赞

## 索引的基本概念 ##

先了解概念和使用

索引是数据库中快速查询数据的方法。

索引能提升查询效率的同时，也会带来一些问题

*  增加了存储空间
 *  写操作时，花费的时间比较多

索引可以提升效率，甚至还可以给字段做一些约束

## 索引的分类 ##

B-Tree索引：最常用的索引。

Hash索引：跟MySQL类似，做等值判断，范围凉凉~

GIN索引：针对字段的多个值的类型，比如数组类型。

## 创建索引看效果 ##

![image.png][]

准备大量测试数据，方便查看索引效果

-- 测试索引效果
    create table tb_index(
        id bigserial primary key,
        name varchar(64),
        phone varchar(64)[]
    );
    
    -- 添加300W条数据测试效果
    do $$
    declare
        i int := 0;
    begin
        while i < 3000000 loop
            i = i + 1;
            insert into
                tb_index
            (name,phone) 
                values
            (md5(random()::text || current_timestamp::text)::uuid,array[random()::varchar(64),random()::varchar(64)]);
        end loop;
    end;
    $$ language plpgsql;

在没有索引的情况下，先基于name做等值查询，看时间，同时看执行计划

-- c0064192-1836-b019-c649-b368c2be31ca
    select * from tb_index where id = 2222222;
    select * from tb_index where name = 'c0064192-1836-b019-c649-b368c2be31ca';
    explain select * from tb_index where name = 'c0064192-1836-b019-c649-b368c2be31ca';
    -- Seq Scan  这个代表全表扫描
    -- 时间大致0.3秒左右

在有索引的情况下，再基于name做等值查询，看时间，同时看执行计划

-- name字段构建索引（默认就是b-tree）
    create index index_tb_index_name on tb_index(name);
    -- 测试效果
    select * from tb_index where name = 'c0064192-1836-b019-c649-b368c2be31ca';
    explain select * from tb_index where name = 'c0064192-1836-b019-c649-b368c2be31ca';
    -- Index Scan 使用索引
    -- 0.1s左右

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

## 测试GIN索引效果 ##

在没有索引的情况下，基于phone字段做包含查询

-- phone：{0.6925242730781953,0.8569644964711074}
    select * from tb_index where phone @> array['0.6925242730781953'::varchar(64)];
    explain select * from tb_index where phone @> array['0.6925242730781953'::varchar(64)];
    -- Seq Scan 全表扫描
    -- 0.5s左右

给phone字段构建GIN索引，在查询

-- 给phone字符串数组类型字段构建一个GIN索引
    create index index_tb_index_phone_gin on tb_index using gin(phone);
    -- 查询
    select * from tb_index where phone @> array['0.6925242730781953'::varchar(64)];
    explain select * from tb_index where phone @> array['0.6925242730781953'::varchar(64)];
    -- Bitmap Index 位图扫描
    -- 0.1s以内完成

## 补充：GIN索引 ##

GIN（Generalized Inverted Index）索引是 PostgreSQL 中的一种索引类型，用于支持对文本、数组、范围等复杂数据类型进行高效的全文搜索和查询。

与传统的 B-tree 索引只适用于单个值的精确匹配不同，GIN 索引可以处理更复杂的数据类型。GIN 索引使用倒排索引的概念，将索引的键值与数据项关联，以便于在查询时快速定位匹配项。

在创建 GIN 索引时，您需要选择适当的 GIN 索引类型，以匹配您要索引的数据类型。以下是几种常见的 GIN 索引类型：

1.  `gin_trgm_ops`：用于对文本进行模糊搜索的索引类型。它使用 trigram 技术将文本分割为三个字符的片段，并构建索引以支持模糊匹配。
2.  `gin_array_ops`：用于对数组类型进行搜索的索引类型。它可以帮助您在包含数组的列上进行高效的查找和匹配。
3.  `gin_tsvector_ops`：用于全文搜索的索引类型。它可以处理文本文档并构建一个词汇表，以支持全文搜索和相关性排序。
4.  `gin_int_ops` 和 `gin_intarray_ops`：用于整数和整数数组类型的索引。它们可以加速针对整数列的查询，包括范围查询和包含/不包含操作。

创建 GIN 索引的语法如下：

CREATE INDEX index_name ON table_name USING gin (column_name gin_index_type);

其中，`index_name` 是索引的名称，`table_name` 是要创建索引的表名，`column_name` 是要索引的列名，`gin_index_type` 是适用的 GIN 索引类型。

例如，以下是在名为 `documents` 的表上创建一个使用 `gin_trgm_ops` 索引的示例：

CREATE INDEX idx_documents_content_gin ON documents USING gin (content gin_trgm_ops);

这将在 `content` 列上创建一个 GIN 索引，以支持基于 `content` 列的模糊搜索。

请注意，GIN 索引适用于处理复杂数据类型和模糊搜索等情况，但在某些情况下，它可能会占用更多的磁盘空间和内存，并且在更新数据时的性能可能会受到影响。因此，在选择是否使用 GIN 索引时，请考虑数据类型和查询需求，并进行适当的性能测试和评估。

[image.png]: https://img-blog.csdnimg.cn/img_convert/e1655d5885813f189d5b2d48c9dfdea1.png