-
mysql> SELECT whatstheweatherlike(5);
-
+----------------------------------------------------------+
-
| whatstheweatherlike(5) |
-
+----------------------------------------------------------+
-
| Its 5°C, time IS 02:28:05, feels [LIKE] almost summer! |
-
+ ----------------------------------------------------------+
-
row IN SET (0.00 sec)
-
-
host # tail /var/log/mysql/mysql-slow.log
可见只显示了对stored-routine的时间记录,并没有对内部的语句的详细运行状况的提示。
这种情况下,可以使用profile进行调优。
过程如下:
-
mysql> SET profiling=1;
-
Query OK, 0 rows affected (0.00 sec)
-
-
mysql> SELECT whatstheweatherlike(3);
-
+-----------------------------------------------------------+
-
| whatstheweatherlike(3) |
-
+-----------------------------------------------------------+
-
| Its 3°C, time IS 02:43:25, feels [LIKE] snow IS melting |
-
+ -----------------------------------------------------------+
-
row IN SET (0.00 sec)
-
-
mysql> SHOW profiles;
-
+----------+------------+-------------------------------------------------------------+
-
Query_ID | Duration | Query |
-
+----------+------------+-------------------------------------------------------------+
-
1 | 0.00005100 | SELECT CURTIME() INTO time |
-
| 2 | 0.00014100 | SELECT feeling INTO feels FROM weather WHERE temp = in_temp |
-
+----------+------------+-------------------------------------------------------------+
-
rows IN SET (0.00 sec)
这样就能看到存储过程内部语句的执行情况了。