MySQL 5.7 native JSON format

MySQL 5.7 native JSON format

In the comparison between MySQL and PostgreSQL, the advantages of PostgreSQL JSON format support are always compared. In fact, MariaDB also stored unstructured data earlier, known as dynamic column, but the solution is to store data in BLOB type. The problem is that the query performance is not high and indexes cannot be effectively created. Compared with some document databases, the query performance is not high, so the response in the Community is average. Of course, the dynamic column function of MariaDB is not limited to the storage of unstructured data, but is not described in this article.

MySQL 5.7.7 labs began to support the InnoDB Storage engine in JSON format. This format is not a simple replacement of BLOB. Native JSON format supports the following advantages:

• JSON Data Validity Check: The BLOB type cannot perform such binding check at the database layer.
• Query performance improvement: you do not need to traverse all strings to query data.
• Supports indexing: partial data in JSON can be indexed using the virtual column function.

 
 

  
  
mysql> create table user ( uid int auto_increment, 
  
  
    -> data json,primary key(uid))engine=innodb; 
  
  
Query OK, 0 rows affected (0.01 sec) 
  
  
  
  
  
mysql> insert into user values (NULL, 
  
  
    -> '{"name":"David","mail":"jiangchengyao@gmail.com","address":"Shangahai"}'); 
  
  
Query OK, 1 row affected (0.00 sec) 
  
  
  
  
  
mysql> insert into user values (NULL,'{"name":"Amy","mail":"amy@gmail.com"}'); 
  
  
Query OK, 1 row affected (0.00 sec) 
 
 

We can see that the table user is created and the column data is defined as JSON type. This means that we can check the inserted data in JSON format to ensure that it complies with the JSON format constraints. If an invalid JSON data is inserted, the following error is returned:

 
 

  
  
mysql> insert into user values (NULL,"test"); 
  
  
ERROR 3130 (22032): Invalid JSON text: "Invalid value" at position 2 in value (or column) 'test'. 
 
 

In addition, as mentioned above, MySQL 5.7 provides a series of functions to efficiently process JSON characters, instead of traversing all characters for search. This has to be said to be a huge improvement for MariaDB dynamic column:

 
 

  
  
mysql> select jsn_extract(data, '$.name'),jsn_extract(data,'$.address') from user; 
  
  
+-----------------------------+-------------------------------+ 
  
  
| jsn_extract(data, '$.name') | jsn_extract(data,'$.address') | 
  
  
+-----------------------------+-------------------------------+ 
  
  
| "David" | "Shangahai" | 
  
  
| "Amy" | NULL | 
  
  
+-----------------------------+-------------------------------+ 
  
  
2 rows in set (0.00 sec) 
 
 

Of course, the most exciting feature is the virtual column feature of MySQL 5.7. Using the traditional B + tree index, you can quickly query attributes in JSON format. First, create the virtual column and then create an index on the virtual column:

 
 

  
  
mysql> ALTER TABLE user ADD user_name varchar(128) 
  
  
    -> GENERATED ALWAYS AS (jsn_extract(data,'$.name')) VIRTUAL; 
  
  
Query OK, 0 rows affected (0.01 sec) 
  
  
Records: 0 Duplicates: 0 Warnings: 0 
  
  
  
  
  
mysql> select user_name from user; 
  
  
+-----------+ 
  
  
| user_name | 
  
  
+-----------+ 
  
  
| "Amy"     | 
  
  
| "David"   | 
  
  
+-----------+ 
  
  
2 rows in set (0.00 sec) 
  
  
  
  
  
mysql> alter table user add index idx_username (user_name); 
  
  
Query OK, 2 rows affected (0.01 sec) 
  
  
Records: 2  Duplicates: 0  Warnings: 0 
 
 

Then, you can quickly query the user name through the added index, which is the same as the normal column query. The explain command can verify that the optimizer has selected the new index created on the virtual column:

 
 

  
  
mysql> explain select * from user where user_name='"Amy"'\G 
  
  
*************************** 1. row *************************** 
  
  
           id: 1 
  
  
  select_type: SIMPLE 
  
  
        table: user 
  
  
   partitions: NULL 
  
  
         type: ref 
  
  
possible_keys: idx_username 
  
  
          key: idx_username 
  
  
      key_len: 131 
  
  
          ref: const 
  
  
         rows: 1 
  
  
     filtered: 100.00 
  
  
        Extra: NULL 
  
  
1 row in set, 1 warning (0.00 sec) 
 
 

We can see that MySQL 5.7 is perfect for the JSON format. I believe the PostgreSQL Camp needs to find new policies to "attack" MySQL. If there is no accident, it will remain in the optimizer. After all, this is the biggest problem that MySQL must overcome at present. Fortunately, the MySQL team is already restructuring the optimizer code, we believe that a better optimizer will be available in the next version. A lot of document databases are already crying in the toilet.

Blog Source: http://www.cnblogs.com/zoucaitou/p/4424575.html