sql - 简单的 Firebird 查询非常慢

Question

我有一个包含大约 246k 记录的表。它大约有 25 列，除了一个小 blob 之外都是整数。

如果我查询表的所有字段

select a.recordid, a.editcount, ect.. from ARTrans a

它在一秒钟内执行。但如果我只包含记录 ID

select a.recordID from ARTrans a

执行需要 20 多秒。大部分时间都花在计划上(自然)，这看起来很奇怪，因为在大多数情况下，我在 recordid 上有一个索引。

我已经进行了垃圾收集、重新创建索引、删除索引、仅在 RecordID 上添加了一个索引，但速度仍然非常慢。

任何帮助将不胜感激。

编辑以提供更多信息:

Firebird :2.5.3.26778

fbclient.dll:2.5.1.26351

数据库中没有其他人，我已将其移至本地。

这是表定义

CREATE TABLE ARTRANS
(
  RECORDID Integer NOT NULL,
  EDITCOUNT Smallint,
  CLASSIFICATION Smallint,
  TRANSID Integer,
  DATEENTERED Integer,
  CLIENTID Integer,
  TRANSTYPE Smallint,
  BILLED Smallint,
  FINALIZEID Smallint,
  INVOICEID Integer,
  INVOICENUM Integer,
  INVOICEDATE Integer,
  GROUPID Smallint,
  EXPORTED Char(1),
  TRANSVALUE Decimal(18,4),
  DESCRIPTION Blob sub_type 0,
  POSTPERIOD Smallint,
  LINKEDTRANSID Integer,
  LINKEDINVID Integer,
  LINKEDFUNDSID Integer,
  INFOONLY Smallint,
  NEEDTRANSFER Char(1),
  DESTTRANSID Integer,
  LSTTKREDIT Integer,
  SPELLNGRAMMARCHECKSTATUS Smallint
);

索引

CREATE UNIQUE INDEX IDX_ARTRANSRecID ON ARTRANS (RECORDID);

SQL语句:

SELECT a.RECORDID FROM ARTRANS a

计划(从 Flamerobin 输出)

Preparing query: 
SELECT a.RECORDID 
FROM ARTRANS a
Prepare time: 20.008s
Field #01: ARTRANS.RECORDID Alias:RECORDID Type:INTEGER
PLAN (A NATURAL)


Executing...
Done.
13257 fetches, 0 marks, 76 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 0 index, 6552 seq.
Delta memory: -19204 bytes.
Total execution time: 20.025s
Script execution finished.

这个 SQL 语句运行良好:

Preparing query: 
SELECT a.RECORDID, a.EDITCOUNT, a.CLASSIFICATION,
a.TRANSID, a.DATEENTERED, a.CLIENTID, a.TRANSTYPE, a.BILLED, a.FINALIZEID,
a.INVOICEID, a.INVOICENUM, a.INVOICEDATE, a.GROUPID, a.EXPORTED, 
a.TRANSVALUE, a.DESCRIPTION, a.POSTPERIOD, a.LINKEDTRANSID, a.LINKEDINVID, 
a.LINKEDFUNDSID, a.INFOONLY, a.NEEDTRANSFER, a.DESTTRANSID, a.LSTTKREDIT, 
a.SPELLNGRAMMARCHECKSTATUS, a.RDB$DB_KEY
FROM ARTRANS a

Prepare time: 0.013s
Field #01: ARTRANS.RECORDID Alias:RECORDID Type:INTEGER
Field #02: ARTRANS.EDITCOUNT Alias:EDITCOUNT Type:SMALLINT
Field #03: ARTRANS.CLASSIFICATION Alias:CLASSIFICATION Type:SMALLINT
Field #04: ARTRANS.TRANSID Alias:TRANSID Type:INTEGER
Field #05: ARTRANS.DATEENTERED Alias:DATEENTERED Type:INTEGER
Field #06: ARTRANS.CLIENTID Alias:CLIENTID Type:INTEGER
Field #07: ARTRANS.TRANSTYPE Alias:TRANSTYPE Type:SMALLINT
Field #08: ARTRANS.BILLED Alias:BILLED Type:SMALLINT
Field #09: ARTRANS.FINALIZEID Alias:FINALIZEID Type:SMALLINT
Field #10: ARTRANS.INVOICEID Alias:INVOICEID Type:INTEGER
Field #11: ARTRANS.INVOICENUM Alias:INVOICENUM Type:INTEGER
Field #12: ARTRANS.INVOICEDATE Alias:INVOICEDATE Type:INTEGER
Field #13: ARTRANS.GROUPID Alias:GROUPID Type:SMALLINT
Field #14: ARTRANS.EXPORTED Alias:EXPORTED Type:STRING(1)
Field #15: ARTRANS.TRANSVALUE Alias:TRANSVALUE Type:NUMERIC(18,4)
Field #16: ARTRANS.DESCRIPTION Alias:DESCRIPTION Type:BLOB SUB_TYPE 0
Field #17: ARTRANS.POSTPERIOD Alias:POSTPERIOD Type:SMALLINT
Field #18: ARTRANS.LINKEDTRANSID Alias:LINKEDTRANSID Type:INTEGER
Field #19: ARTRANS.LINKEDINVID Alias:LINKEDINVID Type:INTEGER
Field #20: ARTRANS.LINKEDFUNDSID Alias:LINKEDFUNDSID Type:INTEGER
Field #21: ARTRANS.INFOONLY Alias:INFOONLY Type:SMALLINT
Field #22: ARTRANS.NEEDTRANSFER Alias:NEEDTRANSFER Type:STRING(1)
Field #23: ARTRANS.DESTTRANSID Alias:DESTTRANSID Type:INTEGER
Field #24: ARTRANS.LSTTKREDIT Alias:LSTTKREDIT Type:INTEGER
Field #25: ARTRANS.SPELLNGRAMMARCHECKSTATUS Alias:SPELLNGRAMMARCHECKSTATUS Type:SMALLINT
Field #26: ARTRANS.DB_KEY Alias:DB_KEY Type:STRING(8)
PLAN (A NATURAL)


Executing...
Done.
1135 fetches, 0 marks, 7 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 0 index, 560 seq.
Delta memory: 25852 bytes.
Total execution time: 0.047s
Script execution finished.

另外，我应该补充一下，有 246804 条记录，花了将近一分钟才得到计数。

Preparing query: SELECT count(*) FROM ARTRANS a
Prepare time: 52.614s
Field #01: .COUNT Alias:COUNT Type:INTEGER
PLAN (A NATURAL)


Executing...
Done.
499643 fetches, 0 marks, 3016 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 0 index, 246804 seq.
Delta memory: -18576 bytes.
Total execution time: 52.716s
Script execution finished.

更新

如果我删除 blob 列，性能就会恢复。如果我保留它并将所有值设为 NULL，性能仍然很快。如果我更新每条记录上的 blob 字段以包含 20 字节的流，则执行简单查询的性能将恢复到 20 秒以上

select a.RecordID from ARTrans a

我更进一步，删除了除 blob 字段和 recordID 之外的所有列，但速度仍然很慢。好像以前就见过这样的情况。很奇怪。

Answer 1

为了提高性能，请创建仅包含 id 和 blob 归档的单独表，并且仅当您需要此 blob 归档时才将其与您的表连接。如果您需要计算行数，请使用 count(RECORDID)(因为它已索引并且所有行都不为空)。