PostgreSQL中json實作中的最大亮點：索引的“模式自由”

1. 索引的模式自由介紹

我們知道在文檔型資料庫中最大的亮點是使用json實作了模式自由。模式自由也稱為無模式，模式自由的最大好處就是不需要事先确定表結構就可以靈活的存儲各種結構的資料，對開發人員來說就是“隻要一送出代碼就它可以很好的工作”，而原先的關系型資料庫，需要事先設計表結構，對于一些特别靈活的業務就不是很友善。是以在一些關系型資料庫中，逐漸的增加了對json的支援，如Oracle和MySQL。

但實際上不管是Oracle、MySQL，還是文檔資料庫如MongoDB，都不能做到索引的“模式自由”，即這些資料庫中對json建索引都需要指定JSON中固定字段來建索引，查詢時，隻能按json中某個确定的字段條件來查詢，MongoDB中雖然在json資料之上實作了全文檢索，看起來象是一種“模式自由”，但是與按某個字段來查詢還是有一些不同。

是以，在通常的資料庫中，不管是關系型資料庫Oracle、MySQL，還是文檔型資料庫如MongoDB，隻是做到了表的模式自由，而沒有做到索引的模式自由。而在PostgreSQL中通過GIN索引則實作了索引的模式自由，即索引時不需要指定JSON資料中的指定字段，後續就可以按JSON中已的任意的字段進行查詢。

下面我們就詳細介紹在PostgreSQL中如何用GIN索引實作索引的模式自由。

2. 建立測試資料

為了示範這個功能，我們需要建測試表。我們先建一個含有jsonb字段的表：

CREATE TABLE tbl_user_jsonb(id serial, user_info jsonb);           

為了友善造資料，我們先造一個普通表，把資料生成到這張普通表中，然後利用PostgreSQL資料庫提供的row_to_json的函數把普通表中的資料轉換到json表中，這個功能也是PostgreSQL資料庫的一個亮點。

CREATE TABLE tbl_user(
id int4,
user_id int8,
user_name varchar(64),
create_time timestamp(6) with time zone default clock_timestamp()
);           

我們生成100萬的資料，在上面的普通表中：

insert into tbl_user(id,user_id, user_name) select r,round(random()*2000000), r || '_osdba' from generate_series(1,1000000) as r           

在我的MAC電腦中生成100萬資料用了4.5秒左右，如下所示：

osdba=# insert into tbl_user(id,user_id, user_name) select r,round(random()*2000000), r || '_osdba' from generate_series(1,1000000) as r;
INSERT 0 1000000
Time: 4548.128 ms (00:04.548)           

然後我們用下面的SQL把資料生成到json的表tbl_user_jsonb中:

insert into tbl_user_jsonb(user_info) select row_to_json(tbl_user)::jsonb from tbl_user;           

因為上面這條SQL又讀又要寫，慢一些，花了14秒左右 ：

osdba=# insert into tbl_user_jsonb(user_info) select row_to_json(tbl_user)::jsonb from tbl_user;
INSERT 0 1000000
Time: 14070.349 ms (00:14.070)           

3. 建GIN索引示範索引的模式自由

建GIN索引的SQL如下：

create index idx_gin_tbl_user_jsonb_user_Info on tbl_user_jsonb using gin(user_Info);           

注意建GIN索引的文法是 `using gin(<字段名>)`，建此索引花了53秒，如下所示：

osdba=# create index idx_gin_tbl_user_jsonb_user_Info on tbl_user_jsonb using gin(user_Info);
CREATE INDEX
Time: 53702.887 ms (00:53.703)           

我們這張表tbl_user_jsonb的資料如下：

osdba=# select * from tbl_user_jsonb limit 5;
id | user_info
----+----------------------------------------------------------------------------------------------------------
1 | {"id": 1, "user_id": 1650327, "user_name": "1_osdba", "create_time": "2018-12-12T16:15:57.681605+08:00"}
2 | {"id": 2, "user_id": 986066, "user_name": "2_osdba", "create_time": "2018-12-12T16:15:57.681747+08:00"}
3 | {"id": 3, "user_id": 1966018, "user_name": "3_osdba", "create_time": "2018-12-12T16:15:57.681755+08:00"}
4 | {"id": 4, "user_id": 1143406, "user_name": "4_osdba", "create_time": "2018-12-12T16:15:57.681759+08:00"}
5 | {"id": 5, "user_id": 1754355, "user_name": "5_osdba", "create_time": "2018-12-12T16:15:57.681763+08:00"}
(5 rows)
Time: 2.777 ms           

這時我們按json中的“user_name”來查詢：

osdba=# select * from tbl_user_jsonb where user_info @> '{"user_name":"232_osdba"}';
id | user_info
-----+-------------------------------------------------------------------------------------------------------------
232 | {"id": 232, "user_id": 597495, "user_name": "232_osdba", "create_time": "2018-12-12T16:15:57.682529+08:00"}
(1 row)
Time: 0.779 ms           

從上面可以看到在0.779ms内就查出來了，就可想象一定是走到了索引。

解釋一下上面SQL語句user_info @> '{"user_name":"232_osdba"}'中的“@>”是一個PostgreSQL中的特别的運算符，意思是包含，GIN索引需要使用這種文法來查詢。

這時我們還可以按時間來查詢，發現也很快就查詢出來了：

osdba=# select * from tbl_user_jsonb where user_info @> '{"create_time":"2018-12-12T16:15:57.682529+08:00"}';
id | user_info
-----+-------------------------------------------------------------------------------------------------------------
232 | {"id": 232, "user_id": 597495, "user_name": "232_osdba", "create_time": "2018-12-12T16:15:57.682529+08:00"}
(1 row)
Time: 0.778 ms           

這時我們插入一行資料，在json資料中增加一個沒有的字段“nick_name”，如下所示：

insert into tbl_user_jsonb(user_info) values('{"id":1000001, "user_id":232323, "user_name":"tangcheng", "nick_name":"osdba"}');
INSERT 0 1           

這時我們再用這個“nick_name”這個字段來查詢：

osdba=# select * from tbl_user_jsonb where user_info @> '{"nick_name":"osdba"}';
id | user_info
---------+------------------------------------------------------------------------------------
1000001 | {"id": 1000001, "user_id": 232323, "nick_name": "osdba", "user_name": "tangcheng"}
(1 row)
Time: 0.544 ms           

發現可以很快可以查詢出來，說明走了索引。從這裡就可以知道，在PostgreSQL中不需要知道字段名就可以建一個通用的GIN索引，後續插入這些之前沒有的字段後，也能按這些字段來查詢，這就是索引的模式自由。

當然想看是否真的走到的索引，在PostgreSQL可以看執行計劃：

osdba=# explain analyze select * from tbl_user_jsonb where user_info @> '{"nick_name":"osdba"}';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl_user_jsonb (cost=55.75..3351.08 rows=1000 width=141) (actual time=0.054..0.055 rows=1 loops=1)
Recheck Cond: (user_info @> '{"nick_name": "osdba"}'::jsonb)
Heap Blocks: exact=1
-> Bitmap Index Scan on idx_gin_tbl_user_jsonb_user_info (cost=0.00..55.50 rows=1000 width=0) (actual time=0.042..0.042 rows=1 loops=1)
Index Cond: (user_info @> '{"nick_name": "osdba"}'::jsonb)
Planning time: 0.106 ms
Execution time: 0.100 ms
(7 rows)
Time: 0.672 ms           

從上面的結果 `Bitmap Index Scan on idx_gin_tbl_user_jsonb_user_infos` 可以看出，按“nick_name”來查詢資料時走到了GIN索引“idx_gin_tbl_user_jsonb_user_info”。

4. 總結

在PostgreSQL中使用GIN索引實作了JSON資料的索引的模式自由，當然在PostgreSQL在JSON資料庫也支援使用Bree索引，這是就不是模式自由了，下面按JSON中的字段“user_name”建一個普通Btree索引：

create index idx_user_infob_user_name on tbl_user_jsonb((user_info ->> 'user_name'));           

注意上面的SQL中 `((user_info ->> 'user_name'))` 是雙括号，單括号會的報錯：

osdba=# create index idx_user_infob_user_name2 on tbl_user_jsonb(user_info ->> 'user_name');
ERROR: syntax error at or near "->>"
LINE 1: ...user_infob_user_name2 on tbl_user_jsonb(user_info ->> 'user_...
^
Time: 1.230 ms           

這主要是取json資料中的子字段的運算符“->>”的限制。

基于某個字段建索引要一些，隻需要22.892秒，如下所示：

osdba=# create index idx_user_infob_user_name on tbl_user_jsonb((user_info ->> ‘user_name’));
CREATE INDEX
Time: 22892.580 ms (00:22.893)           

前面建GIN索引花了53.702秒，雖然建這個GIN索引時間慢，但這個索引是對所有的資料都索引了，當然要慢一些，但通常我們使用JSON資料，就是為了模式自由，為了更好的擴充性和靈活性，性能低一些也是可以接受的。