準備#

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

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

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

インデックスが効くSELECT文#

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

1
SELECT * FROM customer
2
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文でも、インデックスが効きます。

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

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

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

1. カラムの順番

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

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

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

例

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

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

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

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

2. 複数のWHERE条件

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

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

例

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

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

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

関連記事#

MySQLのインデックスとは？