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 будет интенсивно использовать несколько ядер 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. Используйте выборочные обновления набора данных на основе фактических изменений данных (streaming, или триггеры, или event+streaming)

6. Частые обновления записей

Антипаттерн: Частое обновление одной и той же записи в разных транзакциях, создающее множество версий записи.

Почему это плохо?

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

• С точки зрения Firebird: цепочку версий записи нужно реконструировать, чтобы определить правильную версию для конкретной транзакции, это требует множества операций чтения, в результате сборка мусора становится значительно медленнее.

Решения:

1. Перейдите на Firebird 4+, там есть промежуточная сборка мусора

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

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

7. Использование записывающих транзакций для read-only выборок

Антипаттерн: Использование записывающих транзакций для read-only выборок приводит к избыточным операциям.

Почему это плохо?

• Использование записывающих транзакций для read-only выборок приводит к множеству ненужных записей страниц заголовков

• Использование записывающих транзакций для 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 для известных префиксов строк

Когда ваше значение поиска никогда не начинается с подстановочного знака %, предпочитайте 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. Оптимизация поиска по словам

При поиске целых слов (разделённых пробелами, запятыми и т.д.):

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

Почему это плохо?

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

• Выполнение проверок 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 (от 10 EUR/месяц) и участвуйте в закрытых продвинутых вебинарах!

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