sql优化之Show Profile分析（重点）携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第2

携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第24天，点击查看活动详情

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

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

分析步骤：

1、是否支持，看看当前的mysql版本是否支持：show variables like 'profiling';

默认是关闭，使用前需要开启
2、开启功能，默认是关闭，使用前需要开启：set profiling=on;

3、运行SQL（随便运行用来测试）

mysql> select * from emp group by id%10 limit 150000;

mysql> select * from emp group by id%20 order by 5;

4、查看结果：show profiles;

mysql> show profiles;
+----------+------------+-----------------------------------------------+
| Query_ID | Duration   | Query                                         |
+----------+------------+-----------------------------------------------+
|        1 | 0.00204000 | show variables like 'profiling'               |
|        2 | 0.55134250 | select * from emp group by id%10 limit 150000 |
|        3 | 0.56902000 | select * from emp group by id%20 order by 5   |
+----------+------------+-----------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

5、诊断SQL，show profile cpu,block io for query ID号;（ID号为第4步Query_ID列中数字）

mysql> show profile cpu,block io for query 3;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000049 | 0.000000 |   0.000000 |         NULL |          NULL |
| checking permissions | 0.000005 | 0.000000 |   0.000000 |         NULL |          NULL |
| Opening tables       | 0.000012 | 0.000000 |   0.000000 |         NULL |          NULL |
| init                 | 0.000021 | 0.000000 |   0.000000 |         NULL |          NULL |
| System lock          | 0.000009 | 0.000000 |   0.000000 |         NULL |          NULL |
| optimizing           | 0.000003 | 0.000000 |   0.000000 |         NULL |          NULL |
| statistics           | 0.000017 | 0.000000 |   0.000000 |         NULL |          NULL |
| preparing            | 0.000008 | 0.000000 |   0.000000 |         NULL |          NULL |
| Creating tmp table   | 0.000045 | 0.000000 |   0.000000 |         NULL |          NULL |
| Sorting result       | 0.000004 | 0.000000 |   0.000000 |         NULL |          NULL |
| executing            | 0.000002 | 0.000000 |   0.000000 |         NULL |          NULL |
| Sending data         | 0.568704 | 0.546875 |   0.046875 |         NULL |          NULL |
| Creating sort index  | 0.000048 | 0.000000 |   0.000000 |         NULL |          NULL |
| end                  | 0.000003 | 0.000000 |   0.000000 |         NULL |          NULL |
| query end            | 0.000005 | 0.000000 |   0.000000 |         NULL |          NULL |
| removing tmp table   | 0.000006 | 0.000000 |   0.000000 |         NULL |          NULL |
| query end            | 0.000003 | 0.000000 |   0.000000 |         NULL |          NULL |
| closing tables       | 0.000004 | 0.000000 |   0.000000 |         NULL |          NULL |
| freeing items        | 0.000061 | 0.000000 |   0.000000 |         NULL |          NULL |
| cleaning up          | 0.000015 | 0.000000 |   0.000000 |         NULL |          NULL |
+----------------------+----------+----------+------------+--------------+---------------+
20 rows in set, 1 warning (0.00 sec)

参数备注（写在代码中）：show profile cpu,block io for query 3;（如此代码中的cpu,block）

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

6、日常开发需要注意的结论（Status列中的出现此四个问题严重）
- converting HEAP to MyISAM：查询结果太大，内存都不够用了往磁盘上搬了。
- Creating tmp table：创建临时表，拷贝数据到临时表，用完再删除
- Copying to tmp table on disk：把内存中临时表复制到磁盘，危险!
- locked：锁了