Хоча в базах даних значення 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 непогано справляється.