MySql 查询很慢 [英] MySql Query very slow
问题描述
我在我的数据库上运行以下查询:
I run the following query on my database :
SELECT e.id_dernier_fichier
FROM Enfants e JOIN FichiersEnfants f
ON e.id_dernier_fichier = f.id_fichier_enfant
并且查询运行良好.如果我像这样修改查询:
And the query runs fine. If I modifiy the query like this :
SELECT e.codega
FROM Enfants e JOIN FichiersEnfants f
ON e.id_dernier_fichier = f.id_fichier_enfant
查询变得很慢！问题是我想选择表 e 和 f 中的许多列，查询最多可能需要 1 分钟！我尝试了不同的修改，但没有任何效果.我也在 e.codega 上有关于 id_* 的索引.Enfants 有 9000 行，FichiersEnfants 有 20000 行.有什么建议吗?
The query becomes very slow ! The problem is I want to select many columns in table e and f, and the query can take up to 1 minute ! I tried different modifications but nothing works. I have indexes on id_* also on e.codega. Enfants has 9000 lines and FichiersEnfants has 20000 lines. Any suggestions ?
这是询问的信息(抱歉没有从一开始就显示它们):
Here are the info asked (sorry not having shown them from the beginning) :
推荐答案
性能差异可能是由于 e.id_dernier_fichier 在用于 JOIN 的索引中，但 e.codega 不在 那个 索引中.
The difference in performance is possibly due to e.id_dernier_fichier being in the index used for the JOIN, but e.codega not being in that index.
如果没有两个表及其所有索引的完整定义，就无法确定.此外，包括两个查询的两个 EXPLAIN PLAN 会有所帮助.
Without a full definition of both tables, and all of their indexes, it's not possible to tell for certain. Also, including the two EXPLAIN PLANs for the two queries would help.
不过现在，我可以详细说明一些事情......
For now, however, I can elaborate on a couple of things...
如果 INDEX 是 CLUSTERED(这也适用于 PRIMARY KEY)，则数据实际上是按照 INDEX 的顺序物理存储的.这意味着知道您想要 position x 在 INDEX 中也隐含意味着您想要 position x 在 TABLE 中.
If an INDEX is CLUSTERED (this also applies to PRIMARY KEYs), the data is actually physically stored in the order of the INDEX. This means that knowing you want position x in the INDEX also implicity means you want position x in the TABLE.
但是，如果 INDEX 未聚类，则 INDEX 只是为您提供查找.实际上，INDEX 中的 position x 对应于 TABLE 中的 position y.
If the INDEX is not clustered, however, the INDEX is just providing a lookup for you. Effectively saying position x in the INDEX corresponds to position y in the TABLE.
此处的重要性在于访问 INDEX 中未指定的字段时.这样做意味着您必须实际转到 TABLE 来获取数据.在 CLUSTERED INDEX 的情况下，您已经在那里了，查找该字段的开销非常低.但是，如果 INDEX 未聚类，则您必须有效地将 TABLE 加入 INDEX，然后找到您感兴趣的字段.
The importance here is when accessing fields not specified in the INDEX. Doing so means you have to actually go to the TABLE to get the data. In the case of a CLUSTERED INDEX, you're already there, the overhead of finding that field is pretty low. If the INDEX isn't clustered, however, you effectifvely have to JOIN the TABLE to the INDEX, then find the field you're interested in.
注意；在 (id_dernier_fichier, codega) 上有一个复合索引与在 (id_dernier_fichier) 上有一个索引和在 (codega) 上有一个单独的索引有很大不同代码>.
Note; Having a composite index on (id_dernier_fichier, codega) is very different from having one index on just (id_dernier_fichier) and a seperate index on just (codega).
就您的查询而言，我认为您根本不需要更改代码.但是您可能从更改索引中受益.
In the case of your query, I don't think you need to change the code at all. But you may benefit from changing the indexes.
您提到要访问许多字段.将所有这些字段放在一个复合索引中可能不是最好的解决方案.相反，您可能希望在 (id_dernier_fichier) 上创建一个 CLUSTERED INDEX.这意味着一旦找到了 *id_dernier_fichier*，您就已经在正确的位置获取所有其他字段了.
You mention that you want to access many fields. Putting all those fields in a composite index is porbably not the best solution. Instead you may want to create a CLUSTERED INDEX on (id_dernier_fichier). This will mean that once the *id_dernier_fichier* has been located, you're already in the right place to get all the other fields as well.
13.2.10.1.聚集索引和二级索引
13.2.10.1. Clustered and Secondary Indexes
每个 InnoDB 表都有一个称为聚集索引的特殊索引，用于存储行的数据:
Every InnoDB table has a special index called the clustered index where the data for the rows is stored:
- 如果你在你的表上定义了一个 PRIMARY KEY，InnoDB 会使用它作为聚集索引.
- 如果你没有为你的表定义 PRIMARY KEY，MySQL 会选择第一个只有 NOT NULL 列的 UNIQUE 索引作为主键，InnoDB 使用它作为聚集索引.
- 如果表没有 PRIMARY KEY 或合适的 UNIQUE 索引，InnoDB 会在包含行 ID 值的合成列上内部生成一个隐藏的聚集索引.行按 InnoDB 分配给此类表中行的 ID 排序.行 ID 是一个 6 字节的字段，随着插入新行而单调增加.因此，按行 ID 排序的行实际上是按插入顺序排列的.
这篇关于MySql 查询很慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
