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;
 // Загружает всю таблицу в память
 DBGrid1.DataSource.DataSet := FDQuery1;
end;

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

• Несмотря на то, что сетка показывает только 50 записей, пользователи должны прокручивать тысячи записей вместо использования функции поиска

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

• С точки зрения Firebird:

• Каждое открытие требует чтения, хранения в кэше и передачи тысяч записей по сети

• Если вы держите набор данных открытым (в Delphi), Firebird хранит буферы, отсортированные записи во временном пространстве (если есть ORDER BY, GROUP BY и т.д.) до закрытия набора данных

Решения:

1. Ограничьте количество записей с помощью FIRST/SKIP/ROWS

2. Ограничьте количество записей с помощью некоторых критериев, например, показывайте записи, созданные/измененные за последние 3 дня

3. В целом, закрывайте запросы как можно скорее.

4. Чрезмерные запросы при прокрутке

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

procedure TForm1.GridScrolled(Sender: TObject);
begin
 // запрос для каждой строки
 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 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. Оптимизация поиска по словам

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

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

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

5. Для комплексных возможностей полнотекстового поиска:

• Рассмотрите использование IBSurgeon Full Text Search UDR

• Это решение с открытым исходным кодом предоставляет расширенные функции текстового поиска

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

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

• Длительное удержание транзакций открытыми может вынудить Firebird поддерживать множество версий записей для потенциальных транзакций моментального снимка

Решения:

• Используйте транзакции только для чтения, где это возможно, и закрывайте транзакции на запись как можно скорее

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