MySQLではバージョン 5.7.8 以降でJSON型がサポートされるようになりました。
このシリーズでは基本編、パフォーマンス編、論理設計編と、JSON型のデータ操作方法やどのような場合に使用を検討するべきかをベンチマーク結果も踏まえて探っていきたいと思います。
- MySQLでJSON型を使う(基本編)
- MySQLでJSON型を使う(パフォーマンス編)
- MySQLでJSON型を使う(論理設計編)
今回はJSON型をどういった場合に利用するかを考える論理設計編です。
データベース論理設計のアンチパターン
データベースの論理設計で避けるべき事柄をまとめた以下の本があります。
[amazonjs asin=”4873115892″ locale=”JP” title=”SQLアンチパターン”]
この中でいくつかのアンチパターンはJSON型を使うことでも解決できそうです。
マルチカラムアトリビュート
必要な属性の数だけカラムを定義するアンチパターンです。
本の中では下記の図のようにtag属性を格納する従属テーブルを作成する方法が解決策として提示されています。
アンチパターン本での解決方法
テーブル定義
JSON型を使った場合の解決方法
従属テーブルは不要なためシンプルです。
テーブル定義
INSERT
SELECT
パフォーマンスはそれほどよくありませんが、tagsカラムを検索条件に指定できます。
エンティティ・アトリビュート・バリュー(EAV)
動的な属性を定義するために、属性テーブルを定義して各行に属性を格納する方法です。
テーブル定義
INSERT
SELECT
MySQLのようなリレーショナルデータベースではSQLも複雑になりがちですが、JSON型を使った場合はシンプルになります。
JSON型を使った場合の解決方法
テーブル定義
INSERT
SELECT
パフォーマンス
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でJSON型を使う(基本編)
- MySQLでJSON型を使う(パフォーマンス編)
- MySQLでJSON型を使う(論理設計編)
関連サービス
>>MySQL保守サポート