ITC Media

SQL

MySQLでパフォーマンス高めるindexを使おう｜コマンド例付き

（最終更新月：2023年11月）

✔当記事は以下のような疑問をお持ちの方におすすめです

「MySQLのインデックスって何でしょうか？」

「MySQLのインデックスの役割や見方を学びたい」

「MySQLのインデックスを使った効率的なデータベース操作方法を知りたい」

✔記事でまとめて伝える内容

  • MySQLのインデックスについての基礎
  • MySQLのインデックスの操作方法とその応用
  • MySQLのインデックスを利用した具体的な例

当記事では、MySQLのインデックスの基礎知識から、それを利用したデータベース操作の具体的な方法まで、詳しく解説します。

ぜひ最後までお読みください。

MySQLとは?

こちらでは、データベースの基本概念と、MySQLがなぜそんなに人気があるのかについて解説します。

MySQLを理解することで、データベース運用の知識が深まるでしょう。

  • データベースとMySQLの説明
  • MySQLの普及理由

データベースとMySQLの説明

データベースは、情報を整理して格納するためのシステム。

MySQLはその中でもリレーショナルデータベースとして知られるもののひとつです。

表形式でデータを管理し、SQL（Structured Query Language）という言語を使用してデータを操作します。

SELECT * FROM users WHERE age > 20;

上記は、20歳以上のユーザーを選択する簡単なSQLの例です。

MySQLの普及理由

MySQLはオープンソースであり、無料で利用できます。

高いパフォーマンス、信頼性、そして豊富な機能が提供されているため、多くの企業や個人に支持されるでしょう。

とくにWebアプリケーションのバックエンドとしての採用例が多いです。

インデックスとは何か？

こちらでは、インデックスの基本的な役割や、リアルワールドとデータベースでの索引の比較について解説します。

インデックスを理解することで、高速なデータ検索の背後にある仕組みを把握できるでしょう。

  • インデックスの目的と重要性
  • 索引: リアルワールドとデータベースの比較

インデックスの目的と重要性

インデックスは、データベースのテーブル内の特定の列に迅速にアクセスするためのデータ構造です。

これにより、大量のデータの中から必要な情報を高速に検索できます。

データベースのパフォーマンスを向上させるためには、適切なインデックスの設計と利用が不可欠です。

索引: リアルワールドとデータベースの比較

データベースのインデックスは本の索引に例えられます。

特定のトピックやキーワードを迅速に探すという目的が同じだからです。

初心者の方は、データベースのインデックスを本の索引と覚えておくと良いでしょう。

インデックスが効果的な列の性質

こちらでは、インデックスが特に効果的とされる列の特性や、具体的な使用例について解説します。

インデックスを正しく適用することで、データベースのパフォーマンスが向上するはずです。

  • データ量の多いテーブルでの効果性
  • 頻繁に参照される列での有効性
  • NULL値が多い列での利用

データ量の多いテーブルでの効果性

大量のデータを持つテーブルでは、インデックスがない場合、データ検索に時間がかかります。

インデックスを適切に使用すると、検索時間を大幅に短縮できるのです。

頻繁に参照される列での有効性

特定の列が頻繁にクエリの条件として使用される場合、その列にインデックスを設定すると、検索速度が向上します。

SELECT * FROM orders WHERE customer_id = 12345;

上記のようにcustomer_idが頻繁に参照される場合、この列にインデックスを設定すると効果的です。

NULL値が多い列での利用

インデックスは、NULL値を持つレコードを効果的に検索するためにも使用できます。

ただし、NULL値が極端に多い列でインデックスを使用する場合、適切な設計と最適化が必要です。

インデックスが効果的でない列の情報

こちらでは、インデックスの利用があまり効果的でない列の特性について解説します。

全列にインデックスを適用するのではなく、適切な場所に適用することが重要です。

値のカーディナリティ（ユニークな値の数）が非常に低い列、例えば性別のような列は、インデックスの適用があまり効果的ではありません。

また、頻繁に更新される列にインデックスを適用すると、インデックスの再構築が頻繁におこなわれ、パフォーマンスに影響が出る場合があります。

インデックスの種類と作成法

こちらでは、インデックスの種類と、それらのインデックスをどのように作成するのかについて詳しく見ていきましょう。

適切なインデックスの種類を選択することで、更なるパフォーマンス向上が期待できます。

  • インデックスの確認と作成
  • インデックスの削除
  • マルチカラムとユニークインデックスの説明

インデックスの確認と作成

MySQLでは、以下のSQLコマンドを使って現在設定されているインデックスを確認できます。

SHOW INDEX FROM テーブル名;

インデックスの作成は以下のようにおこないます。

CREATE INDEX index_name ON テーブル名 (列名);

例えば、usersテーブルのemail列にインデックスを追加する場合、以下のようにコマンドを実行します。

CREATE INDEX idx_email ON users (email);

インデックスの削除

インデックスを削除する場合は、以下のSQLコマンドを使用します。

DROP INDEX index_name ON テーブル名;

先ほど作成したidx_emailというインデックスをusersテーブルから削除する場合は、以下のようになります。

DROP INDEX idx_email ON users;

マルチカラムとユニークインデックスの説明

マルチカラムインデックスは、複数の列をひとつのインデックスにまとめられます。

これは、クエリで複数の列を組み合わせて検索条件として使用する場合に特に有効です。

CREATE INDEX idx_name_email ON users (name, email);

ユニークインデックスは、インデックスの値が一意であることを保証します。

これは、重複しないことが必要な列（例：ユーザーIDやメールアドレス）に適しています。

CREATE UNIQUE INDEX idx_user_email ON users (email);

これにより、同じメールアドレスを持つレコードを追加しようとすると、エラーが発生します。

インデックス使用時の具体的な条件

インデックス使用時の具体的な条件を見ていきましょう。

  • 使用される場合の条件
  • 使用されない場合の条件

使用される場合の条件

MySQLでは、クエリの最適化の際にインデックスが使用されるかどうかを判断します。

以下のような状況でインデックスが使用される傾向があります。

  • WHERE句で使用される列にインデックスが存在する。
  • 大量のデータの中から一部を効率的に抽出する際。
  • JOIN操作で結合条件に使われる列にインデックスが存在する。

例えば、次のようなクエリがある場合です。

SELECT * FROM users WHERE email = 'example@example.com';

email列にインデックスが存在すれば、そのインデックスが使用される可能性が高いです。

使用されない場合の条件

以下のような状況では、インデックスは使用されない傾向があります。

  • 使用される列のデータが非常に少ない場合（例: 男性/女性のような2つの値しか持たない場合）。
  • LIKE句でワイルドカードが前方に置かれた検索（例: %example）。
  • 列のデータを変更する関数を使った検索条件（例: UPPER(column_name)）。

例えば、次のようなクエリではインデックスが使用されない可能性が高いでしょう。

SELECT * FROM users WHERE LEFT(name, 3) = 'Mr.';

name列の最初の3文字を取得する関数LEFT()を使用しているため、インデックスは適用されにくいです。

インデックスの適用チェック

インデックスの適用するために、事前チェックする方法をご紹介します。

  • EXPLAINの活用
  • typeの確認と読み方

EXPLAINの活用

MySQLのEXPLAINコマンドを使用すると、クエリの実行計画を確認でき、どのインデックスが適用されるかを事前に調べることができます。

例として、先ほどのクエリにEXPLAINを付け加えて実行しましょう。

EXPLAIN SELECT * FROM users WHERE email = 'example@example.com';

インデックスの使用状況やテーブルの走査方法など、クエリの最適化情報を取得できます。

typeの確認と読み方

EXPLAINの結果の中で、type列は特に重要です。

この列がrefやrangeの場合、インデックスが適切に使用されている可能性が高いです。

逆に、ALLやindexの場合、テーブルフルスキャンが行われているか、不適切なインデックスが使用されている可能性が考えられます。

これにより、インデックスの最適化やクエリの調整が必要かどうかを判断する手助けとなります。

インデックスの最適化と注意点

インデックスの最適化と注意点をご覧いただきます。

  • B-Treeとハッシュインデックスとは
  • 注意するべきポイント

B-Treeとハッシュインデックスとは

MySQLでは、インデックスの背後にあるデータ構造としてB-Treeが主に使用されます。

これは、データの挿入、検索、削除の操作が効率的におこなわれることを保証するもの。

とくに、範囲検索や順序付けに適しています。

一方、ハッシュインデックスは、キーのハッシュ値に基づいてデータを格納・検索するためのデータ構造です。

等価検索に非常に高速ですが、範囲検索や順序付けには不適です。

具体的な使用例です。

-- B-Treeインデックスの活用例
SELECT * FROM users WHERE age BETWEEN 20 AND 30;

-- ハッシュインデックスの活用例（MEMORYストレージエンジンの場合）
SELECT * FROM sessions WHERE session_id = 'abc123';

注意するべきポイント

インデックスはデータベースのパフォーマンスを向上させる強力なツールですが、過度な使用は逆効果になることもあります。

以下は、インデックスを使用する際の注意点です。

  • 過度なインデックス：テーブルに多くのインデックスがあると、データの挿入や更新が遅くなる可能性があります。
  • インデックスのサイズ：大量のデータを持つカラムにインデックスを設定すると、インデックス自体のサイズが大きくなり、ディスクスペースの消費が増えます。
  • カーディナリティ：カーディナリティ（一意の値の数）が低いカラムにインデックスを設定することは、ほとんどの場合、あまり効果的ではありません。

まとめ

当記事では、MySQLのインデックスについて学習してきました。

インデックスは、大量のデータの中から必要な情報を迅速に取得するための強力なツールです。

しかし、それを適切に使用するためには、その背後の概念やデータ構造の理解も不可欠。

適切な知識と注意を持って、インデックスを効果的に使用して、データベースの性能を最大限に引き出しましょう。

関連記事