うまいぼうぶろぐ

linuxとhttpdとperlのメモ

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 になった。