mysql - 即使使用索引，mysql 内连接也比 2 个单独的查询慢 50-500 倍

Question

我从内部联接中得到了非常意外的糟糕性能，我无法解释它，因为所有必要的索引似乎都已到位，并且如果我将其分成 2 个查询，它的运行速度至少会快 50 倍。然而对我来说，这正是关系数据库应该擅长的查询类型。 MySQL 5.6(如果有的话)。账户表有~4K记录，事件表~500M。

CREATE TABLE `tbl_account` (
  `accountID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `accountKey` varchar(767) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  ...,
  PRIMARY KEY (`accountID`),
  KEY `index_accountKey` (`accountKey`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `tbl_event` (
  `eventID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `fkAccount` bigint(20) unsigned DEFAULT NULL,
  `creationDate` datetime NOT NULL,
  ...,
  PRIMARY KEY (`eventID`),
  KEY `index_fkAccount` (`fkAccount`),
  KEY `index_creationDate` (`creationDate`),
  KEY `index_fkAccount_creationDate` (`fkAccount`, `creationDate`),
  CONSTRAINT `event_fkAccount` FOREIGN KEY (`fkAccount`) REFERENCES `tbl_account` (`accountID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

当我运行以下查询时，至少需要 5.5 秒，有时几乎一分钟。

SELECT e.* FROM tbl_event e INNER JOIN tbl_account a ON e.fkAccount = a.accountID
WHERE a.accountKey = 'abcdefghij'
  AND e.creationDate >= '2019-02-01 00:00:00'
ORDER BY e.creationDate DESC
LIMIT 500;

如果我首先独立查找accountID，第一个查询始终只需要0.03秒，第二个查询只需要0.1秒(当我告诉它使用复合索引时，mysql有时只想使用index_creationDate自己)。

SELECT a.accountID FROM tbl_account a WHERE a.accountKey = 'abcdefghij';
/*returns accountID = 123*/

SELECT e.* FROM tbl_event e USE INDEX (index_fkAccount_creationDate)
WHERE e.fkAccount IN (123)
  AND e.creationDate >= '2019-02-01 00:00:00'
ORDER BY e.creationDate DESC
LIMIT 500;

一些注意事项:

• 这不是因为缓存。我以不同的顺序、不同的日期和帐户过滤器运行它，当我知道它没有被缓存时，结果是一致的。
• 如果重要的话，accountID 到 accountKey 大多是 1-1，但也不完全是，有一些 accountKey 具有多个 accountID 值。这就是最后一个 where 子句使用 IN 的原因。
• 我知道 tbl_account 中的 index_accountKey 不需要是 varchar 字段的全长来保存索引大小，但这不是此问题的问题。
• 在 tbl_event 中，index_fkAccount 是多余的，因为存在复合索引并将其作为第一个字段，但它目前仍在我的真实表中，所以我也将其放在这里。

使用连接进行选择时 EXPLAIN 的输出:

table       | type | key                          | ref                   |rows| Extra
tbl_account | ref  | index_accountKey             | const                 |001 | Using where; Using temporary; Using filesort
tbl_event   | ref  | index_fkAccount_creationDate | tbl_account.accountID |116 | Using index condition

为什么 EXPLAIN 会说“使用 where；使用临时；使用文件排序”从 tbl_account 返回 1 行？当我单独查找 tbl_account 行时，它只是“使用位置”。我没有从 tbl_account 返回任何行作为排序依据的一部分，那么它试图排序什么？难道不应该只在 tbl_account 中查找 accountID，然后将其插入 tbl_event 并使用那里的索引吗？

我尝试将过滤器放入 ON 子句中。我尝试过使用子查询，但没有任何改进。这是否只是 MySQL 的糟糕优化，我需要拆分查询，或者我可以做些什么来让它通过连接快速运行？

Answer 1

我很确定您的查询:

SELECT e.* 
FROM tbl_event e 
INNER JOIN tbl_account a ON e.fkAccount = a.accountID
WHERE a.accountKey = 'abcdefghij'
AND e.creationDate >= '2019-02-01 00:00:00'
ORDER BY e.creationDate DESC
LIMIT 500;

必须对 tbl_event 表进行表扫描(由creation_date 限定)，然后对于每一行，找到匹配的 tbl_account 行并将给定值与 tbl_account.accountKey 值进行比较。

您可能会更幸运地反转查询中表的顺序，如下所示:

SELECT e.* 
FROM tbl_account a 
INNER JOIN tbl_event e ON e.fkAccount =a.accountID
WHERE a.accountKey='k3'
ORDER BY e.creationDate DESC
LIMIT 500;

这应该首先选择与给定键匹配的 tbl_account 行，然后查找与这些行相对应且在给定时间戳当天或之后创建的 tbl_event 行。

这是一个sqlfiddle link我创建是为了测试这个。