sql - 字符串列的索引

Question

比如说，我有一个表 ResidentInfo，它在列 HomeAddress 上有一个唯一约束，它是 VARCHAR 类型。

我打算在此列上添加索引。查询将只有操作 =。我将使用 B-TREE 索引，因为目前不推荐使用 Hash 索引。

问题:
为了这个 B-TREE 索引的效率，我是否应该添加一个新的列，其中数字 1,2,3.....,N 对应于不同的家庭地址，并索引该数字？

我问这个问题是因为我不知道索引是如何工作的。

Answer 1

对于简单的相等性检查 (=)，varchar 或 text 列上的 B-Tree 索引很简单，也是最佳选择。它肯定对性能有很大帮助。并且 UNIQUE 约束(如您所提到的)已经使用这样的索引实现，因此您不会再创建一个。

当然，简单 integer 上的 B-Tree 索引性能更好。对于初学者来说，比较简单的 integer 值要快一些。但更重要的是，性能也是索引大小的函数。更大的列意味着每个数据页的行数更少，意味着必须读取更多的页面......

因为 HomeAddress 无论如何都不是唯一的，所以它不是一个好的自然主键。我强烈建议改用代理主键。 serial column or IDENTITY in Postgres 10+是显而易见的选择。它的唯一目的是提供一个简单、快速的主键。

如果您有其他表引用该表，这将变得更加高效。无需为外键列复制冗长的字符串，您只需要 4 个字节用于整数列。而且您不需要进行如此多的级联更新，因为地址必然会发生变化，而代理 PK 可以保持不变(当然不一定非要如此)。

您的表格可能如下所示:

CREATE TABLE resident (
   resident_id serial PRIMARY KEY
 , address text NOT NULL
   -- more columns
);

CREATE INDEX resident_adr_idx ON resident(address);

这会产生两个 B-Tree 索引。 resident_id 的唯一索引(实现 PK)和 address 的普通索引。

Postgres 提供了很多选项 - 但对于这个简单的案例，您不需要更多选项。见:

• The manual about indexes