MySQL之性能分析（二）

柔光的暖阳◎ 2023-09-29 09:00 20阅读 0赞

## ![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAamF2YeaZtOWkqei_h-WQjg_size_20_color_FFFFFF_t_70_g_se_x_16][] ##

上篇说到查看SQL查询成本、开启慢查询日志以及慢查询分析工具mysqldumpslow，本文将继续讲述性能分析的排查。

#### 1.查看SQL的执行成本 ####

先查看是否开启查询SQL执行成本使用命令`show variables like 'profiling';`如下图：

![format_png][]

设置开启SQL执行成本记录`set global prifiling = 1;`,需要重新连接MySQL，再次查看，如下图，SQL执行成本已开启。

![format_png 1][]

查看当前会话有哪些执行记录，使用命令`show profiles;`,如下图：

![format_png 2][]

查看最近一次的执行详细开销信息使用命令`show profile`,如下图：

![format_png 3][]

如何查看指定的查询开销呢？，下面我们执行2个SQL：

`select * from student where id = 6000000;` `select 8 from class where id = 6000;`

使用`show profiles;`在查看,如下图：

![format_png 4][]

可以看到已经有记录了，并且还有`quert_id`,我们只需要使用`show profile for query [query_id];`即可查看指定的查询详细开销信息了，如下图：

![format_png 5][]

也可以使用`show profile cpu,block io for query 6;`显示更加详细的返回，如下图

![format_png 6][]

当然还有更多参数，如下：

1.  ALL：显示所有的开销信息。
2.  BLOCK IO：显示块IO开销。
3.  CONTEXT SWITCHES：上下文切换开销。
4.  CPU：显示CPU开销信息。
5.  IPC：显示发送和接收开销信息。
6.  MEMORY：显示内存开销信息。
7.  PAGE FAULTS：显示页面错误开销信息。
8.  SOURCE：显示和Source\_function，Source\_file，Source\_line相关的开销信息。
9.  SWAPS：显示交换次数开销信息。

#### 2.分析查询语句 ####

上面讲的都是如何查看慢SQL，这里我们来讲下`explain`命令如何分析SQL。

官网地址：[dev.mysql.com/doc/refman/…][dev.mysql.com_doc_refman]

在MySQL5.6.3之前`explain`命令只能使用在select上，可是在有些时候，我们的update、delete语句也很慢，也需要分析下怎么办，很好的是在5.6.3后`explain`也能分析update、delete语句了。

在MySQL6.7前，想用`explain`查看partitions信息和filtered信息需要显示的使用`explain [partitions | filtered]`在5.7后只需要`explain`即可。

基本使用直接在外面的查询语句前加上`explain`即可，如：`explain select * from class id = 1;`,结果如下图：

![format_png 7][]

每个字段具体含义参考下图：

![format_png 8][]

下面对`explain`的各个字段进行详细解释。

1.  **id**:对于整个SQL当中有多少个`select`关键字就有多少个id，id是唯一的。

执行如下SQL`explain select * from class where id = 1;`查看结果，会发现有一个id，如下图：

![format_png 9][]

执行如下SQL`explain select * from class c inner join student s on s.classId = c.id where c.id = 1000`会发现也只有一个id，如下图:

![format_png 10][]

我们将连接查询改为子查询`explain select * from class c where c.id in (select s.classId from student s) and c.id = 1000`，会发现id还是只有一个，但是多了一条记录，因为有多少个表就会有多少条记录（包含MySQL生成的临时表，可能在我们的SQL中不存在），如下图

![format_png 11][]

这又是为什么呢？因为MySQL查询优化器将这个查询优化成join查询了实际上还是一个select，换成这个SQL试试看

`explain select * from class c where c.id in (select s.classId from student s where s.id between 5001000 and 5002000)`

![format_png 12][]

这里需要注意的是有三条记录，但是我们的SQL只写了student和class表，多了个`<subquery2>`,他是个临时表依赖于student表。

小结：

**对于id字段来说，有多少个select（在MySQL查询优化器优化之后）就有多少个id，并且id越大越先执行，相同id由上往下执行**。

**有多少个表（在MySQL查询优化器优化之后），就有多少条记录，其中可能会生成临时表也会有记录**。

1.  **select\_type**:具体含义自行去官网查看，下面只列举几个比较常见的类型

![format_png 13][]

3. **table**:在演示id字段的时候其实就可以看出，这个字段含义就是说明当前的记录是哪个表的，如下图

![format_png 14][]

4.**partitions**:这个字段是数据分区相关的 ，没用过基本，需要了解可自行去官网

5.**type**:这个字段很重要，基本上我们靠他参考一个SQL好与坏，type有很多类型，如下（越好的排在前面）：

> `system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 。`

一般来说工作中最主要参考的类型是：`system,const,eq_ref,ref,range,index,all`,能够达到`range`是最低要求，达到`ref`算达标，下面详细介绍下几个类型的出现场景。

`system`:指的是MySQL系统常量，我们都知道在引擎为`MyISAM`的表下会有一个系统常量去记录当前表的数据条数，也就等效与`count(*)`，注意前提是在`MyISAM`引擎的情况下，`InnoDB`引擎下`count(*)`还是回去统计表，当前有表`test`为`MyISAM`引擎，如下图：

![format_png 15][]

如下图，也就是最快的无法进行优化了。

![format_png 16][]

执行如下SQL语句：`select * from test;`

![format_png 17][]

`const`：常量条件很好理解，如下SQL`select * from student where id = 5000001;`执行如下：

![format_png 18][]

`eq_ref`:在连接查询时，如果被驱动表是通过主键或者唯一的二级索引字段去进行匹配的，如下SQL

`explain select * from class c inner join student s on s.classId = c.id;`，（student有字段classId对应class表的主键）如下图：

![format_png 19][]

`ref`:使用普通的二级索引（也就是不唯一的索引字段）或者使用常量去进行匹配，(student表的classId是一个普通索引)如下SQL： `explain select * from student where classId = 6666;`

![format_png 20][]

`ref_or_null`:和上面的`ref`差不多，看名字就知道了，如下SQL

`explain select * from student where classId = 6666 or classId is null;`

![format_png 21][]

`ref_merge`:索引合并，也好理解，正常只能使用一个索引，但是也可能会出现索引合并使用的情况，但实际上我们并没有设置联合索引，这个索引是mysql给我们虚拟的，但是确实两个索引都使用上了，如下SQL:

`explain select * from student where classId = 6666 or id = 5000001;`

![format_png 22][]

`range`:也好理解，字面意思范围，如下两种SQL都可以

`explain select * from class where id in(1,2,3,4);` `explain select * from class where id > 1 and id < 10;`

![format_png 23][]

`index`:索引覆盖，在student表下有联合索引`idx_class_id_sid(`classId`,`id`,`name`(20))`，分别是classId，id,name三个字段，其中name是最后一个，根据最左匹配原则，如下SQL是不能使用到索引的，但是其select查询的字段刚好在联合索引下，则可触发索引

`explain select classId from student where name = 'HVJvkL';`

![format_png 24][]

1.  **possible\_keys**:可能使用到的索引，会有多个。
2.  **key**:实际上使用到的key，一般情况下都是一个，在`index_merge`索引合并的情况下会有多个。
3.  **key\_len**:使用到的索引长度，长度占索引字段百分比越高，索引使用率越高。
4.  **ref**：索引引用的列，如果使用函数那会是函数结果。
5.  **rows**:被扫描的行数。
6.  **filtered**:条件过滤后剩余的数量占扫描行数的百分比。
7.  **extra**:额外的信息，比如文件排序file sort,where condition,join buffer等等，也是重要的参考。

**补充**

1.  **explain输出格式**

explain 输出可以有多种格式，最常见的就是我们数据库这种，也就是上面的，另外还有`json`,`tree`,`可视化workbench`

如josn类型:`explain format=json select * from class where id = 1;`,执行后如下图：

![format_png 25][]

tree格式是8.0之后才有，如下SQL:

`explain format=tree select * from attendance where id between 1 and 1000;`

![format_png 26][]

可视化输出需要安装`workbench`,是MySQL官方的可视化工具，图标显示，非常直观：

![format_png 27][]

鼠标放上去有详细信息，颜色和上面讲述的type一样，具体使用可以自行去官网查看

![format_png 28][]

**2. show warnings命令输出优化后的SQL**

![format_png 29][]

有的时候子查询会被优化成inner连接查询，但是你不知道，这个时候就可以使用`show warnings`命令查看。

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAamF2YeaZtOWkqei_h-WQjg_size_9_color_FFFFFF_t_70_g_se_x_16][]

作者：别给我加香菜  
链接：https://juejin.cn/post/7083328410428112927

[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAamF2YeaZtOWkqei_h-WQjg_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/99da529b77294c2aadb64eff225d868d.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAamF2YeaZtOWkqei_h-WQjg==,size_20,color_FFFFFF,t_70,g_se,x_16
[format_png]: https://img-blog.csdnimg.cn/img_convert/245233deee5fad6e6e7bcacdddd17e75.webp?x-oss-process=image/format,png
[format_png 1]: https://img-blog.csdnimg.cn/img_convert/b0c494da74cdb338e043811921d1fcb3.webp?x-oss-process=image/format,png
[format_png 2]: https://img-blog.csdnimg.cn/img_convert/2ba3cebd471f07bc847510957fb68e1c.webp?x-oss-process=image/format,png
[format_png 3]: https://img-blog.csdnimg.cn/img_convert/277153a4ec9ac33542b375510b3e9060.webp?x-oss-process=image/format,png
[format_png 4]: https://img-blog.csdnimg.cn/img_convert/8f468b898c17a5db444a03eb390be78d.webp?x-oss-process=image/format,png
[format_png 5]: https://img-blog.csdnimg.cn/img_convert/822a138b5a4a2a5cfd6f9cd3f56facf8.webp?x-oss-process=image/format,png
[format_png 6]: https://img-blog.csdnimg.cn/img_convert/0297cb6315cb9aa76a2bfdceeb15215b.webp?x-oss-process=image/format,png
[dev.mysql.com_doc_refman]: https://link.juejin.cn?target=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.7%2Fen%2Fexplain-output.html
[format_png 7]: https://img-blog.csdnimg.cn/img_convert/4fe6038ae6af5a7253408da3e1dd1d28.webp?x-oss-process=image/format,png
[format_png 8]: https://img-blog.csdnimg.cn/img_convert/179f0b8473e8c0add8738992c8aeeeb6.webp?x-oss-process=image/format,png
[format_png 9]: https://img-blog.csdnimg.cn/img_convert/4ce3656f8c7b9cbc81d0fc848c7a2cea.webp?x-oss-process=image/format,png
[format_png 10]: https://img-blog.csdnimg.cn/img_convert/526453c1b5580523d847bc8be192d3d4.webp?x-oss-process=image/format,png
[format_png 11]: https://img-blog.csdnimg.cn/img_convert/1899f2545bcf0ae5685bb67c39f9205f.webp?x-oss-process=image/format,png
[format_png 12]: https://img-blog.csdnimg.cn/img_convert/ffe26d329061ecb8ba0d5545f0517e6f.webp?x-oss-process=image/format,png
[format_png 13]: https://img-blog.csdnimg.cn/img_convert/0c92f18286491a9c722ddce8b7fcb36d.webp?x-oss-process=image/format,png
[format_png 14]: https://img-blog.csdnimg.cn/img_convert/c6bcbb57185ae060fbd65719c8685c4b.webp?x-oss-process=image/format,png
[format_png 15]: https://img-blog.csdnimg.cn/img_convert/b5f057c2398b7d81504c8798246812d6.webp?x-oss-process=image/format,png
[format_png 16]: https://img-blog.csdnimg.cn/img_convert/3d4dc70f8a5b9bcfad953d0a0079d141.webp?x-oss-process=image/format,png
[format_png 17]: https://img-blog.csdnimg.cn/img_convert/48d60332ce3c6227f430b4fe1addf02d.webp?x-oss-process=image/format,png
[format_png 18]: https://img-blog.csdnimg.cn/img_convert/6bd5e4bbb67bce52ab04476040b6e88b.webp?x-oss-process=image/format,png
[format_png 19]: https://img-blog.csdnimg.cn/img_convert/a0c8c47a5e5f99166ba75f749ed20d1c.webp?x-oss-process=image/format,png
[format_png 20]: https://img-blog.csdnimg.cn/img_convert/29fa67a3d56d80983672ed5c20ba2b93.webp?x-oss-process=image/format,png
[format_png 21]: https://img-blog.csdnimg.cn/img_convert/6fe74447b38d2cec30c8a64214956a74.webp?x-oss-process=image/format,png
[format_png 22]: https://img-blog.csdnimg.cn/img_convert/fb5e90bd28b8e40b39f790beaf3e5c38.webp?x-oss-process=image/format,png
[format_png 23]: https://img-blog.csdnimg.cn/img_convert/2601e89661d816025acf7d0bc8200952.webp?x-oss-process=image/format,png
[format_png 24]: https://img-blog.csdnimg.cn/img_convert/c6989eb0b6b70fe272af33c02840b7be.webp?x-oss-process=image/format,png
[format_png 25]: https://img-blog.csdnimg.cn/img_convert/1efdfff7e53e8f088d9903f268532c18.webp?x-oss-process=image/format,png
[format_png 26]: https://img-blog.csdnimg.cn/img_convert/b931293efd3c5e8dd74ef1b782c723a3.webp?x-oss-process=image/format,png
[format_png 27]: https://img-blog.csdnimg.cn/img_convert/de3d0bf6f144cbc2b537fb60e2fc7c6d.webp?x-oss-process=image/format,png
[format_png 28]: https://img-blog.csdnimg.cn/img_convert/ab850df93c1431d9cc84f62cf9adab04.webp?x-oss-process=image/format,png
[format_png 29]: https://img-blog.csdnimg.cn/img_convert/d8151ef626e2dcb3453809909e350a8c.webp?x-oss-process=image/format,png
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAamF2YeaZtOWkqei_h-WQjg_size_9_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/f4cbb36e056c4fd48f86c11f7b943c3f.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAamF2YeaZtOWkqei_h-WQjg==,size_9,color_FFFFFF,t_70,g_se,x_16