mysql - 连接表上的条件比引用上的条件快

我有一个涉及两个表的查询:表A有很多行，并且包含一个名为b_id的字段，该字段引用了表B的记录，该表有大约30个不同的行。表A在b_id上有一个索引，表B在name列上有一个索引。

我的查询看起来像这样:

SELECT COUNT(A.id) FROM A INNER JOIN B ON B.id = A.b_id WHERE (B.name != 'dummy') AND <condition>;

由于condition是表A上的一些随机条件(我有很多这样的条件，它们都表现出相同的行为)。

该查询非常慢(花费2秒以北)，并使用explain说明查询优化器从表B开始，出现约29行，然后扫描表A。进行STRAIGHT_JOIN，将顺序转过来，查询立即运行。

我不喜欢黑魔法，因此我决定尝试其他方法:给出B中记录的ID为dummy的记录的ID(比方说23)，然后将查询简化为:

SELECT COUNT(A.id) FROM A WHERE (b_id != 23) AND <condition>;

令我惊讶的是，此查询实际上比直连接要慢，花费了不到一秒钟的时间。

关于为什么联接比简化查询更快的任何想法？

更新:在注释中的请求之后，来自explain的输出:

直连:

+----+-------------+-------+--------+-----------------+---------+---------+---------------+--------+-------------+
| id | select_type | table | type   | possible_keys   | key     | key_len | ref           | rows   | Extra       |
+----+-------------+-------+--------+-----------------+---------+---------+---------------+--------+-------------+
|  1 | SIMPLE      | A     | ALL    | b_id            | NULL    | NULL    | NULL          | 200707 | Using where |
|  1 | SIMPLE      | B     | eq_ref | PRIMARY,id_name | PRIMARY | 4       | schema.A.b_id |     1  | Using where |
+----+-------------+-------+--------+-----------------+---------+---------+---------------+--------+-------------+

不加入:

+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | A     | ALL  | b_id          | NULL | NULL    | NULL | 200707 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+

更新2:
尝试了另一个变体:
SELECT COUNT(A.id) FROM A WHERE b_id IN (<all the ids except for 23>) AND <condition>;
它的运行速度比不连接要快，但仍比连接要慢，因此，不平等操作似乎是造成部分性能下降的原因，但不是全部。

最佳答案

如果您使用的是MySQL 5.6或更高版本，则可以询问查询优化器它在做什么；

SET optimizer_trace="enabled=on";

## YOUR QUERY
SELECT COUNT(*) FROM transactions WHERE (id < 9000) and user != 11;
##END YOUR QUERY

SELECT trace FROM information_schema.optimizer_trace;

SET optimizer_trace="enabled=off";

您几乎肯定需要在MySQL引用Tracing the Optimiser和The Optimizer中引用以下部分

看一下第一个解释，似乎查询更快了，这可能是因为优化器可以使用表B过滤掉基于联接的所需行，然后使用外键获取表A中的行。

在解释中，这一点很有趣。只有一行匹配，并且它使用schema.A.b_id。实际上，这是从A中预过滤了行，我认为这是性能差异的来源。

   | ref           | rows   | Extra       |
   | schema.A.b_id |     1  | Using where |

因此，与查询一样，它全都取决于索引-更准确地说，是缺少索引。仅仅因为您在各个字段上都有索引，并不一定意味着它们适用于您正在运行的查询。

基本规则:如果EXPLAIN未使用索引显示，则需要添加合适的索引。

具有讽刺意味的是，从解释输出看，每行的第一件事就是最后一件事。即Extra
在第一个示例中，我们看到
| 1 | SIMPLE | A | .... Using where | | 1 | SIMPLE | B | ... Using where |

这两个在不好的地方使用；理想情况下，至少一个，最好两个都说使用索引

当你做
SELECT COUNT(A.id) FROM A WHERE (b_id != 23) AND <condition>;

并查看使用的位置，那么您需要在进行表扫描时添加索引。

例如，如果你做了
EXPLAIN SELECT COUNT(A.id) FROM A WHERE (Id > 23)

您应该看到使用位置；使用索引(假设Id是主键并具有索引)

如果您随后在最后添加了一个条件
EXPLAIN SELECT COUNT(A.id) FROM A WHERE (Id > 23) and Field > 0

并查看使用的位置，那么您需要为两个字段添加索引。仅在字段上具有索引并不意味着MySQL将能够在跨多个字段的查询期间使用该索引-这是查询优化器在内部决定的。我不确定内部规则。但通常添加额外的索引以匹配查询会极大地帮助您。

因此，添加一个索引(在上面的查询中的两个字段上):
ALTER TABLE `A` ADD INDEX `IndexIdField` (`Id`,`Field`)

应该对其进行更改，以便在基于这两个字段进行查询时有一个索引。

我已经在我的一个具有Transactions和User表的数据库中尝试过此操作。

我将使用此查询
EXPLAIN SELECT COUNT(*) FROM transactions WHERE (id < 9000) and user != 11;

在两个字段上不带索引运行:
PRIMARY,user PRIMARY 4 NULL 14334 Using where

然后添加一个索引:
ALTER TABLE `transactions` ADD INDEX `IndexIdUser` (`id`, `user`);

然后再次相同的查询，这一次
PRIMARY,user,Index 4 Index 4 4 NULL 12628 Using where; Using index

这次使用索引-结果将更快。

通过@Wrikken的评论-还请记住，我没有准确的架构/数据，因此本次调查需要对架构进行假设(可能是错误的)
SELECT COUNT(A.id) FROM A FORCE INDEX (b_id) would perform at least as good as SELECT COUNT(A.id) FROM A INNER JOIN B ON A.b_id = B.id.

如果我们查看OP中的第一个EXPLAIN，我们会看到查询有两个元素。引用* eq_ref *的EXPLAIN文档，我可以看到它将基于此关系定义要考虑的行。

说明输出的顺序不一定表示先做一个，再做另一个。只是选择要执行查询的内容(至少据我所知)。

由于某种原因，查询优化器决定不使用b_id上的索引-我在这里假设，由于查询的原因，优化器已经决定执行表扫描会更有效。

第二个解释让我有些担心，因为它没有考虑b_id的索引；可能是因为AND <condition>(它被省略了，所以我猜测可能是什么)。当我尝试使用b_id上的索引时，它确实使用了索引；但是一旦添加条件，它就不会使用索引。

所以，当做
SELECT COUNT(A.id) FROM A INNER JOIN B ON A.b_id = B.id.

这一切向我表明，B上的PRIMARY索引是速度差的来源。我基于解释中的schema.A.b_id假定此表上有一个外键；与b_id上的索引相比，它必须是更好的相关行集合-因此查询优化器可以使用此关系来定义要选择的行-并且由于主索引比辅助索引要好，因此选择行要快得多B，然后使用关系链接与A中的行进行匹配。