MySQL8系にて強化されたJSONを活用しながら正規化と比較していく

こんにちは！
新卒3年目エンジニアのtakadaです。
MySQL8系よりJSON型に関連する新しい機能がいくつか追加されたので、
デモを通して説明しつつ、正規化表現と比較しながらどの場面で使えそうかを模索していきます。

1. はじめに

最初にも述べたように、MySQL8系ではJSON型に関連する新しい機能がいくつか追加され、より柔軟なデータ管理が可能となりました。
そこでリレーショナルデータベースにおける「正規化」に対して、MySQL8系のJSON型を活用することでどんなメリット・デメリットがあるかを示し、どの場面で使えそうかを模索していきたいなと思います。

2. MySQL8系の新しいJSON関連機能

2-1. 複数値 Index

複数値 Indexとは、JSONのカラムの中に、配列としてキー値が複数含まれている場合でも有効に機能できるINDEXです。
JSON型では直接INDEXを貼れないため、JSONから抜き出したデータでカラムを作成し、仮想的なINDEXとして用意することで解決していました。ただ、以前から速度があまり上がらないという問題があり、今回の複数値 Indexの追加によって速度向上が見込めるようになりました。

2-2 新しいJSON関数

2-2-1 JSON_TABLE()

JSONデータをテーブル形式に変換して返してくれる関数で、
この関数を用いて、別のテーブルとJOINできたりもするので便利な関数です。

2-2-2 MEMBER OF()

MEMBER OF()とは、特定の値がJSON配列の中にデータとして存在するかを確認するために使用されます。

具体的なクエリ使用例は4章に記載します。

※ まだまだありますが、今回は説明をする上で主に使用する関数を記載しております🙇‍♀️
他のJSON関数は5章にてtipsとして記載します。

3. 正規化されたDB設計とJSON型の比較

では、実際にMySQL8系にて追加されたJSON関連機能も使いつつ、
正規化されたDB設計とJSON型の比較を行ってみます！

3-1 正規化されたDB設計の例

まずは、よくあるECサイトをイメージしてDB設計を考えてみます。
いろいろと必要なデータはありそうですが、一旦最低限の必要な情報として、
「顧客情報」・「注文情報」・「注文商品」の情報を元にそれぞれ別のテーブルに分割して管理します。下記にクエリ文の例を記載しておきます。

顧客情報テーブル(test_customers)

CREATE TABLE test_customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

注文情報テーブル(test_orders)

CREATE TABLE test_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount_total INT,
    FOREIGN KEY (customer_id) REFERENCES test_customers(id) 
);

注文商品テーブル(test_order_items)

CREATE TABLE test_order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    product_name VARCHAR(100),
    quantity INT,
    price INT,
    FOREIGN KEY (order_id) REFERENCES test_orders(id)
);

3-2 JSON型を用いた設計の例

次にJSON型を用いて、同様にショッピングアプリを想定した設計を行ってみます。
正規化されたテーブルに対して、JSON型を使用して同じデータをシンプルな1つのテーブルに統合してみます。

顧客情報テーブル

CREATE TABLE test_customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    orders JSON -- JSON型を用いる
);

注文情報や商品情報をすべて`test_customers.orders`カラムにJSONの形式で格納する形にしてみました。

実際にデータをINSERTする場合は下記のようなクエリ文を用いて挿入します。

INSERT INTO test_customers (name, email, orders)
VALUES ('SQL taro', 'sql_taro@example.com',
'[
  {
    "order_date": "2024-09-01",
    "amount_total": 350,
    "items": [
      {"product_name": "chocolate", "quantity": 2, "price": 150},
      {"product_name": "apple", "quantity": 1, "price": 200}
    ]
  }
]');

3-3 データ抽出のクエリ文比較

3-3-1 正規化されたテーブルでのクエリ文

先ほど設計した内容を元に、顧客名や注文日などのデータを抽出してみる場合、
下記のようなクエリ文になります。

SELECT 
    tc.name AS '顧客名', 
    to.order_date AS '注文日', 
    toi.product_name AS '製品名', 
    toi.quantity AS '個数', 
    toi.price AS '値段'
FROM test_customers c
JOIN test_orders o ON tc.id = to.customer_id
JOIN test_order_items toi ON to.id = toi.order_id
WHERE tc.id = 1;

3-3-2 JSONを用いたテーブルのクエリ文 (MySQL8系新機能活用)

JSON型の場合、注文と商品情報を1つのクエリ文で取得できそうです。
また、MySQL8系の新しいJSON関連機能にて説明しましたJSON_TABLEを用いて、
クエリ文を記載してみます。

1. JSON形式からデータを取り出した場合

SELECT orders
 FROM test_customers tc;

抽出結果

※ JSONデータが細かいので、一部切り取ってます。

2. JSON_TABLEを用いてデータ抽出した場合

SELECT order_info.*
FROM test_customers,
JSON_TABLE(orders, '$[*]' 
COLUMNS (
    order_date DATE PATH '$.order_date',
    amount_total INT PATH '$.amount_total',
    items JSON PATH '$.items'
)) AS order_info;

抽出結果

3. 「3-3-1」と同様の内容を抽出する場合

SELECT name, order_info.*
FROM test_customers,
JSON_TABLE(orders, '$[*]' COLUMNS (
   order_date DATE PATH '$.order_date',
   total_amount INT PATH '$.total_amount',
   items JSON PATH '$.items'
)) AS order_info
WHERE id = 1;

抽出結果

2・3を見てみるとわかるように、
JSON_TABLEを用いることでテーブルの形式に変換でき、
JSONの柔軟性を活かしつつも正規化されたテーブルと同様の形で抽出できそうです。
また、今回の正規化とJSONの比較という部分を考察すると、
JOINを使わなくなるのでテーブルとしてはシンプルになりますし、
JSONを用いることでデータの柔軟性は上がったのかなと思いつつも、
JOINするテーブルが多くなった時にデータの冗長性が問題となりそうな感じがしました。

4. [おまけ] MySQL8系の機能を活用してパフォーマンスを考える

MySQL8系の新しいJSON関連機能の項目でも説明しました複数値 Indexを用いることで、パフォーマンス向上を図れます。
今回の記事ではパフォーマンス検証を行いませんが活用方法を下記に記載します。

「まずはテスト用のテーブルの作成とテストデータをINSERTする。」

-- JSON型の検証用テーブル作成
 CREATE TABLE test_json (
    id INT AUTO_INCREMENT PRIMARY KEY,
    info JSON -- JSON型を用いる
);

-- テストデータをINSERT
INSERT INTO test_json (info) VALUES
(
    '{
        "name": "Taro",
        "age": 20,
        "address": [123456, 78910]
    }'
);

次に本題の複数値INDEXの貼り方を記載していきます。

「最初に複数値INDEXを貼ってない場合において、MySQL8.0.17から追加された
MEMBER OFを使用して確認する。」

city:Tokyoの値を含むかデータを抽出

SELECT INFO 
FROM test_json WHERE 123456 MEMBER OF(info->'$.address');

抽出結果

実行計画の確認

EXPLAIN SELECT INFO 
FROM test_json WHERE 123456 MEMBER OF(info->'$.address');

抽出結果

もちろんINDEXを何も指定していないためtype ALLとなります。
では、実際に複数値INDEXを貼ってみます。
JSONのフィールドに対するINDEXを作成するには、
JSONの値を適切なデータ型にキャストする必要があります。

adressに対してINDEXを貼る

ALTER TABLE test_json 
ADD INDEX idx_address( (CAST(info->'$.address' AS UNSIGNED ARRAY)) );

再度実行計画を確認

EXPLAIN SELECT INFO FROM test_json WHERE 123456 MEMBER OF(info->'$.address');

抽出結果

上記のようにtype=ref, key=idx_addressとなり、
インデックスが効いていることを確認できました。
JSON形式のデータを使用する場合、複数値INDEXを用いてパフォーマンス向上
を考えられたら素敵ですね！

[参照URL]
https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-multi-valued

5. [おまけ] その他にMySQL8系で追加されたJSON関数

最後にMySQL8系より追加されたJSON関数を下記のテストテーブルを用いて、
実際に具体例を挙げながら説明します。

-- テストテーブル作成
CREATE TABLE test_json (
    id INT AUTO_INCREMENT PRIMARY KEY,
    info JSON
);

-- テストデータ挿入
INSERT INTO test_json (info) VALUES
    ('{"name": "Taro", "hobbies": ["reading", "swimming"]}'),
    ('{"name": "Jiro", "hobbies": ["reading", "music", "running"]}');

① JSON_OVERLAPS()

2つのJSONデータを比較して、データが重複しているかを確認します。
これは2つのJSONオブジェクトや配列に共通の要素がある場合にTRUE、共通の要素がない場合はFALSEを返します。

重複しているデータをチェック

SELECT *
FROM test_json
WHERE JSON_OVERLAPS(info->'$.hobbies', '["swimming", "music"]');

抽出結果

② JSON_SCHEMA_VALID()

MySQL8.0.17以降に使用できる関数で、
JSONデータが指定したJSONスキーマに適合しているかを検証します。
この関数はJSONデータがスキーマに準拠している場合はTRUE、準拠していない場合はFALSEを返します。

JSONデータが準拠しているか確認する。

SELECT id, JSON_SCHEMA_VALID('{
    "type": "object",
    "properties": {
        "name": { "type": "string" },
        "hobbies": { "type": "array" },
    },
    "required": ["name", "hobbies"]
}', info) AS is_valid
FROM test_json;

抽出結果

結果を見てわかりますように、どちらも準拠しているので「１」を返しています。

③ JSON_VALUE()

こちらはシンプルな関数で、JSONデータからスカラー値を抽出します。
特定のキーに対応する単一の値を取得するのに使用します。

nameカラムの値を取得する

SELECT id, JSON_VALUE(info, '$.name') AS name
FROM test_json;

シンプルなだけに多くの場面で使用することがありそうなので、
覚えておくと良いかもしれないです！

詳しくは下記URLにて、JSON関数の一覧が載っておりますので
興味があれば見ていただければと思います！
https://dev.mysql.com/doc/refman/8.0/ja/json-function-reference.html

6. まとめ

今回、JSONと正規化表現の比較をMySQL8系で新しく追加された機能を交えながら説明させていただききました。

紹介していく中で、JSON型・正規化のそれぞれでメリットデメリットがイメージできたかなと思います。
元々5.7系でもJSON型は使えましたが機能の追加によって、より汎用性が高まったかなとも思いますので、メリット・デメリットを考えながらも使用していくのが良いかなと思いました！最後まで見ていただきありがとうございました！

Join Us !

ウエディングパークでは、一緒に働く仲間を募集しています！
ご興味ある方は、お気軽にお問合せください（カジュアル面談から可）

採用情報を見る

目次

1. はじめに

2. MySQL8系の新しいJSON関連機能

2-1. 複数値 Index

2-2 新しいJSON関数

2-2-1 JSON_TABLE()

2-2-2 MEMBER OF()

3. 正規化されたDB設計とJSON型の比較

3-1 正規化されたDB設計の例

顧客情報テーブル(test_customers)

注文情報テーブル(test_orders)

注文商品テーブル(test_order_items)

3-2 JSON型を用いた設計の例

顧客情報テーブル

3-3 データ抽出のクエリ文比較

3-3-1 正規化されたテーブルでのクエリ文

3-3-2 JSONを用いたテーブルのクエリ文 (MySQL8系新機能活用)

1. JSON形式からデータを取り出した場合

抽出結果

2. JSON_TABLEを用いてデータ抽出した場合

抽出結果

3. 「3-3-1」と同様の内容を抽出する場合

抽出結果

4. [おまけ] MySQL8系の機能を活用してパフォーマンスを考える

city:Tokyoの値を含むかデータを抽出

抽出結果

実行計画の確認

抽出結果

adressに対してINDEXを貼る

再度実行計画を確認

抽出結果

5. [おまけ] その他にMySQL8系で追加されたJSON関数

① JSON_OVERLAPS()

重複しているデータをチェック

抽出結果

② JSON_SCHEMA_VALID()

JSONデータが準拠しているか確認する。

抽出結果

③ JSON_VALUE()

nameカラムの値を取得する

6. まとめ

共有:

takada

データベースの人気記事

【MySQL】肥大化したInnoDBテーブルを圧縮機能で縮小する方法！

【DB設計入門|ER図|MySQL】コンビニレシートから学ぶ！データモデリング手法

MySQL 5.7のマルチソースレプリケーション機能を検証してみました