SQL IN声明缓慢

问题描述：

我在使用IN语句有效运行SQL时遇到了一些麻烦。如果我分别运行这两个语句，并手动粘贴一系列结果（在这种情况下，有30个vendor_id），vendor_master查询将立即运行，并且发票查询将在大约2秒内运行。SQL IN声明缓慢

select * FROM invoices where vendor_id IN 

(

select vendor_id from vendor_master WHERE vendor_master_id = 12345 

);

那么是什么导致巨大的减速，超过60秒，并经常超时？有没有办法用逗号把结果放入变量中？或者让内部声明执行firsT？

小记。您在子查询中缺少WHERE关键字 – 2014-09-03 02:19:21

答

那么是什么导致巨大的放缓，超过60秒，并经常超时？

的IN条款效果很好，当数据的IN条件设置里面的“小”和“确定性”。这是因为条件评估每行一次。因此，假设IN子句中的查询返回100行，并且FROM子句中的表有1000行，则服务器必须执行100 * 1000 = 100,000比较来过滤掉数据。过多的努力来过滤太少的数据，你不觉得吗？当然，如果你的数据集（在from和in子句中）都比较大，你可以想象效果。

顺便说一句，当您使用子查询作为in条件时，还有一个额外的开销：子查询需要为每行执行一次。所以顺序是这样的：

行1
- 执行子查询
- 检查，如果第1行的值相匹配的子查询
- 的结果的值，如果上面说的没错，将行保留在结果集中;它排除在外，否则
行2
- 执行子查询
- 检查如果上述属实，保留该行中，如果第2行的值相匹配的子查询
- 的结果的值结果集;它排除在外，否则
...

太多的工作要做，你不觉得吗？

有没有把结果与逗号变量的方法吗？

是的，有一种方法......但是你会真的要做到这一点？让我们来看看：

首先，创建要过滤的值的列表：

set @valueList = (select group_concat(vendor_id separator ',') 
       from (select vendor_id from vendor_master where vendor_master_id = 12345) as a)

然后，创建一个SQL表达式：

set @sql = concat('select * from invoices where vendor_id in (', @valueList, ')';

最后，创建一个准备好的声明，并执行它：

prepare stmt from @sql; 
execute stmt; 
-- when you're done, don't forget to deallocate the statement: 
-- deallocate prepare stmt;

我再次问你：你真的想要做这一切吗？

或获得内部语句首先执行？

其他所有的答案都指向你在正确的方向：而不是使用in使用inner join：

select i.* 
from invoices as i 
    inner join (
     select distinct vendor_id 
     from vendor_master 
     where vendor_master_id = 12345 
    ) as vm on i.vendor_id = vm.vendor_id;

如果由于某种原因，这仍然是太慢，附带唯一的选择在我看来是：创建一个临时表（样的“分而治之的策略”）：

drop table if exists temp_vm; 
create temporary table temp_vm 
    select distinct vendor_id 
    from vendor_master 
    where vendor_master_id = 12345; 
alter table temp_vm 
    add index vi(vendor_id); 
select i.* 
from invoices as i inner join temp_vm as vm on i.vendor_id = vm.vendor_id;

记住：临时表只在连接可见创建它们，并在连接关闭或终止时丢弃。

无论如何，如果您确保您的表格已正确编制索引，您的表现将会得到改善;具体而言，您需要确保invoices.vendor_id和vendor_master.vendor_master_id`已编入索引。

非常感谢所有信息！ – Atari2600 2014-09-03 02:52:43

当我将这些语句单独运行并创建自己的逗号分隔列表时，是否有原因，它运行速度非常快，它仍然必须评估IN数组中的每个值的权利？ – Atari2600 2014-09-03 02:56:39

考虑这一点：对于你的'invoices'表中的每一行，'in'条件被评估一次，而'in'条件是一个必须被评估的查询...太多的负载！当你传递一个简单的以逗号分隔的值列表时，不需要评估一个查询......但它太多了！使用'inner join'而不是'in'（清洁，更快，更便宜） – Barranka 2014-09-03 03:03:42

答

在MySQL 5.6.6之前，in的优化效率非常低下。使用exists代替：

select * 
FROM invoices i 
where exists (select 1 
       from vendor_master vm 
       where i.vendor_id = vm.vendor_id and vm.vendor_master_id = 12345 
      );

为了获得最佳性能，你想在vendor_master(vendor_id, vendor_master_id)的索引。

更不用提供vendor_id为空的问题 – 2014-09-03 02:18:16

答

你可以尝试使用INNER JOIN：

select i.* 
FROM invoices i 
INNER JOIN vendor_master vm 
     ON i.vendor_id = vm.vendor_id AND vm.vendor_master_id = 12345

答

可以使用JOIN与DISTINCT代替IN：

SELECT * 
FROM invoices JOIN 
(
    SELECT DISTINCT vendor_id as vid 
    FROM vendor_master 
    WHERE vendor_master_id = 12345 
) vmi 
ON invoices.vendor_in = vmi.vid

请记住，你必须有DISTINCT，否则如果有两个记录内部查询，比你在JOIN之后将有重复行，并且结果将不同于IN查询。

SQL IN声明缓慢

相关推荐