MySQL 結合を最適化し、ディスク I/O を削減して、遅いクエリを高速化するにはどうすればよいでしょうか?

結合の最適化とディスク書き込みの削減による MySQL の遅いクエリの軽減

複数の結合とテーブル ルックアップを利用する MySQL クエリが過度に時間を消費し、特に Web に影響を及ぼしていた状況ページの読み込み速度の問題のため、調査と解決が行われました。

問題のクエリこれには、poster_data、poster_categories、poster_prodcat の 3 つのテーブルが関係していました。クエリは、特定の apcatnum 値が '623' に等しいという条件に基づいて、これらのテーブルからの特定の情報を表示しようとしました。

データベース スキーマとクエリ プラン (EXPLAIN ステートメントとして提供) の分析により、次のことが判明しました。 1,700 万行を超える poster_prodcat テーブルが速度低下の主な原因でした。具体的には、結合条件が最適化されていないため、このテーブルの約 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;

これらの最適化の統合により、大幅に削減されました。クエリの実行時間。再構築とインデックス作成技術を組み合わせることで、ディスク書き込み操作が最小限に抑えられ、クエリ効率が向上し、Web ページの読み込み速度が大幅に向上しました。

以上がMySQL 結合を最適化し、ディスク I/O を削減して、遅いクエリを高速化するにはどうすればよいでしょうか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。