Как добавить индексы не очень маленькой InnoDB таблице?
В общем столкнулся с проблемой.
Есть сервер mysql, на нем есть таблица InnoDB, размер таблицы 1,4 гигабайта, 19 миллионов записей и каждую секунду добавляется еще сотня записей. В ней идет подсчет статистики.
Проблема — в таблице нет необходимых индексов, поэтому выборка из нее идет очень долго.
Вторая проблема — хотелось бы добавить в нее также составной уникальный ключ, чтобы записи при добавлении не втупую падали в конец таблицы, а группировались через INSERT… ON DUPLICATE KEY UPDATE count = count + 1
Если просто попробовать выставить индексы через ALTER TABLE то я боюсь что у меня сервер повиснет в процессе. Также не хотелось бы потерять данные, которые постоянно добавляются.
Пока придумал такой вариант — создать новую таблицу необходимой структуры, с именем stats2, потом переименовать stats в stats3 а stats2 в stats, чтобы все новые данные падали в новую таблицу, а старые остались в старой. Далее скриптом постепенно перенести все данные из старой таблицы в новую.
При этом, очевидно, отображение статистики какое то время будет не работать, пока все старые данные не перенесутся, зато не потеряются текущие данные. Есть ли еще какие-то варианты?
Как я понимаю, если делать на другом сервере (т. е. скопировать таблицу на второй сервер, добавить индексы каким-либо образом, скопировать обратно) то потеряется информация, собранная за время выполнения этой операции
На эту тему (как обновлять структуру больших таблиц MySQL на живом сервере) в прошлом году была статья на Хабре — вроде бы перевод кого-то из фейсбука — можете поискать. Там какие-то хитрые манипуляции с триггерами, а также копированием и переименованием таблиц. Но в общем предлагаемый метод похож на описанный вами.
> Если просто попробовать выставить индексы через ALTER TABLE то я боюсь что у меня сервер повиснет в процессе.
Так и будет. Это очень медленная операция, на огромных таблицах она займет часы или даже дни.
Для начала попробуйте на таблице похожих объёмов на локальной тачке добавить эти индексы.
И посмотреть будет ли что то как вы боитесь «то я боюсь что у меня сервер повиснет в процессе. „
Вполне возможно будет задержка лишь на 10 мин. Вы ж всётки индексы добавляете а не структура меняете.
О результатах напишите тут нам всем интересно.
Возможно чтоб индексы применились в один проход вначале нужно выключить для таблицы ALTER TABLE… DISABLE KEYS добавить в определение таблицы инжексы и заново перестроить индексы ALTER TABLE… ENABLE KEYS. Но это чисто догадка что так может сработать, на практике не проверял.
Если будите такое эксперементировать отпишитесь плиз о результатах.
Есть решение от фейсбука для таких вещей. Если у вас на этой таблице нет внешних ключей, то оно вам должно помочь Online schema change
В двух словах — небольшой скриптик, который создает новую таблицу с нужными вам индексами и новыми полями, и постепенно переносит данные из старой таблицы в эту новую, добавив при этом на новую таблицу нужные триггеры, для обращения к старым данным.
Средний
