Пропустить копирование в таблицу tmp на диске mysql

У меня вопрос для больших запросов mysql. Можно ли пропустить копирование в таблицу tmp на диске, которую mysql берет для больших запросов, или можно ускорить его? потому что этот шаг слишком длинный, чтобы получить результаты моих запросов. Я прочитал на странице MySQL, что mysql выполняет это, чтобы сохранить память, но я не забочусь о сохранении памяти. Я просто хочу получить результаты моих запросов назад FAST, у меня достаточно памяти на моей машине. Кроме того, мои таблицы правильно проиндексированы, поэтому причина не в том, что мои запросы медленны.

Ответы

Ответ 1

Есть две вещи, которые вы можете сделать, чтобы уменьшить влияние этого

ВАРИАНТ # 1: Увеличьте переменные tmp_table_size и/или max_heap_table_size

Эти параметры будут определять, насколько велика временная таблица в памяти, прежде чем она будет считаться слишком большой, а затем страницы на диск в качестве временной таблицы MyISAM. Чем больше эти значения, тем меньше вероятность того, что вы получите "копирование в таблицу tmp на диске". Пожалуйста, убедитесь, что на вашем сервере достаточно ОЗУ и max_connections умеренно настроен, если для одного соединения с БД требуется много ОЗУ для собственных временных таблиц.

ВАРИАНТ # 2: используйте RAM-диск для таблиц tmp

Вы должны иметь возможность настроить RAM-диск в Linux, а затем установить tmpdir в mysql, чтобы быть папка с установленным RAM-диском.

Для начала настройте RAM-диск в ОС

Создайте папку в Linux под названием /var/tmpfs

mkdir /var/tmpfs

Затем добавьте эту строку в /etc/fstab (например, если вы хотите иметь 16-гигабайтный RAM-диск)

none                    /var/tmpfs              tmpfs   defaults,size=16g        1 2

и перезагрузите сервер.

Примечание. Можно сделать RAM-диск без перезагрузки. Просто не забудьте добавить вышеупомянутую строку в /etc/fstab, чтобы иметь RAM-диск после перезагрузки сервера.

Теперь для MySQL:

Добавьте эту строку в /etc/my.cnf

[mysqld]
tmpdir=/var/tmpfs

и перезапустите mysql.

ВАРИАНТ # 3: Получите таблицу tmp в RAM-диск ASAP (при условии, что вы сначала применяете OPTION # 2)

Возможно, вы захотите принудительно заставить таблицы tmp в RAM-диск как можно быстрее, чтобы MySQL не вращал свои колеса, перенося большие TMP-таблицы в памяти в RAM-диск. Просто добавьте это в /etc/my.cnf:

[mysqld]
tmpdir=/var/tmpfs
tmp_table_size=2K

и перезапустите mysql. Это приведет к тому, что даже минимальная таблица темпов появится на диске RAM. Вы можете периодически запускать ls -l /var/tmpfs, чтобы наблюдать за темпами таблицы.

Дайте ему попробовать!!!

CAVEAT

Если вы видите только таблицы temp в /var/tmpfs 24/7, это может повлиять на функциональность/производительность ОС. Чтобы убедиться, что /var/tmpfs не перенаселен, изучите настройки своих запросов. Как только вы это сделаете, вы увидите меньше tmp-таблиц, вносимых в/var/tmpfs.

Ответ 2

Вы также можете пропустить копию в таблицу tmp на дисковой части (в выбранном ответе нет ответа)

Если вы избегаете некоторых типов данных:

Поддержка типов данных переменной длины (включая BLOB и TEXT), не поддерживаемых MEMORY.

с https://dev.mysql.com/doc/refman/8.0/en/memory-storage-engine.html (или https://mariadb.com/kb/en/library/memory-storage-engine/, если вы использую mariadb).

Если ваша временная таблица достаточно мала: как сказано в выбранном ответе, вы можете

Увеличьте переменные tmp_table_size и/или max_heap_table_size

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