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。

Answer 1

您可以使用 Dependent Subquery 删除临时表:

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 列表进行排序。

更新

如果此查询将一直运行，我会建议(不要太花哨)在供应表上设置触发器以更新跟踪此类计数器的合法表。