MySQL 8: 数百万レコードを捌く「武器」となる Functional Index と Invisible Index

従来のインデックスが実務の課題で「お手上げ」になる時

大手ニュースサイトのCMSを担当していた時のことを思い出します。articlesテーブルのレコード数が2,500万件に達した際、created_atカラムにインデックスを貼っていたにもかかわらず、コンテンツチームが月次レポートを実行するたびにサーバーのCPU使用率が90%に跳ね上がっていました。

SELECT * FROM articles WHERE YEAR(created_at) = 2024;

問題は、カラムをYEAR()のような関数で囲ってしまうと、MySQLがインデックスを完全に「無視」してしまう点にあります。高速な検索の代わりに、2,500万件のレコード一件ずつに対して値を再計算しなければなりません。その結果、フルテーブルスキャンが発生して30〜40秒もかかり、ページがいつまで経っても読み込まれずユーザーにストレスを与えていました。

以前は、年を保存するためだけに実際のカラム（Stored Column）を追加してインデックスを貼る必要がありました。この方法はストレージを消費するだけでなく、コードも煩雑になります。しかし、MySQL 8.0からは、Functional IndexesとInvisible Indexesが登場し、よりスマートなアプローチが可能になりました。私はこのペアを使って、テーブル構造を大きく変えることなくデータベースをクリーンアップしました。

Functional Indexes：「目に見えないもの」にインデックスを貼る

簡単に言うと、Functional Index（関数インデックス）は、式や関数に対して直接インデックスを貼ることを可能にします。これは、文字列操作関数、時間関数、あるいは複雑なレポートクエリを介して頻繁にクエリを実行する場合の救世主となります。

なぜこの方法が速いのか？

実際には、MySQLは隠れた仮想カラム（Hidden Virtual Column）を作成し、その上にインデックスツリーを構築します。その式に合致するクエリを実行すると、オプティマイザは全表スキャンを行う代わりに、自動的にこのインデックスをキャッチします。

先ほどの年別フィルタリングの課題を解決するには、次のコマンドを一つ実行するだけです：

ALTER TABLE articles ADD INDEX idx_created_year ((YEAR(created_at)));

ちょっとしたコツ： 必ず二重の括弧 ((...)) を使用する必要があります。これがないと、MySQLはそれが式であることを理解できず、すぐに構文エラーを返します。

JSONデータを瞬時に処理する

例えば、50万件の電子機器製品があり、各製品のRAMやCPUの構成がspecsという JSONカラムに保存されているとします。RAMが「16GB」の製品を検索するのは通常非常に低速です。MySQL 8では、JSON内のキーに直接インデックスを貼ってみましょう：

ALTER TABLE products ADD INDEX idx_ram_size ((CAST(specs->>"$.ram" AS CHAR(10))));

適用後、クエリ速度は2秒からわずか数ミリ秒に短縮されました。実環境において、これは極めて大きな違いです。

Invisible Indexes：安全なメンテナンスを可能にする「透明」モード

不要だと思ってインデックスを削除したいけれど、誤って削除してサイトがダウンするのが怖いと思ったことはありませんか？インデックスの削除自体は簡単ですが、もし間違いだった場合、数十GBのテーブルでインデックスを再構築するには数時間かかることもあり、その間システムが麻痺してしまいます。

Invisible Index（不可視インデックス）を使用すると、オプティマイザに対してそのインデックスを隠すことができますが、MySQLはINSERTやUPDATEが実行された際に、裏でそのインデックスのデータを更新し続けます。

「プロ」仕様のデータベースクリーンアップ手順

いきなり削除するのではなく、私は常に以下の安全な3ステップの手順を踏んでいます：

1. インデックスを隠す： Invisible状態に切り替えます。

   ALTER TABLE orders ALTER INDEX idx_old_status INVISIBLE;

2. 観察： 48時間、ログとダッシュボードを監視します。急に遅くなるクエリがなければ、そのインデックスは本当に「ゴミ」です。
3. 永久削除： この段階で、自信を持ってDROP INDEXを実行します。

万が一問題が発生しても、VISIBLEコマンドを使えばわずか0.1秒で元に戻せます。最初からインデックスを再構築するのを待つよりもはるかに高速です。

2つの機能のクイック比較表

「苦い経験」から学んだ実践的なノウハウ

これらの新しいインデックスを導入する中で、いくつかの黄金律を導き出しました：

• Functional Indexを乱用しない： 本質的には隠れたカラムです。作りすぎると、データベースの容量が肥大化し、大量のINSERT操作が遅くなります。
• 常にEXPLAINを使用する： インデックス作成後は、EXPLAINを実行して確認してください。クエリ内のデータ型（例：文字列）がFunctional Indexのデータ型（例：数値）と一致しない場合、MySQLはそのインデックスを無視します。
• 新しいインデックスはInvisibleでテストする： 新しいインデックスを追加する際は、まずINVISIBLE状態で作成します。その後、セッションフラグuse_invisible_indexes=onを使用して、自分だけでテストしてからサーバー全体に公開できます。

おわりに

データベースの最適化とは、とにかく多くのインデックスを詰め込むことではありません。優れたエンジニアとは、適切なタイミングで適切な場所にインデックスを使える人のことです。Functional Indexは厄介なクエリを解決し、Invisible Indexはメンテナンス時の絶対的な安心感をもたらします。

もしMySQL 8を使用していながら、まだ手動でGenerated Columnsを作成して苦労していたり、データベースのクリーンアップのたびに不安を感じているなら、ぜひこれら2つの機能を試してみてください。管理業務が格段に楽になるはずです！