45种加速Firebird数据库的方法

在这里，您可以找到针对Firebird数据库不同领域的性能优化技巧列表–从硬件/操作系统和Firebird配置调优到SQL优化建议。此列表并非如何优化Firebird的完整参考，它假定您了解Firebird的基本运作原理，例如执行计划、事务管理和查询性能统计。

请谨慎应用这些技巧，并在投入生产环境之前验证其效果。

我们公司（IBSurgeon）提供全面的数据库性能优化服务。

1. 将数据库放在SSD上

将数据库放在SSD上。SSD驱动器比传统驱动器提供更好的随机IO。随机IO对于读取和写入分布在大数据库文件中的数据至关重要–大多数数据库操作需要密集的并行随机IO。

2. 使用RAID 10

如果您使用RAID1或RAID5，请考虑使用RAID10–它快15-25%。

3. 检查BBU

如果您使用RAID控制器，请检查其是否安装并运行了备份电池单元（BBU）–有些供应商默认不提供BBU。没有BBU，控制器会禁用缓存，RAID运行非常慢，甚至比普通SATA驱动器还慢。通常，您可以在RAID配置工具中检查BBU状态。

4. 将写缓存设置为write-back

如果您使用带有已安装BBU的RAID控制器（以及带有UPS的服务器），请检查其缓存是否设置为write-back（而非write-through）。“Write-back"启用控制器的写缓存。

5. 启用读缓存

如果您使用RAID控制器，请检查其是否启用了读缓存。

6. 检查磁盘子系统

检查您的驱动器是否有坏块和其他硬件问题（包括过热）。硬件问题会显著降低IO性能并导致数据库损坏。

7. 在Firebird 2.5中使用SuperClassic或Classic

如果您使用Firebird 2.5 SuperServer并有许多连接，请尝试使用SuperClassic或Classic，它们可以通过使用CPU的所有核心更好地扩展。

8. 在Firebird 3中使用SuperServer 3.0

如果您在2.5中使用Classic或SuperClassic，请考虑迁移到Firebird 3.0 SuperServer，现在它可以利用多核并同时结合共享缓存的优势。

9. 增加页面缓冲区缓存

将页面缓冲区缓存的大小（参数DefaultDBCachePages）从默认值增加。对于2.5 SuperServer，我们建议10000页；对于3.0 SuperServer，建议50000页；对于Classic和SuperClassic，建议256到2048页。但是，不要将页面缓冲区缓存值设置得太高–缓存同步有其成本，通过调整此值将整个数据库放入RAM的想法是行不通的。请在此处使用预优化的Firebird配置文件：/zh/optimized-firebird-configuration/

10. 增加排序操作的内存大小

增加firebird.conf中TempCacheLimit参数的值–它指定排序临时空间缓存的大小。默认值太低（Classic为8Mb，SuperServer为64Mb），请至少使用Classic 64Mb和SuperServer及SuperClassic 1Gb。同样，请使用#9中的优化配置文件。

11. 关闭Forced Writes（谨慎操作！）

如果您有密集的插入或更新活动（您可以使用HQbird MonLogger检查，详情请参见HQbird用户指南第60页），并且您已安装UPS和复制以保护免受硬件故障影响，请考虑将Forced Writes设置设为OFF，这可以将写入操作速度提高最多3倍。

12. 为Classic/SuperClassic增加哈希槽数量

将Classic和SuperClassic的LockHashSlots参数值从默认的1009增加到某个较大的质数（例如30011），这将减少内部锁定机制中的队列。

13. 为Super Server 2.5使用CPU亲和性

如果您使用SuperServer 2.5，请将CPUAffinity参数设置为等于正在使用的数据库数量的值：2.5中的SuperServer可以使用不同的CPU核心来处理特定数据库的请求。

14. 为临时空间使用快速驱动器

将firebird.conf中TempDirectory参数的第一部分设置为快速磁盘–SSD或RAM驱动器。这将减少大型排序的时间–例如在数据库恢复时。

15. 将数据库备份存储在另一个驱动器上

将数据库备份存储在专用的物理驱动器（RAID）上。这将在备份期间分离读写IO，提高备份速度并减少主驱动器的负载。当用户在数据库工作时进行备份，这一点尤其重要。有关Firebird硬件配置的更多详细信息，请参阅”Firebird硬件指南"。

16. 为批量插入停用索引

如果您插入或更新大量记录（超过表的25%），请为插入记录的表停用索引，并在插入或更新后重新激活它们。索引重建操作可能比多次更新索引更快。

17. 使用全局临时表进行快速插入

为了加速插入和更新，请对大批量记录集的批量插入使用全局临时表，然后将记录转移到永久表中。将记录插入GTT、预处理它们然后移动到持久表可能非常有效。

18. 避免不必要的索引

对于插入和更新密集的表，使用更少的索引。每个索引都会为插入、更新、删除和垃圾回收操作增加显著的开销–对于每个索引，插入/更新/删除/清理单条记录时可能会有3-4次额外的页面读取和写入。

19. 用内嵌函数调用替换UDF

用内嵌函数调用替换UDF调用。在Firebird的最近版本中添加了许多内嵌函数，它们提供了以前仅在UDF库中可用的功能。尽可能替换此类函数，因为内嵌函数的运行速度比UDF快最多3倍。

20. 对读操作使用只读事务

对不更改记录的操作（即SELECT）使用只读事务，隔离模式为read committed。此类事务不会保留来自垃圾回收的记录版本，并且可以无限期运行：它们不会影响数据库性能。

21. 使用短写事务并消除所有长时间运行的事务

使用短的可写事务（用于INSERT/UPDATE/DELETE操作）。

可写事务越短越好。短事务保留的来自垃圾回收的记录版本数量按比例少于长时间运行的事务。不幸的是，即使单个长时间运行的事务（例如从开发工具中遗留打开的）也会破坏所有其他短可写事务的良好效果。这就是为什么您需要监控长时间运行的事务并修复源代码中的相应位置。使用HQbird DataGuard工具接收有关Firebird数据库中最旧活动事务的警报（哪个应用程序启动了它、什么IP地址、其启动时间戳），并使用HQbird MonLogger工具查看完整的长时间运行活动事务列表及其IO统计信息。此外，如果您使用可以缓存记录集的数据库访问组件/库，请使用缓存更新。

22. 避免长记录链

避免一条记录有多个记录版本的情况–Firebird在处理长记录链时运行速度要慢得多。（要查看某些表有多少记录版本，以及最长的记录链是什么，您可以使用HQbird IBAnalyst工具，在Tables选项卡中按"Max Version"排序）。请使用插入和定期删除旧记录的组合，而不是多次更新同一条记录。

23. 正确使用PREPARE

使用预编译语句来执行仅参数变化的SQL查询–例如，在循环执行此类查询之前先进行预编译。预编译可能耗费大量时间（尤其是对于大表），而只预编译一次查询将大幅提升整体性能。

24. 批量插入/更新操作时不要过于频繁地COMMIT

在进行批量INSERT/UPDATE/DELETE操作时，不要在每次更改后都提交事务（如果数据库驱动中启用了自动提交选项，就可能发生这种情况）–至少每1000次或更多操作后再提交事务。每次事务提交都会对数据库执行多次读写IO操作，因此频繁提交会降低数据库性能。

25. 在IN子句中使用大量常量时“关闭”索引

如果使用 WHERE fieldX IN (Constant1, Constant2,… ConstantN) 这样的结构，并且fieldX上有索引，Firebird会按IN列表中常量的数量多次使用索引。通过将fieldX转换为表达式+0来禁用索引搜索：WHERE fieldX+0 IN (Constant1, Constant2,… ConstantN)，对于字符串，可使用 fieldX||''。

26. 用JOIN替代IN

避免使用嵌套的 WHERE IN(SELECT... WHERE IN (SELECT.. WHERE IN() )) 查询，这可能会让Firebird优化器感到困惑。将嵌套的IN转换为连接（JOIN）。

27. 正确使用LEFT JOIN

如果使用LEFT OUTER JOIN，请明确地将表按从小到大的顺序放入连接中。

28. 限制SELECT查询的获取量

始终尝试使用FIRST… SKIP或ROWS子句来限制SELECT查询的大量输出。如果查询不是专门设计为报表（需要打印/导出所有记录），通常显示前10-100条记录就足够了。只获取必要的记录。

29. 在带ORDER BY/GROUP BY的SELECT中指定更少的列

在带ORDER BY/GROUP BY的查询中，减少SELECT部分（即要显示的字段）和ORDER BY子句中的列数及其总宽度。Firebird会将SELECT和ORDER BY/GROUP BY子句中的列合并，并在内存中排序（如果内存不足，则在磁盘上排序）。因此，如果SELECT中有长VARCHAR字段，排序文件的大小可能会非常大（数GB）。只减少必须排序的字段数量，并在后期连接大字段进行显示，可以大幅（3-10倍）提高带ORDER BY/GROUP BY查询的速度。

30. 使用派生表优化带ORDER BY/GROUP BY的SELECT

另一种优化带排序的SQL查询的方法是使用派生表来避免不必要的排序操作。不要使用：

SELECT FIELD_KEY, FIELD1, FIELD2, ... FIELD_NFROM TORDER BY FIELD2

而使用以下修改版本：

SELECT T.FIELD_KEY, T.FIELD1, T.FIELD2, ... T.FIELD_NFROM (SELECT FIELD_KEY FROM T ORDER BY FIELD2) T2JOIN T ON T.FIELD_KEY = T2.FIELD_KEY

31. 短字符串用VARCHAR存储，长文本用BLOB存储

存储短字符数据时使用VARCHAR，存储长文本时使用BLOB。VARCHAR对于小块数据更快，因为它们存储在记录中，整个记录在同一个IO周期内被读取，如果记录大小小于数据库页面大小的2/3，整个记录存储在同一数据库页面上。BLOB存储在记录之外，需要额外的IO轮次来读取，但在读写长字符串时它们显示出优势。

32. 从大型SELECT中排除BLOB列

从大型SELECT中排除BLOB列。使用带有子查询的后期绑定方式来有选择地显示BLOB中的信息（例如，显示文档的内容）。

33. 使用BIGINT作为主键和唯一键

对自增主键、唯一键以及所有类型的标识符使用BIGINT类型。BIGINT操作速度最快，且BIGINT有足够的容量来存储几乎所有数据范围。

34. 不要使用VARCHAR作为键

除非确实必要，否则不要使用VARCHAR作为标识符–对它们的操作远不如整型列高效。尤其要避免使用GUID作为标识符–由于GUID值的随机分布，使用GUID作为主键/唯一键的INSERT/UPDATE操作可能比使用整数慢20倍。

35. 重新计算索引统计信息

定期重新计算索引统计信息。使用 SET STATISTICS 命令更新频繁或大量变更的表的索引统计信息，这能让Firebird优化器选择更好的SQL执行计划。HQbird Firebird DataGuard可以按照所需的时间表（通常每周一次）自动执行此类索引统计信息的重新计算。

36. 使用连接池

如果到Firebird数据库的连接是短连接（这在网站中很典型），请使用连接池–例如，在PHP中使用 ibase_pconnect 函数而不是 ibase_connect。

37. 在Firebird 3.0中使用LINGER选项

如果数据库连接是短连接且使用Firebird 3+，请使用LINGER选项在指定时间内保持缓存活跃，这样即使没有其他连接，也能将常用页面保留在缓存中。例如，ALTER DATABASE SET LINGER TO 60 将在最后一个连接结束后保持缓存60秒。

38. 使用HASH JOIN

在Firebird 3.0中，当连接大表和小表时，HASH JOIN可能比使用索引的“嵌套循环”普通连接快得多。要让Firebird优化器使用HASH连接，请在连接条件中使用+0：T1 JOIN T2 ON T1.FIELD1+0 = T2.FIELD2+0。在投入生产环境之前，请检查优化的结果！

39. 将适当的PSQL函数标记为DETERMINISTIC

将没有参数且返回常量值的PSQL函数（Firebird 3+）用关键字DETERMINISTIC标记。确定性函数会在当前查询范围内被计算并缓存。

40. 在Firebird 3.0中使用分析（窗口）函数

如果在运行SELECT时同时输出某个列及其聚合函数，请使用窗口（分析）函数–这比子查询或两个查询更快。例如：

Select id, department, salary, salary / (select sum(salary) from employee) percentagefrom employee

替换为：

Select id, department, salary, salary / sum(salary) OVER () percentage from employee

41. 为gbak使用-se开关

使用 -se 开关可将gbak备份和/或恢复速度提高最多20%，例如：

gbak -b -g -se service_mgr c:\db\data.fdb e:\backup\data.fbk

42. WHERE CURRENT OF

在PSQL中处理游标获取的记录最快的方式是使用 where current of <> 子句。它比 where rb$db_key = :v_db_key 更快，比使用主键或唯一键搜索快得多。

43. 避免频繁查询监控表

不要过于频繁地运行对Firebird监控表（MON$）的查询–此类查询会消耗大量资源，并可能大幅降低主要业务逻辑的性能。我们建议MON$查询的运行频率不超过每分钟一次。如需持续监控Firebird查询/事务/连接，请使用支持Trace API的HQbird PerfMon工具（详见HQbird用户指南第66页）。

44. 批量插入/更新时使用NO_AUTO_UNDO选项

如果在同一事务框架内运行大量DML（更新/插入/删除）命令，Firebird会将每个命令的撤销日志与事务的撤销日志合并。为了加速批量DML操作，请使用「NO AUTO UNDO」选项启动事务，以避免将每个命令的撤销日志与事务的撤销日志合并。

45. 在Firebird 3中如非必要不要使用SRP认证

如果并非真正需要，请不要使用SRP用户认证（Firebird 3.0+）–使用SRP认证建立连接比常规连接更慢。

总结替代方案

性能优化需要考虑多种因素，并且可能非常棘手。如果您已尝试了上述所有方法，可以考虑聘请专业的数据库性能优化服务。

联系我们

您有任何问题吗？请随时通过电子邮件联系我们！