PostgreSQL7.0手册-用户手册-7. 索引和键字

2019-09-08 23:33:13

字体：大中小

来源：转载

供稿：网友

第七章.　索引和键字

键字　
部分索引　

索引主要用来提高数据库性能。它们应该定义在那些常用于做重复查询的资格条件的表的列（或者表的字段）上。对索引的不当使用会导致性能的下降，因为更新和插入时间在索引出现时都增加了。　
索引还可以用于强制表的主键的唯一性。当定义一个索引为　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　　COLUMNS　

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

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

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

部分索引
作者：这是从　Paul　M.　Aoki　1998-08-11的在　e-mail　列表中回答的问题中摘下来的。
部分索引（partial　index）是建立在一个表的子集上的索引；该子集是用断言（谓语）定义的。Postgres支持带有任意断言（谓语）的部分索引。我相信　IBM　用于　as/400　的　DB2　用单一子句的断言支持部分索引。　
部分索引的主要动机是：如果你发出的所有查询都可以从落在某个范围内的索引中获益，我们为什么要对整个表建立一个索引，而带来相关的更多的空间/时间开销呢？（还有其他原因；参阅　Stonebraker,　M,　1989b　获取更多细节。）　

创建，更新和查询部分索引的机制不算太差。麻烦的部分是索引的选择（我该建立哪个索引？）和查询优化（我该使用哪个索引？）；也就是说，涉及到决定什么样的断言可以有效地匹配工作负荷/查询的那部分。对那些深入数据库理论的人而言，这个问题基本上类似于对应的物化的视图问题，虽然有不同的开销参数和公式。通常而言，这些问题对标准的SQL　类型是很难的问题；对黑盒扩展类型而言，这些问题是超级难的问题，因为选择性评估理论太原始了。　

参考　Stonebraker,　M,　1989b，Olson,　1993　和　Seshardri,　1995　获取更多信息。　

------------------------------------------------------------------

上一篇：PostgreSQL7.0手册-用户手册-8. 数组

下一篇：PostgreSQL7.0手册-用户手册-19. SQL命令-LOCK