mysql - 在 MySQL 中使用 filesort 按价格列排序

Question

我有一个查询比这个查询稍微复杂一点(获得最便宜的价格)，但这代表了性能问题。因此查询不能更改。

我尝试创建不同的索引来加快排序速度，但没有运气。

需要什么索引才能更快地获得这个 1.4 秒的持久查询？如果我删除 ORDER BY，查询将持续 0.05 秒，但我需要排序。

查询

SELECT id AS pid                    
  FROM prices pt                    
  WHERE pt.id = (    
      SELECT pt2.id                        
      FROM prices pt2                          
      WHERE pt2.oid=pt.oid                         
      ORDER BY pt2.price                           
      LIMIT 1
    )

解释一下

1   PRIMARY pt  index   NULL    id_price    12  NULL    9144    Using where; Using index
2   DEPENDENT SUBQUERY  pt2 ref oid,oid_price   oid 4   oid 23  Using where; Using filesort

索引

PRIMARY     PRIMARY 9144    id
price       INDEX   703     price
oid         INDEX   397     oid
id_price    INDEX   9144    id,price,oid
oid_price   INDEX   4572    oid,price

Answer 1

查询无法更改的情况很少见；没有看过原著，很难下定论。例如，您似乎正在尝试获取 id 列表，其中这些 id 是其“oid”的最低价格。

您作为示例给出的查询可以很容易地重写为不需要相关子查询；相关子查询通常是性能问题的根源。

SELECT p.id 
FROM (SELECT oid, MIN(price) AS minPrice FROM prices GROUP BY oid) AS mins
INNER JOIN prices AS p ON mins.oid = p.oid AND mins.minPrice = p.price
;

(oid，价格)上的索引可能就是加快上述查询所需的全部内容。

注意:结果中的唯一区别应该是，如果有多个条目，则该结果可以为同一 oid 值返回多个 id 值最低价格；而不是有效地随机选择价格最低的 id 值之一。