MENU
アイデアを形にする( プログラミング / クラウド / フレームワーク / ライブラリ / デザイン / IT豆知識 )
  1. ホーム
  2. MySQL
  3. JSON型でインデックスを活用する方法と性能比較

JSON型でインデックスを活用する方法と性能比較

MySQL

MySQLのJSON型自体にはインデックスを貼れませんが、「Generated Column(生成カラム)」を利用してJSON型の特定キーにインデックスを貼ることができます。ここでは、実際にインデックスを生成して、性能比較を行います。

目次

テーブル生成

TEXT型

mysql> CREATE TABLE `text_users` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `col` text,
    ->   PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> SHOW COLUMNS FROM `text_users`;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
| col   | text    | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

JSON型(インデックスなし)

mysql> CREATE TABLE `json_users` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `col` JSON,
    ->   PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW COLUMNS FROM `json_users`;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
| col   | json    | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

JSON型(インデックスあり)

Generated Column(生成カラム) を利用してJSON型の特定のキーにインデックスを貼ります。

ここでは、後述するレコード挿入のときにnameというキーを含むJSONデータを格納する予定なので\`col\`->"$.name"とします。

JSON_UNQUOTE関数 で ダブルクォーテーション(") が取り除かれた形で生成している点も気をつけるポイントです。

mysql> CREATE TABLE `json_index_users` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `col` JSON,
    ->   `col_name` varchar(30) GENERATED ALWAYS AS (JSON_UNQUOTE(`col`->"$.name")) VIRTUAL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `col_name` (`col_name`)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> SHOW COLUMNS FROM `json_index_users`;
+----------+-------------+------+-----+---------+-------------------+
| Field    | Type        | Null | Key | Default | Extra             |
+----------+-------------+------+-----+---------+-------------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment    |
| col      | json        | YES  |     | NULL    |                   |
| col_name | varchar(30) | YES  | MUL | NULL    | VIRTUAL GENERATED |
+----------+-------------+------+-----+---------+-------------------+
3 rows in set (0.00 sec)
Generated Columnとは？

MySQL5.7.6で追加された機能で、計算結果をカラムにすることができます。
https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html

テスト用に100万レコード挿入

TEXT型

まず、下記クエリを実行して、10レコード格納します。

INSERT INTO `text_users`
(`col`)
VALUES
('{"name": "tanaka",    "options": {"x": 100, "y": 200}}'),
('{"name": "yamada",    "options": {"x": 300}}'),
('{"name": "suzuki",    "options": {"x": 100, "y": 200, "z": [1, 3, 4]}}'),
('{"name": "yamashita", "options": {"x": 120, "y": 200}}'),
('{"name": "ueda",      "options": {"x": 180}}'),
('{"name": "satou",     "options": {"z": [2, 3]}}'),
('{"name": "takahashi", "options": {"x": 300, "y": 80}}'),
('{"name": "itou",      "options": {"x": 100, "y": 300}}'),
('{"name": "kobayashi", "options": {"x": 230}}'),
('{"name": "kimura",    "options": {"y": 200, "z": [3, 8, 9]}}');

単純結合を6回することで100万レコードになります。
(10 × 10 × 10 × 10 × 10 × 10 = 1,000,000)

mysql> SELECT count(*) FROM text_users u1, text_users u2, text_users u3, text_users u4, text_users u5, text_users u6;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.23 sec)

単純結合を利用して100万レコード追加します。

mysql> INSERT INTO `text_users`
    -> (`col`)
    -> SELECT `u1`.`col` FROM text_users u1, text_users u2, text_users u3, text_users u4, text_users u5, text_users u6;
Query OK, 1000000 rows affected (13.06 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT COUNT(`id`) FROM `text_users`;
+-------------+
| COUNT(`id`) |
+-------------+
|     1000010 |
+-------------+
1 row in set (0.42 sec)

JSON型(インデックスなし)

まず、下記クエリを実行して、10レコード格納します。

INSERT INTO `json_users`
(`col`)
VALUES
('{"name": "tanaka",    "options": {"x": 100, "y": 200}}'),
('{"name": "yamada",    "options": {"x": 300}}'),
('{"name": "suzuki",    "options": {"x": 100, "y": 200, "z": [1, 3, 4]}}'),
('{"name": "yamashita", "options": {"x": 120, "y": 200}}'),
('{"name": "ueda",      "options": {"x": 180}}'),
('{"name": "satou",     "options": {"z": [2, 3]}}'),
('{"name": "takahashi", "options": {"x": 300, "y": 80}}'),
('{"name": "itou",      "options": {"x": 100, "y": 300}}'),
('{"name": "kobayashi", "options": {"x": 230}}'),
('{"name": "kimura",    "options": {"y": 200, "z": [3, 8, 9]}}');

単純結合を利用して100万レコード追加します。

mysql> INSERT INTO `json_users`
    -> (`col`)
    -> SELECT `u1`.`col` FROM json_users u1, json_users u2, json_users u3, json_users u4, json_users u5, json_users u6;
Query OK, 1000000 rows affected (10.89 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> 
mysql> SELECT COUNT(`id`) FROM `json_users`;
+-------------+
| COUNT(`id`) |
+-------------+
|     1000010 |
+-------------+
1 row in set (0.37 sec)

JSON型(インデックスあり)

まず、下記クエリを実行して、10レコード格納します。

INSERT INTO `json_index_users`
(`col`)
VALUES
('{"name": "tanaka",    "options": {"x": 100, "y": 200}}'),
('{"name": "yamada",    "options": {"x": 300}}'),
('{"name": "suzuki",    "options": {"x": 100, "y": 200, "z": [1, 3, 4]}}'),
('{"name": "yamashita", "options": {"x": 120, "y": 200}}'),
('{"name": "ueda",      "options": {"x": 180}}'),
('{"name": "satou",     "options": {"z": [2, 3]}}'),
('{"name": "takahashi", "options": {"x": 300, "y": 80}}'),
('{"name": "itou",      "options": {"x": 100, "y": 300}}'),
('{"name": "kobayashi", "options": {"x": 230}}'),
('{"name": "kimura",    "options": {"y": 200, "z": [3, 8, 9]}}');

単純結合を利用して100万レコード追加します。

mysql> INSERT INTO `json_index_users`
    -> (`col`)
    -> SELECT `u1`.`col` FROM json_index_users u1, json_index_users u2, json_index_users u3, json_index_users u4, json_index_users u5, json_index_users u6;
Query OK, 1000000 rows affected (19.49 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> 
mysql> SELECT COUNT(`id`) FROM `json_index_users`;
+-------------+
| COUNT(`id`) |
+-------------+
|     1000010 |
+-------------+
1 row in set (0.34 sec)

Generated Column(生成カラム) の col_name には以下のようなデータが格納されています。

mysql> SELECT * FROM `json_index_users` LIMIT 5;
+----+---------------------------------------------------------------------+-----------+
| id | col                                                                 | col_name  |
+----+---------------------------------------------------------------------+-----------+
|  1 | {"name": "tanaka", "options": {"x": 100, "y": 200}}                 | tanaka    |
|  2 | {"name": "yamada", "options": {"x": 300}}                           | yamada    |
|  3 | {"name": "suzuki", "options": {"x": 100, "y": 200, "z": [1, 3, 4]}} | suzuki    |
|  4 | {"name": "yamashita", "options": {"x": 120, "y": 200}}              | yamashita |
|  5 | {"name": "ueda", "options": {"x": 180}}                             | ueda      |
+----+---------------------------------------------------------------------+-----------+
5 rows in set (0.00 sec)

挿入時間比較

インデックス生成の必要があるので、JSON型(インデックスあり) の処理時間が最も長くなっています。

型処理時間
TEXT型13.06 sec
JSON型(インデックスなし)10.89 sec
JSON型(インデックスあり)19.49 sec

WHERE句で条件指定したときの処理時間

TEXT型

EXPLAINで確認します。

mysql> EXPLAIN SELECT COUNT(`id`) FROM `text_users` WHERE `col`->"$.name" = "yamada";
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | text_users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 994569 |   100.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

処理時間です。

mysql> SELECT COUNT(`id`) FROM `text_users` WHERE `col`->"$.name" = "yamada";
+-------------+
| COUNT(`id`) |
+-------------+
|      100001 |
+-------------+
1 row in set (12.57 sec)

JSON型(インデックスなし)

EXPLAINで確認します。

mysql> EXPLAIN SELECT COUNT(`id`) FROM `json_users` WHERE `col`->"$.name" = "yamada";
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | json_users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 994812 |   100.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

処理時間です。

mysql> SELECT COUNT(`id`) FROM `json_users` WHERE `col`->"$.name" = "yamada";
+-------------+
| COUNT(`id`) |
+-------------+
|      100001 |
+-------------+
1 row in set (2.45 sec)

JSON型(インデックスあり)

EXPLAINで確認します。

mysql> EXPLAIN SELECT COUNT(`id`) FROM `json_index_users` WHERE `col_name` = "yamada";
+----+-------------+------------------+------------+------+---------------+----------+---------+-------+--------+----------+-------------+
| id | select_type | table            | partitions | type | possible_keys | key      | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+------------------+------------+------+---------------+----------+---------+-------+--------+----------+-------------+
|  1 | SIMPLE      | json_index_users | NULL       | ref  | col_name      | col_name | 33      | const | 177702 |   100.00 | Using index |
+----+-------------+------------------+------------+------+---------------+----------+---------+-------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

処理時間です。

mysql> SELECT COUNT(`id`) FROM `json_index_users` WHERE `col_name` = "yamada";
+-------------+
| COUNT(`id`) |
+-------------+
|      100001 |
+-------------+
1 row in set (0.05 sec)

処理時間比較

単純にJSON型にしただけでも処理時間が短くなりますが、インデックスを貼ることでさらに速くなります。

型処理時間
TEXT型12.57 sec
JSON型(インデックスなし)2.45 sec
JSON型(インデックスあり)0.05 sec

参考

MySQL
よかったらシェアしてね！

関連記事

目次