MySQL调优利器【show profiles】

show profiles

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

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

分析步骤

1、查看当前版本sql是否支持show profiles

1
2
3
4
5
6
7
8
9
1mysql> show variables like 'profiling%';

2+------------------------+-------+

3| Variable_name          | Value |

4+------------------------+-------+

5| profiling              | OFF   |

6| profiling_history_size | 15    |

7+------------------------+-------+

8

9

2、开启该功能

1
2
3
4
1mysql> set global profiling=ON;

2Query OK, 0 rows affected, 1 warning (0.00 sec)

3

4

之后重新连接mysql或新建会话生效。

3、随便执行下sql

1
2
3
4
5
1## sql语句本身没太大含义 只是为了演示

2select * from emp group by id limit 150000;

3select * from emp group by id order by 5;

4

5

4、查看结果show profiles

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
1mysql> show profiles;

2+----------+------------+-------------------------------------------------------------+

3| Query_ID | Duration   | Query                                                       |

4+----------+------------+-------------------------------------------------------------+

5|        1 | 0.00492775 | select @@version_comment limit 1                            |

6|        2 | 0.00012475 | ues bigdata                                                 |

7|        3 | 0.00019650 | SELECT DATABASE()                                           |

8|        4 | 0.00989825 | show databases                                              |

9|        5 | 0.00027950 | show tables                                                 |

10|        6 | 0.00820250 | show variables like 'profiling%'                            |

11|        7 | 0.00034350 | select * from emp group by id%10 limit 150000               |

12|        8 | 0.00031925 | select id,empno,ename  from emp group by id%10 limit 150000 |

13|        9 | 0.13237350 | select id,empno,ename  from emp group by id limit 150000    |

14|       10 | 0.00033525 | select id,empno,ename  from emp group by id limit 5         |

15+----------+------------+-------------------------------------------------------------+

1610 rows in set, 1 warning (0.00 sec)

17

18

5、诊断sql

1
2
3
1show profile 参数 for query x(x表示show Profiles得到的query_id)

2

3

参数介绍:

1、ALL 显示所有的开销信息

2、BLOCK IO 显示块IO相关开销

3、CONTEXT SWITCHES 上下文切换相关开销

4、CPU 显示CPU相关开销信息

5、IPC 显示发送和接收相关开销信息

6、MEMORY 显示内存相关开销信息

7、PAGE FAULTS 显示页面错误相关开销信息

8、SOURCE 显示和Source_function、Source_file、Source_line相关的开销信息

9、SWAPS 显示交换次数相关开销的信息

举例：诊断Query_ID=9的SQL

1
2
3
1show profile all for query 9;

2

3

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
1mysql> show profile cpu,block io for query 9;

2+----------------------+----------+----------+------------+--------------+---------------+

3| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |

4+----------------------+----------+----------+------------+--------------+---------------+

5| starting             | 0.000084 | 0.000054 |   0.000009 |            0 |             0 |

6| checking permissions | 0.000007 | 0.000005 |   0.000001 |            0 |             0 |

7| Opening tables       | 0.000016 | 0.000014 |   0.000002 |            0 |             0 |

8| init                 | 0.000020 | 0.000018 |   0.000003 |            0 |             0 |

9| System lock          | 0.000009 | 0.000007 |   0.000001 |            0 |             0 |

10| optimizing           | 0.000004 | 0.000004 |   0.000001 |            0 |             0 |

11| statistics           | 0.000030 | 0.000025 |   0.000004 |            0 |             0 |

12| preparing            | 0.000014 | 0.000012 |   0.000002 |            0 |             0 |

13| Sorting result       | 0.000004 | 0.000003 |   0.000000 |            0 |             0 |

14| executing            | 0.000002 | 0.000002 |   0.000001 |            0 |             0 |

15| Sending data         | 0.132000 | 0.004110 |   0.127747 |            0 |             0 |

16| end                  | 0.000018 | 0.000009 |   0.000001 |            0 |             0 |

17| query end            | 0.000011 | 0.000009 |   0.000002 |            0 |             0 |

18| closing tables       | 0.000011 | 0.000009 |   0.000002 |            0 |             0 |

19| freeing items        | 0.000129 | 0.000000 |   0.000129 |            0 |             0 |

20| cleaning up          | 0.000016 | 0.000000 |   0.000015 |            0 |             0 |

21+----------------------+----------+----------+------------+--------------+---------------+

2216 rows in set, 1 warning (0.00 sec)

23

24

其中status表示sql执行生命周期，Duration表示该步骤的耗时

可以看出Sending data耗时较大。

注意:如果status中出现了以下内容，说明sql出现了性能问题

1、converting heap to MyISAM 查询结果太大，内存都不够用了 往磁盘上放

2、Creating tmp table 创建临时表，用完再删(group by 有时也会创建临时表)

3、copying to tmp table on disk 把内存中临时表复制到磁盘

4、locked 表被锁了