MySQL 组合IN查询：你的索引为什么罢工了？

MySQL 组合IN查询：你的索引为什么罢工了？

当 WHERE (a,b) IN ((1,2),(3,4)) 让你的索引躺平，是时候谈谈人生了

🚦 问题现场

周五加班写的优雅查询：

SELECT * FROM orders 
WHERE (user_id, status) IN ((1001, 'paid'), (1002, 'shipped'));

结果——全表扫描！索引像周末的你一样彻底躺平 😴

🔍 为什么索引失效？

原因1：MySQL优化器的"近视眼"

MySQL对多列组合IN的优化较弱，可能被拆解为OR条件：

-- 实际可能被优化为：
WHERE (user_id = 1001 AND status = 'paid') 
   OR (user_id = 1002 AND status = 'shipped');

导致复合索引(user_id, status)失效

原因2：数据类型"出轨"

-- ❌ 字符串漏引号（隐式类型转换）
WHERE (user_id, status) IN ((1001, paid)); -- 应该是'paid'

🛠️ 四大解决方案

方案1：UNION ALL 大法（推荐）

SELECT * FROM orders WHERE user_id = 1001 AND status = 'paid'  
UNION ALL  
SELECT * FROM orders WHERE user_id = 1002 AND status = 'shipped';

优点：每条子查询都能命中索引

方案2：临时表JOIN（适合大量组合）

CREATE TEMPORARY TABLE temp_conditions (
  user_id INT, 
  status VARCHAR(20)
);
INSERT INTO temp_conditions VALUES 
  (1001, 'paid'), (1002, 'shipped');

SELECT o.* FROM orders o JOIN temp_conditions t  
ON o.user_id = t.user_id AND o.status = t.status;

方案3：强制索引（慎用！）

SELECT * FROM orders FORCE INDEX(idx_user_status)
WHERE (user_id, status) IN ((1001, 'paid'), (1002, 'shipped'));

方案4：应用层拆分

// 伪代码：分批查询再合并
List<Order> queryByPairs(List<Pair<Integer, String>> pairs) {
  return pairs.stream()
    .flatMap(pair -> jdbc.query(
      "SELECT * FROM orders WHERE user_id=? AND status=?", 
      pair.getKey(), pair.getValue()))
    .collect(Collectors.toList());
}

💡 预防措施

1. 必看执行计划

   EXPLAIN SELECT ...;
   

   • 警惕type=ALL
   • 关注key列是否使用索引

2. 复合索引顺序很重要

   ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);
   

3. 保持类型纯洁性

   -- ✅ 正确姿势
   WHERE (user_id, status) IN ((1001, 'paid'), (1002, 'shipped'))
   

📢 下期预告

《MySQL索引失效的10种骚操作》

• 为什么WHERE DATE(create_time) = '2023-01-01'会让索引哭泣？
• LIKE '%关键字%'的终极解决方案
• 隐式类型转换的连环坑

---

互动话题：
你遇到过哪些「索引突然摆烂」的灵异事件？
欢迎评论区分享你的「调教索引」秘籍！ 💬

（友情提示：周末禁止SELECT * FROM problems🚫）