JSON Support

请加入Percona的顾问DavidDucos,因为他展示了“MySQL5.7中的JSON”。由于在MySQL5.7内部实现,我们可以选择使用JSON作为数据类型。在本次网络研讨会中,我们将回顾一些添加到JSON工作中的有用函数。我们将检查和分析JSON如何在内部工作,并考虑到与使用这种新技术相关的一些成本。
在网络研讨会结束时,您将知道以下问题的答案:
什么是JSON?
我们为什么不继续使用varchar?
它是如何工作的?
费用是多少?
我们应该考虑哪些限制?
使用MySQLJSON支持的好处是什么?

展开查看详情

1.JSON Support An overview on how MySQL supports JSON David Ducos

2.What are we going to see? • What is JSON? • Why don’t keep using VARCHAR? • How does it work? Costs perspective • Limitations and things to take into account • Benefits of using MySQL JSON Support 2

3. What is JSON? JSON (JavaScript Object Notation) How to think it in mysql? But, it is not just an string: OBJECT: JSON objects {"key": JSON_TYPE } set a=5; ARRAY: JSON arrays [,,] BOOLEAN: The JSON true and false literals set a=‘{“id”:”5”}’; NULL: The JSON null literal String and numbers It is a String! you can use string functions: There are some useful functions: JSON_TYPE() mysql> select doc from person limit 1; JSON_ARRAY() +-------------------------------------------------------------------------------------------------+ | doc | JSON_OBJECT() +-------------------------------------------------------------------------------------------------+ | {"_id": "1", "Name": "01234567890123456789012345678901234567890123456789012345678901234567890"} | JSON_EXTRACT() +-------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) JSON_SEARCH() mysql> select replace(doc,'Name','Another thing') from person limit 1; JSON_MERGE() +----------------------------------------------------------------------------------------------------------+ JSON_REPLACE() | replace(doc,'Name','Another thing') | +----------------------------------------------------------------------------------------------------------+ | {"_id": "1", "Another thing": "01234567890123456789012345678901234567890123456789012345678901234567890"} | +----------------------------------------------------------------------------------------------------------+ At the end, you can play with a string inside 1 row in set (0.00 sec) MySQL with some tools. 3

4.Why don’t keep using VARCHAR? • You have the functions which makes your work simpler • You don’t need to check syntaxes as it has Automatic validation • You never know the correct VARCHAR size for a JSON, 100? 200? 8000? • New Structures: several Key-Value per row and (ordered) list of elements and recursive structures • You work with Strings in a fashion way 4

5.Let’s see examples 1 mysql> CREATE TABLE person (jdoc JSON); Query OK, 0 rows affected (0.04 sec) mysql> select * from person where JSON_SEARCH(jdoc- >’$.Surname','all','Ruiz' ) is not null; mysql> INSERT INTO person VALUES('{"Name": "David", "Surname": +--------------------------------------------------+ "Ducos"}'); | jdoc | Query OK, 1 row affected (0.01 sec) +--------------------------------------------------+ | {"Name": "Soledad", "Surname": ["Ruiz", "Diaz"]} | mysql> select * from person where jdoc->'$.Surname' = "Ducos"; +--------------------------------------------------+ +---------------------------------------+ 1 row in set (0.00 sec) | jdoc | +---------------------------------------+ mysql> INSERT INTO person VALUES('{"Name": "Bruno", | {"Name": "David", "Surname": "Ducos"} | "Surname": "Diaz"}'); +---------------------------------------+ Query OK, 1 row affected (0.01 sec) 1 row in set (0.00 sec) mysql> select * from person where JSON_SEARCH(jdoc- mysql> INSERT INTO person VALUES('{"Name": "Soledad", >'$.Surname','all','Diaz' ) is not null; "Surname": ["Ruiz","Diaz"]}'); +--------------------------------------------------+ Query OK, 1 row affected (0.00 sec) | jdoc | +--------------------------------------------------+ mysql> select * from person where jdoc->'$.Surname' = "Ruiz"; | {"Name": "Soledad", "Surname": ["Ruiz", "Diaz"]} | Empty set (0.00 sec) | {"Name": "Bruno", "Surname": "Diaz"} | mysql> select * from person where jdoc->'$.Surname' = +--------------------------------------------------+ '["Ruiz", "Diaz"]'; 2 rows in set (0.00 sec) Empty set (0.00 sec) 5

6.Let’s see examples 2 mysql> update person set jdoc=JSON_MERGE(jdoc,’{"Name" : "Miriam"}') mysql> update person set jdoc=JSON_REPLACE(jdoc,"$.Name" , "['Miriam','Soledad']") where JSON_SEARCH(jdoc->'$.Surname','all','Ruiz' ) is not null and where JSON_SEARCH(jdoc->'$.Surname','all','Ruiz' ) is not null and JSON_SEARCH(jdoc- JSON_SEARCH(jdoc->'$.Surname','all','Diaz' ) is not null; >'$.Surname','all','Diaz' ) is not null; Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from person mysql> select * from person where JSON_SEARCH(jdoc->'$.Surname','all','Ruiz' ) is not null and where JSON_SEARCH(jdoc->’$.Surname','all','Ruiz' ) is not null and JSON_SEARCH(jdoc- json_search(jdoc->'$.Surname','all','Diaz' ) is not null; >'$.Surname','all','Diaz' ) is not null; +--------------------------------------------------------------+ +---------------------------------------------------------------+ | jdoc | | jdoc | +--------------------------------------------------------------+ +---------------------------------------------------------------+ | {"Name": ["Soledad", "Miriam"], "Surname": ["Ruiz", "Diaz"]} | | {"Name": ["Miriam", "Soledad"], "Surname": ["Ruiz", "Diaz"]} | +--------------------------------------------------------------+ +---------------------------------------------------------------+ 1 row in set (0.00 sec) 1 row in set (0.00 sec) mysql> select jdoc->"$.Surname[0]" from person; +----------------------+ | jdoc->"$.Surname[0]" | +----------------------+ | "Ducos" | | "Ruiz" | | "Diaz" | +----------------------+ 3 rows in set (0.00 sec) mysql> select jdoc->"$.Surname[1]" from person; +----------------------+ | jdoc->"$.Surname[1]" | +----------------------+ | NULL | | "Diaz" | 6 | NULL | +----------------------+

7.How does it work? What are the Costs? • When you insert the JSON is validated • Internal binary format for quick access What are the Costs? • Tested with sysbench for write performance • Local VM • 2 Threads over 4 Tables • 500000 requests 7

8.How does it work? What are the Costs? Details of the test • Normal LUA insert line: rs = db_query("INSERT INTO " .. table_name .. " (id, k, c, pad) VALUES " .. string.format("(%d, %d, '%s','%s')",i, k_val, c_val, pad_val)) INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 0, ‘74439754257-89435450632-10029680936-01783388742-41861084122- 02284167466-42765454775-69149408995-51723233043-87650756047’,'68425956890-47076763298-51966610044- 96793431771-00000536786'); • JSON LUA insert line: rs = db_query("INSERT INTO " .. table_name .. " (doc) VALUES " .. string.format("('{\"_id\" : \"%d\", \"k\":\"%d\", \"c\":\"%s\",\"pad\":\"%s\" }')",i, k_val, c_val, pad_val)) INSERT INTO sbtest_json1 (doc) VALUES ('{"_id" : "47483648", "k":"59263672", "c":"24912351166-62227201466-48573979646-46226163775- 05759394754-70094713410-97360717151-61106334505-35565977631-88288836923","pad":"51140030063-90587585668- 02097351492-82961843495-96942957620" }’); 8

9.Costs Write Throughput Max Response Time Table Size MB Avg Response Time 300 12500 1.5 700 10000 1.2 225 525 7500 0.9 150 350 5000 0.6 75 175 2500 0.3 0 0 0 0 JSON Normal JSON Normal JSON Normal JSON Normal sbtest1 sbtest2 Writes/sec Average 95% Max Response Time sbtest3 sbtest4 9

10.Costs - Migration Add a new column in the current table // Add a new table Adding a new extra JSON empty column increased table size (~28 bytes/row) A table with 1M rows will add 28MB - EMPTY Move the data Similar pt-online-schema-change process which involves triggers 10

11.Limitations / Take into account • The size of JSON documents stored in JSON columns is limited to the value of the max_allowed_packet system variable. (While the server manipulates a JSON value internally in memory, it can be larger; the limit applies when the server stores it). Default: 4MB / Max:1GB - Dynamic • Sort keys for JSON values are bound by the value of the max_sort_length system variable, so keys that differ only after the first max_sort_length bytes compare as equal. It is important as we are working with Strings. Default: 1KB / Max: 8MB - Dynamic 11

12.Benefits of using MySQL JSON Support • Some of the same reasons as you decide to use MongoDB • Dynamic structure • Faster way to developers to introduce changes • Migration opportunities • Secure and optimized • Not supported structures are now supported • Opportunity to re think solutions for some problems 12

13. Q&A MySQL is starting to play with JSON By David Ducos

14.Join us at Percona Live When: April 24-27, 2017 Where: Santa Clara, CA, USA The Percona Live Open Source Database Conference is a great event for users of any level using open source database technologies. • Get briefed on the hottest topics • Learn about building and maintaining high-performing deployments • Listen to technical experts and top industry leaders Use promo code “WebinarPL” to save an extra 15% off. Register now and get the early bird rate, but hurry prices go up Jan 8th. https://www.percona.com/live/17/register Sponsorship opportunities available as well: https://www.percona.com/live/17/be-a-sponsor

15.DATABASE PERFORMANCE MATTERS