MySQLでJSON型を使う（論理設計編）

2017年6月8日

この記事は最終更新から7年以上経過しています。内容が古くなっている可能性があります。

MySQLではバージョン 5.7.8 以降でJSON型がサポートされるようになりました。
このシリーズでは基本編、パフォーマンス編、論理設計編と、JSON型のデータ操作方法やどのような場合に使用を検討するべきかをベンチマーク結果も踏まえて探っていきたいと思います。

今回はJSON型をどういった場合に利用するかを考える論理設計編です。

データベース論理設計のアンチパターン

データベースの論理設計で避けるべき事柄をまとめた以下の本があります。

[amazonjs asin=”4873115892″ locale=”JP” title=”SQLアンチパターン”]

この中でいくつかのアンチパターンはJSON型を使うことでも解決できそうです。

マルチカラムアトリビュート

必要な属性の数だけカラムを定義するアンチパターンです。

CREATE TABLE `Posts` (
  `post_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `tag1` varchar(20) NOT NULL,
  `tag2` varchar(20) NOT NULL,
  `tag3` varchar(20) NOT NULL,
  PRIMARY KEY (`post_id`)
);

CREATE TABLE `Posts` (

`post_id` int(11) NOT NULL,

`title` varchar(255) NOT NULL,

`tag1` varchar(20) NOT NULL,

`tag2` varchar(20) NOT NULL,

`tag3` varchar(20) NOT NULL,

PRIMARY KEY (`post_id`)

);

本の中では下記の図のようにtag属性を格納する従属テーブルを作成する方法が解決策として提示されています。

アンチパターン本での解決方法

テーブル定義

CREATE TABLE `Posts` (
  `post_id` int(11) NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`post_id`)
);
-- tag属性を格納する従属テーブル
CREATE TABLE `Tags` (
  `post_id` int(11) NOT NULL,
  `tag` varchar(20) NOT NULL,
  PRIMARY KEY (`post_id`, `tag`),
  FOREIGN KEY (`post_id`) REFERENCES Posts(`post_id`)
);

CREATE TABLE `Posts` (

`post_id` int(11) NOT NULL,

`title` varchar(255) DEFAULT NULL,

PRIMARY KEY (`post_id`)

);

-- tag属性を格納する従属テーブル

CREATE TABLE `Tags` (

`post_id` int(11) NOT NULL,

`tag` varchar(20) NOT NULL,

PRIMARY KEY (`post_id`, `tag`),

FOREIGN KEY (`post_id`) REFERENCES Posts(`post_id`)

);

JSON型を使った場合の解決方法

従属テーブルは不要なためシンプルです。

テーブル定義

CREATE TABLE `Posts` (
  `post_id` int(11) NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `tags` json NOT NULL,
  PRIMARY KEY (`post_id`)
);

CREATE TABLE `Posts` (

`post_id` int(11) NOT NULL,

`title` varchar(255) DEFAULT NULL,

`tags` json NOT NULL,

PRIMARY KEY (`post_id`)

);

INSERT

INSERT INTO Posts (post_id, title, tags) 
VALUES (1, 'SQL anti-pattern', JSON_ARRAY('DB', 'SQL', 'anti-pattern', 'MySQL'));

1 2	INSERT INTO Posts (post_id, title, tags) VALUES (1, 'SQL anti-pattern', JSON_ARRAY('DB', 'SQL', 'anti-pattern', 'MySQL'));

SELECT

SELECT post_id, title 
  FROM Posts 
 WHERE JSON_SEARCH(tags, 'one', 'SQL') IS NOT NULL;

SELECT post_id, title

FROM Posts

WHERE JSON_SEARCH(tags, 'one', 'SQL') IS NOT NULL;

パフォーマンスはそれほどよくありませんが、tagsカラムを検索条件に指定できます。

エンティティ・アトリビュート・バリュー（EAV）

動的な属性を定義するために、属性テーブルを定義して各行に属性を格納する方法です。

テーブル定義

CREATE TABLE `Products` (
  `product_id` int(11) NOT NULL,
  PRIMARY KEY (`product_id`)
);

-- 動的な属性を定義するための属性テーブル
CREATE TABLE `ProductsAttr` (
  `product_id` int(11) NOT NULL,
  `attr_name` varchar(255) NOT NULL,
  `attr_value` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`product_id`,`attr_name`),
  FOREIGN KEY (`product_id`) REFERENCES `Products` (`product_id`)
);

CREATE TABLE `Products` (

`product_id` int(11) NOT NULL,

PRIMARY KEY (`product_id`)

);

-- 動的な属性を定義するための属性テーブル

CREATE TABLE `ProductsAttr` (

`product_id` int(11) NOT NULL,

`attr_name` varchar(255) NOT NULL,

`attr_value` varchar(255) DEFAULT NULL,

PRIMARY KEY (`product_id`,`attr_name`),

FOREIGN KEY (`product_id`) REFERENCES `Products` (`product_id`)

);

INSERT

INSERT INTO Products (`product_id`) VALUES (100);

INSERT INTO ProductsAttr VALUES (100, 'name', 'MySQL');
INSERT INTO ProductsAttr VALUES (100, 'version', '5.7');
INSERT INTO ProductsAttr VALUES (100, 'os', 'Linux');

INSERT INTO Products (`product_id`) VALUES (100);

INSERT INTO ProductsAttr VALUES (100, 'name', 'MySQL');

INSERT INTO ProductsAttr VALUES (100, 'version', '5.7');

INSERT INTO ProductsAttr VALUES (100, 'os', 'Linux');

SELECT

SELECT p.product_id
  FROM Products AS p
  JOIN ProductsAttr AS a
    USING (product_id)
 WHERE a.attr_name = 'name'
   AND a.attr_value = 'MySQL';

SELECT p.product_id

FROM Products AS p

JOIN ProductsAttr AS a

USING (product_id)

WHERE a.attr_name = 'name'

AND a.attr_value = 'MySQL';

MySQLのようなリレーショナルデータベースではSQLも複雑になりがちですが、JSON型を使った場合はシンプルになります。

JSON型を使った場合の解決方法

テーブル定義

CREATE TABLE `Products` (
  `product_id` int(11) NOT NULL,
  `attr` json,
  PRIMARY KEY (`product_id`)
);

CREATE TABLE `Products` (

`product_id` int(11) NOT NULL,

`attr` json,

PRIMARY KEY (`product_id`)

);

INSERT

INSERT INTO Products (`product_id`, `attr`)
VALUES (100, JSON_OBJECT('name', 'MySQL', 'version', '5.7', 'os', 'Linux'));

1 2	INSERT INTO Products (`product_id`, `attr`) VALUES (100, JSON_OBJECT('name', 'MySQL', 'version', '5.7', 'os', 'Linux'));

SELECT

SELECT product_id
  FROM Products
 WHERE JSON_EXTRACT(attr, "$.name") = 'MySQL';

SELECT product_id

FROM Products

WHERE JSON_EXTRACT(attr, "$.name") = 'MySQL';

パフォーマンス

JSON型はインデックスをサポートしていないため、JSONのフィールドを検索対象にする場合、レコード数が多くなるとパフォーマンスが気になります。その場合は Virtual Column を作成してそちらにインデックスを作成することで高速化を図ることができます。

参考　：　MySQL :: MySQL 5.7 Reference Manual :: 13.1.18.9 Secondary Indexes and Generated Columns

ただ、検索対象が増加する度にDDLによるインデックスの追加が必要になるため、インデックス作成はオンラインで行えますが、レコード数が多い場合はそれほど気軽には行えない作業となるかもしれませんので、検索項目が不定でレコード数が多くなる場合は注意が必要です。

（おまけ）WebフレームワークのJSON型への対応状況

使い方次第ではシンプルなテーブル構成で複雑で動的な定義が可能なJSON型ですが、Webフレームワークを使っている場合、マイグレーションに一工夫必要だったり、O/Rマッパーの便利な機能を迂回してSQLを書かなければならないなど、せっかくテーブル構成がシンプルでも、プログラムコード側が複雑になってしまっては意味がありません。
ここでは以下のWebフレームワークとO/RマッパーでJSON型の対応状況を調べてみました。

Webフレームワーク、O/Rマッパー

JSON型への対応状況

DBマイグレーション機能でJSON型が指定できるか？
O/R マッパーでJSON型をどのように扱っているか？

Rails 5

ドキュメントにはPostgreSQLのみJSON型に対応しているように見えます。
参考： Active Record and PostgreSQL — Ruby on Rails Guides

実際は以下のIssueを見るとMySQLのJSON型にも対応しているようです。
参考： Add a native JSON data type support in MySQL by kamipo · Pull Request #21110 · rails/rails · GitHub

Rails 5.0.2 以降は MySQL の JSON型にDBマイグレーションもO/Rマッパー(Active Record)も対応済みです。

Laravel 5.4

Database: Migrations – Laravel – The PHP Framework For Web Artisans

DBマイグレーションでJSON型を指定できます。

Database: Query Builder – Laravel – The PHP Framework For Web Artisans

JSON型に対応しているようです。

CakePHP 3

Database Basics – CakePHP 3.4 Red Velvet Cookbook

Maps to a JSON type if it’s available, otherwise it maps to TEXT. The ‘json’ type was added in 3.3.0

バージョン 3.3.0 以降はJSON型に対応しているようです。

O/RマッパーがJSON型に対応しているかはドキュメントを探した限りだと見つかりませんでした。

Hibernate ORM 5.2

ドキュメントを探した限りではJSON型には対応していないようでした。

まとめ

JSON型を使うことで従来のリレーショナルモデルで不向きなデータ構造もシンプルに解決できます。
トランザクションによるデータの一貫性 + NoSQLの柔軟性のいいとこ取りです。
一部だけJSONを使うために新たにNoSQLデータベースを構築する必要もなく、MySQLのノウハウがそのまま使えます。

MySQLのJSON型について基本編、パフォーマンス編、論理設計編と3回に分けて検証してきました。JSON型は従来のMySQLでは不向きだったデータ構成もシンプルなテーブル構成で解決できる可能性があります。みなさんも使えそうなシチュエーションがあれば検討してみてください。

関連サービス
>>MySQL保守サポート

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

URLをコピーしました！

MySQLでJSON型を使う（論理設計編）

データベース論理設計のアンチパターン

マルチカラムアトリビュート

アンチパターン本での解決方法

テーブル定義

JSON型を使った場合の解決方法

テーブル定義

INSERT

SELECT

エンティティ・アトリビュート・バリュー（EAV）

テーブル定義

INSERT

SELECT

JSON型を使った場合の解決方法

テーブル定義

INSERT

SELECT

パフォーマンス

（おまけ）WebフレームワークのJSON型への対応状況

Webフレームワーク、O/Rマッパー

JSON型への対応状況

Rails 5

Laravel 5.4

CakePHP 3

Hibernate ORM 5.2

まとめ

スマートスタイルTECHブログについて

関連記事