MYSQL 使用show profiles 分析性能

MYSQL 使用show profiles 分析性能

Show profiles是5.0.37之后添加的，要想使用此功能，要确保版本在5.0.37之后。如果要使用此功能，先查看mysql版本是否高于5.0.37

查看数据库版本：

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.9-log |
+-----------+
1 row in set (0.00 sec)

说明此版本mysql具有此功能。

打开分析工具：

set @@prifileing=1;

执行查询：

mysql> select uid from u_user_master where `id_card` like '%233%';
+-----+
| uid |
+-----+
|  48 |
|  62 |
|  67 |
|  68 |
|  69 |
| 140 |
| 145 |
| 146 |
| 166 |
+-----+
9 rows in set (0.00 sec)

查看mysql性能

mysql> show profiles;
+----------+------------+------------------------------------------------------------+
| Query_ID | Duration   | Query                                                      |
+----------+------------+------------------------------------------------------------+
|        1 | 0.00033300 | select uid from u_user_master where `id_card` like '%233%' |
|        2 | 0.00005900 | show version()                                             |
|        3 | 0.00013300 | select version()                                           |
|        4 | 0.00041800 | select uid from u_user_master where `id_card` like '%233%' |
+----------+------------+------------------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)

第二列表示查询所用的时间。

根据Query_ID 查看某个查询的详细时间耗费

mysql> show profile for query 4;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000071 |
| checking permissions | 0.000007 |
| Opening tables       | 0.000016 |
| init                 | 0.000023 |
| System lock          | 0.000020 |
| optimizing           | 0.000010 |
| statistics           | 0.000016 |
| preparing            | 0.000012 |
| executing            | 0.000003 |
| Sending data         | 0.000148 |
| end                  | 0.000005 |
| query end            | 0.000006 |
| closing tables       | 0.000008 |
| freeing items        | 0.000020 |
| logging slow query   | 0.000041 |
| cleaning up          | 0.000012 |
+----------------------+----------+
16 rows in set, 1 warning (0.00 sec)

查看cpu、IO等信息

mysql> show profile block io, cpu for query 4;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000071 | 0.000065 |   0.000005 |            0 |             0 |
| checking permissions | 0.000007 | 0.000005 |   0.000002 |            0 |             0 |
| Opening tables       | 0.000016 | 0.000016 |   0.000001 |            0 |             0 |
| init                 | 0.000023 | 0.000021 |   0.000001 |            0 |             0 |
| System lock          | 0.000020 | 0.000009 |   0.000011 |            0 |             0 |
| optimizing           | 0.000010 | 0.000009 |   0.000002 |            0 |             0 |
| statistics           | 0.000016 | 0.000015 |   0.000001 |            0 |             0 |
| preparing            | 0.000012 | 0.000011 |   0.000001 |            0 |             0 |
| executing            | 0.000003 | 0.000001 |   0.000001 |            0 |             0 |
| Sending data         | 0.000148 | 0.000148 |   0.000001 |            0 |             0 |
| end                  | 0.000005 | 0.000002 |   0.000002 |            0 |             0 |
| query end            | 0.000006 | 0.000006 |   0.000001 |            0 |             0 |
| closing tables       | 0.000008 | 0.000007 |   0.000001 |            0 |             0 |
| freeing items        | 0.000020 | 0.000008 |   0.000012 |            0 |             0 |
| logging slow query   | 0.000041 | 0.000022 |   0.000019 |            0 |             0 |
| cleaning up          | 0.000012 | 0.000011 |   0.000001 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
16 rows in set, 1 warning (0.00 sec)

更详细的使用参考：https://dev.mysql.com/doc/refman/5.7/en/show-profiles.html