MySQL добавить столбец без добавления каких-либо физических данных / увеличения использования хранилища?

Question

У меня есть эта большая таблица (сотни тысяч записей), к которой должен быть добавлен столбец. В столбце хранится только логическое значение (tinyint 0/1).

Цель состоит в том, чтобы избежать дополнительного использования памяти для уже существующих записей, потому что они будут иметь значение NULL/0 по умолчанию, и 1 можно будет установить в будущем, но не для всех и, конечно, не для всех одновременно.

Так занимает ли NULL место для хранения? Или добавление столбца, способного к NULL, добавит какие-либо физические данные в существующие строки?

Answer 1

(Я предполагаю, что вы используете механизм хранения InnoDB и формат строки COMPACT)

В теории:

• Значения NULL будут занимать 1 бит памяти в битовом векторе NULL в заголовке записи.
• Значения, отличные от NULL, будут занимать 1 байт памяти.

На практике:

• Добавление нового столбца приведет к перестроению таблицы, что в большинстве случаев приведет к дефрагментации данных, что займет меньше места на диске, чем раньше, но YMMV

Answer 2

Йохан ответил на этот вопрос о "MySQL: сколько места использует поле NULL?"

Если вы установите поле как не нулевое, это займет меньше места в MyISAM.

Если установить для него значение null, в MyISAM будет больше места.

В InnoDB нулевые значения занимают меньше места, поэтому он может работать там.

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

Другой вариант - не добавлять поле в эту таблицу, а сделать

table extra_data
  id integer primary key
  big_table_id integer
  large_data_seldom_used varchar(65000)

Если вам нужно выбрать дополнительные данные, сделайте:

SELECT large_data_seldom_used FROM bigtable b
INNER JOIN extra_data e ON (e.big_table_id = b.id)

Таким образом, вам вообще не нужно добавлять дополнительное поле в bigtable, экономя много места, если extra_field используется редко.