为什么这个INNER JOIN / ORDER BY mysql查询这么慢？ [英] Why is this INNER JOIN/ORDER BY mysql query so slow?

问题描述

我有非常大的客户数据库。在我添加 ORDER BY 之前，这个查询还可以。如何优化查询速度？

I have very big database of customers. This query was ok before I added ORDER BY. How can I optimize my query speed?

$sql = "SELECT * FROM customers 
LEFT JOIN ids ON customer_ids.customer_id = customers.customer_id AND ids.type = '10'
ORDER BY customers.name LIMIT 10";

ids.type 和 customers.name 是我的索引

解释查询

id  select_type  table      type    possible_keys   key     key_len     ref     rows    Extra   
1   SIMPLE       customers  ALL     NULL            NULL    NULL        NULL    955     Using temporary; Using filesort
1   SIMPLE       ids        ALL     type            NULL    NULL        NULL    3551    Using where; Using join buffer (Block Nested Loop)

推荐答案

（我假设你打算键入 id .customer_id = customer.customer_id 而不是customer_ids.customer_id）

(I assume you meant to type ids.customer_id = customer.customer_id and not customer_ids.customer_id)

没有ORDER BY mysql抓住10型10的前10个（索引） ，为他们看了顾客，并完成了。 （注意这里的LEFT JOIN实际上是一个INNER JOIN，因为连接条件只适用于两个表中都匹配的行）

Without the ORDER BY mysql grabbed the first 10 ids of type 10 (indexed), looked up the customer for them, and was done. (Note that the LEFT JOIN here is really an INNER JOIN because the join conditions will only hold for rows that have a match in both tables)

使用ORDER BY mysql是可能检索所有类型= 10个客户，然后按名字对它们进行排序以找到前10个。

With the ORDER BY mysql is probably retrieving all type=10 customers then sorting them by first name to find the first 10.

您可以通过非规范化来加快速度customers表（将类型复制到客户记录中）或创建映射表以保存 customer_id，name，type 元组。在任何一种情况下，在（类型，名称）上添加索引。如果使用映射表，则使用它与客户和ID进行3向连接。

You could speed this up by either denormalizing the customers table (copy the type into the customer record) or creating a mapping table to hold the customer_id, name, type tuples. In either case, add an index on (type, name). If using the mapping table, use it to do a 3-way join with customers and ids.

如果type = 10相当常见，您也可以强制查询按名称查看customers表，并使用STRAIGHT JOIN检查每个表的类型。它不会像复合索引一样快，但它会比拉起所有匹配更快。

If type=10 is reasonably common, you could also force the query to walk the customers table by name and check the type for each with STRAIGHT JOIN. It won't be as fast as a compound index, but it will be faster than pulling up all matches.

如上所述，在查询上运行EXPLAIN请参阅mysql正在使用的查询计划。

And as suggested above, run an EXPLAIN on your query to see the query plan that mysql is using.

这篇关于为什么这个INNER JOIN / ORDER BY mysql查询这么慢？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！