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

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

    • 使仪表板在启动时/某个表单上的加载为可选，默认禁用

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

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

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

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

3. 加载不必要的数据记录

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

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

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

delphi 
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 负载，因为即使查询在统计中显示 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 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 为潜在的快照事务维护大量旧版本

解决方案：

  • 尽可能使用只读事务，并尽快关闭可写事务

  • 使用现代 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 而不是主键来进行数据库完整性检查。

为什么不好？

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

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

解决方案：

  • 使用主键

  • 避免冗余的完整性检查

  • 保持数据库逻辑简单

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 
-- 使用生成器/序列！
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 操作的性能。

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
 // 静默失败
end;

为什么不好？

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

解决方案：

delphi 
try
 FDQuery1.Open;
except
 on E: Exception do
 begin
 // 全面日志记录
 Logger.Error('数据库连接失败：' + E.Message);
 ShowMessage('无法连接到数据库。请联系支持人员。');
 // 记录附加上下文
 Logger.LogStackTrace(E);
 end;
end;

联系信息