MySQL查询非常缓慢。

Question

3

我在我的数据库上运行了以下查询：

SELECT e.id_dernier_fichier
FROM Enfants e JOIN FichiersEnfants f
ON e.id_dernier_fichier = f.id_fichier_enfant

查询运行良好。如果我修改查询如下：

SELECT e.codega
FROM Enfants e JOIN FichiersEnfants f
ON e.id_dernier_fichier = f.id_fichier_enfant

查询变得非常慢！问题在于我想在表e和f中选择许多列，查询可能需要长达1分钟！我尝试了不同的修改，但没有任何效果。我在id_*上也有索引，还有e.codega上的索引。Enfants有9000行，FichiersEnfants有20000行。有什么建议吗？

以下是所需信息（抱歉一开始没有展示它们）：

表FichiersEnfants的信息如下： Table FichiersEnfants

表Enfants的信息如下： Table Enfants

Enfants上的索引如下： Index Enfants

查询1的说明如下：

查询2的说明如下：

- Emanuel

这些表中有哪些是视图？你能把查询语句和 EXPLAIN 结果发在这里吗？ - Itay Moav -Malimovka

表结构？索引定义？表中记录数量？MySQL版本（需要一点帮助）？硬件规格？ - ajreal

e.codega和e.id_dernier_fichier的数据类型是什么？ - Josh Darnell

发布表结构和解释SELECT e.codega的结果从Enfants e JOIN FichiersEnfants f ON e.id_dernier_fichier = f.id_fichier_enfant - Nicola Cossu

@ajreal 他确实发布了记录数量（尽管他没有发布您列出的其他必要项目中的任何一个）。 - Josh Darnell

显示剩余2条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- MatBailie · Accepted Answer

性能差异可能是由于e.id_dernier_fichier在用于JOIN的索引中，而e.codega不在该索引中。没有两个表的完整定义以及它们的所有索引，无法确定。此外，包括两个查询的解释计划也会有所帮助。

现在，我可以详细说明一些事情...

如果一个索引是聚集索引（这也适用于主键），数据实际上是按照索引的顺序物理存储的。这意味着知道你想要索引中的位置 x也隐含着你想要表中的位置 x。

然而，如果索引不是聚集索引，则索引只是为您提供查找。有效地说位置 x在索引中对应于位置 y在表中。

这里的重要性在于访问未在索引中指定的字段。这样做意味着您必须实际去表中获取数据。在聚集索引的情况下，您已经到达了那里，找到该字段的开销非常低。然而，如果索引没有聚集，则您必须将表连接到索引，然后找到您感兴趣的字段。

注意：在（id_dernier_fichier，codega）上具有复合索引与仅在（id_dernier_fichier）上具有一个索引和仅在（codega）上具有另一个索引非常不同。

在您的问题中，我认为您不需要改变代码。但是，您可能会从更改索引中受益。

您提到您想要访问许多字段。将所有这些字段放入复合索引中可能不是最佳解决方案。相反，您可能希望在 (id_dernier_fichier) 上创建聚集索引。这意味着一旦找到 *id_dernier_fichier*，您已经处于获取所有其他字段的正确位置了。

13.2.10.1. 聚集索引和二级索引

每个InnoDB表都有一个特殊的索引，称为聚集索引，其中存储了行数据：

如果您在表上定义了主键，则InnoDB将其用作聚集索引。
如果您没有为表定义主键，则MySQL选择第一个具有仅包含NOT NULL列的唯一索引作为主键，并将其用作聚集索引。
如果表中没有主键或合适的唯一索引，则InnoDB在包含行ID值的合成列上内部生成隐藏的聚集索引。行按InnoDB分配给此类表中的行的ID进行排序。行ID是一个6字节字段，随着插入新行而单调递增。因此，按行ID排序的行在物理上按插入顺序排列。