長時間運行的查詢可能會嚴重影響 MySQL 資料庫效能，導致從回應時間緩慢到影響每個使用者的全面瓶頸等各種問題。處理這些煩人的查詢——了解它們是什麼、為什麼發生以及如何管理它們——是保持資料庫平穩運行的關鍵。
無論是儘早發現它們、阻止它們，還是設定自動處理它們的方法，本指南都能滿足您的要求。
MySQL 中的長時間執行查詢是指執行時間異常長的查詢。
將查詢歸類為「長時間運行」的具體持續時間可能會有所不同，具體取決於應用程式的效能標準。一般來說，如果查詢運行時間比平常長並且開始減慢資料庫速度，則它被視為長時間運行。
長時間運行查詢的原因可能多種多樣：
缺乏適當的索引 – 如果沒有適當的索引，MySQL 必須掃描整個表才能檢索所需的資料。這個過程效率非常低，特別是對於大型表，因為它消耗大量時間和資源。
重負載情況 – 當伺服器處理大量查詢或同時處理一些複雜的查詢時，可用資源（如 CPU 和記憶體）就會緊張。這種資源競爭可能會延遲查詢的執行，從而導致運行時間更長，尤其是在高峰使用期間。
鎖爭用 – 當多個事務需要同時存取相同資料但由於其他操作持有必要的鎖而被阻止時，就會發生這種情況。例如，如果一個事務正在更新一行，則想要讀取或更新同一行的另一個事務必須等待第一個事務完成並釋放鎖定。
不正確的規範化 – 雖然規範化有助於避免資料冗餘並提高資料完整性，但過度規範化的資料庫可能會導致涉及多個聯接的複雜查詢。這些會降低效能。另一方面，標準化不足可能會導致資料重複過多，從而導致表更大和查詢速度更慢。
大型聯結 – 涉及聯接大型表的查詢，尤其是沒有適當索引的查詢可能會很慢。資料庫必須根據連接條件來匹配表中的行，如果沒有高效率的索引，這個過程可能會佔用大量資源並且速度很慢。
為了有效管理長時間執行的查詢，您首先需要識別它們。這裡有一些方法：
顯示進程清單；命令是取得伺服器上執行的所有活動查詢快照的快速方法。此命令顯示每個查詢以及幾個關鍵信息，包括每個查詢運行的時間。那些具有較高“時間”值的查詢可能是您長時間運行的查詢。以下是如何使用此指令：
顯示完整的流程清單；
此命令將列出所有當前進程，顯示誰啟動了它們，它們正在運行什麼類型的命令，最重要的是，它們已經運行了多長時間。如果您發現任何查詢運行時間異常長，那麼這些查詢就是長時間運行的查詢。然後，您可以決定是否深入優化它們，或者如果它們拖累了您的系統性能，則乾脆將它們殺死。
設定慢查詢日誌是捕捉這些有問題的查詢的另一個好策略。這個方便的 MySQL 功能會記錄任何執行時間超過特定閾值的查詢。它不僅僅是捕獲長時間運行的查詢 - 它還可以幫助您識別未有效使用索引的查詢。
要啟動並執行慢查詢日誌，您需要調整 MySQL 設定檔（my.cnf 或 my.ini）中的一些設定：
MySQL 的效能模式對於更詳細的調查非常有價值。該工具旨在監控伺服器事件並追蹤效能指標，讓您更清晰地了解查詢執行和整體系統效能。
透過新增以下行確保在 MySQL 配置中啟用它：
[mysqld]
Performance_schema = ON
啟動後，您可以探索各種效能架構表來分析查詢的效能。例如，如果您想要精確定位長時間執行的查詢，您可能需要查看 events_statements_history_long 表。查詢方法如下：
SELECT EVENT_ID, SQL_TEXT, TIMER_WAIT/1000000000 AS '持續時間（秒）'
FROM Performance_schema.events_statements_history_long
哪裡 TIMER_WAIT > 10000000000；
此查詢可協助您尋找任何執行時間超過 10 秒的查詢。它為您提供 SQL 文字以及每個查詢運行了多長時間等詳細資訊。
當您發現某個查詢花費的時間過長並導致系統資源緊張時，您可以選擇手動終止它。這是使用 KILL 命令後面跟著查詢的特定進程 ID 來完成的。
您可以透過執行SHOW PROCESSLIST指令找到進程ID，該指令顯示所有目前正在執行的進程及其各自的ID。查看清單中是否有任何顯示高「時間」值的查詢，該值表示它們已經運行了多長時間。
一旦識別出有問題的查詢並記下其進程 ID，您就可以使用 KILL 命令終止它：
殺死[進程ID];
將 [進程 ID] 替換為 SHOW PROCESSLIST 輸出中的實際編號。
這種方法要小心。突然停止查詢有時會導致問題，例如，如果查詢正在寫入或更新訊息，則資料會處於不一致的狀態。
設定自動化來處理長時間運行的查詢可以成為真正的救星，防止那些緩慢或未最佳化的查詢佔用資料庫資源並減慢甚至鎖定整個系統。但請謹慎行事 - 在沒有進行正確檢查的情況下使用此工具實際上可能會隱藏需要您注意的更深層次的效能問題。
始終確保您有全面的日誌記錄和監控，以分析已終止的查詢對應用程式的影響，並考慮改進這些查詢，而不是僅僅自動終止它們。將自動終止視為優化效能的更大策略的一部分，而不是作為解決所有問題的解決方案。
首先，您需要啟用MySQL事件調度程序，預設情況下該功能是停用的。事件規劃程序可讓您建立和規劃您希望伺服器在預先定義時間自動執行的任務。執行以下命令：
設定全域 event_scheduler = ON;
啟用調度程序後，下一步是定義將監視和終止長時間運行的查詢的實際事件。該事件將每分鐘運行一次，以檢查運行時間超過指定閾值（例如 60 秒）的查詢。一旦識別，它將自動終止這些查詢。以下是設定此事件的 SQL 程式碼的細分：
`建立事件kill_long_running_queries
ON SCHEDULE EVERY 1 MINUTE -- 指定事件運行的頻率
做
開始
聲明已完成 INT 預設為 FALSE；
宣告 proc_id INT; -- 儲存每個查詢的進程 ID 的變數
聲明 cur1 遊標用於從 information_schema.processlist
中選擇 ID
WHERE 命令 = '查詢' AND 時間 > 60; -- 將“60”更改為您的閾值（以秒為單位）
聲明未找到的繼續處理程序設定完成 = TRUE;
開啟 cur1；
read_loop：循環
FETCH cur1 INTO proc_id;
如果完成了
離開read_loop;
結束如果；
殺死 proc_id; -- 殺死由 proc_id
標識的進程
結束循環；
關閉 cur1；
結束;`
控制查詢的最大執行時間有助於防止資料庫被運行時間過長的查詢佔用。這是使用 MySQL 5.7.8 及更高版本中的 max_execution_time 系統變數透過為所有唯讀 SELECT 查詢設定係統範圍的執行時間限制來完成的：
設定全域 max_execution_time = 2000;
這將限制設為 2000 毫秒（2 秒）
請記住，此設定不適用於預存程序、函數或觸發器，並且會在伺服器重新啟動時重設為預設值，除非新增至您的 MySQL 設定檔：
[mysqld]
最大執行時間 = 2000
MariaDB 雖然是從 MySQL 衍生出來的，但它提供了一種類似但不同的方法來管理查詢執行時間。從 MariaDB 10.1.1 開始，您可以使用 max_statement_time 系統變數來實現此目的：
設定全域 max_statement_time = 2;
這將所有查詢的執行時間限制為 2 秒。
要透過伺服器重新啟動進行持久性配置，請將此行新增至您的 MariaDB 設定檔：
[mysqld]
最大語句時間 = 2
Releem 的查詢分析工具徹底改變了您監控和最佳化資料庫效能的方式。它會自動收集前 100 個查詢的詳細信息，提供關鍵指標，例如平均執行時間以及每個查詢對資料庫運行效率的整體影響。
使用 Releem，無需手動挖掘 PROCESSLIST 輸出或查看慢速查詢日誌來識別效能不佳的查詢。該工具具有直覺的儀表板，可讓您輕鬆排序和發現延遲或消耗過多時間的查詢。這種即時洞察力可以幫助您立即識別並解決瓶頸。
以上是管理 MySQL 中長時間運行的查詢的詳細內容。更多資訊請關注PHP中文網其他相關文章！
