Лучший способ оптимизировать операции MySQL после роста таблицы

Question

Справочная информация: у нас есть веб-сайт электронной коммерции, и мы пытаемся выяснить, как лучше всего обращаться с "историческими" данными для таблиц, которые используются очень часто и связаны слишком много записей (т. Е. Заказы, клиенты и т. д.).

Я особенно смотрю на 2 конкретных сценария:

• Миграция БД
• вЫБИРАЕТ

Миграция БД

В случае миграции БД мы начинаем понимать, что иногда нам нужно запустить какой-нибудь ALTER TABLE, который блокирует всю таблицу, и, если в таблице так много записей, это может занять некоторое время. Конечно, все операции с таблицей приостановлены до завершения миграции, что означает, что наша проверка может быть недоступна только потому, что мы меняем VARCHAR(15) в VARCHAR(256),

Начиная с MySQL 5.6, многие операции выполняются " INPLACE", что означает (из того, что я понял), что они не будут создавать блокировку полной таблицы: это нормально, но все еще не идеально - что если нам нужно изменить тип столбца (не может быть выполнен INPLACE), и мы действительно не хотим находиться в режиме обслуживания в течение нескольких минут?

Моя идея супер-гетто состояла в том, чтобы просто скопировать таблицу (скопировать ее), затем выполнить миграцию на скопированную таблицу, прекратить запись в исходную таблицу (т.е. заблокировать ее), скопировать данные, которые не были синхронизированы, в скопированную таблицу и поменять их местами. Я думаю, что инструмент Percona для миграции без простоев делает нечто подобное, так что, может быть, это "лучший" подход?

Мнения?

вЫБИРАЕТ

За SELECTsПоскольку к большинству старых данных обращаются очень редко, я подумал о том, чтобы разбить их по дате (например, до 2015 / после 2015 года), а затем изменить большинство наших запросов для получения данных. WHERE YEAR(created_at) >= 2015,

Если пользователю нужны его полные исторические данные, мы бы динамически удалили это условие. Это каким-то образом гарантирует, что данные хорошо разделены.

Любая другая идея? Как вы думаете, разделение может стоить того?

Answer 1

• Только до 5.7.1 вы можете быстро сделать это:

Размер VARCHAR можно увеличить, используя ALTER TABLE на месте, как в этом примере:

ALTER TABLE t1 ALGORITHM = INPLACE, ИЗМЕНИТЬ КОЛОННУ c1 C1 VARCHAR(255);

• Увидеть pt-online-schema-change,

• Если у вас уже настроена репликация, вы можете сыграть в игру ALTERing Раб, затем отказывает. (Да, инструменты Percona удобны в этой области.)

• Не "скрывайте" столбцы внутри функций; оптимизатор их не видит:

  ГДЕ ГОД (созданный_ат) >= 2015. -> ГДЕ созданный_ат>= '2015-01-01'

• Разделение только на 2 раздела вряд ли даст какой-либо выигрыш в производительности.

• Это разумно (и обычно делается) PARTITION BY RANGE какой-то даты (например, TO_DAYS()) с целью окончательной очистки (через DROP PARTITION) старые данные. DROP намного быстрее и менее агрессивен, чем большой DELETE что эта особенность сама по себе оправдывает разделение. Обрезка, о которой вы упоминали, редко ускоряет запросы (кроме случаев, когда индексы плохие). Больше обсуждения скользящих временных рядов