西安市未央区建设局官方网站,dede做网站地图,深圳招工网站,网站提示代码PostgreSQL常用索引与优化 主要内容转载自《PostgreSQL 开发指南》 索引#xff08;Index#xff09;可以用于提高数据库的查询性能#xff1b;但是索引也需要进行读写#xff0c;同时还会占用更多的存储空间#xff1b;因此了解并适当利用索引对于数据库的优化至关重要。…PostgreSQL常用索引与优化 主要内容转载自《PostgreSQL 开发指南》 索引Index可以用于提高数据库的查询性能但是索引也需要进行读写同时还会占用更多的存储空间因此了解并适当利用索引对于数据库的优化至关重要。本篇我们就来介绍如何高效地使用 PostgreSQL 索引。
索引简介
假设存在以下数据表
CREATE TABLE test (id integer,name text
);insert into test
select v,val:||v from generate_series(1, 10000000) v;我们经常需要使用类似以下的查询返回结果
SELECT name FROM test WHERE id 10000;如果没有索引数据库需要扫描整个表才能找到相应的数据。利用EXPLAIN命令可以看到数据库的执行计划也就是 PostgreSQL 执行 SQL 语句的具体步骤
explain analyze
SELECT name FROM test WHERE id 10000;
QUERY PLAN |
------------------------------------------------------------------------------------------------------------------------|
Gather (cost1000.00..107137.70 rows1 width11) (actual time50.266..12082.777 rows1 loops1) |Workers Planned: 2 |Workers Launched: 2 |- Parallel Seq Scan on test (cost0.00..106137.60 rows1 width11) (actual time7674.992..11553.964 rows0 loops3)|Filter: (id 10000) |Rows Removed by Filter: 3333333 |
Planning Time: 16.480 ms |
Execution Time: 12093.016 ms |Parallel Seq Scan 表示并行顺序扫描执行消耗了 12 s由于表中有包含大量数据而查询只返回一行数据显然这种方法效率很低。 关于执行计划的更多信息可以参考这篇文章。 此时如果在 id 列上存在索引则可以通过索引快速找到匹配的结果。我们先创建一个索引
CREATE INDEX test_id_index ON test (id);创建索引需要消耗一定的时间。然后再次查看数据库的执行计划
explain analyze
SELECT name FROM test WHERE id 10000;
QUERY PLAN |
---------------------------------------------------------------------------------------------------------------------|
Index Scan using test_id_index on test (cost0.43..8.45 rows1 width11) (actual time20.410..20.412 rows1 loops1)|Index Cond: (id 10000) |
Planning Time: 14.989 ms |
Execution Time: 20.521 ms |Index Scan 表示索引扫描执行消耗了 20 ms这种方式类似于图书最后的关键字索引读者可以相对快速地浏览索引并翻到适当的页面而不必阅读整本书来找到感兴趣的内容。
索引不仅仅能够优化查询语句某些包含WHERE条件的UPDATE、DELETE语句也可以利用索引提高性能因为修改数据的前提是找到数据。
此外索引也可以用于优化连接查询基于连接条件中的字段创建索引可以提高连接查询的性能。索引甚至还能优化分组或者排序操作因为索引自身是按照顺序进行组织存储的。
另一方面系统维护索引需要付出一定的代价从而增加数据修改操作的负担。所以我们需要合理创建索引一般只为经常使用到的字段创建索引。就像图书一样不可能为书中的每个关键字都创建一个索引。
索引类型 详细介绍可参考: PostgreSQL 9种索引的原理和应用场景 PostgreSQL 提高了多种索引类型B-树、哈希、GiST、SP-GiST、GIN 以及 BRIN 等索引。每种索引基于不同的存储结构和算法用于优化不同类型的查询。默认情况下PostgreSQL 创建 B-树索引因为它适合大部分情况下的查询。
1.B-树索引
注意B树就是B-树-是个连字符号不是减号。 B-树是一种平衡的多路**查找(又称排序)**树在文件系统中有所应用。主要用作文件的索引。其中的B就表示平衡(Balance)与B树区别可参考https://xiaolincoding.com/mysql/index/why_index_chose_bpuls_tree.html#_3%E3%80%81%E8%8C%83%E5%9B%B4%E6%9F%A5%E8%AF%A2
实现原理可参考《深入浅出PostgreSQL B-Tree索引结构》
B-树是一个自平衡树self-balancing tree按照顺序存储数据支持对数时间复杂度O(logN)的搜索、插入、删除和顺序访问。举例来说假如 100 条数据时需要 1 次磁盘 I/O也就是说 N 等于 10010000 条数据时只需要 2 次 I/O1 亿数据时只需要 4 次 I/O。
对于索引列上的以下比较运算符PostgreSQL 优化器都会考虑使用 B-树索引
BETWEENINIS NULLIS NOT NULL
另外如果模式匹配运算符LIKE和~中模式的开头不是通配符优化器也可以使用 B-树索引例如
col LIKE foo%
col ~ ^foo对于不区分大小的的ILIKE和~*运算符如果匹配的模式以非字母的字符不受大小写转换影响开头也可以使用 B-树索引。
B-树索引还可以用于优化排序操作例如
SELECT col1, col2FROM tWHERE col1 BETWEEN 100 AND 200ORDER BY col1;col1 上的索引不仅能够优化查询条件也可以避免额外的排序操作因为基于该索引访问时本身就是按照排序返回结果。
2.哈希索引
哈希索引Hash index只能用于简单的等值查找也就是说索引字段被用于等号条件判断。因为对数据进行哈希运算之后不再保留原来的大小关系。
hash索引特别适用于字段VALUE非常长不适合b-tree索引因为b-tree一个PAGE至少要存储3个ENTRY所以不支持特别长的VALUE的场景例如很长的字符串并且用户只需要等值搜索建议使用hash index。
创建哈希索引需要使用HASH关键字
CREATE INDEX index_name
ON table_name USING HASH (column_name);CREATE INDEX语句用于创建索引USING子句指定索引的类型具体参考下文。
3.GiST 索引
GiST 代表通用搜索树Generalized Search TreeGiST 索引单个索引类型而是一种支持不同索引策略的框架。GiST 索引常见的用途包括几何数据的索引和全文搜索。GiST 索引也可以用于优化“最近邻”搜索例如
SELECT * FROM places ORDER BY location - point (101,456) LIMIT 10;该语句用于查找距离某个目标地点最近的 10 个地方。
GiST是一个通用的索引接口可以使用GiST实现b-tree, r-tree等索引结构。
不同的类型支持的索引检索也各不一样。例如
1、几何类型支持位置搜索包含、相交、在上下左右等按距离排序。
2、范围类型支持位置搜索包含、相交、在左右等。
3、IP类型支持位置搜索包含、相交、在左右等。
4、空间类型PostGIS支持位置搜索包含、相交、在上下左右等按距离排序。
5、标量类型支持按距离排序。
实现原理可参考《PostgreSQL 百亿地理位置数据 近邻查询性能》
4.SP-GiST 索引
SP-GiST 代表空间分区 GiST主要用于 GIS、多媒体、电话路由以及 IP 路由等数据的索引。
与 GiST 类似SP-GiST 也支持“最近邻”搜索。是一个通用的索引接口但是SP-GIST使用了空间分区的方法使得SP-GiST可以更好的支持非平衡数据结构例如quad-trees, k-d tree, radis tree.
实现原理可参考《Space-partitioning trees in PostgreSQL》
5.GIN 索引
GIN 代表广义倒排索引generalized inverted indexes主要用于单个字段中包含多个值的数据例如 hstore、array、jsonb 以及 range 数据类型。一个倒排索引为每个元素值都创建一个单独的索引项可以有效地查询某个特定元素值是否存在。Google、百度这种搜索引擎利用的就是倒排索引。
实现原理可参考《PostgreSQL GIN索引实现原理》
应用场景
1、当需要搜索多值类型内的VALUE时适合多值类型例如数组、全文检索、TOKEN。根据不同的类型支持相交、包含、大于、在左边、在右边等搜索
2、当用户的数据比较稀疏时如果要搜索某个VALUE的值可以适应btree_gin支持普通btree支持的类型。支持btree的操作符
3、当用户需要按任意列进行搜索时gin支持多列展开单独建立索引域同时支持内部多域索引的bitmapAnd, bitmapOr合并快速的返回按任意列搜索请求的数据。
6.BRIN 索引
BRIN 代表块区间索引block range indexes存储了连续物理范围区间内的数据摘要信息。BRIN 也相比于 B-树索引要小很多维护也更容易。对于不进行水平分区就无法使用 B-树索引的超大型表可以考虑 BRIN。
BRIN 通常用于具有线性排序顺序的字段例如订单表的创建日期。
例如时序数据在时间或序列字段创建BRIN索引进行等值、范围查询时效果很棒。
应用场景
《BRIN (block range index) index》
《PostgreSQL 物联网黑科技 - 瘦身几百倍的索引(BRIN index)》
《PostgreSQL 聚集存储 与 BRIN索引 - 高并发行为、轨迹类大吞吐数据查询场景解说》
《PostgreSQL 并行写入堆表如何保证时序线性存储 - BRIN索引优化》
7.rum 索引
原理可参考https://github.com/postgrespro/rum
rum 是一个索引插件由Postgrespro开源适合全文检索属于GIN的增强版本。
增强包括
1、在RUM索引中存储了lexem的位置信息所以在计算ranking时不需要回表查询而GIN需要回表查询。
2、RUM支持phrase搜索而GIN无法支持。
3、在一个RUM索引中允许用户在posting tree中存储除ctid行号以外的字段VALUE例如时间戳。
这使得RUM不仅支持GIN支持的全文检索还支持计算文本的相似度值按相似度排序等。同时支持位置匹配例如速度与激情可以采用速度 2 “激情” 进行匹配而GIN索引则无法做到
应用场景
《PostgreSQL 全文检索加速 快到没有朋友 - RUM索引接口(潘多拉魔盒)》
《从难缠的模糊查询聊开 - PostgreSQL独门绝招之一 GIN , GiST , SP-GiST , RUM 索引原理与技术背景》
《PostgreSQL结合余弦、线性相关算法 在文本、图片、数组相似 等领域的应用 - 3 rum, smlar应用场景分析》
8.bloom 索引
bloom索引接口是PostgreSQL基于bloom filter构造的一个索引接口属于lossy索引可以收敛结果集(排除绝对不满足条件的结果剩余的结果里再挑选满足条件的结果)因此需要二次checkbloom支持任意列组合的等值查询。
bloom存储的是签名签名越大耗费的空间越多但是排除更加精准。有利有弊。
CREATE INDEX bloomidx ON tbloom USING bloom (i1,i2,i3)WITH (length80, col12, col22, col34);签名长度 80 bit, 最大允许4096 bits
col1 - col32分别指定每列的bits默认长度2最大允许4095 bits.应用场景
bloom索引适合多列任意组合查询。
《PostgreSQL 9.6 黑科技 bloom 算法索引一个索引支撑任意列组合查询》
9.zombodb 索引
zombodb是PostgreSQL与ElasticSearch结合的一个索引接口可以直接读写ES。
https://github.com/zombodb/zombodb
应用场景
与ES结合实现SQL接口的搜索引擎实现数据的透明搜索。
创建索引
PostgreSQL 使用CREATE INDEX语句创建新的索引
CREATE INDEX index_name ON table_name
[USING method]
(column_name [ASC | DESC] [NULLS FIRST | NULLS LAST]);其中
index_name 是索引的名称table_name 是表的名称method 表示索引的类型例如 btree、hash、gist、spgist、gin 或者 brin。默认为 btreecolumn_name 是字段名ASC表示升序排序默认值DESC表示降序索引NULLS FIRST和NULLS LAST表示索引中空值的排列顺序升序索引时默认为NULLS LAST降序索引时默认为NULLS FIRST。
如果我们经常使用 name 字段作为查询条件可以为 test 表创建以下索引
CREATE INDEX test_name_index ON test (name);创建索引之后优化器会自动选择是否使用索引例如
explain analyze
SELECT * FROM test WHERE name IS NULL;
QUERY PLAN |
---------------------------------------------------------------------------------------------------------------------|
Index Scan using test_name_index on test (cost0.43..5.77 rows1 width15) (actual time0.036..0.037 rows0 loops1)|Index Cond: (name IS NULL) |
Planning Time: 1.067 ms |
Execution Time: 0.048 ms |基于索引字段的IS NULL运算符同样可以利用索引进行优化。
唯一索引
在创建索引时可以使用UNIQUE关键字指定唯一索引
CREATE UNIQUE INDEX index_name
ON table_name (column_name [ASC | DESC] [NULLS FIRST | NULLS LAST]);唯一索引可以用于实现唯一约束PostgreSQL 目前只支持 B-树类型的唯一索引。多个 NULL 值被看作是不同的值因此唯一索引字段可以存在多个空值。 对于主键和唯一约束PostgreSQL 会自动创建一个唯一索引从而确保唯一性。 多列索引
PostgreSQL 支持基于多个字段的索引也就是多列索引复合索引。默认情况下一个多列索引最多可以使用 32 个字段。只有B-树、GIST、GIN 和 BRIN 索引支持多列索引。
CREATE [UNIQUE] INDEX index_name ON table_name
[USING method]
(column1 [ASC | DESC] [NULLS FIRST | NULLS LAST], ...);对于多列索引应该将最常作为查询条件使用的字段放在左边较少使用的字段放在右边。例如基于c1, c2, c3创建的索引可以优化以下查询
WHERE c1 v1 and c2 v2 and c3 v3;
WHERE c1 v1 and c2 v2;
WHERE c1 v1;但是以下查询无法使用该索引
WHERE c2 v2;
WHERE c3 v3;
WHERE c2 v2 and c3 v3;对于多列唯一索引字段的组合值不能重复但是如果某个字段是空值其他字段可以出现重复值。
函数索引
函数索引也叫表达式索引是指基于某个函数或者表达式的值创建的索引。PostgreSQL 中创建函数索引的语法如下
CREATE [UNIQUE] INDEX index_name
ON table_name (expression);expression 是基于字段的表达式或者函数。
以下查询在 name 字段上使用了 upper 函数
explain analyze
SELECT * FROM test WHERE upper(name) VAL:10000;
QUERY PLAN |
---------------------------------------------------------------------------------------------------------------------------|
Gather (cost1000.00..122556.19 rows50001 width15) (actual time18.629..7310.422 rows1 loops1) |Workers Planned: 2 |Workers Launched: 2 |- Parallel Seq Scan on test (cost0.00..116556.09 rows20834 width15) (actual time4746.266..7171.452 rows0 loops3)|Filter: (upper(name) VAL:10000::text) |Rows Removed by Filter: 3333333 |
Planning Time: 0.100 ms |
Execution Time: 7310.444 ms |虽然 name 字段上存在索引 test_name_index但是函数会导致优化器无法使用该索引。为了优化这种不分区大小写的查询语句可以基于 name 字段创建一个函数索引
drop index test_name_index;
create index test_name_index on test(upper(name));再次查看该语句的执行计划
explain analyze
SELECT * FROM test WHERE upper(name) VAL:10000;
QUERY PLAN |
-------------------------------------------------------------------------------------------------------------------------------|
Bitmap Heap Scan on test (cost1159.93..57095.47 rows50000 width15) (actual time17.046..17.047 rows1 loops1) |Recheck Cond: (upper(name) VAL:10000::text) |Heap Blocks: exact1 |- Bitmap Index Scan on test_name_index (cost0.00..1147.43 rows50000 width0) (actual time17.032..17.032 rows1 loops1)|Index Cond: (upper(name) VAL:10000::text) |
Planning Time: 1.985 ms |
Execution Time: 17.080 ms |函数索引的维护成本比较高因为插入和更新时都需要进行函数计算。
部分索引
部分索引partial index是只针对表中部分数据行创建的索引通过一个WHERE子句指定需要索引的行。例如对于订单表 orders绝大部的订单都处于完成状态我们只需要针对未完成的订单进行查询跟踪可以创建一个部分索引
create table orders(order_id int primary key, order_ts timestamp, finished boolean);create index orders_unfinished_index
on orders (order_id)
where finished is not true;该索引只包含了未完成的订单 id比直接基于 finished 字段创建的索引小很多。它可以用于优化未完成订单的查询
explain analyze
select order_id
from orders
where finished is not true;
QUERY PLAN |
--------------------------------------------------------------------------------------------------------------------------------|
Bitmap Heap Scan on orders (cost4.38..24.33 rows995 width4) (actual time0.010..0.010 rows0 loops1) |Recheck Cond: (finished IS NOT TRUE) |- Bitmap Index Scan on orders_unfinished_index (cost0.00..4.13 rows995 width0) (actual time0.004..0.004 rows0 loops1)|
Planning Time: 0.130 ms |
Execution Time: 0.049 ms |覆盖索引
PostgreSQL 中的索引都属于二级索引意味着索引和数据是分开存储的。因此通过索引查找数据即需要访问索引又需要访问表而表的访问是随机 I/O。
为了解决这个性能问题PostgreSQL 支持 Index-Only 扫描只需要访问索引的数据就能获得需要的结果而不需要再次访问表中的数据。例如
CREATE TABLE t (a int, b int, c int);
CREATE UNIQUE INDEX idx_t_ab ON t USING btree (a, b) INCLUDE (c);以上语句基于字段 a 和 b 创建了多列索引同时利用INCLUDE在索引的叶子节点存储了字段 c 的值。以下查询可以利用 Index-Only 扫描
explain analyze
select a, b, c
from t
where a 100 and b 200;
QUERY PLAN |
----------------------------------------------------------------------------------------------------------------|
Index Only Scan using idx_t_ab on t (cost0.15..8.17 rows1 width12) (actual time0.007..0.007 rows0 loops1)|Index Cond: ((a 100) AND (b 200)) |Heap Fetches: 0 |
Planning Time: 0.078 ms |
Execution Time: 0.021 ms |以上查询只返回索引字段a、b和覆盖的字段c可以仅通过扫描索引即可返回结果。
B-树索引支持 Index-Only 扫描GiST 和 SP-GiST 索引支持某些运算符的 Index-Only 扫描其他索引不支持这种方式。
查看索引
PostgreSQL 提供了一个关于索引的视图 pg_indexes可以用于查看索引的信息
select * from pg_indexes where tablename test;
schemaname|tablename|indexname |tablespace|indexdef |
----------|---------|---------------|----------|---------------------------------------------------------------------|
public |test |test_id_index | |CREATE INDEX test_id_index ON public.test USING btree (id) |
public |test |test_name_index| |CREATE INDEX test_name_index ON public.test USING btree (upper(name))|该视图包含的字段依次为模式名、表名、索引名、表空间以及索引的定义语句。
如果使用 psql 客户端连接可以使用\d table_name命令查看表的结构包括表中的索引信息。
维护索引
PostgreSQL 提供了一些修改和重建索引的方法
ALTER INDEX index_name RENAME TO new_name;
ALTER INDEX index_name SET TABLESPACE tablespace_name;REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } index_name;两个ALTER INDEX语句分别用于重命名索引和移动索引到其他表空间REINDEX用于重建索引数据支持不同级别的索引重建。
另外索引被创建之后系统会在修改数据的同时自动更新索引。不过我们需要定期执行ANALYZE命令更新数据库的统计信息以便优化器能够合理使用索引。
删除索引
如果需要删除一个已有的索引可以使用以下命令
DROP INDEX index_name [ CASCADE | RESTRICT ];CASCADE 表示级联删除其他依赖该索引的对象RESTRICT 表示如果存在依赖于该索引的对象将会拒绝删除操作。默认为 RESTRICT。
我们可以使用以下语句删除 test 上的索引
DROP INDEX test_id_index, test_name_index;
