MySQL解释：是什么导致了“Using temporary; Using filesort”？

Question

我打算使用这个SQL SELECT创建一个视图，但是它的解释显示它正在使用临时文件和文件排序。我无法确定需要哪些索引来解决此问题。 主要是想知道为什么它使用文件排序而不是使用索引进行排序。

以下是我的表格:

CREATE TABLE `learning_signatures` (
  `signature_id` int(11) NOT NULL AUTO_INCREMENT,
  `signature_file` varchar(100) NOT NULL,
  `signature_md5` varchar(32) NOT NULL,
  `image_file` varchar(100) NOT NULL,
  PRIMARY KEY (`signature_id`),
  UNIQUE KEY `unique_signature_md5` (`signature_md5`)
) ENGINE=InnoDB AUTO_INCREMENT=640 DEFAULT CHARSET=latin1

CREATE TABLE `learning_user_suggestions` (
  `user_suggestion_id` int(11) NOT NULL AUTO_INCREMENT,
  `signature_id` int(11) NOT NULL,
  `ch` char(1) NOT NULL,
  `time_suggested` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`user_suggestion_id`),
  KEY `char_index` (`ch`),
  KEY `ls_sig_id_indx` (`signature_id`),
  KEY `user_id_indx` (`user_id`),
  KEY `sig_char_indx` (`signature_id`,`ch`)
) ENGINE=InnoDB AUTO_INCREMENT=1173 DEFAULT CHARSET=latin1

以下是我计划在视图中使用的有问题的 SQL 语句：

select ls.signature_id, ls.signature_file, ls.signature_md5, ls.image_file, sug.ch , count(sug.ch) AS suggestion_count
from (`learning_signatures` `ls` left join `learning_user_suggestions` `sug` on(ls.signature_id = sug.signature_id))
group by ls.signature_id, sug.ch;

从 explain 的输出结果:

id  select_type table   type    possible_keys                   key             key_len ref                 rows    Extra
1   SIMPLE      ls      ALL     NULL                            NULL            NULL    NULL                514     "Using temporary; Using filesort"
1   SIMPLE      sug     ref     ls_sig_id_indx,sig_char_indx    ls_sig_id_indx  4       wwf.ls.signature_id 1

另一个例子，这次使用了 where 子句：

explain select ls.signature_id, ls.signature_file, ls.signature_md5, ls.image_file, sug.ch , count(sug.ch) AS suggestion_count
from (`learning_signatures` `ls` left join `learning_user_suggestions` `sug` on(ls.signature_id = sug.signature_id))
WHERE signature_md5 = '75f8a5b1176ecc2487b90bacad9bc4c'
group by ls.signature_id, sug.ch;

解释输出:

id  select_type table   type    possible_keys                key                    key_len ref     rows    Extra
1   SIMPLE      ls      const   unique_signature_md5         unique_signature_md5   34      const   1       "Using temporary; Using filesort"
1   SIMPLE      sug     ref     ls_sig_id_indx,sig_char_indx ls_sig_id_indx         4       const   1   

- bradvido

---

发布EXPLAIN输出，发布你的CREATE VIEW语句。 - Michael J.V.

好的，我刚刚添加了解释输出。我还没有创建视图，我想先优化SELECT。 - bradvido

那么这里到底有什么问题？你没有一个限制数据集的因素，整个learning_signatures表将被提取。使用temporary可能是因为你的innodb_buffer_pool大小。 - Michael J.V.

@Michael，你说得对。那个查询中没有where子句。但是如果我添加一个WHERE，它仍然会显示使用临时表和文件排序。 - bradvido

查看带有 where 子句的更新示例。为什么它要使用 filesort？ - bradvido

如果您想了解什么是“使用文件排序”，这里有一些阅读材料：http://www.mysqlperformanceblog.com/2009/03/05/what-does-using-filesort-mean-in-mysql/ - Michael J.V.

Answer 1

在你的第一个查询中，你所做的是将签名表与用户建议连接起来，得到许多行，然后使用用户建议中的一些列对结果进行分组。但是，没有为连接表定义索引来帮助分组，因为它必须在先前连接的表上定义。

相反，你应该尝试从用户建议创建一个已经通过ch和signature_id分组的派生表，然后再加入它。

SELECT ls.signature_id, ls.signature_file, ls.signature_md5, ls.image_file, 
       sug.ch, sug.suggestion_count
FROM learning_signatures ls
LEFT JOIN 
  (SELECT s.signature_id, s.ch, count(s.ch) as suggestion_count
    FROM learning_user_suggestions s 
    GROUP BY s.signature_id, s.ch ) as sug
ON ls.signature_id = sug.signature_id

现在优化器应该能够使用您的sig_char_indx索引进行分组，派生表不会比您的签名表大，您可以使用唯一列连接两个表。

仍然需要对签名表进行完全扫描，但这是无法避免的，因为您无论如何都要选择全部。

至于第二个查询，如果您想将签名限制为一个，请添加

WHERE ls.signature_md5='75f8a5b1176ecc2487b90bacad9bc4c'

将之前的查询延续到末尾，并且只按s.ch分组，因为只有一个signature_id会与您的md5匹配。优化器现在应该使用md5索引来进行where操作和char_index进行分组。

- piotrm

---

非常好。这个很有效，优化器按照你在查询中描述的方式工作。感谢详细的解释，我现在明白了区别。 - bradvido

Answer 2

也许如果你在 learning_signatures 上创建一个包含 signature_md5 和 signature_id 的索引（并且按照这个顺序），会有所帮助。

`KEY `md5_id` (`signature_md5`,`signature_id`)?

虽然我不是MySQL专家，但我发现创建一个包含where子句和join子句的键通常有助于消除临时和文件排序。

- Damp

---

谢谢你的建议，但我刚试了一下，它并没有清除临时文件或文件排序。 - bradvido

Answer 3

使用索引。通过在查询中使用EXPLAIN，找到需要的字段。

但是，如果您的数据库主要是写入操作（读取较少），则可能不希望使用索引，因为它们可能会对写入性能产生负面影响。

- sjas