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

刀剑如梦TOTO333

已于 2023-01-29 15:26:20 修改

阅读量440

点赞数

分类专栏：待改进 mysql 笔记文章标签： mysql 数据库 database

于 2021-04-17 22:11:57 首次发布

本文链接：https://blog.csdn.net/qq_26950567/article/details/115802700

版权

笔记同时被 3 个专栏收录

56 篇文章 0 订阅

订阅专栏

待改进

39 篇文章 1 订阅

订阅专栏

mysql

4 篇文章 0 订阅

订阅专栏

文章目录

explain查看执行计划信息（我常用）
explain的局限性与show status
使用show profile剖析单条查询（我常用）
- sending data
参考

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

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

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

using index condition
先读取索引元组，判断是否要下推进行全表扫描。（什么是索引下推）
using where
表示查询可受益于不同的索引，即where字句里包含索引列，mysql服务器将在存储引擎检索后再进行过滤。
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 ?,?

执行计划

[{"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" }]

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)

使用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 |
+----------------------+----------+

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》学习笔记,第三章，服务器性能剖析

刀剑如梦TOTO333

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql执行计划与性能优化（待改进）

文章目录执行计划包含的信息idselect_typetabletypepossible_keyskeykey_lenExtra执行计划包含的信息id查询中，查询子任务操作的顺序，若序号相同则从上到下执行；若顺序不同，数值越大，执行优先级越高.select_type表示查询中每个查询子任务的类型simple：简单查询，查询中不包含子任务或unionprimary：查询中若包含复杂的查询子任务，则最外层的select为primarysubQuery：where里的查询子任务类型为subQ
复制链接

扫一扫

专栏目录