MySQL"IN"使用子查询的查询速度非常慢，但是使用显式值的查询速度很快 [英] MySQL "IN" queries terribly slow with subquery but fast with explicit values

问题描述

我有一个MySQL查询(Ubu 10.04，Innodb，Core i7、16Gb RAM，SSD驱动器，优化的MySQL参数):

I have a MySQL query (Ubu 10.04,Innodb, Core i7, 16Gb RAM, SSD drives, MySQL params optimized):

SELECT
COUNT(DISTINCT subscriberid)
FROM
em_link_data
WHERE
linkid in (SELECT l.id FROM em_link l WHERE l.campaignid = '2900' AND l.link != 'open')

表em_link_data有大约700万行，em_link有数千行. 此查询大约需要 18秒.但是，如果我用结果代替 子查询，然后执行以下操作:

The table em_link_data has about 7million rows, em_link has a few thousand. This query will take about 18 seconds to complete. However, if I substitute the results of the subquery and do this:

SELECT
COUNT(DISTINCT subscriberid)
FROM
em_link_data
WHERE
linkid in (24899,24900,24901,24902);

然后查询将在不到1毫秒的时间内运行.子查询仅在不到1毫秒的时间内运行，因此索引列的linkid已被索引.

then the query will run in less than 1 millisecond. The subquery alone runs in less than 1ms, the column linkid is indexed.

如果我将查询重写为联接，则还不到1毫秒.为什么"IN"查询中的子查询如此之慢，为什么其中包含值之所以如此之快?我无法重写查询(购买的软件)，因此希望进行一些调整或提示以加快查询速度！感谢您的帮助.

If I rewrite the query as a join, also less than 1ms. Why is a "IN" query so slow with a subquery in it and why so fast with values in it? I can't rewrite the query (bought software) so I was hoping there is some tweak or hint to speedup this query! Any help is appreciated.

推荐答案

每次对子查询求值时都会执行子查询(无论如何，在MySQL中，不是所有的RDBMS)，也就是说，您基本上在运行700万个查询！如果可能的话，使用JOIN会将其减少到1.即使添加索引可以提高这些索引的性能，您仍在运行它们.

Subqueries execute every time you evaluate them (in MySQL anyway, not all RDBMSes), i.e. you're basically running 7 million queries! Using a JOIN, if possible, will reduce this to 1. Even if adding indexing improves performance of those, you're still running them.

这篇关于MySQL"IN"使用子查询的查询速度非常慢，但是使用显式值的查询速度很快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！