[解決済み] MYSQL 5.7におけるJSONのネイティブサポート：MYSQLにおけるJSONデータ型の長所と短所は何ですか？

質問

MySQL 5.7 では、新しいデータ型で JSON データを MySQL に格納するための新しいデータ型 テーブルに保存するための新しいデータ型が が追加されました。これは明らかにMySQLの大きな変化となるでしょう。彼らはいくつかの利点を挙げている

ドキュメントバリデーション - 有効な JSON ドキュメントのみを JSONカラムに格納されるため、データの自動検証を行うことができます。

効率的なアクセス - さらに重要なことは、JSONドキュメントをJSONカラムに格納するとき、それはプレーンテキストの値として格納されないということです。代わりに、それは 最適化されたバイナリ形式で保存され、オブジェクトのメンバーや配列の要素に素早くアクセスすることができます。 メンバーや配列要素に素早くアクセスできるように最適化されたバイナリ形式で保存されます。

パフォーマンス - クエリのパフォーマンスを向上させる JSONカラム内の値に対してインデックスを作成することで、クエリのパフォーマンスを向上させることができます。 これは、仮想カラムの「ファンクショナル・インデックス」で実現できます。

利便性 - JSONカラムのインライン構文が追加されたことで、SQLにドキュメントクエリを統合するのが非常に自然になりました。 SQL の中にドキュメントクエリを統合するのが非常に自然です。例えば のようになります (features.feature は JSON カラムです)。 SELECT feature->"$.properties.STREET" AS property_street FROM features WHERE id = 121254;

すごい！素晴らしい機能が盛り込まれていますね。今、データを操作するのがより簡単になりました。今、それはカラムに、より複雑なデータを格納することが可能です。 MySQLは今、NoSQLで味付けされているのです。

さて、JSONデータに対するクエリを想像してみると、次のようになります。

SELECT * FROM t1
WHERE JSON_EXTRACT(data,"$.series") IN 
( 
SELECT JSON_EXTRACT(data,"$.inverted") 
FROM t1 | {"series": 3, "inverted": 8} 
WHERE JSON_EXTRACT(data,"$.inverted")<4 );

では、少数のjsonカラムに巨大な小リレーションを格納することができますか？それは良いことですか？それは正規化を破るのでしょうか。 もしこれが可能なら、私はそれがMySQLのカラムでNoSQLのように動作するのだと思います。 . 私は本当にこの機能についての詳細を知りたいです。MySQL JSON データタイプの長所と短所。

どのように解決するのですか？

SELECT * FROM t1
WHERE JSON_EXTRACT(data,"$.series") IN ...

このように式や関数の内部で列を使用すると、クエリを最適化するためにインデックスを使用する機会が失われます。上に示したクエリはテーブルスキャンを行うことを余儀なくされます。

効率的なアクセスについての主張は誤解を招きます。これは、クエリが JSON ドキュメントを含む行を調べた後、JSON 構文のテキストを解析することなくフィールドを抽出できることを意味します。しかし、行を検索するためには、まだテーブルスキャンが必要です。言い換えれば、クエリはすべての行を調べる必要があります。

例えて言えば、電話帳からファーストネームが "Bill" の人を検索する場合、ファーストネームを見つけるのが少し早くなるようにハイライトされていたとしても、電話帳のすべてのページを読まなければなりません。

MySQL 5.7 では、テーブル内に仮想カラムを定義し、その仮想カラムにインデックスを作成することができます。

ALTER TABLE t1
  ADD COLUMN series AS (JSON_EXTRACT(data, '$.series')),
  ADD INDEX (series);

そして、仮想カラムに問い合わせると、インデックスを使用することができ、テーブルスキャンを回避することができます。

SELECT * FROM t1
WHERE series IN ...

これは素晴らしいことですが、JSONを使用することの重要性を見逃しているようなものです。JSONを使用する魅力的な部分は、ALTER TABLEを実行せずに新しい属性を追加できることです。しかし、インデックスの助けを借りてJSONフィールドを検索したい場合は、いずれにせよ余分な（仮想）カラムを定義しなければならないことがわかります。

しかし、仮想カラムやインデックスを定義しなくても ごとに フィールドのために仮想カラムとインデックスを定義する必要はありません。JSONには、以下のようなselect-listで抽出する必要があるだけの他の属性があるかもしれません。

SELECT JSON_EXTRACT(data, '$.series') AS series FROM t1
WHERE <other conditions>

私は一般的に、MySQL で JSON を使用するにはこれが最適な方法であると言うでしょう。select-listの中だけです。

他の句（JOIN、WHERE、GROUP BY、HAVING、ORDER BY）でカラムを参照する場合は、JSONドキュメント内のフィールドではなく、従来のカラムを使用する方が効率的である。

という講演を行いました。 MySQL で JSON を間違って使用する方法 を2018年4月に開催されたPercona Liveカンファレンスで発表しました。秋に開催されるOracle Code Oneでは、この講演をアップデートして繰り返し発表する予定です。

JSONには他にも問題があります。たとえば、私のテストでは、同じデータを格納する従来のカラムと比較して、JSONドキュメントには2～3倍のストレージ容量が必要でした。

MySQL は新しい JSON 機能を積極的に宣伝していますが、これは主に MongoDB への移行を思いとどまらせるためです。しかし、MongoDB のような文書指向のデータ ストレージは、基本的にデータを整理する非リレーショナルな方法です。リレーショナルとは違うのです。どちらかが優れているというわけではなく、異なるタイプのクエリに適した異なる手法であるというだけです。

JSONがクエリをより効率的にする場合、JSONを使用することを選択すべきです。

新しいからとか、流行のためという理由だけで技術を選んではいけない。

編集：MySQLの仮想カラムの実装は、WHERE句が仮想カラムの定義とまったく同じ式を使用している場合、インデックスを使用することになっています。つまり、次のようになります。 は は仮想カラムのインデックスを使用します。 AS (JSON_EXTRACT(data,"$.series"))

SELECT * FROM t1
WHERE JSON_EXTRACT(data,"$.series") IN ...

ただし、この機能を試してみて、式がJSON抽出関数である場合には、何らかの理由で機能しないことがわかりました。他のタイプの式では動作しますが、JSON関数では動作しません。UPDATE: これは、MySQL 5.7.33 で最終的に動作することが報告されています。