postgre索引

PostgreSQL 第八章 索引  

  索引是提高数据库查询性能的常用方法。有了索引数据库在执行查询时就能快速地找到表中特定的数据行。但是索引会增加更新操作的执行成本，因为索引中的数据要和表中的数据保持一致，更新表中的数据时，数据库会自动更新索引中的数据。
8.1 概述

  假如数据库中有下面的一张表∶

CREATE TABLE test1 (

  id integer,

  content varchar

);

  应用程序会大量地执行下面的查询，这个查询只会返回零个或几个数据行作为结果：

SELECT content FROM test1 WHERE id = constant;

  如果在列id上没有建立任何索引，数据库在执行上面的查询是将不得不扫描表test1中所有的数据行来查找匹配WHERE子句中的条件的记录。如果表test1中执有几百万条记录，查询的执行时间将会非常地长。在列id 列上建立一个索引就可以解决这个问题，数据库只需要扫描索引中的几个数据块就能找到需要的表test1中的数据行。

  使用类似下面的命令创建一个索引∶

CREATE INDEX test1_id_index ON test1 (id);

  test1_id_index是索引的名字，可以随意地为索引取名字，但是要符合标识符的命名规范。

  使用DROP INDEX命令来删除一个索引。可以随时在表上增加索引或者从表中删除索引。

  创建了索引以后，数据库会自动维护索引，不需要用户的干预。但应该经常对索引运行ANALYZE命令以更新索引的优化器统计信息。带搜索条件的 UPDATE 和 DELETE 命令也可以使用索引来快速定位需要的数据行。在对表进行连接操作时，如果列是连接条件的一部分，有时这个列上的索引也可以加快连接操作的执行。

  创建了索引以后，索引必须和表中的数据保持同步。这就增加了更新操作执行的成本。应该把那些非关键或者根本用不上的索引删除掉。
 
8.2 索引类型

  PostgreSQL 提供了几种类型索引，分别是B-tree、Hash、GiST和GIN。每种索引类型都使用了不同的算法，只适合适合某些特定的查询类型。在默认的情况下，CREATE INDEX 命令将创建一个 B-tree类型的索引。

  B-tree索引适用于那些可以按照某种顺序排序的数据上的相等和范围查询。当一个列作为某些比较运算符（<、<=、=、>= 和 >）的操作数时， PostgreSQL 的查询优化器会考虑使用该列上定义的 B-tree 索引∶

  谓词BETWEEN和IN，也可以使用B-tree 索引。IS NULL谓词不能使用B-tree索引。

  HASH索引只能被用来处理相等类型的查询，使用下面的命令来创建HASH索引：

CREATE INDEX name ON table USING hash (column);

  注意：测试结果表明，HASH索引的性能不比B-tree索引更好，HASH索引占的存储空间的比B-tree索引大，创建HASH索引需要的时间比创建B-tree索引需要的时间长。而且HASH索引上的操作不会被记录在数据库的重做日志中，在数据库崩溃，重新恢复以后，需要重建所有的HASH索引。所以推荐使用B-tree索引。

  GiST 索引不是某种特定的索引类型，而是一种通用索引基础结构，可以在这种结构上实现很多不同的索引策略。 GIN索引与GiST类似，它和GiST索引主要在文本数据的全文检索中使用。一般情况下，使用B-tree索引即可。

8.3 多列索引

  一个索引可以定义在表中的多个列上。假如数据库中有下面的表∶

CREATE TABLE test2 (
  major int,

  minor int,

  name varchar

);


  应用程序经常在表上做这样的查询：


SELECT name FROM test2 WHERE major = constant AND minor = constant;


  可以在列major 和 minor上建立一个索引来加快查询的执行速度，使用下面的命令来创建多列索引∶


CREATE INDEX test2_mm_idx ON test2 (major, minor);


  当前，只能在多个列上创建B-tree 和 GiST类型的索引。同一个多列索引引用的列的个数不能超过32个。


  只要查询条件引用的列是多列索引引用的列的子集，查询在执行时就可以使用多列索引。


  在大多数情况下，在单个列上创建索引就足够了，它比多列索引节约存储空间。 除非表上有些特定的查询引用了许多个列，否则超过三个列的索引几乎没什么用处。第8.5节详细讨论了单列索引与多列索引的选择策略。

8.4 索引与ORDER BY子句

  除了用于快速查找查询要返回的数据行，索引也可以用于对查询结果进行排序。这样的话，查询的ORDER BY子句在扫描索引时就可以被执行，不用再使用一个额外的排序操作来对查询结果进行排序。当前只有B-tree类型的索引才能被用于对查询结果进行排序。查询优化器在确定查询的执行计划时，会自动决定使用索引对查询结果进行排序还是使用一个排序操作对查询结果进行排序。

  对于需要扫描表中大量的数据的查询来说，使用一个排序操作比使用索引进行排序要更有效。只有在查询只扫描少量的数据行的情况下，使用索引进行排序才更有效。


  在默认的情况下，B-tree索引用升序排列它的索引项，空值排在最后面。

  在创建索引时，可以使用选项ASC、 DESC、 NULLS FIRST和NULLS LAST来改变索引项的排列顺序。例如：


CREATE INDEX test2_info_nulls_low ON test2 (info NULLS FIRST);


CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);

  使用升序存储的索引可以用来求解ORDER BY x ASC NULLS LAST和ORDER BY x DESC NULLS FIRST。使用降序存储的索引可以用来求解ORDER BY x ASC NULLS FIRST 和ORDER BY x DESC NULLS LAST。

8.5 在查询中使用多个索引

  假定在一个表的列a和上定义了一个索引，对于搜索条件类似WHERE a = 5 AND b = 6的查询，只需要对这个索引扫描一次就可以得到需要的数据行。但对于WHERE a = 5 OR b = 6这样的查询，只对索引扫描一次无法得到需要的数据行。

  对于那些只扫描一次索引无法得到结果的查询，PostgresSQL可以通过扫描多个索引（包括对同一索引扫描多次）来求解这些查询。例如，对于类似WHERE x = 42 OR x = 47 OR x = 53 OR x = 99这样的查询，系统会对建在列x上的索引扫描4次，最后将每次扫描的结果合并起来。再看一个例子，假设在列x和y上各创建了一个索引，对于WHERE x = 5 AND y = 6这样的查询，可以对x和y上的索引各扫描一次，然后将两个结果求交集即可。

  可以对表中的多个列建立一个索引，也可以对每个列都建一个单独的索引。如何选择索引类型要看具体的查询类型。例如，假设数据库有一个表中有两个列，应用程序使用的查询有时候引用x，有时候引用y，有时候同时引用x和y，有下面三种建立索引的策略：


（1）在x和y上个创建一个单列索引。当处理同时引用x和y的查询时，可以同时扫描x和y上的索引，然后对两个扫描结果求交集。


（2）可以选择在x 和y上创建一个多列索引，这个索引对于同时引用x和y的查询很有用，它也可以用于只引用x的查询，但它对于只引用y的查询却没有什么用处。


（3）在y上创建一个单列索引，同时在x和y上创建一个多列索引。这种方式创建的索引比较多，要消耗较多的资源维护索引和表中数据的同步。


（4）在x和y上各创建一个单列索引，同时在x和y上创建一个多列索引。这种方式创建的索引过多，要消耗大量资源维护索引和表中数据的同步。

  如果大部分查询只引用x或y，可以选第一种策略。如果大部分查询同时引用x和y，可以选第二或第三种策略。如果表上的更新操作非常少，同时查询有时候引用x，有时候引用y，有时候同时引用x和y，可以选第三种或第四种策略。

8.6 唯一索引


  索引还可以用保证表的某个列或某些列上的值的唯一性，使用下面的命令来创建一个唯一索引。


CREATE UNIQUE INDEX name ON table (column [, ...]);

  当前，只有 B-tree 索引才可以被声明为是唯一的。


  如果在表中个某个列上建立了一个唯一索引，那么表中就不能有两个数据行在这个列上的值相等。如果在表中个某些列上建立了一个唯一索引，那么表中就不能有两个数据行在这些列上的值全部相等。两个空值是不相等的。

  如果一个表上定义了一个唯一约束或者一个主键，那么PostgreSQL自动在那些组成主键的列或者唯一约束引用的列上创建一个唯一索引。所以没有必要对已经定义了唯一约束的列再创建一个唯一索引。
8.7 表达式索引


  不但可以在表的列上直接建索引，还可以在函数或标量表达式上建索引，但函数或标量表达式必须引用表中的一个或多个列。表达式索引索引可以加快某些查询的执行速度。例如，下面的命令在函数lower(col1)上创建了一个表达式索引，col1是表test1中的一个列：

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

  下面的查询在执行时，就可以使用上面创建的索引test1_lower_col1_idx，函数调用lower(col1)的值可以直接从索引中得到，不需要再通过col1的值计算出来：


SELECT * FROM test1 WHERE lower(col1) = 'value';

  通常应该将用于创建索引的表达式用括号括起来，如果表达式只包含一个函数调用，可以省略括号，例如：


CREATE INDEX people_names ON people ((first_name || ' ' || last_name));

  表达式索引的维护成本要比普通的索引要高，因为在更新索引时，不能直接使用列的值来作为索引项，必须计算索引表达式的值来作为索引项。可以在查询速度比更新速度更重要的情况下使用表达式索引。

8.8 部分索引


  部分索引是创建在表中所有的数据行的子集上的索引。数据行的子集通过一个条件表达式来定义（被称为部分索引的谓词）。部分索引中只包含满足谓词的数据行的索引项。部分索引在某些情况下非常地有用。例如，假设一个表有100万条记录，这个表有一个整型的列col_1，有99万条记录在col_1上的取值都是10，同时在col_1上定义了一个索引，如果一个查询要找出所有col_1等于10的数据行，在执行查询时，col_1上的索引就起不到任何的作用。如果只对col_1上的值不等于10的数据行建立索引，索引所占的存储空间会小得多，而且在索引上查找的速度也会更快。此外索引的维护成本也会降低，因为更新那些col_1上的值等于10的数据行时，不需要更新索引。

  下面再看一个实例，假定web服务器的访问日志要存储在数据库中，大部分访问web服务器的客户端的IP地址来本企业内部，剩下的IP地址来自其它地方。如果只想得到企业外部IP地址的信息，创建索引时就没有必要对企业内部的IP地址建立索引项。首先建表:


CREATE TABLE access_log (


  url varchar,


  client_ip inet,


  ...


);


 


  然后使用类似下面的命令建部分索引：


CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)


  WHERE NOT (client_ip > inet '192.168.100.0' AND client_ip < inet '192.168.100.255');


 


  下面的查询就可以使用创建的部分索引：


SELECT * FROM access_log WHERE url = '/index.html' AND client_ip = inet '212.78.10.32';

  下面的查询就无法使用创建的部分索引：


SELECT * FROM access_log WHERE client_ip = inet '192.168.100.23';

  从上面的例子中，可以看出创建部分索引时，需要了解用于创建索引的列的取值分布情况。如果列的取值分布是固定的，那么部分索引只需要创建一次即可。如果列的取值分布是经常变化的，那么就需要经常重新定义部分索引。

  再看一个例子，首先建表：


CREATE TABLE orders (


  order_nr int,


  amount decimal(20,2)


billed boolean


);


 


  然后创建下面的部分索引：


CREATE INDEX orders_unbilled_index ON orders (order_nr)


  WHERE billed is not true;

  下面的查询就可以使用创建的部分索引：


SELECT * FROM orders WHERE billed is not true AND order_nr < 10000;

  下面的查询也可以使用创建的部分索引，虽然索引没有建在列amount上面，需要扫描索引中的每个索引项才能找到需要的数据行：


SELECT * FROM orders WHERE billed is not true AND amount > 5000.00;

  但下面的查询就无法使用创建的部分索引，因为WHERE子句中没有出现条件billed is not true：


SELECT * FROM orders WHERE order_nr = 3501;

  从上面的例子可以看出，只有在系统能判定WHERE子句中的查询条件蕴含部分索引的谓词的条件下才会使用部分索引。这是是一个困难的工作，数据库无法判断任意两个条件表达式在数学上是等价的。一般只有在WHERE子句中的查询条件表达式的某个部分完全匹配部分索引的谓词的条件下，数据库才会考虑使用部分索引。当然，数据库也可以进行一些简单的数学推理，例如，假设部分索引的谓词是“x<2“，如果查询条件是“x<1”，那么数据库可以判定“x<1“蕴含”x<2“，执行查询时会使用这个部分索引。如果WHERE子句中的查询条件含有参数，则查询在执行时不会使用部分索引。

  也可以建立UNIQUE类型的部分索引，这样表中的一部分数据行内部满足唯一约束，表中的另一部分数据行内部不满足唯一约束，例如，首先建表：


CREATE TABLE tests (


  subject text,


  target text,


  success boolean,


  ...


);


 


  然后在表上建一个唯一类型的部分索引：


CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)


  WHERE success;

  在这个例子中，所有列success的值为真的数据行在列(subject, target)上的值必须是唯一的。

  使用部分索引需要了解优化器是如何工作的，尤其是那些优化器是如何使用索引来执行查询的知识。需要大量的经验才能真正理解优化器的工作原理。在大部分的情况下，没有必要使用部分索引，使用普通的索引就足够了。