Firebird数据库：OCTET与CHAR类型主键的查询性能对比及选型咨询

数据老陈

2026-6-30

平台信息

服务器名称：localhost/3050
服务器版本：WI-V3.0.7.33374 Firebird 3.0
服务器实现：Firebird/Windows/AMD/Intel/x64
服务版本：2

问题背景与测试情况

之前一直听说记录标识符用整数类型查询和连接性能最优，所以早年用BIGINT做主键，但生产数据迁移时出了大问题——合并3个不停机的生产库，各库用每秒生成的顺序整数标识关联，导致“动态缺口”问题，详情在我亚马逊的书中有写，这里不多赘述。

后来改用非顺序唯一主键，选了GUID：CHAR(36) CHARACTER SET ASCII，插入时通过= UUID_TO_CHAR(GEN_UUID())赋值。当年测试时性能和整数相当，就没在意，结果数据量上来后性能越来越慢，最后不得不重写应用。

现在新项目里了解到，非顺序唯一标识+高性能的方案是用OCTET类型，插入时直接用GEN_UUID()赋值。我建了一张500万条记录的测试表，字段包括：

MY_BIGINT BIGINT（设为主键）
MY_GUID CHAR(36) CHARACTER SET ASCII（设唯一约束）
MY_UUID CHAR(16) CHARACTER SET OCTETS（设唯一约束）

我选了表末尾附近的一条记录，分别用三个字段做条件执行SELECT ... WHERE查询单条记录，结果三种类型的查询响应时间完全相同，执行计划步骤数也一致，这和我查的资料、过往经验都不符。

想请教有实际使用这些类型做主键经验的人：哪种字段类型在查询和连接时性能最优？因为不能用BIGINT或INTEGER，应该选VARCHAR(16) OCTET还是CHAR(36) CHARACTER SET ASCII类型的GUID？

gstat工具执行结果

gstat -u sysdba -p masterkey -a -t TBL1 "D:\app_vlt\db\fdb\SPEED_TEST.FDB"

gstat results of table:


Database "D:\APP_VLT\DB\FDB\SPEED_TEST.FDB"
Gstat execution time Sun Feb 11 07:17:39 2024

Database header page information:
    Flags           0
    Generation      821
    System Change Number    0
    Page size       16384
    ODS version     12.0
    Oldest transaction  710
    Oldest active       711
    Oldest snapshot     711
    Next transaction    711
    Sequence number     0
    Next attachment ID  75
    Implementation      HW=AMD/Intel/x64 little-endian OS=Windows CC=MSVC
    Shadow count        0
    Page buffers        256
    Next header page    0
    Database dialect    3
    Creation date       Feb 10, 2024 10:56:30
    Attributes      force write

    Variable header data:
    Sweep interval:     20000
    *END*


Database file sequence:
File D:\APP_VLT\DB\FDB\SPEED_TEST.FDB is the only file

Analyzing database pages ...
TBL1 (128)
    Primary pointer page: 167, Index root page: 168
    Pointer pages: 21, data page slots: 67208
    Data pages: 67208, average fill: 77%
    Primary pages: 67208, secondary pages: 0, swept pages: 0
    Empty pages: 6, full pages: 67201
    Fill distribution:
     0 - 19% = 6
    20 - 39% = 1
    40 - 59% = 0
    60 - 79% = 67201
    80 - 99% = 0

    Index PK_TBL1_UUID (0)
    Root page: 5241, depth: 3, leaf buckets: 17496, nodes: 10000000
    Average node length: 19.75, total dup: 0, max dup: 0
    Average key length: 16.76, compression ratio: 0.95
    Average prefix length: 2.24, average data length: 13.76
    Clustering factor: 9999855, ratio: 1.00
    Fill distribution:
         0 - 19% = 40
        20 - 39% = 0
        40 - 59% = 5246
        60 - 79% = 7738
        80 - 99% = 4472

    Index UNQ_MY_BIGINT (1)
    Root page: 19342, depth: 3, leaf buckets: 6930, nodes: 10000000
    Average node length: 11.22, total dup: 0, max dup: 0
    Average key length: 8.22, compression ratio: 1.09
    Average prefix length: 3.78, average data length: 5.22
    Clustering factor: 67202, ratio: 0.01
    Fill distribution:
         0 - 19% = 0
        20 - 39% = 0
        40 - 59% = 0
        60 - 79% = 1
        80 - 99% = 6929

    Index UNQ_MY_GUID (2)
    Root page: 1614, depth: 3, leaf buckets: 32999, nodes: 10000000
    Average node length: 36.85, total dup: 0, max dup: 0
    Average key length: 33.86, compression ratio: 1.06
    Average prefix length: 5.14, average data length: 30.86
    Clustering factor: 9999857, ratio: 1.00
    Fill distribution:
         0 - 19% = 144
        20 - 39% = 1
        40 - 59% = 10726
        60 - 79% = 13874
        80 - 99% = 8254

Gstat completion time Sun Feb 11 07:17:42 2024

测试用SQL语句

/* Each select is run in it's own connection to the database */
SELECT
  MY_BIGINT, MY_GUID, UUID_TO_CHAR(MY_UUID) AS "MY_UUID"
FROM
  TBL1
WHERE
  MY_BIGINT = 12999998
--
SELECT
  MY_BIGINT, MY_GUID, UUID_TO_CHAR(MY_UUID) AS "MY_UUID"
FROM
  TBL1
WHERE
  MY_GUID = '38DD0E53-A80D-4E0A-976E-7AA6C80C11A8'
--
SELECT
  MY_BIGINT, MY_GUID, UUID_TO_CHAR(MY_UUID) AS "MY_UUID"
FROM
  TBL1
WHERE
  MY_UUID = CHAR_TO_UUID('6B81D7EC-BACE-4C3D-963F-CF251ED0202C')
---

内容的提问来源于stack exchange，提问作者user19251612