mysql profile 查谒_MySQL使用profile查询性能的操作教程

MYSQL的profiling功能要在Mysql版本5.0.37以上才能使用。

查看profile是否开启

mysql> show variables like '%profil%';

+------------------------+-------+

| Variable_name

| Value |

+------------------------+-------+

| profiling

| OFF |

--开启SQL语句剖析功能

| profiling_history_size | 15 |

--设置保留profiling的数目，缺省为15，范围为0至100，为0时将禁用profiling

+------------------------+-------+

2 rows in set (0.00 sec)

基于会话级别开启

mysql> set profiling = 1;

--关闭则用set profiling = off

Query OK, 0 rows affected (0.00 sec)

mysql> select distinct d.account,a.server_id from tab_appserver_user a

-> inner join tab_department_parent b on a.key_id = b.parent_id

-> inner join tab_department_member c on b.department_id = c.department_id and c.state=1

-> and c.isdefault=1 inner join tab_user_info d on c.user_id = d.user_id and d.state=1

-> where a.type=1

-> union

-> select distinct b.account,a.server_id from tab_appserver_user a

-> inner join tab_user_info b on a.key_id = b.user_id and b.state=1

-> where a.type=0;

查看是否设置生效：

select @@profiling;

默认是0，设置成功是1

运行SQL语句：

mysql> select * FROM hx_line WHERE id = '1455023';

查看profiles

mysql> show profiles;

+----------+------------+---------------------------------------------+

| Query_ID | Duration | Query

|

+----------+------------+---------------------------------------------+

| 1 | 0.00036150 | select * FROM hx_line WHERE id = '1455023' |

+----------+------------+---------------------------------------------+

查看具体某条的profile

mysql> show profile FOR QUERY 1;

+--------------------------------+----------+

| Status

| Duration |

+--------------------------------+----------+

| starting

| 0.000013 |

| Waiting for query cache lock | 0.000014 |

| checking query cache for query | 0.000038 |

| checking permissions

| 0.000006 |

| Opening tables

| 0.000013 |

| System lock

| 0.000009 |

| Waiting for query cache lock | 0.000024 |

| init

| 0.000060 |

| optimizing

| 0.000014 |

| statistics

| 0.000046 |

| preparing

| 0.000017 |

| executing

| 0.000004 |

| Sending data

| 0.000081 |

| end

| 0.000005 |

| query end

| 0.000004 |

| closing tables

| 0.000008 |

| freeing items

| 0.000009 |

| Waiting for query cache lock | 0.000003 |

| freeing items

| 0.000013 |

| Waiting for query cache lock | 0.000003 |

| freeing items

| 0.000003 |

| storing result in query cache | 0.000005 |

| logging slow query

| 0.000003 |

| cleaning up

| 0.000004 |

+--------------------------------+----------+

24 rows

我们看到了一个简单的查询，MYSQL内部做了24次操作。

另外，看到了一堆query cache的操作，试着把query_cache_size=0，把query_cache关闭，再次测试：

mysql> show profile FOR QUERY 1;

+----------------------+----------+

| Status

| Duration |

+----------------------+----------+

| starting

| 0.000040 |

| checking permissions | 0.000007 |

| Opening tables | 0.000015 |

| System lock

| 0.000010 |

| init

| 0.000061 |

| optimizing

| 0.000013 |

| statistics

| 0.000059 |

| preparing

| 0.000018 |

| executing

| 0.000004 |

| Sending data

| 0.000092 |

| end

| 0.000006 |

| query end

| 0.000004 |

| closing tables | 0.000008 |

| freeing items | 0.000020 |

| logging slow query | 0.000003 |

| cleaning up

| 0.000004 |

+----------------------+----------+

16 rows in set (0.00 sec)

当开启了query_cache的情况下，需要多操作6次，在这个示例里面多化了0.000087s。

查询这条语句对CPU的使用情况：

mysql> show profile cpu FOR QUERY 1;

+----------------------+----------+----------+------------+

| Status

| Duration | CPU_user | CPU_system |

+----------------------+----------+----------+------------+

| starting

| 0.000037 | 0.000000 | 0.000000 |

| checking permissions | 0.000009 | 0.000000 | 0.000000 |

| Opening tables | 0.000014 | 0.000000 | 0.000000 |

| System lock

| 0.000009 | 0.000000 | 0.000000 |

| init

| 0.000059 | 0.000000 | 0.000000 |

| optimizing

| 0.000009 | 0.000000 | 0.000000 |

| statistics

| 0.000044 | 0.000000 | 0.000000 |

| preparing

| 0.000015 | 0.000000 | 0.000000 |

| executing

| 0.000004 | 0.000000 | 0.000000 |

| Sending data

| 0.000081 | 0.000000 | 0.000000 |

| end

| 0.000006 | 0.000000 | 0.000000 |

| query end

| 0.000004 | 0.000000 | 0.000000 |

| closing tables | 0.000008 | 0.000000 | 0.000000 |

| freeing items | 0.000021 | 0.000000 | 0.000000 |

| logging slow query | 0.000004 | 0.000000 | 0.000000 |

| cleaning up

| 0.000004 | 0.000000 | 0.000000 |

+----------------------+----------+----------+------------+

查看io及cpu的消耗

mysql> show profile block io,cpu for query 1;