JSON_VALUE() で関数インデックスを作ってみたら

MySQL Release note でわいわい言う勉強会 8.0.21　2020/08/12

Transcript

2. ◦ ◦ https://github.com/hmatsu47/mysql80_no_usui_hon ◦ https://hmatsu47.booth.pm/ 2
3. ◦ https://qiita.com/hmatsu47/items/aa687aa30c4570bac861 ◦ https://qiita.com/hmatsu47/items/013da2971c8934d867e7 3
4. ◦ https://qiita.com/hmatsu47/items/aa687aa30c4570bac861 ◦ https://qiita.com/hmatsu47/items/013da2971c8934d867e7 4
5. https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-21.html#mysqld-8-0-21-json 5
8. 8 mysql> CREATE DATABASE jsontest; Query OK, 1 row affected (0.01 sec) mysql> USE jsontest; Database changed mysql> CREATE TABLE t1( -> j JSON, -> INDEX i1 ( (JSON_VALUE(j, '$.id' RETURNING UNSIGNED)) ) -> ); 1 row in set (0.00 sec)
9. 9 INSERT INTO t1 SET j = '{"id": 100, "val": [1, 2, 3]}'; INSERT INTO t1 SET j = '{"id": 101, "val": [4, 5, 6, 7]}'; INSERT INTO t1 SET j = '{"id": 110, "val": [8, 9, 0]}'; INSERT INTO t1 SET j = '{"id": 120, "val": [1, 2]}'; INSERT INTO t1 SET j = '{"id": 122, "val": 3}'; INSERT INTO t1 SET j = '{"id": 130, "val": [4, 5]}'; INSERT INTO t1 SET j = '{"id": 140, "val": [6, 7, 8]}'; INSERT INTO t1 SET j = '{"id": 150, "val": [9, 0, 1, 2]}'; INSERT INTO t1 SET j = '{"id": 200, "val": [3, 4, 5]}'; INSERT INTO t1 SET j = '{"id": 220, "val": [6, 7]}';
10. 10 mysql> EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$.id' RETURNING UNSIGNED) = 150\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 10 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec)
11. 11 mysql> EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$.id' RETURNING UNSIGNED) = 150\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 10 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec)
12. 12 mysql> SHOW CREATE TABLE t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `j` json DEFAULT NULL, KEY `i1` ((json_value(`j`, _utf8mb4'$.id' returning unsigned))) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)
13. 13 mysql> SHOW CREATE TABLE t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `j` json DEFAULT NULL, KEY `i1` ((json_value(`j`, _utf8mb4'$.id' returning unsigned))) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)
14. 14 mysql> EXPLAIN SELECT * FROM t1 WHERE json_value(`j`, _utf8mb4'$.id' returning unsigned) = 150\G
15. 15 mysql> EXPLAIN SELECT * FROM t1 WHERE json_value(`j`, _utf8mb4'$.id' returning unsigned) = 150\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ref possible_keys: i1 key: i1 key_len: 9 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)
16. https://qiita.com/hmatsu47/items/bd1634b93bf9c1fbce1e 16 // Create Collection const session = await mysqlx.getSession(connectParam); const collection = await session.getSchema(schemaName).createCollection(collectionName); const flag = await collection.createIndex('labels', {fields: [{"field": "$.labels", "type":"CHAR(100)", "array": true}]});
17. https://qiita.com/hmatsu47/items/bd1634b93bf9c1fbce1e 17 // Create Collection const session = await mysqlx.getSession(connectParam); const collection = await session.getSchema(schemaName).createCollection(collectionName); const flag = await collection.createIndex('labels', {fields: [{"field": "$.labels", "type":"CHAR(100)", "array": true}]}); CREATE TABLE `image_labeling` ( `doc` json DEFAULT NULL, `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL, PRIMARY KEY (`_id`), KEY `labels` ((cast(json_extract(`doc`,_utf8mb4'$.labels') as char(100) array))) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
18. https://qiita.com/hmatsu47/items/bd1634b93bf9c1fbce1e 18 // Create Collection const session = await mysqlx.getSession(connectParam); const collection = await session.getSchema(schemaName).createCollection(collectionName); const flag = await collection.createIndex('labels', {fields: [{"field": "$.labels", "type":"CHAR(100)", "array": true}]}); CREATE TABLE `image_labeling` ( `doc` json DEFAULT NULL, `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL, PRIMARY KEY (`_id`), KEY `labels` ((cast(json_extract(`doc`,_utf8mb4'$.labels') as char(100) array))) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; https://github.com/mysql/mysql-server/tree/8.0/plugin/x/src