Одна из самых больших жалоб, которые я слышал о MySQL, заключается в том, что он блокирует таблицу, если вы пытаетесь изменить ее схему, например, добавить столбец или индекс.
Под "блокировкой таблицы" подразумевается, что я не могу ни читать, ни писать в таблицу? Иногда часами?
Это кажется довольно серьезным ограничением. Я собирался использовать MySQL для своего нового проекта, но это заставило меня задуматься.
Есть ли обходной путь для этого? Как вы справляетесь с задачей изменения схемы вашей производственной базы данных MySQL?
Кстати, кто-то сказал мне, что у Postgresql этой проблемы нет. Это правда - я могу читать и писать в таблицу Postgresql, изменяя ее схему? Есть ли штраф за производительность?
Хотелось бы услышать ваш опыт.
Да, MySQL полностью блокирует таблицу на время выполнения оператора ALTER TABLE. Большая часть этого времени тратится на физическое копирование таблицы, поэтому рекомендуется объединить все необходимые изменения в одном операторе ALTER TABLE.
Есть несколько подходов к устранению этой проблемы в действующей базе данных, если вы не можете получить приличный период обслуживания.
Во-первых, многие среды имеют таймауты в несколько минут, ожидая, пока таблица станет доступной для их запроса, и не знают (и это действительно сложно проверить), почему таблица заблокирована. Я использовал эту причуду живого веб-сайта, чтобы вносить изменения в таблицы. На сайте, за которым я раньше ухаживал, я полагал, что у нас будет около 7 минут, прежде чем кто-то начнет замечать. :-) Это помогает убедиться, что ваш босс на вашей стороне.
Другой способ сделать это - использовать трюк с выбором-вставкой-переименованием. Это хорошо работает, если на столе довольно низкий UPDATE частота или просто цель INSERTс. Основные шаги - скопировать схему таблицы, внести необходимые изменения, составить инструкцию для выполнения INSERT...SELECT от старого к новому и переименуйте таблицы (сделайте переименование одним оператором). Вам также необходимо заранее подготовить инструкцию для копирования любых «новых» записей, которые были добавлены или обновлены между SELECT и RENAME. Я также делал это несколько раз на прошлой работе.
Однако есть предостережения:
INSERT...SELECT это работает.SELECT и RENAME. Для таблиц, которые используются только для INSERTиспользуйте столбец auto_increment. Для таблиц, которые получают UPDATEs, вам понадобится надежный столбец с последними изменениями.Другие способы решения этой проблемы включают изменение ведомых устройств и отказ приложения. Это более тесно связано с тем, как ваши базы данных реплицируются. Я тоже этого не делал, поэтому не могу описать точные шаги.
Наконец, есть дюжина настроек сервера, которые вы можете изменить, и еще несколько, которые гораздо сложнее изменить, что повлияет на время, необходимое для копирования таблицы. Буфер сортировки - это одно, но также, сколько памяти разрешено использовать MySQL - другое. (Помните, что вы также можете установить множество из них для каждого соединения, а не устанавливать некоторые из них глобально.) При работе с большим количеством данных MySQL имеет эффект «переломного момента», когда все становится достаточно линейным до определенного размер, а потом вдруг попадет в ад. Часто возникают сложные запросы, работающие с большим количеством данных, и они связаны с размерами внутренних временных таблиц и объемом памяти, который им разрешено использовать, но могут возникать изменения в таблицах, поскольку они связаны с повторной индексацией данных. Это одна из причин, почему увеличение объема памяти для базы данных почти всегда полезно.
Это самая большая жалоба, которую вы слышали о MySQL? Боже, у меня есть ведро побольше, чем это ... (история для другого дня, возможно)
Да, MySQL полностью блокирует таблицу, когда вы запускаете для нее ALTER TABLE; никаких операций чтения или записи в течение всего времени, а запросы, которые пытаются это сделать, приостанавливаются до завершения. В нечетных случаях мне приходится изменять схему большой таблицы MySQL (маленькие завершают свои изменения достаточно быстро, чтобы не вызывать заметных проблем), я обычно просто планирую окно обслуживания и делаю это потом. Нетрудно точно определить, сколько времени займет такое изменение для данной таблицы на реплике.
Если у вас есть одно из тех идиотских менеджеров, которые не позволяют вам иметь разумные окна обслуживания (и если это так, бегать, как черт, на другую работу), то я слышал о людях, делающих такие вещи, как создание копии схемы таблицы, изменение пустую таблицу, затем выполните выборку-копирование в новую таблицу (с блокировкой записи для предотвращения изменений), а затем переименуйте таблицы. На мой взгляд, это слишком большой риск. Мааткит мк-таблица-синхронизация есть способ сделать это, если вы предпочитаете иметь возможность обвинять кого-то еще, когда он умирает.
В то время как PostgreSQL не имеет «гигантского замка гибели», вы по-прежнему значительно снижаете производительность при изменении схемы таблицы в PgSQL - это большой объем дискового ввода-вывода. Однако я не могу себе представить, как любая РСУБД сможет этого избежать.