Postgresql索引浅析
⼀、摘要
1、索引是提⾼数据库性能的常⽤途径。⽐起没有索引，使⽤索引可以让数据库服务器更快到并获取特定⾏。但是索引同时也会增加数据库系统的⽇常管理负担，因此我们应该聪明地使⽤索引。
2、索引其实就是⼀种数据结构，将数据库中的数据以⼀定的数据结构算法进⾏存储。当表数据量越来越⼤时查询速度会下降，建⽴合适的索引能够帮助我们快速的检索数据库中的数据，快速定位到可能满⾜条件的记录，不需要遍历所有记录。
3、索引⾃⾝也占⽤存储空间、消耗计算资源，创建过多的索引将对数据库性能造成负⾯影响（尤其影响数据导⼊的性能，建议在数据导⼊后再建索引）。postgresql⾥的所有索引都是“从属索引”，也就是索引在物理上与它描述的表⽂件分离。索引是⼀种数据库对象，每个索引在pg_class⾥都有记录。不同种类的索引有着不同的访问⽅法和内部构造。PG⾥所有的索引访问⽅法都通过页⾯来组织索引的内部结构。从本质来讲，索引是⼀些数据的键值和元组标识符（TID）之间的映射。在查询数据时如果⼀个page中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提⾼查询效率。
⼆、简介
⼀旦⼀个索引被创建，就不再需要进⼀步的⼲预：系统会在表更新时更新索引，⽽且会在它觉得使⽤索引⽐顺序扫描表效率更⾼时使⽤索引。但我们可能需要定期地运⾏ANALYZE命令来更新统计信息以便查询规划器能做出正确的决定。索引也会使带有搜索条件
的UPDATE和DELETE命令受益。此外索引还可以在连接搜索中使⽤。因此，⼀个定义在连接条件列上的索引可以显著地提⾼连接查询的速度。
在⼀个⼤表上创建⼀个索引会耗费很长的时间。默认情况下，PostgreSQL允许在索引创建时并⾏地进⾏读（SELECT命令），但写（INSERT、UPDATE和DELETE）则会被阻塞直到索引创建完成。在⽣产环境中这通常是不可接受的。这时需要并发构建索引，创建索引可能会⼲扰数据库的常规操作。通常 PostgreSQL会锁住要被索引的表，让它不能被写⼊，并且⽤该表上的⼀次扫描来执⾏整个索引的构建。其他事务仍然可以读取表，但是如果它们尝试在该表上进⾏插⼊、更新或者删除，它们会被阻塞直到索引构建完成。如果系统是⼀个⽣产数据库，这可能会导致严重的后果。索引⾮常⼤的表可能会需要很多个⼩时，⽽且即使是较⼩的表，在构建索引过程中阻塞写⼊者⼀段时间在⽣产系统中也是不能接受的。PostgreSQL⽀持构建索引时不阻塞写⼊。这种⽅法通过指定CREATE INDEX的CONCURRENTLY选项实现。当使⽤这个选项时，PostgreSQL必须执⾏该表的两次扫描，此外它必须等待所有现有可能会修改或者使⽤该索引的事务终⽌。因此这种⽅法⽐起标准索引构建过程来说要做更多⼯作并且需要更多时间。不过，由于它允许在构建索引时继续普通操作，这种⽅式对于在⽣产环境中增加新索引很有⽤。当
然，由索引创建带来的额外 CPU 和 I/O 开销可能会拖慢其他操作。
如果在扫描表⽰出现问题，例如死锁或者唯⼀索引中的唯⼀性被违背，CREATE INDEX将会失败，但留下⼀个“不可⽤” 的索引。这个索引会被查询所忽略，因为它可能不完整。不过它仍将消耗更新开销。psql的\d命令将把这类索引报告为INVALID。这种情况下推荐的恢复⽅法是删除该索引并且尝试再次执⾏CREATE INDEX CONCURRENTLY。
常规索引构建允许在同⼀个表上同时构建其他常规索引，但是在⼀个表上同时只能有⼀个并发索引构建发⽣。
三、索引类型
PostgreSQL提供了多种索引类型： B-tree、Hash、GiST、SP-GiST 、GIN 和 BRIN。每⼀种索引类型使⽤了⼀种不同的算法来适应不同类型的查询。默认情况下， CREATE INDEX命令创建适合于⼤部分情况的B-tree 索引。
pg_am存储关于关系访问⽅法的信息。系统⽀持的每种访问⽅法在这个⽬录中都有⼀⾏。
pg_am为每⼀种索引⽅法都包含⼀⾏（内部被称为访问⽅法）。PostgreSQL中内建了对表常规访问的⽀持，但是所有的索引⽅法则是在pg_am中描述。可以通过编写必要的代码并且在pg_am中创建⼀项来
增加⼀种新的索引访问⽅法. ⼀个索引⽅法的例程并不直接了解它将要操作的数据类型。⽽是由⼀个操作符类标识索引⽅法⽤来操作⼀种特定数据类型的⼀组操作。
1、B-tree
B-tree索引是最常见的索引并且适合处理等值查询和范围查询的索引，可以在可排序数据上的处理等值和范围查询。特别地，PostgreSQL的查询规划器会在任何⼀种涉及到以下操作符的已索引列上考虑使⽤B-tree索引：<、<=、=、>=、>。将这些操作符组合起来，例
如BETWEEN和IN，也可以⽤B-tree索引搜索实现。同样，在索引列上的IS NULL或IS NOT NULL条件也可以在B-tree索引中使⽤。
优化器也会将B-tree索引⽤于涉及到模式匹配操作符LIKE和~ 的查询，前提是如果\模式是⼀个常量且被固定在字符串的开头—例如：col LIKE 'foo%'或者col ~ '^foo', 但在col LIKE '%bar'上则不会。
PostgreSQL的B-tree索引与Oracle的B-tree索引区别⽐较⼤，主要是以下4点：
PostgreSQL中索引会存储NULL，⽽Oracle不会；
PostgreSQL中建⽴索引时，可以使⽤where来建⽴部分索引，⽽Oracle不能；
PostgreSQL中可以对同⼀列建⽴两个相同的索引，⽽Oracle不能；
PostgreSQL中可以使⽤concurrently关键字达到创建索引时不阻塞表的DML的功能，Oracle也有online参数实现类似的功能。
注意点：
此选项只能指定⼀个索引的名称。
普通CREATE INDEX命令可以在事务内执⾏，但是CREATE INDEX CONCURRENTLY不可以在事务内执⾏。
列存表、分区表和临时表不⽀持CONCURRENTLY⽅式创建索引。
2、Hash
适⽤场景：hash索引存储的是被索引字段VALUE的哈希值，只⽀持简单的等值查询。hash索引特别适⽤于字段VALUE⾮常长（不适合b-tree索引，因为b-tree⼀个PAGE⾄少要存储3个ENTRY，所以不⽀持特别长的VALUE）的场景，例如很长的字符串，并且⽤户只需要等值搜索，建议使⽤hash index。
在pg10之前是不提倡使⽤hash索引的，因为hash索引不会写wal⽇志。不过从pg10开始解决了这⼀问题，并且对hash索引进⾏了⼀些加强hash索引其主要⽬的就是对于某些数据类型（索引键）的值，我们的任务是快速到匹配的⾏的ctid。
Hash索引只能处理简单等值⽐较。不论何时当⼀个索引列涉及到⼀个使⽤了=操作符的⽐较时，查询规划器将考虑使⽤⼀个Hash索引。Hash索引只能处理简单的等值查询。下⾯的命令将创建⼀个Hash索引：
CREATE INDEX name ON table USING HASH (column);
3、GiST
GiST索引即通⽤搜索树。和btree⼀样，也是平衡的搜索树。和btree不同的是，btree索引常常⽤来进⾏例如⼤于、⼩于、等于这些操作中，⽽在实际⽣活中很多数据其实不适⽤这种场景，例如地理数据、图像等等。如果我们想要查询在某个地⽅是否存在某⼀点，即判断地理位置的"包含"那么我们就可以使⽤gist索引了。
Gist索引的使⽤场景有哪些。
因为gist是⼀个通⽤的索引接⼝，所以可以使⽤GiST实现b-tree, r-tree等索引结构。
不同的类型，⽀持的索引检索也各不⼀样。例如：
⼏何类型，⽀持位置搜索（包含、相交、在上下左右等），按距离排序。
范围类型，⽀持位置搜索（包含、相交、在左右等）。
IP类型，⽀持位置搜索（包含、相交、在左右等）。
空间类型（PostGIS），⽀持位置搜索（包含、相交、在上下左右等），按距离排序。
标量类型，⽀持按距离排序。
⽰例：创建⼀个存放⼏何数据的表：
create table t_gist (id int, pos point);
insert into t_gist select generate_series(1,100000), point(round((random()*1000)::numeric, 2), round((random()*1000)::numeric, 2));
select * from t_gist limit 5;
在pos列上创建gist索引：create index idx_t_gist_1 on t_gist using gist (pos);
4、GIN
GIN(Generalized Inverted Index, 通⽤倒排索引) 是⼀个存储对（key、posting list）集合的索引结构，其中key是⼀个键值，posting list是⼀组出现过key的位置。如‘hello', '14:2 23:4'中，表⽰hello在14:2和23:4这两个位置出现过，这些位置实际上就是元组的tid（⾏号，包括数据块ID，⼤⼩为32 bit；以及item point，⼤⼩为16 bit）。通过这种索引结构可以快速的查到包含指定关键字的元组，因此GIN索引特别适⽤于多值类型的元素搜索。
GIN索引常⽤于查询索引字段中的部分元素值，如在text类型和json类型字段中检索某个关键字。在PG中，GIN索引会为每⼀个键建⽴⼀个B-tree索引，这会导致GIN索引的更新速度⾮常慢，因为插⼊或更新⼀条记录，所有相关键值的索引都会被更新。
PG提供gin_pending_list_limit参数来控制GIN索引的更新速度，适当将maintenance_work_mem参数增⼤，可以加快GIN索引的创建过程。如果查询返回的结果集特别⼤，则可以⽤gin_fuzzy_search_limit参数来控制返回的⾏数，默认为0，不限制，⼀般建议设置为5000~20000⽐较合适。
5、BRIN
BRIN 索引是块级索引，有别于B-TREE等索引，BRIN记录并不是以⾏号为单位记录索引明细，⽽是记
录每个数据块或者每段连续的数据块的统计信息。因此BRIN索引空间占⽤特别的⼩，对数据写⼊、更新、删除的影响也很⼩。BRIN属于LOSSLY索引，当被索引列的值与物理存储相关性很强时，BRIN索引的效果⾮常的好。例如时序数据，在时间或序列字段创建BRIN索引，进⾏等值、范围查询时效果很好。6、SP-GiST索引
SP-Gist是Space-Partition Gist（空间分区Gist索引）的简写。和Gist索引⼀样，它也是⼀个索引框架，但是相⽐较于Gist，它优化了索引算法，提⾼了索引的性能。
三、索引⽅式
1、多列索引
⼀个索引可以定义在表的多个列上，⽬前，只有 B-tree、GiST、GIN 和 BRIN 索引类型⽀持多列索引，最多可以指定32个列。多列索引应该较少地使⽤。在绝⼤多数情况下，单列索引就⾜够了且能节约时间和空间。具有超过三个列的索引不太有⽤，除⾮该表的使⽤是极端程式化的。
2、索引和ORDER BY
除了简单地查查询要返回的⾏外，⼀个索引可能还需要将它们以指定的顺序传递。这使得查询中的ORDER BY不需要独⽴的排序步骤。在PostgreSQL当前⽀持的索引类型中，只有B-tree可以产⽣排序后
的输出，其他索引类型会把⾏以⼀种没有指定的且与实现相关的顺序返回。
默认情况下，B-tree索引将它的项以升序⽅式存储，并将空值放在最后(表TID被处理为其它相等条⽬之间的分线器列)。这意味着对列x上索引的⼀次前向扫描将产⽣满⾜ORDER BY x（或者更长的形式：ORDER BY x ASC NULLS LAST）的结果。索引也可以被后向扫描，产⽣满⾜ORDER BY x DESC（ORDER BY x DESC NULLS FIRST， NULLS FIRST是ORDER BY DESC的默认情况）。我们可以在创建B-tree索引时通过ASC、DESC、NULLS FIRST和NULLS LAST选项来改变索引的排序。
3、组合索引
也叫复合索引，只有查询⼦句中在索引列上使⽤了索引操作符类中的操作符并且通过AND连接时才能使⽤单⼀索引。例如，给定⼀个(a, b)上的索引，查询条件WHERE a = 5 AND b = 6可以使⽤该索引，⽽查询WHERE a = 5 OR b = 6不能直接使⽤该索引。幸运的
是，PostgreSQL具有组合多个索引（包括多次使⽤同⼀个索引）的能⼒来处理那些不能⽤单个索引扫描实现的情况。
在所有的应⽤（除了最简单的应⽤）中，可能会有多种有⽤的索引组合，数据库开发⼈员必须做出权衡以决定提供哪些索引。有时候多列索引最好，但是有时更好的选择是创建单独的索引并依赖于索引组合特性。在查询使⽤时，最好将条件顺序按索引的顺序，这样效率最⾼。如：
create index idx1 on table1(col2,col3,col5)；
"select * from table1 where col2=A and col3=B and col5=D" 索引效果明显
如果是"select * from table1 where col3=B and col2=A and col5=D"
或者是"select * from table1 where col3=B"将不会使⽤索引，或者效果不明显
4、唯⼀索引
索引也可以被⽤来强制列值的唯⼀性，或者是多个列组合值的唯⼀性。当前，只有B-tree能够被声明为唯⼀。空值被视为不相同。
5、表达式索引
⼀个索引列并不⼀定是底层表的⼀个列，也可以是从表的⼀列或多列计算⽽来的⼀个函数或者标量表达式。这种特性对于根据计算结果快速获取表中内容是有⽤的。
索引表达式的维护代价较为昂贵，因为在每⼀个⾏被插⼊或更新时都得为它重新计算相应的表达式。然⽽，索引表达式在进⾏索引搜索时却不\需要重新计算，因为它们的结果已经被存储在索引中了。表达式索引对于检索速度远⽐插⼊和更新速度重要的情况⾮常有⽤。
6、部分索引
⼀个部分索引是建⽴在表的⼀个⼦集上，⽽该⼦集则由⼀个条件表达式定义。如：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');
7、索引和排序规则
⼀个索引在每⼀个索引列上只能⽀持⼀种排序规则。如果需要多种排序规则，你可能需要多个索引。
可以创建⼀个额外的⽀持"y"排序规则的索引CREATE INDEX test1c_content_y_index ON test1c (content COLLATE "y");
8、检查索引使⽤
尽管PostgreSQL中的索引并不需要维护或调优，但是检查真实的查询负载实际使⽤了哪些索引仍然⾮常重要。检查⼀个独⽴查询的索引使⽤情况可以使⽤EXPLAIN命令，也可以在⼀个运⾏中的服务器上收集有关索引使⽤的总体统计情况。
9、索引存储参数
可选的WITH⼦句为索引指定存储参数。每⼀种索引⽅法都有⾃⼰的存储参数集合。B-树、哈希、GiST以及SP-GiST索引⽅法都接受这个参数：
fillfactor (integer)
access常见条件表达式
索引的填充因⼦是⼀个百分数，它决定索引⽅法将尝试填充索引页⾯的充满程度。如果页⾯后来被完全填满，它们就会被分裂，导致索引的效率逐渐退化。B-树使⽤了默认的填充因⼦ 90，但是也可以选择为 10 到 100 的任何整数值。如果表是静态的，那么填充因⼦ 100 是最好的，因为它可以让索引的物理尺⼨最⼩化。但是对于更新负荷很重的表，较⼩的填充因⼦有利于最⼩化对页⾯分裂的需求。其他索引⽅法以不同但是⼤致类似的⽅式使⽤填充因⼦，不同⽅法的默认填充因⼦也不相同。
四、性能优化
对于⼤多数索引⽅法，索引的创建速度取决于的设置。较⼤的值将会减少索引创建所需的时间，当然不要把它设置得超过实际可⽤的内存量。
PostgreSQL可以在构建索引时利⽤多个CPU以更快地处理表⾏。这种特性被称为并⾏索引构建。对于⽀持并⾏构建索引的索引⽅法（当前只有B-树），maintenance_work_mem指定每次索引构建操作整体
可⽤的最⼤内存量，⽽不管启动了多少⼯作者进程。⼀般来说，⼀个代价模型（如果有）⾃动判断应该请求多少⼯作者进程。
增加maintenance_work_mem可以让并⾏索引构建受益，⽽等效的串⾏索引构建将⽆法受益或者得到很⼩的益处。注意
maintenance_work_mem可能会影响请求的⼯作者进程的数量，因为并⾏⼯作者必须在总的maintenance_work_mem预算中占有⾄少32MB 的份额。还必须有32MB的份额留给领袖进程。增加max_parallel_maintenance_workers可以允许使⽤更多的⼯作者，这将降低索引创建所需的时间，只要索引构建不是I/O密集型的。当然，还需要有⾜够的CPU计算能⼒，否则⼯作者们会闲置。通过ALTER TABLE为
parallel_workers设置⼀个值直接控制着CREATE INDEX会对表请求多少并⾏⼯作者进程。这会完全绕过代价模型，并且防⽌maintenance_work_mem对请求多少并⾏⼯作者产⽣影响。通过ALTER TABLE将parallel_workers设置为0将禁⽤所有情况下的并⾏索引构建。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。