MySQL 8のFunctional Indexを活用して式ベースのクエリを最適化する実践ガイド

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

インデックスがあるのにフルテーブルスキャンになる原因とは?

500万件のレコードを保持するusersテーブルがあるとします。created_at列とusername列には適切なB-Treeインデックスが設定されており、idによるクエリや完全一致検索はすべて5ms未満でレスポンスを返しています。

しかし、登録年でデータを絞り込んだり、大文字・小文字を区別せずにアカウントを検索したりする必要が生じたときに問題が発生します。

-- 2023年に登録したユーザーを検索
SELECT id, full_name, email FROM users 
WHERE YEAR(created_at) = 2023;

-- 大文字・小文字を区別せずにアカウントを検索
SELECT id, username FROM users 
WHERE LOWER(username) = 'nguyenvana';

実行時間は数ミリ秒から一気に4〜6秒へと跳ね上がります。MySQLは全500万行をスキャンし、同時に複数のクエリが実行されるとサーバーのCPU使用率は100%に達してしまいます。

なぜデータベースは既存のインデックスを無視してしまうのでしょうか?従来のB-Treeインデックスは、基本的にカラムの元の値のみを格納しており、関数で処理された後の結果を保持していないためです。

技術的な本質:Non-sargableクエリとフルテーブルスキャン

データベースの最適化において、上記の2つのSQL文はnon-sargableクエリ(Search Argument Ableではないクエリ)に分類されます。つまり、オプティマイザ(Optimizer)がインデックスツリーを利用して検索範囲を絞り込むことができません。

その仕組みは非常にシンプルです。

  • created_atのインデックスツリーには、2023-05-12 08:30:00のような生の日時値しか含まれておらず、2023という年数値はあらかじめ保持されていません。
  • カラムをYEAR()関数でラップすると、MySQLはディスクから全行を読み出し、比較対象の値を取得するためにCPUで500万回の関数計算を実行せざるを得なくなります。

この動作はEXPLAINコマンドで確認できます。

EXPLAIN SELECT id, full_name, email FROM users 
WHERE YEAR(created_at) = 2023;

type列にはALLが表示され、key列はNULLを返します。既存のインデックスが完全に無効化されていることは明白です。

従来のアプローチとその限界

MySQL 8が登場する前は、通常以下の2つのアプローチのいずれかが選択されていました。

1. クエリ条件を範囲指定(Range Query)に書き換える

日時データ型の場合、最もクリーンな方法は元カラムをそのまま保持し、範囲でフィルタリングすることです。

SELECT id, full_name, email FROM users 
WHERE created_at >= '2023-01-01 00:00:00' 
  AND created_at < '2024-01-01 00:00:00';

この解決策により、既存のインデックスを即座に活用できます。しかし、数値や日付データにしか適用できず、文字列の切り出しや複雑な式の計算、JSONデータの抽出などには対応できません。

2. 生成列(Generated Column)を作成してインデックスを付与する

MySQL 5.7以降では、仮想列を追加する手法が一般的でした。

-- アカウント作成年を保持する仮想列を作成
ALTER TABLE users 
ADD COLUMN created_year INT GENERATED ALWAYS AS (YEAR(created_at)) VIRTUAL;

-- 仮想列にインデックスを作成
CREATE INDEX idx_users_created_year ON users(created_year);

クエリ速度は劇的に向上しますが、引き換えにテーブルスキーマが肥大化します。また、バックエンド開発チームは新しいカラムを参照するようにアプリケーションコードを改修する必要があります。

最適な解決策:MySQL 8のFunctional Index

MySQL 8.0.13以降では、Functional Index(式インデックス)がサポートされています。この機能により、テーブルに新しいカラムを追加することなく、式の評価結果に対して直接インデックスを作成できます。

内部的には、MySQL 8がストレージ層に非表示の仮想列(hidden virtual column)を生成し、その上にB-Treeを構築します。そのため、外部に公開されているテーブル定義は完全にクリーンな状態を保てます。

Functional Indexの作成構文

インデックス対象の式は、必ず二重の丸括弧(())で囲む必要があります。

-- 年の式に対するインデックスを作成
CREATE INDEX idx_users_year_created 
ON users ((YEAR(created_at)));

-- 小文字変換した文字列に対するインデックスを作成
CREATE INDEX idx_users_lower_username 
ON users ((LOWER(username)));

外側の括弧はCREATE INDEX構文用で、内側の括弧がインデックス対象の式を囲むためのものです。

実際のパフォーマンス検証

元のクエリを変更せずに、再度EXPLAINで実行計画を確認します。

EXPLAIN SELECT id, full_name, email FROM users 
WHERE YEAR(created_at) = 2023;

各指標に劇的な改善が見られます。

  • key列でインデックスidx_users_year_createdが適用される。
  • type列がALLからrefへ改善。
  • スキャン対象行数(rows)が5,000,000行から約120,000行に減少。
  • 実行時間が4.2秒からわずか3ミリ秒に短縮。

最も重要なのは、バックエンドのソースコードを1行も修正する必要がないという点です。

JSONデータ検索の高速化

Functional Indexは、JSONカラムを扱う際に特に真価を発揮します。テーブルにデバイス情報を格納するmetadataカラム(例: {"device": {"os": "iOS", "version": 16}})があると仮定しましょう。

JSONは直接インデックスを作成できないため、CAST関数を使ってデータ型を明示的に変換します。

-- JSON内のosフィールドにインデックスを作成
CREATE INDEX idx_user_os 
ON users ((CAST(metadata->>'$.device.os' AS CHAR(20))));

-- クエリ実行:WHERE句の式はインデックス定義と完全一致する必要がある
SELECT id, full_name FROM users 
WHERE CAST(metadata->>'$.device.os' AS CHAR(20)) = 'iOS';

実務導入における3つの技術的注意点

本番環境でのトラブルを防ぐために、以下の3つのルールを正しく理解しておく必要があります。

  1. 関数は決定論的(Deterministic)であること:同じ入力に対して常に同じ出力を返す必要があります。NOW()、CURDATE()、RAND()など、実行ごとに値が変わる関数を指定した場合、MySQLはインデックス作成を拒絶します。
  2. 書き込みオーバーヘッド(Write Overhead):対象カラムに対するINSERTやUPDATEのたびに式の再計算とB-Treeの更新が発生します。頻繁に使用される検索条件に絞って作成することが重要です。
  3. 式の一致が必要:オプティマイザがインデックスを利用するには、WHERE句の式がインデックス定義時に指定した式と完全に一致している必要があります。
Share: