如何优化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中文网其他相关文章！