оптимизация таблицы mysql с 1,5 млн записей, большинство из которых мягко удалены

У меня есть таблица MySQL, в которой около 1,5 миллиона записей, а размер таблицы составляет 1,3 ГБ.

Я использую механизм мягкого удаления в этой таблице, что означает, что у меня есть столбец deleted_at, который указывает, была ли строка удалена и когда. если запись не удалена, то deleted_at значение равно NULL

Из этих 1,5 миллиона записей только 30 КБ не подлежат обратимому удалению. это означает, что к ним часто обращаются, в то время как к другим записям доступ почти не осуществляется, но в некоторых случаях это так.

Таким образом, эта таблица интенсивно используется и запрашивается для записей, которые не были удалены, а иногда и для записей, удаленных без возможности восстановления.

У меня есть тип индекса BTREE для записи deleted_at (с мощностью 35 КБ). Таблица со временем становится тяжелее, и очевидно, что это не масштабируемое решение.

Таблица движка MyISAM. большинство других таблиц InnoDB, но эта таблица сильно запрашивается с STORED PROCEDURE, и когда я перешел на InnoDB, запросы были намного медленнее.

Я ищу решение, которое не потребует замены оборудования. текущего оборудования достаточно для того, чтобы эта таблица имела хорошую производительность, но этого не произойдет, как только эта таблица вырастет еще больше.

Вещи, о которых я подумал:

  • разбиение на разделы, но я не могу использовать partitions, поскольку некоторые столбцы FULL TEXT проиндексированы.
  • разделить данные на две таблицы. один для удаленных строк и один для не удаленных строк, к которым часто обращались и которые запрашивали. это изменение требует значительных изменений инфраструктуры, поэтому я не тороплюсь с этим.
  • создание новой таблицы, которая будет синхронизироваться с исходной таблицей один раз в 10/20 минут вместо разделения и будет содержать только не удаленные строки. это потребует небольших изменений инфраструктуры, а обслуживание станет намного проще и безопаснее. разделение на две таблицы может привести к отсутствию записей из-за сбоев запросов, поскольку операция «УДАЛИТЬ» фактически перемещает строку из одной таблицы в другую, и, следовательно, требует сложного механизма

Какие еще у меня есть варианты? я могу дать приоритет некоторым строкам в таблице с MySQL? память мудрая.

У меня 10.3.20-MariaDB и 32 ГБ ОЗУ


mysql sql optimization stored-procedures myisam
person jony89    schedule 02.02.2020    source источник
comment
Есть ли определенные запросы, которые показывают значительно низкую производительность, которые вы могли бы предоставить, которые можно было бы оптимизировать лучше? Кроме того, можно улучшить индексы таблиц (включая индексы покрытия). Запрос на варианты улучшения может быть предложен, но если базовая структура и конкретные индексы и то, как структурированы запросы, могут быть улучшены другими способами.   -  person DRapp    schedule 02.02.2020
comment
Сколько у вас оперативной памяти? Какая версия MySQL?   -  person Rick James    schedule 03.02.2020
comment
@RickJames У меня 10.3.20-MariaDB и 32 ГБ ОЗУ   -  person jony89    schedule 03.02.2020
comment
FULLTEXT существует для InnoDB в 10.3. (Фактически, начиная с 10.0.5) Ваша таблица достаточно мала, чтобы ее можно было легко кэшировать в ОЗУ. Таким образом, полная неэффективность выборки, а затем отбрасывания 98% желаемых строк не должна быть такой уж плохой.   -  person Rick James    schedule 03.02.2020
comment
@RickJames, что ты имеешь в виду? Я пробовал использовать InnoDB с FULLTEXT. но у меня есть не такой простой запрос (включающий вычисление расстояний), который плохо работает с InnoDB. Вы говорите, что после разделения таблиц имеет смысл перейти на InnoDB?   -  person jony89    schedule 03.02.2020
comment
расчет расстояний ?? Это добавляет большую морщину к Вопросу. Как дела?   -  person Rick James    schedule 03.02.2020
comment
извините, если я ввел в заблуждение, но я упомянул использование тяжелой СОХРАНЕННОЙ ПРОЦЕДУРЫ, которая не работает с InnoDB   -  person jony89    schedule 03.02.2020
comment
Запрос дополнительной информации. # ядер, какие-либо SSD-устройства на сервере MySQL Host? Разместите на pastebin.com и поделитесь ссылками. Из корневого входа SSH текстовые результаты: B) ПОКАЗАТЬ ГЛОБАЛЬНЫЙ СТАТУС; после минимум 24 часов UPTIME C) ПОКАЗАТЬ ГЛОБАЛЬНЫЕ ПЕРЕМЕННЫЕ; D) ПОКАЗАТЬ ПОЛНЫЙ СПИСОК ПРОЦЕССОВ; E) заполните отчет MySQLTuner И Необязательную очень полезную информацию, если она доступна, включает - htop ИЛИ top для большинства активных приложений, ulimit -a для списка ограничений Linux / Unix, iostat -xm 5 3 для IOPS по устройству и количеству ядер / ЦП, для анализа настройки рабочей нагрузки сервера, чтобы предоставить предложения.   -  person Wilson Hauck    schedule 04.02.2020
comment
Пожалуйста, опубликуйте ТЕКСТ результатов SHOW CREATE TABLE (yourtablewith1.5Mrows); Спасибо   -  person Wilson Hauck    schedule 04.02.2020

Ответы (1)


arrow_upward
3
arrow_downward

MyISAM не кэширует строки, он кеширует только индексы. Он полагается на кеш файловой системы для буферизации строк.

Таким образом, вы можете попытаться убедиться, что хотя бы индекс полностью загружен в кеш:

  1. Увеличьте key_buffer_size, чтобы он был как минимум равным вашим индексам MyISAM для этой таблицы. Используйте SHOW TABLE STATUS, чтобы узнать размер индекса.
  2. Если у вас несколько таблиц MyISAM, вам может потребоваться выделить ключевой кеш специально для этой таблицы. См. ИНДЕКС КЭШЕРА.
  3. Предварительно загрузите индекс в ключевой кеш при запуске. См. ЗАГРУЗИТЬ ИНДЕКС В КЭШ.

Вы также можете рассмотреть возможность использования индексов с несколькими столбцами, адаптированных к вашим запросам. Например, если у вас есть запрос WHERE user_id = 1234 AND deleted_at IS NULL, вы должны создать индекс для (user_id, deleted_at).

Какие индексы вам нужны, зависит от запросов, которые вы хотите оптимизировать.

Честно говоря, я бы разделил таблицу так, чтобы удаленные строки находились во второй таблице. Это уменьшит размер вашей таблицы на 98% и может заставить запросы выполняться достаточно быстро, чтобы вам больше не нужно было использовать MyISAM.

person Bill Karwin    schedule 02.02.2020
comment
Даже я бы предпочел разделить данные на две таблицы, чтобы удаленные записи были изолированы от часто используемых данных. Может быть OP имеет какое-то ограничение. - person MKR; 02.02.2020
comment
Они просто говорят, что для этого потребуется много изменений инфраструктуры, что, как я полагаю, означает, что другой код, который ссылается на таблицу, должен будет измениться. - person Bill Karwin; 02.02.2020
comment
Спасибо за ответ. Я рассматриваю третий вариант: создание новой таблицы, которая будет синхронизироваться с исходной таблицей один раз в 10/20 минут вместо разделения. это потребует небольших изменений инфраструктуры, а обслуживание станет намного проще и безопаснее. разделение на две таблицы может привести к отсутствию записей из-за сбоев запросов, поскольку операция DELETE фактически перемещает строку из одной таблицы в другую и, следовательно, требует сложного механизма, что вы думаете по этому поводу? - person jony89; 03.02.2020
comment
Я бы использовал pt-archiver, бесплатный инструмент для безопасного копирования или перемещения данных из одной таблицы в другую. Это часть набора Percona Toolkit. percona.com/software/database-tools/percona-toolkit - person Bill Karwin; 03.02.2020
comment
find out the index size ты про столбец Index_length правильно? - person jony89; 04.02.2020
comment
Да, это соответствует размеру файла .MYI в случае таблицы MyISAM. - person Bill Karwin; 04.02.2020