mysql index索引

All /null :  full table scan

const / primary : primary key / unique key

ref / key_name : 非primary key / unique key，可能包含多行

range/ possible_keys : 组合索引，找到A，B索引失效，可能多行

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	opponent_stages	range	opponent_id,idx_opponent_stage	opponent_id	8	NULL	12	Using where; Using filesort

组合索引里有了的，不需要单独建索引

explain select * from opponent_stages where opponent_id = 137616

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	opponent_stages	ref	opponent_id,idx_opponent_stage,opponent_id_1	opponent_id	8	const	1

group by 会创建临时表，如果索引没起作用的话

explain select count(rank) from opponent_stages where opponent_id in(137621,	137624,	137620, 137614,	137618, 137619,	137615,	137622,	137617, 137616,	137623, 137613,	137621,	137624, 137614,	137619, 137615,	137622, 137616,	137613, 137615,	137616, 137621,	137614, 137621,	137615, 137614,	137616) group by rank

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	opponent_stages	range	opponent_id,idx_opponent_stage,opponent_id_1	opponent_id	8	NULL	12	Using where; Using temporary; Using filesort

explain select * from opponent_stages where stage = 0 limit 10

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	opponent_stages	ALL	NULL	NULL	NULL	NULL	86576	Using where

stage是组合索引的后一个，单独查询是全表，加上order by id，变成primary，好神奇

explain select * from opponent_stages where stage = 0 order by id limit 10

  
  
id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra
1
SIMPLE
opponent_stages
index
NULL
PRIMARY
8
NULL
10
Using
 where