PostgreSQL
上一页		下一页

第七章. 索引和键字

内容
键字
部分索引

索引主要用来提高数据库性能。它们应该定义在那些常用于做重复查询的资格条件的表的列（或者表的字段）上。对索引的不当使用会导致性能的下降，因为更新和插入时间在索引出现时都增加了。

索引还可以用于强制表的主键的唯一性。当定义一个索引为 UNIQUE，那么将不允许多行具有相同的索引字段。这里的目的是保证数据完整性，而不是改善性能，因此上面的关于不当使用的话并不适用。

可以定义两种类型的索引：

对于值索引（value index），索引的键字域声明为字段名；如果索引访问模式支持多字段索引，可以声明多个字段。
对于 函数索引（functional index），它是定义在一个函数的结果上的，这个用户定义函数对某一个表的一个或多个字段进行操作。这是一个单字段索引（也就是说，函数结果），即使该函数使用多于一个输入字段。函数索引可以用于获得对那些基于操作符的数据的快速访问，这些数据通常需要做一些转换，转换成基本数据类型来使用。

Postgres 为从索引提供 btree，rtree 和 hash（散列）访问模式。btree 访问模式是一个 Lehman-Yao 高并发 btrees 的实现。rtree 访问模式用 Guttman 的二分算法实现了标准的 rtrees。hash（散列）访问模式是 Litwin 的线性散列的一个实现。我们单独的列出这些所用的算法是要表明所有这些访问模式都是完全动态的并且不必进行周期性的优化（例如，象静态散列算法常见的那样）。

当一个索引了的字段涉及到使用： <，<=，=，>=，> 之一进行比较时， Postgres 的查询优化器将考虑在扫描中使用 btree 索引。

当一个索引了的字段涉及到使用： <<，&<，&>，>>，@，~=，&& 之一进行比较时，Postgres 的查询优化器将考虑在扫描中使用 rtree 索引。

当一个索引了的字段涉及到使用 = 进行比较时，Postgres 的查询优化器将考虑在扫描中使用散列（hash）索引。

目前，只有 btree 访问模式支持多字段索引。缺省最多可以声明 16 的关键字（这个限制可以在制作 Postgres 时改变）。

可以为某个索引的每个字段声明一个操作符表（operator class）。这个操作符表标识该索引用于该字段要使用的操作符。例如，一个在4字节整数上的 btree 索引将使用 int4_ops 表；这个操作符表包括用于4字节整数的比较函数。实际上，该字段类型的缺省操作符通常就足够了。拥有操作符表的原因是：对于某些数据类型，可能存在多于一个有意义的顺序。例如，我们可能想排序两个复数，既可能通过绝对值，也可能通过实数部分。我们可以通过为该数据类型定义两个操作符表，然后在建立索引时选择合适的一个来实现这个目的。同样还有一些有特殊用途的操作符表：

操作符表 box_ops 和 bigbox_ops 都支持对 box 数据类型的 rtree 索引。两者的区别是 bigbox_ops 把方形的坐标按比例缩小，以避免在对非常大的浮点数坐标做乘法，加法和减法时出现浮点例外。如果你的方形所在的范围的大小是 20,000 单位的平面或更大，你应该用 bigbox_ops。
int24_ops 操作符表在为 int2 类型的数据构建索引并且与查询资格条件里的 int4 数据做比较时很有用。类似的，int42_ops 支持对要和查询里的 int2 数据做比较的 int4 数据进行索引。

下面的查询显示所有定义了的操作符表：

SELECT am.amname AS acc_name,
       opc.opcname AS ops_name,
       opr.oprname AS ops_comp
    FROM pg_am am, pg_amop amop,
         pg_opclass opc, pg_operator opr
    WHERE amop.amopid = am.oid AND
          amop.amopclaid = opc.oid AND
          amop.amopopr = opr.oid
    ORDER BY acc_name, ops_name, ops_comp

使用 DROP INDEX 删除一个索引。

关键字

作者：由 Herouth Maoz 写作。这些最早出现在 1998-03-02 用户邮件列表里关于"主键（PRIMARY KEY）和唯一约束（UNIQUE constraints）有什么异同"问题的解答．

Subject: Re: [QUESTIONS] PRIMARY KEY | UNIQUE

        What's the difference between:（下面两者有何区别？）

              PRIMARY KEY(fields,...) and
              UNIQUE (fields,...)

       - Is this an alias?（这是别名吗？）
       - If PRIMARY KEY is already unique, then why（如果 PRIMARY KEY 已经唯一，）
         is there another kind of key named UNIQUE?（那么为什么有另外一个名为 UNIQUE 类型的键字？）

主键是用于标识某特定行的字段．例如，身份证（社会安全号）标识一个人．

一个简单的由字段组成的 UNIQUE 与行标识毫无关系．它只是一个完整性约束．例如，我收集了一些互联网链接．每一个互联网链接用一个唯一的数字标识，也就是主键，这个键用于表中。

但是，我的应用要求每套集合还要有一个唯一的名称．因为这样做的话一个想更改一个互联网链接集合的"人"就可以找出该集合．毕竟，如果你有两个集合都叫"生命科学"，一个被标识成24433，另一个是29882，而24433那个是你要找的，这样找要比从拥有唯一集合名称的组中找难得多．

所以，用户用名称来选择集合．因此，对这个数据库我们确信名称是唯一的．但是数据库中没有其他表与链接集合表通过链接集合名称相关．因此这样做很效率很底．

另外，尽管是唯一的，组名称实际上并不定义集合！例如，如果某人决定将集合名从"生命科学"改为"生物学"，该集合仍然是同一集合，只是名字不同而已．只要名称仍然是唯一的即可．

所以：

主键：

用于标识某行而且与之相关．
是不可能（或很难）更新．
不应该允许空（NULL）．

唯一域/字段：

用于作为访问某行的可选手段．
只要唯一就可以更新．
可以为空（NULLs）．

那么为什么标准 SQL 语法里没有非唯一键的显式定义呢？这是因为索引是与具体实现相关的．SQL 不定义实现，只定义数据库内数据间的关系， Postgres的确允许非唯一索引，但是用于增强 SQL 键字的索引总是唯一的．

因此，你可以通过任何表中的列的组合来查询该表，不管你是否在这些列上建立了索引．索引只是每个 RDBMS 的实现提供给你的一个工具，以便令常用的查询可以更有效的进行．有些 RDBMS 可能会给你另外一些工具，例如在主存里保留一个键值．它们有一些特殊的命令，例如

CREATE MEMSTORE ON <table> COLUMNS <cols>

（这不是一个实际的命令,只是一个例子）．

实际上，当你创建一个主键或一个域/字段的唯一组合时，在SQL 声明中没有任何地方提到创建了索引，用该键对数据的检索也不会比一次顺序搜索更高效！

所以，如果你想用一个并不唯一的字段组合作为第二个键字，你实际上不用声明任何东西-只要用那个组合检索就行了．但是，如果你想令检索更高效一些，你就不得不依赖你的RDBMS 提供者给你的工具－不管是索引还是我想象的那种 MEMSTORE 命令或是一个智能的 RDBMS，它在你还没有意识到你常用一套特定的键字组合来查询时就给你创建了索引... （它从经验中学来）。

上一页	首页	下一页
UNION 查询	开头	部分索引