Mysql 执行计划分析（Explain关键字使用）

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服务层完成的，不需要再回 表再查询记录。