EXPLAIN SELECT tc.phone FROM teacher_contact tc
WHERE tcid = (
SELECT tcid FROM teacher t WHERE t.tid = (
SELECT c.tid FROM course c WHERE c.cname = 'java'
)
);
-- 查询 java 课程的老师手机号
SQL执行结果:
可以看到执行计划中的id值不同,在id 值不同的时候,会先查询 id 值大的(先大后小)。
查询顺序:course c——teacher t——teacher_contact tc。
子查询只能以这种方式进行,只有拿到内层的结果之后才能进行外层的查询。
执行计划中id相同
EXPLAIN SELECT
t.tname,
c.cname,
tc.phone
FROM
teacher t,
course c,
teacher_contact tc
WHERE
t.tid = c.tid
AND t.tcid = tc.tcid
AND (c.cid = 2 OR tc.tcid = 3);
-- 查询课程 ID 为 2,或者联系表 ID 为 3 的老师
system 是 const 的一种特例,只有一行满足条件。例如:只有一条数据的系统表。不过在我们开发应用时基本不会去查系统表。
eq_ref
通常出现在多表的 join 查询,表示对于前表的每一个结果,,都只能匹配到后表的一行结果。
一般是唯一性索引的查询(UNIQUE 或 PRIMARY KEY)。
eq_ref 是除 const 之外最好的访问类型。
DELETE FROM teacher where tid in (4,5,6);
commit; -- 删除多余的三条记录
ALTER TABLE teacher_contact ADD PRIMARY KEY(tcid);
-- 为 teacher_contact 表的 tcid 创建主键索引。
ALTER TABLE teacher ADD INDEX idx_tcid (tcid);
-- 为 teacher 表的 tcid 创建普通索引。
以上三种 system,const,eq_ref,都是可遇而不可求的,基本上很难优化到这个状态。
ref
查询用到了非唯一性索引,或者关联操作只使用了索引的最左前缀。
例如:使用 tcid 上的普通索引查询:
range
索引范围扫描。
如果 where 后面是条件是索引,且范围为 between and 或 <或 > 或 >= 或 <=或 in 这些,type 类型就为 range。
注意 没有索引type就是ALL 全表扫描了。
index
Full Index Scan,查询全部索引中的数据(比不走索引要快)。
all
Full Table Scan,如果没有索引或者没有用到索引,type 就是 ALL。代表全表扫描。
NULL
不用访问表或者索引就能得到结果,例如:
EXPLAIN select 1 from dual where 1=1;
小结:
一般来说,需要保证查询至少达到 range 级别,最好能达到 ref。
ALL(全表扫描)和 index(查询全部索引)都是需要优化的。
4) possible_key、key
possible_key : 可能用到的索引
key : 实际用到的索引
如果是 NULL 就代表没有用到索引。
possible_key 可以有一个或者多个,可能用到索引并不代表一定用到索引。
possible_key 为空,key 可能有值吗?
ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);--为name和phone建立联合索引
explain select phone from user_innodb where phone='126'; --以索引为条件查询建立了索引的字段
因此,是有可能的,这是使用到了覆盖索引的情况(无需回表)。
如果优化时,通过分析发现没有用到索引,就要检查 SQL 或者创建索引。
5)key_len
索引的长度(使用的字节数)。跟索引字段的类型、长度有关。
6)rows
MySQL 认为扫描多少行才能返回请求的数据,是一个预估值。一般来说行数越少越好。
7)filtered
这个字段表示存储引擎返回的数据在 server 层过滤后,剩下多少满足查询的记录数量的比例,它是一个百分比。
8)ref
使用哪个列或者常数和索引一起从表中筛选数据。
9)Extra
执行计划给出的额外的信息说明。
using index:
用到了覆盖索引,不需要回表。
using where:
使用了 where 过滤,表示存储引擎返回的记录并不是所有的都满足查询条件,
需要在 server 层进行过滤(跟是否使用索引没有关系)。
Using index condition(索引条件下推):使用到了第二篇中提到的索引条件下推
using filesort:不能使用索引来排序,用到了额外的排序(跟磁盘或文件没有关系)。需要优化。
using temporary :用到了临时表。
比如:
distinct 非索引列:
EXPLAIN select DISTINCT(tid) from teacher t;
group by 非索引列:
EXPLAIN select tname from teacher group by tname;
使用 join 的时候,group 任意列:
EXPLAIN select t.tid from teacher t join course c on t.tid = c.tid group by t.tid;
总结一下,模拟优化器执行 SQL 查询语句的过程,来知道 MySQL 是怎么处理一条 SQL 语句的。