PostgreSQL 的虹吸效应，吸收 NoSQL 之无结构化索引！-51CTO.COM

PostgreSQL JSON 索引简介

JSONB（二进制 JSON）是一种数据类型，允许您存储 JSON 数据并对其进行高效查询。

当一个 JSONB 列具有复杂的 JSON 结构时，使用索引可以显著提高查询性能。

PostgreSQL 使用GIN索引类型来索引具有 JSONB 数据类型的列。GIN的意思是通用倒排索引。

请注意，您可以将 GIN 索引用于 tsvector 或数组列。

要为 JSONB 列创建GIN索引，可以使用下面的CREATE INDEX语句：

CREATE INDEX index_name
ON table_name 
USING GIN(jsonb_column);

此语句在jsonb_column上创建GIN索引。这种GIN索引适用于对 JSONB 数据的通用查询。

在一个 JSONB 列上创建GIN索引时，可以使用特定的GIN操作符类。

操作符类确定 PostgreSQL 如何构建索引，以及如何优化索引列上的查询。

例如，下面的CREATE INDEX语句，使用jsonb_path_ops操作符类在jsonb_coumn上创建GIN索引：

CREATE INDEX index_name
ON table_name 
USING GIN(jsonb_column jsonb_path_ops);

该索引针对使用 @> (contains)、? (exists) 和 @@ 的 JSONB 运算符的查询做了优化。它对于涉及 JSONB 文档中的键值搜索非常有用。

下表显示了GIN操作符类：

名称	可索引的操作符
`array_ops`	`&& (anyarray,anyarray)`
`@> (anyarray,anyarray)`
`<@ (anyarray,anyarray)`
`= (anyarray,anyarray)`
`jsonb_ops`	`@> (jsonb,jsonb)`
`@? (jsonb,jsonpath)`
`@@ (jsonb,jsonpath)`
`? (jsonb,text)`
`?\| (jsonb,text[])`
`?& (jsonb,text[])`
`jsonb_path_ops`	`@> (jsonb,jsonb)`
`@? (jsonb,jsonpath)`
`@@ (jsonb,jsonpath)`
`tsvector_ops`	`@@ (tsvector,tsquery)`
`@@@ (tsvector,tsquery)`

请注意，如果您没有显式指定GIN操作符类，则该语句将默认使用jsonb_ops操作符，这适用于大多数情况。

此外，PostgreSQL 允许您为 JSON 文档中的特定字段创建GIN索引，如下所示：

CREATE INDEX index_name 
ON table_name 
USING GIN ((data->'field_name') jsonb_path_ops);

如果有一个 JSON 文档存储在 JSONB 列中，当查询涉及在该 JSON 文档数据的字段field_name中搜索值时，该索引可以改进查询性能。

PostgreSQL JSON 索引示例

我们将使用示例数据库中的表。

1) 设置一个示例表

首先，创建一个新表，名为customer_json，以 JSON 格式存储客户信息：

CREATE TABLE customer_json(
   id SERIAL PRIMARY KEY,
   data JSONB NOT NULL
);

第二步，将customer、address、city和country表中的数据插入到customer_json表中：

WITH json_cte AS(
  SELECT 
    jsonb_build_object(
      'first_name',  first_name, 
      'last_name',  last_name, 
      'email',  email, 
      'phone',  a.phone, 
      'address', 
      jsonb_build_object(
        'address', a.address, 
        'city', i.city, 
        'postal_code', a.postal_code, 
        'district',  a.district, 
        'country', o.country
      )
    ):: jsonb AS data 
  FROM 
    customer c 
    INNER JOIN address a ON a.address_id = c.address_id 
    INNER JOIN city i ON i.city_id = a.city_id 
    INNER JOIN country o ON o.country_id = i.country_id
) 
INSERT INTO customer_json(data) 
SELECT data FROM json_cte;

第三步，检索名为John的客户的电子邮件：

SELECT
   data ->> 'first_name' first_name,
   data ->> 'last_name' last_name,
   data ->> 'phone' phone
FROM
   customer_json
WHERE
   data @> '{"first_name": "John"}';

输出：

first_name | last_name  |    phone
------------+------------+-------------
 John       | Farnsworth | 51917807050
(1 row)

最后，对上面的查询进行解释和分析：

EXPLAIN ANALYZE  
SELECT
   data ->> 'first_name' first_name,
   data ->> 'last_name' last_name,
   data ->> 'phone' phone
FROM
   customer_json
WHERE
   data @> '{"first_name": "John"}';

输出：

QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Seq Scan on customer_json  (cost=0.00..31.50 rows=1 width=96) (actual time=0.063..0.118 rows=1 loops=1)
   Filter: (data @> '{"first_name": "John"}'::jsonb)
   Rows Removed by Filter: 598
 Planning Time: 1.109 ms
 Execution Time: 0.128 ms
(5 rows)

输出表明 PostgreSQL 必须扫描整个customer_json表来搜索客户。

要提升查询的性能，可以在customer_json表的数据列上创建GIN索引。

2) 在 JSONB 列上创建索引

首先，在customer_json表的data列上创建一个索引：

CREATE INDEX customer_json_index 
ON customer_json 
USING GIN(data);

第二步，执行查询，搜索名为John的客户：

EXPLAIN ANALYZE  
SELECT
   data ->> 'first_name' first_name,
   data ->> 'last_name' last_name,
   data ->> 'phone' phone
FROM
   customer_json
WHERE
   data @> '{"first_name": "John"}';

输出：

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on customer_json  (cost=21.51..25.53 rows=1 width=96) (actual time=0.024..0.024 rows=1 loops=1)
   Recheck Cond: (data @> '{"first_name": "John"}'::jsonb)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on customer_json_index  (cost=0.00..21.51 rows=1 width=0) (actual time=0.014..0.014 rows=1 loops=1)
         Index Cond: (data @> '{"first_name": "John"}'::jsonb)
 Planning Time: 0.164 ms
 Execution Time: 0.045 ms
(7 rows)

查询计划表明 PostgreSQL 使用了customer_json_index来提升性能。

这一次，执行时间明显缩短了，0.045ms对比0.128ms，比不使用GIN索引的查询快了 2 - 3 倍。

3) 使用 GIN 操作符类在 JSONB 列上创建索引

首先，删除索引customer_json_index：

DROP INDEX customer_json_index;

第二步，使用GIN操作符类，在customer_json表的 data 列上创建GIN索引：

CREATE INDEX customer_json_index 
ON customer_json 
USING GIN(data jsonb_path_ops);

第三步，解释查询，该查询查找名为John的客户：

EXPLAIN ANALYZE  
SELECT
   data ->> 'first_name' first_name,
   data ->> 'last_name' last_name,
   data ->> 'phone' phone
FROM
   customer_json
WHERE
   data @> '{"first_name": "John"}';

输出：

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on customer_json  (cost=12.82..16.84 rows=1 width=96) (actual time=0.014..0.015 rows=1 loops=1)
   Recheck Cond: (data @> '{"first_name": "John"}'::jsonb)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on customer_json_index  (cost=0.00..12.82 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)
         Index Cond: (data @> '{"first_name": "John"}'::jsonb)
 Planning Time: 0.120 ms
 Execution Time: 0.034 ms
(7 rows)

查询计划显示查询确实使用了customer_json_index来提升性能。

最后，解释查询，该查询搜索 data 列中first_name字段中的值为 John 的客户：

EXPLAIN ANALYZE 
SELECT * FROM customer_json
WHERE data->>'first_name' = 'John';

输出：

QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Seq Scan on customer_json  (cost=0.00..32.98 rows=3 width=275) (actual time=0.161..0.284 rows=1 loops=1)
   Filter: ((data ->> 'first_name'::text) = 'John'::text)
   Rows Removed by Filter: 598
 Planning Time: 0.085 ms
 Execution Time: 0.298 ms
(5 rows)

在此计划中，查询无法充分利用GIN索引customer_json_index。原因是查询没有使用 JSONB 操作符（@、@?、@@），而jsonb_path_ops操作符类是针对这些操作符进行优化的。

4) 在 JSONB 列的特定字段上创建索引

首先，删除索引customer_json_index：

DROP INDEX customer_json_index;

第二步，使用GIN操作符类，在customer_json表的first_name字段上创建GIN索引：

CREATE INDEX customer_json_index 
ON customer_json 
USING GIN((data->'first_name'));

第三步，解释查询，该查询查找 JSONB 列data中的 “first_name“ 字段包含值"John"的行：

EXPLAIN ANALYZE  
SELECT
   data ->> 'first_name' first_name,
   data ->> 'last_name' last_name,
   data ->> 'phone' phone
FROM
   customer_json
WHERE
   data->'first_name' @> '"John"';

输出：

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on customer_json  (cost=8.58..23.72 rows=6 width=96) (actual time=0.031..0.032 rows=1 loops=1)
   Recheck Cond: ((data -> 'first_name'::text) @> '"John"'::jsonb)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on customer_json_index  (cost=0.00..8.58 rows=6 width=0) (actual time=0.015..0.015 rows=1 loops=1)
         Index Cond: ((data -> 'first_name'::text) @> '"John"'::jsonb)
 Planning Time: 0.167 ms
 Execution Time: 0.133 ms
(7 rows)

输出表明查询使用了customer_json_index索引。

总结

使用GIN索引为表的 JSONB 列创建索引，以提升查询性能。