当前位置: 首页 > news >正文

PostgreSQL-如何创建并发索引

索引简介

索引是数据库中一种快速查询数据的方法。索引中记录了表中的一列或多列值与其物理位置之间的对应关系,就好比一本书前面的目录,通过目录中页码就能快速定位到我们需要查询的内容。

建立索引的好处是加快对表中记录的查找或排序,但建索引需要付出以下代价:

  • 增加了数据库的存储空间
  • 在插入和修改数据时要花费较多的时间,因为索引也要随之更新

除了加快查询的作用外,索引还有一些其他的用途,如唯一索引还可以起到唯一约束的作用。

索引的分类

PG中支持以下几类索引:

  • BTree:最常用的索引,BTree适用于处理等值查询和范围查询
  • HASH:只能处理简单的等值查询
  • GiST:不是单独一种索引类型,而是一种架构,可以在这种架构上实现很多不同的索引策略
  • SP-GiST:“Space-Partitioned GiST”的缩写,即空间分区GiST索引。
  • GIN:反转索引,可以处理包含多个健的值,如数组等,它支持用户定义的索引策略,可通过定义GIN索引的特定操作符类型实现不同的功能。PG的标准发布中包含了用于一维数组的GIN操作符类,比如,它支持包含操作符“@>”、被包含操作符“@<”、相等操作符“=”、重叠操作符“&&”等等

创建索引

CREATE [UNIQUE] INDEX [CONCURRENTLY] [name] ON table_name [USING method]
( { column_name | (expression)} [COLLATE collation] [opclass] [ASC | DESC] [ NULLS {FIRST | LAST}] [,...])
[WITH (storage_parameter = value [,...])]
[TABLESPACE tablespace_name]
[WHERE predicate]

一般,在创建索引的过程中会把表中的数据全部读一遍,该过程所用时间由表的大小决定,对于较大的表,可能会花费很久的时间。在创建索引的过程中,对表的查询可以正常运行,但对表的增、删、改等操作需要等索引建完后才能进行。对此PG提供了并发创建索引的方法。

假设由一张联系人的表,命令如下:

CREATE TABLE contacts(id int primary key,name varchar(40),phone varchar(32)[],address text
);

在该表中,由于一个人可能有多个电话号码,所以把“phone”定义为一个数组

为了实现按name快速查找,可以在字段name上建一个简单的BTree索引,命令如下:

CREATE INDEX idx_contacts_name on contacts(name);

如果像按电话号码phone字段快速查询,比如查询某个电话号码是谁的,由于此字段是一个数组,前面所建的BTree索引将不再起作用,这时可以建一个GIN索引,命令如下:

CREATE INDEX idx_contacts_phone on contacts using gin(phone);

如果想要查询号码“15873135680”是谁的,可以使用下面的查询语句:

SELECT * FROM contacts WHERE phone @> array['15873135680'::varchar(32)];

HASH索引的更新不会记录到WAL日志中,所以实际使用场景很少

创建索引可以指定存储参数“WITH(storage_paramter = value)”,常用的存储参数为FILLFACTOR,比如,可以这样创建索引:

CREATE INDEX idx_contacts_name on contacts(name) WITH (FILLFACTOR = 50);

也可以按降序创建索引:

CREATE INDEX idx_contacts_name on contacts(name desc);

如果字段name中有空值,则可以在创建索引时指定空值排在非空值前面:

CREATE INDEX idx_contacts_name on contacts(name desc NULLS FIRST);

也可以指定空值排在非空值后面:

CREATE INDEX idx_contacts_name on contacts(name desc NULLS LAST);

并发创建索引

通常情况下,在创建索引的时候PG会锁定表以防止写入,然后对表做全表扫描,从而完成创建索引的操作。在此过程中,其他用户仍然可以读取表,但是插入、更新、删除等操作将一直被阻塞,直到索引创建完毕

如果这张表示更新较频繁且比较大的表,那么创建索引可能需要几十分钟,甚至数个小时,这段时间内都不能做任何插入、删除、更新操作,这在大多数的在线数据库中都是不可接受的。

所以,PG支持在长时间阻塞更新的情况下建索引,通过在CREATE INDEX中加CONCURRENTLY选项来实现。

该选项PG会执行表的两次扫描,因此会需要更长的时间来建索引,但是它还是很有用的。

并发创建索引测试

image

create table jxx_test(id int primary key,note text);insert into jxx_test select generate_series(1,5000000),generate_series(1,5000000); 

同时开两个窗口,一个窗口执行创建索引,另一个窗口删除数据:
image
image

删除操作在创建完索引之后才会执行(图片上因为执行删除数据操作在创建索引发生之后才触发,有时间差)

继续同时开两个窗口,一个窗口执行创建索引(使用concurrently关键字),另一个窗口删除数:
image
image

创建索引时间虽然变长了,但是删除数据的操作不受阻塞,直接完成

并发重建索引

PG中,重建索引不支持CONCURRENTLY选项,但是PG中一个字段可以创建两个索引,所以并发重建索引可以执行以下步骤:

  1. 使用CONCURRENTLY选项建一个新的索引
  2. 删除旧索引

无效索引

创建索引过程需要注意,如果在创建索引过程中强行取消操作,会留下一个无效的索引:

  1. 仍然会导致更新速度变慢
  2. 如果是唯一索引,这个无效索引还会导致插入重复值失败

image
上述图片,可以通过shell窗口输入\d+table的命令查看。

需要手动删除该索引:

drop index idx_jxx_test_note;

修改索引

alter index idx_jxx_test_note rename to idx_jxx_test_note_new;

image

相关文章:

PostgreSQL-如何创建并发索引

索引简介 索引是数据库中一种快速查询数据的方法。索引中记录了表中的一列或多列值与其物理位置之间的对应关系&#xff0c;就好比一本书前面的目录&#xff0c;通过目录中页码就能快速定位到我们需要查询的内容。 建立索引的好处是加快对表中记录的查找或排序&#xff0c;但…...

【大数据模型】使用Claude浅试一下

汝之观览&#xff0c;吾之幸也&#xff01;本文主要聊聊Claude使用的流程&#xff0c;在最后对国内外做了一个简单问题的对比&#xff0c;希望国内的大数据模型更快的发展。 一、产品介绍 claude官网 Claude是一款由前OpenAI的研究员和工程师开发的新型聊天机器人&#xff0c;…...

鼎盛合——国产电量计芯片的分类与发展

电池技术在 200 余年的时间里不断演进&#xff0c;并在近 30 年的时间里取得了飞速发展&#xff0c;从最早期的铜-锌电池、铅酸电池&#xff0c;到目前的锂电池、钠电池&#xff0c;电池能量密度从早期的~10Wh/kg 飞速攀升至 200Wh/kg。回顾历史上来看&#xff0c;电池管理系统…...

交叉验证之KFold和StratifiedKFold的使用(附案例实战)

&#x1f935;‍♂️ 个人主页&#xff1a;艾派森的个人主页 ✍&#x1f3fb;作者简介&#xff1a;Python学习者 &#x1f40b; 希望大家多多支持&#xff0c;我们一起进步&#xff01;&#x1f604; 如果文章对你有帮助的话&#xff0c; 欢迎评论 &#x1f4ac;点赞&#x1f4…...

Cloud Kernel SIG月度动态:发布ANCK 5.10、4.19新版本,ABS新增仓库构建功能

Cloud Kernel SIG&#xff08;Special Interest Group&#xff09;&#xff1a;支撑龙蜥内核版本的研发、发布和服务&#xff0c;提供生产可用的高性价比内核产品。 01 SIG 整体进展 发布 ANCK 5.10-014 版本。 发布 ANCK 4.19-027.2 版本。 ABS 平台新增 OOT 仓库临时构建功…...

JavaScript:new操作符

一、new操作符的作用 用于创建一个给定构造函数的实例对象 new操作符创建一个用户定义的对象类型的实例 或 具有构造函数的内置对象的实例。二、new一个构造函数的执行过程 2.1、创建一个空对象obj 2.2、将空对象的原型与构造函数的原型连接起来 2.3、将构造函数中的this绑定…...

XShell配置以及使用教程

目录 1、XShell介绍 2、安装XShell 1. 双击运行XShell安装文件&#xff0c;并点击“下一步” 2. 点击“我接受许可证协议中的条款”&#xff0c;点击“下一步” 3. 点击“浏览”更改默认安装路径&#xff0c;点击“下一步” 4. 直接点击“安装” 5. 安装完成&#xff0…...

Vue3 基础语法

文章目录 1.创建Vue项目1.1创建项目1.2 初始项目 2.vue3 语法2.1 复杂写法2.2 简易写法2.3 reactive&#xff08;对象类型&#xff09;2.4 ref&#xff08;简单类型&#xff09;2.5 computed(计算属性)2.6 watch&#xff08;监听&#xff09; 3.vue3 生命周期4.vue3 组件通信4.…...

【开源项目】Disruptor框架介绍及快速入门

Disruptor框架简介 Disruptor框架内部核心的数据结构是Ring Buffer&#xff0c;Ring Buffer是一个环形的数组&#xff0c;Disruptor框架以Ring Buffer为核心实现了异步事件处理的高性能架构&#xff1b;JDK的BlockingQueue相信大家都用过&#xff0c;其是一个阻塞队列&#xf…...

双向链表实现约瑟夫问题

title: 双向链表实现约瑟夫问题 date: 2023-05-16 11:42:26 tags: **问题&#xff1a;**知n个人围坐在一张圆桌周围。从编号为k的人开始报数&#xff0c;数到m的那个人出列&#xff1b;他的下一个人又从1开始报数&#xff0c;数到m的那个人又出列&#xff1b;依此规律重复下去&…...

日心说为人类正确认识宇宙打下了基础(善用工具的重要性)

文章目录 引言I 伽利略1.1 借助天文望远镜获得了比别人更多的信息。1.2 确定了科学研究方法&#xff1a;实验和观测 II 开普勒三定律 引言 享有科学史上崇高地位的人&#xff0c;都需要在构建科学体系上有重大贡献。 日心说在哥白尼那里还是一个假说&#xff0c;伽利略拿事实…...

Kali-linux系统指纹识别

现在一些便携式计算机操作系统使用指纹识别来验证密码进行登录。指纹识别是识别系统的一个典型模式&#xff0c;包括指纹图像获取、处理、特征提取和对等模块。如果要做渗透测试&#xff0c;需要了解要渗透测试的操作系统的类型才可以。本节将介绍使用Nmap工具测试正在运行的主…...

Java版本电子招标采购系统源码:营造全面规范安全的电子招投标环境,促进招投标市场健康可持续发展

营造全面规范安全的电子招投标环境&#xff0c;促进招投标市场健康可持续发展 传统采购模式面临的挑战 一、立项管理 1、招标立项申请 功能点&#xff1a;招标类项目立项申请入口&#xff0c;用户可以保存为草稿&#xff0c;提交。 2、非招标立项申请 功能点&#xff1a;非招标…...

Java字符串知多少:String、StringBuffer、StringBuilder

一、String 1、简介 String 是 Java 中使用得最频繁的一个类了&#xff0c;不管是作为开发者的业务使用&#xff0c;还是一些系统级别的字符使用&#xff0c; String 都发挥着重要的作用。String 是不可变的、final的&#xff0c;不能被继承&#xff0c;且 Java 在运行时也保…...

中国20强(上市)游戏公司2022年财报分析:营收结构优化,市场竞争进入白热化

易观&#xff1a;受全球经济增速下行的消极影响&#xff0c;2022年国内外游戏市场规模普遍下滑。但中国游戏公司凭借处于全球领先水平的研发、发行和运营的能力与经验&#xff0c;继续加大海外市场布局&#xff0c;推动高质量发展迈上新台阶。 风险提示&#xff1a;本文内容仅代…...

如何自学C++编程语言,聊聊C++的特点,别轻易踩坑

为什么现在有那么多C培训班呢&#xff1f;因为这些培训班可以为学生安排工作&#xff0c;而外包公司因为缺人&#xff0c;需要做很多项目&#xff0c;可能需要在全国各地分配不同的程序员去干不同的项目&#xff0c;因此需要大量的程序员入职。这样&#xff0c;外包公司就会找培…...

算法Day07 | 454.四数相加II,383. 赎金信,15. 三数之和, 18. 四数之和

Day07 454.四数相加II383. 赎金信15. 三数之和18. 四数之和 454.四数相加II 题目链接&#xff1a;454.四数相加II 寻找两个数组之和&#xff0c;是否与另外两个数组之和有特定的关系。 因为数值可能跨度太大&#xff0c;选择使用下标表示为对应的数值大小&#xff0c;会很浪费…...

ps抠图、抠头发去背景等

方法一&#xff1a;背景橡皮擦 一、很早之前我们使用的是魔术棒工具&#xff0c;但现在我们可以使用Photoshop 有内置的“背景橡皮擦” 步骤&#xff1a; 第1步&#xff1a;在Photoshop中打开需要修的图。 第2步&#xff1a;单击并按住工具栏…...

计算机组成原理基础练习题第一章

有些计算机将一部分软件永恒地存于只读存储器中&#xff0c;称之为&#xff08;&#xff09; A.硬件    B.软件C.固件    D.辅助存储器输入、输出装置以及外界的辅助存储器称为&#xff08;&#xff09; A.操作系统    B.存储器 C.主机      D.外围设备完整的计算机系…...

[PyTorch][chapter 34][池化层与采样]

前言&#xff1a; 这里主要讲解一下卷积神经网络中的池化层与采样 目录 DownSampleMax poolingavg poolingupsampleReLu 1&#xff1a; DownSample 下采样,间隔一定行或者列进行采样&#xff0c;达到降维效果 早期LeNet-5 就采样该采样方式。 LeNet-5 2 Max pooling 最大值采样…...

基于数字孪生的水厂可视化平台建设:架构与实践

分享大纲&#xff1a; 1、数字孪生水厂可视化平台建设背景 2、数字孪生水厂可视化平台建设架构 3、数字孪生水厂可视化平台建设成效 近几年&#xff0c;数字孪生水厂的建设开展的如火如荼。作为提升水厂管理效率、优化资源的调度手段&#xff0c;基于数字孪生的水厂可视化平台的…...

2021-03-15 iview一些问题

1.iview 在使用tree组件时&#xff0c;发现没有set类的方法&#xff0c;只有get&#xff0c;那么要改变tree值&#xff0c;只能遍历treeData&#xff0c;递归修改treeData的checked&#xff0c;发现无法更改&#xff0c;原因在于check模式下&#xff0c;子元素的勾选状态跟父节…...

Nuxt.js 中的路由配置详解

Nuxt.js 通过其内置的路由系统简化了应用的路由配置&#xff0c;使得开发者可以轻松地管理页面导航和 URL 结构。路由配置主要涉及页面组件的组织、动态路由的设置以及路由元信息的配置。 自动路由生成 Nuxt.js 会根据 pages 目录下的文件结构自动生成路由配置。每个文件都会对…...

鸿蒙中用HarmonyOS SDK应用服务 HarmonyOS5开发一个生活电费的缴纳和查询小程序

一、项目初始化与配置 1. 创建项目 ohpm init harmony/utility-payment-app 2. 配置权限 // module.json5 {"requestPermissions": [{"name": "ohos.permission.INTERNET"},{"name": "ohos.permission.GET_NETWORK_INFO"…...

【论文阅读28】-CNN-BiLSTM-Attention-(2024)

本文把滑坡位移序列拆开、筛优质因子&#xff0c;再用 CNN-BiLSTM-Attention 来动态预测每个子序列&#xff0c;最后重构出总位移&#xff0c;预测效果超越传统模型。 文章目录 1 引言2 方法2.1 位移时间序列加性模型2.2 变分模态分解 (VMD) 具体步骤2.3.1 样本熵&#xff08;S…...

华硕a豆14 Air香氛版,美学与科技的馨香融合

在快节奏的现代生活中&#xff0c;我们渴望一个能激发创想、愉悦感官的工作与生活伙伴&#xff0c;它不仅是冰冷的科技工具&#xff0c;更能触动我们内心深处的细腻情感。正是在这样的期许下&#xff0c;华硕a豆14 Air香氛版翩然而至&#xff0c;它以一种前所未有的方式&#x…...

R 语言科研绘图第 55 期 --- 网络图-聚类

在发表科研论文的过程中&#xff0c;科研绘图是必不可少的&#xff0c;一张好看的图形会是文章很大的加分项。 为了便于使用&#xff0c;本系列文章介绍的所有绘图都已收录到了 sciRplot 项目中&#xff0c;获取方式&#xff1a; R 语言科研绘图模板 --- sciRplothttps://mp.…...

用鸿蒙HarmonyOS5实现中国象棋小游戏的过程

下面是一个基于鸿蒙OS (HarmonyOS) 的中国象棋小游戏的实现代码。这个实现使用Java语言和鸿蒙的Ability框架。 1. 项目结构 /src/main/java/com/example/chinesechess/├── MainAbilitySlice.java // 主界面逻辑├── ChessView.java // 游戏视图和逻辑├──…...

前端调试HTTP状态码

1xx&#xff08;信息类状态码&#xff09; 这类状态码表示临时响应&#xff0c;需要客户端继续处理请求。 100 Continue 服务器已收到请求的初始部分&#xff0c;客户端应继续发送剩余部分。 2xx&#xff08;成功类状态码&#xff09; 表示请求已成功被服务器接收、理解并处…...

对象回调初步研究

_OBJECT_TYPE结构分析 在介绍什么是对象回调前&#xff0c;首先要熟悉下结构 以我们上篇线程回调介绍过的导出的PsProcessType 结构为例&#xff0c;用_OBJECT_TYPE这个结构来解析它&#xff0c;0x80处就是今天要介绍的回调链表&#xff0c;但是先不着急&#xff0c;先把目光…...