mysql - EXISTS vs IN - 哪一个在MySQL 5.5和MySQL 5.7中更好？

Question

我从性能角度测试了mysql 5.7和5.5上的两种不同sql方法（exists和in）。
作为测试的附带说明，两个数据库都在同一台机器上，在这台机器上，每次测试我只激活其中一个数据库。它们每个都有4GB的内存分配给它们。我在每次测试之前重新启动了数据库，以确保没有缓存完成（至少在数据库级别没有）。
我在stackoverflow上看到了很多问题，从in到exists的转换在性能方面很有帮助。在大多数线程中，老的mysql版本（ver<5.6）就是这样。所以我的第一个目标是测试这个理论（exists比老版本的mysql要好）。
另外，我一直在阅读更新的mysql版本中的改进，所以我想亲自看看。
因此，为了更好地了解哪一个更适合我以后的查询，我运行了以下测试：
定义常量：

SET @quantity = 50;

存在查询：

SELECT SQL_NO_CACHE
    c.c_first_name, c.c_birth_country
FROM
    customer c
WHERE
    EXISTS( SELECT 
            1
        FROM
            store_sales ss
        WHERE
            ss.ss_quantity > @quantity
                AND ss.ss_customer_sk = c.c_customer_sk)
ORDER BY c.c_first_name DESC , c.c_birth_country DESC
LIMIT 1000;

查询中的等价项：

SELECT SQL_NO_CACHE
    c.c_first_name, c.c_birth_country
FROM
    customer c
WHERE
    c.c_customer_sk IN (SELECT 
            ss.ss_customer_sk
        FROM
            store_sales ss
        WHERE
            ss.ss_quantity > @quantity)
ORDER BY c.c_first_name DESC , c.c_birth_country DESC
LIMIT 1000;

结果是：
mysql 5.5-存在-53秒
mysql 5.5-in-48秒
mysql 5.7-存在-46秒
mysql 5.7-在4秒内
正如你所看到的，结果有点令人惊讶：
由于某些原因，exists替代方案的性能比mysql 5.5的in替代方案差。
对于mysql 5.7，in的性能似乎明显优于exists，即使exists替代方案的解释比in替代方案“看起来更好”（请参见下面的解释输出）。
你能分享一下你的想法吗？
当您要编写新查询时，如何在in和exists之间进行选择？你将如何指导你的团队？我们可以每次都尝试这两种方法，但这听起来有点可笑，在编写复杂的查询时可能会浪费很多时间。对于mysql<5.6和mysql>5.6，应该有一些指导原则来说明它们各自何时更好。
有没有我遗漏的mysql文档？
只是用所有相关的表来结束循环并解释信息-
带有索引的表创建脚本（您可能会在这里看到许多无用或冗余的索引，但请忽略它们，因为这是一个测试环境）：

CREATE TABLE `customer` (
   `c_customer_sk` int(11) NOT NULL,
   `c_customer_id` char(16) NOT NULL,
   `c_current_cdemo_sk` int(11) DEFAULT NULL,
   `c_current_hdemo_sk` int(11) DEFAULT NULL,
   `c_current_addr_sk` int(11) DEFAULT NULL,
   `c_first_shipto_date_sk` int(11) DEFAULT NULL,
   `c_first_sales_date_sk` int(11) DEFAULT NULL,
   `c_salutation` char(10) DEFAULT NULL,
   `c_first_name` char(20) DEFAULT NULL,
   `c_last_name` char(30) DEFAULT NULL,
   `c_preferred_cust_flag` char(1) DEFAULT NULL,
   `c_birth_day` int(11) DEFAULT NULL,
   `c_birth_month` int(11) DEFAULT NULL,
   `c_birth_year` int(11) DEFAULT NULL,
   `c_birth_country` varchar(20) DEFAULT NULL,
   `c_login` char(13) DEFAULT NULL,
   `c_email_address` char(50) DEFAULT NULL,
   `c_last_review_date` char(10) DEFAULT NULL,
   PRIMARY KEY (`c_customer_sk`),
   KEY `c_fsd2` (`c_first_shipto_date_sk`),
   KEY `c_fsd` (`c_first_sales_date_sk`),
   KEY `c_hd` (`c_current_hdemo_sk`),
   KEY `c_cd` (`c_current_cdemo_sk`),
   KEY `c_a` (`c_current_addr_sk`),
   KEY `customer_index_1` (`c_first_name`,`c_birth_country`),
   CONSTRAINT `c_a` FOREIGN KEY (`c_current_addr_sk`) REFERENCES `customer_address` (`ca_address_sk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `c_cd` FOREIGN KEY (`c_current_cdemo_sk`) REFERENCES `customer_demographics` (`cd_demo_sk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `c_fsd` FOREIGN KEY (`c_first_sales_date_sk`) REFERENCES `date_dim` (`d_date_sk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `c_fsd2` FOREIGN KEY (`c_first_shipto_date_sk`) REFERENCES `date_dim` (`d_date_sk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `c_hd` FOREIGN KEY (`c_current_hdemo_sk`) REFERENCES `household_demographics` (`hd_demo_sk`) ON DELETE NO ACTION ON UPDATE NO ACTION
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8


CREATE TABLE `store_sales` (
   `ss_sold_date_sk` int(11) DEFAULT NULL,
   `ss_sold_time_sk` int(11) DEFAULT NULL,
   `ss_item_sk` int(11) NOT NULL,
   `ss_customer_sk` int(11) DEFAULT NULL,
   `ss_cdemo_sk` int(11) DEFAULT NULL,
   `ss_hdemo_sk` int(11) DEFAULT NULL,
   `ss_addr_sk` int(11) DEFAULT NULL,
   `ss_store_sk` int(11) DEFAULT NULL,
   `ss_promo_sk` int(11) DEFAULT NULL,
   `ss_ticket_number` int(11) NOT NULL,
   `ss_quantity` int(11) DEFAULT NULL,
   `ss_wholesale_cost` decimal(7,2) DEFAULT NULL,
   `ss_list_price` decimal(7,2) DEFAULT NULL,
   `ss_sales_price` decimal(7,2) DEFAULT NULL,
   `ss_ext_discount_amt` decimal(7,2) DEFAULT NULL,
   `ss_ext_sales_price` decimal(7,2) DEFAULT NULL,
   `ss_ext_wholesale_cost` decimal(7,2) DEFAULT NULL,
   `ss_ext_list_price` decimal(7,2) DEFAULT NULL,
   `ss_ext_tax` decimal(7,2) DEFAULT NULL,
   `ss_coupon_amt` decimal(7,2) DEFAULT NULL,
   `ss_net_paid` decimal(7,2) DEFAULT NULL,
   `ss_net_paid_inc_tax` decimal(7,2) DEFAULT NULL,
   `ss_net_profit` decimal(7,2) DEFAULT NULL,
   PRIMARY KEY (`ss_item_sk`,`ss_ticket_number`),
   KEY `ss_s` (`ss_store_sk`),
   KEY `ss_t` (`ss_sold_time_sk`),
   KEY `ss_d` (`ss_sold_date_sk`),
   KEY `ss_p` (`ss_promo_sk`),
   KEY `ss_hd` (`ss_hdemo_sk`),
   KEY `ss_c` (`ss_customer_sk`),
   KEY `ss_cd` (`ss_cdemo_sk`),
   KEY `ss_a` (`ss_addr_sk`),
   KEY `store_sales_index_1` (`ss_quantity`,`ss_customer_sk`),
   KEY `store_sales_idx_sk_price` (`ss_item_sk`,`ss_sales_price`),
   KEY `store_sales_idx_price_sk` (`ss_sales_price`,`ss_item_sk`),
   CONSTRAINT `ss_a` FOREIGN KEY (`ss_addr_sk`) REFERENCES `customer_address` (`ca_address_sk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `ss_c` FOREIGN KEY (`ss_customer_sk`) REFERENCES `customer` (`c_customer_sk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `ss_cd` FOREIGN KEY (`ss_cdemo_sk`) REFERENCES `customer_demographics` (`cd_demo_sk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `ss_d` FOREIGN KEY (`ss_sold_date_sk`) REFERENCES `date_dim` (`d_date_sk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `ss_hd` FOREIGN KEY (`ss_hdemo_sk`) REFERENCES `household_demographics` (`hd_demo_sk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `ss_i` FOREIGN KEY (`ss_item_sk`) REFERENCES `item` (`i_item_sk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `ss_p` FOREIGN KEY (`ss_promo_sk`) REFERENCES `promotion` (`p_promo_sk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `ss_s` FOREIGN KEY (`ss_store_sk`) REFERENCES `store` (`s_store_sk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `ss_t` FOREIGN KEY (`ss_sold_time_sk`) REFERENCES `time_dim` (`t_time_sk`) ON DELETE NO ACTION ON UPDATE NO ACTION
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

explain-mysql 5.7-exists备用-

1   PRIMARY c       index       customer_index_1    124     1000    100.00  Using where; Using index
2   DEPENDENT SUBQUERY  ss      ref ss_c,store_sales_index_1    ss_c    5   tpcds.c.c_customer_sk   32  50.00   Using where

解释-mysql 5.7-可选-

1   SIMPLE  ss      range   ss_c,store_sales_index_1    store_sales_index_1 5       1395022 100.00  Using where; Using index; Using temporary; Using filesort; Start temporary
1   SIMPLE  c       eq_ref  PRIMARY PRIMARY 4   tpcds.ss.ss_customer_sk 1   100.00  End temporary

explain-mysql 5.5-exists可选-

1   PRIMARY c   index       customer_index_1    124     1000    Using where; Using index
2   DEPENDENT SUBQUERY  ss  ref ss_c,store_sales_index_1    ss_c    5   tpcds.c.c_customer_sk   14  Using where

解释-mysql 5.5-可选-

1   PRIMARY c   index       customer_index_1    124     1000    Using where; Using index
2   DEPENDENT SUBQUERY  ss  index_subquery  ss_c,store_sales_index_1    ss_c    5   func    14  Using where

Answer 1

这个问题没有最终的真相。如果in的性能总是比exists差，那么优化器可以采取的第一步就是简单地将每个in重写为exists。
in允许优化器利用several different execution paths，这是常规exists子查询无法做到的。它尤其可以执行in作为exists（但反之亦然）。所以如果你想有一个通用的指导原则，你可以在任何可能的地方使用in，因为它可以很容易地被重写为exists，让你选择（和编译器）以任何一种方式来实现它。如果测试显示mysql走错了路径，您可以简单地切换到exists，强制优化器执行同样的操作。
如果优化器选择采用其中一个新的可用执行计划，那么它们可能会更快——或者不会。对于优化器所做的许多决策来说，这是正确的：它基本上是基于它所拥有的关于数据的一些有限信息进行猜测的，而且可能猜错了。告诉优化器探索一些不同路径的直接方法是利用Optimizer Hints。稍微更改查询（例如将in切换到exists）也可以使优化器选择不同的执行计划（例如，因为其他的已经不可用），因此您可以将其视为间接提示，尽管它比实际提示更不可控。
这些可能会给你一个更快的结果-或者，出于同样的原因，相反的。这通常取决于你的实际数据和情况。你只需根据你的具体情况测试一下，然后选择一个速度更快的。但请记住，情况可能会改变（如果数据分布发生变化），因此可能需要在某个时候重新测试并可能重写查询。
但它通常并不适用——正如您已经意识到的，对于您的具体情况，您的假设“存在比在旧的mysql版本中更好”并不成立，而对于您所看到的大多数问题（这可能是或可能不是一个有偏见的选择）似乎都是这样。
在一般的介绍之后（你想听一些想法，所以你得到了一些）：
您的infor 5.7之所以表现如此出色，是因为mysql在可能的执行计划中找到了一种适合您的特定数据分发的方法。
假设您只有一个客户ss_quantity > @quantity。因为您在ss_quantity上有一个索引，所以对您的查询最快的答案是简单地使用这个索引，用那个数量查找客户，就完成了。你拥有的顾客越多，这就越不有效。例如，假设每个客户都满足数量条件，那么支持您的order by（因此limit）的索引更可取-这是mysql 5.5决定通过选择使用索引customer_index_1的执行计划来做的。
将exists更改为in使mysql找到了该路径。优化器在5.5之间变得更好了。所以这不仅仅是偶然的运气。但是如果你的数据分布超出了临界点，而mysql仍然走这条路，那么它将变得更慢。在你达到盈亏平衡点的地方会有很多神奇的顾客。你显然站在这一点的好一边。
一种测试方法是将@quantity设置为一个较低的值。您可能会找到一个in将被执行的值，比如exists，甚至可能找到一个exists比in快的值。另一个因素是limit的值。limit 1应该像exists当前那样执行（假设您的查询返回的行多于少数行），因此您可能会找到一些数量参数，并限制in比exists慢的地方。如果mysql确实将in的执行计划更改为类似于exists，那么limit将有一些值，而limit则没有（我们知道至少对于值1000）。您可能会找到一个值，其中in再次比exists慢。
但要再次强调这一点：它并不普遍适用。这些值将取决于您的数据，情况可能会随之改变。如果你获得了越来越多的客户，那么1000的限制可能会变得越来越不相关，而且你可能会在未来达到临界点，即in比exists更糟糕（mysql没有意识到这一点），并且可能不得不更改你的查询。