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 в 2 сетках без задержки.

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. Оптимизација претраге засноване на речима

Када тражите комплетне речи (раздвојене размацима, зарезима, итд.):

• Направите посебну табелу мапирања речи на 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 injection

Решења:

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('Database connection failed: ' + E.Message);
 ShowMessage('Unable to connect to database. Please contact support.');
 // Евидентирање додатног контекста
 Logger.LogStackTrace(E);
 end;
end;

Контакт информације

• Пошаљите своја питања на [email protected]

• Постаните Firebird Supporter (од EUR10 месечно) и учествујте у затвореним напредним вебинарима!

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