mysql - 为什么这个 MySQL IN 比 WHERE OR 花费的时间长得多？

Question

我有两个表，identities 和 events。

identities 只有两列，identity1 和 identity2 并且都有一个 HASH INDEX。

events 有大约 50 列，列 _p 有一个 HASH INDEX。

CREATE TABLE `identities` (
  `identity1` varchar(255) NOT NULL DEFAULT '',
  `identity2` varchar(255) DEFAULT NULL,
  UNIQUE KEY `uniques` (`identity1`,`identity2`),
  KEY `index2` (`identity2`) USING HASH,
  KEY `index1` (`identity1`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-

CREATE TABLE `events` (
  `rowid` int(11) NOT NULL AUTO_INCREMENT,
  `_p` varchar(255) NOT NULL,
  `_t` int(10) NOT NULL,
  `_n` varchar(255) DEFAULT '',
  `returning` varchar(255) DEFAULT NULL,
  `referrer` varchar(255) DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,

  [...]

  `fcc_already_sells_online` varchar(255) DEFAULT NULL,
  UNIQUE KEY `_p` (`_p`,`_t`,`_n`),
  KEY `rowid` (`rowid`),
  KEY `IDX_P` (`_p`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=5231165 DEFAULT CHARSET=utf8;

那么，为什么这个查询:

SELECT SQL_NO_CACHE * FROM events WHERE _p IN (SELECT identity2 FROM identities WHERE identity1 = 'user@example.com') ORDER BY _t

大约需要 40 秒，而这个:

SELECT SQL_NO_CACHE * FROM events WHERE _p = 'user@example.com' OR _p = 'user2@example.com' OR _p = 'user3@example.com' OR _p = 'user4@example.com' ORDER BY _t

基本相同时只需要20ms？

---

编辑:

这个内部查询需要 3.3 毫秒:

SELECT SQL_NO_CACHE identity2 FROM identities WHERE identity1 = 'user@example.com'

Answer 1

原因:

MySQL 处理条件 IN <static values list>和 IN <sub-query>作为不同的东西。在 documentation 中有详细说明第二个等于= ANY()即使该索引存在也不能使用索引的查询。 MySQL 不够巧妙，无法做到这一点。相反，当索引存在时，第一个被视为简单的范围扫描，这意味着 MySQL 可以轻松使用索引。

可能的解决方法:

在我看来，有一些解决方法，您甚至已经提到了其中之一。所以它可能是:

• 使用 JOIN .如果有一个领域可以加入，这很可能是解决问题的最佳方法。实际上，从 5.6 版本开始，MySQL 已经tries to enforce this optimization如果可能的话，但这在复杂的情况下或在没有依赖子查询的情况下不起作用(所以基本上如果 MySQL 无法“跟踪”该引用)。根据您的情况，这不是一个选项，而这实际上是您的子查询不会发生的情况。
• 查询应用中的子资源并形成静态列表。是的，尽管通常的做法是避免由于连接/网络/查询计划开销而导致的多次查询，但在这种情况下它实际上可以工作。在您的情况下，即使您之前在所有重新计算的内容上有大约 200 毫秒的开销，仍然值得独立查询子资源并将静态列表替换为应用程序中的下一个查询。