15 антипатернів Firebird

Олексій Ковязин, 14-січ-2025

Вступ

Цей документ описує 15 поширених анти-патернів при роботі з базами даних Firebird та надає рішення для кожного з них.

1. Множинні паралельні запити до MON$

Анти-патерн: Дуже поширена помилка - тригер OnConnect, запит до MON$ATTACHMENTS для вибору даних користувача з метою аудиту, або підрахунок кількості з’єднань для ліцензування.

Чому це погано?

• Таблиці MON$ є віртуальними таблицями, які зберігаються в системних файлах fbNN_mon_xx, зі статистикою продуктивності тощо

• Файл >1 Гб означає, що ви використовуєте його занадто часто

• Вони призначені лише для використання системними адміністраторами - тобто, 1-2 паралельних запити, виключно для адміністраторів

• 200+ з’єднань з паралельними запитами до MON$ значно сповільнять Firebird, а 500+ одночасних запитів “підвісять” Firebird з високою ймовірністю

Рішення:

• Не використовуйте MON$ для неадміністративних завдань, тобто для підрахунку або аудиту, уникайте їх використання в OnConnect

• Для цілей аудиту:

  • Використовуйте контекстні змінні, такі як CURRENT_USER, CURRENT_TIMESTAMP тощо
  • Використовуйте Audit - вбудовану функцію Firebird, набагато потужнішу за тригери

• Для цілей ліцензування - використовуйте контекстні змінні користувача

2. Повільне завантаження дашбордів

Анти-патерн: Завантаження комплексних дашбордів або табло, які підсумовують усі замовлення та рахунки за останній місяць або рік під час запуску застосунку, або оновлення деяких метрик щохвилини або частіше.

SELECT
 SUM(total_sales) as yearly_sales,
 COUNT(DISTINCT customers) as customer_count,
 AVG(order_value) as avg_order_value
FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-01-01';

Чому це погано?

• Користувачі повинні чекати кілька секунд, щоб побачити загальнокомпанійну статистику, перш ніж вони зможуть розпочати свою фактичну роботу

• З точки зору Firebird - щоб постійно виконувати багато паралельних запитів, отримувати величезні обсяги даних, сортувати/групувати їх, Firebird інтенсивно використовуватиме кілька ядер процесора, читаючи з диска, кешу, пам’яті, виділеної для сортування (а іноді сортування йде на диск)

• Це як створення звіту кілька разів на хвилину!

Рішення:

1. Зменшіть кількість користувачів, які бачитимуть дашборди:

   • Зазвичай дашборд потрібен лише аналітикам та керівництву, виключіть його із загального завантаження застосунку
   • Зробіть завантаження дашборду при запуску/для деякої форми опціональним, вимкненим за замовчуванням
   • Завантажуйте дані дашборду за явним натисканням кнопки, а не при запуску (тобто зробіть це як звіт)

2. Розраховуйте дані дашборду одним процесом за розкладом (тобто роботом) і зберігайте їх у простій таблиці, готовій для отримання простим запитом

3. Використовуйте тригери для агрегації даних і зберігання їх у готовому для використання вигляді

4. Використовуйте репліку бази даних для розрахунку даних дашборду (а також усіх важких звітів)

3. Завантаження непотрібних записів

Анти-патерн: Завантаження всіх даних без фільтрації в сітку при відкритті застосунку або форми, незалежно від того, чи містить вона сотні тисяч записів.

procedure TDataForm.LoadAllRecords;
begin
 FDQuery1.SQL.Text := 'SELECT * FROM large_table';
 FDQuery1.Open;
 // Завантажує всю таблицю в пам'ять
 DBGrid1.DataSource.DataSet := FDQuery1;
end;

Чому це погано?

• Незважаючи на те, що сітка показує лише 50 записів, користувачі повинні прокручувати тисячі записів замість використання функції пошуку

• У 99% випадків користувачам потрібен дуже вузький підмножина даних: наприклад, останні записи продажів

• З точки зору Firebird:

  • Кожне відкриття вимагає читання, зберігання в кеші та передачі тисяч записів через мережу
  • Якщо ви тримаєте набір даних відкритим (у Delphi), Firebird зберігає буфери, відсортовані записи в тимчасовому просторі (якщо є ORDER BY, GROUP BY тощо) до закриття набору даних

Рішення:

1. Обмежте кількість записів за допомогою FIRST/SKIP/ROWS

2. Обмежте кількість записів за допомогою критеріїв, наприклад, показуйте записи, створені/змінені протягом останніх 3 днів

3. Загалом, закривайте запити якомога швидше.

4. Надмірні запити при прокручуванні

Анти-патерн: Виконання запитів під час подій прокручування. Наприклад, при відображенні даних у сітці або таблиці, виконання окремого запиту ДЛЯ КОЖНОГО запису, або якщо ви використовуєте класичний приклад прокручування master-detail у 2 сітках без затримки.

procedure TForm1.GridScrolled(Sender: TObject);
begin
 // запит для кожного рядка
 FDQuery2.SQL.Text :=
 'SELECT additional_info FROM details ' +
 'WHERE id = ' + IntToStr(CurrentRowId);
 FDQuery2.Open;
end;

Чому це погано?

• Виконання окремого запиту ДЛЯ КОЖНОГО запису в динамічній сітці змушує Firebird обробляти тисячі дрібних запитів, непотрібно споживаючи ресурси процесора

• З точки зору Firebird:

  • Багато (тисячі за секунду) дрібних запитів створять значне навантаження на процесор, оскільки навіть якщо запит показує 0 мс у статистиці, він вимагає підготовки, виконання, передачі результату тощо

Рішення:

1. Завантажуйте кілька рядків одночасно за допомогою пакетних операцій

2. Розширте основний запит для сітки, щоб виконувати детальний запит як його частину

3. Додайте явну кнопку для завантаження деталей для видимої частини сітки

4. Додайте затримку для виконання запиту отримання деталей, щоб запобігти миттєвим запитам під час прокручування

5. Не вмикайте завантаження деталей при прокручуванні для всіх користувачів за замовчуванням

5. Непотрібне автоматичне оновлення

Анти-патерн: Автоматичне оновлення даних сітки з мінімальними інтервалами в кожному клієнтському застосунку, з цією функцією, увімкненою за замовчуванням.

Чому це погано?

• Це призводить до того, що сотні клієнтських з’єднань виконують майже ідентичні запити для отримання тих самих записів

• Де це відбувається: автоматичне оновлення розкладів, або вибір позицій у черзі, або пошук “найближчого слота” тощо

• З точки зору Firebird:

  • Поєднання завантаження дашбордів і подій прокручування: багато середніх запитів створюють навантаження на систему

Рішення:

1. Збільште інтервал!

2. Впровадьте явні (ініційовані користувачем) оновлення

3. Використовуйте вибіркове оновлення набору даних на основі фактичних змін даних (стрімінг або тригери або подія+стрімінг)

6. Часті оновлення записів

Анти-патерн: Часте оновлення одного й того ж запису в різних транзакціях, створюючи численні версії записів.

Чому це погано?

• Запис із десятками версій може значно погіршити продуктивність, запис із тисячами може стати блокувальником

• З точки зору Firebird: ланцюжок версій запису має бути реконструйований для визначення правильної версії для конкретної транзакції, це вимагає численних операцій читання, в результаті збір сміття стає значно повільнішим.

Рішення:

1. Мігруйте на Firebird 4+, там є проміжний збір сміття

2. Не тримайте довгі транзакції запису, виконуйте належний збір сміття

3. Для Firebird <4 розгляньте використання DELETE+INSERT замість UPDATE

7. Використання транзакцій запису для read-only select

Анти-патерн: Використання транзакцій запису для read-only select призводить до надмірних операцій.

Чому це погано?

• Використання транзакцій запису для read-only select призводить до багатьох непотрібних записів сторінок заголовків

• Використання транзакцій запису для read-only операцій неефективне (великий TIP під час коміту створює додаткове навантаження на сервер)

Рішення:

• Використовуйте окрему read-only транзакцію для операцій, які не змінюють дані

• Firebird - одна з небагатьох баз даних, яка дозволяє відкривати кілька транзакцій у межах одного з’єднання

• Глобальні тимчасові таблиці доступні для використання в read-only транзакціях

8. Використання LIKE :param

Наступний запит із параметром не використовуватиме індекс для поля fieldName (навіть якщо індекс існує):

SELECT * FROM Table1 WHERE fieldName LIKE :param1

Чому це погано?

Оскільки LIKE дозволяє пошук із підстановочними символами (%), які можуть замінювати будь-яку кількість символів, Firebird не може заздалегідь визначити, чи буде значення параметра придатним для пошуку за індексом.

Зазвичай розробники намагаються обійти це, вбудовуючи значення параметра в текст запиту:

• fieldName LIKE «Alex%» - можливе використання індексу

• fieldName LIKE «%Alex» - неможливо використати стандартний індекс

• fieldName LIKE «%Alex%» - неможливо використати індекс взагалі

Це призводить до інших проблем (див. #10 нижче).

Рішення:

1. Використовуйте STARTING WITH для відомих префіксів рядків

Коли ваше значення пошуку ніколи не починається з wildcard %, віддавайте перевагу STARTING WITH замість LIKE:

WHERE fieldName STARTING WITH ?param1

2. Оптимізуйте двосторонній пошук рядків

Для рядків з відомими шаблонами префіксів або суфіксів використовуйте зворотний індекс:

-- Створення зворотного індексу
CREATE INDEX ixreverse1 ON TABLE1 COMPUTED BY (REVERSE(fieldName));
-- Запит з використанням обох напрямків
WHERE fieldName STARTING WITH :param1
   OR reverse(fieldName) STARTING WITH reverse(:param2)

3. Реалізуйте стратегію прогресивного пошуку

Для рядків, які зустрічаються на початку/в кінці/в середині (але не одночасно):

• Спочатку спробуйте швидкий індексований пошук з STARTING WITH

• Якщо результатів не знайдено, використовуйте повільніший пошук LIKE

4. Оптимізація пошуку за словами

Під час пошуку повних слів (розділених пробілами, комами тощо):

• Створіть окрему таблицю відповідності слово-ID

• Шукайте через таблицю відповідності замість оригінального тексту

5. Для повноцінного повнотекстового пошуку:

• Розгляньте використання IBSurgeon Full Text Search UDR

• Це рішення з відкритим кодом надає розширену функціональність текстового пошуку

9. Незакриття транзакцій для операцій лише для читання

Чому це погано?

• Відкриті транзакції протягом тривалого часу можуть змусити Firebird підтримувати численні старі версії записів для потенційних snapshot-транзакцій

Рішення:

• Використовуйте транзакції лише для читання, де це можливо, і закривайте транзакції для запису якомога швидше

• Використовуйте сучасні версії Firebird (4+) для зменшення впливу ланцюжків версій записів

• Реалізуйте належний sweep

10. Проблеми з параметризацією запитів

Анти-патерн: Уникнення підготовлених запитів і параметризації, замість цього вбудовування значень параметрів безпосередньо в текст запиту.

FDQuery1.SQL.Text :=
 'SELECT * FROM users WHERE name = ''' +
 EditUsername.Text + '''';
FDQuery1.Open;

Чому це погано?

• Це знижує продуктивність для повторюваних запитів

• Кожен запит із вбудованими значеннями параметрів має бути підготовлений заново

• Підготовка може бути довгою та трудомісткою для великих таблиць

• Ускладнює аналіз проблем

• Важко групувати запити за текстом

• Створює вразливості SQL-ін’єкцій

Рішення:

FDQuery1.SQL.Text :=
 'SELECT * FROM users WHERE name = :username';
FDQuery1.ParamByName('username').AsString :=
 EditUsername.Text;
FDQuery1.Open;

11. Неправильна перевірка цілісності: тригери/CHECK замість первинного ключа

Анти-патерн: Використання тригерів або CHECK замість первинних ключів для перевірки цілісності бази даних.

Чому це погано?

• Це ігнорує той факт, що перевірка первинного ключа використовує спеціальний режим для читання поточної версії запису, незалежно від рівня ізоляції транзакцій користувача.

• Виконання перевірок первинного ключа за допомогою тригерів у транзакціях користувача збільшує ймовірність дублювання та надмірно ускладнює логіку

Рішення:

• Використовуйте первинні ключі

• Уникайте надлишкових перевірок цілісності

• Тримайте логіку бази даних простою

12. Генерація ID за допомогою MAX()

Анти-патерн: Використання MAX(id)+1 для нових ідентифікаторів є ненадійним та неефективним.

INSERT INTO users (id, name)
VALUES ((SELECT MAX(id) + 1 FROM users),
 'John Doe');

Чому це погано?

• Використання MAX(id)+1 замість послідовностей (генераторів) для нових ідентифікаторів

• MAX(id)+1 не гарантує унікальності за звичайних параметрів транзакцій - дві паралельні транзакції можуть отримати однакове значення MAX()

• Комбінація Max()+1 та CHECK(select if unique) також не працює!

Рішення:

-- Використовуйте генератор/послідовність!
CREATE GENERATOR gen_user_id;
-- Використовуйте генератор для генерації ID
INSERT INTO users (id, name)
VALUES (
 GEN_ID(gen_user_id, 1),
 'John Doe' );

13. Неефективне використання GUID

Чому це погано?

• Використання системних GUID замість gen_uuid() може вплинути на продуктивність індексів

• Системний GUID є високовипадковим

Рішення:

• Використовуйте функцію gen_uuid()

• Розгляньте використання BIGINT

• У версії 6 буде UUID v7

14. Неефективні обчислювані поля

Анти-патерн: Використання обчислюваних полів із SELECT до інших таблиць значно знижує продуктивність простих операцій SELECT.

CREATE TABLE orders (
 id INTEGER,
 total_amount COMPUTED BY (
 (SELECT SUM(item_price) FROM order_items
 WHERE order_items.order_id = orders.id)));

Чому це погано?

• Обчислювані поля обчислюються на льоту, і вони не призначені для реалізації складної логіки, що може значно ускладнити зусилля з оптимізації

• Це посилює зв’язки між таблицями

• Обчислювані поля має сенс використовувати лише для легких обчислень з полями таблиці, наприклад, конкатенації

Рішення:

CREATE TABLE orders (
 id INTEGER PRIMARY KEY,
 cached_total_amount DECIMAL(10,2));

CREATE TRIGGER update_order_total
BEFORE INSERT OR UPDATE ON orders
AS
BEGIN
 NEW.cached_total_amount = (
 SELECT SUM(item_price)
 FROM order_items
 WHERE order_items.order_id = NEW.id
 );
END;

15. Придушення помилок без журналювання

Анти-патерн: Не придушуйте помилки та попередження Firebird без журналювання!

try
 FDQuery1.Open;
except
 // Тихий збій
end;

Чому це погано?

• Приховування помилок перешкоджає належній діагностиці та налагодженню. Належне журналювання помилок є критично важливим для швидкого розуміння та вирішення проблем.

Рішення:

try
 FDQuery1.Open;
except
 on E: Exception do
 begin
 // Повне журналювання
 Logger.Error('Помилка підключення до бази даних: ' + E.Message);
 ShowMessage('Неможливо підключитися до бази даних. Будь ласка, зверніться до служби підтримки.');
 // Журналювання додаткового контексту
 Logger.LogStackTrace(E);
 end;
end;

Контактна інформація

• Надсилайте свої запитання на [email protected]

• Станьте прихильником Firebird (від 10 євро/місяць) та беріть участь у закритих просунутих вебінарах!

• https://store.firebirdsql.org/p/firebird-associate-donation-eur/