Posted inMySQL

MySQL 8におけるMulti-Valued Index活用ガイド：JSON配列クエリの最適化

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

現場の実態：JSON配列がデータベースを逼迫させた日

先日のブラックフライデー期間中、私たちが運用するEコマースシステムで重大なアラートが発生しました。マスターデータベース（AWS RDS db.r6g.2xlarge）のCPU使用率が一気に98%まで跳ね上がり、多くのAPIエンドポイントで504 Gateway Timeoutが頻発する事態に陥ったのです。

原因は、app_usersテーブルのassigned_rolesカラムと、productsテーブルのtagsカラムにありました。設計段階でロールIDを[101, 204, 305]のようなJSON配列として保持していたためです。ステージング環境で数万件程度のデータを検証していた頃は、MEMBER OF()やJSON_CONTAINS()といった関数も10ms未満で軽快に動作していました。

しかし、レコード数が1,000万件を超える本番環境にデプロイした途端、状況は一変しました。ユーザー権限を絞り込むクエリが1回あたり1.8秒から3秒近くもかかり、MySQLは1,000万行すべてに対してFull Table Scan（ALL）を実行していたのです。ストレージエンジンは、目的の数値を1つ探すためだけに全レコードのJSON配列を1件ずつ展開・評価しなければならず、瞬く間にシステム全体のボトルネックとなりました。

従来のMySQLバージョンであれば、1対Nのリレーションテーブルに正規化するか、Generated Column（生成列）を活用するのが定石の解決策でした。しかし、標準的なB-Treeインデックスは1行に対して1つのスカラー値しかマッピングできません。JSON配列内の個々の要素に対して直接インデックスを張ることはできなかったのです。

この問題を根本から解決したのが、MySQL 8.0.17で導入されたMulti-Valued Indexです。この機能により、B-Tree上で複数の異なるインデックスレコードが同一のPrimary Keyを参照できるようになりました。

環境要件とサンプルデータ

この機能を利用するには、InnoDBエンジンを搭載したMySQL 8.0.17以上（またはそれ以降の8.0.x系、8.4 LTS）が必要です。

現在のバージョンは以下のコマンドで手軽に確認できます：

mysql -u root -p -e "SELECT VERSION();"

以下は、実際にトラブルが発生したテーブル構造を再現したスキーマです：

CREATE DATABASE IF NOT EXISTS demo_indexing;
USE demo_indexing;

CREATE TABLE app_users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL,
    assigned_roles JSON NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

各レコードには、例えば[1, 5, 12, 45]のようにユーザーの権限リストが格納されます。

Multi-Valued Indexの設定方法

1. JSON配列に対するインデックス作成構文

Functional Indexの構文内でCAST(... AS type ARRAY)関数を使用します：

ALTER TABLE app_users 
ADD INDEX idx_user_roles ( (CAST(assigned_roles AS UNSIGNED ARRAY)) );

二重の丸括弧( (...) )は必須の構文です。括弧が1重欠けているだけでも、MySQLは即座に構文エラーを返します。

動作の仕組みは非常に明快です。例えばユーザーID 999が配列[1, 5, 12]を持っている場合、B-Tree上には「1」「5」「12」という3つの独立したインデックスキーが生成されます。そして、これら3つのキーはすべて主キーである999を直接指し示します。

2. インデックスを有効活用できる3つのクエリ関数

MySQLのオプティマイザがMulti-Valued Indexを使用するのは、以下の3つの関数に限定されます：

  • MEMBER OF(): スカラー値が配列内に存在するかどうかを判定します。
  • JSON_CONTAINS(): 指定したサブセット（部分集合）が配列に含まれているかを検証します。
  • JSON_OVERLAPS(): 2つの配列に共通する要素が存在するか（重複があるか）を判定します。「ロール12またはロール45」を持つユーザーを抽出したい場合などに極めて便利です。

実際のクエリ例は以下の通りです：

-- 1. ロールID 12を持つユーザーを検索
SELECT id, username, assigned_roles 
FROM app_users 
WHERE 12 MEMBER OF (assigned_roles->'$');

-- 2. ロール12と45の両方を同時に持つユーザーを検索
SELECT id, username, assigned_roles 
FROM app_users 
WHERE JSON_CONTAINS(assigned_roles, '[12, 45]');

-- 3. ロール12または45のいずれかを持つユーザーを検索（積集合・共通部分）
SELECT id, username, assigned_roles 
FROM app_users 
WHERE JSON_OVERLAPS(assigned_roles, CAST('[12, 45]' AS JSON));

3. 落とし穴：型不一致（Type Mismatch）

これは、インデックスが無効化されてしまう最も代表的な原因であり、静かにパフォーマンス低下を引き起こします。

インデックス定義時にUNSIGNED ARRAYを指定したにもかかわらず、バックエンド側の実装で文字列として'12' MEMBER OF (...)のように値を渡してしまうケースです。文字列型と整数型の不一致が発生すると、MySQLは即座にインデックスの使用を諦め、再び悪夢のFull Table Scanへとフォールバックしてしまいます。

クエリに渡すパラメータのデータ型は、インデックス化した配列の型と必ず100%一致させるように徹底してください。

性能検証とトレードオフ

ALTER TABLE文の実行が完了したからといって、油断は禁物です。必ずEXPLAINコマンドを用いて実行計画を確認しましょう。

1. 実行計画（Execution Plan）の比較

インデックス作成前のクエリ：

EXPLAIN SELECT * FROM app_users WHERE 12 MEMBER OF (assigned_roles->'$');
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | app_users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10245100 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+

MySQLはtype = ALLとなり、1,020万行を超える全レコードをスキャンしています。

idx_user_rolesインデックスを追加した後：

EXPLAIN SELECT * FROM app_users WHERE 12 MEMBER OF (assigned_roles->'$');
+----+-------------+-----------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | app_users | NULL       | ref  | idx_user_roles | idx_user_roles | 9       | const |   42 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+----------------+----------------+---------+-------+------+----------+-------+

アクセスタイプがrefへと切り替わりました。読み取り対象の行数は1,000万行以上からわずか42行へと激減し、本番環境におけるクエリのレイテンシは1.8秒から2ミリ秒へと大幅に短縮されました。

2. 書き込み増幅（Write Amplification）コストとディスク容量

インデックスによって参照性能は劇的に向上しますが、その分書き込み時のオーバーヘッドが生じます。この機能は決して無償ではありません。

1,000万行のテーブルにおいて各JSON配列が平均5個のロールIDを保持している場合、MySQLはB-Tree上で最大5,000万件ものインデックスエントリを管理・維持しなければなりません。

インデックスが消費しているディスク容量は、以下のクエリで確認できます：

SELECT 
    table_name,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_mb
FROM information_schema.tables
WHERE table_schema = 'demo_indexing' AND table_name = 'app_users';

現場での実践的な教訓として、Multi-Valued Indexの適用はロールID、パーミッション、カテゴリIDなどのように要素数が少なく比較的固定的な配列（20要素未満が目安）にとどめるべきです。配列に数百個の要素が含まれ、かつ頻繁にINSERTやUPDATEが発生するテーブルの場合、ディスクI/Oが急激に跳ね上がるリスクがあります。

Share:
Tags: