mysql 的json字段 性能 mysql 5.7 json性能

一 写在前面
  本系列文章基于 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. in set (0.00 sec)
9. > 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. in set (0.00 sec)

初始化 

1. create table json_test (
2. int(11) PRIMARY KEY NOT NULL auto_increment,
3.     data json
4. ) engine=innodb default charset=utf8;
5. into json_test values (1,'{ "DBA": [ { "firstName": "yi", "lastName":"yang", "email": "[e-mail]" }],
6. "SA": [{ "firstName": "you", "lastName": "zan", "email": "[e-mail]" }],
7. )

修改

1. mysql> select * from json_test \G
2. *************************** 1. row ***************************
3. : 1
4. : {"PE": [{"email": "[e-mail]", "lastName": "xiao", "firstName": "xiao"}], "SA": [{"email": "[e-mail]", "lastName": "zan", "firstName": "you"}], "DBA": [{"email": "[e-mail]", "lastName": "yang", "firstName": "yi"}]}
5. in set (0.00 sec)
6. > update json_test set data=json_array_append(data,'$.DBA','{"email": "[e-mail]", "lastName": "yang", "firstName": "qilong"}') where id=1;
7. , 1 row affected (0.01 sec)
8. : 1 Changed: 1 Warnings:
9. 
   

1. mysql> select * from json_test \G
2. *************************** 1. row ***************************
3. : 1
4. : {"PE": [{"email": "[e-mail]", "lastName": "xiao", "firstName": "xiao"}], "SA": [{"email": "[e-mail]", "lastName": "zan", "firstName": "you"}], "DBA": [{"email": "[e-mail]", "lastName": "yang", "firstName": "yi"}, "{\"email\": \"[e-mail]\", \"lastName\": \"yang\", \"firstName\": \"qilong\"}"]}
5. in set (0.00 sec)

删除 

1. mysql> update json_test set data=json_remove(data,'$.DBA[1]') where id=1;
2. , 1 row affected (0.01 sec)
3. : 1 Changed: 1 Warnings: 0
4. > select * from json_test \G
5. *************************** 1. row ***************************
6. : 1
7. : {"PE": [{"email": "[e-mail]", "lastName": "xiao", "firstName": "xiao"}], "SA": [{"email": "[e-mail]", "lastName": "zan", "firstName": "you"}], "DBA": [{"email": "[e-mail]", "lastName": "yang", "firstName": "yi"}]}
8. 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. in set (0.00 sec)
8. 查看DBA对应的值
9. > SELECT id,json_extract(data,'$.DBA[0]') from json_test;
10. +----+--------------------------------------------------------------------+
11. | id | json_extract(data,'$.DBA[0]')                                      |
12. +----+--------------------------------------------------------------------+
13. | 1 | {"email": "[e-mail]", "lastName": "yang", "firstName": "yi"}  |
14. +----+--------------------------------------------------------------------+
15. 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. : yang
4. : json_test
5. : 1.81 ms
6. : 21
7. : 1.45 ms
8. : 2
9. : 192.67 us
10. : 2
11. : 166.94 us
12. : 0
13. : 0 ps
14. : 54
15. : 4.21 KiB
16. : 289.37 us
17. : 43
18. : 388.53 KiB
19. : 703.51 us
20. : 75
21. : 40.02 ms
22. ##直接查看未使用过的索引 ，方便吗？
23. > 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. 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新特性之四》