什么是ReadableProtobuf,如何使用ReadableProtobuf - 云原生数据库 PolarDB

PolarDB MySQL版支持Readable Protobuf功能，即针对存储在数据库中的经过Protobuf序列化的Blob类型的字段，您可以在对应的字段上配置Protobuf schema，并通过可视化函数PROTO_TO_JSON(blob_field)来读取数据。同时，您也可以使用JSON_EXTRACT()函数来抽取数据中的部分信息用于创建索引或者虚拟列。

背景信息

在游戏行业，某些信息在存储时经过了Protobuf序列化，甚至可能还经过了ZLIB压缩，然后才写入数据库中的Blob类型的字段中。这时，数据库中的Blob类型的数据没有办法直接被读取，对于软件调试和开发工作很不友好，并且在数据分析场景也需要维护额外的组件来读取数据。

PolarDB MySQL版提供的Readable Protobuf功能，支持使用可视化函数来直接读取经过Protobuf序列化且经过ZLIB压缩的数据，而不需要借助额外的组件。

前提条件

PolarDB集群版本需为PolarDB MySQL版8.0版本且Revision version为8.0.2.2.5及以上，您可以通过查询版本号确认集群版本。

使用方法

配置Protobuf schema

语法

ALTER TABLE table_name ALTER COLUMN column_name
[PROTO_NAME = protobuf_schema_name]
PROTO_TEXT = protobuf_schema_definition
PROTO_MESSAGE = protobuf_message
[COMPRESSION = compression_algorithm]

参数说明

参数	是否必选	说明
PROTO_NAME	否	Protobuf的schema名称。
PROTO_TEXT	是	Protobuf schema的定义。
PROTO_MESSAGE	是	序列化的Protobuf Message。
COMPRESSION	否	当序列化的Protobuf Message数据在写入数据库之前，经过了ZLIB压缩时需要配置该选项。目前仅支持配置为ZLIB。说明经过ZLIB压缩的数据可以使用`UNCOMPRESS()`函数来进行解压，且解压后的数据为十六进制的数据。

取消字段的Protobuf schema定义
将Protobuf schema设置为空，即可取消字段的Protobuf schema定义。命令如下：
```
ALTER TABLE table_name ALTER COLUMN column_name PROTO_NAME="" PROTO_TEXT="" PROTO_MESSAGE=''; 
```
说明
取消字段的Protobuf schema定义前，请确保该字段与相关的索引和虚拟列已解除关联关系。
查看字段的Protobuf schema定义
1. 执行以下命令，将display_readable_proto_info设置为true。
```
SET display_readable_proto_info=true;
```
2. 执行以下命令，查看字段的Protobuf schema定义。
```
SHOW columns FROM table_name
```

示例

以表t1为例，介绍如何使用Readable Protobuf功能，以及如何使用可视化函数PROTO_TO_JSON(blob_field)提取数据并用来创建索引或虚拟列等。

创建表t1，建表语句如下：
```
CREATE TABLE t1(c1 INT, c2 BLOB);
```
其中，c2是经过Protobuf序列化的Blob类型的字段。

为c2字段添加Protobuf Schema定义。

此处使用Protobuf社区的addressbook.proto，如下：

syntax = "proto2";

package tutorial;

message Person {
optional string name = 1;
optional int32 id = 2;
optional string email = 3;

enum PhoneType {
MOBILE = 0;
HOME = 1;
WORK = 2;
}

message PhoneNumber {
optional string number = 1;
optional PhoneType type = 2 [default = HOME];
}

repeated PhoneNumber phones = 4;
}

message AddressBook {
repeated Person people = 1;
}

数据未经过ZLIB压缩时，示例如下：

ALTER TABLE t1 ALTER COLUMN c2 PROTO_NAME="AddressBook" PROTO_TEXT="syntax = \"proto2\";\n\npackage tutorial;\n\nmessage Person {\n  optional string name = 1;\n  optional int32 id = 2;\n  optional string email = 3;\n\n  enum PhoneType {\n    MOBILE = 0;\n    HOME = 1;\n    WORK = 2;\n  }\n\n  message           PhoneNumber {\n    optional string number = 1;\n    optional PhoneType type = 2 [default = HOME];\n  }\n\n  repeated PhoneNumber phones = 4;\n}\n\nmessage  AddressBook {\n  repeated Person people = 1;\n}" PROTO_MESSAGE='AddressBook';

数据经过ZLIB压缩时，示例如下：

ALTER TABLE t1 ALTER COLUMN c2 PROTO_NAME="AddressBook" PROTO_TEXT="syntax = \"proto2\";\n\npackage tutorial;\n\nmessage Person {\n  optional string name = 1;\n  optional int32 id = 2;\n  optional string email = 3;\n\n  enum PhoneType {\n    MOBILE = 0;\n    HOME = 1;\n    WORK = 2;\n  }\n\n  message           PhoneNumber {\n    optional string number = 1;\n    optional PhoneType type = 2 [default = HOME];\n  }\n\n  repeated PhoneNumber phones = 4;\n}\n\nmessage  AddressBook {\n  repeated Person people = 1;\n}" PROTO_MESSAGE='AddressBook' COMPRESSION='zlib';

将通过Protobuf序列化后的数据写入表t。

数据未经过ZLIB压缩时，示例如下：

INSERT INTO t1 VALUES(1, X'0a380a0b56697375616c50726f746f10011a1776697375616c70726f746f40706f6c617264622e636f6d220e0a0a313233343536373839301002');

数据经过ZLIB压缩时，示例如下：

INSERT INTO t1 VALUES(1, X'3C000000785ee3b2e0e20ecb2c2e4dcc0928ca2fc9176094122f03730b405c8782fc9cc4a29424bde4fc5c253e2e2e432363135333730b4b03012600183d10de');

经过ZLIB压缩后的数据可以通过UNCOMPRESS()函数进行解压，以经过ZLIB压缩的数据为例，示例如下：

SELECT HEX(uncompress(X'3C000000785ee3b2e0e20ecb2c2e4dcc0928ca2fc9176094122f03730b405c8782fc9cc4a29424bde4fc5c253e2e2e432363135333730b4b03012600183d10de')) AS UNCOMPRESS_DATA;

解压后的十六进制数据如下：

+----------------------------------------------------------------------------------------------------------------------+
| UNCOMPRESS_DATA                                                                                                      |
+----------------------------------------------------------------------------------------------------------------------+
| 0A380A0B56697375616C50726F746F10011A1776697375616C70726F746F40706F6C617264622E636F6D220E0A0A313233343536373839301002 |
+----------------------------------------------------------------------------------------------------------------------+

读取c2列的数据或提取c2列的数据来创建索引或虚拟列。

读取c2列的数据。

未使用可视化函数PROTO_TO_JSON(blob_field)时，读取c2列的数据。

数据未经过ZLIB压缩时，执行如下命令读取c2列的数据：

SELECT c2 FROM t1\G

读取的数据内容如下：

*************************** 1. row ***************************
c2:
8

VisualProtovisualpr***@polardb.com"

1234567890

数据经过ZLIB压缩时，执行如下命令读取c2列的数据：

SELECT c2 FROM t1\G

读取的数据内容如下：

*************************** 1. row ***************************
c2: <   x^����,.M�    (�/�`�/s
                                @\���Ģ�$���\%>..C#cS3s
                                                      K& =�

通过可视化函数PROTO_TO_JSON(blob_field)读取c2的内容。

SELECT PROTO_TO_JSON(c2) FROM t1;

读取的数据内容如下：

+------------------------------------------------------------------------------------------------------------------------------------------+
| PROTO_TO_JSON(c2)                                                                                                                        |
+------------------------------------------------------------------------------------------------------------------------------------------+
| {"people": [{"id": 1, "name": "VisualProto", "email": "visualpr***@polardb.com", "phones": [{"type": "WORK", "number": "1234567890"}]}]} |
+------------------------------------------------- ----------------------------------------------------------------------+

说明

通过可视化函数PROTO_TO_JSON(blob_field)读取数据时，经过ZLIB压缩的数据和未经过ZLIB压缩的数据均能被读取。

使用JSON函数提取c2列的部分数据，示例如下：

SELECT json_extract(PROTO_TO_JSON(c2), '$.people[0].name') FROM t1;

提取的数据内容如下：

+-----------------------------------------------------+
| json_extract(PROTO_TO_JSON(c2), '$.people[0].name') |
+-----------------------------------------------------+
| "VisualProto"                                       |
+-----------------------------------------------------+

提取c2列的数据来创建索引，示例如下：

CREATE INDEX i_email ON t1((cast(JSON_UNQUOTE(json_extract(PROTO_TO_JSON(c2), '$.people[0].email')) AS char(100))));

使用EXPLAIN 命令检测SQL语句的执行性能，示例如下：

EXPLAIN SELECT * FROM t1 WHERE (cast(JSON_UNQUOTE(json_extract(PROTO_TO_JSON(c2), '$.people[0].ema

执行结果如下：

+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | i_email       | i_email | 403     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+

提取c2列的数据来创建虚拟列，示例如下：

ALTER TABLE t1 ADD COLUMN c3 varchar(100) AS (json_extract(proto_to_json(`c2`), _utf8mb4'$.people[0].email'));

执行如下命令，查看t1表的表结构：

desc t1;

t1表的表结构如下：

+-------+--------------+------+-----+---------+-------------------+
| Field | Type         | Null | Key | Default | Extra             |
+-------+--------------+------+-----+---------+-------------------+
| c1    | int(11)      | YES  |     | NULL    |                   |
| c2    | blob         | YES  |     | NULL    |                   |
| c3    | varchar(100) | YES  |     | NULL    | VIRTUAL GENERATED |
+-------+--------------+------+-----+---------+-------------------+

其中，c3为新创建的虚拟列。