MySQL 5.7 Native JSON format support

In the comparison between MySQL and PostgreSQL, the JSON format support advantages of PG are always compared. In fact, the previous MARIADB also had a scheme for storing unstructured data, called dynamic column, but the scheme was stored in the form of BLOB types. This leads to the problem is that the query performance is not high, not effectively indexed, compared with some document databases, the advantages are not large, so the response in the community is actually more general. Of course, MARIADB's dynamic column functionality is not limited to the storage of unstructured data, but is not expanded in this article.

MySQL 5.7.7 Labs version begins INNODB The storage engine has natively supported JSON format, which is not a simple blob-like replacement. Native JSON format support has the following advantages:

• JSON data validation: BLOB types cannot do such a restrictive check at the database layer
• Improved query performance: Queries do not need to traverse all strings to find data
• Support index: Partial data in JSON can be indexed by the function of a virtual column

Let's start by looking at how to use the native JSON format in MySQL:

12345678910

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":"[email protected]","address":"Shangahai"}‘);Query OK, 1 row affected (0.00 sec)mysql> insert into user values (NULL,‘{"name":"Amy","mail":"[email protected]"}‘);Query OK, 1 row affected (0.00 sec)

The

can see that we have created a new table user, and that the column data is defined for the JSON type. This means that we can check the inserted data in JSON format to ensure that it conforms to the JSON format constraints, such as inserting an illegal JSON data will report the following error:

12	MYSQL>  insert into user values ( null error 3130 (22032): Invalid JSON text:  "Invalid value" at position 2  in value ( or column ' test ' .

Also, as mentioned earlier, MySQL 5.7 provides a series of functions to efficiently handle JSON characters instead of having to traverse through all the characters to find, which has to be said to be a huge improvement to the MARIADB dynamic column:

12345678

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 should be the MySQL 5.7 virtual column function, the traditional B + Tree index can be implemented in the JSON format part of the properties of the fast query. It is used by first creating the virtual column and then creating an index on that virtual column:

1234567891011121314151617

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: 0mysql> 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

You can then quickly query the user name by adding an index, which is the same as a normal type of column query. With explain, you can verify that the optimizer has selected a new index created on the virtual column:

123456789101112131415

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

MySQL 5.7 can be found to be perfect for JSON format, I believe the PostgreSQL camp need to find a new strategy to "attack" MySQL. If there is no accident, or will stay in the optimizer this piece, after all, this is the current MySQL must overcome the biggest problem, fortunately, the MySQL team is already refactoring the optimizer code, I believe the better optimizer will be in the next version of the full outbreak. And a whole bunch of document databases are already crying out in the toilet.

MySQL 5.7 Native JSON format support