mysql - 奇怪的 MySql 连接行为

Question

我有一个包含 4 个表的 MySql 数据库(实际上比这多得多，但只有这 4 个与问题相关)，我们称它们为 A、B、C 和 D。这是架构:

CREATE TABLE A
(
  pKey INT NOT NULL AUTO_INCREMENT,
  name NVARCHAR(50),
  PRIMARY KEY(pKey),
  UNIQUE INDEX(name)
);

CREATE TABLE C
(
  pKey INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY(pKey)
);

CREATE TABLE B
(
  pKey INT NOT NULL AUTO_INCREMENT,
  aKey INT NOT NULL,
  cKey INT NOT NULL,
  PRIMARY KEY(pKey),
  UNIQUE INDEX UniqueKey (aKey, cKey),
  FOREIGN KEY(aKey) REFERENCES A(pKey),
  FOREIGN KEY(cKey) REFERENCES C(pKey)
);

CREATE TABLE D
(
  pKey INT NOT NULL AUTO_INCREMENT,
  cKey INT NOT NULL,
  PRIMARY KEY(pKey),
  INDEX(cKey),
  FOREIGN KEY(cKey) REFERENCES C(pKey)
);

我正在运行以下查询:

SELECT 
    --stuff...
FROM A 
INNER JOIN B
    ON A.pKey=B.aKey
INNER JOIN C
    ON B.cKey=C.pKey
INNER JOIN D
    ON D.cKey=C.pKey
WHERE
    A.name=parameter_1;

麻烦的是，这是一个运行在单台服务器上的大型数据库，大多数表都有100K+记录，一张表破千万条记录的情况并不少见。一张表有2亿多条记录。

撇开 MySql 和体系结构的任何问题(我都坚持使用两者)，当我对此查询使用 explain 时，我在上述查询中遇到了一些奇怪的行为。由于这种行为，我有几个问题。我将首先展示奇怪的行为。

如果我只是在 MySql 中解释上述查询，那么我会在 EXPLAIN 输出的 ref 列中得到我期望的引用。但是，我需要将此查询作为更大查询的子查询来运行。 EXPLAINning 较大的查询为我提供了类似上述查询的内容(这只是与此查询中的表相对应的较大查询的行):

+----+-------------+-------+-------+---------------------+---------+---------+-----------------+-------+--------------------------+
| id | select_type | table | type  | possible_keys       | key     | key_len | ref             | rows  | Extra                    |
+----+-------------+-------+-------+---------------------+---------+---------+-----------------+-------+--------------------------+
|  1 | SIMPLE      | A     | const | PRIMARY,key1,key2   | key1    | 38      |                 |     1 |                          |
|  1 | SIMPLE      | D     | index | key3                | key3    | 12      | NULL            | 73868 | Using index              |
|  1 | SIMPLE      | C     | index | PRIMARY,key3        | PRIMARY | 8       | NULL            |     1 |                          |
|  1 | SIMPLE      | B     | ref   | key4,UniqueKey,key6 | key4    | 12      | const,DB.D.key3 |     1 | Using where; Using index |
+----+-------------+-------+-------+---------------------+---------+---------+-----------------+-------+--------------------------+

MySql 正在执行两次索引扫描和一次引用类型连接。如果我使用索引提示，我可以稍微改进一下，但只是稍微改进一下。我之前说过，这个查询是作为子查询运行的。这是其他查询的格式:

SELECT
  --stuff
FROM
(
  --sub-query1
) a
INNER JOIN
(
  --the query I have a question about
) b
ON a.c1=b.c2

优化器完全忽略表 C，转而对两个外键列 B.cKey=D.cKey 进行连接。 所以问题 1) 为什么优化器会像这样忽略表 C？

接下来，即使我确实使用了索引提示，并且它忽略了表 C，尽管有适当的索引，它仍然会进行索引扫描以连接 B 和 D。 为什么？

在上面的explain中，显示表D有73868行，做这道题的时候实际有73568行。正在查询的其他表之一(未在此问题中显示)大约有 1 亿行，因此对其进行优化非常重要。对于完整查询，行列的乘积约为 2.37E42。是的，我已经考虑过减少查询中表数量的方法；我需要获取的信息需要我正在访问的每个表，而且我无法更改数据库的架构。

最后，我在这里唯一可以更改的是查询和索引/约束。我坚持使用其他所有东西，因为这是一个预先存在的系统。 还有其他方法可以进一步优化此操作吗？

谢谢!

编辑:我修复了 super 查询的格式。

Answer 1

你能为这个模式添加索引吗？

如果是这样，我建议您将复合索引(name, pKey) 添加到您的表A 中。不要费心对其施加唯一约束；您已经使用其他索引处理过该问题。该化合物将使您的选择标准 A.name=parameter_1 和您的连接满足一次索引扫描。

您对单列表 C 的使用只是为了消除不在该表中的结果集行。我不会担心它会从 EXPLAIN 中丢失，除非您的查询有可怕的性能问题。

一般来说，在处理这些多路连接操作时，您应该尝试使用复合覆盖索引来帮助您提高性能。您可以阅读有关此类索引的信息。