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 ;