mysql - ORDER BY 导致 MySQL 查询变得极慢

Question

我有以下查询:

SELECT * 
FROM   products 
   INNER JOIN product_meta 
           ON products.id = product_meta.product_id 
   JOIN sales_rights 
     ON product_meta.product_id = sales_rights.product_id 
WHERE  ( products.categories REGEXP '[[:<:]]5[[:>:]]' ) 
   AND ( active = '1' ) 
   AND ( products.show_browse = 1 ) 
   AND ( product_meta.software_platform_mac IS NOT NULL ) 
   AND ( sales_rights.country_id = '240' 
          OR sales_rights.country_id = '223' ) 
GROUP  BY products.id 
ORDER  BY products.avg_rating DESC
LIMIT  0, 18;

在省略 ORDER BY 部分的情况下运行查询，查询将在 ~90 毫秒内运行，使用 ORDER BY 部分，查询将花费~8 秒。

我浏览了 SO，发现原因可能是在返回所有数据之前执行了排序，而我们应该在结果集上运行 ORDER BY反而？ (参见这篇文章:Slow query when using ORDER BY)

但我不太清楚如何执行此操作的 final方法？

Answer 1

I've browsed around SO and have found the reason for this could be that the sort is being executed before all the data is returned, and instead we should be running ORDER BY on the result set instead?

我觉得很难相信，但如果这确实是问题所在，我认为您需要做这样的事情。 (注意我把括号放在哪里。)

select * from
(
  SELECT products.id, products.avg_rating 
  FROM   products 
  INNER JOIN product_meta 
          ON products.id = product_meta.product_id 
  JOIN sales_rights 
    ON product_meta.product_id = sales_rights.product_id 
  WHERE  ( products.categories REGEXP '[[:<:]]5[[:>:]]' ) 
   AND ( active = '1' ) 
   AND ( products.show_browse = 1 ) 
   AND ( product_meta.software_platform_mac IS NOT NULL ) 
   AND ( sales_rights.country_id = '240' 
          OR sales_rights.country_id = '223' ) 
  GROUP  BY products.id 
) as X
ORDER  BY avg_rating DESC
LIMIT  0, 18;

此外，编辑您的问题并包含指向该建议的链接。我认为我们中的许多人都会从阅读中受益。

---

其他可能不相关的问题

WHERE 子句中使用的每一列都应该以某种方式建立索引。多列索引可能对于此特定查询表现更好。

products.categories 列似乎存储了您使用正则表达式过滤的多个值。在单个列中存储多个值通常不是一个好主意。

MySQL's GROUP BY is indeterminate .使用 GROUP BY 的标准 SQL 语句可能会返回更少的行，并且可能会更快地返回它们。