我必須調查一個 MySQL 生產伺服器，在調查時我發現一個DELETE查詢的狀態"SHOW PROCESSLIST"超過 400 秒，我試圖在慢速日誌中找到該查詢，但我無法在慢速日誌中找到該查詢。preparing

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()，但這沒有發生，為什麼慢日誌中沒有呼叫語句？
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)) 

可以嗎？

你實際上問了兩個問題：

1. “如果在 MySQL SP/Triggers/Functions/Events 中寫入查詢很慢，則不會記錄到慢日誌文件中”

答：不會登錄標準的 MySQL 伺服器。請查看慢速日誌的 Percona Server擴展：特別是查看log_slow_sp_statements配置變數。它做你想做的事：記錄常式中的慢查詢。請注意，這不適用於觸發器。 2. “我們需要單獨優化每個 SP/觸發器/功能/事件嗎？”

答：嗯，當然。每個查詢都需要盡可能優化，具體取決於您的要求。常式不是導致內部查詢執行得更快的“神奇解決方案”……

您將進行一些盡職調查。換句話說，咬緊牙關優化您的查詢。

在您的特定情況下，您應該對刪除查詢做兩件事：

將查詢重構為刪除連接

代替

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

試一試 ！！！

引用自：https://dba.stackexchange.com/questions/55809