2018/3/19 初次分析explain
mysql执行计划分析主要从一下几个关键字入手:
1.id
id标识sql语句中表的执行顺序,id越大越先执行;如果id相同,按照从上到下的顺序执行;如果id为null,表示当列是一个结果集。
2.select_type
2.1 simple
simple 表示该表上对应的操作是一个简单的select,没有union、子查询等。
EXPLAIN SELECT t2.`id`, t4.company, t4.`position_name`, t4.`status` FROM user_contacts t1 INNER JOIN USER t2 ON t1.telephone = t2.telephone LEFT JOIN user_info t3 ON t2.id = t3.uid LEFT JOIN user_info_authentication_record t4 ON t3.uid = t4.`uid` WHERE t1.uid = 129292 AND t1.status = 2 AND t1.is_delete = 0 ORDER BY t1.create_date DESC |
2.2 primary
包含union操作或包含子查询的select 语句。其中union 关键字前的select 均为primary,包含子查询的外部查询为primary。
EXPLAIN SELECT * FROM USER t1 WHERE t1.id = (SELECT uid FROM circle_members WHERE uid > 4 AND is_delete = 0 LIMIT 0) |
EXPLAIN SELECT t1.real_name FROM user_info t1 LEFT JOIN circle_members t2 ON t1.uid = t2.uid WHERE t2.uid > 100 UNION SELECT t3.real_name FROM user_info t3 LEFT JOIN circle_members t4 ON t3.uid = t4.uid WHERE t4.uid < 20 |
2.3 union
在一条sql中union关键字后面的查询为union(当然,如果union后面的查询包含子查询的话子查询也可以是其他类型)
EXPLAIN SELECT t1.real_name FROM user_info t1 LEFT JOIN circle_members t2 ON t1.uid = t2.uid WHERE t2.uid > 100 UNION SELECT t3.real_name FROM user_info t3 LEFT JOIN circle_members t4 ON t3.uid = t4.uid WHERE t4.uid < (SELECT id FROM USER t5 WHERE t5.id = 6) |
2.4 dependent union
和union关键字作用相同,只是dependent union 会受到外部查询的影响。
2.5 union result
union的结果集。union result出现在union 语句中,它不需要参与查询过程,只是表示union 的查询结果,因此union result 没有id。(可参考union 关键字)
2.6 subquery
subquery 常出现在有子查询的select中。
什么是子查询? 子查询允许把一个查询嵌套在另一个查询中。 子查询又叫内部查询,相对于内查询,包含内查询的select 就叫外部查询。 子查询可以包含普通select查询可以包含的任何自居,但是外部查询必须是select、insert、update、delete、set或 者do之一。 子查询可以出现在select中、from中、where中、group by中、order by中。(后两个意义不大) |
EXPLAIN SELECT * FROM USER t1 WHERE t1.id = (SELECT uid FROM circle_members WHERE uid > 4 AND is_delete = 0 LIMIT 0) |
对比两外两种子查询的情况:
EXPLAIN SELECT * FROM (SELECT * FROM USER ) AS u |
EXPLAIN SELECT * FROM USER WHERE id IN (SELECT uid FROM circle_members WHERE id = 3 AND is_delete = 0) |
这两种情况同样是包含子查询语句,但是explain执行结果显示非subquey。(具体原因后续补充)
2.8 dependent subquey
dependent subquery 和subquery相同,只是dependent subquery 查询结果受到外部查询的影响。
EXPLAIN SELECT t2.`id`, t4.company, t4.`position_name`, t4.`status`, (SELECT id FROM circle t6 WHERE t6.create_user = t2.id) AS tmp FROM user_contacts t1 INNER JOIN USER t2 ON t1.telephone = t2.telephone LEFT JOIN (SELECT * FROM user_info ) t3 ON t2.id = t3.uid LEFT JOIN user_info_authentication_record t4 ON t3.uid = t4.`uid` WHERE t1.uid = (SELECT t5.id FROM USER t5 WHERE t5.id = 8 LIMIT 1) AND t1.status = 2 AND t1.is_delete = 0 ORDER BY t1.create_date DESC |
2.9 devired
衍生的意思。
通常出现在子查询中,将子查询结果(虚表)作为查询依据。
EXPLAIN SELECT * FROM (SELECT * FROM USER ) AS u |
3.table
显示的是查询的表名,如果该表使用了别名,那么这里就显示别名;如果不涉及对数据库表的操作,显示为null;如果显示为尖括号括起来的形式,<derivedN>就表示着是个临时表,后面的N代表执行计划中的id,表示结果来自于id对应的查询产生;<union M N> 和<derived N> 类似,表示结果来自于union 查询的id为M 和N 的结果集。
4.type
从好到差的顺序:
system,const,eq_ref,ref,fulltext,ref_or_null,unique_subquery,index_subquery,range,index_merge,index,ALL
除了all之外其他的type都可以使用到索引;除了index_merge 之外其他的type 都只能使用一个索引。
4.1systm
表中只有一行数据或者是空表,并且表的引擎类型是myisam或memory;如果表的引擎是innodb,这种情况下type值为all。
4.2 const
where条件中,当常量和主键索引或唯一索引进行比较时,type类型为const,前提是索引没有失效。(如果比较条件为where id > 3,会导致索引失效)
4.3 eq_ref
4.4 ref
4.5 fulltext
全文检索索引。我们使用的数据库索引通常都是b树索引,这里可以不过多研究。
4.6 ref_or_null
4.7 unique_subquery
4.8 index_subquery
4.9 range
4.10 index_merge
4.11 index
4.12 all
5. possible_keys
每张表执行查询时可能使用到的索引列
6.key
每张表执行查询时使用到的索引,没有使用索引时显示为null。当select_type 为index_merge 时该列中会出现多个索引。
7.key_len
该列表示每张表执行时使用到的索引的长度,长度越小越好。
8.ref
如果是使用的常数等值查询,这里会显示const,如果是连接查询,被驱动表的执行计划这里会显示驱动表的关联字段,如果是条件使用了表达式或者函数,或者条件列发生了内部隐式转换,这里可能显示为func
9.rows
表示执行计划中估计扫描的行数,是个估计值。
10.extra
10.1 using filesort
mysql中排序有两种方式:order by排序和使用索引。
通常情况下是排序时没有通过索引进行排序会出现这种情况,常见于“order by 排序字段”,其中排序字段没有使用索引
注意:mysql排序是在内存中完成的。
10.2 using tempory
表明使用了临时表来存储临时结果,通常情况下是order by 或group by造成的。
10.3 using index
出现using index 是好征兆,说明mysql直接从索引中过滤不需要的记录并返回命中的结果,这是在mysql服务层完成的,不需要再回 表再查询记录。