SQL优化之Show Profiles

/ SQL优化 / 2 条评论 / 345人围观

题外话:我使用数据库可视化工具Navicat查询出来的参数竟然跟我直接xshell查出来的参数不一样,Navicat的版本是11.2.7-premium,以后修改参数还是在命令行修改,比较准确。

show profiles命令是在MySQL5.0以后才新加入的,是一个语句分析的利器。

如何使用

首先查看是否支持该功能。

show variables like 'have_profiling';

+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| have_profiling | YES   |
+----------------+-------+

默认是关闭的,开启该功能。

set profiling=on;

查看开启状态。15表示历史缓存sql的个数。

show variables like 'profiling%';

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| profiling              | ON    |
| profiling_history_size | 15    |
+------------------------+-------+

OK,接下来随便运行几条sql,并使用 show profiles 命令,将展示刚才运行的SQL语句。参数很好理解,不做解释了。

+----------+------------+----------------------------------+
| Query_ID | Duration   | Query                            |
+----------+------------+----------------------------------+
|        1 | 0.00106200 | show variables like 'profiling%' |
|        2 | 0.00044400 | select * from t_logs             |
|        3 | 0.00019350 | select * from t_commonts         |
|        4 | 0.00043725 | select * from t_comments         |
+----------+------------+----------------------------------+

下面分析指定的SQL语句,使用如下命令(4是上面查出来的Query_ID,cpu和block io代表cpu的处理时间和io的时间):

如果cpu时间慢,表示cpu的占用率比较高,io慢的话,表示内存占用率比较高。

show profile cpu,block io for query 4;

+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000072 | 0.000032 |   0.000033 |            0 |             0 |
| checking permissions | 0.000008 | 0.000003 |   0.000004 |            0 |             0 |
| Opening tables       | 0.000022 | 0.000011 |   0.000011 |            0 |             0 |
| init                 | 0.000038 | 0.000038 |   0.000000 |            0 |             0 |
| System lock          | 0.000009 | 0.000009 |   0.000000 |            0 |             0 |
| optimizing           | 0.000005 | 0.000005 |   0.000000 |            0 |             0 |
| statistics           | 0.000014 | 0.000014 |   0.000000 |            0 |             0 |
| preparing            | 0.000011 | 0.000010 |   0.000000 |            0 |             0 |
| executing            | 0.000003 | 0.000003 |   0.000000 |            0 |             0 |
| Sending data         | 0.000209 | 0.000209 |   0.000000 |            0 |             0 |
| end                  | 0.000004 | 0.000004 |   0.000000 |            0 |             0 |
| query end            | 0.000007 | 0.000006 |   0.000000 |            0 |             0 |
| closing tables       | 0.000009 | 0.000009 |   0.000000 |            0 |             0 |
| freeing items        | 0.000017 | 0.000017 |   0.000000 |            0 |             0 |
| cleaning up          | 0.000011 | 0.000011 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+

其中cpu和block io两个参数比较重要,还有其他参数,如下:

typedesc
ALL显示所有的开销信息
BLOCK IO显示与IO相关的开销
CONTEXT SWITCHES上下文切换相关开销
CPU与CPU相关的开销
IPC显示发送和接受相关的开销信息
MEMORY显示内存相关的开销信息
PAGE FAULTS显示页面错误相关开销信息
SOURCE显示和Source_function,SOURCE_file,SOURCE_line 相关的开销信息
SWAPS显示交换次数相关的开销信息

参数的分析

在show profile 的时候有一个字段叫Status,几个重要的参数如下:

状态描述
System lock确认是由于哪个锁引起的,通常是因为MySQL或InnoDB内核级
的锁引起的建议:如果耗时较大再关注即可,一般情况下都还好
Sending data从server端发送数据到客户端,也有可能是接收存储引擎层返回的数据
再发送给客户端,数据量很大时尤其经常能看见,
备注:Sending Data不是网络发送,是从硬盘读取,发送到网络是
Writing to net。建议:通过索引或加上LIMIT,减少需要扫描并且发送
给客户端的数据量
Sorting result正在对结果进行排序,类似Creating sort index,不过是正常表,
而不是在内存表中进行排序建议:创建适当的索引
Table lock表级锁,没什么好说的,要么是因为MyISAM引擎表级锁,
要么是其他情况显式锁表
create sort index当前的SELECT中需要用到临时表在进行ORDER BY排序。
建议:创建适当的索引
checking query cache for
querychecking privileges on
cachedsending cached result to
clienstoring result in query cache
和query cache相关的状态,已经多次强烈建议关闭

除了上述几个字段,如果Status出现了如下几个字段,说明SQL性能问题很严重。

  1. 膜拜大佬 6666

    回复

    @十二 878

    回复