MySQL之性能分析（二）

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

1.查看SQL的执行成本

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

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

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

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

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

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

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

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

也可以使用show profile cpu,block io for query 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/…

在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;,结果如下图：

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

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

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

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

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

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

这又是为什么呢？因为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)

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

小结：

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

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

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

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

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引擎，如下图：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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;,执行后如下图：

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

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

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

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

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

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

 

 

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