概要
MySQLのインデックスは、データベース内のテーブルにおいて、特定の列を高速に検索するために使用されます。
インデックスを使用することで、テーブル内のレコードの検索速度が向上します。
逆にインデックスがないとMySQLは関連するレコードを見つけるために、先頭行から始めてテーブル全件読み取ることになりレコード数によっては実行速度がかなり遅くなります。(フルテーブルスキャン)
構文
例として、以下のようなユーザーテーブルを考えてみます。
CREATE TABLE users (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
age INT(11) NOT NULL,
PRIMARY KEY (id)
);
このテーブルには、ユーザーのID、名前、メールアドレス、年齢が含まれています。ここで、例えば、ユーザーの名前を使って検索する場合を考えてみましょう。
SELECT * FROM users WHERE name = 'John';
この検索クエリは、テーブル内のすべてのレコードをスキャンして、名前が"John"であるレコードを検索します。しかし、テーブルに多くのレコードがある場合、この検索は非常に遅くなります。
この問題を解決するために、名前列にインデックスを作成することができます。
インデックスは、テーブル内の各レコードに対応する、名前の値を持つインデックスエントリを作成します。これにより、MySQLはインデックスを使用して、名前が"John"であるレコードを迅速に検索できます。
ALTER TABLE users ADD INDEX name_index (name);
このインデックスを作成した後、同じ検索クエリを実行すると、MySQLはインデックスを使用してレコードを検索します。
また、複数の列に対してインデックスを作成することもできます。例えば、名前と年齢列に対して複合インデックスを作成することができます。
CREATE INDEX ON users (name, age);
この複合インデックスを使用することで、名前が"John"であるかつ年齢が25歳以上であるレコードを検索する場合、MySQLはインデックスを使用して効率的にレコードを検索できます。
インデックスの種類
MySQLには、いくつかのインデックスの種類があります。
以下によく使用されるインデックスの種類を一部説明します。
- B-Treeインデックス（基本コレ）
B-Treeインデックスは、MySQLで最も一般的に使用されるインデックスです。B-Treeインデックスは、データを木構造で管理し、各ノードが複数の子ノードを持つことができます。B-Treeインデックスは、検索、範囲検索、ソートなどの操作を高速に実行できます。 - Hashインデックス
Hashインデックスは、ハッシュ値を使用してデータを管理するインデックスです。Hashインデックスは、完全一致検索や=や<=>といった単純な比較の場合は、速いですが文字列検索などは、B-Tree Indexの構造上前方一致の方が速度が出やすいです。
インデックスの利点と欠点
インデックスは、データベースのパフォーマンスを改善するために非常に重要ですが、利点と同時に欠点もあります。
メリット
- 検索速度が向上する：インデックスを使用することで、テーブル内のデータを高速に検索できるようになります。
- データの一貫性が保たれる：UNIQUEインデックスを使用することで、データの一貫性が保たれます。
デメリット
- ストレージの使用量が増加する：インデックスを作成することで、ストレージの使用量が増加します。
- インデックスの更新に時間がかかる：テーブルにデータを追加、更新、削除する場合、インデックスの更新に時間がかかることがあります。
インデックス対象となるカラムの候補
- WHEREやJOINで使用される
- レコード件数が1万件を超えるテーブル
- 明確な闘値ではないが、レコード数の多さによってインデックスを検討
- 外部キー
- MySQLの場合、外部キーを作成すると自動的にインデックスが作成
- カーディナリティ(カラムの数)が20以上目安
参考
https://zenn.dev/hk_206/articles/ec5f4e347caff4
https://qiita.com/C058/items/1c9c57f634ebf54d99bb
https://www.javadrive.jp/mysql/index/index1.html
https://qiita.com/kodai-saito/items/541e4fe46c2d3edc9634
https://zenn.dev/hk_206/articles/ec5f4e347caff4
https://yut.hatenablog.com/entry/20110418/1303083112
https://qiita.com/K-jun/items/a86a3829cf796b6d5ad8