MySQL. Быстрая загрузка миллионов строк в таблицу

Не так давно передо мною стояла задача — загрузить 50 миллионов строк в базу на мускуле. Скажу я вам, то ещё удовольствие. И так пробовал, и эдак — примерно на двадцати миллионах скорость загрузки начинала резко падать. Эти 20 миллионов загружались примерно за час. Следующий же миллион входил в базу только к концу суток. В итоге detect нашёл выход:

      создаём временную таблицу нужной структуры без индексов и ключей
      загружаем данные командой

      —delete — если перед загрузкой надо очистить таблицу
      Разделитель данных по умолчанию я не помню. В принципе, про него описано в доке по утилите mysqlimport.
      создаём нужные ключи и индексы
      удаляем старую таблицу
      переименовываем временную таблицу

Дело, как вы уже поняли, было в обновлении ключей при загрузке данных.

3 комментария

  1. Да индексы они это могут, в «больших» СУБД при загрузке больших объемов их обычно стараются отключить перед заливкой, и включить уже после.

  2. Эпопея получила продолжение. Сейчас передо мной стоит задача загрузить в таблицу порядка 100 миллионов записей. Загружаются без проблем. Веселье начинается при запуске добавления первичного ключа. Сервер и мускуль почти что умирают. Завершения добавления ключа я не дождался — от греха подальше прервал выполнение запроса, дабы разгрузить сервер.

  3. Пока что удалось победить мускул. Подозреваю, что скоро встанет проблема хранить в нём миллиарды записей в одной таблице. 🙂
    Вот решение:

    1. флашим таблицы:
    FLUSH TABLES;
    2. отключаем ключи во временной таблице (её структура полностью идентична структуре таблицы, куда надо засунуть данные — со всеми ключами):
    myisamchk —keys-used=0 -rq /path/to/db/tbl_new
    3. тащим из старой таблицы данные в новую:
    insert into tbl_new select * from tbl_real;
    4. загружаем данные из файла:
    LOAD DATA INFILE «/tmp/data.txt» REPLACE INTO TABLE tbl_new FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY «n»;
    5. восстанавливаем ключи:
    myisamchk —sort_buffer_size=50M -rqq /path/to/db/tbl_new
    (без повышения размера буфера myisamchk быстро выругался на нехватку памяти — по дефолту у него буфер для сортировки всего 2Мб)
    6. флашим таблицы:
    FLUSH TABLES;
    7. переименовываем старую таблицу:
    alter table tbl_real rename to tbl_old;
    8. переименовываем новую таблицу:
    alter table tbl_new rename to tbl_real;

    На копирование 17 миллионов из реальной таблицы потребовалось менее получаса, на загрузку 80 миллионов из файла — около получаса, на восстановление ключей — ещё около получаса. Вполне приемлемые затраты времени. Особенно по сравнению с возможными.

Leave a Reply

Ваш e-mail не будет опубликован. Обязательные поля помечены *