数组索引和操作符（`@>`，`<@`，`&&`）实现快速查询

在 PostgreSQL 里，数组让你能在一张表的一个单元格里存好多值。比如你想把一篇文章的标签列表，或者一个产品的分类全塞进一个字段，这就超方便。 但一旦你要查找、过滤或者做数组交集，性能就可能暴跌。所以数组索引就是救命稻草。索引能加速这些操作，比如：

• 检查数组里有没有某个元素，
• 查找包含指定元素的数组，
• 判断数组之间有没有交集。

数组常用操作符

在搞索引之前，先来熟悉下数组的主要操作符：

@>（包含 contains） — 检查一个数组是不是包含另一个数组的所有元素。

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

这里我们查找所有带有 "SQL" 标签的课程。

<@（被包含 is contained by） — 检查一个数组是不是被另一个数组包含。

SELECT *
FROM courses
WHERE ARRAY['PostgreSQL', 'SQL'] <@ tags;

这里我们查找标签里包含 ARRAY['PostgreSQL', 'SQL'] 这两个元素的课程。

&&（重叠 overlap） — 检查两个数组有没有交集。

SELECT *
FROM courses
WHERE tags && ARRAY['NoSQL', 'Big Data'];

这个查询会找出至少有 "NoSQL" 或 "Big Data" 其中一个标签的课程。

索引怎么帮忙？

想象一下，你有一张叫 courses 的表，里面有几百万条数据，你用上面这些操作符查数据。如果没有索引，PostgreSQL 只能一行一行慢慢扫——这速度，等得你都能把咖啡喝完了（尤其是像程序员等编译那种耐心）。

有了索引就不一样了。PostgreSQL 有两种适合数组的索引：

1. GIN（Generalized Inverted Index） — 数组首选，速度快。
2. BTREE — 主要用来整体比较数组。

例子：给数组建索引

我们先建个小表，实际操作下：

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    tags TEXT[] NOT NULL
);

插入几条数据：

INSERT INTO courses (name, tags)
VALUES
    ('SQL基础', ARRAY['SQL', 'PostgreSQL', '数据库']),
    ('大数据实战', ARRAY['Hadoop', 'Big Data', 'NoSQL']),
    ('Python开发', ARRAY['Python', 'Web', '数据']),
    ('PostgreSQL课程', ARRAY['PostgreSQL', 'Advanced', 'SQL']);

表大概长这样：

没有索引：查询慢得飞起

现在我们想找所有有 SQL 标签的课程：

EXPLAIN ANALYZE
SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

这个查询能跑出来，但数据一多就慢得要命。PostgreSQL 会做所谓的顺序扫描（Sequential Scan），就是一行一行查。

查询结果大概是：

创建 GIN 索引

为了加速查询，来建个 GIN 索引：

CREATE INDEX idx_courses_tags
ON courses USING GIN (tags);

再跑一次同样的查询：

EXPLAIN ANALYZE
SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

现在 PostgreSQL 会用上我们刚建的 GIN 索引，查询速度直接起飞。

原来用的是 顺序扫描（Seq Scan），现在执行计划里能看到 Bitmap Index Scan：

操作符和索引怎么配合？

例子1：@> 操作符

查询：

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

GIN 索引对这个操作符效果最佳。Postgres 能很快查出哪些行包含指定元素，然后返回结果。

查询结果：

例子2：&& 操作符

查询：

SELECT *
FROM courses
WHERE tags && ARRAY['NoSQL', 'Big Data'];

这个操作符查的是数组交集：只要 tags 里有传入数组的任意一个元素就行。

GIN 索引又来秀操作——数据再多也能飞快查出来。

查询结果：

索引和优化建议

用数组时，建议这样玩：

1. 查数组内容就用 GIN 索引。比顺序扫描快多了。
2. 只给经常查的字段加索引。索引会占空间，还会拖慢插入，所以别啥都加。
3. 用 EXPLAIN 和 EXPLAIN ANALYZE 检查查询，看看索引到底有没有被用上。

例子：数组索引的实际用法

来看看怎么针对不同操作建索引，以及实际场景下为啥要这么做。

@> 操作符的索引

假设我们已经有了 courses 这张表：

为了加速 @>（数组包含元素）查询，建个 GIN 索引：

CREATE INDEX idx_courses_tags_gin
ON courses USING GIN (tags);

现在查一下：

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

结果：

支持 @>、<@、&& 的索引

表结构和上面一样。

因为 @>、<@ 和 && 这几个操作符都能用 GIN 索引加速，所以建一个通用索引就行：

CREATE INDEX idx_tags
ON courses USING GIN (tags);

下面是查询例子和结果：

• @> — 检查数组是否包含指定元素：

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

---

• <@ — 检查数组是否被另一个数组包含：

SELECT *
FROM courses
WHERE tags <@ ARRAY['SQL', 'PostgreSQL', 'Advanced', 'Big Data', 'NoSQL', 'Python'];

---

• && — 检查数组交集：

SELECT *
FROM courses
WHERE tags && ARRAY['NoSQL', 'Big Data'];

来点进阶玩法

写个查询，找出标签和 ['Python', 'SQL', 'NoSQL'] 至少有一个交集的课程：

SELECT *
FROM courses
WHERE tags && ARRAY['Python', 'SQL', 'NoSQL'];

输出：

有了 GIN 索引，这种查询哪怕表里有几百万行也能秒出结果。

数组常见坑

索引没用上：如果 EXPLAIN 输出里看到 Seq Scan，检查下索引是不是建了，操作符是不是支持索引。

数组字段很少用：如果数组字段很少查或者经常更新，索引可能白占空间，没啥用。

索引太多：索引会占磁盘，还会拖慢写入，所以只建真的需要、会被用到的索引。

现在你已经有了 PostgreSQL 里高效玩数组的所有工具——用 @>、<@、&& 操作符和 GIN 索引加速查询吧。赶紧在你自己的数据上试试吧！