Zenn
🐬

インデックスの効き方について

2024/04/04に公開
MySQL
tech

準備

顧客管理テーブル

カラム名 型 説明 主キー 外部キー
顧客ID int 顧客を一意に識別する番号 PRIMARY KEY
氏名 varchar(255) 顧客の氏名
性別 char(1) 顧客の性別
生年月日 date 顧客の生年月日
住所 varchar(255) 顧客の住所
電話番号 varchar(13) 顧客の電話番号
メールアドレス varchar(255) 顧客のメールアドレス

このテーブルは、顧客情報を管理するために使用されます。

  • 顧客IDは、顧客を一意に識別するために使用されます。
  • 氏名は、顧客の氏名を格納するために使用されます。
  • 性別は、顧客の性別を格納するために使用されます。
  • 生年月日は、顧客の生年月日を格納するために使用されます。
  • 住所は、顧客の住所を格納するために使用されます。
  • 電話番号は、顧客の電話番号を格納するために使用されます。
  • メールアドレスは、顧客のメールアドレスを格納するために使用されます。

顧客ID、性別、生年月日をひとまとめにした複合インデックス

CREATE INDEX idx_customer_info ON customer (customer_id, gender, birth_date);

解説

  • CREATE INDEX: インデックスを作成するコマンド
  • idx_customer_info: インデックス名。任意の名前を指定できます。
  • ON customer: インデックスを作成するテーブル名
  • customer_id, gender, birth_date: インデックスを作成するカラム名のリスト。順番通りにインデックスが作成されます。

その他のオプション

  • USING: インデックスの種類を指定できます。デフォルトは BTREE です。
  • ORDER BY: インデックスの順序を指定できます。デフォルトはカラムの順序です。

例

CREATE INDEX idx_customer_info ON customer (customer_id, gender, birth_date) USING BTREE;

CREATE INDEX idx_customer_info ON customer (customer_id, birth_date, gender) ORDER BY birth_date DESC;

インデックスが効くSELECT文

複合インデックス idx_customer_info でインデックスが効くように、以下のSELECT文を書くことができます。

SELECT * FROM customer
WHERE customer_id = 123 AND gender = 'M' AND birth_date BETWEEN '1990-01-01' AND '2000-12-31';

解説

  • WHERE: 絞り込み条件を指定する句
  • customer_id = 123: 顧客IDが123の顧客を検索
  • gender = 'M': 性別が男性の顧客を検索
  • birth_date BETWEEN '1990-01-01' AND '2000-12-31': 生年月日が1990年1月1日から2000年12月31日までの顧客を検索

インデックスが効く理由

上記のSELECT文では、WHERE句で指定している条件は、すべて複合インデックス idx_customer_info に含まれるカラムです。

そのため、データベースはインデックスを使用して、効率的に検索を実行することができます。

その他のSELECT文

以下のSELECT文でも、インデックスが効きます。

SELECT * FROM customer
WHERE customer_id = 123 AND gender = 'M'
ORDER BY birth_date;

SELECT customer_id, gender FROM customer
WHERE birth_date BETWEEN '1990-01-01' AND '2000-12-31';

インデックスの効き方について

1. カラムの順番

インデックスに含まれるカラムの順番が異なっていても、インデックスは効きます。

ただし、インデックスの効き方には違いがあります。

  • 先頭一致：インデックスの先頭にあるカラムで条件を指定すると、インデックスが最も効率的に効きます。
  • 範囲検索：インデックスの途中にあるカラムで条件を指定すると、インデックスは部分的にしか効きません。
  • 末尾一致：インデックスの末尾にあるカラムで条件を指定すると、インデックスはほとんど効きません。

例

-- 先頭一致
SELECT * FROM customer
WHERE customer_id = 123 AND gender = 'M';

-- 範囲検索
SELECT * FROM customer
WHERE birth_date BETWEEN '1990-01-01' AND '2000-12-31';

-- 末尾一致
SELECT * FROM customer
WHERE gender = 'M' AND customer_id = 123;

上記の例では、最初のSELECT文はインデックスが最も効率的に効きます。2番目のSELECT文はインデックスは部分的にしか効きません。3番目のSELECT文はインデックスはほとんど効きません。

2. 複数のWHERE条件

インデックスに含まれるカラムのうち、どれか1つのWHERE条件でもインデックスは効きます。

ただし、すべてのWHERE条件がインデックスに含まれるカラムで指定されている場合の方が、インデックスはより効率的に効きます。

例

-- すべてのWHERE条件がインデックスに含まれるカラムで指定されている
SELECT * FROM customer
WHERE customer_id = 123 AND gender = 'M';

-- 一部のWHERE条件のみがインデックスに含まれるカラムで指定されている
SELECT * FROM customer
WHERE customer_id = 123 AND birth_date BETWEEN '1990-01-01' AND '2000-12-31';

上記の例では、最初のSELECT文の方がインデックスはより効率的に効きます。

関連記事

MySQLのインデックスとは？

https://zenn.dev/btc/articles/240404_mysql_index

Discussion