如何加速MySQL JOIN查询？附表结构及慢查询案例

表结构信息 #

CREATE TABLE `glinks_BuildRelations` (
  `relation_id` int(11) NOT NULL,
  `cat_id` int(11) NOT NULL,
  `link_id` int(11) NOT NULL,
  `page_num` int(11) NOT NULL,
  `distance` float DEFAULT NULL,
  `paid` int(11) NOT NULL DEFAULT 0
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3;

ALTER TABLE `glinks_BuildRelations`
  MODIFY `relation_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2384882;
COMMIT;

CREATE TABLE `glinks_Link_Descriptions_with_URLs` (
  `description_id` mediumint(8) NOT NULL,
  `Description` text DEFAULT NULL,
  `Description_gite` text DEFAULT NULL,
  `Directions` text DEFAULT NULL,
  `Description_ES` text DEFAULT NULL,
  `Description_gite_ES` text DEFAULT NULL,
  `Directions_ES` text DEFAULT NULL,
  `Description_EN` text DEFAULT NULL,
  `Description_gite_EN` text DEFAULT NULL,
  `Directions_EN` text DEFAULT NULL,
  `Multilang_english_Description` text DEFAULT NULL,
  `Multilang_espanol_Description` text DEFAULT NULL,
  `Short_Description` text DEFAULT NULL,
  `link_id_fk` mediumint(7) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

ALTER TABLE `glinks_Link_Descriptions_with_URLs`
  ADD PRIMARY KEY (`description_id`),
  ADD KEY `link_id_fk` (`link_id_fk`);

ALTER TABLE `glinks_Link_Descriptions_with_URLs`
  MODIFY `description_id` mediumint(8) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=121050;
COMMIT;

查询现状 #

• glinks_Link_Descriptions_with_URLs表数据量：121k条
• glinks_BuildRelations表数据量：2,384,881条

单独过滤glinks_BuildRelations速度很快：

SELECT * FROM glinks_BuildRelations as relations WHERE cat_id = 197;
耗时0.012秒

但关联查询速度极慢：

SELECT * FROM glinks_BuildRelations as relations
JOIN glinks_Link_Descriptions_with_URLs AS descs ON descs.link_id_fk = relations.link_id
WHERE cat_id = 197

返回5720条记录，耗时6.928秒

优化方法 #

1. 创建复合索引加速过滤与关联
   在glinks_BuildRelations表上创建复合索引(cat_id, link_id)，查询时先通过cat_id快速过滤目标记录，同时索引直接包含link_id无需回表，关联时可直接使用该字段：

CREATE INDEX idx_cat_link ON glinks_BuildRelations (cat_id, link_id);

2. 统一关联字段的数据类型
   当前glinks_BuildRelations.link_id是int(11)，glinks_Link_Descriptions_with_URLs.link_id_fk是mediumint(7) UNSIGNED，类型不匹配会触发隐式转换导致索引失效。建议统一两者类型，例如：

ALTER TABLE glinks_Link_Descriptions_with_URLs MODIFY link_id_fk int(11) NOT NULL;

3. *避免SELECT ，只查询需要的字段
   SELECT *会返回所有字段，尤其是多个TEXT类型字段会大幅增加数据传输和内存消耗。明确指定所需字段，示例：

SELECT relations.relation_id, relations.cat_id, relations.link_id,
       descs.Description, descs.Short_Description
FROM glinks_BuildRelations as relations
JOIN glinks_Link_Descriptions_with_URLs AS descs ON descs.link_id_fk = relations.link_id
WHERE cat_id = 197

4. 将MyISAM引擎转换为InnoDB
   glinks_BuildRelations使用的MyISAM引擎在关联查询、缓存机制上不如InnoDB，转换后可利用InnoDB的缓冲池和索引优化提升性能：

ALTER TABLE glinks_BuildRelations ENGINE=InnoDB;

内容的提问来源于stack exchange，提问作者Andrew Newby