如何最佳化緩慢的 MySQL SELECT 查詢以提高效能並減少磁碟空間使用？

最佳化MySQL 選擇查詢以提高效能並減少磁碟空間

需要幾分鐘才能執行的緩慢MySQL 查詢是常見的效能問題由開發商。在此場景中，查詢從三個表中檢索資料以顯示網頁。使用EXPLAIN命令進行調查後發現，該查詢將中間結果寫入磁碟，導致了嚴重的效能瓶頸。

為了最佳化查詢，採取了綜合方法：

表格結構分析

查詢使用了三個表格：poster_data、poster_categories 和 poster_prodcat。 Poster_data 包含有關各個海報的信息，而 poster_categories 列出了所有類別（例如電影、藝術）。 Poster_prodcat 保存海報 ID 和相關類別。主要瓶頸是在 poster_prodcat 中發現的，該系統有超過 1700 萬行和大量正在過濾的特定類別的結果（約 40 萬個）。

索引最佳化

EXPLAIN 輸出顯示查詢缺乏最佳索引，導致資料存取效率低且執行緩慢。主要問題是 poster_prodcat.apcatnum 列上缺少用於過濾的索引。在沒有索引的情況下，MySQL 優化器會採用全表掃描，導致磁碟 I/O 過多，執行時間過長。

查詢重寫

來解決效能問題問題，使用更有效的方法重寫了查詢：

• 這三個表使用INNER JOIN 而非效率較低的SELECT *。
• WHERE 子句被簡化為直接過濾所需的類別，避免了對子查詢的需要。
• ORDER BY 子句移至查詢末尾以防止對大型資料集進行不必要的排序。

臨時表建立

為了緩解磁碟空間使用問題，最佳化查詢被進一步修改以建立儲存中間結果的臨時表。這種方法允許查詢繞過將資料寫入磁碟，從而顯著提高效能。

其他最佳化

除了主要最佳化之外，也實作了一些附加措施來進一步增強查詢效能：

• 使用LIMIT 子句限制結果：原始查詢沒有限制，可能會傳回一個很大的結果網頁不需要的結果數量。
• 快取結果：使用 MEMORY 引擎建立臨時表，將資料保留在記憶體中以便更快存取。

結論

透過解決索引、查詢結構和臨時表使用問題，對原始查詢進行了最佳化，以提高效能並減少磁碟空間使用。此最佳化顯著減少了執行時間，使網頁產生的反應速度更快。

以上是如何最佳化緩慢的 MySQL SELECT 查詢以提高效能並減少磁碟空間使用？的詳細內容。更多資訊請關注PHP中文網其他相關文章！