如何在firebird中创建索引以改进选择查询 [英] How to create index in firebird for improvement of select query

问题描述

我是使用火鸟的新手。我有3张桌子。

I am newbie in using firebird. I have 3 tables.

T_TABLE1 结构如下所示：

T_TABLE2 结构如下所示：

和 T_TABLE3 结构如下：

主键是 ID 从 T_TABLE1 到 TBL1_ID T_TABEL2 ， TBL1_ID T_TABLE3 。如何选择3个表的数据连接，我想使用索引进行最佳选择查询，但我不知道如何创建索引，因为我是使用firebird的新手，我想了解更多在使用火鸟。希望我的解释清楚。

the primary key is the ID from T_TABLE1 to TBL1_ID in T_TABEL2 and TBL1_ID in T_TABLE3. How can I select data join with that 3 tables and I want to use index for best select query but I don't know how to create index because I am newbie in using firebird and I want to learn more in using firebird. Hope my explanation is clear.

推荐答案

创建索引的语法记录在Firebird 2.5语言参考中， CREATE INDEX ，它还包含其他信息。

The syntax for creating an index is documented in The Firebird 2.5 Language Reference, CREATE INDEX, it also contains additional information.

创建索引的语法是：

CREATE [UNIQUE] [ASC[ENDING] | [DESC[ENDING]] INDEX indexname
   ON tablename
   { (<col> [, <col> ...]) | COMPUTED BY (expression) }

<col>  ::=  a column not of type ARRAY, BLOB or COMPUTED BY

因此创建（升序）你会做的 T_TABEL2.TBL1_ID 的索引：

So for creating an (ascending) index on T_TABEL2.TBL1_ID you would do:

CREATE INDEX ix_tabel2_tbl1_id ON T_TABEL2 (TBL1_ID)

但正如我今天早些时候评论的那样，如果有外键，则没有必要此列，因为Firebird中的外键自动获取索引（同样适用于主键）。

But as I commented earlier today that is not necessary if there is a foreign key on this column, as foreign keys in Firebird automatically get an index (the same applies to primary keys).

你的问题不清楚你是否有外键，但我建议你创建它们而不是索引：它给你索引和顶部这样做会强制 TBL1_ID 的值实际存在于 T_TABLE1 中。

It is not clear from your question if you have foreign keys, but I'd advise you to create them instead of an index: it gives you the index and on top of that enforces that the value of TBL1_ID actually exists in T_TABLE1.

请记住，创建索引不会自动提高性能。例如，优化器可能会决定使用索引是不值得的（或者特定索引与查询无关）。

Just keep in mind that creating an index does not automatically improve performance. The optimizer for example might decide that using an index is not worth the effort (or that a specific index is not relevant for the query).

这篇关于如何在firebird中创建索引以改进选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！