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

від Alexey Kovyazin, 14-Jan-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 інтенсивно використовуватиме кілька ядер CPU, читаючи з диска, кешу, пам’яті, виділеної для сортування (а іноді сортування відбувається на диску)

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

Рішення:

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

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

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

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

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

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

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

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

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

procedure TDataForm.LoadAllRecords;
begin
 FDQuery1.SQL.Text := 'SELECT * FROM large_table';
 FDQuery1.Open;
 // Loads entire table into memory
 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
 // query for each row
 FDQuery2.SQL.Text :=
 'SELECT additional_info FROM details ' +
 'WHERE id = ' + IntToStr(CurrentRowId);
 FDQuery2.Open;
end;

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

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

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

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

Рішення:

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

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

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

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

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

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

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

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

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

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

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

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

Рішення:

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

2. Реалізуйте явні (ініційовані користувачем) оновлення

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

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

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

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

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

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

Рішення:

1. Перейдіть на Firebird 4+, там є проміжне збирання сміття

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

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

7. Використання транзакцій запису для вибірок лише для читання

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

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

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

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

Рішення:

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

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

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

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 для відомих префіксів рядків

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

WHERE fieldName STARTING WITH ?param1

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

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

-- Create reversed index
CREATE INDEX ixreverse1 ON TABLE1 COMPUTED BY (REVERSE(fieldName));
-- Query using both directions
WHERE fieldName STARTING WITH :param1
   OR reverse(fieldName) STARTING WITH reverse(:param2)

3. Впровадьте стратегію прогресивного пошуку

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

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

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

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

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

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

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

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 замість первинних ключів для перевірки цілісності бази даних.

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

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

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

Рішення:

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

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

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

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) також не працює!

Рішення:

-- Use generator/sequence!
CREATE GENERATOR gen_user_id;
-- Use generator for ID generation
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
 // Silent failure
end;

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

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

Рішення:

try
 FDQuery1.Open;
except
 on E: Exception do
 begin
 // Comprehensive logging
 Logger.Error('Database connection failed: ' + E.Message);
 ShowMessage('Unable to connect to database. Please contact support.');
 // Log additional context
 Logger.LogStackTrace(E);
 end;
end;

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

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

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

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