联系方式
IBSurgeon
此页面为机器翻译。请阅读英文原文。 English

IBSurgeon 文库

15 个 Firebird 反模式

作者：Alexey Kovyazin，2025年1月14日

引言

本文档概述了使用 Firebird 数据库时的 15 个常见反模式，并为每个反模式提供了解决方案。

1. 对 MON$ 的多个并行查询

反模式： 一个非常常见的错误——在 OnConnect 触发器中查询 MON$ATTACHMENTS 以获取用户详细信息用于审计目的，或计算连接数用于许可目的。

为什么不好？

  • MON$ 表是虚拟表，存储在 fbNN_mon_xx 系统文件中，包含性能统计信息等

  • 文件 >1Gb 意味着你使用得太多了

  • 它们仅设计供系统管理员使用——即 1-2 个并行查询，专供管理员使用

  • 200+ 个连接并行查询 MON$ 会显著降低 Firebird 性能，500+ 个同时查询将很有可能“挂起”Firebird

解决方案：

  • 不要将 MON$ 用于非管理任务，即计数或审计，避免在 OnConnect 中使用它们

  • 对于审计目的：

  • 使用上下文变量，如 CURRENT_USER、CURRENT_TIMESTAMP 等

  • 使用 Audit——Firebird 原生功能，比触发器强大得多

  • 对于许可目的——使用用户上下文变量

2. 仪表板加载缓慢

反模式： 在应用程序启动时加载综合仪表板或记分板，汇总上个月或全年的所有订单和发票，或每分钟或更频繁地更新某些指标。

sql 
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. 减少能看到仪表板的用户数量：

  • 通常仪表板仅分析师和管理层需要，将其从一般应用程序负载中排除

  • 使启动时/某些窗体的仪表板加载变为可选，默认禁用

  • 通过显式按钮点击加载仪表板数据，而非在启动时（即，将其作为报表）

  1. 通过调度（即机器人）用 1 个进程计算仪表板数据，并将其存储到一个简单的表中，以便通过简单查询检索

  2. 使用触发器聚合数据并存储以备使用

  3. 使用副本数据库计算仪表板数据（以及所有重型报表）

3. 加载不必要的记录

反模式： 打开应用程序或窗体时，将所有数据不加过滤地加载到网格中，无论其是否包含数十万条记录。

delphi 
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. 滚动时过度查询

反模式： 在滚动事件上执行查询。例如，在网格或表格中显示数据时，为每条记录执行单独的查询，或者如果你使用经典的双网格主从滚动示例而没有延迟。

delphi 
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 负载，因为即使查询在统计中显示 0ms，它也需要被准备、执行、传输结果等

解决方案：

  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 使用索引（即使索引存在）：

sql 
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：

sql 
WHERE fieldName STARTING WITH ?param1

2. 优化双向字符串搜索

对于具有已知前缀或后缀模式的字符串，使用反向索引：

sql 
-- 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. 基于单词的搜索优化

当搜索完整单词（由空格、逗号等分隔）时：

  • 创建单独的单词-ID 映射表

  • 通过映射表进行搜索，而非原始文本

5. 对于全面的全文搜索功能：

  • 考虑使用 IBSurgeon Full Text Search UDR

  • 这个开源解决方案提供高级文本搜索功能

9. 只读操作未关闭事务

为什么不好？

  • 长时间保持事务打开可能迫使 Firebird 为潜在的快照事务维护大量旧版本记录

解决方案：

  • 在可能的情况下使用只读事务，并尽快关闭可写事务

  • 使用现代 Firebird 版本（4+）以减少记录版本链的影响

  • 实施适当的 sweep

10. 查询参数化问题

反模式： 避免使用预编译查询和参数化，而是将参数值直接嵌入查询文本中。

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

为什么不好？

  • 这种做法会降低重复查询的性能

  • 每个嵌入参数值的查询都必须作为新查询进行预编译

  • 对于大表，预编译可能耗时较长

  • 使问题分析复杂化

  • 难以按文本对查询进行分组

  • 产生 SQL 注入漏洞

解决方案：

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

11. 错误的完整性检查：使用触发器/CHECK 而非主键

反模式： 使用触发器或 CHECK 而非主键进行数据库完整性检查。

为什么不好？

  • 这忽略了主键验证使用特殊模式来读取记录的当前版本，不受用户事务隔离级别的影响。

  • 在用户事务中使用触发器进行主键检查会增加重复的可能性，并不必要地复杂化逻辑

解决方案：

  • 使用主键

  • 避免冗余的完整性检查

  • 保持数据库逻辑简单

12. 使用 MAX() 生成 ID

反模式： 使用 MAX(id)+1 生成新标识符不可靠且效率低下。

sql 
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) 的组合同样无效！

解决方案：

sql 
-- 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 操作的性能。

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

为什么不好？

  • 计算字段是即时计算的，不应实现复杂逻辑，且会显著复杂化优化工作

  • 它加强了表之间的关联

  • 计算字段仅适用于对本表字段进行轻量级计算（如拼接）才有意义

解决方案：

sql 
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 错误和警告！

delphi 
try
 FDQuery1.Open;
except
 // Silent failure
end;

为什么不好？

  • 隐藏错误会妨碍正确的诊断和调试。适当的错误日志记录对于快速理解和解决问题至关重要。

解决方案：

delphi 
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;

联系信息