MySQL 8におけるGenerated Columns活用ガイド:クエリ最適化とスマートなインデックス設計

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

クイックスタート:5分で試せる実践入門

注文金額の計算、姓名の文字列連結、あるいはJSONフィールドの抽出を行う際、皆さんはどこで処理していますか?バックエンド側で処理するか、データベースのトリガーを書くかでしょうか。しかし、どちらのアプローチもコードが複雑化しがちです。MySQLには、こうした処理をスマートに解決できる便利な機能が用意されています。それが Generated Columns(生成列)です。

端的に言えば、MySQLが同一行内の他のカラム値に基づき、指定した式から自動的にカラム値を計算する仕組みです。以下の注文テーブルの例を見てみましょう。

CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    item_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    quantity INT NOT NULL,
    discount_percent INT DEFAULT 0,
    -- 割引適用後の合計金額を自動計算する生成列
    total_amount DECIMAL(10, 2) GENERATED ALWAYS AS (
        (price * quantity) * (1 - discount_percent / 100)
    ) VIRTUAL
);

-- total_amountカラムを省略してテストデータを挿入
INSERT INTO order_items (item_name, price, quantity, discount_percent)
VALUES ('メカニカルキーボード', 1500000, 2, 10);

-- 結果の確認
SELECT id, item_name, total_amount FROM order_items;

コマンドを実行すると、total_amount に自動的に 2700000.00 が表示されます。開発者が手作業で計算を行う必要はなく、MySQLがすべて自動で処理してくれます。

仕組みの本質:Virtual ColumnとStored Columnの違い

Generated Columnを作成する際は、VIRTUAL または STORED のいずれかのモードを選択する必要があります。選択を誤ると、データベース容量が肥大化したり、CPUリソースを枯渇させたりする原因になります。

1. Virtual Column(デフォルトモード)

VIRTUAL では、MySQLはこのカラムのデータをディスクに保存しません。SELECT クエリによって参照されるたびに、MySQLがRAM上でその場で(on-the-fly)計算します。

  • メリット: ディスク容量を1バイトも消費しません。ストレージエンジンが追加のカラムをディスクへ書き込む必要がないため、INSERT や UPDATE の書き込み速度が低下しません。
  • デメリット: クエリ実行時に追加のCPUサイクルを消費します。インデックスがない状態で数百万件規模の行をフルスキャンすると、CPUに大きな負荷がかかります。
  • パフォーマンス向上の鍵: MySQLでは、VIRTUAL カラムに対して直接セカンダリインデックス(B-Tree)を作成できます。計算結果の値はプライマリテーブルではなくインデックスツリーにのみ保存されるため、ディスク容量を節約しつつクエリ速度を最大限に高められます。

2. Stored Column

対照的に、STORED はレコードが新規挿入または更新されるたびに値を計算し、ディスク上に直接書き込みます。

  • メリット: 読み取りが極めて高速です。通常の物理カラムと同様にデータがディスク上に存在するため、クエリ時にCPUが再計算する必要がありません。
  • デメリット: ディスク容量を消費します。I/Oコストが増加するため、書き込み操作(INSERT/UPDATE)の処理速度が低下します。
ALTER TABLE users 
ADD COLUMN full_name VARCHAR(150) 
GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) STORED;

実践編:JSONフィールドへのインデックス作成と複合インデックス

筆者が以前運用していた50GB規模の本番ログシステムでは、audit_events テーブルが1200万行にまで肥大化していました。当時、JSON属性でフィルタリングを行うクエリはすべて悪夢と化し、フルテーブルスキャンによって1クエリあたり2.8秒から4秒もかかっていました。

Functional IndexによるJSONクエリの高速化

MySQLでは、JSON 型のカラム全体に対して直接B-Treeインデックスを作成することはできません。効率的なクエリを実現する王道パターンは、検索条件として抽出したいフィールドをVirtual Columnとして切り出し、その仮想列にインデックスを作成することです。

CREATE TABLE user_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    profile_data JSON NOT NULL,
    -- JSONからemailを仮想列として抽出
    user_email VARCHAR(255) GENERATED ALWAYS AS (
        profile_data->>'$.contact.email'
    ) VIRTUAL,
    -- 仮想列にインデックスを作成
    INDEX idx_user_email (user_email)
);

-- 既存テーブルへ手軽に追加する場合
ALTER TABLE user_profiles 
ADD COLUMN phone_number VARCHAR(20) 
GENERATED ALWAYS AS (profile_data->>'$.contact.phone') VIRTUAL,
ADD INDEX idx_phone (phone_number);

EXPLAIN コマンドで実行計画を確認してみましょう。

EXPLAIN SELECT id FROM user_profiles WHERE user_email = '[email protected]';

EXPLAIN結果の type 列が ALL から ref へと変化します。数百万行全体をスキャンする代わりに、MySQLはインデックスツリー上で数十件のレコードをルックアップするだけで済むようになります。これにより、レスポンスタイムは2.8秒からわずか4ミリ秒へと劇的に短縮されます。

複雑なフィルタ条件に対応する複合インデックス

Generated Columnは、通常の物理カラムと組み合わせて複合インデックス(Composite Index)に含めることも可能です。

ALTER TABLE order_items 
ADD INDEX idx_discount_amount (discount_percent, total_amount);

本番環境で避けるべき落とし穴

非常に便利なGenerated Columnsですが、以下のルールを無視して乱用すると、予期せぬトラブルにつながる恐れがあります。

  1. 関数は必ずDeterministic(決定論的)であること: 列を生成する式は、同一の入力に対して常に同一の出力を返す保証がなければなりません。NOW() や RAND()、UUID() といった時間経過や乱数によって結果が変わる関数、あるいは別テーブルを参照するサブクエリの使用は厳禁です。
  2. 原則としてまずはVIRTUALを選択する: 計算式が極端に複雑で、なおかつ非常に高い頻度で読み取られる場合を除き、安易に STORED を選ばないようにしましょう。ストレージ容量とディスクI/Oの両面において、VIRTUAL + インデックスの組み合わせが最もバランスの取れたベストプラクティスです。
  3. 適切なデータ型サイズを定義する: JSONからテキストを抽出する際は、無闇に VARCHAR(255) や TEXT を指定せず、実データに即したサイズ(例: VARCHAR(100))を定義してください。これにより、バッファプール(buffer pool)の消費を抑え、インデックスサイズをコンパクトに保てます。
  4. 大規模テーブルに対するALTER操作は慎重に: MySQL 8での VIRTUAL カラムの追加はInstant/In-Place DDLとして処理され、テーブルロックなしでほぼ瞬時に完了します。しかし、数千万行規模のテーブルに STORED カラムを追加する場合、MySQLはディスク書き込みのために全データをスキャンするため、深刻なI/O負荷と長時間のテーブルロックが発生します。本番環境でマイグレーションを実行する前に、必ずステージング環境で入念に検証してください。
Share: