MySQL ORDER BY极其缓慢 - 即使有索引也是如此

Question

我有一个非常复杂的查询，其中包含许多连接，在没有排序的情况下运行得非常好。但是，一旦我尝试按任何字段排序，它就会执行得非常慢，需要大约30秒才能完成。

以下是查询语句：

SELECT SQL_NO_CACHE *
FROM et_order

INNER JOIN et_order_type ON et_order.type_id = et_order_type.id
INNER JOIN et_order_data ON et_order.id = et_order_data.order_id
INNER JOIN et_user et_user_consultant ON et_order.user_id_consulting = et_user_consultant.id
INNER JOIN et_customer ON et_order.customer_id = et_customer.id
INNER JOIN et_appointment ON et_order.appointment_id = et_appointment.id
INNER JOIN et_order_status order_status ON et_order.order_status_id = order_status.id
INNER JOIN et_status glass_r_status ON et_order_data.status_id_glass_r = glass_r_status.id
INNER JOIN et_status glass_l_status ON et_order_data.status_id_glass_l = glass_l_status.id

ORDER BY et_order.id DESC 

LIMIT 50

最初的查询语句更大，并且有各种WHERE操作，但即使是没有任何条件的基本查询速度也不合理地慢。当我删除ORDER BY et_order.id DESC时，查询只需大约0.01秒即可获取。

在我的原始查询中，我单独选择了我需要的每个字段 - 现在为了更好地阅读语句而改成了'SELECT *'。

Explain Select的结果如下：

This is a MySQL query result with 9 rows and the following columns: id, select_type, table, type, possible_keys, key, key_len, ref, rows, and Extra. The query involves multiple tables including et_customer, et_order, et_user_consultant, et_appointment, et_order_data, et_order_type, glass_l_status, order_status, and glass_r_status. There are various types of joins and keys used in the query. The result shows that temporary filesort is used and there is also filtering using the "where" clause.

我真正不理解的是为什么解释器说select语句在et_order_type上没有使用任何键。也许是因为它不需要，因为只有4行数据！？

但是，在et_order中有一个type_id索引：KEY type_id (type_id)。

我已经为我用于连接和排序的每个键添加了（单个）INDEX。这可能是问题吗？我需要创建组合索引吗？

该表包含大约200,000个数据集在et_order和et_order_data中，60,000个在et_customer中，150,000个在et_apointments中。其他内容可以忽略不计。

当我仅连接et_order_data和et_order_type时，也需要很长时间，并且解释器仍然会显示et_order_type的键为空。

说明：该SQL查询语句从表et_order中选择所有列，然后将其与et_order_type和et_order_data表进行内连接。连接条件是et_order表的type_id等于et_order_type表的id，以及et_order表的id等于et_order_data表的order_id。查询结果按照et_order表的id降序排列，最多返回50条记录。执行计划显示，第一个操作使用临时表并且使用文件排序，第二个操作使用了索引，第三个操作使用了连接缓存和where子句过滤。

这里可以查看et_order和et_order_type的表格结构：http://pastebin.com/PED6Edyx

有什么优化查询的技巧吗？

我尝试在子查询中排序，例如：

SELECT SQL_NO_CACHE *
FROM (SELECT * FROM et_order ORDER BY et_order.id DESC) as et_order
INNER JOIN et_order_type ON et_order.type_id = et_order_type.id
...

这很快，但没有帮助，因为我不仅要按et_order排序，还要按加入表的字段排序。

提前感谢您的帮助！

更新：

奇怪的是，当我将每个内连接更改为左连接时，它就像魅力一样工作...

从et_order LEFT JOIN et_order_type ON et_order.type_id = et_order_type.id LEFT JOIN et_order_data ON et_order.id = et_order_data.order_id LEFT JOIN et_user et_user_consultant ON et_order.user_id_consulting = et_user_consultant.id LEFT JOIN et_customer ON et_order.customer_id = et_customer.id LEFT JOIN et_appointment ON et_order.appointment_id = et_appointment.id LEFT JOIN et_order_status order_status ON et_order.order_status_id = order_status.id LEFT JOIN et_status glass_r_status ON et_order_data.status_id_glass_r = glass_r_status.id LEFT JOIN et_status glass_l_status ON et_order_data.status_id_glass_l = glass_l_status.id

ORDER BY et_order.id DESC LIMIT 50

有人知道为什么吗？

- bladr

---

每当你在数万行中看到“使用临时表; 使用文件排序”时，你就知道今天会很糟糕。 - tadman

使用临时表；使用文件排序 --- 这就是导致它变慢的原因。 - FredTheWebGuy

你能否在不对结果进行排序的情况下获取所需内容，而是在应用程序级别上对数据进行排序？ - FredTheWebGuy

@DudeSolutions 在应用程序级别进行排序会太过繁琐，需要对大约 200,000 个结果进行排序。 - bladr

@tadman 是的，文件排序很痛苦，但我该如何处理它呢？ - bladr

Answer 1

尝试这个查询

SELECT SQL_NO_CACHE *
FROM et_order
INNER JOIN et_order_type ON et_order.type_id = et_order_type.id
INNER JOIN et_order_data ON et_order.id = et_order_data.order_id
INNER JOIN et_user et_user_consultant ON et_order.user_id_consulting = et_user_consultant.id
INNER JOIN et_customer FORCE INDEX(et_customer.id) ON et_order.customer_id = et_customer.id
INNER JOIN et_appointment ON et_order.appointment_id = et_appointment.id
INNER JOIN et_order_status order_status ON et_order.order_status_id = order_status.id
INNER JOIN et_status glass_r_status ON et_order_data.status_id_glass_r = glass_r_status.id
INNER JOIN et_status glass_l_status ON et_order_data.status_id_glass_l = glass_l_status.id
ORDER BY et_order.id DESC LIMIT 50

- Ajay Patel

---

这会产生一个 SQL 错误。当我尝试执行以下语句：SELECT SQL_NO_CACHE * FROM et_order FORCE INDEX ( customer_id ) INNER JOIN et_order_type ON et_order.type_id = et_order_type.id ...它需要大约 120 秒才能完成 :-( - bladr