Wie können wir MySQL-Joins optimieren und Festplatten-I/O reduzieren, um langsame Abfragen zu beschleunigen?

Verringerung langsamer MySQL-Abfragen durch Optimierung von Joins und Reduzierung des Festplattenschreibvorgangs

In einer Situation, in der eine MySQL-Abfrage mit mehreren Joins und Tabellensuchen übermäßig viel Zeit in Anspruch nahm, was sich insbesondere auf das Web auswirkte Seitenladegeschwindigkeiten, wurde eine Untersuchung und Lösung durchgeführt.

Die betreffende Abfrage umfasste drei Tabellen: poster_data, poster_categories und poster_prodcat. Die Abfrage versuchte, spezifische Informationen aus diesen Tabellen anzuzeigen, basierend auf der Bedingung, dass ein bestimmter Apcatnum-Wert gleich „623“ war.

Eine Analyse des Datenbankschemas und des Abfrageplans (bereitgestellt als EXPLAIN-Anweisung) ergab dies Die Tabelle poster_prodcat, die über 17 Millionen Zeilen enthielt, trug maßgeblich zur Verlangsamung bei. Insbesondere schrieb die Abfrage beim Zugriff auf eine Teilmenge von etwa 400.000 Zeilen aus dieser Tabelle aufgrund ihrer nicht optimierten Join-Bedingungen auf die Festplatte.

Um dieses Problem zu beheben, wurde eine Umstrukturierung der Datenbankstruktur und der Indizes vorgeschlagen. Die ursprüngliche komplexe Join-Struktur wurde durch eine vereinfachte und effizientere Join-Strategie ersetzt. Darüber hinaus wurden Clustered Composite Indizes für die relevanten Spalten eingeführt, um die Abfrageleistung zu verbessern.

Der folgende Codeausschnitt zeigt die optimierte Datenbankstruktur und Abfrage:

-- 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;

Die Integration dieser Optimierungen wurde erheblich reduziert die Ausführungszeit der Abfrage. Durch die Kombination der Umstrukturierungs- und Indizierungstechniken werden Schreibvorgänge auf die Festplatte minimiert und die Abfrageeffizienz verbessert, was zu einer erheblichen Verbesserung der Ladegeschwindigkeit der Webseite führt.

Das obige ist der detaillierte Inhalt vonWie können wir MySQL-Joins optimieren und Festplatten-I/O reduzieren, um langsame Abfragen zu beschleunigen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!