mysql 执行计划

前提 : 

首先创建两张表 : user 用户表, user_goods 物品表

---

执行计划详情

主要包含列 : 

• id
• select_type
• table
• partitions
• type
• possible_keys
• key
• key_len
• ref
• rows
• filtered
• Extra

接下来一一解析这些列

---

1. id

select 查询序列号

id相同时 执行顺序从上向下执行

id不同时 执行顺序由大到小

 id相同时 : 

user表左连接user_goods, 先查询user表, 再查询user_goods表

id不相同时 : 

包含子查询, id不相同, 先执行子查询,再执行主查询, 从图中可以看出 id是由2到1;

---

2. select_type

查询语句类型

SIMPLE (普通查询)

PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)

UNION(UNION中的第二个或后面的SELECT语句)

UNION RESULT(UNION的结果)

 DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)

SUBQUERY(子查询中的第一个SELECT)

DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询, 类似于相关子查询)

DERIVED(派生/衍生表的SELECT, FROM子句的子查询)

---

3. table

查询涉及的表或衍生表

---

4. partitions查询涉及到的分区

---

5. type 提供了判断查询是否高效的重要依据依据

通过 type 字段, 我们判断此次查询是全表扫描还是索引扫描

system: 表中只有一条数据，相当于系统表； 这个类型是特殊的 const 类型；

 

const: 主键或者唯一索引的常量查询，表格最多只有1行记录符合查询

主键 : 

唯一索引 :  给user表name字段添加唯一索引

删除user表name字段唯一索引

 eq_ref: 唯一索引扫描，对于每个索引键，表中只有一条记录与之对应；常用于主键或唯一索引扫描

未给user_goods表的user_id字段设置索引 : 

给user_goods表的user_id字段设置普通索引 : 

给user_goods表的user_id字段设置唯一索引 : 

 

ref：此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了最左前缀规则索引的查询(换句话说，连接不能基于键值选择单行,可能是多行)

range: 表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, , BETWEEN, IN() 操作中

未给 user_goods表的user_id字段设置索引 : 

给 user_goods表的user_id字段设置普通索引 : 

 给 user_goods表的user_id字段设置唯一索引 : 

q : 我们知道在sql优化方面, 当查询的值是一个连续的范围时, 尽量使用between而不是in, 大家有没有想过这是为什么?

数据库数据如下 : 

同样给user_id设置为普通索引 : 

 接下来查看in和between的执行计划 : 

 从上面得知 : between使用了索引, in没有使用索引

结论 : 当in()种的数据很大时，不走索引

还有一个情况 between也不适用索引 如下 : 

还么有搞清楚为什么 , 如果有知道的大佬请告知

between使用索引参考 : http://www.51testing.com/html/85/132585-860191.html

---

index: 扫描索引树 (最弱的索引)

使用场景 : 

• 查询的值即是索引 (聚簇索引, 不需要回表) Extra : using index

给user_id设置索引 : 

给user_id设置索引, 同时添加查询gname字段, 此时需要回表

---

All : 无索引使用 需要优化

---

 

6. possible_keys : 可能使用的索引列

7. key : 实际上使用的索引列

8. key_len : 表示索引中使用的字节数

9. ref：显示该表的索引字段关联了哪张表的哪个字段 (相关子查询等)

10. rows : 过滤了多少行

11. filtered : 命中率 (单位 %) : rows/ 总条数, 值越大越好

12. Extra : 包含不适合在其他列中显示但十分重要的额外信息

---

讨论 : 当我们使用a表左连接b表时, 是先查询a表还是b表呢 ? 

举例: 

添加 : where u.id = "1" 

结论 : 

当a表左连接b表时, 默认先查询a表, 当在where中加入b表的查询条件时, 先查询b表