SHOW PROFILE分析SQL语句性能开销

最新推荐文章于 2024-01-17 23:10:07 发布

小老犇

最新推荐文章于 2024-01-17 23:10:07 发布

阅读量1.6k

点赞数 3

分类专栏：数据库文章标签：数据库 mysql profile sql开销 sql优化

本文链接：https://blog.csdn.net/numbbe/article/details/109520468

版权

数据库专栏收录该内容

31 篇文章 2 订阅

订阅专栏

前言

在项目中，我们用的sql语句，无非就是CRUD，小老犇统计了数百台数据库服务器（胡说八道），得出结论，它们百分之八十的性能用在了查询上，修改，新增，删除三个模块共同占了百分之二十。
所以，我们对SQL的优化，基本体现在select上。

步骤一：举例说明SQL时间概念

在这里我举几个例子来说明，让大家对sql执行时间有一个简单的概念。
场景：
我现在有两张表，一张表有5000条数据，另一张表有50000条数据。
audit_legal ：5000条数据。
resource：50000条数据。

案例一：查询1000条数据，两张表速度为什么不一样？。

在这里插入图片描述

一个用了21ms，一个用了243ms，得出结果。
就算查询同样的数据，如果基数不一样，那么查询耗时不同。
原因如下：
在没有索引的情况下，我们执行一条sql语句，表是进行全局遍历，磁盘寻址，就算逻辑上相邻的记录在磁盘上也并不一定是物理相邻的，基数越大，查询时间也就越长。

项目中访问接口的请求响应时间一般在200ms以内，高于200ms的接口，嘿嘿，要求不高就没问题。
注意一点：
直接在MySQL可视化界面执行sql的时间要远大于访问这个接口获得响应的时间，因为子弹要飞一会。

案例二：增加索引，查询效率提升有多少倍。
在resource这张表进行测试，
分两次：
1、通过主键进行查询。

在这里插入图片描述

2、通过普通字段进行查询。

在这里插入图片描述
用一张表，同一条数据：

一个几乎不耗时，0.00025s，另一个用了1.378s，所以索引很牛逼，如果有人问你，增加索引，查询效率提升有多少倍。那他一定是个锤子，不同基数，查询效率倍数不一样。
再问一个问题，MySQL数据库单表数据量为多少时，效率基本就达到了极限。
一千万条数据量，答案在这里：
https://blog.csdn.net/numbbe/article/details/109300087

步骤二：开启数据库分析功能

开启之前，先介绍一个英文单词：profiling
它的中文是分析。接下来会一直用到它。
输入命令行：
SHOW VARIABLES LIKE ‘%pro%’;
在这里插入图片描述
我们看到了两个参数：
profiling：
确定分析语句功能是否开启，mysql默认off/0关闭，on/1为开启。
开启后，之后执行的SQL，mysql服务器则会记录该条sql的系统开销，比如cpu，io，内存消耗等。
profiling_history_size：
保留分析profiling数量，范围为0-100，为0时，即关闭该功能。

步骤三：MySQL教你怎么玩profiling

我说的肯定没有mysql教的专业，所以我们看MySQL怎么说。
输入命令行：
HELP PROFILE;
在这里插入图片描述

我将mysql的example，拷贝到这里。
哇，好长啊，别看了，往下翻，我把尿文翻译一下。

NAME	description	EXAMPLE
SHOW PROFILE	Syntax:
SHOW PROFILE [TYPE [, TYPE] ... ]
    [FOR QUERY n]
    [LIMIT ROW_COUNT [OFFSET OFFSET]]

TYPE: {
    ALL
  | BLOCK IO
  | CONTEXT SWITCHES
  | CPU
  | IPC
  | MEMORY
  | PAGE FAULTS
  | SOURCE
  | SWAPS
}

The SHOW PROFILE AND SHOW PROFILES statements display profiling
information that indicates resource USAGE FOR statements executed
during the course of the current session.

*Note*:

The SHOW PROFILE AND SHOW PROFILES statements are deprecated AND will
be removed IN a future MySQL release. USE the Performance SCHEMA
instead; see
https://dev.mysql.com/doc/refman/5.7/en/performance-SCHEMA-QUERY-profil
ing.html.

TO control profiling, USE the profiling SESSION variable, which has a
DEFAULT VALUE of 0 (OFF). ENABLE profiling BY setting profiling TO 1 OR
ON:

mysql> SET profiling = 1;

SHOW PROFILES displays a LIST of the most recent statements sent TO the
server. The size of the LIST IS controlled BY the
profiling_history_size SESSION variable, which has a DEFAULT VALUE of
15. The maximum VALUE IS 100. Setting the VALUE TO 0 has the practical
effect of disabling profiling.

ALL statements are profiled except SHOW PROFILE AND SHOW PROFILES, so
you will find neither of those statements IN the PROFILE list.
Malformed statements are profiled. FOR EXAMPLE, SHOW PROFILING IS an
illegal statement, AND a syntax error occurs IF you try TO EXECUTE it,
but it will SHOW up IN the profiling list.

SHOW PROFILE displays detailed information about a single statement.
Without the FOR QUERY n clause, the output pertains TO the most
recently executed statement. IF FOR QUERY n IS included, SHOW PROFILE
displays information FOR statement n. The VALUES of n correspond TO the
Query_ID VALUES displayed BY SHOW PROFILES.

The LIMIT ROW_COUNT clause may be given TO LIMIT the output TO
ROW_COUNT rows. IF LIMIT IS given, OFFSET OFFSET may be added TO BEGIN
the output OFFSET ROWS INTO the FULL SET of rows.

BY DEFAULT, SHOW PROFILE displays STATUS AND Duration columns. The
STATUS VALUES are LIKE the State VALUES displayed BY SHOW PROCESSLIST,
although there might be SOME minor differences IN interpretion FOR the
two statements FOR SOME STATUS VALUES (see
https://dev.mysql.com/doc/refman/5.7/en/thread-information.html).

Optional TYPE VALUES may be specified TO display SPECIFIC additional
TYPES of information:

o ALL displays ALL information

o BLOCK IO displays counts FOR block input AND output operations

o CONTEXT SWITCHES displays counts FOR voluntary AND involuntary
  context switches

o CPU displays USER AND system CPU USAGE times

o IPC displays counts FOR messages sent AND received

o MEMORY IS NOT currently implemented

o PAGE FAULTS displays counts FOR major AND minor page faults

o SOURCE displays the NAMES of functions FROM the source CODE, together
  WITH the NAME AND line number of the FILE IN which the FUNCTION
  occurs

o SWAPS displays swap counts

Profiling IS enabled per session. WHEN a SESSION ENDS, its profiling
information IS lost.

URL: https://dev.mysql.com/doc/refman/5.7/en/SHOW-profile.html

	mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 ROW IN SET (0.00 sec)

mysql> SET profiling = 1;
QUERY OK, 0 ROWS affected (0.00 sec)

mysql> DROP TABLE IF EXISTS t1;
QUERY OK, 0 ROWS affected, 1 warning (0.00 sec)

mysql> CREATE TABLE T1 (id INT);
QUERY OK, 0 ROWS affected (0.01 sec)

mysql> SHOW PROFILES;
+----------+----------+--------------------------+
| Query_ID | Duration | QUERY                    |
+----------+----------+--------------------------+
|        0 | 0.000088 | SET PROFILING = 1        |
|        1 | 0.000136 | DROP TABLE IF EXISTS t1  |
|        2 | 0.011947 | CREATE TABLE t1 (id INT) |
+----------+----------+--------------------------+
3 ROWS IN SET (0.00 sec)

mysql> SHOW PROFILE;
+----------------------+----------+
| STATUS               | Duration |
+----------------------+----------+
| checking permissions | 0.000040 |
| creating TABLE       | 0.000056 |
| AFTER CREATE         | 0.011363 |
| QUERY END            | 0.000375 |
| freeing items        | 0.000089 |
| logging slow QUERY   | 0.000019 |
| cleaning up          | 0.000005 |
+----------------------+----------+
7 ROWS IN SET (0.00 sec)

mysql> SHOW PROFILE FOR QUERY 1;
+--------------------+----------+
| STATUS             | Duration |
+--------------------+----------+
| QUERY END          | 0.000107 |
| freeing items      | 0.000008 |
| logging slow QUERY | 0.000015 |
| cleaning up        | 0.000006 |
+--------------------+----------+
4 ROWS IN SET (0.00 sec)

mysql> SHOW PROFILE CPU FOR QUERY 2;
+----------------------+----------+----------+------------+
| STATUS               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| checking permissions | 0.000040 | 0.000038 |   0.000002 |
| creating TABLE       | 0.000056 | 0.000028 |   0.000028 |
| AFTER CREATE         | 0.011363 | 0.000217 |   0.001571 |
| QUERY END            | 0.000375 | 0.000013 |   0.000028 |
| freeing items        | 0.000089 | 0.000010 |   0.000014 |
| logging slow QUERY   | 0.000019 | 0.000009 |   0.000010 |
| cleaning up          | 0.000005 | 0.000003 |   0.000002 |
+----------------------+----------+----------+------------+
7 ROWS IN SET (0.00 sec)

这里讲了如何使用profile命令，介绍了它的语法，介绍了它的参数。

SHOW PROFILE	Syntax:

语法
SHOW PROFILE [TYPE [, TYPE] ... ]	type是下面的type
    [FOR QUERY n]					这里的n是sql语句代号。
    [LIMIT ROW_COUNT [OFFSET OFFSET]]

参数
TYPE: {
    ALL					展示所有的开销信息。
  | BLOCK IO			IO开销。
  | CONTEXT SWITCHES	上下文开销。
  | CPU					CPU开销。
  | IPC					发送和接受开销。
  | MEMORY				内存开销。
  | PAGE FAULTS			页面错误开销。
  | SOURCE				source开销。
  | SWAPS				交换次数开销。
}

举例说明：
SHOW PROFILE CPU FOR QUERY 2;	查询2号SQL的CPU开销情况。

下面是一段英译中，用CSDN翻译的（在CSDN里不给某翻译打广告），有能力的同学可以看上边的英文。
博主建议大家，将这段中文拷贝到自己的notepad++之类文件打开，这里看着实在不顺眼，太小了。
这段话最好还是看一看，不要因为我说是翻译的就不看。
里边不仅仅是翻译，有我自己的话在其中，有很多知识。
一共十一句话。


1、SHOW PROFILE和SHOW PROFILES语句根据配置文件显示语句在本次session期间执行的资源使用情况的信息。

2、SHOW PROFILE和SHOW PROFILES语句已弃用并将在未来的MySQL版本中删除。使用Performance Schema替代。
# 纳尼，以后就没profile这个东西了，大家散了吧。

3、要控制分析，请使用分析会话变量，该变量具有默认值为0（关闭）。通过将分析设置为1或启用分析on：

4、SHOW PROFILES显示发送到的最新语句的列表,服务器列表的大小由profile_history_size会话变量决定，默认值为15最大值为100。将值设置为0具有禁用分析的效果。

5、除SHOW PROFILE和SHOW PROFILES外，所有语句都将被分析，因此您在概要文件列表中找不到这些语句。

6、profile格式错误的语句。例如，SHOW PROFILING是一个非法语句，如果试图执行该语句，则会出现语法错误，但它会出现在分析列表中。

7、show profile语句显示有关单个语句的详细信息。如果没有for query n子句，则输出属于最近执行的语句。如果包含FOR QUERY n，则显示PROFILE，显示语句n的信息。n的值对应于查询SHOW PROFILES显示的ID值。

8、可以使用LIMIT ROW_COUNT子句将输出限制为数行。如果给定了限制，则可以在开始处添加偏移偏移量，将输出偏移行放入完整的行集合中。

9、默认情况下，show profile显示状态和持续时间列。即两个字段，状态值类似于SHOW PROCESSLIST显示的状态值，可以指定可选类型值来显示特定的附加值的信息类型。

10、show profile语句参数类型解释,和上边的九个参数一一对应.
ALL						全部显示所有信息

BLOCK IO				块IO显示块输入和输出操作的计数

CONTEXT SWITCHES		上下文切换

CPU						CPU显示用户和系统CPU使用时间

IPC						IPC显示发送和接收的消息计数

MEMORY					当前未实现内存

PAGE FAULTS				页面错误显示主要和次要页面错误的计数

SOURCE					SOURCE显示源代码中函数的名称或者函数所在文件的名称和行号

SWAPS					交换显示交换计数

11、为每个会话启用分析，会话结束时，其分析信息将丢失。

步骤四：解析MySQL的教学example。

执行命令行，开启sql分析功能：
SHOW VARIABLES LIKE ‘%pro%’;

SET profiling = 1;

SELECT @@profiling; 用来查看是否开启

在这里插入图片描述

执行命令：
SELECT COUNT(*) FROM laoben;
SHOW PROFILES;
可以看出上方select命令的query_id为5，消耗时间为0.00078257s。
在这里插入图片描述

执行命令：
SHOW PROFILE;
该命令显示的是上一条sql语句的执行情况，默认包括两个字段。
status，duration。状态和持续时间。
在这里插入图片描述

执行命令：指定字段，指定query_id，查看cpu,io开销。
SHOW PROFILE cpu,block io FOR QUERY 11;
在这里插入图片描述

小老犇

关注

3
点赞
踩
3

收藏

觉得还不错? 一键收藏
4
评论
SHOW PROFILE分析SQL语句性能开销

前言在项目中，我们用的sql语句，无非就是CRUD，小老犇统计了数百台数据库服务器（胡说八道），得出结论，它们百分之八十的性能用在了查询上，修改，新增，删除三个模块共同占了百分之二十。所以，我们对SQL的优化，基本体现在select上。步骤一：举例说明SQL时间概念在这里我举几个例子来说明，让大家对sql执行时间有一个简单的概念。场景：我现在有两张表，一张表有5000条数据，另一张表有50000条数据。audit_legal ：5000条数据。resource：50000条数据。案例一：查
复制链接

扫一扫