手把手教你分析MySQL查询性能瓶颈,包教包会

·  阅读 886
手把手教你分析MySQL查询性能瓶颈,包教包会

携手创作,共同成长!这是我参与「掘金日新计划 · 8 月更文挑战」的第9天,点击查看活动详情

当一条SQL执行较慢,需要分析性能瓶颈,到底慢在哪?

我们一般会使用Explain查看其执行计划,从执行计划中得知这条SQL有没有使用索引?使用了哪个索引?

image-20220806222612663.png

但是执行计划显示内容不够详细,如果显示用到了某个索引,查询依然很慢,我们就无法得知具体是哪一步比较耗时?

好在MySQL提供一个SQL性能分析工具 — Profile

Profile 可以帮助我们分析SQL性能瓶颈和资源消耗情况。

1. 查看Profile配置

show variables like '%profil%';
复制代码

image-20220806224252303.png

have_profiling 表示是否支持profile功能,YES表示支持

profiling 表示是否开启profile功能,ON开启,OFF关闭,默认是关闭状态

profiling_history_size 表示保存最近15条历史数据

2. 开启Profile功能

set profiling=1;
复制代码

image-20220806224652421.png

注意:修改配置,只对当前会话生效,会话关闭,Profile历史信息被清空。

3. 使用Profile

先造点数据,创建一张用户表:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(100) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` tinyint NOT NULL  DEFAULT 0 NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
复制代码

执行一条耗时SQL:

select * from user order by name;
复制代码

下面轮到主角Profile出场了。

我们执行的所有SQL语句都会被记录到Profile里面,包括执行失败的SQL语句。

可以使用show profiles命令查看:

image-20220806230212047.png

输出参数详解:

Query_ID 表示自动分配的查询ID,顺序递增。

Duration 表示SQL语句执行耗时

Query 表示SQL语句内容

然后,我们再使用Query_IDProfile中查看具体每一步的耗时情况:

show profile for query 1;
复制代码

image-20220806230723609.png

可以清楚的看到耗时主要花在创建排序索引(Creating sort index) 上面。

再试一条SQL:

select distinct name from user;
复制代码

image-20220806231300522.png

这次的耗时主要花在了,创建临时文件、拷贝文件到磁盘、发送数据、删除临时表上面。

由此,可以得知distinct函数会创建临时文件,提醒我们建索引。

我们还可以扩展一下这条分析语句,查看一下cpu和block io的使用情况:

show profile cpu,block io for query 2;
复制代码

image-20220806232136871.png

另外,其实所有Profile历史数据都被记录在information_schema.profiling表中,我们也可以查询表得到结果:

select * from information_schema.profiling where Query_ID=2;
复制代码

image-20220806232935997.png

以上数据都是基于MySQL5.7版本,在MySQL8.0版本的输出结果字段有些变化。

另外,细心的你应该发现了,在我们每执行完一条SQL,都显示了一条warning信息,我们查看一下具体的warning信息:

show warnings;
复制代码

image-20220806234607577.png

意思就是,Profile工具将来有可能被删除,不建议继续使用了。

好吧,下篇文章我们再一块学习一下MySQL提供的,用来替换Profile的最新性能瓶颈分析工具,使用更便捷。。

分类:
后端
收藏成功!
已添加到「」, 点击更改