ALTER очень больших таблиц в MySQL

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

Дано: таблица размером в несколько десятков гигабайт данных. Задача — изменить структуру таблицы.

Сразу забегу в перед, работать метод будет только на транзакционных таблицах. Если у вас MyISAM-таблица на десятки гигабайт, то тут как в том анекдоте — «разбирайтесь сами со своими проблемами». Пример будет приведен для InnoDB таблицы.

Предположим что структура нашей таблицы такая:

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(40) NOT NULL DEFAULT '',
  `password_hash` char(32) NOT NULL DEFAULT '',
  `registration_date` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Мы хотим добавить в эту таблицу поле last_login.

Какие у нас есть варианты.

В лоб

ALTER TABLE `users` ADD COLUMN `last_login` int(11) NOT NULL DEFAULT 0;

Вариант прекрасно работет на мелких проектах где размер таблиц редко превышает 50 000 записей. Нам вариант не подходит т.к. ALTER будет выполнятся слишком долго и все это время таблица будет заблокирована как на запись так и на чтение. Соответственно сервис нужно будет останавливать на это время.

Включаем мозг

Можно вообще не трогать таблицу раз уж на то пошло, а сделать отдельную `users_lastvisits`:

CREATE TABLE `users_lastvisits` (
  `user_id` int(11) NOT NULL,
  `last_login` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

И все таки — нужно добавить поле

Можно поднять master-slave репликацию, сделать ALTER на slave-сервере а потом поменять их местами. Если честно я таким никогда не занимался, может это и проще следующего способа, но не всегда есть возможность поднять репликацию.

Мой способ заключается в следующем

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

Итак, подготавливаем 2 таблицы — первая с нужной структурой, вторая для логирования изменений.

CREATE TABLE `_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(40) NOT NULL DEFAULT '',
  `password_hash` char(32) NOT NULL DEFAULT '',
  `registration_date` int(11) NOT NULL DEFAULT '0',
  `lastvisit` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `users_updated_rows` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `row_id` int(11) NOT NULL DEFAULT '0',
  `action` enum('updated','deleted') NOT NULL DEFAULT 'updated',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Ставим триггеры:

DELIMITER ;;

CREATE TRIGGER users_after_delete AFTER DELETE ON users FOR EACH ROW
BEGIN
  INSERT INTO users_updated_rows VALUES (0, OLD.id, 'deleted');
END;;

CREATE TRIGGER users_after_insert AFTER INSERT ON users FOR EACH ROW
BEGIN
  INSERT INTO users_updated_rows VALUES (0, NEW.id, 'updated');
END;;

CREATE TRIGGER users_after_update AFTER UPDATE ON users FOR EACH ROW
BEGIN
  IF (OLD.id != NEW.id) THEN
    INSERT INTO users_updated_rows VALUES (0, OLD.id, 'deleted');
  END IF;
  INSERT INTO users_updated_rows VALUES (0, NEW.id, 'updated');
END;;

DELIMITER ;

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

mysql> LOCK TABLES users WRITE;
Query OK, 0 rows affected (0.00 sec)

mysql> -- триггеры уже должны быть установлены
mysql> TRUNCATE users_updated_rows;
Query OK, 0 rows affected (0.17 sec)

mysql> -- в другой консоли пускаем переливку
mysql> INSERT INTO _users SELECT id, email, password_hash, registration_date, 0 FROM users;

mysql> -- снова в первой консоли
mysql> UNLOCK TABLES;

Все, теперь пока таблица переливается у нас есть время подумать как будем вливать изменившиеся с момента начала переливки данные. Тут вообщем то ничего сложного — скрипт приводить я не буду, нужно просто брать по одной записи из таблицы users_updated_rows в том порядке, в котором они добавлялись (сортировать по первичному ключу) и обновлять или удалять её в таблице _users;

Итак, переливка таблицы уже закончилась, нужно вливать остальные данные. Запускаем скрипт. Скрипт должен работать постоянно и обновлять все записи которые добавляются в лог, когда он перельет все данные нужно переименовать таблицы:

mysql> TRUNCATE users_updated_rows;
Query OK, 0 rows affected (0.16 sec)
mysql> RENAME TABLE users TO __users, _users TO users;
Query OK, 0 rows affected (0.11 sec)

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

Если все сделать правильно данные не будут потеряны и перерывов в работе сервиса практически не будет. Что нам и требовалось. Таким же способом можно перелить данные на другой сервер, поменяется только способ переливки. Вместо

mysql> INSERT INTO _users SELECT id, email, password_hash, registration_date, 0 FROM users;

нужно перелить через mysqldump:

$ mysqldump -h host1 db users --single-transaction -ecQ | pv | mysql -h host2

Таким способом мне удалось перелить без остановки работы сервисов на другой сервер таблицу размером в 60Gb и 400 млн строк где то за 12 часов.

Кстати, велосипед уже изобретен Facebook и называется Online Schema Change for MySQL.