如何使用MySQL查看语句运行时间
不知道大家之前对类似如何使用MySQL查看语句运行时间的文章有无了解，今天我在这里给大家再简单的讲讲。感兴趣的话就一起来看看正文部分吧，相信看完如何使用MySQL查看语句运行时间你一定会有所收获的。
方法1> 使用 show profiles 进行查看
# 查看 profile 是不是打开的，默认是不打开
mysql> show variables like "%pro%";
+------------------------------------------+-------+
| Variable_name | Value |
+------------------------------------------+-------+
| check_proxy_users | OFF |
| have_profiling | YES |
| mysql_native_password_proxy_users | OFF |
| performance_schema_max_program_instances | -1 |
| profiling | OFF |
| profiling_history_size | 15 |
| protocol_version | 10 |
| proxy_user | |
| sha256_password_proxy_users | OFF |
| slave_compressed_protocol | OFF |
| stored_program_cache | 256 |
+------------------------------------------+-------+
11 rows in set (0.01 sec)
# 开启 profile
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
# 查询所有语句的执行时间
mysql> show profiles;
+----------+------------+----------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 0.02008300 | SELECT a.* FROM (SELECT t1.*,(SELECT COUNT(*)+1 FROM a WHERE name=t1.name AND age<t1.age) AS group_id FROM a t1) a WHERE a.group_id<=2 |
| 2 | 0.00034425 | SELECT * FROM a |
+----------+------------+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
# 即可查看第1个 sql语句 执行的各个操作的耗时详情
mysql> show profile for query 1;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000151 |
| checking permissions | 0.000009 |
| checking permissions | 0.000005 |
| Opening tables | 0.019543 |
| init | 0.000080 |
| System lock | 0.000021 |
| optimizing | 0.000003 |
| optimizing | 0.000002 |
| statistics | 0.000011 |
| preparing | 0.000022 |
| optimizing | 0.000009 |
| statistics | 0.000009 |
| preparing | 0.000008 |
| statistics | 0.000007 |
| preparing | 0.000003 |
| executing | 0.000007 |
| Sending data | 0.000006 |
| executing | 0.000001 |
| Sending data | 0.000033 |
| executing | 0.000002 |
| Sending data | 0.000021 |
| executing | 0.000003 |
| Sending data | 0.000008 |
| executing | 0.000002 |
| Sending data | 0.000006 |
| executing | 0.000002 |
| Sending data | 0.000007 |
| executing | 0.000002 |
| Sending data | 0.000007 |
| executing | 0.000002 |
| Sending data | 0.000007 |
| executing | 0.000002 |
| Sending data | 0.000005 |
| executing | 0.000002 |
| Sending data | 0.000014 |
| end | 0.000003 |
| query end | 0.000006 |
| closing tables | 0.000002 |
| removing tmp table | 0.000003 |
| closing tables | 0.000004 |
| freeing items | 0.000036 |
| cleaning up | 0.000011 |
+----------------------+----------+
42 rows in set, 1 warning (0.00 sec)
方法2> 使用 timestampdiff 来查看执行时间
mysql> DELIMITER ;;
mysql> SET @d=now();
-> SELECT * FROM a;
-> SELECT TIMESTAMPDIFF(second,@d,NOW());;
Query OK, 0 rows affected (0.00 sec)
+----+-------+------+-------+
| id | name | age | work |
+----+-------+------+-------+
| 1 | name1 | 12 | work1 |
| 2 | name2 | 14 | work2 |
| 3 | name1 | 15 | work3 |
| 4 | name1 | 16 | work4 |
| 5 | name3 | 17 | work5 |
| 6 | name1 | 18 | work6 |
| 7 | name4 | 19 | work7 |
| 8 | name1 | 22 | work8 |
+----+-------+------+-------+
8 rows in set (0.00 sec)
+--------------------------------+
| timestampdiff(second,@d,now()) |
+--------------------------------+
| 0 |
+--------------------------------+
1 row in set (0.00 sec)
mysql> DELIMITER ;
看完如何使用MySQL查看语句运行时间这篇文章，大家觉得怎么样？如果想要了解更多相关，可以继续关注我们的行业资讯板块。
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
