存储性能对比，谁才是最强王者？

[RedCrazyGhost]

性能对结论：MySQL 宽表 > MySQL JSON ≈ PGSQL JSONB > PGSQL JSON

测试环境

• MySQL VERSION：8.0.35
• PGSQL VERSION：16.1（Ubuntu 16.1-1.pgdg22.04+1）
• 内存大小：2 GB

测试条件

• 数据量：1 万条数据
• 字段命名方式：col_0、...、col_499
• 文本数据随机生成

方案拟定

宽表方案

MySQL Row Size 65,535 bytes and Row Columns 4096

UTF8 编码：1 个字符占 3 个字节
UTF8MB4 编码：1 个字符占 4 个字节

采用 500 个存储字段，由以下字段组成：

类型	数量
varchar(50)	100
integer	100
double	100
datetime	100
date	50
time	50

参考内容：

• 这500多个字段引起的问题，大部分DBA都搞不定
• 12.5 Limits on Table Column Count and Row Size

JSON 方案

• MySQL 1 个存储字段（JSON）
• PGSQL 1 个存储字段（JSONB）
• PGSQL 1 个存储字段（JSON）

操作结果对比

判断查询

• MySQL 宽表

> select * from data where col_0 = '⽹卡';
...
3 726 rows in set (0.278 sec)

• MySQL JSON

> select data from json where data -> '$.col_0' = '⽹卡';
...
704 rows in set (0.319 sec)

• PGSQL JSONB

# select data -> 'col_0' from jsonb;
Time: 233.711 ms （0.233 sec）

• PGSQL JSON

# select data -> 'col_0' from json;
Time: 876.308 ms （0.876 sec）

分页查询

• MySQL 宽表

> select * from data where col_0 = 'CPU' limit 50;
...
50 rows in set (0.019 sec)

• MySQL JSON

> select data from json where data -> '$.col_0' = 'CPU' limit 50;
...
50 rows in set (0.017 sec)

• PGSQL JSONB

# select data -> 'col_0' from jsonb limit 50;
Time: 1.834 ms （0.001 sec）

• PGSQL JSON

# select data -> 'col_0' from json limit 50;
Time: 4.824 ms （0.004 sec）

分组查询

• MySQL 宽表

> select col_0,count(1) from data group by col_0;
+-----------+----------+
| col_0 | count(1) |
+-----------+----------+
| 内存 | 757 |
| ⿏标 | 695 |
| 机箱 | 688 |
| 声卡 | 744 |
| 显卡 | 662 |
| ⽹卡 | 726 |
| 打印机 | 728 |
| 显⽰器 | 726 |
| 硬盘 | 700 |
| 主板 | 718 |
| 键盘 | 749 |
| 光驱 | 690 |
| 电源 | 717 |
| CPU | 656 |
+-----------+----------+
14 rows in set (0.045 sec)

• MySQL JSON

> select data->'$.col_0',count(1) from json group by data->'$.col_0';
+-----------------+----------+
| data->'$.col_0' | count(1) |
+-----------------+----------+
| "显卡" | 715 |
| "光驱" | 703 |
| "主板" | 745 |
| "声卡" | 683 |
| "键盘" | 751 |
| "机箱" | 711 |
| "⿏标" | 723 |
| "电源" | 699 |
| "显⽰器" | 738 |
| "⽹卡" | 704 |
| "内存" | 720 |
| "硬盘" | 725 |
| "打印机" | 697 |
| "CPU" | 638 |
+-----------------+----------+
14 rows in set (0.416 sec)

• PGSQL JSONB

# select data ->> 'col_0',count(1) from jsonb group by data ->> 'col_0';
?column? | count
----------+-------
显⽰器 | 721
光驱 | 680
CPU | 736
声卡 | 724
主板 | 736
硬盘 | 732
⿏标 | 675
机箱 | 686
键盘 | 712
显卡 | 732
打印机 | 686
电源 | 706
内存 | 709
⽹卡 | 766
(14 rows)

Time: 225.264 ms （0.225 sec）

• PGSQL JSON

# select data ->> 'col_0',count(1) from json group by data ->> 'col_0';
?column? | count
----------+-------
主板 | 736
CPU | 736
显⽰器 | 722
键盘 | 712
⿏标 | 675
电源 | 706
打印机 | 686
声卡 | 724
内存 | 709
机箱 | 686
⽹卡 | 766
硬盘 | 732
显卡 | 732
光驱 | 680
(14 rows)

Time: 885.111 ms （0.885 sec）

联表查询

• MySQL 宽表

> select d1.col_0,d2.col_1 from data d1 left join data d2 on d1.col_0 = d2.col_1 where d1.col_0 = 'CPU';
463792 rows in set (0.490 sec)

• MySQL JSON

> select j1.data -> '$.col_0',j2.data->'$.col_1' from json j1 left join json j2 on json_value(j1.data, '$.col_0') = json_value(j2.data,'$.col_1') where j1.data -> '$.col_0' = 'CPU';
490622 rows in set (2.384 sec)

• MySQL JSON（虚拟列索引）

> alter table json add column col_0 varchar(50) as (JSON_EXTRACT(data,
'$.col_0')) VIRTUAL;
> alter table json add column col_1 varchar(50) as (JSON_EXTRACT(data,
'$.col_1')) VIRTUAL;

> create index index_col_0 on json (col_0);
> create index index_col_1 on json (col_1);

> select j1.col_0,j2.col_1 from json j1 left join json j2 on j1.col_0 =j2.col_1 where j1.col_0 ='"CPU"';
490622 rows in set (0.601 sec)

• PGSQL JSONB

# select j1.data ->> 'col_0',j2.data ->> 'col_1' from jsonb j1 left join jsonb j2 on j1.data ->>'col_0' = j2.data ->>'col_1' where j1.data ->> 'col_0'= 'CPU';
Time: 20984.119 ms (00:20.984) （20.984 sec）

• PGSQL JSON

# select j1.data ->> 'col_0',j2.data ->> 'col_1' from json j1 left join json j2 on j1.data ->>'col_0' = j2.data ->>'col_1' where j1.data ->> 'col_0'= 'CPU' ;
⚠️ Select Time > 70967.075 ms (01:10.967) （70.967 sec）