使用explain可以得知查询SQL的执行计划,这里主要介绍从explain的每列,我们可以看出什么
explain出的type列的值罗列如下,查询性能从好到坏
system
const
eq_ref
ref
fulltext
ref_or_null
index_merge
unique_subquery
index_subquery
range
index
all
这里主要说说经常碰到的const,eq_ref,ref,index,all
const可以推出:const
is used when you compare all parts of a PRIMARY KEY
or UNIQUE
index to constant values;
eq_ref可以推出:It is used when all parts of an index are used by the join and the index is a PRIMARY KEY
or UNIQUE NOT NULL
index;
ref可以推出:ref
is used if the join uses only a leftmost prefix of the key or if the key is not a PRIMARY KEY
or UNIQUE
index (in other words, if the join cannot select a single row based on the key value).
index,不一定表示使用了索引,要看extra列是否出现Using index。
all,是全表扫描,没有走索引。
const,eq_ref,ref,index,all这几个里面一定走索引的是const,eq_ref,ref;可能走索引的是index,一定不走索引的是all。