MySQL关联查询未使用product_pic表productid索引问题求助

首先，从你的EXPLAIN结果和查询语句来看，核心问题出在优化器选择了低效的执行路径：全表扫描product和product_pic，并使用Block Nested Loop（BNL）连接，加上临时表和文件排序，导致即使只返回36行也耗时极长。下面一步步拆解问题并给出解决方案：

1. 重构查询逻辑，避免全表扫描product表 #

你的原查询用了p.id IN (SELECT * FROM PID)，MySQL对这类子查询的优化有时不够理想，容易触发全表扫描。改成先关联PID和product表，利用product的主键索引快速定位需要的36行数据：

SELECT p.id, p.title AS name, pp.pic
FROM PID
JOIN product p ON PID.id = p.id  -- 利用product的PRIMARY KEY快速查找
LEFT JOIN product_pic pp ON p.id = pp.productid
GROUP BY p.id;

这样执行时，优化器会先从PID获取36个目标ID，再通过product的主键索引精准查询对应行，彻底避免全表扫描product表。

2. 确保product_pic的索引被有效利用 #

你已经创建了productid索引，但未被使用，可能有以下原因和解决办法：

• 更新表统计信息：MySQL优化器依赖统计信息选择执行计划，如果统计信息过时，可能误判索引成本。执行：

  ANALYZE TABLE product_pic;
  

• 创建覆盖索引：因为你只需要pic字段，创建包含productid和pic的覆盖索引，避免回表查询，让索引更具吸引力：

  CREATE INDEX idx_productid_pic ON product_pic(productid, pic);
  

  之后再执行查询，优化器大概率会选择这个索引，替代全表扫描。

3. 优化GROUP BY操作，避免临时表和文件排序 #

原查询的GROUP BY p.id是为了每个product只返回一行，但如果你的需求是获取默认图片，可以直接在JOIN条件里过滤default_img=1，完全不需要GROUP BY：

SELECT p.id, p.title AS name, pp.pic
FROM PID
JOIN product p ON PID.id = p.id
LEFT JOIN product_pic pp ON p.id = pp.productid AND pp.default_img = 1;

如果确实需要聚合所有图片（比如用GROUP_CONCAT），也可以保留GROUP BY，但此时因为product只有36行，临时表的开销会非常小。

4. 优化PID临时表（如果PID是子查询） #

如果PID不是物理表而是子查询结果，建议将其转为带主键的临时表，进一步提升关联效率：

-- 创建临时表并添加主键
CREATE TEMPORARY TABLE pid_temp (id INT(11) PRIMARY KEY) ENGINE=InnoDB;
-- 插入子查询结果
INSERT INTO pid_temp SELECT id FROM [你的原PID子查询];

-- 用临时表执行查询
SELECT p.id, p.title AS name, pp.pic
FROM pid_temp
JOIN product p ON pid_temp.id = p.id
LEFT JOIN product_pic pp ON p.id = pp.productid
GROUP BY p.id;

验证优化效果 #

执行上述优化后的查询，再跑EXPLAIN，你应该会看到：

• product表的type为eq_ref（利用主键索引）
• product_pic表的type为ref（利用productid或覆盖索引）
• 不再出现Using temporary; Using filesort和Using join buffer (BNL join)

这些变化会让查询耗时从几分钟降到毫秒级。

内容的提问来源于stack exchange，提问作者Hadi Aminzadeh