这篇文章主要介绍了MySQL使用profile查询性能的操作教程,不仅是SQL语句的性能,更能够收集系统运行的CPU及内存占用情况,需要的朋友可以参考下 MYSQL的profiling功能要在Mysql版本5.0.37以上才
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;
+------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | profiling | OFF | --开启SQL语句剖析功能 | profiling_history_size | 15 | --设置保留profiling的数目,缺省为15,范围为0至100,为0时将禁用profiling +------------------------+-------+ 2 rows in set (0.00 sec) 0
我们看到了一个简单的查询,MYSQL内部做了24次操作。
另外,看到了一堆query cache的操作,试着把query_cache_size=0,把query_cache关闭,再次测试:
mysql> show profile FOR QUERY 1;
+------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | profiling | OFF | --开启SQL语句剖析功能 | profiling_history_size | 15 | --设置保留profiling的数目,缺省为15,范围为0至100,为0时将禁用profiling +------------------------+-------+ 2 rows in set (0.00 sec) 2
当开启了query_cache的情况下,需要多操作6次,在这个示例里面多化了0.000087s。
查询这条语句对CPU的使用情况:
+------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | profiling | OFF | --开启SQL语句剖析功能 | profiling_history_size | 15 | --设置保留profiling的数目,缺省为15,范围为0至100,为0时将禁用profiling +------------------------+-------+ 2 rows in set (0.00 sec) 3
+------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | profiling | OFF | --开启SQL语句剖析功能 | profiling_history_size | 15 | --设置保留profiling的数目,缺省为15,范围为0至100,为0时将禁用profiling +------------------------+-------+ 2 rows in set (0.00 sec) 4
查看io及cpu的消耗
+------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | profiling | OFF | --开启SQL语句剖析功能 | profiling_history_size | 15 | --设置保留profiling的数目,缺省为15,范围为0至100,为0时将禁用profiling +------------------------+-------+ 2 rows in set (0.00 sec) 5
+------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | profiling | OFF | --开启SQL语句剖析功能 | profiling_history_size | 15 | --设置保留profiling的数目,缺省为15,范围为0至100,为0时将禁用profiling +------------------------+-------+ 2 rows in set (0.00 sec) 6
使用查询语句对消耗进行排序
+------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | profiling | OFF | --开启SQL语句剖析功能 | profiling_history_size | 15 | --设置保留profiling的数目,缺省为15,范围为0至100,为0时将禁用profiling +------------------------+-------+ 2 rows in set (0.00 sec) 7
+------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | profiling | OFF | --开启SQL语句剖析功能 | profiling_history_size | 15 | --设置保留profiling的数目,缺省为15,范围为0至100,为0时将禁用profiling +------------------------+-------+ 2 rows in set (0.00 sec) 8
show profile额外一些命令:
* ALL - displays all information
* BLOCK IO - displays counts for block input and output Operations
* CONTEXT SWITCHES - displays counts for voluntary and involuntary context switches
* ipC - displays counts for messages sent and received
* MEMORY - is not currently implemented
* PAGE FAULTS - displays counts for major and minor page faults
* SOURCE - displays the names of functions from the source code, together with the name and line number of the file in which the function occurs
* SWAPS - displays swap counts
最后说明:profile是一个非常量化的子标,可以根据这些量化指标来比较各项资源的消耗,有利于我们对该语句的整体把控!
注:相关教程知识阅读请移步到MYSQL教程频道。