show profile 进行 sql 分析

最新推荐文章于 2024-03-26 00:00:57 发布

chengqiuming

最新推荐文章于 2024-03-26 00:00:57 发布

阅读量267

点赞数

分类专栏： Mysql 文章标签： mysql

本文链接：https://blog.csdn.net/chengqiuming/article/details/120420591

版权

Mysql 专栏收录该内容

94 篇文章 7 订阅

订阅专栏

一点睛

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

官网：

MySQL :: MySQL 5.7 Reference Manual :: 13.7.5.30 SHOW PROFILE Statement

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

二分析步骤

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

# 默认是关闭，使用前需要开启
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

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

mysql> set profiling=on;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | ON    |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

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)

常用查询类型

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

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

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

三查询流程

mysql 的查询流程大致如下。

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

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

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

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