MySQL Объясните: что вызывает "Использование временного; Использование filesort '

Я планирую создать представление, используя этот SQL SELECT, но объяснение для него показывает его с использованием временного и использования filesort. Я не могу понять, какие индексы мне нужны, чтобы исправить эту проблему. В основном, мне интересно, почему он использует filesort для использования индекса для сортировки.

Вот мои таблицы:

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

Ответы

Ответ 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 для группировки.

Ответ 2

Возможно, это поможет, если вы создадите индекс для learn_signatures, который содержит как signature_md5, так и signature_id (и в этом порядке)

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

Я не эксперт по MySQL, но обнаружил, что созданные ключи, которые инкапсулируют предложение where и предложение join, обычно помогают избавиться от временных и filesort

Ответ 3

Используйте индексы. Найдите поля, в которых они вам нужны, используя EXPLAIN в ваших запросах.

Если у вас, однако, есть база данных с записью (только чтение), вы можете отказаться от использования индексов, так как они могут негативно повлиять на производительность записи.