mysql - 了解 mysql 元组搜索的性能影响

Question

我正在处理这样的表结构 (emp_data)

id   dept_id    emp_id   emp_name      role
1      101       1001      Tom      Good Worker
2      101       1002      Dick     Smart Worker
3      102       1001      Harry    Hard Worker
4      103       1001      Kate      Nice Worker
5      101       1003      Lucy     Great Worker

• id 是无争议的主键 :)
• (dept_id, emp_id) 是多列索引

现在，我需要对 (dept_id, emp_id) 的组合进行一些非常大的搜索。

我使用这样的元组搜索。

select * from emp_data 
where (dept_id, emp_id) in 
  ((101, 1001), 
   (101, 1002), 
   (103, 1001));

当表格很长时，这需要相当长的时间。

但是如果我这样做，

select * from emp_data 
where dept_id in (101, 103) 
and (dept_id, emp_id) in 
((101, 1001), 
 (101, 1002), 
 (103, 1001));

它要快得多，甚至快 100 倍。

这里我不明白的是，

• 为什么查询 1 不快，即使搜索是在索引列上进行的？

---编辑---

我对表中的两个查询做了解释。

• 我真的很困惑 mysql 对第一个查询进行全表扫描。这至少得出了一个结论——在“in”子句中使用元组搜索时索引是无用的。
• 第二个查询的行数小于且大约等于结果。这意味着在“in”子句中有一个索引列是有效的。

那么，在 in 子句中使用索引列是不是不好？

Answer 1

根据 this question , MySQL 中对元组的支持未优化。正如 @O.Jones 在他的评论中所写，MySQL 中的查询规划器是一个非常复杂的野兽，应该工作的东西并不总是像您预期的那样运行。

我相信您的第二个查询更快，因为第一个 where 子句 dept_id in (101, 103)减少第二个使用元组的搜索空间。查询优化器应该自动执行此操作，但至少在您的示例中不会这样做。

我不认为 IN 子句是问题所在 - 它是扫描整个表而不使用可用索引的元组比较。