MySQL 查询优化。避免使用临时表和文件排序。

Question

目前我有一张接近100万行的表格需要进行查询。我需要根据给定的产品ID列表，对包含产品数量进行排名。

SELECT count(productID) AS commonProducts, packageID
FROM supply
WHERE productID IN (2,3,4,5,6,7,8,9,10) 
GROUP BY packageID 
ORDER BY commonProducts 
DESC LIMIT 10

这个查询已经能够正常工作，但我希望能进行改进。我尝试了在productId和packageID上建立复合索引，但相比于单独为每个列建立索引，它似乎会查找更多的行。

MySQL解释器

select_type: SIMPLE
table: supply
type: range
possible_keys: supplyID
key: supplyID
key_len: 3
ref: null
rows: 996
extra: Using where; Using temporary; Using filesort

我的主要关注点是查询使用了临时表和文件排序。我该如何优化这个查询？我认为最大的问题是 count() 函数和对 count() 函数结果的 ORDER BY 操作。

- IOInterrupt

---

在“SELECT”之前执行“EXPLAIN”以查看MySQL如何处理查询，以获得更多见解。 - ThinkCode

@IOInterrupt 供应ID索引长什么样？ - Karolis

@IOInterrupt 一个 packageID 是否可以有两个 productID 具有相同的值？如果是，那么这种情况常见吗？ - Karolis

supplyID索引只是supplyID列上的一个索引。packageID永远不会有相同的productID。但是，productID可能与许多packageID相关联。 - IOInterrupt

你能给我们提供创建供应表的DDL吗？ - Fabian Barney

Answer 1

你可以使用依赖子查询来删除临时表:

select * from 
  (
   SELECT count(productID) AS commonProducts, s.productId, s.packageID 
   FROM supply as s
   WHERE EXISTS
   (
      select 1 from supply as innerS 
        where innerS.productID in (2,3,4,5,6,7,8,9,10) 
          and s.productId = innerS.productId 
   )
   GROUP BY s.packageID
  ) AS t
ORDER BY t.commonProducts 
DESC LIMIT 10

内部查询与外部查询相连接并保留索引。您会发现，任何按commonProducts排序的查询（包括上面的查询）都将使用文件排序，因为count(*)肯定没有被索引。但不要害怕，文件排序只是一个花哨的排序词 - MySQL可以选择使用有效的内存排序 - 无论您是现在这样做还是在索引临时表的过程中进行归并排序，您都必须为此付出代价。然而，这种情况是相当好的，因为文件排序将在达到您设置的LIMIT后停止排序。它不会对整个commonProducts列表进行排序。

更新

如果该查询将一直运行，我建议（不要太花哨）在supply表上设置触发器，以更新跟踪计数器的合法表。

- Michael Hays

Answer 2

创建一个临时结果集：

SELECT  TMP.*
FROM (  SELECT count(productID) AS commonProducts, packageID
        FROM supply
        WHERE productID IN (2,3,4,5,6,7,8,9,10)
        GROUP BY packageID 
) AS TMP 

ORDER BY commonProducts 
DESC LIMIT 10

- Harold Sota

---

似乎存在相同的问题。实际上，根据EXPLAIN的结果，它增加了搜索的行数。 - IOInterrupt

Answer 3

也许这不是最优雅的方式，我也不能保证它会更快，因为一切都取决于您特定的数据。但在某些情况下，这会带来更好的结果：

SELECT count(*) AS commonProducts, packageID
FROM (
    SELECT packageID FROM supply WHERE productID = 2
    UNION ALL
    SELECT packageID FROM supply WHERE productID = 3
    UNION ALL
    .
    .
    .
    SELECT packageID FROM supply WHERE productID = 10
) AS t
GROUP BY packageID
ORDER BY commonProducts DESC
LIMIT 10

- Karolis

---

谢谢您的建议，但我所举的例子并不是正常运行的方式。通常产品ID可能会相距很远并且“随机”。 - IOInterrupt