由于ORDER BY未使用索引，导致SQL查询速度缓慢。

Question

我有一个查询：

SELECT cl.title, cl.URL, cl.ID AS ad_id, cl.cat_id, cl.price, cs.name AS cat_name, pix.file_name, area.area_name 
FROM classifieds cl 
FORCE INDEX (advertiser_id) 
INNER JOIN classifieds_pix pix ON cl.ID = pix.classified_id 
INNER JOIN cat_names_sub cs ON cl.cat_id = cs.ID 
INNER JOIN zip_codes zip ON cl.zip_id = zip.zip_id 
INNER JOIN area_names area ON zip.area_id = area.id 
WHERE cl.confirmed = 1 AND cl.price != '' AND cl.country = 'de' 
GROUP BY cl.advertiser_id
ORDER BY cl.timestamp DESC 
LIMIT 5

当classifieds包含168k行时，需要> 1秒的时间，这太长了。使用“FORCE INDEX（advertiser_id）”让我在没有“ORDER BY”子句的情况下将其降至0.00x秒。timestamp列也被索引，我尝试添加“FORCE INDEX（timestamp）”，但它没有起作用。

EXPLAIN显示，在classifieds表的第一个SELECT上，“Using where; Using temporary; Using filesort” - 这显然会导致性能问题。

你能帮我解决这个问题吗？

提前感谢！

PS：此查询的目的是获取5个最新的分类广告（包括一些附加信息，如图片、类别、邮政编码和区域名称）。此外，每位广告主只能显示一个分类广告。这很难吗？

PPS：我尽可能地锁定了问题，并最终得到了这个查询。

SELECT cl.title
FROM classifieds cl
GROUP BY cl.advertiser_id
ORDER BY cl.timestamp DESC
LIMIT 5

原本需要23秒才能完成！但是使用FORCE INDEX (advertiser_id)后，只需要1秒钟即可完成。如果我移除GROUP BY或者ORDER BY中的任意一个，时间将缩短至0.0003秒。

我的表格/索引可能有什么问题吗？我不应该需要FORCE INDEX（顺带提一下：USE INDEX不起作用 - 我必须强制使用它！）并且不应该需要那么长的时间！

- eWolf

---

我对在 GROUP BY cl.advertiser_id 的上下文中执行 SELECT 是什么意思感到困惑。 - Narveson

GROUP BY 确保只选择每个广告主的一个分类。 - eflorico

你能否在 http://pastie.org/ 上发布你的查询解释计划，展示你查询中所有表的索引和创建表的语句吗？这样我们或许可以提供帮助。谢谢。 - Jon Black

我已经更新了帖子，包括你请求的数据。 - eflorico

时间戳的基础是什么？最后“接触”时间，还是最近广告发布者发布的指示器？或者，是否存在另一个与广告发布者最新广告相关的“ID”键（可能是顺序的）？如果列出多个广告但计划在未来的时间显示，如果您只关心即将到来的时间或给定时间范围内的时间，则可能不希望这样做... 这可以显着帮助/影响查询。 - DRapp

时间戳是广告创建日期 - 没有预定的广告，所有广告都会立即发布。我需要的唯一过滤是在查询顶部的 WHERE 子句中完成的。 - eflorico

Answer 1

我认为无论如何都无法避免的事实是，对168k行数据进行排序需要一些操作，无论是否建立索引。虽然可以通过索引在表中查找行，但一旦找到了这些行，引擎仍然需要对它们进行排序。

顺便说一句，1秒钟对我来说听起来很合理。

（已删去建议使用其他索引的编辑；原帖已尝试过，但未成功）

- Brian

---

“Using where; Using temporary; Using filesort”这些信息不是表明某些地方没有被优化到位吗？ - eflorico

@eWolf - 更正，使用文件排序并不意味着慢。 - ajreal

你在第一句话中打破了自己的逻辑。让我反过来说——“如果有一个可以用于过滤和按顺序检索记录的索引，我认为有一种避免对168M（或k）条记录进行排序的方法。” 之后你承认了这种可能性，并提到了复合索引。 - Unreason

@Brian，我认为答案中的一部分明显是误导性的。如果您编辑或在答案中声明第一部分是错误的，我将很乐意取消负评。 - Unreason

哎呀，也许真的无法提高排序的速度。但是，仅为了获取前5个而对168k条记录进行排序并不是非常有效率。我建议重新考虑最好的尝试方案，例如只选择最新的时间戳并确定它们属于哪个广告商。 - Brian

Answer 2

虽然稍微重构一下，但我认为你应该看看分类广告表中的where子句，看它是否有任何可以利用的索引...比如通过已确认、价格、国家等方式。无论哪个可用的概率最低的记录数，我都会首先列出——可能先列出国家，然后是已确认。此外，删除group by。你的查询没有聚合函数与之相关联。

SELECT STRAIGHT_JOIN
      cl.title, 
      cl.URL, 
      cl.ID AS ad_id, 
      cl.cat_id, 
      cl.price, 
      cs.name AS cat_name, 
      pix.file_name, 
      area.area_name  
   FROM 
      ( select clMax.advertiser_id, 
               max( clMax.TimeStamp ) as AdvMaxTime
           from findix.classifieds clMax
           where  clMax.confirmed = 1 
              AND clMax.price != '' 
              AND clMax.country = 'de'  
           group by 1
           order by 2 desc
           limit 5 ) clQualified,
      findix.classifieds cl,
      findix.classifieds_pix pix,
      findix.cat_names_sub cs,
      findix.zip_codes zip,
      findix.area_names area
   WHERE
          clQualified.advertiser_id = cl.advertiser_id
      AND clQualified.AdvMaxTime = cl.timestamp;
      AND cl.ID = pix.classified_id  
      AND cl.cat_id = cs.ID 
      AND cl.zip_id = zip.zip_id  
      AND zip.area_id = area.id

通过更改以匹配您的资格，我将其移动到一个内部预查询中，该查询针对符合条件的每个广告客户获取最大时间戳条目订单，并将其限制为前5个作为结果集的第一个“表”。从那里，我有5个记录要与其他表连接，这应该是几乎瞬间完成的，因为您正在遇到。

- DRapp

---

+1，因为我也对GROUP BY感到困惑，但无法确定它为什么会引起问题。 - orangepips

GROUP BY 是必要的，以使结果中的每个 advertiser_id 唯一 - 有关更多详细信息，请阅读我对原始帖子所做的最新编辑。 - eflorico

@eWolf，我已根据您的反馈进行了修订...这应该会有所帮助。 - DRapp

@eWolf，这个查询最终得到了你想要的结果吗？ - DRapp

Answer 3

你尝试过使用多索引吗？

像这样：

CREATE INDEX adv_tt ON classifieds ( advertiser_id , `timestamp` );

甚至包括这个：

CREATE INDEX adv_tt ON classifieds 
( confirmed , price , country , advertiser_id , `timestamp` );

附注：我不知道MySQL是先应用GROUP BY还是ORDER BY，如果先应用ORDER BY，则必须在索引中更改列的顺序（... timestamp，advertiser_id）

- BT26

Answer 4

你试过更新表上的统计数据吗？

- Heiko Hatzfeld

---

你是指 ANALYZE TABLE 吗？是的，我做了那个。 - eflorico

Answer 5

你的查询出了问题。你有GROUP BY cl.advertiser_id，但同时也有ORDER BY cl.timestamp DESC。 timestamp不在Group BY中，这是不允许的！

为什么你要使用Group BY！把它拿掉。移除你的force index。 然后修复你的代码，使其没有任何重复项，这不是group by的作用。

编辑： 尝试：

SELECT cl.title, cl.URL, cl.ID AS ad_id, cl.cat_id, cl.price, cl.timestamp
cs.name AS cat_name, pix.file_name, area.area_name 
FROM findix.classifieds cl 
INNER JOIN findix.classifieds_pix pix ON cl.ID = pix.classified_id 
INNER JOIN findix.cat_names_sub cs ON cl.cat_id = cs.ID 
INNER JOIN findix.zip_codes zip ON cl.zip_id = zip.zip_id 
INNER JOIN findix.area_names area ON zip.area_id = area.id 
WHERE cl.confirmed = 1 AND cl.price != '' AND cl.country = 'de' 
ORDER BY cl.timestamp DESC 

或者

SELECT cl.advertiser_id,cl.title, cl.URL, cl.ID AS ad_id, cl.cat_id, cl.price, cl.timestamp
max(cs.name) AS cat_name, max(pix.file_name) as file_name, max(area.area_name) as area.area_name
FROM findix.classifieds cl 
INNER JOIN findix.classifieds_pix pix ON cl.ID = pix.classified_id 
INNER JOIN findix.cat_names_sub cs ON cl.cat_id = cs.ID 
INNER JOIN findix.zip_codes zip ON cl.zip_id = zip.zip_id 
INNER JOIN findix.area_names area ON zip.area_id = area.id 
WHERE cl.confirmed = 1 AND cl.price != '' AND cl.country = 'de' 
Group By cl.advertiser_id,cl.title, cl.URL, cl.ID AS ad_id, cl.cat_id, cl.price, cl.timestamp
ORDER BY cl.timestamp DESC 

- Morons

---

“GROUP BY” 应该只选择每个广告主的一个分类 - 这样做是错误的吗？ - eflorico

如果子表中有4个不同的值，查询如何知道要选择哪一个？（请参见我的编辑）。 - Morons

你的第一个查询运行得非常慢（0.7秒）。EXPLAIN显示“Using temporary; Using filesort”。第二个查询需要1秒钟，也使用了临时表和文件排序。 - eflorico

请查看我原帖的最新编辑。我非常怀疑是我的索引/表格出了问题。 - eflorico