了解对MySQL元组搜索的性能影响 [英] Understanding performance impacts for mysql tuple search

问题描述

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

I am working on a table structure like this (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)上的组合进行一些非常大的搜索.

Now, I need to do some really big search on combinations on (dept_id, emp_id).

我使用类似这样的元组搜索.

I use tuple search which goes like this.

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

当表很长时，这会花费一些时间.

This takes quite some time when the table is quite long.

但是如果我这样做，

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

这是相当快的，甚至是100倍.

It is considerably faster, even 100x.

我在这里不明白的是，

• 即使搜索是在索引列上，为什么查询1的速度也不快?

-编辑---

我对表上的两个查询做了解释.

I did an explain on the two queries on my tables.

• 我真的很困惑mysql对第一个查询进行全表扫描.这至少可以得出一个结论-在'in'子句中使用元组搜索时，索引是无用的.
• 第二个查询的行数小于并近似等于结果.这意味着在"in"子句中具有索引列是可行的.
• I really am confusing mysql to do a full table scan for the first query. This at least leads to one conclusion - indexes are useless when working with tuple searches in 'in' clause.
• The row count for the second query is less than and approx equal to the results. This means having an indexed column in 'in' clause works.

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

So, is it bad to use indexed columns in the in clause?

推荐答案

根据

According to this question, support for tuples in MySQL is not optimized. As @O.Jones writes in his comment, the query planner in MySQL is a mightily complex beast, and things that should work do not always behave as you might expect.

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

I believe your second query is faster because the first where clause dept_id in (101, 103) reduces the search space for the second one which uses the tuples. The query optimizer should do this automagically, but at least in your example does not do so.

我不相信IN子句是问题-它是元组比较，它扫描整个表并且不使用可用索引.

I do not believe the IN clause is the problem - it's the tuple comparison which is scanning the entire table and not using available indexes.

这篇关于了解对MySQL元组搜索的性能影响的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！