mysql执行查询的时间记录及查看

在mysql中，通常情况下，查询占据了大量的处理时间，要从这些查询中找出那些查询占据了大量的时间，导致mysql服务器cpu占据过大，可以通过配置系统参数来实现。

1. 查看配置

mysql> show variables like "%profiling%";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set

可以看到，默认情况下，profiling是关闭状态。

2. 开启profiling

mysql> set profiling = 1;
Query OK, 0 rows affected

3. 查看执行的每条查询sql的执行时间

mysql> show profiles;
+----------+------------+-------------------------------------------------------+
| Query_ID | Duration   | Query                                                 |
+----------+------------+-------------------------------------------------------+
|       22 | 0.01073875 | select sum(bill_count) from flink_merchant_stat_day   |
|       23 |  0.0001045 | show variables like profiling                         |
|       24 |  0.0001215 | show variables like profiling*                        |
|       25 | 0.00016975 | show variables = profiling                            |
|       26 | 0.00011025 | show variables profiling                              |
|       27 |    0.00012 | show variable profiling                               |
|       28 | 0.00270125 | show variables like "%profiling%"                     |
+----------+------------+-------------------------------------------------------+

4. 针对具体的sql进行进一步分析

mysql> show profile for query 22;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 6.9E-5   |
| checking permissions | 1.3E-5   |
| Opening tables       | 2.3E-5   |
| init                 | 2.5E-5   |
| System lock          | 1.5E-5   |
| optimizing           | 1.1E-5   |
| statistics           | 2.8E-5   |
| preparing            | 2.8E-5   |
| executing            | 8E-6     |
| Sending data         | 0.01043  |
| end                  | 1.4E-5   |
| query end            | 1.4E-5   |
| closing tables       | 1.2E-5   |
| freeing items        | 3.4E-5   |
| cleaning up          | 1.9E-5   |
+----------------------+----------+
15 rows in set

可以看出整个sql执行过程中，各个步骤的时间使用。

5. 关闭配置

分析完毕的时候，应关闭记录。

mysql> set profiling = 0;
Query OK, 0 rows affected