MySQL 5.0.37 以上开始支持 MySQL Query Profiler, 可以查询到此 SQL 会执行多少时间, 并看出 CPU/Memory 使用量, 执行过程中 System lock, Table lock 花多少时间等等.

启用 MySQL Query Profile

mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)

测试查询语句

mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
|      200 |
+----------+
1 row in set (0.00 sec)

查看 Profiles

mysql> show profiles;
+----------+------------+-----------------------------------------------+
| Query_ID | Duration   | Query                                         |
+----------+------------+-----------------------------------------------+
|        0 | 0.00007300 | set profiling=1                               |
|        1 | 0.00048366 | select count(*) from user |
+----------+------------+-----------------------------------------------+
2 rows in set (0.00 sec)

查看单条 Profile 详情

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000062 |
| checking permissions | 0.000011 |
| Opening tables       | 0.000023 |
| After opening tables | 0.000023 |
| System lock          | 0.000007 |
| Table lock           | 0.000005 |
| After table lock     | 0.000009 |
| init                 | 0.000028 |
| optimizing           | 0.000015 |
| statistics           | 0.000033 |
| preparing            | 0.000015 |
| executing            | 0.000007 |
| Sending data         | 0.000143 |
| end                  | 0.000009 |
| query end            | 0.000008 |
| closing tables       | 0.000012 |
| freeing items        | 0.000007 |
| updating status      | 0.000056 |
| cleaning up          | 0.000010 |
+----------------------+----------+
19 rows in set (0.00 sec)

查看 CPU 使用信息

mysql> show profile cpu for query 1;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting             | 0.000062 | 0.000033 |   0.000027 |
| checking permissions | 0.000011 | 0.000007 |   0.000005 |
| Opening tables       | 0.000023 | 0.000013 |   0.000010 |
| After opening tables | 0.000023 | 0.000012 |   0.000011 |
| System lock          | 0.000007 | 0.000004 |   0.000003 |
| Table lock           | 0.000005 | 0.000003 |   0.000002 |
| After table lock     | 0.000009 | 0.000005 |   0.000004 |
| init                 | 0.000028 | 0.000016 |   0.000013 |
| optimizing           | 0.000015 | 0.000008 |   0.000006 |
| statistics           | 0.000033 | 0.000019 |   0.000015 |
| preparing            | 0.000015 | 0.000008 |   0.000006 |
| executing            | 0.000007 | 0.000004 |   0.000003 |
| Sending data         | 0.000143 | 0.000079 |   0.000064 |
| end                  | 0.000009 | 0.000005 |   0.000004 |
| query end            | 0.000008 | 0.000005 |   0.000003 |
| closing tables       | 0.000012 | 0.000006 |   0.000005 |
| freeing items        | 0.000007 | 0.000004 |   0.000004 |
| updating status      | 0.000056 | 0.000050 |   0.000039 |
| cleaning up          | 0.000010 | 0.000005 |   0.000005 |
+----------------------+----------+----------+------------+
19 rows in set (0.00 sec)

除了CPU, 还可以下述属性查看不同的信息:

  • all 显示所有 Profile 信息
  • block io 显示数据快操作统计信息
  • ipc 显示发送和接收的消息的计数
  • memory 显示内存使用统计信息
  • page faults 显示主要和次要页面错误的计数
  • source 显示源代码中的函数名称,以及函数发生的文件的名称和行号
  • swaps 显示swap的统计信息

查看默认 Profile 记录条数

mysql > show variables where variable_name='profiling_history_size'; # 默认15条
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| profiling_history_size | 15    |
+------------------------+-------+
1 row in set (0.00 sec)

关闭 MySQL Query Profile

mysql > set profiling=0;
Query OK, 0 rows affected (0.00 sec)

【腾讯云】境外1核2G服务器低至2折,半价续费券限量免费领取!
https://cloud.tencent.com/act/cps/redirect?redirect=1068&cps_key=e4b50f6c64a4480367f8a8d16fd07c5a&from=console

标签: MySQL, MySQL Query Profile, 查询分析器

添加新评论