如何避免MySQL查询触发temporary table/filesort？求索引优化方案

兄弟，我太懂这种明明是简单查询却突然蹦出临时表拖垮性能的糟心感了！之前踩过好多次类似的坑，咱们一步步来拆解问题，先搞清楚为啥会触发临时表，再针对性地加索引解决。

MySQL会在几种常见场景下被迫创建临时表，大概率你是碰到了其中一种：

• 查询里用了ORDER BY或GROUP BY，但排序/分组的字段不在关联后的索引覆盖范围内，MySQL只能把中间结果存到临时表来排序
• 多表关联时，关联字段没有合适的索引，导致优化器不得不全表扫描产生大量中间数据，只能用临时表缓存
• 使用了DISTINCT、UNION，或者聚合函数（比如SUM()、COUNT()）但没有索引支持
• 关联的表数据量差异太大，优化器选错了执行计划，被迫用临时表来处理数据

第一步你得先跑EXPLAIN看执行计划，重点盯Extra列里是不是有Using temporary，同时看type列的关联类型（要是显示ALL就是全表扫描，那肯定容易出问题）。

结合你的表数据量（Urls120w、Categories1000、CategoriesUrls4.4w），我给你分表说最实用的索引策略：

1. CategoriesUrls表（多对多关联核心，重点优化） #

这张表是90%临时表问题的根源，必须优先搞定：

• 建双向联合索引：如果你的查询是类似CategoriesUrls JOIN Categories ON category_id = Categories.id JOIN Urls ON url_id = Urls.id这类跨表关联，那直接建两个联合索引：

  CREATE INDEX idx_caturl_cat_url ON CategoriesUrls(category_id, url_id);
  CREATE INDEX idx_caturl_url_cat ON CategoriesUrls(url_id, category_id);
  

  这两个索引分别对应「从分类找URL」和「从URL找分类」的场景，能让关联时直接通过索引定位数据，不用全表扫描，从根源减少中间数据量，也就避免了临时表缓存。
• 如果你的查询里有GROUP BY category_id或者ORDER BY url_id，上面的联合索引已经覆盖了排序/分组字段，MySQL就不用再建临时表来排序了。

2. Urls表（120w行大表，精准建索引） #

大表别乱加索引，要贴合查询场景：

• 先确认Urls.id是主键（这个应该默认就有，但一定要检查）
• 如果查询里会过滤Urls的字段（比如WHERE Urls.status = 1）或者按某个字段排序（比如ORDER BY Urls.create_time），那建覆盖索引：比如查询是SELECT Urls.url FROM CategoriesUrls JOIN Urls ON ... WHERE Urls.status = 1 ORDER BY Urls.create_time，就建：

  CREATE INDEX idx_urls_status_create_time ON Urls(status, create_time, url);
  

  把查询需要的所有字段都放进索引里，MySQL直接从索引拿数据，不用回表查原数据，既快又不会触发临时表。
• 如果只是基础的关联查询，那只要id主键存在，配合CategoriesUrls的索引就够了。

3. Categories表（1000行小表，锦上添花） #

小表本身全表扫描也快，但加个索引能让关联更顺畅：

• 确保Categories.id是主键
• 如果查询里会按分类名称过滤（比如WHERE Categories.name = '技术教程'），那建：

  CREATE INDEX idx_categories_name ON Categories(name);
  

  减少关联时的匹配时间，间接降低中间结果的大小，避免临时表。

如果你有一堆简单查询都触发临时表，那可以按这几步批量处理：

• 把所有出问题的查询都跑一遍EXPLAIN，把Using temporary的场景列出来，找共性：是不是都是某类排序/分组，或者某类跨表关联？
• 优先给**多对多关联表（比如你的CategoriesUrls）**建双向联合索引，这类表是跨表查询的核心，大部分性能瓶颈都在这。
• 给所有ORDER BY、GROUP BY的字段组合建联合索引，注意顺序：过滤条件在前，排序/分组字段在后，最后加查询需要的返回字段（覆盖索引）。
• 别用SELECT *，只选需要的字段，这样更容易做覆盖索引，减少数据量，也就降低了触发临时表的概率。

加完索引后，再跑EXPLAIN看Extra列，要是Using temporary消失了，就说明优化生效了。如果还是有，那可能是查询写法的问题（比如用了UNION而不是UNION ALL，或者排序字段是计算出来的），这时候就得调整查询语句了。

内容的提问来源于stack exchange，提问作者xotihcan