我必须调查一个MySQL Production服务器,在调查我在“SHOW PROCESSLIST”中发现了一个DELETE查询,在准备状态下花了400多秒,我试图在慢速日志中找到查询,但我无法在慢日志.

mysql> show full processlist;
+------+-----------------+---------------------+-----------+---------+------+-----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id   | User            | Host                | db        | Command | Time | State                       | Info                                                                                                                                                                                                                                                                                                         |
+------+-----------------+---------------------+-----------+---------+------+-----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|    2 | event_scheduler | localhost           | NULL      | Daemon  |  204 | Waiting for next activation | NULL                                                                                                                                                                                                                                                                                                         | 
| 7229 | root            | 192.168.1.178       | mydb      | Connect |  204 | preparing                   | DELETE
          FROM    TEST_DATA_1
         WHERE ID in
                (SELECT ID
                   FROM TEST_DATA_2
                  WHERE STATE >= 16384
                    AND (MODIFY_DT IS NULL or MODIFY_DT <= ADDDATE(SYSDATE(), INTERVAL -10 MINUTE))) | 
+------+-----------------+---------------------+-----------+---------+------+-----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

我把它转换成相应的SELECT并执行.执行花了大约180秒,我观察到它正在准备状态很长时间.

我将相同的SELECT子查询转换为JOIN,它在几分之一秒内执行.所以我认为我可以优化查询.

经过一番分析后我发现查询是在一个程序里面写的,所以我的问题是如果一个查询是用MySQL编写的SP / Triggers / Functions / Events很慢,它就不会被记录到慢速日志文件中.

我们是否需要单独优化每个SP /触发器/功能/事件？我有大约300个SP,40个触发器以及一些事件,所以我需要独立优化它们.

更新于2013年12月31日

>如果一个过程花费的时间比慢的日志时间要长,它应该作为CALL ProcedureName()记录到慢速日志中,但这不会发生慢速日志中没有调用语句为什么？
>为什么查询在准备状态上花费了大量时间？从MySQL Doc General Thread States我发现准备状态的定义为

preparing

This state occurs during query optimization.

我希望我可以重写删除查询

DELETE A.* FROM 
TEST_DATA_1 A 
INNER JOIN 
TEST_DATA_2 B ON A.ID = B.ID 
WHERE B.STATE >= 16384 AND (B.MODIFY_DT IS NULL or B.MODIFY_DT <= ADDDATE(SYSDATE(), INTERVAL -10 MINUTE)) 

可以吗？

解决方法:

你实际上问了两个问题：

>“如果在MySQL SP / Triggers / Functions / Events中写入查询很慢,则不会将其记录到慢速日志文件中”

答：它不会记录在标准MySQL服务器中.请查看Percona Server extension for the slow log：特别是查看log_slow_sp_statements配置变量.它可以满足您的需求：记录例程中的慢查询.请注意,这不适用于触发器.
>“我们需要单独优化每个SP /触发器/功能/事件吗？”

答：好吧,当然.每个查询都需要尽可能优化,具体取决于您的要求.例程不是导致内部查询运行得更快的“神奇解决方案”……