Heim >Datenbank >MySQL-Tutorial >mysql的sql性能分析器_MySQL

mysql的sql性能分析器_MySQL

WBOY
WBOYOriginal
2016-06-01 13:43:34922Durchsuche

bitsCN.com author:skate
time:2012/02/17
 
mysql的sql性能分析器
 
MySQL 的SQL性能分析器主要用途是显示SQL执行的整个过程中各项资源的使用情况。分析器可以更好的展示
出不良SQL的性能问题所在。
 
mysql sql profile的使用方法
 
1.开启mysql sql profile
 
检查mysql sql profile是否启用
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set (0.01 sec)
 
默认情况下profiling 的值为0表示MySQL SQL Profiler处于OFF状态,如果开启SQL性能分析器后,profiling 的值将为1.
 
mysql> set profiling=1;
Query OK, 0 rows affected (0.03 sec)
 
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
1 row in set (0.01 sec)
 
 
上面可以看到profiling已经变为1了,但是这个是session级别的,系统是不支持的。如下测试
 
退出mysql
mysql> quit
Bye
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or /g.
Your MySQL connection id is 3
Server version: 5.0.45-log Source distribution
 
Type 'help;' or '/h' for help. Type '/c' to clear the buffer.
 
查看profiling的值
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set (0.01 sec)
 
发现已经变为默认值0了,那如果设置系统级会如何呢?
 
mysql> set global profiling=1;
ERROR 1228 (HY000): Variable 'profiling' is a SESSION variable and can't be used with SET GLOBAL
mysql>
 
看到这里报错了。所以mysql sql profile是session级别的。
 
2. 举个例如,看如何使用
 
mysql> create table t5 as select * from t1;
ERROR 1046 (3D000): No database selected
mysql> use backup;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> create table t5 as select * from t1;
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> select count(*) from t5;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)
 
mysql> select count(*) from t5;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)
 
mysql> show profiles;
+----------+------------+-------------------------------------+
| Query_ID | Duration   | Query                               |
+----------+------------+-------------------------------------+
|        1 | 0.00382400 | select @@profiling                  |
|        2 | 0.00268500 | create table t5 as select * from t1 |
|        3 | 0.00017200 | SELECT DATABASE()                   |
|        4 | 0.01985400 | show databases                      |
|        5 | 0.00018900 | show tables                         |
|        6 | 0.06225200 | create table t5 as select * from t1 |
|        7 | 0.00368800 | select count(*) from t5             |
|        8 | 0.00322200 | select count(*) from t5             |
+----------+------------+-------------------------------------+
8 rows in set (0.01 sec)
 
mysql>
mysql> show profile for query 7;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| (initialization)   | 0.000414 |
| Opening tables     | 0.000599 |
| System lock        | 0.000254 |
| Table lock         | 0.000175 |
| init               | 0.000052 |
| optimizing         | 0.00001  |
| executing          | 0.002107 |
| end                | 0.000042 |
| query end          | 0.000005 |
| freeing items      | 0.000014 |
| closing tables     | 0.000011 |
| logging slow query | 0.000005 |
+--------------------+----------+
12 rows in set (0.03 sec)
 
mysql> show profile for query 8;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| (initialization)   | 0.000064 |
| Opening tables     | 0.000018 |
| System lock        | 0.00001  |
| Table lock         | 0.000013 |
| init               | 0.00002  |
| optimizing         | 0.00001  |
| executing          | 0.002589 |
| end                | 0.000459 |
| query end          | 0.000007 |
| freeing items      | 0.000015 |
| closing tables     | 0.000012 |
| logging slow query | 0.000005 |
+--------------------+----------+
12 rows in set (0.00 sec)
 
mysql> select sum(format(duration,6)) as duration from information_schema.profiling where query_id=7;
+----------+
| duration |
+----------+
| 0.003688 |
+----------+
1 row in set (0.02 sec)
 
mysql> select sum(format(duration,6)) as duration from information_schema.profiling where query_id=8;
+----------+
| duration |
+----------+
| 0.003222 |
+----------+
1 row in set (0.00 sec)
 
mysql>
 
从如上的信息可以看出这两个sql的profile统计信息里,前4项差别比较大,这是两个sql主要区别,第二次查询有很多
缓存了了。SQL 性能分析器可以帮助我们对一些比较难以确定性能问题的SQL 进行诊断,找出问题根源。
 
 
------end----- bitsCN.com

Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn