优化来自MySQL SP/触发器/函数的查询

Question

我必须调查一个MySQL生产服务器，当我在"SHOW PROCESSLIST"中发现一个DELETE查询时，它在preparing状态下花费了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 secs执行，我观察到它处于preparing状态很长一段时间。

我将同一个SELECT子查询转换为JOIN，它是在很短的时间内执行的。所以我想我可以优化查询。

经过一些分析后，我发现查询是在procedure中编写的，所以我的问题是，如果用MySQL SP/Triggers/Functions/Events编写查询是缓慢的，那么它将不会被记录到慢速日志文件中。

我们需要单独优化每个SP/Triggers/Functions/Events吗？我有300+的SP，40+触发器以及一些事件，所以我需要独立地优化它们。

2013年12月31日最新消息

1. 如果一个过程花费的时间比缓慢的日志时间长，那么它应该作为CALL ProcedureName()记录到慢日志中，但是这种情况没有发生，慢日志中没有call语句，为什么？
2. 为什么查询要花费大量的时间来准备状态？从MySQL Doc 一般螺纹国中，我发现preparing state的定义是

在查询优化过程中会准备此状态。

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

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)) 

还好吗?

Answer 1

你实际上问了两个问题：

1. “如果查询是用MySQL SP/触发器/函数/事件编写的，那么它将不会被记录到慢速日志文件中”回答:它不会被记录在标准的MySQL服务器中。请查看Percona 慢速日志的扩展：特别是查看log_slow_sp_statements配置变量。它可以做您想做的事情:记录例程中的缓慢查询。请注意，这不适用于触发器。
2. “我们是否需要单独优化每个SP/触发器/函数/事件？”答:嗯，当然。每个查询都需要尽可能地优化，这取决于您的需求。例程不是导致内部查询运行更快的“神奇解决方案”.

Answer 2

你会进行一些尽职调查。换句话说，咬紧牙关，优化查询。

在您的特定情况下，您应该对该删除查询做两件事：

将查询重构为删除联接

而不是

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))
);

将其重写如下

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

添加一个索引以支持新的查询

添加包含STATE、MODIFY_DT和ID的索引

ALTER TABLE TEST_DATA_2 ADD IDNEX State_ModifyDate_Index (STATE,MODIFY_DT,ID);

试试看！