mysql temp sort,MySQL查询优化.避免临时文件排序

目前,我有一个包含近一百万行的表,我需要从中查询.我需要做的是根据给定产品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.

解决方法:

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(*)绝对未建立索引.但不要担心,filesort只是一个排序的花哨词-mysql可以选择使用有效的内存排序-并且无论您现在是这样做还是作为到索引临时表的合并排序,您都将拥有支付某处的排序费用.但是,这种情况非常好,因为一旦文件排序达到您设置的LIMIT,它将停止排序.它不会对commonProducts的整个列表进行排序.

更新资料

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

标签：mysql

来源： https://codeday.me/bug/20191102/1990397.html