为什么此mySQL查询速度极慢? [英] Why is this mySQL query extremely slow?

问题描述

Given是一个名为"orders_products"的mySQL表，具有以下相关字段:

Given is a mySQL table named "orders_products" with the following relevant fields:

• products_id
• orders_id

两个字段都被索引.

我正在运行以下查询:

SELECT products_id, count( products_id ) AS counter
FROM orders_products
WHERE orders_id
IN (
  SELECT DISTINCT orders_id
  FROM orders_products
  WHERE products_id = 85094
)
AND products_id != 85094
GROUP BY products_id
ORDER BY counter DESC
LIMIT 4

此查询耗时极长，大约20秒.否则数据库不会很忙，并且在其他查询上表现良好.

This query takes extremely long, around 20 seconds. The database is not very busy otherwise, and performs well on other queries.

我想知道是什么导致查询如此缓慢?

I am wondering, what causes the query to be so slow?

表很大(大约有150万行，大小约为210 mb)，这可能是内存问题吗?

The table is rather big (around 1,5 million rows, size around 210 mb), could this be a memory issue?

有没有一种方法可以准确地弄清mySQL花费了这么长时间吗?

Is there a way to tell exactly what is taking mySQL so long?

说明输出:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   PRIMARY     orders_products     range   products_id     products_id     4   NULL    1577863     Using where; Using temporary; Using filesort
2   DEPENDENT SUBQUERY  orders_products     ref     orders_id,products_id   products_id     4   const   2   Using where; Using temporary

推荐答案

使用WHERE ID IN (subquery)的查询在mysql上的性能非常差.

Queries that use WHERE ID IN (subquery) perform notoriously badly with mysql.

但是在大​​多数情况下，可以将它们重写为JOIN，这也不例外:

With most cases of such queries however, it is possible to rewrite them as a JOIN, and this one is no exception:

SELECT
    t2.products_id,
    count(t2.products_id) AS counter
FROM orders_products t1
JOIN orders_products t2
    ON t2.orders_id = t1.orders_id
    AND t2.products_id != 85094 
WHERE t1.products_id = 85094
GROUP BY t2.products_id
ORDER BY counter DESC
LIMIT 4

如果要返回没有其他产品的行(并显示为零)，请将联接更改为LEFT JOIN.

If you want to return rows where there are no other products (and show a zero count for them), change the join to a LEFT JOIN.

请注意，表的第一个实例如何具有WHERE products_id = X，它允许索引查找并立即减少行数，表的第二个实例具有目标数据，但它在id字段上进行了查找(再次快速)，但在联接条件中进行过滤以计算其他个产品.

Note how the first instance of the table has the WHERE products_id = X, which allows index look up and immediately reduces the number of rows, and the second instance of the table has the target data, but it looked up on the id field (again fast), but filtered in the join condition to count the other products.

这篇关于为什么此mySQL查询速度极慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！