Большие таблицы и ENUM

    Продолжение статьи Большие таблицы и уникальные ключи.

    У меня снова возникла необходимость изменить структуру достаточно большой таблицы в MySQL. Изменения касались поля типа ENUM.

    Итак
    Дано. Таблица MyISAM
    В таблице есть поле типа ENUM.
    Размер — 145 миллионов записей.
    Объём на диске — 12 ГБ.
    Также в таблице есть уникальный ключ на строковое поле VARCHAR(150).
    Необходимо изменить список констант поля ENUM.

    То есть имеем поле `my_field` ENUM('A', 'B') NOT NULL. Надо поменять на ENUM('C', 'D', 'E', 'F') NOT NULL.

    Делать ALTER TABLE CHANGE COLUMN смысла нет, потому что на таких объёмах данных и с уникальным индексом это займёт вечность. Причина? MySQL при изменении структуры таблицы в большинстве случаев создаёт временную таблицу желаемой структуры, куда построчно копирует все данные. А создание уникального ключа построчно в таких объёмах занимает непомерно много времени.

    Однако в памяти у меня засело, что я где-то подобное уже встречал. Нужный мне рецепт я в итоге нашёл в книге «High Performance MySQL», где кстати отыскал и хак из моей предыдущей статьи.

    Рецепт прост — подмена файла, где хранится определение структуры таблицы .FRM.
    1. Создаём новую таблицу со структурой как у исходной: CREATE TABLE test_struct LIKE test.
    2. Изменяем в новой таблице поле ENUM: ALTER TABLE `test_struct` CHANGE `my_field` ENUM('C', 'D', 'E', 'F') NOT NULL.
    3. Блокируем таблицы: FLUSH TABLES WITH READ LOCK
    4. в директории с нужной базой данных копируем файл test_struct.frm поверх test.frm.
    5. Снимаем блокировку: UNLOCK TABLES

    Всё готово. Можете проверять.

    О чём надо помнить? В MySQL данные в полях типа ENUM представляют собой целые числа. Отсчёт начинается с единицы.
    Таким образом было: константа A = 1, B = 2.
    Стало: C = 1, D = 2, E = 3, F = 4. — В итоге все записи со значением «A» после наших изменений стали отображаться как «C», а все «B» — как «D», поскольку их внутреннее представление осталось прежним — 1 и 2 соответственно.

    Где ещё применим этот рецепт?
    1. Убрать у поля атрибут AUTO_INCREMENT.
    2. Добавить, изменить или удалить константы в полях типа SET.
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 43

      0
      а если на время изменения поля дропнуть индекс? потом его восстановить, или бред?
        0
        почитайте статью, на которую даю ссылку вначале.
        +2
        145 миллионов записей? БД граждан России? :)
          +3
          а в уникальном ключе код днк :D
            0
            > уникальный ключ на строковое поле VARCHAR(150)

            не уверен что код ДНК туда поместится… хотя если сжать… :)
              +5
              К сожалению, ДНК некоторых граждан -поместятся :)
            +1
            Полторы сотни — это не предел. У меня в ежедневной работе несколько таблиц от пары сотен миллионов до миллиарда записей.
            Говорят у физиков-ядерщиков после экспериментов данные некуда складывать. Метеорологи уже без суперкомпьютеров не могут. А ведь раньше всю их погоду предсказывал сосед-радикулитик :)
            0
            а я бы как лох в цикле по 10000-100000 записей бы переносил. спасибо!
              +2
              По-моему, у автора топиков подозрительная любовь к уникальным индексам по VARCHAR'а длиной в полтораста знаков. Не спроста это, попомните мое слово! :-)

              2lexxscorp: Все ждут третьего топика, где вы все-таки триумфально объявите, зачем вам такие индексы! :-)
                +2
                Да бог с ними, с индексами (хотя мне тоже интересно)… А вот то, что человек в течение одной недели второй раз меняет структуру таблицы с 145 МегаЗаписями, немного странно :)
                  +1
                  у меня тоже такой вопрос вознк, зачем такой индекс и почему бы не сделать CHAR и сделать ROW_FORMAT FIXED

                  Хотя я предполагаю что в этом поле может храниться имя файла.
                    0
                    Думаю, что если сделать строки таблицы фиксированными по длине, то всё равно останутся тормоза на построчном создании уникального индекса. Даже как-то хочется проверить :)
                      0
                      зато сама таблица станет быстрее и безопасней.
                      создание индекса можно отложить используя DELAY_KEY_WRITE
                        0
                        В моём случае индекс не влазит в оперативную память.
                    0
                    В обеих статья одна и таже таблица. Просто с прошлого раза она ужалась с 15 до 12 гигов.
                      0
                      Тогда возникает уже заданный ваше вопрос — зачем эту несчастную таблицу так часто ковырять? :-)
                      Нет, серьезно, скиньте задачу. Я думаю, Хабр с удовольствием поделится советами по организации базы… Просто я честно не могу придумать задачу, требующую такого индекса, которую нельзя оптимизировать и реализовать иначе…
                    0
                    А для InnoDB такое сработает?
                      –1
                      а вы подумайте, если все таблички InnoDB хранятся в одном файле, то как скопировать одну табличку в другую?
                        0
                        Никак. Не знал что в InniDB таблицы хранятся в одном файле. Спасибо.
                          0
                          А они и не хранятся, если настроить file_per_table
                            0
                            по умолчанию хранятся.
                        0
                        Сработает. Файл описания структуры таблицы все равно хранится отдельно от данных.
                        +5
                        Перетаскиваете потихоньку сюда свежак из блога Пети Зайцева? :-)

                        www.mysqlperformanceblog.com/2007/10/29/hacking-to-make-alter-table-online-for-certain-changes/
                          0
                          спасибо за ссылочку
                            0
                            Какой же это свежак? 2007 год. А взял из книжки Пети Зайцева, когда возникла необходимость.
                              +3
                              Свежак это такое ироническое выражение для тех, кто знает
                              0
                              я вот думаю, такой трюк — это же риск уронить всю таблицу, не страшно?
                                0
                                Перед этим неплохо засейвиться
                                  0
                                  на боевом? а места хватит?
                                    0
                                    Можно не всё, можно только самое необходимое!
                              0
                              А если сделать ALTER TABLE ADD COLUMN — тоже будет временная таблица?
                              +4
                              На мотив Pinky and the Brain:

                              Pinky: «Gee, Brain, what do you want to do tonight?»
                              The Brain: «The same thing we do every night, Pinky — try to take over the world! change the table structure!»

                              :-)
                                –1
                                на практике человек застремается без бэкапа заменять какие-то файлы в /var/lib/mysql — там же пипец ценные данные! поэтому к затрачиваемому времени добавьте время на изготовление дампа базы, так что выигрыш невелик.
                                  0
                                  Бекап можно слить сразу в бинарном виде и сортировать ничего не нужно. Быстрее даже с учетом бекапа.
                                  0
                                  зачем вы используете уникальный ключ на 160 символов?
                                    0
                                    А что вы предложите взамен? Задача — гарантировать уникальность строкового поля.
                                    1. перейти на CHAR с меньшей длиной не могу, придётся пожертвовать частью данных; а если задать CHAR (160), то сильно вырастет объём данных.
                                    2. можно конечно использовать 16-байтовый хеш типа MD5, который хранить в 2-х BIGINT'ах.
                                    3. можно разбить таблицу горизонтально на несколько других.

                                    Какие есть ещё идеи?
                                      0
                                      сделать суррогатный автоинкрементируемый ключ, а на это поле сделать просто unique?
                                        0
                                        ну md5 и хранить, тока одним полем. это уже серьезно повлияет на производительность. ключ с varchar вообще убрать.

                                        Почему мы используете myisam?
                                      +1
                                      145 миллионов записей на таблице MyISAM?
                                      Похоже, у вас тяжёлое наследие.
                                        0
                                        Вполне нормальная таблица. На выборку и добавление записей работает отлично.
                                        0
                                        второй пост уже всех интригуете своей странной таблицей
                                        может уже пора шардить, или InnoDb+partitioning
                                          0
                                          шардить горизонтально придётся :)

                                        Only users with full accounts can post comments. Log in, please.