MySQL“输入"使用子查询查询非常慢，但使用显式值查询速度很快 [英] 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 毫秒内运行.子查询单独运行不到1ms，列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“输入"使用子查询查询非常慢，但使用显式值查询速度很快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！