如何優化緩慢的 MySQL 查詢以提高效能並減少磁碟 I/O？

最佳化MySQL 查詢以減少執行時間和磁碟寫入

您的MySQL 查詢遇到效能問題，需要幾分鐘才能完成並寫入執行期間的磁碟。這會顯著影響網頁的回應能力。為了解決這個問題，我們將探索一種涉及資料建模和索引優化的解決方案。

您提到的慢查詢是：

SELECT * 
FROM poster_prodcat, 
     poster_data, 
     poster_categories 
WHERE poster_data.apnumber = poster_prodcat.apnumber 
  AND poster_categories.apcatnum = poster_prodcat.apcatnum 
  AND poster_prodcat.apcatnum='623'  
ORDER BY aptitle ASC 
LIMIT 0, 32

根據 EXPLAIN 輸出，查詢正在執行完整的查詢對海報_prodcat 表進行表掃描，該表包含超過 1700 萬行。這會導致執行時間過長。

為了優化查詢性能，我們可以實施以下更改：

標準化和索引：

1. 標準化數據： 將海報_prodcat 表轉換為標準化結構兩個表：海報和類別。建立第三個表海報_類別，以建立海報和類別之間的關係。
2. 建立索引：在海報、類別和海報_類別表中的對應欄位上建立索引。這將使資料庫能夠快速定位數據，而無需執行全表掃描。

以下是更新架構的範例：

CREATE TABLE poster (
  poster_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL UNIQUE,
  PRIMARY KEY (poster_id)
) ENGINE=InnoDB;

CREATE TABLE category (
  cat_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL UNIQUE,
  PRIMARY KEY (cat_id)
) ENGINE=InnoDB;

CREATE TABLE poster_category (
  cat_id MEDIUMINT UNSIGNED NOT NULL,
  poster_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (cat_id, poster_id),
  INDEX (poster_id) -- Clustered composite index
) ENGINE=InnoDB;

資料標準化並建立索引後，查詢應該執行得更快，從而顯著減少執行時間。

以上是如何優化緩慢的 MySQL 查詢以提高效能並減少磁碟 I/O？的詳細內容。更多資訊請關注PHP中文網其他相關文章！