これはマイネットエンターテイメント Advent Calendar三日目の記事です。

今回はmysql5.7で追加されたjson型の基礎的な利用法、メリットとmysql5.7以降に搭載された新機能 Generated Column機能でjson型のデータにインデックスを張る方法について紹介をします

環境

macOS 10.11
mysql 5.7.16

参考資料

公式ドキュメント

基礎的な使い方

下記のように型として指定するだけでjson型として認識されます。
また、json内部のデータを取り扱うときにはjson用の関数があるのでそれを利用します。（公式ドキュメントを参考）

# text型のテーブル
CREATE TABLE `data_txt` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1

# json型のテーブル
CREATE TABLE `data_json` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` json NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1


# json内の特定のデータを取得
mysql> SELECT json_extract(data,"$.name") FROM data_txt;
+-----------------------------+
| json_extract(data,"$.name") |
+-----------------------------+
| "kenken"                    |
+-----------------------------+
1 row in set (0.00 sec)

mysql> SELECT json_extract(data,"$.name") FROM data_json;
+-----------------------------+
| json_extract(data,"$.name") |
+-----------------------------+
| "kenken"                    |
+-----------------------------+
1 row in set (0.00 sec)

# jsonデータのアップデート 
text型だとカラム内のデータを全て入れ替える必要がありますが、jsonだと一部のみ変更可能です。


select id,body,json_extract(body,"$.price") as extract from T_JSON_DOC where id = 3;
+----+------------------------------------------------------------------------+---------+
| id | body                                                                   | extract |
+----+------------------------------------------------------------------------+---------+
|  3 | {"id": 3, "name": "お茶", "price": 50000, "Conditions": ["NEW", 2015]}| 50000   |
+----+------------------------------------------------------------------------+---------+

update T_JSON_DOC set T_JSON_DOC.body = JSON_REPLACE(body,"$.price",15000) 
where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

select id,body,json_extract(body,"$.price") as extract from T_JSON_DOC where id = 3;
+----+----------------------------------------------------------------------- +---------+
| id | body                                                                   | extract |
+----+------------------------------------------------------------------------+---------+
|  3 | {"id": 3, "name": "お茶", "price": 15000, "Conditions": ["NEW", 2015]}| 15000   |
+----+------------------------------------------------------------------------+---------+

型のバリデートについて

バリデーションがされているらしくjson型のテーブルに、json以外の形式のデータを挿入しようとすると下記のようなエラーが出る。

mysql> INSERT INTO data_txt(data) VALUES ('{"id": 2, "name": "kenken"');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO data_json(data) VALUES ('{"id": 2, "name": "kenken"');
ERROR 3140 (22032): Invalid JSON text: "Missing a comma or '}' after an object member." at position 23 in value for column 'data_json.data'.

jsonデータ型とtext型のパフォーマンス比較

text型でもjson_extract関数で、json型のデータの一部を取得するのは出来たが
処理速度はjson型のテーブルのほうが圧倒的に早いらしいのでテストをする。

http://www.json-generator.com/
こちらのサイトで生成した20万件のjsonデータをインサートして参照レスポンス比較。
それぞれの参照クエリにて同じコマンドと同じデータでレスポンス比較

SELECT distinct json_extract(feature,’$.type’) as feature FROM test.features_txt;
SELECT distinct json_extract(feature,’$.type’) as feature FROM test.features_json;

結果として、json型が圧倒的に早い事が確認出来る。

SELECT distinct json_extract(feature,'$.type') as feature FROM 各テーブル

# text型
+-----------+
| feature   |
+-----------+
| "Feature" |
+-----------+

real    0m4.312s
user    0m0.005s
sys     0m0.002s

# json型
+-----------+
| feature   |
+-----------+
| "Feature" |
+-----------+

real    0m0.702s
user    0m0.004s
sys     0m0.004s

jsonとGenerated Columnの連携によるインデックス利用とパフォーマンス

jsonドキュメントのオブジェクトの中からstreet名の部分からデータを抽出して列を作成しindexを付与。

列:json_extract(feature,’$.properties.STREET’))
インデックス： KEY `feature_street` (`feature_street`)

JSONデータ型のサンプルを入れたテーブル
*************************** 1. row ***************************
            id: 12250
       feature: {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.39836263491878, 37.79189388899312, 0], [-122.39845248797837, 37.79233030084018, 0], [-122.39768507706792, 37.7924280850133, 0], [-122.39836263491878, 37.79189388899312, 0]]]}, "properties": {"TO_ST": "388", "BLKLOT": "0265003", "STREET": "MARKET", "FROM_ST": "388", "LOT_NUM": "003", "ST_TYPE": "ST", "ODD_EVEN": "E", "BLOCK_NUM": "0265", "MAPBLKLOT": "0265003"}}
  feature_type: "Feature"
  feature_street: "MARKET"

jsonデータ型とGenerated Columnを利用したテーブル
*************************** 1. row ***************************
       Table: features
Create Table: CREATE TABLE `features` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `feature` json NOT NULL,
  `feature_type` varchar(30) GENERATED ALWAYS AS (json_extract(feature,'$.type')) VIRTUAL,
  `feature_street` varchar(30) GENERATED ALWAYS AS (json_extract(feature,'$.properties.STREET')) VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `feature_type` (`feature_type`),
  KEY `feature_street` (`feature_street`)
) ENGINE=InnoDB AUTO_INCREMENT=206561 

jsonドキュメントに対して、index検索が利用出来るか確認 -> where json_extract(feature,'$.properties.STREET') = '"MARKET"'
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: features
   partitions: NULL
         type: ref
possible_keys: feature_street
          key: feature_street
      key_len: 123
          ref: const
         rows: 808
     filtered: 100.00
        Extra: NULL

終わりに

mysql5.7で導入されたjson型、Generated Column機能はすでにDB内に大量のjsonカラムが存在しているDBにおいて特にパフォーマンス面でメリットの多い新機能だと思われました。
本稿がなにかの参考になれば幸いです。

以上です

次回はHItokiMabuchiさんです。

json型でもindexが貼りたい！

目次

環境