PostgreSQL
上一页		下一页

第七章. 索引和键字

内容
键字
部分索引

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

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

一是 值索引（value index），索引的键字域声明为字段名；一个字段可能还有一个关联的操作符表。一个操作符表用于声明该操作符被用于某一指定索引。例如，对四字节整数的 btree 索引会使用int4_ops 表；这个操作符表包括用于四字节整数的比较函数。缺省操作符表是用于该字段类型的合适的操作符表。
一是 函数索引（functional index），它是定义在一个用户定义函数的结果上的，这个用户定义函数对某一个表的一个或多个字段进行操作。这些函数索引可以用于获得对那些基于操作符的数据的快速访问，这些数据通常需要做一些转换，转换成基本数据类型来使用。

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

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

Postgres 里两种 box （方形）表都支持对 box 数据类型的索引。两者的区别是 bigbox_ops 把方形的坐标按比例缩小，以避免在对非常大的浮点数坐标做乘法，加法和减法时出现浮点例外。如果你的方形所在的范围的大小是 20,000 单位的平面或更大，你应该用 bigbox_ops。poly_ops 操作符表支持 polygon 类型数据的 rtree 索引。

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

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

目前，只有 BTREE 访问模式支持多字段索引。最多可以声明 7 的关键字。

使用 DROP INDEX 删除一个索引。

int24_ops 操作符表在为 int2 类型的数据构建索引并且与查询资格条件里的 int4 数据做比较时很有用。类似的，int42_ops 支持对要和查询里的 int2 数据做比较的 int4 数据进行索引。

下面的选择列表返回所有 ops_names：

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

关键字

作者：由 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
         is there another kind of key named UNIQUE?

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

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

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

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

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

所以：

主键：
- 用于标识某行而且与之相关．
- 是不可能（或很难）更新．
- 不应该允许空（NULL）．
唯一域/字段：
- 用于作为访问某行的可选手段．
- 只要唯一就可以更新．
- 可以为空（NULLs）．

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

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

CREATE MEMSTORE ON <table> COLUMNS <cols>

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

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

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

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