【MySQL】5.7新特性之五

haoge0205發表於2016-11-22
MySql
一 寫在前面
  本系列文章基於 5.7.12 版本講述MySQL的新特性。從安裝，檔案結構，SQL ,最佳化 ，運維層面 複製，GITD等幾個方面展開介紹 5.7 的新特性和功能。同時也建議大家跟蹤官方blog和官方文件，以儘快知悉其新的變化。本文將重點介紹新版本對JSON格式的支援。
5.1  支援JSON
  從MySQL 5.7.8 開始，MySQL支援原生的JSON格式，即有獨立的json型別，用於存放 json格式的資料。JSON 格式的資料並不是以string格式儲存於資料庫而是以內部的binary 格式，以便於快速的定位到json 格式中值。
在插入和更新操作時MySQL會對JSON 型別做校驗，已檢查資料是否符合json格式，如果不符合則報錯。同時5.7.8 版本提供了四種JSON相關的函式，從而不用遍歷全部資料。
a 建立: JSON_ARRAY(), JSON_MERGE(), JSON_OBJECT()
b 修改: JSON_APPEND(), JSON_ARRAY_APPEND(), JSON_ARRAY_INSERT(), JSON_INSERT(), JSON_QUOTE(), JSON_REMOVE(), JSON_REPLACE(), JSON_SET(), and JSON_UNQUOTE()
c 查詢: JSON_CONTAINS(), JSON_CONTAINS_PATH(), JSON_EXTRACT(), JSON_KEYS(),JSON_SEARCH().
d 屬性: JSON_DEPTH(), JSON_LENGTH(), JSON_TYPE() JSON_VALID(). 
我們透過簡單的例子來對json有一定的認識。
建立
  1. mysql> SELECT JSON_ARRAY('id', 1, 'name', 'dba@youzan');
  2. +-------------------------------------------+
  3. | JSON_ARRAY('id', 1, 'name', 'dba@youzan') |
  4. +-------------------------------------------+
  5. | ["id", 1, "name", "dba@youzan"] |
  6. +-------------------------------------------+
  7. 1 row in set (0.00 sec)

  9. mysql> SELECT JSON_OBJECT('id', 1, 'name', 'dba@youzan');
  10. +--------------------------------------------+
  11. | JSON_OBJECT('id', 1, 'name', 'dba@youzan') |
  12. +--------------------------------------------+
  13. | {"id": 1, "name": "dba@youzan"} |
  14. +--------------------------------------------+
  15. 1 row in set (0.00 sec)
初始化 
  1. create table json_test (
  2.     id int(11) PRIMARY KEY NOT NULL auto_increment,
  3.     data json
  4.     ) engine=innodb default charset=utf8;
  5. insert into json_test values (1,'{ "DBA": [ { "firstName": "yi", "lastName":"yang", "email": "dba@youzan.com" }],
  6. "SA": [{ "firstName": "you", "lastName": "zan", "email": "sa@youzan.com" }],
  7. "PE": [{ "firstName": "xiao", "lastName": "xiao", "email": "pe@youzan.com" }] }')
修改
  1. mysql> select * from json_test \G
  2. *************************** 1. row ***************************
  3.   id: 1
  4. 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"}]}
  5. 1 row in set (0.00 sec)
  6. mysql> update json_test set data=json_array_append(data,'$.DBA','{"email": "dba@youzan.com", "lastName": "yang", "firstName": "qilong"}') where id=1;
  7. Query OK, 1 row affected (0.01 sec)
  8. Rows matched: 1 Changed: 1 Warnings: 0
  1. mysql> select * from json_test \G
  2. *************************** 1. row ***************************
  3.   id: 1
  4. 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\"}"]}
  5. 1 row in set (0.00 sec)
刪除 
  1. mysql> update json_test set data=json_remove(data,'$.DBA[1]') where id=1;
  2. Query OK, 1 row affected (0.01 sec)
  3. Rows matched: 1 Changed: 1 Warnings: 0
  4. mysql> select * from json_test \G
  5. *************************** 1. row ***************************
  6.   id: 1
  7. 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"}]}
  8. 1 row in set (0.00 sec)
檢視 json的key
  1. mysql> SELECT id,json_keys(data) as "keys" FROM json_test;
  2. +----+---------------------+
  3. | id | keys |
  4. +----+---------------------+
  5. | 1 | ["PE", "SA", "DBA"] |
  6. +----+---------------------+
  7. 1 row in set (0.00 sec)
  8. 檢視DBA對應的值
  9. mysql> SELECT id,json_extract(data,'$.DBA[0]') from json_test;
  10. +----+--------------------------------------------------------------------+
  11. | id | json_extract(data,'$.DBA[0]')                                      |
  12. +----+--------------------------------------------------------------------+
  13. | 1 | {"email": "dba@youzan.com", "lastName": "yang", "firstName": "yi"}  |
  14. +----+--------------------------------------------------------------------+
  15. 1 row in set (0.00 sec)
其他函式的用法請感興趣的讀者朋友自行參考《官方文件》
MySQL 5.7 版本提供的json格式以及對應的操作函式極豐富了MySQL的儲存格式,可以在一定程度上和Mongodb和pg競爭，對於經常使用MySQL varchar 儲存json的業務是一個福音。同時再強調一下對於OLTP業務的表結構設計 儘可能的避免大欄位儲存。一來是減少不必要的查詢帶來的IO，頻寬，記憶體方面的影響 二來是 避免因為表大小太大導致的ddl 時間成本增加系統風險。
5.2 sys schema
  MySQL 5.7 版本新增了sys 資料庫,該庫透過檢視的形式把information_schema 和performance_schema結合起來，查詢出更加令人容易理解的資料，幫助DBA快速獲取資料庫系統的各種緯度的後設資料資訊，幫助DBA和開發快速定位效能瓶頸。詳細的資訊請參考《官方文件》，這裡給兩個例子能直觀的瞭解sys 功能的強大。
  1. mysql> select * from sys.schema_table_statistics limit 2\G
  2. *************************** 1. row ***************************
  3.      table_schema: yang
  4.        table_name: json_test
  5.     total_latency: 1.81 ms
  6.      rows_fetched: 21
  7.     fetch_latency: 1.45 ms
  8.     rows_inserted: 2
  9.    insert_latency: 192.67 us
  10.      rows_updated: 2
  11.    update_latency: 166.94 us
  12.      rows_deleted: 0
  13.    delete_latency: 0 ps
  14.  io_read_requests: 54
  15.           io_read: 4.21 KiB
  16.   io_read_latency: 289.37 us
  17. io_write_requests: 43
  18.          io_write: 388.53 KiB
  19.  io_write_latency: 703.51 us
  20.  io_misc_requests: 75
  21.   io_misc_latency: 40.02 ms
  22. ##直接檢視未使用過的索引 ，方便嗎？
  23. mysql> SELECT * FROM schema_unused_indexes;
  24. +---------------+-------------+------------+
  25. | object_schema | object_name | index_name |
  26. +---------------+-------------+------------+
  27. | yang          | t           | idx_a |
  28. | yang          | yy          | idx_nm |
  29. +---------------+-------------+------------+
  30. 2 rows in set (0.00 sec)
參考文章
 [1] 《MySQL 5.7 官方文件》
[2] 《MySQL 5.7 初探》
[3] 《MySQL 5.7新特性之一》
[4] 《MySQL 5.7新特性之二》
[5] 《MySQL 5.7新特性之三》
[6] 《MySQL 5.7新特性之四》

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28939273/viewspace-2128847/，如需轉載，請註明出處，否則將追究法律責任。

相關文章