mysql 获取show 结果_[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：死锁。