MySQL очень медленный для запроса альтернативной таблицы

Почему требуется больше часа, чтобы просто обновить эту таблицу, чтобы добавить столбец? Эта таблица имеет 15M строк. Он имеет 2 индекса и один ключевой первичный ключ. Запрос ALTER TABLE находился в состоянии "копировать в таблицу tmp" в течение 1 часа 15 минут.

ALTER TABLE `frugg`.`item_catalog_map` 
ADD COLUMN `conversion_url` TEXT NULL DEFAULT NULL

Таблица:

mysql> describe item_catalog_map;
+------------------------+---------------+------+-----+---------+-------+
| Field                  | Type          | Null | Key | Default | Extra |
+------------------------+---------------+------+-----+---------+-------+
| catalog_unique_item_id | varchar(255)  | NO   | PRI | NULL    |       |
| catalog_id             | int(11)       | YES  | MUL | NULL    |       |
| item_id                | int(11)       | YES  | MUL | NULL    |       |
| price                  | decimal(10,2) | YES  |     | 0.00    |       |
+------------------------+---------------+------+-----+---------+-------+

mysql> show index from item_catalog_map;
+------------------+------------+----------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table            | Non_unique | Key_name             | Seq_in_index | Column_name            | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+----------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+
| item_catalog_map |          0 | PRIMARY              |            1 | catalog_unique_item_id | A         |    15485115 |     NULL | NULL   |      | BTREE      |         |
| item_catalog_map |          1 | IDX_ACD6184FCC3C66FC |            1 | catalog_id             | A         |          18 |     NULL | NULL   | YES  | BTREE      |         |
| item_catalog_map |          1 | IDX_ACD6184F126F525E |            1 | item_id                | A         |    15485115 |     NULL | NULL   | YES  | BTREE      |         |
+------------------+------------+----------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+

Ответы

Ответ 1

MySQL. Производительность ALTER TABLE может стать проблемой с очень большими таблицами. MySQL выполняет большинство изменений, сделав пустую таблицу с нужной новой структурой, вставив все данные из старой таблицы в новую и удалив старую таблицу. Это может занять очень много времени, особенно если у вас мало памяти, а таблица большая и имеет множество индексов. У многих людей есть опыт работы с операциями ALTER TABLE, которым потребовалось несколько часов или дней.

Ответ 2

Если вам не нравится время простоя, в моем предложении используются три разделенных оператора ALTER TABLE. Первый оператор удаляет все существующие вторичные индексы. Второе утверждение применяет все изменения, связанные с столбцом. Последний оператор добавляет отбрасываемые вторичные индексы назад и применяет другие изменения индекса.

Еще две подсказки:

Прежде чем применять изменения индекса, выполните два следующих оператора и измените значения на 1 после завершения изменения индекса.
```
SET unique_checks=0;
SET foreign_key_checks=0;
```
При создании нескольких вторичных индексов помещайте их в один оператор ALTER TABLE, а не в несколько разделенных операторов ALTER TABLE.

На следующем рисунке показана разница в производительности. Подход 1 - ваш подход и подход 2 - мой путь. Подход 2 занимает около 3,47% времени по сравнению с подходом 1 для таблицы в 50 м. Решение работает только для MySQL ( >= 5.5) двигателя InnoDB.

Ответ 3

В вашей таблице есть 15 миллионов строк, что и есть. ALTER TABLE включает в себя копирование по всем данным из таблицы и воссоздание индексов. В качестве первого измерения попробуйте скопировать файл данных (item_catalog_map.MYD, если это MyISAM) в вашу файловую систему, и посмотреть, сколько времени это займет. Это время, когда ALTER TABLE, по крайней мере, займет.

Ответ 4

Инструменты Percona - это спасатель для этого материала с большими таблицами.

http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html

они в основном:

создать повторяющуюся таблицу
создать триггер для синхронизации таблиц
объемные копии данных
проверить
таблицы подкачки

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

Ответ 5

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

Создайте новую пустую таблицу на основе существующей таблицы и измените эту новую пустую таблицу.
Сделайте mysqldump большой таблицы таким образом, чтобы она содержала один полный оператор insert для записи в большой таблице (переключатели -c и -skip-extended-insert)
Импортируйте этот mysqldump в другую (пустую) базу данных с пустой переименованной большой_таблицей.
Возьмите mysqldump этой новой таблицы переименований из другой базы данных и импортируйте ее в исходную базу данных

Переименуйте large_table и large_table_new в исходную базу данных.

mysql> create table DATABASE_NAME.LARGE_TABLE_NEW like DATABASE_NAME.LARGE_TABLE;
mysql> alter table DATABASE_NAME.LARGE_TABLE_NEW add column NEW_COLUMN_NAME COL_DATA_TYPE(SIZE) default null;

$ mysqldump -c --no-create-info --skip-extended-insert --no-create-db -u root -p DATABASE_NAME LARGE_TABLE > LARGE_TABLE.sql

mysql> create table test.LARGE_TABLE like DATABASE_NAME.LARGE_TABLE;

$ mysql -u root -p -D test < LARGE_TABLE.sql

mysql> rename table test.LARGE_TABLE to test.LARGE_TABLE_NEW;

$ mysqldump -c --no-create-info --skip-extended-insert --no-create-db -u root -p test LARGE_TABLE_NEW > LARGE_TABLE_NEW.sql

$ mysql -u root -p -D DATABASE_NAME < LARGE_TABLE_NEW.sql

mysql> rename table DATABASE_NAME.LARGE_TABLE to DATABASE_NAME.LARGE_TABLE_OLD, DATABASE_NAME.LARGE_TABLE_NEW to DATABASE_NAME.LARGE_TABLE;

Ответ 6

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