DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ

MySQLインデックスを極めてクエリパフォーマンスを高速化

MySQLインデックスに関する包括的なガイドで、データベースのパフォーマンスを向上させましょう。主要なインデックスタイプ（PRIMARY KEY、UNIQUE、INDEX、FULLTEXT）、複合インデックスの作成と管理のベストプラクティス、強力なEXPLAIN文を使ったインデックス使用状況の分析方法を学びます。クエリを最適化し、データ取得を大幅に高速化して、より効率的なMySQLデータベースを実現します。

MySQLインデックスを極めてクエリパフォーマンスを高速化

MySQLインデックスは、データベースが遅いと感じたときに最初に確認する場所ですが、同時に自信を持って間違いを犯しやすい部分でもあります。インデックスはテーブルスキャンを高速なルックアップに変えることができます。しかし、書き込みを遅くしたり、メモリを浪費したり、クエリプランナーがそれを使用しない場合には進捗の錯覚を与えることもあります。

実践的な質問は「このカラムにインデックスを付けるべきか？」ではありません。より良い質問は「どのクエリを安くしようとしているのか、そしてインデックスが役立ったことをどう証明するのか？」です。この質問を念頭に置いて読み進めてください。良いインデックス設計は、実際のクエリから始まり、重要そうに見えるカラムのリストから始まるわけではありません。

MySQLインデックスとは

MySQLインデックスは、データベーステーブルに対するデータ取得操作の速度を向上させるデータ構造です。本の索引のようなものだと考えてください：特定のトピックを見つけるために本全体を読む代わりに、索引でトピックを調べると正確なページ番号がわかります。同様に、データベースインデックスを使用すると、MySQLはテーブル全体をスキャンすることなく、特定のクエリ条件に一致する行を迅速に見つけることができます。

テーブルにクエリを実行するとき、MySQLはインデックスを使用して、すべての行を調べるよりもはるかに速く関連する行を見つけることができます。これは、行数が多いテーブルや、フィルタリング（WHERE句）、テーブル結合（JOIN句）、ソート（ORDER BY句）を含むクエリに特に有益です。

インデックスの仕組み

MySQLは通常のInnoDBインデックスにB-treeインデックスを一般的に使用します。B-treeはキーをソートされた順序で保持するため、等価ルックアップ、範囲検索、順序付きスキャン、多くの結合に適しています。1つ以上のカラムにインデックスを作成すると、MySQLは次のような構造を構築します：

  • リーフノードには実際のデータポインタ、またはクラスタ化インデックス（InnoDBのプライマリキーなど）の場合はデータ行自体が含まれます。
  • 内部ノードには、正しいリーフノードを見つけるためにツリーをナビゲートするのに役立つキーが含まれます。

クエリがそのインデックスの左側を使用できる場合、MySQLはテーブル全体を読み取る代わりに、ツリーの狭い部分に移動できます。これが本当の利点です。インデックスはすべてのクエリを高速にするわけではありません。特定のアクセスパターンを安くするのです。

MySQLインデックスの種類

MySQLはさまざまなタイプのインデックスをサポートしており、それぞれに強みと使用例があります。

1. PRIMARY KEY

  • PRIMARY KEY制約は、カラムの各値が一意であり、NULLではないことを保証します。暗黙的にインデックスが作成されます。
  • テーブルは1つのPRIMARY KEYのみを持つことができます。
  • InnoDBテーブルは、プライマリキー（クラスタ化インデックス）によって物理的に順序付けられます。

例：

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100)
);

2. UNIQUEインデックス

  • UNIQUEインデックスは、インデックスが付けられたカラムのすべての値が異なることを強制します。NULL値を許可しますが、複数のNULLは許可されます（カラムがPRIMARY KEYまたはそれを防ぐ別のUNIQUE制約の一部でない限り）。
  • カラムが一意でなければならないが、プライマリ識別子ではない場合のデータ整合性の確保に役立ちます。

例：

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    sku VARCHAR(50) UNIQUE
);

3. INDEX（またはKEY）

  • 標準的なインデックスで、非一意インデックスとも呼ばれます。
  • データ取得の高速化に使用されます。一意性は強制しません。

例：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    INDEX idx_customer_id (customer_id)
);

4. FULLTEXTインデックス

  • CHAR、VARCHAR、TEXTカラムの全文検索に使用されます。
  • 大きなテキストフィールド内のキーワード検索を可能にします。
  • 最新のMySQLバージョンではInnoDBでサポートされています。古いMySQLインストールでは異なる制限がある場合があるため、設計する前に正確なバージョンを確認してください。

例：

CREATE TABLE articles (
    article_id INT PRIMARY KEY,
    title VARCHAR(255),
    body TEXT,
    FULLTEXT (title, body)
);

5. SPATIALインデックス

  • 点、線、ポリゴンなどの空間データ型のインデックスに使用されます。
  • 動作と要件はMySQLのバージョンとストレージエンジンによって異なります。実行予定の正確な空間クエリをテストしてください。空間インデックスがすべてのGISスタイルの述語に役立つとは想定しないでください。

6. HASHインデックス（限定的な使用）

  • MySQLのMEMORYストレージエンジンはHASHインデックスをサポートしています。等価ルックアップ用に構築されており、範囲スキャンやソートには適していません。
  • ほとんどの一般的なシナリオでは汎用のインデックスタイプではありません。

インデックスの作成と管理

インデックスの作成方法

インデックスは、テーブル作成時または既存のテーブルを変更して作成できます。

1. テーブル作成時：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    hire_date DATE,
    INDEX idx_department (department_id),
    INDEX idx_hire_date (hire_date)
);

2. 既存のテーブルを変更：

-- 単一カラムインデックスを追加
ALTER TABLE customers
ADD INDEX idx_email (email);

-- ユニークインデックスを追加
ALTER TABLE users
ADD UNIQUE INDEX uidx_username (username);

-- 複数カラム（複合）インデックスを追加
ALTER TABLE orders
ADD INDEX idx_customer_date (customer_id, order_date);

インデックスの削除方法

インデックスが不要になった場合や、パフォーマンスに悪影響を及ぼしている場合（書き込み時など）は、削除できます。

-- 標準インデックスを削除
ALTER TABLE customers
DROP INDEX idx_email;

-- ユニークインデックスを削除
ALTER TABLE users
DROP INDEX uidx_username;

複数カラム（複合）インデックス

複合インデックスは2つ以上のカラムに作成されます。複合インデックス内のカラムの順序は非常に重要です。

  • (col1, col2)の複合インデックスは、col1のみ、またはcol1とcol2の両方でフィルタリングするクエリに使用できます。
  • 一般的に、col2のみでフィルタリングするクエリには使用されません。

例：

(customer_id, order_date)のインデックスを考えます。このインデックスは次のようなクエリに最も効果的です：

SELECT * FROM orders WHERE customer_id = 123;
SELECT * FROM orders WHERE customer_id = 123 AND order_date = '2023-10-27';
SELECT * FROM orders WHERE customer_id = 123 ORDER BY order_date;

SELECT * FROM orders WHERE order_date = '2023-10-27';にはあまり役立たないかもしれません。

MySQLインデックスのベストプラクティス

1. WHERE、JOIN、ORDER BY句で使用されるカラムにインデックスを付ける

これらは、インデックスがパフォーマンスに大きなメリットをもたらす最も一般的な場所です。

  • WHERE句： フィルタ条件が主な使用例です。
  • JOIN条件： JOIN文のON句で使用されるカラムにインデックスを付けると、テーブル結合が劇的に高速化されます。
  • ORDER BYおよびGROUP BY句： インデックスはMySQLがソート操作を回避するのに役立ちます。

2. 複合インデックスを賢く使用する

  • 順序が重要： クエリの形状に一致するカラムを最初に配置します。等価フィルターは通常、範囲フィルターの前に来ます。順序付けに使用されるカラムは、フィルタリングカラムの後に役立ちます。
  • 実際のクエリがそれでフィルタリングしない場合、最も選択性の高いカラムを盲目的に最初に配置しないでください。(status, created_at)のインデックスは、statusのカーディナリティが低くても、アクセスパターンに一致するため、WHERE status = 'paid' ORDER BY created_at DESC LIMIT 50に優れています。

3. 前後にEXPLAINを使用する

希望だけでインデックスを判断しないでください。EXPLAINを実行し、ステージングまたはメンテナンスセーフな環境でインデックスを追加し、再度EXPLAINを実行します。

EXPLAIN
SELECT order_id, total, created_at
FROM orders
WHERE customer_id = 123
  AND created_at >= '2025-01-01'
ORDER BY created_at DESC
LIMIT 20;

type、key、rows、Extraを確認します。keyがNULLの場合、MySQLはインデックスを選択しませんでした。rowsがまだテーブルサイズに近い場合、インデックスはこのクエリに対して十分に選択的でない可能性があります。ExtraにUsing filesortと表示されても、自動的に悪いわけではありませんが、MySQLが選択したインデックスから要求された順序で行を返せなかったことを示します。

MySQL 8.0.18以降では、EXPLAIN ANALYZEがさらに便利です。クエリを実行し、実際のタイミングと行数を報告するためです：

EXPLAIN ANALYZE
SELECT order_id, total
FROM orders
WHERE customer_id = 123
ORDER BY created_at DESC
LIMIT 20;

ステートメントを実行するため、本番システムでは注意して使用してください。

4. テーブルではなくワークフローを中心にインデックスを構築する

最近の失敗した支払いを一覧表示する管理画面を想像してください：

SELECT id, customer_id, failure_code, created_at
FROM payments
WHERE status = 'failed'
ORDER BY created_at DESC
LIMIT 100;

statusのみのインデックスでは、MySQLが多数の失敗した行をソートする必要があるかもしれません。(status, created_at)のインデックスは通常、より良い一致です。MySQLは失敗した行を見つけて時間順に読み取ることができるからです。クエリがインデックス内のカラムのみを返す場合、カバリングインデックスを検討できます：

CREATE INDEX idx_payments_status_created_cover
ON payments (status, created_at, id, customer_id, failure_code);

これは高速ですが、無料ではありません。インデックスはより広く、より多くのストレージを消費し、書き込みごとにコストがかかります。私は、追加のメンテナンスコストを正当化するほど頻繁に実行されるホットクエリにのみカバリングインデックスを使用します。

5. 正しく見えて何もしないインデックスに注意する

よくある落とし穴：

  • 関数がインデックス値を隠す：WHERE DATE(created_at) = '2025-01-01'。
  • 先頭のワイルドカードが通常のB-tree使用を妨げる：WHERE email LIKE '%@example.com'。
  • 型の不一致が変換を強制する：整数カラムを引用符で囲まれた文字列と比較しても機能する場合がありますが、実際のスキーマでは計画を混乱させる可能性があります。
  • インデックスがクエリに対して間違ったカラムで始まる：(created_at, customer_id)は(customer_id, created_at)と同じではありません。

可能な場合は述語を書き換えます：

WHERE created_at >= '2025-01-01'
  AND created_at <  '2025-01-02'

この形式により、MySQLはcreated_atで範囲スキャンを使用できます。

6. 冗長で未使用のインデックスを慎重に削除する

過剰なインデックスは静かなパフォーマンス問題です。追加のセカンダリインデックスはそれぞれ、INSERT、UPDATE、DELETE中に維持する必要があります。書き込みが多いテーブルでは、5つの未使用インデックスが1つの遅いSELECTよりも重要になることがあります。

MySQL 5.7および8.0では、sysスキーマが候補を見つけるのに役立ちます：

SELECT *
FROM sys.schema_unused_indexes
WHERE object_schema = 'app';

その出力をリードとして扱い、コマンドとして扱わないでください。インデックスは、サーバーが最近再起動した、月次レポートがまだ実行されていない、ステージングのトラフィックが本番と一致しないなどの理由で未使用に見える場合があります。インデックスを削除する前に、デプロイ履歴、スケジュールされたジョブ、外部キー要件を確認してください。

7. 大規模テーブルに安全にインデックスを追加する

小規模テーブルでは、ALTER TABLE ... ADD INDEXは通常問題ありません。大規模な本番テーブルでは、実際の操作になる可能性があります。MySQLのバージョン、ストレージエンジン、テーブル定義、正確なDDLによっては、インデックスの追加はオンラインDDLを使用する場合もあれば、メタデータロック、一時スペース、redo生成、レプリケーションラグを通じて圧力を生み出す場合もあります。

大きなインデックスを追加する前に、以下を確認してください：

  • テーブルと既存のインデックスのサイズ。
  • レプリカが追いつけるかどうか。
  • MySQLのバージョンが期待するオンラインアルゴリズムをサポートしているかどうか。
  • 長時間のトランザクションがDDLをブロックした場合に、アプリケーションがメタデータロックに耐えられるかどうか。

重要なシステムでは、pt-online-schema-changeやgh-ostなどの移行ツールを使用するか、トラフィックの少ない時間帯にDDLをスケジュールしてください。

実践的なインデックスレビュールーチン

遅いMySQLクエリをレビューするときは、次の順序を使用します：

  1. 実際のバインド値を持つ正確なSQLをキャプチャします。
  2. EXPLAINを実行し、安全な場合はEXPLAIN ANALYZEを実行します。
  3. 既存のインデックスがWHERE、JOIN、ORDER BYパターンに一致するか確認します。
  4. ステージングで最小限の有用な複合インデックスを追加します。
  5. 検査された行数、クエリ時間、書き込みへの影響を比較します。
  6. 慎重にロールアウトし、スロークエリログとレプリケーションラグを監視します。

このルーチンにより、インデックス設計は正直になります。インデックスを収集しようとしているのではありません。アプリケーションが実際に実行するクエリに対してMySQLが行う作業量を減らそうとしているのです。