postgresql-索引与优化

2023-09-13 23:20:57

postgresql-索引与优化

索引简介

索引（Index）可以用于提高数据库的查询性能；但是索引也需要进行读写，同时还会占用
更多的存储空间；因此了解并适当利用索引对于数据库的优化至关重要。本篇我们就来介绍如何
高效地使用 PostgreSQL 索引。

-- 创建表
CREATE TABLE test (
 id integer,
 name text
);
-- generate_series 产生序列
INSERT INTO test
SELECT v,'val:'||v FROM generate_series(1, 10000000) v;

SELECT name FROM test WHERE id = 10000;

如果没有索引，数据库需要扫描整个表才能找到相应的数据。利用 EXPLAIN 命令可以看到
数据库的执行计划，也就是 PostgreSQL 执行 SQL 语句的具体步骤
执行计划参考文档

-- Parallel Seq Scan 表示并行顺序扫描，执行消耗了大量时间
--；由于表中有包含大量数据，而查询只返回一行数据，显然这种方法效率很低。
explain analyze
SELECT name FROM test WHERE id = 10000;

在这里插入图片描述

--如果在 id 列上存在索引，则可以通过索引快速找到匹配的结果。我们先创建一个索引：
CREATE INDEX test_id_index ON test (id);

-- 创建索引之后，再次查看数据库的执行计划
explain analyze
SELECT name FROM test WHERE id = 10000;

在这里插入图片描述
Index Scan 表示索引扫描，执行消耗了 1.3ms；这种方式类似于图书最后的关键字索引，读
者可以相对快速地浏览索引并翻到适当的页面，而不必阅读整本书来找到感兴趣的内容。

索引不仅仅能够优化查询语句，某些包含 WHERE 条件的 UPDATE、DELETE 语句也可以
利用索引提高性能，因为修改数据的前提是找到数据。

此外，索引也可以用于优化连接查询，基于连接条件中的字段创建索引可以提高连接查询的
性能。索引甚至还能优化分组或者排序操作，因为索引自身是按照顺序进行组织存储的。

另一方面，系统维护索引需要付出一定的代价，从而增加数据修改操作的负担。所以，我们
需要合理创建索引，一般只为经常使用到的字段创建索引。就像图书一样，不可能为书中的每个
关键字都创建一个索引

索引类型

PostgreSQL 提高了多种索引类型：B-树、哈希、GiST、SP-GiST、GIN 以及 BRIN 索引。每
种索引基于不同的存储结构和算法，用于优化不同类型的查询。默认情况下，PostgreSQL 创建
B-树索引，因为它适合大部分情况下的查询

B-树索引

B-树是一个自平衡树（self-balancing tree），按照顺序存储数据，支持对数时间复杂度（O(logN)）
的搜索、插入、删除和顺序访问。
对于索引列上的以下比较运算符，PostgreSQL 优化器都会考虑使用 B-树索引：
• <
• <=
• =
• >=
• BETWEEN
• IN
• IS NULL
• IS NOT NULL
另外，如果模式匹配运算符 LIKE 和~中模式的开头不是通配符，优化器也可以使用 B-树索
引，例如：

col LIKE 'foo%'
col ~ '^foo'

对于不区分大小的的 ILIKE 和~*运算符，如果匹配的模式以非字母的字符（不受大小写转
换影响）开头，也可以使用 B-树索引。
B-树索引还可以用于优化排序操作，例如：

SELECT col1, col2
 FROM t
WHERE col1 BETWEEN 100 AND 200
ORDER BY col1;

col1 上的索引不仅能够优化查询条件，也可以避免额外的排序操作；因为基于该索引访问时
本身就是按照排序返回结果

哈希索引

哈希索引（Hash index）只能用于简单的等值查找（=），也就是说索引字段被用于等号条
件判断。因为对数据进行哈希运算之后不再保留原来的大小关系。
创建哈希索引需要使用 HASH 关键字：

-- CREATE INDEX 语句用于创建索引，USING 子句指定索引的类型
CREATE INDEX index_name
ON table_name USING HASH (column_name);

GiST 索引

GiST 代表通用搜索树（Generalized Search Tree），GiST 索引单个索引类型，而是一种支持
不同索引策略的框架。GiST 索引常见的用途包括几何数据的索引和全文搜索。GiST 索引也可以
用于优化“最近邻”搜索，例如

-- 该语句用于查找距离某个目标地点最近的 10 个地方。
SELECT *
FROM places
ORDER BY location <-> point '(101,456)'
LIMIT 10;

SP-GiST 索引

SP-GiST 代表空间分区 GiST，主要用于 GIS、多媒体、电话路由以及 IP 路由等数据的索引。
与 GiST 类似，SP-GiST 也支持“最近邻”搜索

GIN 索引

GIN 代表广义倒排索引（generalized inverted indexes），主要用于单个字段中包含多个值的
数据，例如 hstore、array、jsonb 以及 range 数据类型。一个倒排索引为每个元素值都创建一个单
独的索引项，可以有效地查询某个特定元素值是否存在。Google、百度这种搜索引擎利用的就是
倒排索引。

BRIN 索引

BRIN 代表块区间索引（block range indexes），存储了连续物理范围区间内的数据摘要信息。
BRIN 也相比 B-树索引要小很多，维护也更容易。对于不进行水平分区就无法使用 B-树索引的
超大型表，可以考虑 BRIN。
BRIN 通常用于具有线性排序顺序的字段，例如订单表的创建日期
postgresql官网关于索引的介绍

创建索引

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。默认为 btree；
• column_name 是字段名，ASC 表示升序排序（默认值），DESC 表示降序索引；
• NULLS FIRST 和 NULLS LAST 表示索引中空值的排列顺序，升序索引时默认为 NULLS
LAST，降序索引时默认为 NULLS FIRST。
在这里插入图片描述

唯一索引

在创建索引时，可以使用 UNIQUE 关键字指定唯一索引：

CREATE UNIQUE INDEX index_name
ON table_name (column_name [ASC | DESC] [NULLS FIRST | NULLS LAST]);

唯一索引可以用于实现唯一约束，PostgreSQL 目前只支持 B-树类型的唯一索引。多个 NULL
被看作是不同的值，因此唯一索引字段可以存在多个空值
对于主键和唯一约束，PostgreSQL 会自动创建一个唯一索引，从而确保唯一性。

多列索引

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 函数：
在这里插入图片描述
虽然 name 字段上存在索引 test_name_index，但是函数会导致优化器无法使用该索引。为了
优化这种不分区大小写的查询语句，可以基于 name 字段创建一个函数索引

drop index test_name_index;
create index test_name_index on test(upper(name));

再次查看该语句的执行计划：
在这里插入图片描述
函数索引的维护成本比较高，因为插入和更新时都需要进行函数计算。

部分索引

部分索引（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;

在这里插入图片描述

覆盖索引

PostgreSQL 中的索引都属于二级索引，意味着索引和数据是分开存储的。因此通过索引查
找数据即需要访问索引，又需要访问表，而表的访问是随机 I/O
为了解决这个性能问题，PostgreSQL 支持 Index-Only Scan，只需要访问索引的数据就能获
得需要的结果，而不需要再次访问表中的数据。例如

-- 创建表
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 Scan：

explain analyze
select a, b, c
from t
where a = 100 and b = 200;

在这里插入图片描述
以上查询只返回索引字段（a、b）和覆盖的字段（c），可以仅通过扫描索引即可返回结果。
B-树索引支持 Index-Only Scan，GiST 和 SP-GiST 索引支持某些运算符的 Index-Only Scan，
其他索引不支持这种方式

查看索引

PostgreSQL 提供了一个关于索引的视图 pg_indexes，可以用于查看索引的信息：

-- 该视图包含的字段依次为：模式名、表名、索引名、表空间以及索引的定义语句。
select * from pg_indexes where tablename = 'test';

在这里插入图片描述

维护索引

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;
更多推荐

Sui Move智能合约提供更智能和简单的编程

编写区块链和其他计算环境的代码有很多相似之处，但区块链的重点大部分都集中在构建智能合约上。智能合约部署编译后的代码，本质上是在区块链上自动执行交易的apps。Sui网络本地的Sui虚拟机使用SuiMove编程语言编写智能合约。术语“智能合约”意味着区块链网络独特的以交易为中心的环境。考虑到区块链的无需许可以及公共环境的

[设计模式]springboot优雅实现策略器模式(加入注册器实现)

优雅实现策略器模式场景废话少说，源码地址。讲解关于本文创建登陆策略创建环境（注册器）简化代码创建父类对象，也方便后期扩展具体策略实现类用户名密码登陆手机号登陆触发点调试简言场景登陆场景使用（登陆之后返回用户信息和token所做操作基本一致，杜绝多个判断）用户名密码登陆，指纹登录手机号登陆楼主之前写过一次通过注解实现的，

NoSQL之 Redis配置与优化

1、关系数据库与非关系型数据库1.1关系型数据库关系型数据库是一个结构化的数据库，创建在关系模型（二维表格模型）基础上，一般面向于记录。SQL语句（标准数据查询语言）就是一种基于关系型数据库的语言，用于执行对关系型数据库中数据的检索和操作。主流的关系型数据库包括Oracle、MySQL、SQLServer、Micros

软件设计模式系列之三———工厂方法模式

1模式的定义工厂方法模式是一种常见的设计模式，属于创建型设计模式之一，它在软件工程中用于对象的创建。该模式的主要思想是将对象的创建过程抽象化，将具体对象的实例化延迟到子类中完成，以便在不同情况下可以创建不同类型的对象，而客户端代码不需要知道实际创建的对象类型。2举例说明不同公司都生产自己的手机，就符合工厂方法模式。比如

探索GmSSL+Nginx实践及原理

前言随着大国崛起步伐的迈进，敏感单位的数据安全问题越发受到重视，数据的加密安全传输尤为重要，对于安全问题，国家自研加密算法提供了有力的保障。作为信创行业的国有企业，十分有必要在网络通信中使用国密算法加密通信，保障客户数据安全，做出客户放心满意的产品。本文将介绍如何制作出支持国密算法的nginx，及相关原理。GmSSL介

Java线程池详解

好处降低资源消耗。通过重复利用已创建的线程降低线程创建和销毁造成的消耗。提高响应速度。当任务到达时，任务可以不需要等到线程创建就能立即执行。提高线程的可管理性。线程是稀缺资源，如果无限制的创建，不仅会消耗系统资源，还会降低系统的稳定性，使用线程池可以进行统一的分配，调优和监控。线程池一般用于执行多个不相关联的耗时任务，

Linux底层基础知识

一.汇编，C语言，C++，JAVA之间的关系汇编，C语言，C++可以通过不同的编译器，编译成机器码。而java只能由Java虚拟机识别。Java虚拟机可以看成一个操作系统，Java虚拟机是由汇编，C，Linux等编写而成的一个操作系统（面向os）不同的芯片，底层的CISC指令集不同，所以其机器码有区别，因此汇编不能跨平

用了 TCP 协议，就一定不会丢包吗？

表面上我是个技术博主。但没想到今天成了个情感博主。我是没想到有一天，我会通过技术知识，来挽救粉丝即将破碎的感情。掏心窝子的说。这件事情多少是沾点功德无量了。事情是这样的。最近就有个读者加了我的绿皮聊天软件，女生，头像挺好看的，就在我以为她要我拉她进群发成人专升本广告的时候。画风突然不对劲。她说她男朋友也是个程序员，异地

Docker 安装

Docker官网：Docker:AcceleratedContainerApplicationDevelopmentDockerHub官网：https://hub.docker.com/前提说明CentOSDocker安装前提条件目前，CentOS仅发行版本中的内核支持Docker。Docker运行在CentOS7(6

蒙特卡洛树搜索（MCTS）在Python中实现井字游戏策略优化详细教程

1.介绍井字游戏（TicTacToe）是大家都很熟悉的一款策略游戏，两个玩家轮流在3x3的棋盘上放置自己的标记（通常是’X’和’O’），目标是在任意方向上（横、竖、斜）连续三个自己的标记。而蒙特卡洛树搜索（MCTS）则是一种广泛用于复杂策略游戏（例如围棋、象棋等）的算法。在本文中，我们将结合这两者，使用MCTS为井字游

iOS系统下轻松构建自动化数据收集流程

目录python的优势ShortcutsApp介绍如何结合Python与ShortcutAppiOS系统下轻松构建自动化数据收集流程总结在当今的数字化时代，数据已经成为企业成功的关键因素之一。然而，随着业务的发展和数据量的增加，手动收集和分析数据的方式已经不再可行。在iOS系统下，我们可以利用一些工具和技术来轻松构建自

热文推荐
  1. Web 3.0 发展到什么水平了？

    最初，有互联网：电线和服务器的物理基础设施，让计算机和它们前面的人相互交谈。美国政府的阿帕网在1969年发出了第一条消息，但我们今天所知道的网络直到1991年才出现，当时HTML和URL使用户可以在静态页面之间导航。将此视为只读Web或Web1。在2000年代初期，情况开始发生变化。首先，互联网的互动性越来越强;这是一

  2. 金融投资公司如何实现创新， 盛创汇凭借人工智能站上硬科技C位

    作为硬科技产业的重要组成部分,近年人工智能受到了国家政策的高度重视。在《“十四五”规划和2035年远景目标纲要》中,人工智能被摆放在科技前沿领域攻关方面的首要位置,先后八次被提及。《规划》指出,对新一代人工智能,要在前沿基础理论突破,专用芯片研发,深度学习框架等开源算法平台构建,学习推理与决策、图像图形、语音视频、自然

  3. 深入思考redis面经

    1redission分布式锁1.1为了保证数据一致性，引入了redission的锁，你是为了抗住高并发而去为了引入这个锁还是说为了保证数据一致性去引入的答：主要是为了抗住高并发问题，解决redis的缓存击穿问题，但是也能解决一定的数据一致性问题。是的，当我们谈到“击穿”问题时，通常指的是缓存击穿，即当某个热点缓存失效时

  4. C++ PrimerPlus 复习 第一章 命令编译链接文件 make文件

    第一章命令编译链接文件C++有什么呢？C++源代码文件后缀运行C++过程可执行代码：编译语法：makeMakefile基础语法编写完make只要和将要编译的文件放一起就行然后在该目录使用make命令，就将自动运行；基础的Makefile版本现在的缺点也大\^-\^更加健全的Makefile但还是有不小的缺点常用版本C+

  5. C++设计模式_04_Strategy 策略模式

    接上篇，本篇将会介绍C++设计模式中的Strategy策略模式，和上篇模板方法TemplateMethod一样，仍属于“组件协作”模式，它与TemplateMethod有着异曲同工之妙。文章目录1.动机（Motivation）2.代码演示Strategy策略模式2.1传统方法处理2.2怎么用扩展的方式来支持未来的变化呢

  6. PROB: Probabilistic Objectness for Open World Object Detection（论文解析）

    PROB:ProbabilisticObjectnessforOpenWorldObjectDetection摘要2相关工作摘要开放世界目标检测（OWOD）是一个新的、具有挑战性的计算机视觉任务，它弥合了传统的目标检测（OD）基准和现实世界中的目标检测之间的差距。除了检测和分类已知/标记的对象外，OWOD算法还应该能够

  7. 【论文阅读】Untargeted Backdoor Attack Against Object Detection（针对目标检测的无目标后门攻击）

    文章目录一.论文信息二.论文内容0.摘要1.论文概述2.背景介绍3.作者贡献4.重点图表一.论文信息论文题目：UntargetedBackdoorAttackAgainstObjectDetection（针对目标检测的无目标后门攻击）发表年份：2023-ICASSP（CCF-B）作者信息：ChengxiaoLuo（清华

  8. 为什么JWT要结合Redis使用

    JWT解决了什么问题存储token的情况：校验token需要重复调用数据库耗时的问题JWT本身缺陷alg不要指定为none算法不要指定数组，只使用一种算法令牌长度可能会超过允许的URL长度，和cookie长度如果需要跟踪用于速率限制和IP白名单的API这些功能，那么使用无状态是不现实的以上都是容易避免的问题，JWT最大

  9. Java实现截取视频第一帧

    目录前言一、通过Java借助第三方库实现1.引用ffmpeg使用maven，导入pom依赖：工具类2.引用jcodec二、使用第三方存储自带的方法实现（如阿里云OSS、华为云OBS）前言在实际项目中，会遇到上传视频后，需要截取视频的首帧或指定帧为图片，作为展示使用的需求。这个需求本身并不难，而且网上一搜一大把，今天就针