一、开启show profile功能
- 查看当前的状态
是否启用，看看当前的MySQL版本是否支持
默认是关闭，使用前需要开启show variables like ‘profiling‘;
或者
示例：show variables like ‘profiling%‘;mysql> show variables like ‘profiling‘; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | profiling | OFF | +---------------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> show variables like ‘profiling%‘; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | profiling | OFF | | profiling_history_size | 15 | +------------------------+-------+ 2 rows in set, 1 warning (0.00 sec) - 开启
默认该功能是关闭的，使用前需要开启
示例：set profiling=on;
目前只是临时设置，退出当前连接之后，设置失效。这种很耗性能的操作，没有必要一直开启。mysql> set profiling=on; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show variables like ‘profiling%‘; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | profiling | ON | | profiling_history_size | 15 | +------------------------+-------+ 2 rows in set, 1 warning (0.00 sec)
二、运行SQL并查看结果
- 运行SQL语句
运行需要测试SQL语句，比如这里我运行的SQL语句如下：select item_title from tb_orders group by item_title; select sleep(3); select * from tb_orders where id=1; - 查看结果
这里的记录了所有sql的执行记录的信息。mysql> show profiles; +----------+------------+------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+------------------------------------------------------+ | 1 | 0.00151275 | select item_title from tb_orders group by item_title | | 2 | 3.00110125 | select sleep(3) | | 3 | 0.00091525 | select * from tb_orders where id=1 | +----------+------------+------------------------------------------------------+ 3 rows in set, 1 warning (0.00 sec)Duration是该条SQL的运行时间。可以根据这个值来分辨哪个SQL比较耗时。
三、诊断SQL
在上面的粗略结果展示出来之后，我们能看出哪条SQL比较耗时。如果我们想知道耗时具体耗在哪里的话，可以使用SQL诊断获取更多更详细的信息。
我们以Query ID为1的那条语句为例。语法如下：
show profile [选项] for query [上一步前面的问题SQL数字号码];
示例：
mysql> show profile for query 1;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000073 |
| Executing hook on transaction | 0.000003 |
| starting | 0.000004 |
| checking permissions | 0.000004 |
| Opening tables | 0.000146 |
| init | 0.000004 |
| System lock | 0.000005 |
| optimizing | 0.000003 |
| statistics | 0.000009 |
| preparing | 0.000010 |
| Creating tmp table | 0.000022 |
| executing | 0.000002 |
| Sending data | 0.001181 |
| end | 0.000003 |
| query end | 0.000001 |
| waiting for handler commit | 0.000005 |
| removing tmp table | 0.000004 |
| waiting for handler commit | 0.000002 |
| closing tables | 0.000005 |
| freeing items | 0.000022 |
| cleaning up | 0.000008 |
+--------------------------------+----------+
21 rows in set, 1 warning (0.00 sec)
这里列举了数据库处理你的SQL语句详细步骤，包括每一步骤的耗时。可以参照这些数据对比分析自己的SQL的问题具体出在哪里。
分析的时候，CUP消耗和阻塞IO也是很重要的参考标准，可以增加参数以展示数据，示例如下：
mysql> show profile cpu, block io for query 1;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000073 | 0.000000 | 0.000000 | NULL | NULL |
| Executing hook on transaction | 0.000003 | 0.000000 | 0.000000 | NULL | NULL |
| starting | 0.000004 | 0.000000 | 0.000000 | NULL | NULL |
| checking permissions | 0.000004 | 0.000000 | 0.000000 | NULL | NULL |
| Opening tables | 0.000146 | 0.000000 | 0.000000 | NULL | NULL |
| init | 0.000004 | 0.000000 | 0.000000 | NULL | NULL |
| System lock | 0.000005 | 0.000000 | 0.000000 | NULL | NULL |
| optimizing | 0.000003 | 0.000000 | 0.000000 | NULL | NULL |
| statistics | 0.000009 | 0.000000 | 0.000000 | NULL | NULL |
| preparing | 0.000010 | 0.000000 | 0.000000 | NULL | NULL |
| Creating tmp table | 0.000022 | 0.000000 | 0.000000 | NULL | NULL |
| executing | 0.000002 | 0.000000 | 0.000000 | NULL | NULL |
| Sending data | 0.001181 | 0.000000 | 0.000000 | NULL | NULL |
| end | 0.000003 | 0.000000 | 0.000000 | NULL | NULL |
| query end | 0.000001 | 0.000000 | 0.000000 | NULL | NULL |
| waiting for handler commit | 0.000005 | 0.000000 | 0.000000 | NULL | NULL |
| removing tmp table | 0.000004 | 0.000000 | 0.000000 | NULL | NULL |
| waiting for handler commit | 0.000002 | 0.000000 | 0.000000 | NULL | NULL |
| closing tables | 0.000005 | 0.000000 | 0.000000 | NULL | NULL |
| freeing items | 0.000022 | 0.000000 | 0.000000 | NULL | NULL |
| cleaning up | 0.000008 | 0.000000 | 0.000000 | NULL | NULL |
+--------------------------------+----------+----------+------------+--------------+---------------+
21 rows in set, 1 warning (0.00 sec)
参数备注：
| type | 说明 |
|---|---|
| ALL | 显示所有的开销信息 |
| BLOCKIO | 显示块℃相关开销 |
| CONTEXTSWITCHES | 上下文切换相关开销 |
| CPU | 显示CPU相关开销信息 |
| IPC | 显示发送和接收相关开销信息 |
| MEMORY | 显示内存相关开销信息 |
| PAGE FAULTS | 显示页面错误相关开销信息 |
| SOURCE | 显示和Sourcefunction，Source蒯e，Source |
| SWAPS | 显示交换次数相关开销的信息 |
四、开发需要注意的事项
-
converting HEAP to MyISAM：查询结果太大，内存都不够用了往磁盘上搬了。 -
Creating tmp table：
创建临时表
拷贝数据到临时表
用完再删除 -
Copying to tmp table on disk：把内存中临时表复制到磁盘，危险！！！ -
locked
出现以上情况的时候，表示你的SQL很耗性能，必须要优化处理了。