postgres 数据库的索引知识整理

postgres 数据库的索引知识整理。

前言

翻资料到处抄一抄，整理一下关于数据库的索引，这是因为在数据库查询的过程中总离不开与索引打交道。
主要还是抄官网http://www.postgres.cn/docs/12/indexes.html O(∩_∩)O

一、什么是索引

就是类比一本书籍里的目录。感兴趣的读者可以相对快地扫描索引并跳到合适的页而不需要阅读整本书来寻找感兴趣的材料。
在没有事前准备的情况下，系统不得不扫描整个test1表，一行一行地去找到所有匹配的项，这张很耗时间，但是如果系统被指示维护一个在id列上的索引，它就能使用一种更有效的方式来定位匹配行。

二、创建索引

2.1普通的索引创建方式

这种单索引的方式很常见也常用，

CREATE INDEX table_column_name_index ON tablename (column_name);

2.2组合索引

这种索引创建方式常用在多条件查询的i情况下，并且column_name1出现的频率比columname_2高，就将高频率的索引放到前面。

CREATE INDEX table_column_name_index ON tablename (column_name1，columname_2);

2.3唯一索引

索引也可以被用来强制列值的唯一性，或者是多个列组合值的唯一性。

CREATE unique INDEX table_column_name_index ON tablename (column_name1，columname_2);

2.4部分索引

使用部分索引的一个主要原因是避免索引公值。由于搜索一个公值的查询（一个在所有表行中占比超过一定百分比的值）不会使用索引，所以完全没有理由将这些行保留在索引中。这可以减小索引的尺寸，同时也将加速使用索引的查询。它也将加速很多表更新操作，因为这种索引并不需要在所有情况下都被更新

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');

三.查询某张表中有哪些索引

知道哪张表有哪些索引，一个是看sql的建表文件，一个是直接数据库查询。能一下子查询到谁还看文件O(∩_∩)O

select * from pg_indexes where tablename='table_name'

四.删除索引

每个数据库都用这么一张表 pg_indexes进行维护索引

drop index employee_name_addr_index

五.索引带来的好处

1.外索引会使带有搜索条件的UPDATE和DELETE命令受益。
2.索引还可以在连接搜索中使用，一个定义在连接条件列上的索引可以显著地提高连接查询的速度。

六.索引的缺点

1.一个索引被创建后，系统必须保持它与表同步。这增加了数据操作的负担。因此哪些很少或从不在查询中使用的索引应该被移除。
2.在一个大表上创建一个索引会耗费很长的时间。默认情况下，PostgreSQL允许在索引创建时并行地进行读（SELECT命令），但写（INSERT、UPDATE和DELETE）则会被阻塞直到索引创建完成。在生产环境中这通常是不可接受的。

七索引的类型

PostgreSQL提供了多种索引类型： B-tree、Hash、GiST、SP-GiST 、GIN 和 BRIN。每一种索引类型使用了 一种不同的算法来适应不同类型的查询。默认情况下， CREATE INDEX命令创建适合于大部分情况的B-tree 索引。

7.1B-tree索引

B-tree可以在可排序数据上的处理等值和范围查询，如<,<=,=,>=,>,BETWEEN和IN,IS NULL或IS NOT NULL都可以在可以在B-tree索引中使用。 对于like的查询方式，只有开头为常量的才能利用到索引。

7.2 Hash索引

Hash索引只能处理简单等值比较

创建方式

CREATE INDEX name ON table USING HASH (column);

7.3 GiST索引

GiST索引并不是一种单独的索引，而是可以用于实现很多不同索引策略的基础设施.。

7.4 BRIN 索引索引

块范围索引的缩写）存储有关存放在一个表的连续物理块范围上的值摘要信息。与 GiST、SP-GiST 和 GIN 相似，BRIN 可以支持很多种不同的索引策略，并且可以与一个 BRIN 索引配合使用的特定操作符取决于索引策略。对于具有线性排序顺序的数据类型，被索引的数据对应于每个块范围的列中值的最小值和最大值，使用这些操作符来支持用到索引的查询：<，<=，=，>=，>。

八. B-tree中的多列索引的查询。

一个B-tree索引可以用于条件中涉及到任意索引列子集的查询，但是当先导列（即最左边的那些列）上有约束条件时索引最为有效。确切的规则是：在先导列上的等值约束，加上第一个无等值约束的列上的不等值约束，将被用于限制索引被扫描的部分。在这些列右边的列上的约束将在索引中被检查，这样它们适当节约了对表的访问，但它们并未减小索引被扫描的部分。

当使用多列索引的时候

1.要使索引起作用，查询条件中的列必须要使用适合于索引类型的操作符，使用其他操作符的子句将不会被考虑使用索引。
2.多列索引应该较少地使用。在绝大多数情况下，单列索引就足够了且能节约时间和空间。具有超过三个列的索引不太有用，除非该表的使用是极端程式化的。

九. 索引和ORDER BY

规划器会考虑以两种方式来满足一个ORDER BY说明：扫描一个符合说明的可用索引，或者先以物理顺序扫描表然后再显式排序。对于一个需要扫描表的大部分的查询，一个显式的排序很可能比使用一个索引更快，因为其顺序访问模式使得它所需要的磁盘I/O更少。只有在少数行需要被取出时，索引才会更有用。一种重要的特殊情况是ORDER BY与LIMIT n联合使用：一个显式的排序将会处理所有的数据来确定最前面的n行，但如果有一个符合ORDER BY的索引，前n行将会被直接获取且根本不需要扫描剩下的数据。

创建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);

十. 只用索引的扫描和覆盖索引

PostgreSQL中的所有索引是二级索引,这意味着每个索引都是与表的主数据区（在PostgreSQL术语称为表的堆中）分开存储。这意味着在普通索引扫描中，每行检索都需要从索引和堆中取数据。 此外，虽然匹配给定的可索引WHERE条件的索引条目通常在一起靠近存储，但它们引用的表行可能在堆中的任何地方。 因此索引扫描的堆访问部分涉及到对堆的大量随机访问，这可能很慢，特别是在传统旋转媒介上。

10.1只用索引的扫描的条件

为了解决这种性能问题，PostgreSQL支持只用索引的扫描，这类扫描可以仅用一个索引来回答查询而不产生任何堆访问，
在使用这种方法时有两个根本的限制：

1.查询必须只引用存储在该索引中的列。
2.索引类型必须支持只用索引的扫描。B-树索引总是支持只用索引的扫描。

10.2创建有效利用仅索引扫描功能的索引

为了有效利用仅索引扫描功能，可以选择创建一个覆盖索引，它是一个特别设计的索引，包含经常运行的特殊类型查询所需要的列。
经常要查询的列

SELECT y FROM tab WHERE x = 'key';

那么索引应该定义成这样

CREATE INDEX tab_x_y ON tab(x) INCLUDE (y);

可以将这些查询作为仅索引扫描处理，因为y可以从索引中获取而不需要访问堆。