Bagaimanakah Kami Boleh Mengoptimumkan Gabungan MySQL dan Mengurangkan I/O Cakera untuk Mempercepatkan Pertanyaan Perlahan?

Mengurangkan Pertanyaan Lambat MySQL dengan Mengoptimumkan Gabungan dan Mengurangkan Tulisan Cakera

Dalam situasi di mana pertanyaan MySQL yang menggunakan berbilang gabungan dan carian jadual memakan masa yang berlebihan, terutamanya menjejaskan web kelajuan memuatkan halaman, penyiasatan dan penyelesaian telah dijalankan.

Pertanyaan yang dipersoalkan terlibat tiga jadual: poster_data, poster_categories dan poster_prodcat. Pertanyaan cuba untuk memaparkan maklumat khusus daripada jadual ini berdasarkan syarat bahawa nilai apcatnum tertentu adalah sama dengan '623'.

Analisis skema pangkalan data dan pelan pertanyaan (disediakan sebagai pernyataan EXPLAIN) mendedahkan bahawa jadual poster_prodcat, yang mengandungi lebih 17 juta baris, merupakan penyumbang utama kepada kelembapan. Khususnya, pertanyaan itu ditulis pada cakera apabila mengakses subset lebih kurang 400,000 baris daripada jadual ini disebabkan oleh syarat gabungannya yang tidak dioptimumkan.

Untuk menangani isu ini, penstrukturan semula struktur pangkalan data dan indeks telah dicadangkan. Struktur gabungan kompleks asal telah digantikan dengan strategi gabungan yang mudah dan lebih cekap. Selain itu, indeks komposit berkelompok telah diperkenalkan pada lajur yang berkaitan untuk meningkatkan prestasi pertanyaan.

Coretan kod berikut mempamerkan struktur pangkalan data dan pertanyaan yang dioptimumkan:

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

Pengintegrasian pengoptimuman ini berkurangan dengan ketara masa pelaksanaan pertanyaan. Teknik penyusunan semula dan pengindeksan digabungkan untuk meminimumkan operasi penulisan cakera dan meningkatkan kecekapan pertanyaan, menghasilkan peningkatan yang ketara dalam kelajuan pemuatan halaman web.

Atas ialah kandungan terperinci Bagaimanakah Kami Boleh Mengoptimumkan Gabungan MySQL dan Mengurangkan I/O Cakera untuk Mempercepatkan Pertanyaan Perlahan?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!