MySQL :: A Quick Look of JSON Data Type Over OneProxy on MySQL 5.7

MySQL 5.7 support JSON data type now, this is a good feature for internet application, let’s take a quick look of it. First I create four sharding tables over OneProxy in single MySQL instance.

mysql> create table my_hash (id int not null primary key, doc json);
Query OK, 0 rows affected (0.59 sec)

mysql> show tables;
+------------------+
| Tables_in_test   |
+------------------+
| my_hash_0        |
| my_hash_1        |
| my_hash_2        |
| my_hash_3        |
| pre_forum_post_6 |
| pre_forum_post_7 |
| t_json           |
+------------------+
7 rows in set (0.00 sec)

And insert some rows into the table for testing purpose.

mysql> insert into my_hash (id, doc) 
    ->    values (1, '{"key1": "row1_1", "key2": "row1_2"}');
Query OK, 1 row affected (0.02 sec)

mysql> insert into my_hash (id, doc) 
    ->    values (2, '{"key1": "row2_1", "key2": "row2_2"}');
Query OK, 1 row affected (0.02 sec)

mysql> insert into my_hash (id, doc) 
    ->    values (3, '{"key1": "row3_1", "key2": "row3_2"}');
Query OK, 1 row affected (0.02 sec)

mysql> insert into my_hash (id, doc) 
    ->    values (4, '{"key1": "row4_1", "key2": "row4_2"}');
Query OK, 1 row affected (0.02 sec)

Now let’s run some query to extract the values for “key2” property, OneProxy has support “->” operator now.

mysql> select doc->"$.key1" from my_hash where id =2;
+---------------+
| doc->"$.key1" |
+---------------+
| "row2_1"      |
+---------------+
1 row in set (0.00 sec)

mysql> select json_extract(doc,"$.key1") from my_hash where id =2;
+------------------------------+
| json_extract(doc , '$.key1') |
+------------------------------+
| "row2_1"                     |
+------------------------------+
1 row in set (0.00 sec)

Then you can operate the JSON value by different JSON functions, for example to add a new element to JSON value.

mysql> update my_hash set doc = json_set(doc, "$.newkey", 'new value')
    -> where id = 2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from my_hash where id = 2;
+----+-------------------------------------------------------------+
| id | doc |
+----+-------------------------------------------------------------+
| 2  | {"key1": "row2_1", "key2": "row2_2", "newkey": "new value"} |
+----+-------------------------------------------------------------+
1 row in set (0.00 sec)

How is the performance to extract one proeprty from the JSON doc? Let’s take a benchmark with the following testing case.

option
  user test/test@172.30.12.15:3307:test
  time 1m
  log /dev/null
declare
  vid int 1 4
begin
  select doc->"$.key1" from my_hash where id = :vid;
end

Here is the result  (MySQL run ot of 8 cores and OneProxy run out of other 4 Cores).

[root@localhost data]# ./mydbtest_linux64.bin query=test1.sql degree=200
Summary: SQL01 exec=8833459, rows=8833459=100/e, avg=1378 us
Summary: exec=142475/s, qtps=142475/s

The JSON feature can reduce the network traffic between the application and database servers greatly to improve the overall system performance.