Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about the json storage of Mysql5.7 and how sys schema is. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

This paper focuses on the new version's support for JSON format.

1 supports JSON

Starting with MySQL 5.7.8, MySQL supports the native JSON format, which has a separate json type for storing data in json format. The data in JSON format is not stored in the database in string format, but in internal binary format, so as to quickly locate the value in json format.

During insert and update operations, MySQL verifies the JSON type and checks whether the data conforms to the json format. If not, an error is reported. At the same time, version 5.7.8 provides four JSON-related functions so that you don't have to traverse all the data.

A create: JSON_ARRAY (), JSON_MERGE (), JSON_OBJECT ()

B modify: JSON_APPEND (), JSON_ARRAY_APPEND (), JSON_ARRAY_INSERT (), JSON_INSERT (), JSON_QUOTE (), JSON_REMOVE (), JSON_REPLACE (), JSON_SET (), and JSON_UNQUOTE ()

C query: JSON_CONTAINS (), JSON_CONTAINS_PATH (), JSON_EXTRACT (), JSON_KEYS (), JSON_SEARCH ().

D attribute: JSON_DEPTH (), JSON_LENGTH (), JSON_TYPE () JSON_VALID ().

We have a certain understanding of json through simple examples.

Create

Mysql > SELECT JSON_ARRAY ('id', 1,' name', 'dba@youzan')

+-- +

| | JSON_ARRAY ('id', 1,' name', 'dba@youzan') | |

+-- +

| | ["id", 1, "name", "dba@youzan"] |

+-- +

1 row in set (0.00 sec)

Mysql > SELECT JSON_OBJECT ('id', 1,' name', 'dba@youzan')

+-- +

| | JSON_OBJECT ('id', 1,' name', 'dba@youzan') | |

+-- +

| | {"id": 1, "name": "dba@youzan"} |

+-- +

1 row in set (0.00 sec)

Initialization

Create table json_test (

Id int (11) PRIMARY KEY NOT NULL auto_increment

Data json

) engine=innodb default charset=utf8

Insert into json_test values (1)'{"DBA": [{"firstName": "yi", "lastName": "yang", "email": "dba@youzan.com"}]

"SA": [{"firstName": "you", "lastName": "zan", "email": "sa@youzan.com"}]

"PE": [{"firstName": "xiao", "lastName": "xiao", "email": "pe@youzan.com"}]}')

Modify

Mysql > select * from json_test\ G

* * 1. Row *

Id: 1

Data: {"PE": [{"email": "pe@youzan.com", "lastName": "xiao", "firstName": "xiao"}], "SA": [{"email": "sa@youzan.com", "lastName": "zan", "firstName": "you"}], "DBA": [{"email": "dba@youzan.com", "lastName": "yang", "firstName": "yi"}]}

1 row in set (0.00 sec)

Mysql > update json_test set data=json_array_append (data,'$.DBA',' {"email": "dba@youzan.com", "lastName": "yang", "firstName": "qilong"}') where id=1

Query OK, 1 row affected (0.01sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > select * from json_test\ G

* * 1. Row *

Id: 1

Data: {"PE": [{"email": "pe@youzan.com", "lastName": "xiao", "firstName": "xiao"}], "SA": [{"email": "sa@youzan.com", "lastName": "zan", "firstName": "you"}], "DBA": [{"email": "dba@youzan.com", "lastName": "yang", "firstName": "yi"} "{\" email\ ":\" dba@youzan.com\ ",\" lastName\ ":\" yang\ ",\" firstName\ ":\" qilong\ "}]}

1 row in set (0.00 sec)

Delete

Mysql > update json_test set data=json_remove (data,'$.DBA [1]') where id=1

Query OK, 1 row affected (0.01sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > select * from json_test\ G

* * 1. Row *

Id: 1

Data: {"PE": [{"email": "pe@youzan.com", "lastName": "xiao", "firstName": "xiao"}], "SA": [{"email": "sa@youzan.com", "lastName": "zan", "firstName": "you"}], "DBA": [{"email": "dba@youzan.com", "lastName": "yang", "firstName": "yi"}]}

1 row in set (0.00 sec)

-- delete statement

Update history_tab_sum set data=json_remove (data,'$. "2017.11.28"')

-- Update

-- update history_tab_sum set total=json_replace (total,'$. "2017-11-28", id)

-- insert:

UPDATE history_tab_sum set data=JSON_MERGE (data,' {"2017-11-28": 3344}') where id=10283

-- append

Update history_tab_sum set data=json_array_append (data,'$. "2017-11-28", id) where id=10283

View the key of json

Mysql > SELECT id,json_keys (data) as "keys" FROM json_test

+-+ +

| | id | keys |

+-+ +

| | 1 | ["PE", "SA", "DBA"] |

+-+ +

1 row in set (0.00 sec)

Check the corresponding value of DBA

Mysql > SELECT id,json_extract (data,'$.DBA [0]') from json_test

+-+-

| | id | json_extract (data,'$.DBA [0]') |

+-+-

| | 1 | {"email": "dba@youzan.com", "lastName": "yang", "firstName": "yi"} |

+-+-

1 row in set (0.00 sec)

The json format provided by MySQL version 5.7 and the corresponding operation functions greatly enrich the storage format of MySQL, which can compete with Mongodb and pg to some extent, which is a boon for businesses that often use MySQL varchar to store json. At the same time, it is emphasized that the table structure design of OLTP business should avoid large field storage as much as possible. One is to reduce the impact of unnecessary queries on IO, bandwidth, and memory, and the other is to avoid the ddl time cost caused by the large table size to increase the system risk.

2 sys schema

MySQL version 5.7 adds sys database, which combines information_schema and performance_schema in the form of views to query more understandable data, helps DBA to quickly obtain metadata information of various latitudes of the database system, and helps DBA and developers to quickly locate performance bottlenecks. Here are two examples to intuitively understand the power of sys.

Mysql > select * from sys.schema_table_statistics limit 2\ G

* * 1. Row *

Table_schema: yang

Table_name: json_test

Total_latency: 1.81 ms

Rows_fetched: 21

Fetch_latency: 1.45 ms

Rows_inserted: 2

Insert_latency: 192.67 us

Rows_updated: 2

Update_latency: 166.94 us

Rows_deleted: 0

Delete_latency: 0 ps

Io_read_requests: 54

Io_read: 4.21 KiB

Io_read_latency: 289.37 us

Io_write_requests: 43

Io_write: 388.53 KiB

Io_write_latency: 703.51 us

Io_misc_requests: 75

Io_misc_latency: 40.02 ms

# # is it convenient to view unused indexes directly?

Mysql > SELECT * FROM schema_unused_indexes

+-+

| | object_schema | object_name | index_name | |

+-+

| | yang | t | idx_a |

| | yang | yy | idx_nm | |

+-+

2 rows in set (0.00 sec)

After reading the above, do you have any further understanding of Mysql5.7 's json storage and sys schema? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.