不知道大家之前对类似如何使用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 | 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查看语句运行时间这篇文章,大家觉得怎么样?如果想要了解更多相关,可以继续关注我们的行业资讯板块。
本文标题:如何使用MySQL查看语句运行时间
转载源于:http://www.cdkjz.cn/article/jpecop.html