MySQL 对连接表列的排序非常慢(临时表)

Question

我有一些表:

object 
person 
project 
[...] (some more tables) 
type 

对象表有所有其他表的外键。

现在我做这样的查询:

SELECT * FROM object 
LEFT JOIN person ON (object.person_id = person.id) 
LEFT JOIN project ON (object.project_id = project.id)
LEFT JOIN [...] (all other joins)
LEFT JOIN type ON (object.type_id = type.id)
WHERE object.customer_id = XXX 
ORDER BY object.type_id ASC
LIMIT 25

即使对于大型结果集，这也能完美且快速地工作。例如我有 90000 个对象，查询大约需要 3 秒。结果相当大，因为表有很多列并且所有列都被提取了。有关信息:我将 Symfony 与 Propel、InnoDB 和“doSelectJoinAll”函数一起使用。

但是如果做这样的查询(按 type.name 排序):

SELECT * FROM object 
LEFT JOIN person ON (object.person_id = person.id) 
LEFT JOIN project ON (object.project_id = project.id)
LEFT JOIN [...] (all other joins)
LEFT JOIN type ON (object.type_id = type.id)
WHERE object.customer_id = XXX 
ORDER BY type.name ASC
LIMIT 25

查询大约需要200秒!

解释:

id  | select_type   | table     | type      | possible_keys | key       | key_len   | ref           | rows      | Extra
1   | SIMPLE    | object    | ref       | object_FI_2   | object_FI_2   | 4     | const         | 164966    | Using where; Using temporary; Using filesort
1   | SIMPLE    | person    | eq_ref    | PRIMARY   | PRIMARY   | 4         | db.object.person_id   | 1     
1   | SIMPLE    | ...       | eq_ref    | PRIMARY   | PRIMARY   | 4         | db.object...._id  | 1     
1   | SIMPLE    | type      | eq_ref    | PRIMARY   | PRIMARY   | 4         | db.object.type_id     | 1     

我在进程列表中看到，MySQL 正在为连接表上的此类排序创建一个临时表。

向 type.name 添加索引并没有提高性能。只有大约 800 个类型行。

我发现许多连接和大结果是问题所在，因为如果我只使用一个连接进行查询，例如:

SELECT * FROM object 
LEFT JOIN type ON (object.type_id = type.id)
WHERE object.customer_id = XXX 
ORDER BY type.name ASC
LIMIT 25

它的运行速度和预期的一样快。

有没有办法在具有许多连接表的大结果集上改进此类排序查询？或者对连接表列进行排序只是一个坏习惯，无论如何都不应该这样做？

谢谢

Answer 1

LEFT 妨碍了重新排列表格的顺序。没有任何 LEFT 的速度有多快？你得到相同的答案吗？

LEFT 可能是一个转移注意力的问题……优化器可能会这样做:

1. 决定表格的顺序。考虑任何WHERE 过滤和任何LEFTs。因为 WHERE object.customer_id = XXX，object 可能是最好的开始表。
2. 从 object 中获取满足 WHERE 的行。
3. 从其他表中获取所需的列(执行JOINs)。
4. 根据ORDER BY排序**见下文
5. 提供前 25 行。

** 让我们更深入地研究这两个:

WHERE object.customer_id = XXX ORDER BY object.id
WHERE object.customer_id = XXX ORDER BY virtually-anything-else

您有 INDEX(customer_id)，对吗？表是 InnoDB，对吗？那么，每个二级索引都隐含地包含 PRIMARY KEY，就好像您说的是 INDEX(customer_id, id)。第一个 WHERE + ORDER BY 的最佳索引正是这样。它将定位到 XXX 并扫描 25 行，然后停止。您可能会说步骤 2、4、5 混合在一起。

第二个 WHERE 只是收集第 4 步中的所有内容。这可能有数千行。因此它可能会慢很多。

另见 article on building optimal indexes .