インデックスとは、
データベースに作成できる「索引」のことで、検索を高速化するための仕組み。
テーブル全体を全件検索(フルスキャン)することなく、必要なデータに直接アクセスできる。 書籍の最後についている索引のようなもの。
インデックスは列(カラム)に対して作られる。
インデックスの操作(作成・削除など)は、DDL文の命令に含まれる。
Primary Keyは自動的にインデックスが作成される。
- 複合インデックスとは、複数の列を１つのインデックスとしてまとめたもの。
作成(CREATE)
CREATE INDEX インデックス名 ON テーブル名(列名)
CREATE INDEX idx_customers_name ON customers(name);
☀︎customersテーブルのnameカラムにインデックスを作成している。
変更(ALTER)
削除(DROP)
ALTER TABLE テーブル名 DROP インデックス名
ALTER TABLE customers DROP INDEX idx_customers_name;
☀︎idx_customers_nameという名前のインデックスを削除している。
- 削除もテーブル自体の構造を変更しているので
ALTER TABLEを使う。
追加(ADD INDEX)
ALTER TABLE テーブル名 ADD INDEX インデックス名(カラム名)
ALTER TABLE customers ADD INDEX idx_customers_created_date(created_date);
☀︎customersテーブルのcreated_dateカラムにインデックスを追加(作成)している。
参照(SHOW)
SHOW INDEX FROM データベース名.テーブル名
データベース名は省略可能
SHOW INDEX FROM db_01.customers;
☀︎customersテーブルにあるインデックスを確認する。
高速化の効果を測る(EXPLAIN)
EXPLAIN PLAN文でも可能
EXPLAIN SELECT * FROM customers WHERE created_date IS NULL;
このクエリの実行計画の結果を見て、どのカラムにインデックスを張るべきか検討する。
効率の良いインデックスの張り方
インデックスによって検索は高速になるが、
データを更新するときに追加の処理コストの発生や、ディスク容量を消費してしまう。
- 検索や並び替えでよく使う列
- JOINのON句でよく使うForeign key(外部キー)の列
- データの値の種類が多い(ガーディナリティが高い)列
例①WHERE句での絞り込み条件で使う列
SELECT * FROM customers WHERE name LIKE ’John’;
- 完全一致検索や前方一致検索
’John%’で効果が高く、
部分一致検索’%John%’や後方一致検索’%John’では、インデックスが使えない。
例②ORDER BY句の並び替えで使う列
SELECT * FROM customers ORDER BY created_date;
例③JOIN結合で使う列
SELECT * FROM customers JOIN id ON customers.id = orders.customer_id;
☀︎ordersテーブルのcustomer_idカラムにインデックスを張るといい。
- 結合処理は内部で並び替えをしている。
