ALTER TABLE без блокировки таблицы?
При выполнении оператора ALTER TABLE в MySQL вся таблица блокируется для чтения (разрешает одновременное чтение, но запрещает одновременную запись) на время выполнения оператора. Если это большая таблица, инструкции INSERT или UPDATE могут быть заблокированы на очень долгое время. Есть ли способ выполнить «горячее изменение», например, добавить столбец таким образом, чтобы таблица оставалась обновляемой на протяжении всего процесса?
В основном меня интересует решение для MySQL, но мне были бы интересны другие СУБД, если MySQL не может этого сделать.
Чтобы уточнить, моя цель - просто избежать простоев, когда новая функция, для которой требуется дополнительный столбец таблицы, отправляется в производство. Любая схема базы данных будут меняется со временем, это просто факт жизни. Я не понимаю, почему мы должны признать, что эти изменения неизбежно приводят к простоям; это просто слабо.
IMHO, изменения схемы базы данных связаны с целыми новыми версиями - они не выпускаются спорадически, как другие изменения. Это неизбежно большое дело.
@AllainLalonde - более 0 раз делает этот вопрос законным, особенно если простой в вашей системе будет стоить жизни или больших денег. Во всяком случае, новые требования к программному обеспечению иногда появляются.
Ответы 19
Неа. Если вы используете таблицы MyISAM, насколько я понимаю, они выполняют только блокировки таблиц - нет никаких блокировок записей, они просто пытаются сохранить все сверхбыстро за счет простоты. (Другие таблицы MySQL работают иначе.) В любом случае вы можете скопировать таблицу в другую таблицу, изменить ее, а затем переключить их, обновляя для различий.
Это настолько масштабное изменение, что я сомневаюсь, что какая-либо СУБД его поддержит. Считается преимуществом в первую очередь иметь возможность делать это с данными в таблице.
InnoDB использует блокировки строк - dev.mysql.com/doc/refman/5.0/en/internal-locking.html
Да, MySQL - это заблуждение. Вот почему я особо остановился на «стандартных» таблицах.
Вы писали - стандартные таблицы MySQL только блокируют таблицы - что неверно.
Как вы интерпретируете это о таблицах MyISAM (т. Е. Стандарта MySQL) со страницы, которую вы процитировали? «MySQL использует блокировку на уровне таблиц для таблиц MyISAM и MEMORY, блокировку на уровне страниц для таблиц BDB и блокировку на уровне строк для таблиц InnoDB».
некоторые механизмы хранения используют блокировку на уровне строк, а некоторые - блокировку на уровне таблицы. Стандартного механизма хранения нет (возможно, вы имели в виду значение по умолчанию в phpMyAdmin ...)
Так вы новичок в MySQL? MyISAM - это то, что установлено, если вы его не измените. Это, безусловно, наиболее широко используемый. Какое определение вы бы связали со словом «Стандарт»?
Все упомянутые механизмы хранения установлены по умолчанию. Вы определяете механизм хранения с помощью запроса на создание таблицы. Я использую MySQL более 7 лет, чтобы ответить на ваш вопрос.
ОК. Я признаю, какова бы ни была ваша точка зрения - ваш опыт отличается и, возможно, более обоснован, чем мой. Что бы вы хотели, чтобы я изменил свой ответ?
Предложение о замках совершенно неверное, его следует отбросить
Я не думаю, что это совсем неточно; Я прояснил это. Возможно, мы сможем по крайней мере согласиться уважительно отличаться. Как вы читаете это MyISAM; «Двоичный переносной механизм хранения, который является механизмом хранения по умолчанию, используемым MySQL». dev.mysql.com/doc/refman/5.1/en/create-table.html
Говоря «стандарт», вы подразумевали нечто большее, чем механизм хранения по умолчанию, а скорее то, что он был «стандартным». Я использую InnoDB в качестве стандартного механизма хранения в своих приложениях, как и большинство производственных баз данных MySQL, требующих транзакций. Мой отрицательный голос был удален, так как вы его очистили.
@Eran, мне напомнили, насколько важно для архитекторов и разработчиков баз данных быть точными и недвусмысленными. Спасибо за вашу помощь.
Не совсем.
В конце концов, вы изменяете базовую структуру таблицы, и это часть информации, которая очень важна для базовой системы. Вы также (вероятно) перемещаете большую часть данных на диске.
Если вы планируете делать это часто, вам лучше просто заполнить таблицу «фиктивными» столбцами, которые будут доступны для использования в будущем.
Заполнение таблицы фиктивными столбцами кажется действительно плохой идеей.
Единственный другой вариант - сделать вручную то, что и так делают многие системы РСУБД ...
- Создать новую таблицу
Затем вы можете копировать содержимое старой таблицы по фрагменту за раз. Всегда будьте осторожны с любыми INSERT / UPDATE / DELETE в исходной таблице. (Можно управлять с помощью триггера. Хотя это приведет к замедлению, это не блокировка ...)
По завершении измените имя исходной таблицы, а затем измените имя новой таблицы. Желательно в сделке.
После завершения перекомпилируйте все хранимые процедуры и т. д., Которые используют эту таблицу. Планы выполнения, скорее всего, больше не будут действительны.
Обновлено:
Были сделаны некоторые комментарии по поводу того, что это ограничение немного скучно. Поэтому я подумал, что взгляну на это с новой точки зрения, чтобы показать, почему это так ...
- Добавление нового поля похоже на изменение одного поля в каждой строке.
- Блокировки полей были бы намного сложнее, чем блокировки строк, не говоря уже о блокировках таблиц.
- Фактически вы меняете физическую структуру диска, каждая запись перемещается.
- Это действительно похоже на ОБНОВЛЕНИЕ для всей таблицы, но с большим влиянием ...
И перед заменой имейте подробный план тестирования. Если не получится, начните заново.
Блокировка столбцов не такая уж большая проблема для столбцовой базы данных :-). Кроме того, если вы добавляете столбец, в зависимости от вашего физического макета это может вообще не потребовать каких-либо изменений в данных таблицы - см. Ответ Oracle ниже.
Хорошей идеей было управление синхронизацией с помощью триггеров. Я использую MySQL так долго, что все время забываю, что у них есть триггеры. Я использовал эту технику, и теперь у меня есть работающий скрипт горячего изменения. С индикатором выполнения. И это работает с MyISAM. Жизнь хороша.
+1 Это буквально то, что менеджер SQL Enterprise делает за кулисами, когда вы вносите определенные виды изменений в таблицы в пользовательском интерфейсе. В SQL 2008 они фактически добавили предупреждение, чтобы пользователь ЗНАЕТ, что он выполняет это радикальное действие.
Вы ничего не упомянули о внешних ключах, ссылающихся на изменяемые таблицы. Разве это не проблема?
@MohammadRafayAleem - И поля AUTOINCREMENT, и просмотры, и триггеры, и т.д., и т.д. Но даже в этом случае подход все еще работает.
Если вы можете сделать это вручную, нет причин, по которым сервер не может сделать это прозрачно. Жаль, что разработчики MySQL не видят, насколько это требование очевидно.
Фиктивные столбцы - хорошая идея, если вы можете предсказать их тип (и сделать их допускающими значение NULL). Проверьте, как ваш механизм хранения обрабатывает нули.
MyISAM заблокирует все, если вы хотя бы мимоходом упомянете имя стола по телефону в аэропорту. Он просто делает это ...
При этом замки на самом деле не такая уж большая проблема; до тех пор, пока вы не пытаетесь добавить значение по умолчанию для нового столбца в каждую строку, но пусть он будет иметь значение null, а ваш механизм хранения достаточно умен, чтобы не писать его, вы должны быть в порядке с блокировкой, которая только достаточно долго, чтобы обновить метаданные. Если вы все же попытаетесь написать новое значение, то вы - тост.
Я попытался добавить столбец NULL в таблицу InnoDB, и ему пришлось перестроить всю таблицу; не простая операция «обновить метаданные».
Я думаю, что идея заключалась в том, чтобы включить дополнительные столбцы, допускающие значение NULL, в базу данных при ее разработке, чтобы, если требуется новая функция, можно было «добавить» новый столбец, просто начав ее использовать. У него не будет красивого имени, но если тип данных был правильно выбран / предсказан, он должен работать.
Поскольку вы спрашивали о других базах данных, вот некоторая информация об Oracle.
Добавление столбца NULL в таблицу Oracle - очень быстрая операция, поскольку она обновляет только словарь данных. Это удерживает исключительную блокировку таблицы в течение очень короткого периода времени. Однако это сделает недействительными все депедантные хранимые процедуры, представления, триггеры и т. д. Они будут автоматически перекомпилированы.
Оттуда, если необходимо, вы можете создать индекс, используя предложение ONLINE. Опять же, только очень короткие блокировки словаря данных. Он будет читать всю таблицу в поисках объектов для индексации, но при этом никого не блокирует.
Если вам нужно добавить внешний ключ, вы можете сделать это и заставить Oracle доверять вам в правильности данных. В противном случае ему необходимо прочитать всю таблицу и проверить все значения, которые могут быть медленными (сначала создайте свой индекс).
Если вам нужно поместить значение по умолчанию или вычисленное значение в каждую строку нового столбца, вам нужно будет запустить массовое обновление или, возможно, небольшую служебную программу, которая заполняет новые данные. Это может быть медленным, особенно если ряды становятся намного больше и больше не помещаются в свои блоки. Во время этого процесса можно управлять блокировкой. Поскольку старая версия вашего приложения, которая все еще работает, не знает об этом столбце, вам может понадобиться скрытый триггер или указать значение по умолчанию.
Оттуда вы можете переключить свои серверы приложений на новую версию кода, и она продолжит работу. Бросьте хитрый спусковой крючок.
В качестве альтернативы вы можете использовать DBMS_REDEFINITION, который представляет собой черный ящик, предназначенный для подобных вещей.
Все это так сложно тестировать и т. д., Что у нас просто отключается рано утром в воскресенье, когда мы выпускаем основную версию.
Используя плагин Innodb, операторы ALTER TABLE, которые только добавляют или удаляют вторичные индексы, могут быть выполнены «быстро», то есть без перестройки таблицы.
Однако, вообще говоря, в MySQL любой ALTER TABLE включает в себя перестройку всей таблицы, что может занять очень много времени (т.е. если таблица содержит полезный объем данных).
Вам действительно нужно спроектировать свое приложение так, чтобы операторы ALTER TABLE не выполнялись регулярно; вы, конечно, не хотите, чтобы при нормальном запуске приложения выполнялись какие-либо операции ALTER TABLE, если только вы не готовы ждать или не изменяете крошечные таблицы.
В общем, ответ будет «Нет». Вы меняете структуру таблицы, что потенциально потребует большого количества обновлений », и я определенно согласен с этим. Если вы ожидаете, что будете делать это часто, я предложу альтернативу« фиктивным »столбцам - используйте вместо них VIEW. таблиц для данных SELECT. IIRC, изменение определения представления является относительно легким делом, и косвенное обращение к представлению выполняется при компиляции плана запроса. Расходы состоят в том, что вам придется добавить столбец в новую таблицу и сделать просмотреть JOIN в колонке.
Конечно, это работает только в том случае, если вы можете использовать внешние ключи для выполнения каскадных удалений и прочего. Другой бонус заключается в том, что вы можете создать новую таблицу, содержащую комбинацию данных, и указать на нее точку зрения, не мешая использованию клиента.
Просто мысль.
Я бы порекомендовал один из двух подходов:
Создавайте таблицы базы данных с учетом возможных изменений. Например, я работал с системами управления контентом, которые регулярно меняют поля данных в контенте. Вместо того, чтобы строить физическую структуру базы данных в соответствии с начальными требованиями к полям CMS, гораздо лучше создать гибкую структуру. В этом случае используется текстовое поле большого двоичного объекта (например, varchar (max)) для хранения гибких данных XML. Это снижает частоту структурных изменений. Структурные изменения могут быть дорогостоящими, поэтому здесь тоже есть выгода.
Имейте время на обслуживание системы. Либо система отключается во время изменений (ежемесячно и т. д.), И изменения планируются на наименее загруженное время дня (например, 3-5 утра). Изменения вносятся до развертывания производственной среды, поэтому у вас будет хорошая фиксированная оценка времени простоя за фиксированное окно.
2а. Иметь резервные серверы, чтобы во время простоя системы не выходил из строя весь сайт. Это позволит вам «выкатывать» обновления в шахматном порядке, не останавливая работу всего сайта.
Варианты 2 и 2а могут оказаться невыполнимыми; они, как правило, предназначены только для крупных сайтов / операций. Однако это допустимые варианты, и я лично использовал все варианты, представленные здесь.
В варианте 1 мы можем добавить универсальное поле под названием «другое» типа JSON и использовать его для любых полей, которые мы хотим добавить.
Если вы не можете позволить себе простои базы данных при обновлении приложений, вам следует подумать о поддержке двухузлового кластера для обеспечения высокой доступности. С помощью простой настройки репликации вы можете почти полностью в оперативном режиме вносить структурные изменения, подобные предложенному вами:
- подождите, пока все изменения будут реплицированы на пассивном ведомом устройстве
- изменить пассивное ведомое устройство на активное ведущее
- сделать структурные изменения в старом мастере
- реплицировать изменения с нового мастера на старый
- выполните главную замену снова и развертывание нового приложения одновременно
Это не всегда просто, но работает, как правило, без простоев! Второй узел не обязательно должен быть только пассивным, его можно использовать для тестирования, сбора статистики или как резервный узел. Если у вас нет инфраструктуры, репликацию можно настроить на одной машине (с двумя экземплярами MySQL).
Старый мастер вне кластера или внутри кластера?
См. Онлайн-инструмент изменения схемы Facebook.
http://www.facebook.com/notes/mysql-at-facebook/online-schema-change-for-mysql/430801045932
Не для слабонервных; но он сделает свою работу.
Я рекомендую Postgres, если это возможно. В postgres практически нет простоев при выполнении следующих процедур:
- ALTER TABLE ADD COLUMN (если столбец может быть NULL)
- ИЗМЕНИТЬ КОЛОНКУ КАБЕЛЯ
- CREATE INDEX (необходимо использовать CREATE INDEX CONCURRENTLY)
- ПАДЕНИЕ ИНДЕКСА
Другая замечательная особенность заключается в том, что большинство операторов DDL являются транзакционными, поэтому вы можете выполнить всю миграцию в рамках транзакции SQL, и если что-то пойдет не так, все будет откатано.
Я написал это немного назад, возможно, он может пролить больше информации на другие достоинства.
Постгрес по-прежнему создает исключительную блокировку для изменения, не позволяя другим читать из этой таблицы.
Я не согласен с принципом «по существу без простоев». Как сказано в clofresh, ALTER TABLE захватывает исключительную блокировку таблицы, блокируя все одновременные операции чтения и записи. По моему опыту, для активных таблиц в большинстве случаев вы даже не получите блокировку (ALTER TABLE будет голодать). А с транзакциями вы можете легко попасть в тупик, если не будете предельно осторожны. Из-за этого я теперь всегда устанавливаю время простоя при изменении существующих таблиц в Postgres.
более подробное объяснение: dba.stackexchange.com/questions/27153/… в нем упоминаются последствия эксклюзивной блокировки и некоторые способы ее обхода
Да, изменение таблицы в postgres захватывает эксклюзивную блокировку, но поскольку сама операция завершается за миллисекунды, в большинстве случаев это практически не имеет значения. Я лично добавил столбцы в таблицы с сотнями миллионов строк в середине рабочего дня, и в результате время простоя было нулевым.
Ты прав. Я узнал, что мои проблемы с голоданием были связаны с одновременным запуском pg_dump как части нашего ежечасного резервного копирования. Не следует делать pg_dump в основной / основной БД.
@NoahYetter просто любопытно, выполняли ли вы команды DROP COLUMN, и если да, то они примерно такие же быстрые, как и добавление, или им нужно "трогать" каждую строку?
@cobbzilla Да, DROP COLUMN работает так же быстро. Под капотом он в основном отмечает столбец как скрытый. Значения, которые существовали в этом столбце до удаления, все еще находятся в файлах данных (и видимы для других транзакций) и останутся таковыми до тех пор, пока вы не выполните VACUUM FULL.
Если кто-то все еще читает это или приезжает сюда, это большое преимущество использования системы баз данных NoSQL, такой как mongodb. У меня была та же проблема, связанная с изменением таблицы для добавления столбцов для дополнительных функций или индексов в большой таблице с миллионами строк и большим количеством записей. Это привело бы к блокировке на очень долгое время, поэтому выполнение этой операции в базе данных LIVE расстроило бы наших пользователей. На маленьких столиках это может сойти с рук.
Я ненавижу тот факт, что мы должны «проектировать наши таблицы, чтобы не изменять их». Я просто не думаю, что это работает в современном мире веб-сайтов. Вы не можете предсказать, как люди будут использовать ваше программное обеспечение, поэтому вы быстро меняете вещи на основе отзывов пользователей. С mongodb вы можете добавлять «столбцы» по желанию без простоев. На самом деле вы их даже не добавляете, вы просто вставляете данные с новыми столбцами, и он делает это автоматически.
Стоит проверить: www.mongodb.com
MySQL до сих пор используется во многих системах, поэтому вопрос действительно заключается в том, как добиться изменения схемы в СУБД SQL, хотя я тоже яростный сторонник NoSQL.
TokuDB может добавлять / удалять столбцы и добавлять индексы «горячо», таблица полностью доступна на протяжении всего процесса. Он доступен на сайте www.tokutek.com.
Временное решение...
Другим решением может быть добавление другой таблицы с первичным ключом исходной таблицы вместе с вашим новым столбцом.
Заполните свой первичный ключ в новой таблице и заполните значения для нового столбца в новой таблице и измените свой запрос, чтобы присоединиться к этой таблице для операций выбора, и вам также необходимо вставить, обновить отдельно для значения этого столбца.
Когда у вас будет время простоя, вы можете изменить исходную таблицу, изменить запросы DML и удалить новую таблицу, созданную ранее.
В противном случае вы можете использовать метод кластеризации, репликацию, инструмент pt-online-schema от percona.
Percona создает инструмент под названием pt-online-schema-change, который позволяет это делать.
По сути, он делает копию таблицы и изменяет новую таблицу. Чтобы синхронизировать новую таблицу с исходной, она использует триггеры для обновления. Это позволяет получить доступ к исходной таблице, пока новая таблица готовится в фоновом режиме.
Это похоже на метод, предложенный Демсом выше, но выполняется автоматически.
Некоторые из их инструментов требуют обучения, а именно подключения к базе данных, но как только вы это сделаете, они станут отличными инструментами.
Бывший:
pt-online-schema-change --alter "ADD COLUMN c1 INT" D=db,t=numbers_are_friends
Похоже ссылка не работает. Я обнаружил, что ссылка это работает.
Разница между Postgres и MySQL в этом отношении заключается в том, что в Postgres он не воссоздает таблицу заново, а изменяет словарь данных, аналогичный Oracle. Следовательно, операция выполняется быстро, хотя по-прежнему требуется выделить исключительную блокировку таблицы DDL на очень короткое время, как указано выше другими.
В MySQL операция будет копировать данные в новую таблицу при блокировании транзакций, что было основной проблемой для администраторов баз данных MySQL до версии 5.6.
Хорошая новость заключается в том, что с момента выпуска MySQL 5.6 ограничение было в основном поднял, и теперь вы можете наслаждаться истинной мощью базы данных MYSQL.
Похоже, вы пытались ссылаться на ссылку об изменении в MySql 5.6, но это не сработало. Пожалуйста, попробуйте еще раз.
Как упомянул Шон Дауни, pt-online-schema-change - один из лучших инструментов для выполнения того, что вы описали в вопросе здесь. Недавно я внес много изменений в схему в живой БД, и все прошло хорошо. Вы можете прочитать об этом в моем блоге здесь: http://mrafayaleem.com/2016/02/08/live-mysql-schema-changes-with-percona/.
Этот вопрос от 2009 года. Теперь MySQL предлагает решение:
Онлайн DDL (язык определения данных)
A feature that improves the performance, concurrency, and availability of InnoDB tables during DDL (primarily ALTER TABLE) operations. See Section 14.11, “InnoDB and Online DDL” for details.
The details vary according to the type of operation. In some cases, the table can be modified concurrently while the ALTER TABLE is in progress. The operation might be able to be performed without doing a table copy, or using a specially optimized type of table copy. Space usage is controlled by the innodb_online_alter_log_max_size configuration option.
Он позволяет вам регулировать баланс между производительностью и параллелизмом во время операции DDL, выбирая, следует ли полностью блокировать доступ к таблице (предложение LOCK = EXCLUSIVE), разрешить запросы, но не DML (предложение LOCK = SHARED), или разрешить полный запрос и DML. доступ к таблице (предложение LOCK = NONE). Когда вы опускаете предложение LOCK или указываете LOCK = DEFAULT, MySQL разрешает максимально возможный параллелизм в зависимости от типа операции.
Выполнение изменений на месте, где это возможно, вместо создания новой копии таблицы, позволяет избежать временного увеличения использования дискового пространства и накладных расходов ввода-вывода, связанных с копированием таблицы и восстановлением вторичных индексов.
см. Справочное руководство MySQL 5.6 -> InnoDB и онлайн-DDL для получения дополнительной информации.
Кажется, что онлайн-DDL также доступен в MariaDB
Alternatively you can use ALTER ONLINE TABLE to ensure that your ALTER TABLE does not block concurrent operations (takes no locks). It is equivalent to LOCK=NONE.
Жаль, что нет другого способа, кроме голосов, поднять этот вопрос наверх, поскольку он в основном сводит на нет все остальные ответы просто потому, что они больше не ссылаются на текущую версию MySQL.
Обязательно стоит попробовать pt-online-schema-change. Я использовал этот инструмент для миграции на AWS RDS с несколькими ведомыми устройствами, и он мне очень помог. Я написал подробное сообщение в блоге о том, как сделать то, что может быть полезно для вас.
Блог: http://mrafayaleem.com/2016/02/08/live-mysql-schema-changes-with-percona/
Придется задаться вопросом, сколько раз вы будете менять таблицу?