[mysql]查询截取分析-Show Profile

Show Profile

是什么

MySQL提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量.

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

分析步骤

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

mysql> show variables like 'profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+
2 rows in set (0.00 sec)

2开启功能

mysql> set profiling = on;
Query OK, 0 rows affected, 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 profile

mysql> show profiles;
+----------+------------+---------------------------------------------------+
| Query_ID | Duration   | Query                                             |
+----------+------------+---------------------------------------------------+
|        1 | 0.47962675 | SELECT * FROM `emp` GROUP BY `id`%10 LIMIT 150000 |
|        2 | 0.50838825 | SELECT * FROM `emp` GROUP BY `id`%20 ORDER BY 5   |
|        3 | 0.49408000 | SELECT * FROM `emp` GROUP BY `id`%20 ORDER BY 5   |
|        4 | 0.52304925 | SELECT * FROM `emp` GROUP BY `id`%20 ORDER BY 5   |
+----------+------------+---------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)

5诊断SQL

mysql> SHOW PROFILE cpu,block io FOR QUERY 4;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000095 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions | 0.000007 | 0.000000 |   0.000000 |            0 |             0 |
| Opening tables       | 0.000022 | 0.000000 |   0.000000 |            0 |             0 |
| init                 | 0.000029 | 0.000000 |   0.000000 |            0 |             0 |
| System lock          | 0.000016 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing           | 0.000009 | 0.000000 |   0.000000 |            0 |             0 |
| statistics           | 0.000019 | 0.000000 |   0.000000 |            0 |             0 |
| preparing            | 0.000010 | 0.000000 |   0.000000 |            0 |             0 |
| Creating tmp table   | 0.000035 | 0.000000 |   0.000000 |            0 |             0 |
| Sorting result       | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |
| executing            | 0.000002 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data         | 0.522402 | 0.520000 |   0.000000 |            0 |             0 |
| Creating sort index  | 0.000068 | 0.000000 |   0.000000 |            0 |             0 |
| end                  | 0.000005 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000008 | 0.000000 |   0.000000 |            0 |             0 |
| removing tmp table   | 0.000007 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |
| closing tables       | 0.000008 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items        | 0.000290 | 0.000000 |   0.000000 |            0 |             0 |
| cleaning up          | 0.000014 | 0.000000 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
20 rows in set, 1 warning (0.00 sec)

show profile查询参数备注：

ALL：显示所有的开销信息。
BLOCK IO：显示块IO相关开销 CONTEXT SWITCHES：上下文切换相关开销。 CPU：显示CPU相关开销信息 IPC：显示发送和接收相关开销信息。 MEMORY：显示内存相关开销信息。 PAGE FAULTS：显示页面错误相关开销信息。 SOURCE：显示和Source_function,Source_file,Source_line相关信息 SWAPS`：显示交换次数相关开销的信息。

6日常开发需要注意的结论（不要出现下面的情况）

• converting HEAP to MyISAM：查询结果太大，内存都不够用了，往磁盘上搬了。
• Creating tmp table：创建临时表：
  • 拷贝数据到临时表
  • 用完再删除
• Copying to tmp table on disk：把内存中的临时表复制到磁盘，危险！！！
• locked：死锁。