MySQL性能分析show profiles

show profile是由Jeremy Cole捐献给MySQL社区版本的。默认的是关闭的，但是会话级别可以开启这个功能。开启它可以让MySQL收集在执行语句的时候所使用的资源。为了统计报表,把profiling设为1
Profiling 功能由MySQL会话变量 : profiling控制,默认是OFF.关闭状态。select @@profiling; 或者show variables like '%profi%'；

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+

开启Profiling功能：

mysql> set profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

之后运行一个sql语句

SELECT * from support_base where id=266;

查看报告

这个执行语句的剖析信息存储在这个会话中。使用SHOW PROFILES进行查看。
show profiles :列表,显示最近发送到服务器上执行的语句的资源使用情况.显示的记录数由变量:profiling_history_size 控制,默认15条。Query_ID 是语句的id，Duration是语句执行总耗时。
修改显示条数：

set profiling_history_size =100;

mysql> show profiles;
+----------+------------+-------------------------------+
| Query_ID | Duration   | Query                         |
+----------+------------+-------------------------------+
|        1 | 0.00371475 | show variables like '%profi%' |
|        2 | 0.00005700 | show prifiles                 |
|        3 | 0.00011775 | SELECT DATABASE()             |
|        4 | 0.00034875 | select * from student         |
+----------+------------+-------------------------------+

show profile: 展示最近一条语句执行的详细资源占用信息,默认显示 Status和Duration两列

mysql> show PROFILE for QUERY 158;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000054 |
| checking permissions | 0.000007 |
| Opening tables       | 0.000116 |
| init                 | 0.000019 |
| System lock          | 0.000009 |
| optimizing           | 0.000004 |
| statistics           | 0.000011 |
| preparing            | 0.000010 |
| executing            | 0.000002 |
| Sending data         | 0.000061 |
| end                  | 0.000005 |
| query end            | 0.000006 |
| closing tables       | 0.000006 |
| freeing items        | 0.000031 |
| cleaning up          | 0.000010 |
+----------------------+----------+

剖析报告

剖析报告给出了查询执行的每个步骤及其话费的时间，看结果很烂快速确定那个步骤话费的时间最多，因为输出是按照执行顺序排列，但是无法通过诸如order by之类的命令重新排序，可以直接查询information_schema中的表来实现排序等操作

set @query_id = 158;
SELECT
    state,
    sum(duration) AS total_r,
    ROUND(
        100 * sum(duration) / (
            SELECT
                sum(duration)
            FROM
                information_schema.PROFILING
            WHERE
                QUERY_ID = @query_id
        ),
        2
    ) AS pct_r,
    count(*) AS calls,
    SUM(duration) / count(*) AS "r/call"
FROM
    information_schema.PROFILING
WHERE
    QUERY_ID =@query_id
GROUP BY
    STATE
ORDER BY
    total_r DESC

返回的结果为

image.png

然后就能很容易的看到各环节的执行耗时，并且做出针对优化