Share
Embed
JSON_VALUE() で関数インデックスを作ってみたら
MySQL Release note でわいわい言う勉強会 8.0.21 2020/08/12
Transcript
18 slides
-
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 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 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 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 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 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 mysql> EXPLAIN SELECT * FROM t1 WHERE json_value(`j`, _utf8mb4'$.id'
returning unsigned) = 150\G -
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) -
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}]}); -
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; -
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