如何最佳化MySQL連線並減少磁碟I/O以加快慢查詢速度？

透過最佳化聯接和減少磁碟寫入來緩解MySQL 慢查詢

在使用多個聯接和表查找的MySQL 查詢過於耗時的情況下，特別是影響Web頁面載入速度，進行了調查和解決。

有問題的查詢涉及三個表格：poster_data、海報類別和海報產品。該查詢試圖根據特定 apcatnum 值等於「623」的條件來顯示這些表中的特定資訊。

對資料庫架構和查詢計劃（作為 EXPLAIN 語句提供）的分析顯示， poster_prodcat 表包含超過 1700 萬行，是導致速度下降的主要原因。具體來說，由於未最佳化的連接條件，當存取該表中約 400,000 行的子集時，查詢正在寫入磁碟。

為了解決此問題，建議對資料庫結構和索引進行重組。原來複雜的連接結構被簡化且更有效率的連接策略所取代。此外，在相關列上引入了聚集複合索引，以提高查詢效能。

以下程式碼片段展示了最佳化後的資料庫結構和查詢：

-- Create the tables
DROP TABLE IF EXISTS poster;
CREATE TABLE poster (
  poster_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL UNIQUE
)
ENGINE = INNODB;


DROP TABLE IF EXISTS category;
CREATE TABLE category (
  cat_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL UNIQUE
)
ENGINE = INNODB;


DROP TABLE IF EXISTS poster_category;
CREATE TABLE poster_category (
  cat_id MEDIUMINT UNSIGNED NOT NULL,
  poster_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (cat_id, poster_id) -- Clustered composite index
)
ENGINE = INNODB;


-- Populate the tables with data

-- Fetch data from the existing database to populate the new structure

SELECT
  p.*,
  c.*
FROM
  poster_category pc
INNER JOIN category c
  ON pc.cat_id = c.cat_id
INNER JOIN poster p
  ON pc.poster_id = p.poster_id
WHERE
  pc.cat_id = 623
ORDER BY
  p.name
LIMIT
  32;

這些最佳化的整合顯著減少查詢執行時間。重組和索引技術相結合，最大限度地減少磁碟寫入操作並提高查詢效率，從而使網頁的載入速度大幅提升。

以上是如何最佳化MySQL連線並減少磁碟I/O以加快慢查詢速度？的詳細內容。更多資訊請關注PHP中文網其他相關文章！