MySQL добавить несколько столбцов в большую таблицу оптимизированным способом

Я хотел добавить 8 новых столбцов в большую таблицу mysql (версия 5.6) с миллионами записей innodb. Я пытаюсь добиться этого наиболее оптимизированным способом.

  • Есть ли преимущество использования одного запроса для добавления всех столбцов по сравнению с добавлением 8 столбцов в 8 разных запросах. Если да, то хотелось бы знать, почему.

  • При указании ALGORITHM=INPLACE, LOCK=NONE обо всем, что мне нужно позаботиться, чтобы это не привело к повреждению данных или сбою приложения!

Я тестировал ALGORITHM=INPLACE, LOCK=NONE с запросом.

ALTER TABLE table_test ADD COLUMN test_column TINYINT UNSIGNED DEFAULT 0 ALGORITHM=INPLACE LOCK = NONE;

Но это занимает то же время, что и запрос с ALGORITHM=DEFAULT. В чем может быть причина.

Таблица, которая изменяется, имеет только индекс первичного ключа и никаких других индексов. Из приложения в эту таблицу поступают следующие запросы:

insert into table;
select * from table where user_id=uid;
select sum(column) from table where user_id=id and date<NOW();

mysql database innodb algorithm in-place
person bijoshtj    schedule 27.10.2017    source источник

Ответы (1)


arrow_upward
2
arrow_downward

Под «оптимизированным» вы подразумеваете «самый быстрый»? Или «наименьшее влияние на другие запросы»?

В старых версиях оптимальным способом (без надстроек) было поместить все ADD COLUMNs в один ALTER TABLE; затем подождите, пока он не закончится.

В любой версии pt-online-schema-change добавит все столбцы с коротким временем простоя.

Поскольку вы упомянули ALGORITHM=INPLACE, LOCK=NONE, я предполагаю, что вы используете более новую версию? Таким образом, может быть оптимальным 8 ALTERs. Будут некоторые помехи, но, возможно, не "слишком сильные".

ALGORITHM=DEFAULT позволяет серверу выбрать «лучший». Это почти всегда действительно «лучший». То есть редко нужно говорить что-то кроме DEFAULT.

Вы никогда не можете получить повреждение данных. В худшем случае запрос может завершиться ошибкой из-за тайм-аута из-за помех ALTER(s). Вы всегда должны проверять наличие ошибок (включая тайм-ауты) и обрабатывать их в своем приложении.

Для обсуждения вопросов...

insert into table;

По одному ряду? Или пакетно? (Пакетная обработка более эффективна — возможно, в 10 раз лучше.)

select * from table;

Конечно нет! Это даст вам все столбцы для миллионов строк. Почему вы должны это делать?

select count(column) from table where pk=id and date<NOW();

COUNT(col) проверяет col на то, что ему NOT NULL -- Вам это нужно? Если нет, то просто сделайте COUNT(*).

WHERE pk=id дает вам только одну строку; так зачем еще квалифицироваться с date<NOW()? PRIMARY KEY делает запрос максимально быстрым.

Единственный индекс PRIMARY KEY? Это кажется необычным для таблицы с миллионом строк. Это таблица «Фактов» в приложении «Хранилище данных»?

Внутреннее устройство

(Предостережение: большая часть этого обсуждения Внутреннего происходит косвенно и может быть неверным.)

Для некоторых ALTERs работа, по сути, заключается только в схеме. Например: добавление опций в конце ENUM; увеличение размера файла VARCHAR.

Для некоторых ALTERs с INPLACE обработка по существу изменяет данные на месте - без необходимости их копирования. Например: добавление столбца в конце.

PRIMARY KEY изменения (в InnoDB) обязательно включают перестроение BTree, содержащего данные; их нельзя сделать INPLACE.

Многие вторичные INDEX операции можно выполнять, не касаясь (кроме чтения) данных. DROP INDEX отбрасывает BTree и вносит некоторые мета-изменения. ADD INDEX читает всю таблицу, строит индекс BTree сбоку, а затем объявляет о своем существовании. Изменения CHARACTER SET и COLLATION требуют перестроения индекса.

Если таблица должна быть скопирована, это значит, что таблица заблокирована. Любой ALTER, которому необходимо прочитать все данные, имеет косвенное влияние из-за операций ввода-вывода и/или ЦП и/или кратковременных блокировок блоков/строк/и т. д.

Неясно, достаточно ли умен код, чтобы справиться с многозадачностью ALTER наиболее эффективным способом. Добавление 8 столбцов за один INPLACE проход должно быть возможным, но если это делает код слишком сложным, эту операцию может преобразовать в КОПИРОВАНИЕ.

Возможно, многозадачность ALTER подойдет в худшем случае. Например, изменение PRIMARY KEY и увеличение ENUM просто сделает и то, и другое в одном COPY. Поскольку COPY — это оригинальный способ выполнения всех ALTERs, к настоящему времени он хорошо отлажен и оптимизирован. (Но это медленно и агрессивно.)

COPY действительно довольно просто реализовать, в основном используя существующие примитивы:

  1. Заблокируйте real, чтобы на него никто не писал
  2. CREATE TABLE new LIKE real;
  3. ALTER TABLE new ... -- все, что вы просили
  4. скопируйте все строки от real до new - это медленная часть
  5. RENAME TABLE real TO old, new TO real; -- быстрый, атомарный и т. д.
  6. Разблокировать
  7. DROP TABLE old;

INPLACE более сложен, потому что он должен выбирать среди множества различных алгоритмов и уровней блокировки. DEFAULT должен переместиться на COPY, если он не может сделать INPLACE.

person Rick James    schedule 29.10.2017
comment
оптимизированный, я имею в виду как более быстрый, так и его влияние на живое приложение. Версия mysql 5.6, я пытался добавить несколько столбцов в один запрос, что быстрее. Несмотря на то, что я добавляю один столбец или 8 столбцов в один запрос, время остается таким же. - person bijoshtj; 30.10.2017
comment
@bijoshtj - Это приятно знать. Похоже, это тот ответ, который вы хотели? - person Rick James; 30.10.2017
comment
на влияние запроса это не повлияло ни на один из моих запросов, перечисленных выше. и я до сих пор не знаю, как. :) . это результат моего теста в тестовой среде. Я хотел бы знать, как это работает внутри. Я не мог найти подходящей документации для этого. - person bijoshtj; 30.10.2017
comment
Я добавил Внутренности. - person Rick James; 30.10.2017