mysql の複合インデックス復習
mysqlは1つのSQLで、1つのテーブルには1つのインデックスしか利用されない。
なので、whereで2つのカラムを条件に指定する場合それぞれのカラムにインデックスを張っていたとしても、(where id = 0 and name = 'hoge' のような) どちらか1つしか利用されない。where区で複数の条件を指定し、インデックスを使う場合は複合インデックスを使う。
また、利用されるindexはmysqlが最適だと判断したindexを利用する。が、たまに誤ることがあるので、明示的に指定する場合はuse index(index_name) を使う。
create table, index
mysql> create table hoge ( id int, name char(8), hoge varchar(32), index idx_id(id), index idx_name(name) ) ENGINE=InnoDB;
insert
mysql> insert into hoge values (0, 'hoge', 'abcde'); Query OK, 1 row affected (0.03 sec) mysql> insert into hoge values (0, 'fuga', 'abcde'); Query OK, 1 row affected (0.03 sec) mysql> insert into hoge values (1, 'foo', 'abcde'); Query OK, 1 row affected (0.03 sec) mysql> insert into hoge values (1, 'bar', 'abcde'); Query OK, 1 row affected (0.03 sec) mysql> insert into hoge values (2, 'baz', 'abcde'); Query OK, 1 row affected (0.03 sec)
select
idとnameをwhereで指定
mysql> explain select * from hoge where id = 0 and name = 'hoge'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: hoge type: ref possible_keys: idx_id,idx_name key: idx_name key_len: 25 ref: const rows: 1 Extra: Using where 1 row in set (0.01 sec)
possible_keysに2つのindexが表示されたが、実際に使ったのはidx_nameだけ。idとname 両方のindexを使う場合、複合インデックスを作成する。
複合インデックス作成
mysql> create index idx_id_and_name on hoge (id, name);
再度select
mysql> explain select * from hoge where id = 0 and name = 'hoge'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: hoge type: ref possible_keys: idx_id,idx_name,idx_id_and_name key: idx_name key_len: 25 ref: const rows: 1 Extra: Using where 1 row in set (0.00 sec)
possible_keysが3つに増えたけど、やっぱり利用されたのはidx_name。use indexで明示的に指定する。
mysql> explain select * from hoge use index(idx_id_and_name) where id = 0 and name = 'hoge'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: hoge type: ref possible_keys: idx_id_and_name key: idx_id_and_name key_len: 30 ref: const,const rows: 1 Extra: Using index condition 1 row in set (0.00 sec)
use indexで指定したので、possible_keys, 実際に仕様したkey ともidx_id_and_name になった。