show profile 进行 sql 分析

柔情只为你懂 2022-09-12 06:56 5阅读 0赞

# 一 点睛 #

show profile 是 mysql 提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于 SQL 的调优的测试。

官网：

[MySQL :: MySQL 5.7 Reference Manual :: 13.7.5.30 SHOW PROFILE Statement][MySQL _ MySQL 5.7 Reference Manual _ 13.7.5.30 SHOW PROFILE Statement]

默认情况下，参数处于关闭状态，并保存最近15次的运行结果。

# 二 分析步骤 #

## 1 是否支持，看看当前 mysql 版本是否支持 ##

## 2 开启功能，默认是关闭，使用前需要开启 ##

## 3 运行 SQL ##

select * from emp group by id%10 limit 150000;
    select * from emp group by id%20 order by 5;

### 4 查看结果，show profiles; ###

mysql> show profiles;
    +----------+------------+-----------------------------------------------+
    | Query_ID | Duration   | Query                                         |
    +----------+------------+-----------------------------------------------+
    |        1 | 0.00133675 | show variables like 'profiling'               |
    |        2 | 0.42947575 | select * from emp group by id%10 limit 150000 |
    |        3 | 0.41530425 | select * from emp group by id%20 order by 5   |
    |        4 | 0.00130625 | show variables like 'profiling'               |
    +----------+------------+-----------------------------------------------+
    4 rows in set, 1 warning (0.00 sec)

## 5 诊断 SQL， show profile cpu,block io for query Query\_ID ##

mysql> show profile cpu,block io for query 2;
    +----------------------+----------+----------+------------+--------------+---------------+
    | Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
    +----------------------+----------+----------+------------+--------------+---------------+
    | starting             | 0.000108 | 0.000000 |   0.000000 |         NULL |          NULL |
    | checking permissions | 0.000005 | 0.000000 |   0.000000 |         NULL |          NULL |
    | Opening tables       | 0.000017 | 0.000000 |   0.000000 |         NULL |          NULL |
    | init                 | 0.000020 | 0.000000 |   0.000000 |         NULL |          NULL |
    | System lock          | 0.000008 | 0.000000 |   0.000000 |         NULL |          NULL |
    | optimizing           | 0.000003 | 0.000000 |   0.000000 |         NULL |          NULL |
    | statistics           | 0.000050 | 0.000000 |   0.000000 |         NULL |          NULL |
    | preparing            | 0.000009 | 0.000000 |   0.000000 |         NULL |          NULL |
    | Creating tmp table   | 0.000031 | 0.000000 |   0.000000 |         NULL |          NULL |
    | Sorting result       | 0.000003 | 0.000000 |   0.000000 |         NULL |          NULL |
    | executing            | 0.000002 | 0.000000 |   0.000000 |         NULL |          NULL |
    | Sending data         | 0.428955 | 0.406250 |   0.015625 |         NULL |          NULL |
    | Creating sort index  | 0.000058 | 0.000000 |   0.000000 |         NULL |          NULL |
    | end                  | 0.000006 | 0.000000 |   0.000000 |         NULL |          NULL |
    | query end            | 0.000008 | 0.000000 |   0.000000 |         NULL |          NULL |
    | removing tmp table   | 0.000009 | 0.000000 |   0.000000 |         NULL |          NULL |
    | query end            | 0.000006 | 0.000000 |   0.000000 |         NULL |          NULL |
    | closing tables       | 0.000007 | 0.000000 |   0.000000 |         NULL |          NULL |
    | freeing items        | 0.000160 | 0.000000 |   0.000000 |         NULL |          NULL |
    | cleaning up          | 0.000013 | 0.000000 |   0.000000 |         NULL |          NULL |
    +----------------------+----------+----------+------------+--------------+---------------+
    20 rows in set, 1 warning (0.00 sec)

常用查询类型

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY2hlbmdxaXVtaW5n_size_20_color_FFFFFF_t_70_g_se_x_16][]

show profile cpu,block io for query Query\_ID 这个是最常用的。

## 6 日常开发需要注意的结论 ##

当出现下面这些就需要注意。

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY2hlbmdxaXVtaW5n_size_20_color_FFFFFF_t_70_g_se_x_16 1][]

# 三 查询流程 #

mysql 的查询流程大致如下。

mysql 客户端通过协议与 mysql 服务器建连接，发送查询语句，先检查查询缓存，如果命中，直接返回结果， 否则进行语句解析,也就是说，在解析查询之前，服务器会先访问查询缓存(query cache)——它存储 SELECT 语句以及 相应的查询结果集。如果某个查询结果已经位于缓存中，服务器就不会再对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户即可，这将大大提高系统的性能。

语法解析器和预处理：首先 mysql 通过关键字将 SQL 语句进行解析，并生成一颗对应的“解析树”。mysql 解析器将使用 mysql 语法规则验证和解析查询；预处理器则根据一些 mysql 规则进一步检查解析数是否合法。

查询优化器当解析树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式， 最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

然后，mysql 默认使用的 BTREE 索引，并且一个大致方向是:无论怎么折腾 sql，至少在目前来说，mysql 最多只用到表中的一个索引。

[MySQL _ MySQL 5.7 Reference Manual _ 13.7.5.30 SHOW PROFILE Statement]: http://dev.mysql.com/doc/refman/5.7/en/show-profile.html
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY2hlbmdxaXVtaW5n_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/72fcba82dc5341838362789cbfcc081f.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY2hlbmdxaXVtaW5n_size_20_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220828/598bcfb2b291406bac90346f1a335c61.png