Ⅰ、认识执行计划的每个字段
(root@localhost) [(none)]> desc select 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
1.1 id
-
单纯的join，id都是1，且从上到下
-
subquery、scala subquery都会使id递增
1.2 select type
-
simple
-
不使用union或者subquery的简单query
-
子查询被优化器打开，失效了
-
-
primary
-
使用union结合select时，第一个select type
-
subquery的query
-
-
union
-
使用union结合select除了第一个select type为primary，其余为union(extra中union result是union去掉重复值的临时表)
-
5.7开始union all不会出现union result，因为不去重
-
-
subquery
-
不是用在from后面的subquery
-
和外部表无关联
-
subquery(标量子查询)执行计划没错误，不代表sql执行没错(不能超过1行数据，subquery return more than 1 row)
-
-
dependent subquery
-
必须依附于外面的值
-
scala subquery(和外部有关系的标量子查询)
-
exists
-
-
derived
-
unin/union all
-
group by
-
distinct
-
聚合函数
-
limit
-
@
-
from位置之后的subquery
-
derived是生成在内存或者临时表空间中
-
如果derived作驱动表时，要点是减少数据量
-
当作被驱动表时产生auto_key索引，也是以减少数据量为目
-
5.7中optimizer_switch='derived_merge=on'可以把简单subquery打开成join
-
derived_merge为on时被驱动表的连接条件要有索引，为off时被驱动表结果集要小
-
5.7的derived_merge可能导致子查询中order by失效
-
5.7以下操作可以防止derived_merge
-
-
materialized
-
select /*+ semijoin(@sub MATERIALIZATION) / * from t_order t2 where t2.emp_no in (select /+ QB_NAME(sub) */ t1.emp_no from dept_emp t1);
-
物化，5.7开始in会产生，也会生成auto_key索引
-
in中使用hint QB_NAME，外层使用这个hint
-
1.3 table
-
NULL
-
表示不使用任何表
-
使用dual
-
extra中出现select tables optimized away,多见于count操作
-
-
表名或者表的别名
-
<derived+ id><union + id>
-
临时表<>里的数字是id列
-
tmp_table_size = max_heap_table_size适当调大
-
1.4 type
-
const
-
使用primary key或者unique key取得一条数据
-
-
eq_ref
-
join，且满足被驱动表的连接条件unique key或者primary key
-
-
ref
-
对索引列做等号判断
-
-
range
-
between in like > <
-
和const的区别在于索引扫描范围不一样
-
-
index
-
索引全扫描，比扫全表且order by的情况快，但是绝大部分情况下也是优化对象
-
不能使用range，const，ref的情况下，且只查询索引列，即不回表，使用索引进行排序或者聚合即省略排序
-
索引(a,b),select a from xxx where b = ''; 即联合索引中前导列不在where条件中，且查询列在索引中
-
在聚合运算中group by后面的列在索引或者primary key中，且查询列也在索引中
-
-
all
-
无索引
-
对索引列加工
-
索引列隐式类型转换
-
对日期类型进行like '20xxx'
-
单列索引，对数字列进行like '30%'
-
全表扫描
-
大表中查询超过一半以上的值，效果更好
-
索引失效
-
1.5 possible_keys
-
列出可能用到的索引，对优化没什么帮助
-
5.6之后开始支持auto_key
-
auto_key就是临时创建索引，需要消耗一些内存和cpu，对tmp_table_size，max_heap_table_size依赖较大
-
mysql列大小超过767个字节，无法生成auto_key
-
convert(xxx,数据类型,字符集)
1.6 key
-
sql用到的索引
1.7 key_len
-
显示sql到底使用了多少索引
1.8 ref
-
只有type是ref或者const才会出现内容，没啥用，不用管
1.9 rows
-
MySQL优化器根据统计信息预估出来的值，不一定准
1.10 filter
-
和rows一样是预估值，非100的情况是extra有using where关键字，表示从存储引擎中拿到数据后再加工的比例
-
5.7开始该值比较准确
1.11 Extra
-
Distinct
-
MySQL在join过程中取出一行之后查询另一个表时，碰到一行就停止，有点像exsits
-
必须是join
-
distinct关键字
-
select列上只能含有驱动表的字段
-
使用straight_join hint可以强制改变驱动表
-
-
select tables optimized away
-
查询中只有min、max的时候出现，有时候count貌似也会出现
-
联合主键，其中任一一个字段用等值查询，查出另一个字段的min或max，且不能包含group by
-
-
Using filesort
-
order by, group by且没使用索引
-
8.0 group by不会出现
-
-
Using index
-
只使用索引不回表就可以查到
-
如果表对应的where条件选择率不是很好，且一行长度很长，此时课考虑创建包含对应列的索引达到减少物理io的目的
-
延迟join必须使用using index，否则无效
-
-
Using temporary
-
sql执行过程中存储中间结果会使用tempoary table，但无法判断在内存还是disk
-
order by,group by未使用索引
-
执行计划中的select type为derived
-
show swssion status like '%tmp%'
-
max_heap_table_size和tmp_table_size(不一致时以小的为准)
-
-
Using where
-
一般和filtered，rows一起看
-
表示从存储引擎中拿到数据再过滤
-
rows是存储引擎中拿数据的预估值，filtered是再过滤的百分比
-
-
Using index condition
-
必须是二级索引才有,且有索引后面部分无法使用时，回表次数很大，效果更好
-
optimizer_switch='index_condition_pushdown=on'
-
-
Using MRR
-
optimizer_switdch='mrr_cost_based=0ff'
-
回表之前先排序，降低随机io
-
-
Range checked for each record
-
type为all
-
这是优化对象，紧接着用show warnings来定位问题
-
-
Using join buffer(Block Nested Loop)
-
optimizer_switch='block_nested_loop=on,batched_key_access=on'
-
被驱动表没有索引且数据量较少的时候，一般这种情况也是优化对象
-
Ⅱ、获取运行中SQL的执行计划
desc for connection connection_id;
