通过profile优化SQL语句

开启profile优化SQL语句：
set profiling=1;
执行SQL语句
show profiles;
show profile for query 2;//根据query_id 查看某个查询的详细时间耗费

SHOW STATUS LIKE 'last_query_cost';//查询上一条语句执行的代价

 

例：

mysql> show profiles;
+----------+------------+----------------------------------------+
| Query_ID | Duration   | Query                                  |
+----------+------------+----------------------------------------+
|        1 | 0.00049325 | select * from t_1_waybill              |
|        2 | 0.00130650 | select SQL_NO_CACHE * from t_1_waybill |
|        3 | 0.05051650 | show profiles for query 2              |
+----------+------------+----------------------------------------+
3 rows in set (0.00 sec)

mysql> show profile for query 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000032 |
| checking query cache for query | 0.000010 |
| checking privileges on cached  | 0.000010 |
| sending cached result to clien | 0.000435 |
| logging slow query             | 0.000004 |
| cleaning up                    | 0.000003 |
+--------------------------------+----------+
6 rows in set (0.00 sec)

mysql> show profile for query 2;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| starting           | 0.000159 |
| Opening tables     | 0.000034 |
| System lock        | 0.000005 |
| Table lock         | 0.000017 |
| init               | 0.000051 |
| optimizing         | 0.000004 |
| statistics         | 0.000019 |
| preparing          | 0.000009 |
| executing          | 0.000002 |
| Sending data       | 0.000607 |
| end                | 0.000006 |
| query end          | 0.000008 |
| freeing items      | 0.000364 |
| logging slow query | 0.000006 |
| cleaning up        | 0.000017 |
+--------------------+----------+
15 rows in set (0.00 sec)