对于存储过程的调优过程

普通的query的调优方式一般是查看slow-log,或者long-query-time = 0然后用mk-log-paser进行分析。
假设需要对一个存储过程进行调优。那么如果使用slow-log将会看到很多如下的结果：

SQL:

1. mysql> SELECT whatstheweatherlike(5);
2. +----------------------------------------------------------+
3. | whatstheweatherlike(5)                                   |
4. +----------------------------------------------------------+
5. | Its 5°C, time IS 02:28:05, feels [LIKE] almost summer!   |
6. +  ----------------------------------------------------------+
7. 
   
8.  row IN SET (0.00 sec)
9.  
10. host # tail /var/log/mysql/mysql-slow.log
11. 
    
12.  Time: 090115  2:25:28
13. # User@Host: me[me] @ localhost []
14. # Thread_id: 3432  Schema: test
15. # Query_time: 0.000695  Lock_time: 0.000284  Rows_sent: 1  Rows_examined: 0  Rows_affected: 0  Rows_read: 1
16. # QC_Hit: No  Full_scan: No  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
17. # Filesort: No  Filesort_on_disk: No  Merge_passes: 0
18. # No InnoDB statistics available for this query
19. SELECT whatstheweatherlike(5);
20.  User@Host: me[me] @ localhost []
21. # Thread_id: 3432  Schema: test
22. # Query_time: 0.000695  Lock_time: 0.000284  Rows_sent: 1  Rows_examined: 0  Rows_affected: 0  Rows_read: 1
23. # QC_Hit: No  Full_scan: No  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
24. # Filesort: No  Filesort_on_disk: No  Merge_passes: 0
25. # No InnoDB statistics available for this query
26. SELECT whatstheweatherlike(5);
27.  Thread_id: 3432  Schema: test
28. # Query_time: 0.000695  Lock_time: 0.000284  Rows_sent: 1  Rows_examined: 0  Rows_affected: 0  Rows_read: 1
29. # QC_Hit: No  Full_scan: No  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
30. # Filesort: No  Filesort_on_disk: No  Merge_passes: 0
31. # No InnoDB statistics available for this query
32. SELECT whatstheweatherlike(5);
33.  Query_time: 0.000695  Lock_time: 0.000284  Rows_sent: 1  Rows_examined: 0  Rows_affected: 0  Rows_read: 1
34. # QC_Hit: No  Full_scan: No  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
35. # Filesort: No  Filesort_on_disk: No  Merge_passes: 0
36. # No InnoDB statistics available for this query

可见只显示了对stored-routine的时间记录，并没有对内部的语句的详细运行状况的提示。
这种情况下，可以使用profile进行调优。

过程如下：

1. mysql> SET profiling=1;
2. Query OK, 0 rows affected (0.00 sec)
3.  
4. mysql> SELECT whatstheweatherlike(3);
5. +-----------------------------------------------------------+
6. | whatstheweatherlike(3)                                    |
7. +-----------------------------------------------------------+
8. | Its 3°C, time IS 02:43:25, feels [LIKE] snow IS melting   |
9. +   -----------------------------------------------------------+
10. 
    
11.  row IN SET (0.00 sec)
12.  
13. mysql> SHOW profiles;
14. +----------+------------+-------------------------------------------------------------+
15. 
    
16.  Query_ID | Duration   | Query                                                       |
17. +----------+------------+-------------------------------------------------------------+
18. 
    
19.         1 | 0.00005100 | SELECT CURTIME() INTO time                                  |
20. |        2 | 0.00014100 | SELECT feeling INTO feels FROM weather WHERE temp = in_temp |
21. +----------+------------+-------------------------------------------------------------+
22. 
    
23. rows IN SET (0.00 sec)

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