Хотя в базах данных значение NULL обычно обозначает «ничего», но тем не менее это «ничего» вполне себе занимает место и память. Встроенное в Firebird сжатие записей делает проблему не столь актуальной, но все еще довольно заметной для таблиц с миллионами строк.
Немного теории. Сколько места занимают разные типы
То что поле типа INTEGER занимает 4 байта, а поле типа SMALLINT 2 байта это понятно и новичку — с данными фиксированных форматов никаких неоднозначностей нет. Но все несколько сложней с типом VARCHAR. Может показаться что он занимает ровно столько, сколько занимает текст хранящийся в нем, плюс разве что еще два байта длины. На самом деле это не совсем так — VARCHAR(16) займет 16+2=18 байт независимо от того какой длины строку он фактически хранит. Но это в памяти (при фетче и распаковке записи со страницы в буфер. На диске же вся строка сжата простейшим алгоритмом сжатия — «RLE». Именно благодаря этому сжатию данные занимают меньше места на диске (если конечно в них есть повторяющиеся подряд одинаковые байты, но могут занять и больше, если повторений мало). Казалось бы, какая разница, используем ли мы VARCHAR(16) или скажем VARCHAR(256) для хранения строк, если неиспользуемый остаток строки все равно будет сжат? Но во первых распаковка в буфер тоже требует времени, а во вторых сжатие не такое уж и эффективное (например если пустой VARCHAR(16) можно сжать до двух байт, то пустой VARCHAR(256) уже сожмется до 6 байт, а скажем VARCHAR(32000) в котором ничего нет займет целых 500 байт).
В этом легко убедиться открыв базу данных при помощи инструмента «DatabaseInside» который есть в IBExpert.
Тест заполнения таблиц с NULL-строками разной длины
Для того чтобы проверить как именно будет изменяться занимаемое NULL-строками место при увеличении максимальной их длины я создал пять таблиц с одним ключевым полем INTEGER и одним полем VARCHAR в кодировке 1251 с длинами 16, 120, 256, 8192, 32762 символов. Причем первые две длины (16 и 120) выбрал такими, чтобы упакованная строка занимала одинаковое место. Я хотел посмотреть будет ли разница в скорости.
Тест полного чтения таблиц с NULL-строками разной длины
В каждую из таблиц было залито по одному миллиону строк. Integer содержал идентификатор, а varchar был всегда null. Я не привожу марку процессора, винчестера и другие характеристики системы, так как цель сравнить результаты между собой в рамках одной и той же системы.
Итак теперь самое интересное, займемся интерпретацией результатов тестов! В колонках varchar(16) и varchar(120) мы видим одинаковое количество чтений, но несколько разное время выполнения запроса. Это говорит о том что распаковка NULL-строки разной длины (звучит абсурдно) занимает разное время. Дальнейшее увеличение максимального размера varchar ожидаемо увеличивает время выполнения запроса, в последней колонке время еще больше за счет того что вся таблица не уместилась в кэш, под который отведено 65535 страниц.
Отдельно следует сказать про выборку count(*), которая очевидно оптимизирована таким образом, что если это возможно запись не распаковывается, и соответственно мы видим что во первых выполнение идет существенно быстрей, а во вторых увеличение максимальной длины varchar замедляет выполнение запроса совсем незначительно.
Если сравнивать результаты тестов между Firebird 2.5 и Firebird 3.0 то видно что последний практически всегда уступает в скорости, хотя и есть два теста, в которых он смог обогнать Firebird 2.5 (кстати не очень понятно за счет чего).
Как оптимизировать. Выводы.
Совершенно точно можно сказать что неосторожное добавление столбца varchar к таблице с большим количеством записей негативно повлияет на производительность даже если там будет NULL по большей части. Так что не лишним будет подумать о вынесении такой информации в отдельную таблицу, особенно для случая когда эта информация редко используется.
P.S. Что интересно — в трекере для «Firebird 4.0» уже висит запрос (CORE-4401) на усовершенствование алгоритма сжатия записей, чтобы он более эффективно сжимал длинные последовательности (хотя раньше он был запланирован еще в версии 3.0). Весь вопрос в том удастся ли сделать это так чтобы не ухудшить те результаты, где и существующий RLE неплохо справляется.