mysql--sql常用语句

通过profile命令来查看当前最主要的耗费时间的步骤。

mysql> select count(1) from t1;
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (0.11 sec)
 
mysql> show profiles;
+----------+------------+-------------------------+
| Query_ID | Duration   | Query                   |
+----------+------------+-------------------------+
|        1 | 0.00123300 | show databases          |
|        2 | 0.00016775 | SELECT DATABASE()       |
|        3 | 0.00092900 | show databases          |
|        4 | 0.00122325 | show tables             |
|        5 | 0.00134250 | show tables             |
|        6 | 0.11396400 | select count(1) from t1 |
+----------+------------+-------------------------+
6 rows in set, 1 warning (0.00 sec)
 
mysql> show profile for query 6; 
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000089 |
| Executing hook on transaction  | 0.000013 |
| starting                       | 0.000011 |
| checking permissions           | 0.000008 |
| Opening tables                 | 0.000040 |
| init                           | 0.000015 |
| System lock                    | 0.000015 |
| optimizing                     | 0.000007 |
| statistics                     | 0.000024 |
| preparing                      | 0.000029 |
| executing                      | 0.113622 |
| end                            | 0.000011 |
| query end                      | 0.000006 |
| waiting for handler commit     | 0.000014 |
| closing tables                 | 0.000013 |
| freeing items                  | 0.000022 |
| cleaning up                    | 0.000027 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)

mysql> select state,sum(duration) as total_r , round(100*sum(duration)/(select sum(duration) from information_schema.profiling where query_id=6),2) as pct_r,count(*) as calls,sum(duration)/count(*) as 'R/Call' from information_schema.profiling where query_id group by state order by total_r desc;
+--------------------------------+----------+--------+-------+--------------+
| state                          | total_r  | pct_r  | calls | R/Call       |
+--------------------------------+----------+--------+-------+--------------+
| executing                      | 0.114058 | 100.08 |     6 | 0.0190096667 |
| Opening tables                 | 0.001226 |   1.08 |     6 | 0.0002043333 |
| checking permissions           | 0.000852 |   0.75 |    56 | 0.0000152143 |
| starting                       | 0.000692 |   0.61 |     8 | 0.0000865000 |
| Creating tmp table             | 0.000524 |   0.46 |     4 | 0.0001310000 |
| init                           | 0.000410 |   0.36 |     6 | 0.0000683333 |
| statistics                     | 0.000361 |   0.32 |     5 | 0.0000722000 |
| preparing                      | 0.000165 |   0.14 |     5 | 0.0000330000 |
| freeing items                  | 0.000138 |   0.12 |     7 | 0.0000197143 |
| cleaning up                    | 0.000108 |   0.09 |     7 | 0.0000154286 |
| optimizing                     | 0.000092 |   0.08 |     6 | 0.0000153333 |
| waiting for handler commit     | 0.000084 |   0.07 |     9 | 0.0000093333 |
| System lock                    | 0.000079 |   0.07 |     5 | 0.0000158000 |
| closing tables                 | 0.000067 |   0.06 |     6 | 0.0000111667 |
| query end                      | 0.000039 |   0.03 |     6 | 0.0000065000 |
| end                            | 0.000035 |   0.03 |     6 | 0.0000058333 |
| removing tmp table             | 0.000018 |   0.02 |     4 | 0.0000045000 |
| Executing hook on transaction  | 0.000013 |   0.01 |     1 | 0.0000130000 |
+--------------------------------+----------+--------+-------+--------------+
18 rows in set, 2 warnings (0.00 sec)

profile还支持查看all、cpu、block io、content switch、page faults等明细类型，例如查看上述语句在CPU资源上消耗的时间：

show profile cpu  for query 6；