MySQL执行计划

MySQL执行计划

https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

id

  • 简单的 join 都是1且从上到下顺序执行

  • id列越大执行优先级越高，id相同则从上往下执行，id为null最后执行

Select_type

simple

简单的查询，不使用union或者子查询的查询

priamry

  • 用union 或使用子查询的query
  • 复杂查询的最外层的select

union

使用union结合的select，除了第一个之外的 select_type 都使用 union

union result 是 union 去重后的临时表

union all 不出现 union result 是因为不去重【5.6 还是会产生的】

subquery

使用在 select 与 from 之间的子查询，且与from后面的表没有关联

不建议使用，这个也就是一个常数，不如从别地方直接拿过来

root@yq [optimization]> desc select a.id,(select id from t_group where id = 1) from t_in a;
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | PRIMARY     | a       | index | NULL          | PRIMARY | 4       | NULL  |   11 | Using index |
|  2 | SUBQUERY    | t_group | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+
2 rows in set (0.00 sec)

Dependent subquery

必须依附与外面的值(子查询必须和外部的表有关联)，如scala query 或 exists

yqtest@yq [optimization]> desc select a.id,(select id from t_group where id = a.id and id =1) from t_in a;
+----+--------------------+---------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type        | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+--------------------+---------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | PRIMARY            | a       | index | NULL          | PRIMARY | 4       | NULL  |   11 | Using index |
|  2 | DEPENDENT SUBQUERY | t_group | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
+----+--------------------+---------+-------+---------------+---------+---------+-------+------+-------------+
2 rows in set (0.00 sec)

exists 在 8.0.16版本后有差异，从 show warnings 可发现，8.0.16+ 将exists改写成了 join

-- 5.6/5.7
root@yq [optimization]> desc select a.* from t_in a where exists (select 1 from t_group where id = a.id and id =1);
+----+--------------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type        | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+--------------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY            | a       | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  |   11 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | t_group | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+--------------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

-- 8.0.16+ 被转换成了 SIMPLE 
root@yq [optimization]> desc select a.* from t_in a where exists (select 1 from t_group where id = a.id and id =1);
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | a       | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | t_group | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

derived

这里有个特性，一般5.6升级为 5.7后容易出性能问题就是这个参数导致的

derived_merge=on : 视图合并功能（在子查询中含有 limit、group by、distinct、聚合函数[min/max等]、自定义变量[@xx:=xx] 时是无法进行视图合并的）

  • 是From 后面的子查询
  • derived 是生成在内存或临时表空间中的，也就是说会生成一张临时表，虽然在执行计划中没有体现，但能根据 flush status;show status like '%tmp%'；观测到的
  • 因此当 derived 作为驱动表时，一定要是以减少数据量为目的的
  • 当为被驱动表时候，会产生<auto_key>，这个类似于一个临时索引，5.7开始使用 derived_merge=on 后，sql给改写了，没用到临时索引了，所以可能有些语句会有性能问题
 -- 5.6
yqtest@yq [optimization]>  desc select * from (select * from t_group where id <3) a;
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL |    2 | NULL        |
|  2 | DERIVED     | t_group    | range | PRIMARY       | PRIMARY | 4       | NULL |    2 | Using where |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
2 rows in set (0.00 sec)

-- 5.6+
root@yq [optimization]> desc select * from (select * from t_group where id <3) a;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_group | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

-- 5.6+
root@yq [optimization]> set optimizer_switch='derived_merge=off';
Query OK, 0 rows affected (0.00 sec)

root@yq [optimization]> desc select * from (select * from t_group where id <3) a;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |    2 |   100.00 | NULL        |
|  2 | DERIVED     | t_group    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

MATERIALIZED

当表出现在非相关子查询中,并且需要进行物化时会出现MATERIALIZED关键词,8.0.16前，只有使用了 in 才会出现

root@yq [optimization]>  desc select * from t_in where a in (select a from t_in2 where a > 2*3);
+----+--------------+-------------+------------+------+---------------+-------+---------+---------------+------+----------+-------------+
| id | select_type  | table       | partitions | type | possible_keys | key   | key_len | ref           | rows | filtered | Extra       |
+----+--------------+-------------+------------+------+---------------+-------+---------+---------------+------+----------+-------------+
|  1 | SIMPLE       | <subquery2> | NULL       | ALL  | NULL          | NULL  | NULL    | NULL          | NULL |   100.00 | Using where |
|  1 | SIMPLE       | t_in        | NULL       | ref  | idx_a         | idx_a | 13      | <subquery2>.a |    1 |   100.00 | Using index |
|  2 | MATERIALIZED | t_in2       | NULL       | ALL  | NULL          | NULL  | NULL    | NULL          |   11 |    33.33 | Using where |
+----+--------------+-------------+------------+------+---------------+-------+---------+---------------+------+----------+-------------+
3 rows in set, 2 warnings (0.01 sec)

UNCACHEABLE SUBQUERY

UNCACHEABLE SUBQUERY表示子查询不可被物化 需要逐次运行

UNCACHEABLE UNION

同上 子查询中出现union 并且不可被缓存 在union 后的select 语句出现此关键词

table

  • 访问的表名或者别名
  • 当from中有子查询时，table列显示的是<derivedN>格式，表示当前查询依赖 id=N的查询，那么会先执行 id = N的查询
  • 当有union时，table列显示的是<union1N,M>，N,M表示参与union的select

null

不使用实体表

root@yq [optimization]> desc select 1;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.01 sec)

表名或者是别名

root@yq [optimization]> desc select * from t_group;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t_group | ALL  | NULL          | NULL | NULL    | NULL |   25 | NULL  |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

root@yq [optimization]> desc select * from t_group a;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | a     | ALL  | NULL          | NULL | NULL    | NULL |   25 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

<derived+id>与<union+id>

表示临时表，<>里到数字是id列，如下<derived2>表示是id 2到临时表

使用临时表了，max_heap_table_size=tmp_table_size 参数可以适当调大，太小会写到磁盘影响性能

yqtest@yq [optimization]>  desc select * from (select * from t_group where id <3) a;
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL |    2 | NULL        |
|  2 | DERIVED     | t_group    | range | PRIMARY       | PRIMARY | 4       | NULL |    2 | Using where |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
2 rows in set (0.00 sec)

root@yq [optimization]> desc select * from t_group where id = 1 union all select * from t_group where id = 2;
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
| id | select_type  | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra           |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
|  1 | PRIMARY      | t_group    | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL            |
|  2 | UNION        | t_group    | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL            |
| NULL | UNION RESULT | <union1,2> | ALL   | NULL          | NULL    | NULL    | NULL  | NULL | Using temporary |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
3 rows in set (0.00 sec)

type

执行效率以下是由效率高到低排列

system

比较特殊，当表只有一行时候会显示为system

const

使用 primary或union key 获取一条数据，特点是show warnings 可以看到是一个常量

root@yq [optimization]> desc select * from t_group where id = 1;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_group | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

root@yq [optimization]> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                       |
+-------+------+-----------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select '1' AS `id`,'1' AS `a`,'d' AS `b` from `optimization`.`t_group` where 1 |
+-------+------+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

eq_ref

join 时候出现，被关联表字段需要是 PK 或 UK

root@yq [optimization]> desc select * from t_in a inner join t_group b on a.id = b.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref               | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | a     | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL              |   11 |   100.00 | NULL  |
|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | optimization.a.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

ref

索引非唯一性扫描

-- a 表存在索引 idx_a ，b表存在索引 idx_a
root@yq [optimization]> desc select * from t_in a left join t_group b on a.a = b.a;
+----+-------------+-------+------------+-------+---------------+-------+---------+------------------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref              | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------+---------+------------------+------+----------+-------------+
|  1 | SIMPLE      | a     | NULL       | index | NULL          | idx_a | 13      | NULL             |   11 |   100.00 | Using index |
|  1 | SIMPLE      | b     | NULL       | ref   | idx_a         | idx_a | 13      | optimization.a.a |    2 |   100.00 | NULL        |
+----+-------------+-------+------------+-------+---------------+-------+---------+------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

fulltext

使用全文索引时出现

ref_or_null

当谓词出现索引列等于某值或为空时出现


root@yq [optimization]> desc select * from t_group where a is null or a = '1';
+----+-------------+---------+------------+-------------+---------------+-------+---------+-------+------+----------+-----------------------+
| id | select_type | table   | partitions | type        | possible_keys | key   | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------------+---------------+-------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t_group | NULL       | ref_or_null | idx_a         | idx_a | 13      | const |    3 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------------+---------------+-------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

index_merge

当分别用到多个索引的首列,并且连接为或时

-- 表存在索引 idx_a(a),idx_b(b)
root@yq [optimization]> desc select * from t_index_merge where a = '1' or b = '2';
+----+-------------+---------------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table         | partitions | type        | possible_keys | key         | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+---------------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | t_index_merge | NULL       | index_merge | idx_a,idx_b   | idx_a,idx_b | 13,13   | NULL |    2 |   100.00 | Using union(idx_a,idx_b); Using where |
+----+-------------+---------------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

range

范围查询， < , > , between , like , in 等

root@yq [optimization]> desc select * from t_group where id in (1,2,3);
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_group | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    3 |   100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

root@yq [optimization]> desc select * from t_group where id between 1 and 3;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_group | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    3 |   100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

root@yq [optimization]> desc select * from t_group where id >= 8;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_group | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   19 |   100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

index

索引全扫描，比起全表扫描且有排序的情况下快，但大部分情况下还是需要进行优化的对象

只查询索引列，避免回表

Group by 后面的列在索引中且查询列也在索引中也可能会用到

root@yq [optimization]> desc select a from t_group;
+----+-------------+---------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_group | NULL       | index | NULL          | idx_a | 13      | NULL |   26 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

all

全表扫描，效率最差

导致全表扫描的可能是如下原因：

  • 没有索引
  • 对索引列进行了加工
  • 对索引列进行了隐式转换
  • 对日期类型进行了扫描量超出 20%
  • 单列索引时，扫描量超出 30%
  • like 'xx%' ，百分号在后能使用索引进针对字符串类型的，数字类型的不管是在前还是在后都不能用上索引

root@yq [optimization]> desc select * from t_group where b = 'a';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_group | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   26 |    10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

-- 数字类型的就算%在后面也用不上索引
root@yq [optimization]> desc select * from t_group where id like '1%';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_group | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |   26 |    11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

possible_keys

可能使用的索引，看看就好

key

实际使用到的索引

key_len

查询中使用的索引的长度（最大可能长度）

1.字符串类型key_length计算规则

各个字符集长度 utf8mb4=4,utf8=3,gbk=2,latin1=1

key_len=(表字符集长度) * 列长度 + 1(null) + 2(变长列)

char(n)：n字节长度

varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n + 2

注意：该索引列可以存储NULL值，则key_len比不可以存储NULL值时多1个字节。

比如：varchar(50)，字符集为utf8 则实际占用的key_len长度是 3 * 50 + 2 = 152，如果该列允许存储NULL，则key_len长度是153。

2.数值类型key_length计算规则

tinyint：1字节 smallint：2字节 int：4字节 bigint：8字节

3.时间类型

date：3字节 timestamp：4字节 datetime：8字节

ref

关联字段信息或常量

rows

预估扫描行数，不是很准确，但这也是一个比较关键的优化观察点，都是希望扫描的行数越少越好

filtered

过滤，先取出数据后对数据进行了二次过滤，过滤行数大概可以使用 rows * filtered 来计算出来（不是很准确）

值太低的话说明过滤的数据很多，可观察索引是否正确

root@yq [optimization]> desc select * from t_date where t_datetime like '2021-01-01%';
+----+-------------+--------+------------+------+----------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys  | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+----------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_date | NULL       | ALL  | idx_t_datetime | NULL | NULL    | NULL |    9 |    11.11 | Using where |
+----+-------------+--------+------------+------+----------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

root@yq [optimization]> select count(*) from t_date where t_datetime like '2021-01-01%';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

root@yq [optimization]> select 9 * 0.1111;
+------------+
| 9 * 0.1111 |
+------------+
|     0.9999 |
+------------+
1 row in set (0.00 sec)

Extra

Distinct

找不同的值，找到第一个后停止搜索更多的行

1.必须有 distinct 关键字

2.select 列上只能含有驱动表的字段

root@yq [optimization]> desc select distinct a.a from t_in a inner join t_in2 b on a.id = b.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref               | rows | filtered | Extra                        |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+------------------------------+
|  1 | SIMPLE      | a     | NULL       | index  | PRIMARY,idx_a | idx_a   | 13      | NULL              |   11 |   100.00 | Using index; Using temporary |
|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | optimization.a.id |    1 |   100.00 | Using index; Distinct        |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+------------------------------+
2 rows in set, 1 warning (0.00 sec)

root@yq [optimization]> desc select distinct b.a from t_in a inner join t_in2 b on a.id = b.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref               | rows | filtered | Extra                        |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+------------------------------+
|  1 | SIMPLE      | a     | NULL       | index  | PRIMARY       | idx_a   | 13      | NULL              |   11 |   100.00 | Using index; Using temporary |
|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | optimization.a.id |    1 |   100.00 | NULL                         |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+------------------------------+
2 rows in set, 1 warning (0.01 sec)

No tables used

查询没有FROM子句，或者有 FROM DUAL子句。

Select tables optimized away

只有在 min、max、count 统计时可能出现，

root@yq [optimization]> desc select min(id) from t_date;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 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 | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)

Using filesort

无法使用索引完成排序， order by 、group by(8.0前，group by 是带排序的，8.0后不带排序了，相当于 order by null)

root@yq [optimization]> desc select * from t_in2 order by a;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | t_in2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   11 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

-- 5.7 group by
root@yq [optimization]> desc select a from t_in2 group by a;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | t_in2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   11 |   100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)

-- 8.0 group by
root@yq [optimization]> desc select a from t_in2 group by a;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | SIMPLE      | t_in2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   11 |   100.00 | Using temporary |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)

Using temporary

MySQL需要创建一个临时表来保存结果。

如果查询包含GROUP BY、 ORDER BY 、distinct，通常会发生这种情况。

子查询也会使用（derived）

8.0 和全局参数temptable_max_ram有关，默认优先使用这个参数的设置，默认大小1G

-- b 列不存在索引

root@yq [optimization]> desc select b from t_group group by b;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | t_group | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   25 |   100.00 | Using temporary; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)

root@yq [optimization]> desc select b from t_group group by b order by null;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | SIMPLE      | t_group | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   25 |   100.00 | Using temporary |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)

Using index

可称为覆盖索引

只使用索引，不回表，也就是索引全扫描

root@yq [optimization]> desc select id,a from t_group;
+----+-------------+---------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_group | NULL       | index | NULL          | idx_a | 13      | NULL |   25 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Using index condition

可称为索引下推优化

使用索引过滤数据，减少回表次数【ICP特性】

只有二级索引会出现

-- a列存在单列索引
root@yq [optimization]> desc select * from t_group where a > '8';
+----+-------------+---------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t_group | NULL       | range | idx_a         | idx_a | 13      | NULL |    2 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

Using index for group-by

与Using index类似，索引覆盖扫描，不需要进行额外的磁盘操作，以最有效的方式使用索引，因此对于每个组，仅读取少数索引条目。

-- 存在索引 idx_a(a)
root@yq [optimization]> desc select a from t_group group by a;
+----+-------------+---------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+---------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t_group | NULL       | range | idx_a         | idx_a | 13      | NULL |   13 |   100.00 | Using index for group-by |
+----+-------------+---------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

Using where

先取出数据后再比对，一般和 filtered 和 rows 一起看


root@yq [optimization]> desc select * from t_group where b = '3';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_group | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   25 |    10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Range checked for each record (index map: N)

注意：一般出现这个都需要优化

一般很可能是出现隐式转换了

Using join buffer (Block Nested Loop)

一般性能都不会很好

将外层循环的行/结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较，从而减少内层循环的次数.

举例来说，外层循环的结果集是100行，使用NLJ 算法需要扫描内部表100次，如果使用BNL算法，先把对Outer Loop表(外部表)每次读取的10行记录放到join buffer,然后在InnerLoop表(内部表)中直接匹配这10行数据，内存循环就可以一次与这10行进行比较, 这样只需要比较10次，对内部表的扫描减少了9/10。所以BNL算法就能够显著减少内层循环表扫描的次数.

root@yq [optimization]> desc select * from t_in a inner join t_in2 b on a.a = b.a+11 ;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                                                           |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------------------------------------------------+
|  1 | SIMPLE      | b     | NULL       | ALL   | NULL          | NULL  | NULL    | NULL |   11 |   100.00 | NULL                                                            |
|  1 | SIMPLE      | a     | NULL       | index | idx_a         | idx_a | 13      | NULL |   11 |    10.00 | Using where; Using index; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------------------------------------------------+
2 rows in set, 3 warnings (0.00 sec)

影响执行计划的配置-optimizer_switch

https://dev.mysql.com/doc/refman/5.7/en/switchable-optimizations.html

-- 5.6 的
| optimizer_switch              | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on |
| optimizer_trace               | enabled=off,one_line=off 

-- 5.7 的
| optimizer_switch                              | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,prefer_ordering_index=on |
| optimizer_trace                               | enabled=off,one_line=off  

-- 8.0的
| optimizer_switch              | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on |
| optimizer_trace               | enabled=off,one_line=off
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 205,033评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 87,725评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 151,473评论 0赞 338
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,846评论 1赞 277
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,848评论 5赞 368
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,691评论 1赞 282
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,053评论 3赞 399
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,700评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 42,856评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,676评论 2赞 323
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,787评论 1赞 333
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,430评论 4赞 321
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,034评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,990评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,218评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,174评论 2赞 352
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,526评论 2赞 343

推荐阅读更多精彩内容