mysql执行计划与性能优化（待改进）_mysql8 explain update

mysql8官网8.8节 理解执行计划

执行计划又叫explain计划。

• explain可以与select、delete、insert、replace、update语句一起使用。
• explain可以告诉我们，表与表是如何联接的，以及子任务的执行顺序。
• explain对于SELECT语句可以使用show warnings显示的附加执行计划信息。
• explain对于涉及分表的查询特别有用。
• explain的format选项可以选择结果输出的方式，一般是以表格形式展示，也可以使用json形式。

explain查看执行计划信息（我常用）

​​

id

查询中，查询子任务操作的顺序，若序号相同则从上到下执行；若顺序不同，数值越大，执行优先级越高.

select_type

表示查询中每个查询子任务的类型

• simple：简单查询，查询中不包含子任务或union
• primary：查询中若包含复杂的查询子任务，则最外层的select为primary
• subQuery：where里的查询子任务类型为subQuery
• derived（衍生）：from字句里的查询子任务为derived
• union：union后紧跟着的查询子任务为union
• union result：不懂求助

table

表别名

type

mysql从表中找到所需行的方式

• all：最糟糕的全表扫描
• index：全索引遍历扫描，遍历整棵索引树
• range：部分范围内索引遍历扫描，常用于between、>、<、in
• ref：noUniqueIndex（非唯一性索引扫描）或唯一性索引的非唯一性前缀，返回匹配到的所有行
• eq_ref：唯一性索引扫描，常用于主键（primary key）或 普通唯一性所以（unique
  index），表中只会有一条记录与之匹配。
• const：当使用主键作为where字句的条件时，mysql就会把这个查询转换为一个常量，方便高效从链接执行中删除。
• null：说明直接从索引树中就可以完成了，而不用再去查表的操作，比如获取索引的最大最小值.

possible_keys

可能会用到的索引

key

实际会用到的索引

key_len

作为索引的字段的长度ref在key列所记录的索引中，查找值所用的列或常量rows扫描的行数

Extra

1. using index
   表示mysql将使用覆盖索引，避免访问表，涉及概念覆盖索引

1. 如果索引中已经包含你所有查找的所有字段，就没必要回表（读磁盘查表），这就叫覆盖索引。
2. 由于innodb使用了聚簇索引，且innodb的二级索引在叶子节点中保存了行的主键，所以若二级索引可以覆盖查询，就能省去反查主键的消耗。
3. 覆盖索引要求索引中必须存储索引列的值，而hash索引、空间索引、全文索引都不支持存储索引列的值，所以不是所有索引都支持覆盖索引，mysql只支持btree做覆盖索引。

2. using index condition
   先读取索引元组，判断是否要下推进行全表扫描。（什么是索引下推）

3. using where
   表示查询可受益于不同的索引，即where字句里包含索引列，mysql服务器将在存储引擎检索后再进行过滤。

4. using filesort
   表示mysql将会使用外部索引排序（什么是外部索引排序），但mysql提供了两种外部索引排序，且并不会告知我们到底使用了那种，以及是在内存还是在磁盘完成排序。

• using tempory 表示mysql在对查询结果排序时会使用一个临时表
• using where 表示查询可受益于不同的索引，即where字句里包含索引列，mysql服务器将在存储引擎检索后再进行过滤。
• using filesort 表示mysql将会使用外部索引排序（什么是外部索引排序）
• range check for each record 表示没有好用的索引，新的索引将在联接的每一行上重新估算（不懂啥意思）

优化案例

案例1

select
	*
from
	xxxx
where
	`is_deleted` = 0
	and `task_id` = ?
	and `type` in (?,?,?,?,?,?)
order by
	`id`
limit ?,?
1
2
3
4
5
6
7
8
9
10
11

执行计划

[{"ref":"const",
"filtered":4.0,
"Extra":"Using index condition; Using where; Using filesort",
"select_type":"SIMPLE",
"id":1,
"type":"ref",
"rows":201156,
"possible_keys":"key",
"table":"xxxx",
"key":"key",
"key_len":"8" }]
1
2
3
4
5
6
7
8
9
10
11

in查询会走索引，但随着in的范围变大，效率会主键下降为全表扫描。

explain的局限性与show status

1. limit在explain时无效

不论在Mysql5还是Mysql8，explain select * from x limit 10，执行计划在预测扫描行数时都会剔除limit子句。

2. explain预估影响行数的机制（扫描行数不准）

MySQL是按照Page存储的，每个Page都有自己的元数据，会记录行数。
假设select * from x where sex = ‘male’，性别为男性的记录实际占10页半。
但是
mysql5以前，预估扫描行数，只会采样2页，首页及尾页
mysql5以后，预估扫描行数，只会采样10页，首页 + 尾页 + 首页往后8页

预估Rows = ((第1页 + 第2…第9页 + 最后1页)/10)*10页半，这就出现误差了。

3. 使用show status like “Handler%” 提高扫描行数准确率

5.1.10 Server Status Variables
关注这几个参数

• Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数,越低代表索引不经常使用。
• Handler_read_next ：按照键顺序读下一行的请求数。利用索引进行范围查找，该值增加。
• Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。

rnd_next过大需要优化;
read_key、read_next、read_prev太大也不行，这说明扫描了很多索引值才找到记录，也需要优化；
通过show status可以较为准确的知道扫描了多少行，同时不会像explain一样忽略limit子句；

flush status;
# last_name字段不是索引
select * from employees where last_name = 'Facello';
SHOW SESSION STATUS LIKE "Handler%";
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| Handler_commit             | 1      |
| Handler_delete             | 0      |
| Handler_discover           | 0      |
| Handler_external_lock      | 2      |
| Handler_mrr_init           | 0      |
| Handler_prepare            | 0      |
| Handler_read_first         | 1      |
| Handler_read_key           | 1      |
| Handler_read_last          | 0      |
| Handler_read_next          | 0      |
| Handler_read_prev          | 0      |
| Handler_read_rnd           | 0      |
| Handler_read_rnd_next      | 300025 |
| Handler_rollback           | 0      |
| Handler_savepoint          | 0      |
| Handler_savepoint_rollback | 0      |
| Handler_update             | 0      |
| Handler_write              | 0      |
+----------------------------+--------+
18 rows in set (0.00 sec)

flush status;
# first_name字段是索引
select * from employees where first_name = 'Kyoichi';
SHOW SESSION STATUS LIKE "Handler%";
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 251   |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+

flush status;
# 带有limit子句的情况
select * from employees where first_name = 'Kyoichi' limit 10;
SHOW SESSION STATUS LIKE "Handler%";
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 9     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
18 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82

使用show profile剖析单条查询（我常用）

​默认禁用，但它是会话级别的参数。
set profiling=1 ，然后在服务器上直送所有的语句，都会测量其耗费的时间和其他一些查询执行状态变更相关的数据。

​当一条查询提交给服务器时，此工具会记录剖析信息到一张临时表，并给查询赋一个从1开始的整数标识符。

set profiling=1;
select * from t_Order;
select * from t_Product;
show profiles;
+----------+------------+-------------------------+
| Query_ID | Duration   | Query                   |
+----------+------------+-------------------------+
| 1        | 9.75e-05   | SHOW WARNINGS           |
| 2        | 0.00052075 | select * from t_order   |
| 3        | 0.000511   | select * from t_product |
| 4        | 5.3e-05    | SHOW WARNINGS           |
+----------+------------+-------------------------+
show profile for query 3;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000065 |
| checking permissions | 0.000009 |
| Opening tables       | 0.000142 |
| init                 | 0.000022 |
| System lock          | 0.000010 |
| optimizing           | 0.000008 |
| statistics           | 0.000013 |
| preparing            | 0.000012 |
| executing            | 0.000007 |
| Sending data         | 0.000154 |
| end                  | 0.000010 |
| query end            | 0.000011 |
| closing tables       | 0.000010 |
| freeing items        | 0.000016 |
| cleaning up          | 0.000012 |
+----------------------+----------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

sending data

这个指标是非常具有迷惑性的，它不是单纯指mysql服务器往客户端发送数据，而应该理解为”读取、筛选、聚合并发送数据“。
这个状态出现在sql即将进行大量的磁盘读取的时候。
慢查询一般这个指标都比较高，这个状态如果耗时长，代表的原因非常多，单看该指标很难分析出东西，需要视具体情况而定：

• 可能是各种不同的服务器原因。
• 也可能是关联查询时搜索匹配的行记录等。

stackOverflow的案例：

其他指标请参考
mysql5：https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html
mysql8：https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html

参考

《高性能MySQL》 学习笔记,第三章，服务器性能剖析