mysql in 性能分析_mysql中使用profile对sql语句进行性能分析

在mysql的使用过程中，很多时候就要牵扯到mysql的性能调优，这时候profile就会派上用场了，profile可以查询到 SQL 语句的执行情况，以及执行过程中的每个步骤所消耗的时间, 并能查看 CPU和内存的使用情况，有了这些内容我们就可以以此为基础，找到一个sql的问题瓶颈。

1.查看profile是否开启

mysql> show variables like '%profil%';

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

| Variable_name | Value |

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

| have_profiling | YES |

| profiling | OFF | --开启SQL语句剖析功

| profiling_history_size | 15 | --设置保留profiling的数目，缺省为15，

+------------------------+-------+ 范围为0至100，为0时将禁用profiling

3 rows in set (0.00 sec)

2.基于会话级别开启

mysql> set profiling = 1;

Query OK, 0 rows affected (0.00 sec)

关闭则用set profiling = off；

mysql> show variables like '%profil%';

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

| Variable_name | Value |

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

| have_profiling | YES |

| profiling | ON |

| profiling_history_size | 15 |

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

3 rows in set (0.00 sec)

3.查询

mysql> SELECT

-> id,appid,phone

-> FROM cbh_account ct

-> limit 0,10;

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

| id | appid | phone |

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

| 10599 | 800120141120133821 | 15111111111 |

| 10600 | 800120141120133821 | 15111111111 |

| 10601 | 800120141120142804 | 15111111111 |

| 10602 | 800120141120142804 | 15111111111 |

| 10603 | 800120141120142804 | 15111111111 |

| 10604 | 800120141120142804 | 15111111111 |

| 10605 | 800120141120142937 | 15111111111 |

| 10606 | 800120141120142937 | 15111111111 |

| 10607 | 800120141120142937 | 15111111111 |

| 10608 | 800120141120142937 | 15111111111 |

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

10 rows in set (0.00 sec)

4.显示缓存的profile

mysql> show profiles;

5.具体消耗，进行详细的列出

mysql> show profile for query 2;

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

| Status | Duration |

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

| starting | 0.003575 |

| checking permissions | 0.000009 |

| checking permissions | 0.000019 |

| Opening tables | 0.025834 |

| System lock | 0.000310 |

| init | 0.000411 |

| optimizing | 0.000054 |

| statistics | 0.000886 |

| preparing | 0.000109 |

| executing | 0.000012 |

| Sorting result | 0.000003 |

| Sending data | 0.005335 |

| end | 0.000014 |

| query end | 0.000008 |

| closing tables | 0.000020 |

| freeing items | 0.000126 |

| logging slow query | 0.000001 |

| cleaning up | 0.000002 |

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

18 rows in set (0.00 sec)

6.查看cpu的消耗情况

mysql> show profile cpu for query 2;

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

| Status | Duration | CPU_user | CPU_system |

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

| starting | 0.003575 | 0.000000 | 0.000000 |

| checking permissions | 0.000009 | 0.000000 | 0.000000 |

| checking permissions | 0.000019 | 0.000000 | 0.000000 |

| Opening tables | 0.025834 | 0.000000 | 0.000000 |

| System lock | 0.000310 | 0.000000 | 0.000000 |

| init | 0.000411 | 0.000000 | 0.000000 |

| optimizing | 0.000054 | 0.000000 | 0.000000 |

| statistics | 0.000886 | 0.000000 | 0.000000 |

| preparing | 0.000109 | 0.000000 | 0.000000 |

| executing | 0.000012 | 0.000000 | 0.000000 |

| Sorting result | 0.000003 | 0.000000 | 0.000000 |

| Sending data | 0.005335 | 0.000000 | 0.000000 |

| end | 0.000014 | 0.000000 | 0.000000 |

| query end | 0.000008 | 0.000000 | 0.000000 |

| closing tables | 0.000020 | 0.000000 | 0.000000 |

| freeing items | 0.000126 | 0.000000 | 0.000000 |

| logging slow query | 0.000001 | 0.000000 | 0.000000 |

| cleaning up | 0.000002 | 0.000000 | 0.000000 |

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

18 rows in set (0.00 sec)

7.查看内存消耗

mysql> show profile memory for query 2;

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

| Status | Duration |

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

| starting | 0.003575 |

| checking permissions | 0.000009 |

| checking permissions | 0.000019 |

| Opening tables | 0.025834 |

| System lock | 0.000310 |

| init | 0.000411 |

| optimizing | 0.000054 |

| statistics | 0.000886 |

| preparing | 0.000109 |

| executing | 0.000012 |

| Sorting result | 0.000003 |

| Sending data | 0.005335 |

| end | 0.000014 |

| query end | 0.000008 |

| closing tables | 0.000020 |

| freeing items | 0.000126 |

| logging slow query | 0.000001 |

| cleaning up | 0.000002 |

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

18 rows in set (0.00 sec)

8.查看io及cpu的消耗

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

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

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

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

| starting | 0.003575 | 0.000000 | 0.000000 | NULL | NULL |

| checking permissions | 0.000009 | 0.000000 | 0.000000 | NULL | NULL |

| checking permissions | 0.000019 | 0.000000 | 0.000000 | NULL | NULL |

| Opening tables | 0.025834 | 0.000000 | 0.000000 | NULL | NULL |

| System lock | 0.000310 | 0.000000 | 0.000000 | NULL | NULL |

| init | 0.000411 | 0.000000 | 0.000000 | NULL | NULL |

| optimizing | 0.000054 | 0.000000 | 0.000000 | NULL | NULL |

| statistics | 0.000886 | 0.000000 | 0.000000 | NULL | NULL |

| preparing | 0.000109 | 0.000000 | 0.000000 | NULL | NULL |

| executing | 0.000012 | 0.000000 | 0.000000 | NULL | NULL |

| Sorting result | 0.000003 | 0.000000 | 0.000000 | NULL | NULL |

| Sending data | 0.005335 | 0.000000 | 0.000000 | NULL | NULL |

| end | 0.000014 | 0.000000 | 0.000000 | NULL | NULL |

| query end | 0.000008 | 0.000000 | 0.000000 | NULL | NULL |

| closing tables | 0.000020 | 0.000000 | 0.000000 | NULL | NULL |

| freeing items | 0.000126 | 0.000000 | 0.000000 | NULL | NULL |

| logging slow query | 0.000001 | 0.000000 | 0.000000 | NULL | NULL |

| cleaning up | 0.000002 | 0.000000 | 0.000000 | NULL | NULL |

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

18 rows in set (0.00 sec)

profile是一个非常量化的指标，可以根据这些量化指标来比较各项资源的消耗，这样就有利于我们对该sql语句的整体把控！

本文固定链接：心知博客 » mysql中使用profile对sql语句进行性能分析

本站内容除特别标注外均为原创，欢迎转载，但请保留出处！