mysql sql语句运行时间查询_mysql sql语句执行时间查询

第一种：

开启profile

1    mysql> set profiling=1;

2    Query OK, 0 rows affected (0.00 sec)

eg：

1    mysql> select * from test_1;

2    mysql> show profiles;

3    +----------+------------+----------------------+

4    | Query_ID | Duration   | Query                |

5    +----------+------------+----------------------+

6    |        1 | 0.84718100 | select * from test_1 |

7    +----------+------------+----------------------+

8    1 row in set (0.00 sec)

3  mysql> show profile for query 1;   #对进程1的详细查询

+--------------------------------+----------+

| Status                         | Duration |

+--------------------------------+----------+

| starting                       | 0.000042 |

| Waiting for query cache lock   | 0.000006 |

| checking query cache for query | 0.000009 |

| checking privileges on cached  | 0.000006 |

| checking permissions           | 0.000033 |

| sending cached result to clien | 0.000013 |

| logging slow query             | 0.000005 |

| cleaning up                    | 0.000006 |

+--------------------------------+----------+

8 rows in set (0.00 sec)

4 mysql> show profile cpu for query 1; #对进程1使用CPU情况查询

+--------------------------------+----------+----------+------------+

| Status                         | Duration | CPU_user | CPU_system |

+--------------------------------+----------+----------+------------+

| starting                       | 0.000042 | 0.000000 |   0.000000 |

| Waiting for query cache lock   | 0.000006 | 0.000000 |   0.000000 |

| checking query cache for query | 0.000009 | 0.000000 |   0.000000 |

| checking privileges on cached  | 0.000006 | 0.000000 |   0.000000 |

| checking permissions           | 0.000033 | 0.000000 |   0.000000 |

| sending cached result to clien | 0.000013 | 0.000000 |   0.000000 |

| logging slow query             | 0.000005 | 0.000000 |   0.000000 |

| cleaning up                    | 0.000006 | 0.000000 |   0.000000 |

+--------------------------------+----------+----------+------------+

8 rows in set (0.00 sec)

5 mysql> set profiling=0;   #退出查询 第二种：(通过时间差查看) delimiter // set @d=now(); select * from comment; select timestampdiff(second,@d,now()); delimiter ; Query OK, 0 rows affected (1 min 55.58 sec) +----------------------------------+ | timestampdiff(second, @d, now()) | +----------------------------------+ |                                2 | +----------------------------------+ 1 row in set (1 min 55.58 sec)