MySQL存储过程的调优过程

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 )

这样就能看到存储过程内部语句的执行情况了。

转载于:https://www.cnblogs.com/buro79xxd/archive/2009/10/10/1682554.html