Блог

Главная - Вопросы по программированию - Оптимизируйте MySQL для более быстрого добавления столбца в таблицу alter

Оптимизируйте MySQL для более быстрого добавления столбца в таблицу alter

#mysql #performance #alter

#mysql #Производительность #изменить

Вопрос:

У меня есть таблица, которая содержит 170 002 225 строк примерно с 35 столбцами и двумя индексами. Я хочу добавить столбец. Команда alter table заняла около 10 часов. В течение этого времени процессор не казался загруженным, и не было чрезмерных ожиданий ввода-вывода. Это 4-полосная высокопроизводительная система с большим объемом памяти.

Это лучшее, что я могу сделать? Есть ли что-нибудь, на что я могу обратить внимание, чтобы оптимизировать добавление столбца при настройке БД?

Комментарии:

1. Причиной замедления была система жесткого диска, все, что вы можете сделать, это либо получить более быструю систему жесткого диска (FusionIO, если вы можете себе это позволить), либо вам просто придется подождать. В качестве альтернативы, не добавляйте столбцы 🙂

2. Какую версию MySQL вы используете?

3. @Michael он сказал «и не было чрезмерных ожиданий ввода-вывода».

4. @FractalizeR — поскольку не было сказано, КАК была получена информация о вводе-выводе, я склонен полагать, что это подсистема памяти / жесткого диска (или raid-контроллер, если таковой имеется), которая поддерживает изменение таблицы. По моему опыту (а я работаю с большими наборами данных), когда дело доходит до подобных проблем с производительностью, почти всегда виновата система хранения.

5. Мы используем таблицы innodb. Майкл — На что лучше всего обратить внимание, чтобы определить, является ли проблема с HDD?

Ответ №1:

Я сталкивался с очень похожей ситуацией в прошлом, и я улучшаю производительность операции таким образом :

  1. Создайте новую таблицу (используя структуру текущей таблицы) с включенными новыми столбцами.
  2. выполните INSERT INTO new_table (column1,..columnN) SELECT (column1,..columnN) FROM current_table;
  3. переименуйте текущую таблицу
  4. переименуйте новую таблицу, используя имя текущей таблицы.

Комментарии:

1. Некоторые примечания … 1. CREATE TABLE new_table LIKE table; 2. INSERT INTO new_table SELECT * FROM table; 3 и 4. Вы можете получить псевдоатомное переименование, если вы объедините шаги 3 и 4 в это: RENAME TABLE table = old_table, table = new_table;

2. Я также использую эту процедуру. Часто мы запускаем select into скрипт для замедления (drip feed), чтобы обеспечить нормальное продолжение операций с базой данных.

3. Как вы обрабатываете записи, которые происходят со старой таблицей во время ее копирования (т. Е. перед переименованием)?

4. Мы перевели наш сайт в режим обслуживания для такого рода интенсивных операций. Предотвращение ввода-вывода во время миграции также повысит производительность.

5. Как вы обрабатываете FK в этом случае (InnoDB)?

Ответ №2:

ALTER TABLE в MySQL на самом деле собирается создать новую таблицу с новой схемой, затем повторно INSERT все данные и удалить старую таблицу. Вы могли бы сэкономить некоторое время, создав новую таблицу, загрузив данные и затем переименовав таблицу.

Из «Книги о высокопроизводительном MySQL» (ребята из percona):

Обычный прием для эффективной загрузки таблицы MyISAM — отключить ключи, загрузить данные и изменить ключи:

 mysql> ALTER TABLE test.load_data DISABLE KEYS;
-- load data
mysql> ALTER TABLE test.load_data ENABLE KEYS;

Ответ №3:

Что ж, я бы рекомендовал использовать последние сборки Percona MySQL plus, поскольку в руководстве по MySQL есть следующее примечание

В других случаях MySQL создает временную таблицу, даже если данные не требуют строгого копирования. Для таблиц MyISAM вы можете ускорить операцию воссоздания индекса (которая является самой медленной частью процесса изменения), установив системной переменной myisam_sort_buffer_size высокое значение.

Вы можете сделать ALTER TABLE DISABLE KEYS сначала, затем добавить столбец, а затем ALTER TABLE ENABLE KEYS . Я не вижу, что здесь можно что-то сделать.

Кстати, ты не можешь перейти на MongoDB? При добавлении столбца ничего не перестраивается.

Комментарии:

1. ключи отключения и включения работают только для движка myisam, там 170 002 225 строк, я думаю, что он использует innodb!

2. @Neo максимальное количество строк для MyISAM составляет ~ 2 ^ 32, и это значительно больше, чем 170 миллионов. С опцией —with-big-tables это ограничение увеличивается до ~ 2 ^ 64, что даже больше.

3. Таблицы MyISAM имеют проблемы с параллелизмом из-за блокировки чтения. InnoDB — это, безусловно, правильный путь.

4. @FoneyOp Я сомневаюсь, что переход на InnoDB ускорит изменение ТАБЛИЦЫ

5. Согласен. InnoDB почти всегда работает медленнее для ALTER TABLE по причинам, упомянутым здесь. Комментарий касался того, что мои максимальные строки находятся внутри 170 миллионов строк.

Ответ №4:

Может быть, вы можете удалить индекс перед изменением таблицы, потому что на построение индекса уходит большая часть времени?

Комментарии:

1. Индекс либо не будет перестроен, либо будет перестроен только один раз, в зависимости от ALTER TABLE . Удаление его и добавление обратно не сэкономили бы время.

2. Я указывал на двоичный поиск и на то, что создание индекса обходится дорого. Я никогда не пробовал это на 170 миллиардах строк.

3. строк всего 170 миллионов, а не миллиарды 😉

4. @Phpdna, при построении индексов выполняется сортировка, которая равна O (nlog n), а не двоичный поиск, который равен O (log n).

5. Этот ответ является наиболее правильным на практике, который я ve tested on - table that has >22.000.000 rows. What I did - dropped all indexes (there were 3), then added the field (that takes 4.23 on HDD) and recreate indexes, which took less then 2 mins each. Darius Jahandarie - isn't right, he do not know, in practice what he is talking about, the answer of RRUZ also seems to work but in practice - ВСТАВЛЯЮ В… ВЫБОР * FROM` занимает часы, когда удаление индексов-добавление столбца-воссоздание индексов кажется действительно быстрым решением. PS Я бы предпочел заблокировать эту таблицу раньше, чтобы предотвратить замедление запросов.

Ответ №5:

Объединив некоторые комментарии к другим ответам, это было решение, которое сработало для меня (MySQL 5.6):

  1. create table mytablenew like mytable;
  2. alter table mytablenew add column col4a varchar(12) not null after col4;
  3. alter table mytablenew drop index index1, drop index index2,...drop index indexN;
  4. insert into mytablenew (col1,col2,...colN) select col1,col2,...colN from mytable;
  5. alter table mytablenew add index index1 (col1), add index index2 (col2),...add index indexN (colN);
  6. rename table mytable to mytableold, mytablenew to mytable

В таблице с 75 метрами строк удаление индексов перед вставкой привело к тому, что запрос был выполнен за 24 минуты, а не за 43 минуты.

В других ответах / комментариях есть insert into mytablenew (col1) select (col1) from mytable , но это приводит к ERROR 1241 (21000): Operand should contain 1 column(s) тому, что у вас есть скобка в запросе select.

В других ответах / комментариях есть insert into mytablenew select * from mytable; , но это приводит к тому, ERROR 1136 (21S01): Column count doesn't match value count at row 1 что вы уже добавили столбец.