MySQL 解释 : what's causing 'Using temporary; Using filesort'

Question

我正计划使用此 SQL SELECT 创建 View ，但对它的解释显示它正在使用临时文件并使用文件排序。我不知道我需要什么索引来解决这个问题。大多数情况下，我想知道为什么它使用文件排序而不是使用索引进行排序。

这是我的表格:

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;

解释的输出:

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   

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 进行分组。