Какие данные включаются, когда MySQL создает временную таблицу?

Я читал о том, что поля VARCHAR преобразуются в поля CHAR всякий раз, когда MySQL создает временную таблицу.

Если у меня есть следующая таблица...

  • Превышение 100 тыс. строк
  • Дюжина столбцов VARCHAR с реальной длиной (например, 10 для телефонных номеров).
  • 5 столбцов VARCHAR установлены на 15 КБ (с длиной, которая может быть всего 20, но в худшем случае до 15 КБ)

И, скажем, у меня есть этот запрос...

  • Возвращает несколько столбцов, включая VARCHAR.
  • СОЕДИНЕНИЕ с полдюжиной других таблиц (не в столбцах VARCHAR)
  • Сортирует строки по дате и числовому идентификатору (т. е. не в столбцах VARCHAR).
  • Имеет предложение WHERE, которое не включает поля VARCHAR.
  • Имеет LIMIT 12 строк

Когда MySQL создает временную таблицу для сортировки и иногда группировки (в некоторых случаях), что оказывается во временной таблице?

Например, включает ли таблица только столбцы, необходимые для выбора возвращаемых строк (т. е. те, которые указаны в предложениях WHERE и ORDER BY), за которыми следует механизм базы данных, определяющий 12 строк в LIMIT, а затем считывающий данные только для эти 12 рядов? Или во временную таблицу включен больший блок данных (например, все потенциальные строки, включая длинные столбцы VARCHAR)?


mysql select temp-tables varchar
person Alan M.    schedule 28.11.2011    source источник
comment
вы создаете временную таблицу с этим оператором? CREATE TEMPORARY TABLE temp_table (count int);   -  person Matteo    schedule 28.11.2011
comment
@Matteo: Нет, я не создаю временную таблицу явно. Насколько я понимаю (или неправильно понимаю), временная таблица неявно создается всякий раз, когда мой запрос сортируется или группируется.   -  person Alan M.    schedule 28.11.2011

Ответы (2)


arrow_upward
1
arrow_downward

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

person Vladislav Rastrusny    schedule 28.11.2011
comment
Можете ли вы доказать это документально? - person gbn; 28.11.2011
comment
@FractalizeR: Не могли бы вы объяснить это более элементарно? По сути, мне интересно, влияет ли наличие или длина полей VARCHAR на производительность или память, когда эти столбцы VARCHAR не являются основой предложений WHERE, ORDER BY или GROUP BY. В моей упрощенной логике нет необходимости включать данные, не влияющие на выбор строк, пока эти строки (например, в моем случае 12, указанные оператором LIMIT) не будут определены. Я предполагаю (опять же упрощенно), что данные, которые я хотел вернуть (включая содержимое столбцов VARCHAR), будут затем считаны только для этих 12 строк. - person Alan M.; 28.11.2011
comment
@ЛиБ. Ну, в основном, это означает, что когда mysql строит временную таблицу в памяти и эта таблица имеет столбцы VARCHAR(X1), VARCHAR(X2)..., каждая строка данных этой таблицы будет занимать примерно X1+X2+...+Xn байт. Память для столбцов VARCHAR во временных таблицах резервируется так же, как и для столбцов CHAR (фиксированный размер). - person Vladislav Rastrusny; 28.11.2011

arrow_upward
0
arrow_downward

У меня была аналогичная ситуация, когда я пытался выяснить, необходимо ли просто иметь TEXT или BLOB в таблице, чтобы принудительно создать временную таблицу на диске, когда MySQL должен создать временную таблицу, или что она также основана на том, является ли этот столбец в запросе (предложение SELECT или WHERE).

Это был запрос (posts.body имеет тип TEXT)

SELECT * FROM posts p LEFT JOIN user u ON p.user_id = u.id LEFT JOIN setting s ON u.id = s.user_id WHERE (p.title LIKE '%search%' AND (p.status = 'PUBLISH' AND p.date <= 1368441957)) GROUP BY u.id LIMIT 5

Я видел, что это увеличивает переменную Created_tmp_disk_tables. Поэтому я изменил запрос на

SELECT p.id FROM posts p LEFT JOIN user u ON p.user_id = u.id LEFT JOIN setting s ON u.id = s.user_id WHERE (p.title LIKE '%search%' AND (p.status = 'PUBLISH' AND p.date <= 1368441957)) GROUP BY u.id LIMIT 5

И что интересно, счетчик не увеличился. Однако в документации MySQL упоминается следующее, что отличается от моего вывода.

Некоторые условия препятствуют использованию временной таблицы в памяти, и в этом случае вместо этого сервер использует таблицу на диске:

Наличие столбца BLOB или TEXT в таблице

...

person Arvind Gupta    schedule 13.05.2013