MySQL indexについて
現場のログにスロークエリが出ていたので、indexを設定することで解消できるか検証することになりました。
その際に、indexについて調べたことをまとめました。
indexとは
特定のカラムを持つレコードを高速に検索するためのデータ構造のこと
indexを設定することで、検索速度が向上
indexを設定するとどうなるか
indexを設定すると、そのカラムの値を元にした検索が高速になる
辞書で例えると、indexを設定することで、辞書の索引を引くことができるようになる
indexが設定されていない状態だと、全てのレコードを順番に見ていく必要があるため、検索に時間がかかる（フルテーブルスキャン）
indexの設定方法
indexを設定するには、CREATE文やALTER文で設定する
CREATE文でテーブルを作成する際に、indexを設定することができる
ALTER文で既存のテーブルにindexを追加することもできる
例）CREATE文でindexを設定する場合
CREATE TABLE テーブル名 (
カラム名1 データ型,
カラム名2 データ型,
...
INDEX インデックス名 (カラム名1)
);
例）ALTER文でindexを追加する場合
ALTER TABLE テーブル名 ADD INDEX インデックス名 (カラム名1);
indexのデメリット
indexを設定することで、検索速度が向上するが、デメリットもある
・indexを設定することで、データの挿入や更新、削除が遅くなる
-> indexを設定すると、indexの更新も行われるため、データの挿入や更新、削除が遅くなる
・indexを設定することで、データベースの容量が増える
-> indexはデータベースの容量を増やすため、データベースの容量が増える
indexを設定するカラムの選定方法
カーディナリティが高いカラムを選定する
カーディナリティが高いというのは、そのカラムの値の種類が多いことを指す
例）性別カラム
性別カラムは男性と女性の2つの値しか持たないため、カーディナリティが低い
そのため、性別カラムにindexを設定しても、検索速度が向上しない
例）都道府県カラム
都道府県カラムは47都道府県の値を持つため、カーディナリティが高い
そのため、都道府県カラムにindexを設定すると、検索速度が向上する
indexが効かない場合
indexが効かない場合もある
・カーディナリティが低いカラムにindexを設定した場合
・データ量が少ない場合
・検索条件にindexが使われない場合
・order byやgroup byなどの処理にindexが使われない場合
練習問題
問題1 (初級)
MySQLでインデックスを作成する主な目的は何ですか？ 3つ挙げてください。
問題2 (中級)
以下のテーブルとクエリにおいて、インデックスが効果的に使用されるのはどのクエリですか？ また、その理由も説明してください。
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(255),
department VARCHAR(255),
salary INT
);
-- クエリ1
SELECT * FROM employees WHERE id = 100;
-- クエリ2
SELECT * FROM employees WHERE name = '山田太郎';
-- クエリ3
SELECT * FROM employees WHERE department = '営業' AND salary > 500000;
問題3 (中級)
複合インデックス (last_name, first_name) があるテーブルに対して、以下のクエリを実行した場合、インデックスは効果的に使用されますか？ 理由も説明してください。
SELECT * FROM employees WHERE last_name = '田中';
問題4 (上級)
B-Treeインデックスとハッシュインデックスのそれぞれの特徴と、どのような場合にどちらが適しているかを説明してください。
問題5 (難問)
インデックスを過剰に作成することのデメリットを3つ挙げて、それぞれ詳しく説明してください。
解答
問題1 (初級)
解答:
MySQLでインデックスを作成する主な目的は次の3つです。
データ検索の高速化: インデックスは、特定の条件に一致するデータを効率的に探し出すためのデータ構造です。インデックスを利用することで、テーブル全体をスキャンする必要がなくなり、検索処理が大幅に高速化されます。
テーブル結合の効率化: 外部キー制約で関連付けられたテーブルを結合する際、インデックスが利用されることで、結合処理の効率が向上します。
ソート処理の効率化: ORDER BY句で指定されたカラムにインデックスが存在する場合、インデックスを利用してソート処理を行うため、効率が向上します。
問題2 (中級)
解答:
インデックスが効果的に使用されるのはクエリ1です。
理由:
クエリ1は、プライマリキー id を検索条件としています。プライマリキーには自動的にインデックスが作成されるため、高速な検索が可能です。
クエリ2は、name カラムを検索条件としていますが、インデックスが存在しない場合は、テーブル全体をスキャンする必要があります。
クエリ3は、department と salary の複合条件で検索していますが、department にインデックスがない場合は、テーブル全体をスキャンする必要があります。
問題3 (中級)
解答:
はい、インデックスは効果的に使用されます。
理由:
複合インデックス (last_name, first_name) は、last_name で検索する際にも利用可能です。MySQLは、複合インデックスの左端部分 (この場合は last_name) のみで検索する場合でも、インデックスを利用して効率的な検索を行うことができます。
問題4 (上級)
解答:
B-Treeインデックス:
特徴: データをキーの値でソートして階層構造（木構造）で管理します。範囲検索や順序検索に優れています。
適している場合:
範囲検索 (WHERE column BETWEEN ... AND ...)
順序検索 (ORDER BY column)
等価検索 (WHERE column = ...)
ハッシュインデックス:
特徴: キーとデータの格納場所をハッシュ関数で計算して管理します。等価検索に非常に高速です。
適している場合:
等価検索 (WHERE column = ...)
どちらが適しているか:
範囲検索や順序検索が頻繁に行われる場合は、B-Treeインデックスが適しています。
等価検索のみが行われる場合は、ハッシュインデックスが適しています。
問題5 (難問)
解答:
インデックスを過剰に作成することのデメリットは次の3つです。
更新処理のオーバーヘッド: インデックスはデータの追加、更新、削除のたびに更新されるため、これらの処理に時間がかかるようになります。特に更新が多いテーブルでは、インデックスの更新処理がボトルネックになる可能性があります。
ディスク容量の増加: インデックスはデータとは別にディスク上に保存されるため、インデックスが増えるとディスク容量を消費します。特に大規模なテーブルでは、インデックスが大量のディスク容量を占めることがあります。
クエリオプティマイザの誤った選択: インデックスが多すぎると、クエリオプティマイザが最適な実行計画を選択できなくなることがあります。誤った実行計画が選択されると、クエリの実行速度が低下する可能性があります。
補足:
インデックスはデータベースのパフォーマンス向上に役立ちますが、適切に設計・管理することが重要です。インデックスを作成する際は、データの特性やクエリの傾向を分析し、本当に必要なインデックスだけを作成するように心がけましょう。