MySQL子查询拖慢主查询，求优化提速解决方案

问题背景 #

orders表包含200万+行数据，使用IN子查询过滤prefix时，查询耗时可达1-2分钟；但直接将子查询返回的固定值（如'LPR','LFR','LFL'）写入IN子句，查询仅需约6秒。核心代码如下：

原慢查询 #

SELECT
  field1,
  field2
FROM `orders`
WHERE orders.buyers_date BETWEEN '2024/02/01' AND '2024/02/29'
  AND orders.user = 'ABCD1' 
  AND orders.prefix IN(
    (SELECT DISTINCT prefix FROM `photo` WHERE thedate BETWEEN '2024/02/10' AND '2024/02/20' AND user = 'ABCD1')
  )

优化后快速查询 #

SELECT
  field1,
  field2
FROM `orders`
WHERE buyers_date BETWEEN '2024/02/01' AND '2024/02/29'
  AND orders.user = 'ABCD1' 
  AND orders.prefix IN('LPR','LFR','LFL')

另外尝试用变量存储子查询结果时失败：

SET @prefs = (SELECT DISTINCT prefix FROM `photo` WHERE thedate BETWEEN '2024/02/10' AND '2024/02/20' AND user = 'ABCD1');
SELECT @prefs;

返回空结果集，但实际子查询能得到有效数据。

解决思路 #

1. 修正IN子查询的语法问题 #

原查询中IN子句里的子query多套了一层括号IN( (SELECT ...) )，这会让MySQL将子查询视为单个值而非结果集，导致优化器无法按预期先执行子查询再过滤主表。去掉多余括号即可：

SELECT
  field1,
  field2
FROM `orders`
WHERE orders.buyers_date BETWEEN '2024/02/01' AND '2024/02/29'
  AND orders.user = 'ABCD1' 
  AND orders.prefix IN(
    SELECT DISTINCT prefix FROM `photo` WHERE thedate BETWEEN '2024/02/10' AND '2024/02/20' AND user = 'ABCD1'
  )

2. 改用JOIN替代IN子查询 #

MySQL对部分IN子查询的优化逻辑不如JOIN高效，尤其是主表数据量较大时，内连接能让优化器更好地利用索引：

SELECT DISTINCT o.field1, o.field2
FROM `orders` o
JOIN (
  SELECT DISTINCT prefix 
  FROM `photo` 
  WHERE thedate BETWEEN '2024/02/10' AND '2024/02/20' 
    AND user = 'ABCD1'
) p ON o.prefix = p.prefix
WHERE o.buyers_date BETWEEN '2024/02/01' AND '2024/02/29'
  AND o.user = 'ABCD1'

注：DISTINCT用于避免JOIN产生重复行，可根据实际数据情况调整。

3. 添加针对性索引 #

性能瓶颈大多源于缺少合适索引，为两张表创建联合索引：

• 给photo表创建：

  CREATE INDEX idx_photo_user_date_prefix ON photo(user, thedate, prefix);
  

  该索引可让子查询快速过滤出符合条件的prefix，无需全表扫描。
• 给orders表创建：

  CREATE INDEX idx_orders_user_date_prefix ON orders(user, buyers_date, prefix);
  

  主查询可直接通过索引过滤user、buyers_date，再匹配prefix，避免扫描全表。

4. 修正变量存储子查询结果的方法 #

子查询返回多行结果时，直接用SET @prefs = ...会异常（MySQL仅取第一行或返回空），可通过GROUP_CONCAT拼接结果后用FIND_IN_SET查询：

SET @prefs = (SELECT GROUP_CONCAT(DISTINCT prefix SEPARATOR ',') FROM `photo` WHERE thedate BETWEEN '2024/02/10' AND '2024/02/20' AND user = 'ABCD1');

SELECT field1, field2
FROM `orders`
WHERE buyers_date BETWEEN '2024/02/01' AND '2024/02/29'
  AND user = 'ABCD1'
  AND FIND_IN_SET(prefix, @prefs);

注：GROUP_CONCAT有长度限制，若prefix数量极多，需先调整group_concat_max_len参数。

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