一）基本内容
1. 查看
- desc ...
- explain ...
2. 计划类型【参数】
-
mysql5.6:
desc extended | format=traditional ...
--extended 查看优化过的SQL语句 -
mysql5.7:
desc extended | format=traditional ... -
mysql8.0:
desc format=tree ...
--tree 因为hash,所以用tree 可以客观体现 join方式
show warnings\G;
3. 执行计划关键词
- id： --单纯的查询，尖都是从1开始，子查询都会使id递增。
- select_type： --simple没有关联查询
- type： --
- possible_keys：--可用的索引
- key： --使用的索引
- ref: --驱动表的关联字段
- rows： --预估行数
- filtered： -- 100是没有二次过滤
- extra： --
4. 驱动表：
选择驱动表时，应选结果集小，因为双循环外循环（驱动表为外循环）次数越少，越省资源，进而查询速度也相对快些。
驱动表是跟最后一个join开始执行，然后倒数第二个，倒数第三个。。。以此类推。
二）select_type
1. simple:--不使用union或者subquery的简单query。
-
mysql5.7之后有 derived_merge
- 查：show variables like '%opt%'; optimizer_search_switch:derived_merge=on--子查询合并（视图合并）
- 关闭：set session optimizer_search_switch='derived_merge=off';
-
limit:--用在优化时，使用时标量子查询视图不合并情况之一。
2. subquery:标量子查询，位于from之前。
- 标量子查询，跟外部表没啥关联，相当于一个常数。
- 子查询不要嵌套子查询，影响性能。
3. dependent subquery（标量子查询，子查询都有可能。特别注意，表的别名不要重复。）
- 必须依附于外面的值
- scala subquery（标量子查询 ）
- exists
- mysql5.7及之前 depentend subquery
- mysql8.0.16之后的版本，exists的语句会转换成in,执行计划为simple，如何使exists不改写为in，使执行计划为depentend subquery?
select straight_join * from t1 exists (select 1 from t2 where t1.id=t2.id);
4. primary:
- 用union
- 或者用subquery
5. union:
- union result去重复值
- 生成临时表
- max_heap_table_size
- tmp_table_size
查询时 使用的是union。mysql5.6版本union all操作也去重。
6. union all:是union all操作，不去重。
- 5.6 union result 产生临时表
- 5.7 不产生临时表
7. derived：派生表，位于from后 子查询。
-
derived是生成在内存或临时表空间中的。
- max_heap_table_size
- tmp_table_size
-
derived 当作驱动表时，要点是要减少数据量为目的。
-
derived 当作被驱动表时，产生auto_key索引（当条件字段大于767时，不产生auto_key），也要以减少数据量为目的。
-
不视图合并的情况：
- union/union all
from (select ...from ... union select ... from ...) - group
from (select ...from... group by ) - distinct
from (select distinct(xxx) from ...) - 聚合函数
from (select min(x),max(x),sum(x)... from...) - limit
from (select... from ... limit ...) - @
- select c1,(select @a:=0) x from ...
- union/union all
8. materialized：semi join,产生auto_key
- 使用in的时候
- 使用exists的时候 ？？有可能
- 用hint强制使用materialized
select /*+ semi join(@sub materialization) / * from t1 where t1.c1 in(select /+ QB_NAME(sub) */) c2 from t2);
三）type
1. const
- 出现在单表、驱动表的pk、unique， 返回一条数据。
2. eq_ref
- join时，被驱动表连接条件有PK或unique时。
3. ref
- 索引列作为 "=" 值条件运算时
- 当有联合索引跟单列索引时，可能会错用索引，可以用 force index(xx)解决
4. range
- 范围查询，只能用到驱动表
5. index 绝大部分情况下是优化对象，相当于索引全扫描
- 不能使用range const ref时；
- 只查询索引列，不回表时；
- 使用索引进行排序或聚合
- 联合索引中，前导列不在where条件中，且查询列在索引中。
- group by 字段 在索引或pk中
6. all
- 结果集超过一半的量的查询效果更好。
- 没有索引时，或类型转换时。
- like 字符 可走索引
- c1 like 'li%';
- all 关键字在执行计划中的位置越往下，出问题的情况越高。
四）possible_keys：可用的索引
- 列出所有的索引
- 列出auto_key
- 当子查询当作被驱动表时，字段长度不超过767时，自动生成auto_key。
- auto_key 在临时表，控制临时表相关参数：
- tmp_table_size
- max_heap_table_size
五）key：实际使用的索引
- 查索引 show index from tb_name
- cardinality 去掉重复值的预估行数，参数：innodb_stats_persistent_sample_pages
- visible
六）key_len 使用到的索引长度
七）ref: --驱动表的关联字段
八）rows： --预估行数
九）filtered：二次过滤预估值，从引擎拿到数据再加工的比率，100是没有二次过滤。
九）extra：
1. distinct
- join 中 两表关联，有一条符合记录即停止，有点像semi join
- 条件：
- select 必须有distinct 关键字
- select 只能含有驱动表字段
- 可以使用straight_join强制第一个表为驱动表
2. select tables optimized away
- 8.0之前 聚合函数出现时
3. using filesort
- 进行order by ,group by(8.0之后不排序) 且没使用索引时；
- order by 时，select的列都会写入临时表空间
4. using index
- 只使用索引，不回表
- 一种思路，可把常用列创建索引，达到垂直分表的效果。
5. using temporary
- 中间结果会使用临时表,但无法判断在内存中，还是硬盘中。（设置参数 temp_table_max_ram=1 测试）
- order by、group by 时；
- 产生derived(派生表)时
6. using where
- 从存储引擎中拿到一些数据，然后再过滤。
7. using index condition（减少回表量）
- 必须二级索引，且有一部分无法使用索引时。
- set session optimize_switch="index_condition_pushdown=on"
8. using mrr
- 二级索引取得PK之后，对PK进行排序，减少随机IO。
- set session optimize_switch='mrr_cost_based=off'
9. range checked for each record
- 这时type肯定是all,没用上索引
10. using join buffer:(block nested loop)
- 大部分情况是优化对象
- 适用于被驱动表没有索引时且数据量较小时。
- set session optimizer_switch='block_nested_loop=on'
网友评论