什麼是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為新建立的虛擬列。