一般用于会话级,用于收集SQL语句的资源使用情况。
1.首先要设置系统变量profiling为on(作用于当前会话)
mysql> show variables like 'profiling%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+
2 rows in set (0.01 sec)
mysql> set profiling=on;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
2.执行SQL语句
mysql> select * from user where user_id=2222;
+---------+--------------+---------------+---------------------+
| user_id | user_name | user_email | created |
+---------+--------------+---------------+---------------------+
| 2222 | bill2222 | 123456@qq.com | 2020-02-09 12:24:32 |
+---------+--------------+---------------+---------------------+
1 row in set (0.00 sec)
3.通过show profiles查看
mysql> show profiles;
+----------+------------+---------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------+
| 1 | 0.00041000 | select database() |
| 2 | 0.00018500 | SELECT DATABASE() |
| 3 | 0.00382575 | show databases |
| 4 | 0.00040850 | show tables |
| 5 | 0.00106125 | show tables |
| 6 | 0.00211250 | desc user |
| 7 | 0.00031950 | SELECT * FROM USER WHERE user_id=2222 |
| 8 | 0.00019250 | SELECT DATABASE() |
| 9 | 0.00018200 | SELECT * FROM USER WHERE user_id=2222 |
| 10 | 0.00040700 | show databases |
| 11 | 0.00016275 | SELECT DATABASE() |
| 12 | 0.00031225 | SELECT * FROM USER WHERE user_id=2222 |
| 13 | 0.00041075 | show tables |
| 14 | 0.00031975 | select * from user where user_id=2222 |
+----------+------------+---------------------------------------+
14 rows in set, 1 warning (0.00 sec)
4.通过show profile for query 查看指定的SQL语句
mysql> show profile for query 14;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000074 |
| checking permissions | 0.000015 |
| Opening tables | 0.000023 |
| init | 0.000029 |
| System lock | 0.000013 |
| optimizing | 0.000012 |
| statistics | 0.000061 |
| preparing | 0.000012 |
| executing | 0.000005 |
| Sending data | 0.000017 |
| end | 0.000005 |
| query end | 0.000010 |
| closing tables | 0.000009 |
| freeing items | 0.000020 |
| cleaning up | 0.000015 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)
mysql>
6.查看SQL语句的CPU及资源情况
mysql> show profile cpu,source for query 14;
+----------------------+----------+----------+------------+-----------------------+----------------------+-------------+
| Status | Duration | CPU_user | CPU_system | Source_function | Source_file | Source_line |
+----------------------+----------+----------+------------+-----------------------+----------------------+-------------+
| starting | 0.000074 | 0.000046 | 0.000025 | NULL | NULL | NULL |
| checking permissions | 0.000015 | 0.000008 | 0.000004 | check_access | sql_authorization.cc | 809 |
| Opening tables | 0.000023 | 0.000015 | 0.000008 | open_tables | sql_base.cc | 5753 |
| init | 0.000029 | 0.000019 | 0.000009 | handle_query | sql_select.cc | 128 |
| System lock | 0.000013 | 0.000009 | 0.000005 | mysql_lock_tables | lock.cc | 330 |
| optimizing | 0.000012 | 0.000007 | 0.000004 | optimize | sql_optimizer.cc | 158 |
| statistics | 0.000061 | 0.000041 | 0.000022 | optimize | sql_optimizer.cc | 374 |
| preparing | 0.000012 | 0.000007 | 0.000003 | optimize | sql_optimizer.cc | 482 |
| executing | 0.000005 | 0.000003 | 0.000002 | exec | sql_executor.cc | 126 |
| Sending data | 0.000017 | 0.000011 | 0.000005 | exec | sql_executor.cc | 202 |
| end | 0.000005 | 0.000003 | 0.000003 | handle_query | sql_select.cc | 206 |
| query end | 0.000010 | 0.000007 | 0.000003 | mysql_execute_command | sql_parse.cc | 4956 |
| closing tables | 0.000009 | 0.000006 | 0.000003 | mysql_execute_command | sql_parse.cc | 5009 |
| freeing items | 0.000020 | 0.000013 | 0.000007 | mysql_parse | sql_parse.cc | 5622 |
| cleaning up | 0.000015 | 0.000010 | 0.000005 | dispatch_command | sql_parse.cc | 1931 |
+----------------------+----------+----------+------------+-----------------------+----------------------+-------------+
15 rows in set, 1 warning (0.00 sec)
mysql>
还可以使用下面的语句查看更多的信息:
mysql> show profile all for query 14;