Posted inMySQL

MySQL Generated Columns：コードを変更せずにクエリ速度を劇的に向上させるテクニック

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

なぜGenerated Columnsに注目すべきなのか？

数十ものクエリの中で (price * quantity) AS total_price という記述を何度も繰り返すのは、退屈なだけでなくミスも誘発しやすくなります。さらに深刻なのは、JSONデータを保存していて、その中の特定のフィールドで頻繁にフィルタリングを行う場合です。テーブルが数百万行に達すると、JSON_EXTRACT 関数はCPUに多大な負荷をかけることになります。

以前は、補助的なカラムを更新するために Trigger を使用したり、Backend 側で直接計算を行ったりするのが一般的でした。しかし、Trigger はメンテナンスが難しい「ブラックボックス」になりがちです。一方で、アプリケーション層で計算を行うと、検索を高速化するための Index を貼ることが不可能になります。

私が参加したあるプロジェクトの実例： 約1,000万レコード（容量50GB）の orders テーブルにおいて、計算処理をクエリから Generated Columns に移行したことで、CPU負荷を70%から25%にまで削減できました。Generated Columns（MySQL 5.7から導入）を使用すると、同じ行内の既存データから自動的に計算されるカラムを作成できます。

リソースの無駄を避けるために、これら2つのカラムタイプの違いを明確に理解しておく必要があります：

  • Virtual Generated Columns（デフォルト）： データが読み取られるときにのみ値が計算されるため、ディスク容量を消費しません。重要なのは、この仮想カラムに対しても Index を貼ることができる という点です。
  • Stored Generated Columns： INSERT または UPDATE 時に値が物理的にディスクへ保存されます。ストレージ容量を消費しますが、MySQLが再計算する必要がないため、読み取り速度は非常に高速になります。

実践的な実装：構文と使い方

これはコア機能であるため、追加のエクステンションをインストールする必要はありません。以下は、テーブル作成時に自動計算カラムを定義する方法です：

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    price DECIMAL(10, 2),
    quantity INT,
    -- 仮想カラム：ストレージ容量の節約
    total_price DECIMAL(10, 2) AS (price * quantity) VIRTUAL,
    -- 保存済みカラム：CPU負荷の最適化
    total_price_stored DECIMAL(10, 2) AS (price * quantity) STORED
);

稼働中のシステムであれば、ALTER TABLE を使用できます。私は通常、アプリケーションのロジック構造を壊すことなく、遅いクエリを最適化するためにこの方法を使用します：

ALTER TABLE products 
ADD COLUMN discounted_price DECIMAL(10, 2) 
AS (original_price * 0.9) VIRTUAL;

重要な注意点：これらのカラムに手動でデータを INSERT することはできません。MySQLが完全に制御権を持ちます。アプリケーション側から値を上書きしようとする試みは、システムによって即座に拒否されます。

大規模システムを救う2つのケーススタディ

1. JSONデータの検索を25倍高速化する

これが最も実用的な例です。ユーザー設定を settings カラム（JSON型）に保存していると仮定します。theme = 'dark' に設定している全ユーザーを検索したい場合：

-- このクエリはフルテーブルスキャン（Full Table Scan）を発生させ、非常に低速です！
SELECT * FROM user_profiles WHERE settings->"$.theme" = 'dark';

解決策は、theme を抽出する Virtual Column を作成し、そこに Index を貼ることです：

ALTER TABLE user_profiles
ADD COLUMN user_theme VARCHAR(20) AS (settings->>"$.theme") VIRTUAL,
ADD INDEX idx_user_theme (user_theme);

これを適用すると、MySQLは各JSONファイルをパースする代わりに B-Tree Index を使用して検索を行います。レスポンスタイムは 500ms から 20ms 以下に短縮される可能性があります。

2. フルネーム（Full Name）による検索

WHERE 句で CONCAT(first_name, ' ', last_name) を使用して Index を無効化してしまう代わりに、Stored Column を使用しましょう：

ALTER TABLE employees
ADD COLUMN full_name VARCHAR(255) AS (CONCAT(first_name, ' ', last_name)) STORED,
ADD INDEX idx_full_name (full_name);

Virtual と Stored、どちらを選ぶべきか？

サーバーのリソースに基づいて検討してください：

  • Virtual を選ぶ場合： ディスク容量を節約したいとき。データの変更は少ないが、フィルタリング（Filter）によく使われるフィールドに Index を貼る場合に非常に効果的です。
  • Stored を選ぶ場合： 計算が非常に複雑で、文字列処理に多大なCPUリソースを消費する場合。または、仮想カラムへの Index サポートが不十分な古いバージョンの MySQL を使用している場合。

最適化効果の確認

変更後は、必ず EXPLAIN を使って検証してください。key カラムに作成した Index 名が表示されていれば成功です。

EXPLAIN SELECT * FROM user_profiles WHERE user_theme = 'dark';

システム内の自動計算カラムの一覧を管理するには、information_schema を介して素早く照会できます：

SELECT table_name, column_name, generation_expression 
FROM information_schema.columns
WHERE is_generated = 'ALWAYS' AND table_schema = 'your_db_name'; -- your_db_name は実際のデータベース名に置き換えてください

実体験に基づいたアドバイス：巨大なテーブルで Stored Columns を使用する場合は、ディスク容量（Data_length）を注意深く監視してください。逆に、複雑な正規表現（Regex）関数を含む Virtual Columns を使用する場合は、サーバーのCPUグラフに注意を払ってください。

結論として、Generated Columns は利便性とパフォーマンスの完璧なバランスを実現するソリューションです。Backend のロジックを大きく変えることなく、SQLコードをスッキリさせ、Index の力を最大限に引き出すことができます。

Share:
Tags: